MySQL error 1525: Incorrect DATETIME value: ''

šŸ’¬ General Discussion
1

with " Targets - Lists" query on email_addresses fails

[FATAL]  Query Failed: SELECT leads.id AS id, `email_addresses_primary`.id AS 'email_addresses_primary_id' FROM leads LEFT JOIN email_addr_bean_rel ON leads.id=email_addr_bean_rel.bean_id AND email_addr_bean_rel.deleted=0
 AND email_addr_bean_rel.primary_address = '1' AND email_addr_bean_rel.bean_module = 'Leads'
LEFT JOIN email_addresses `email_addresses_primary` ON `email_addresses_primary`.id=email_addr_bean_rel.email_address_id AND `email_addresses_primary`.deleted=0
 WHERE (email_addresses_primary.confirm_opt_in_date IS NULL OR email_addresses_primary.confirm_opt_in_date = '') AND leads.status = 'New' AND leads.date_modified > '2020-05-11 18:25:50' AND leads.date_entered <> leads.date_modified AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id='id' AND aow_processed.parent_id=leads.id AND aow_processed.status = 'Complete' AND aow_processed.deleted = 0) AND leads.deleted = 0 : MySQL error 1525: Incorrect DATETIME value: ''

The part ā€œOR email_addresses_primary.confirm_opt_in_date = ā€˜ā€™ā€ just need to be removed and it would work.

How can I easily modify this to not wait for an new release?

2

You donā€™t even say what your versions of SuiteCRM and MySQL areā€¦ :frowning:

3

the issue is with most current versions SuiteCRM 7.12.7 (not with 8.2 -tested) and mysql Ver 8.0.31-0ubuntu0.20.04.1

4

Do you get the same result in the live demo? Just to check if the problem is specific to your installation or not.

And what exactly are the steps to reproduce?

5

Iā€™m not aware of a live demo in v7. Can you please send a link?
As mentioned it is not in V 8.2 (after upgrade test of same server - v7.12.7 is still in production)

6

https://demo.suiteondemand.com/

7

I cannot reproduce it in demo.
On my system it appears right away on opening contacts Subpannel in Targets - Lists. Iā€™ll find out what was modified hereā€¦

1 Like
8

If you can try downgrading MySQL without too much hassleā€¦ that would be the first thing Iā€™d tryā€¦

9

OK, Iā€™ve compared the files and they match with the provided from installer. Nothing was changed related to SQL (e.g. only .htaccess.)

The fact I cannot recreate with demo may relays on different behavior of mysql versions. Depending on sql-server configuration (STRICT mode) or version the behavior of DATETIME compare with =ā€™ā€™ differ.

Is there any easy (may dirty) way of modifying the query and remove the query part (as this is not only invalid but also unnecessary)

10

The error belongs to version/config of mysql for sure. However, downgrading is not an option as this is production system and 8.0.31 is most stable security update.

Here release notes of introduced change in 8.0.16:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-16.html

BTW. there are similar issues reported by others:

11

If this hasnā€™t been reported on Github then it would be a good idea to add it thereā€¦

12

yes, thatā€™s done. However, is there any possibility for a quick solution without need to wait for next release bug-fix.

13

Query generation is a very tricky part of the code. Everything is generic and built on the fly, using the field names from the base modules and all the possible customizations from Studio etc. So itā€™s not immediately obvious where that part of the query is being generated, and it would take a serious amount of time to investigate.

If youā€™re a PHP developer and youā€™re working with an IDE and a debugger, it shouldnā€™t be too hard, but in other casesā€¦

14

No response so far on Issue 9825

15

There are a couple of places where you could try fixing itā€¦ based on a quick text search of the code, I found this one, but I doubt that is relevant to the current situation:

ā†’ SuiteCRM/SearchForm2.php at hotfix Ā· salesagility/SuiteCRM Ā· GitHub

If you can get a stack trace of the SQL FATAL it might help track down where the query is being built.