Mysqli_query failed in AOW Workflow

💬 General Discussion
1

One of my workflows is failing with an error logged and a stuck job queue for ‘Check Inbound Mailboxes’.

The error appears to implicate bad SQL syntax generated (I presume) by the ‘get_flow_beans’ code in AOW_Workflow.php.

Note the “LEFT JOIN cases case” in the log excerpt below. Any suggestions gratefully received.

Tue Jun 8 18:35:55 2021 [28803][1][DEBUG] process_full_list_query: query is SELECT aop_case_updates.id AS id, case.id AS ‘case_id’ FROM aop_case_updates LEFT JOIN cases case ON aop_case_updates.case_id=case.id AND case.deleted=0

WHERE case.status = ‘Open_Pending_Customer_Response_Notification’ AND aop_case_updates.date_entered > ‘2021-06-08 01:36:19’ AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id=‘f206f234-2ebf-4a23-c8ec-60bec9763f83’ AND aow_processed.parent_id=aop_case_updates.id AND aow_processed.status = ‘Complete’ AND aow_processed.deleted = 0) AND aop_case_updates.deleted = 0
Tue Jun 8 18:35:55 2021 [28803][1][INFO] Query:SELECT aop_case_updates.id AS id, case.id AS ‘case_id’ FROM aop_case_updates LEFT JOIN cases case ON aop_case_updates.case_id=case.id AND case.deleted=0

WHERE case.status = ‘Open_Pending_Customer_Response_Notification’ AND aop_case_updates.date_entered > ‘2021-06-08 01:36:19’ AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id=‘f206f234-2ebf-4a23-c8ec-60bec9763f83’ AND aow_processed.parent_id=aop_case_updates.id AND aow_processed.status = ‘Complete’ AND aow_processed.deleted = 0) AND aop_case_updates.deleted = 0
Tue Jun 8 18:35:55 2021 [28803][1][DEBUG] Mysqli_query failed, error was: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'case ON aop_case_updates.case_id=case.id AND case.deleted=0

WHERE case.stat…’ at line 1, query was:
Tue Jun 8 18:35:55 2021 [28803][1][FATAL] Mysqli_query failed.
Tue Jun 8 18:35:55 2021 [28803][1][INFO] Query Execution Time:0.00025606155395508
Tue Jun 8 18:35:55 2021 [28803][1][FATAL] Query Failed: SELECT aop_case_updates.id AS id, case.id AS ‘case_id’ FROM aop_case_updates LEFT JOIN cases case ON aop_case_updates.case_id=case.id AND case.deleted=0

WHERE case.status = ‘Open_Pending_Customer_Response_Notification’ AND aop_case_updates.date_entered > ‘2021-06-08 01:36:19’ AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id=‘f206f234-2ebf-4a23-c8ec-60bec9763f83’ AND aow_processed.parent_id=aop_case_updates.id AND aow_processed.status = ‘Complete’ AND aow_processed.deleted = 0) AND aop_case_updates.deleted = 0 : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'case ON aop_case_updates.case_id=case.id AND case.deleted=0

WHERE case.stat…’ at line 1
Tue Jun 8 18:35:55 2021 [28803][1][DEBUG] process_full_list_query: result is
Tue Jun 8 18:35:55 2021 [28803][1][INFO] Resolving job 7224c4e3-4963-c9ca-e4cb-60bf0fd1a332 as success:
Tue Jun 8 18:35:55 2021 [28803][1][DEBUG] Hook called: SchedulersJobs::before_save
Tue Jun 8 18:35:55 2021 [28803][1][INFO] Query:UPDATE job_queue
SET assigned_user_id=‘1’,name=‘Process Workflow Tasks’,date_modified=‘2021-06-08 06:35:54’,scheduler_id=‘2f7896f3-d278-edfc-2221-5cdcaedc92f2’,execute_time=‘2021-06-08 06:35:00’,status=‘done’,resolution=‘success’,message=NULL,target=‘function::processAOW_Workflow’,data=NULL,requeue=0,retry_count=NULL,failure_count=NULL,job_delay=0,client=‘CRON031952892d6ab8937fbdee355ef876de:28803’,percent_complete=NULL
WHERE job_queue.id = ‘7224c4e3-4963-c9ca-e4cb-60bf0fd1a332’ AND deleted=0
Tue Jun 8 18:35:55 2021 [28803][1][INFO] Query Execution Time:0.15384411811829

2

It would be important to include your versions of…

  • SuiteCRM
  • MariaDB
  • PHP
3

Of course, sorry:
*was SuiteCRM 7.11.18 but now SuiteCRM 7.11.20

  • MariaDB 10.3.27
  • PHP 7.4
    The upgrade to 7.11.20 seems to have eliminated the error I asked about above.
4

Yes, it was probably PHP 7.4 breaking things, but now in 7.11.20 it is compatible with 7.4.

Always check the matrix before jumping to newer PHP versions: