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?
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)
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ā¦
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)
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.
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ā¦
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:
