Mysqli_query failed in suitecrm.log - Scheduled jobs related

SuiteCRM version 7.10.5

These two issues keep repeating in the suitecrm.log every minute. I know they are pointing to two scheduler jobs that are related to Task and Contract. But I do not know what is wrong.

Fri Jun  1 12:48:02 2018 [4093268][1][FATAL] Mysqli_query failed.
Fri Jun  1 12:48:02 2018 [4093268][1][FATAL]  Query Failed: SELECT tasks.id AS id FROM tasks  WHERE tasks.date_start <= tasks.now AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id='9ff2096b-9eab-8fec-9384-5389e201ebf5' AND aow_processed.parent_id=tasks.id AND aow_processed.status = 'Complete' AND aow_processed.deleted = 0) AND tasks.deleted = 0 : MySQL error 1054: Unknown column 'tasks.now' in 'where clause'
Fri Jun  1 12:48:02 2018 [4093268][1][FATAL] Mysqli_query failed.
Fri Jun  1 12:48:02 2018 [4093268][1][FATAL]  Query Failed: SELECT aos_contracts.id AS id FROM aos_contracts  WHERE aos_contracts.renewal_reminder_date <= aos_contracts.now AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id='261a09a3-a91d-4f92-c618-588384df0677' AND aow_processed.parent_id=aos_contracts.id AND aow_processed.status = 'Complete' AND aow_processed.deleted = 0) AND aos_contracts.deleted = 0 : MySQL error 1054: Unknown column 'aos_contracts.now' in 'where clause'

This is a known issue in 7.10.5, wait for the next version or apply this fix manually, you just need to add a couple of parenthesis:

https://github.com/salesagility/SuiteCRM/pull/5932/files

I have already applied this fix some days ago for other issue.

Is the link correct?

I’m glad i’m not the only one getting this message! Glad I had a snapshot of my previous install.

I too got a bunch of query failures for contacts, leads (modules would not even load). The tasks were loading but I did get errors in the log for tasks as well and for cases.

Reverting back to the earlier version for now!

@acoolov I am trying to understand if the fix I linked above, which seemed to solve many of these “unknown column” errors, still doesn’t fix some of them.

In your case, do you still get errors after the fix? Can you tell me which errors, on which screens?

I actually didn’t bother trying the fix. I try not to do any of the manual coding with SuiteCRM. It’s in testing phase for us still, but we do like it and we want to make sure it can work out of the box for us because we just don’t have time to play around with the code. But I am still pretty active on the forum and try to report issues and sometimes fixes to give back to the community. I’ll just wait for the official fix and this time I won’t upgrade before the version is tested and working.

1 Like

Found the actual fix https://github.com/salesagility/SuiteCRM/pull/5953/files

It works for my case.

I have both fixes applied (PR 5932 and 5953) and I’m having this error when opening a contact from one certain user (only from this user specifically)


Tue Jul  3 15:51:49 2018 [18121][c3220439-aa5f-610f-b529-5b0c3ed61049][FATAL] Mysqli_query failed.
Tue Jul  3 15:51:49 2018 [18121][c3220439-aa5f-610f-b529-5b0c3ed61049][FATAL] Error retrieving Contact list:  Query Failed: (SELECT tasks.id ,  tasks.contact_id  ,  tasks.assigned_user_id  ,  tasks.name ,  tasks.status  ,  LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) contact_name ,  '                                                                                                                                                                                                                                                              ' contact_name_owner ,  '                                                                                                                                                                                                                                                              ' contact_name_mod ,  tasks.date_due  as date_start  ,  jt1.user_name assigned_user_name ,  jt1.created_by assigned_user_name_owner  ,  'Users' assigned_user_name_mod,  tasks.created_by  ,  'tasks' panel_name, NULL recurring_source, NULL date_due  FROM tasks   LEFT JOIN  contacts contacts ON tasks.contact_id=contacts.id AND contacts.deleted=0

 AND contacts.deleted=0  LEFT JOIN  users jt1 ON tasks.assigned_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 INNER JOIN  contacts tasks_rel ON tasks.contact_id=tasks_rel.id AND tasks_rel.deleted=0

  where ( tasks.contact_id='99e191db-5b95-4bdb-8590-31d2fa62d33e' AND (tasks.status != 'Completed' AND tasks.status != 'Deferred')) AND tasks.deleted=0) UNION ALL ( SELECT tasks.id ,  tasks.contact_id  ,  tasks.assigned_user_id  ,  tasks.name ,  tasks.status  ,  LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) contact_name ,  '                                                                                                                                                                                                                                                              ' contact_name_owner ,  '                                                                                                                                                                                                                                                              ' contact_name_mod ,  tasks.date_due  as date_start  ,  jt1.user_name assigned_user_name ,  jt1.created_by assigned_user_name_owner  ,  'Users' assigned_user_name_mod,  tasks.created_by  ,  'tasks_parent' panel_name, NULL recurring_source, NULL date_due  FROM tasks   LEFT JOIN  contacts contacts ON tasks.contact_id=contacts.id AND contacts.deleted=0

 AND contacts.deleted=0  LEFT JOIN  users jt1 ON tasks.assigned_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 INNER JOIN  contacts tasks_parent_rel ON tasks.parent_id=tasks_parent_rel.id AND tasks_parent_rel.deleted=0
 AND tasks.parent_type = 'Contacts'
  where ( tasks.parent_id='99e191db-5b95-4bdb-8590-31d2fa62d33e' AND (tasks.status != 'Completed' AND tasks.status != 'Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT meetings.id ,  '                                    '  contact_id  ,  meetings.assigned_user_id  ,  meetings.name ,  meetings.status ,  '                                                                                                                                                                                                                                                              ' contact_name ,  '                                                                                                                                                                                                                                                              ' contact_name_owner ,  '                                                                                                                                                                                                                                                              ' contact_name_mod , NULL date_start,  jt1.user_name assigned_user_name ,  jt1.created_by assigned_user_name_owner  ,  'Users' assigned_user_name_mod,  meetings.created_by  ,  'meetings' panel_name,  meetings.recurring_source , NULL date_due  FROM meetings  LEFT JOIN meetings_cstm ON meetings.id = meetings_cstm.id_c   LEFT JOIN  users jt1 ON meetings.assigned_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 INNER JOIN  meetings_contacts ON meetings.id=meetings_contacts.meeting_id AND meetings_contacts.contact_id='99e191db-5b95-4bdb-8590-31d2fa62d33e' AND meetings_contacts.deleted=0

  where ((meetings.status !='Held' AND meetings.status !='Not Held')) AND meetings.deleted=0 ) UNION ALL ( SELECT calls.id ,  '                                    '  contact_id ,  calls.assigned_user_id  ,  calls.name ,  calls.status ,  '                                                                                                                                                                                                                                                              ' contact_name ,  '                                                                                                                                                                                                                                                              ' contact_name_owner ,  '                                                                                                                                                                                                                                                              ' contact_name_mod , NULL date_start,  jt1.user_name assigned_user_name ,  jt1.created_by assigned_user_name_owner  ,  'Users' assigned_user_name_mod,  calls.created_by  ,  'calls' panel_name,  calls.recurring_source ,  calls.date_due    FROM calls   LEFT JOIN  users jt1 ON calls.assigned_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 INNER JOIN  calls_contacts ON calls.id=calls_contacts.call_id AND calls_contacts.contact_id='99e191db-5b95-4bdb-8590-31d2fa62d33e' AND calls_contacts.deleted=0

  where ((calls.status != 'Held' AND calls.status != 'Not Held')) AND calls.deleted=0 ) ORDER BY date_start desc LIMIT 0,10: MySQL error 1054: Unknown column 'calls.date_due' in 'field list'
Tue Jul  3 15:51:49 2018 [18121][c3220439-aa5f-610f-b529-5b0c3ed61049][FATAL] Exception handling in /var/www/html/suitecrm/include/MVC/Controller/SugarController.php:402
Tue Jul  3 15:51:49 2018 [18121][c3220439-aa5f-610f-b529-5b0c3ed61049][FATAL] Exception in Controller: Error de base de datos. Por favor, consulte SuiteCRM error .log para más detalles.
Tue Jul  3 15:51:49 2018 [18121][c3220439-aa5f-610f-b529-5b0c3ed61049][FATAL] backtrace:
#0 /var/www/html/suitecrm/include/database/DBManager.php(353): sugar_die('Error de base d...')
#1 /var/www/html/suitecrm/include/database/DBManager.php(328): DBManager->registerError('Error retrievin...', 'Error retrievin...', true)
#2 /var/www/html/suitecrm/include/database/MysqliManager.php(177): DBManager->checkError('Error retrievin...', true)
#3 /var/www/html/suitecrm/include/database/MysqlManager.php(285): MysqliManager->query('(SELECT tasks.i...', true, 'Error retrievin...')
#4 /var/www/html/suitecrm/data/SugarBean.php(1291): MysqlManager->limitQuery('(SELECT tasks.i...', 0, 10, true, 'Error retrievin...')
#5 /var/www/html/suitecrm/data/SugarBean.php(1018): SugarBean->process_union_list_query(Object(Contact), '(SELECT tasks.i...', 0, 10, 10, '', Object(aSubPanel), '( SELECT count(...', Array)
#6 /var/www/html/suitecrm/include/ListView/ListView.php(1101): SugarBean::get_union_related_list(Object(Contact), 'date_start desc', 'desc', '', '', -1, 10, '', Object(aSubPanel))
#7 /var/www/html/suitecrm/include/ListView/ListViewSubPanel.php(142): ListView->processUnionBeans(Object(Contact), Object(aSubPanel), 'activities_CELL')
#8 /var/www/html/suitecrm/include/SubPanel/SubPanel.php(220): ListViewSubPanel->process_dynamic_listview('Contacts', Object(Contact), Object(aSubPanel))
#9 /var/www/html/suitecrm/include/SubPanel/SubPanelTiles.php(366): SubPanel->ProcessSubPanelListView('include/SubPane...', Array)
#10 /var/www/html/suitecrm/include/MVC/View/SugarView.php(1186): SubPanelTiles->display()
#11 /var/www/html/suitecrm/include/MVC/View/SugarView.php(235): SugarView->_displaySubPanels()
#12 /var/www/html/suitecrm/include/MVC/Controller/SugarController.php(432): SugarView->process()
#13 /var/www/html/suitecrm/include/MVC/Controller/SugarController.php(375): SugarController->processView()
#14 /var/www/html/suitecrm/include/MVC/SugarApplication.php(109): SugarController->execute()
#15 /var/www/html/suitecrm/index.php(52): SugarApplication->execute()
#16 {main}

I am concerned about this part

MySQL error 1054: Unknown column 'calls.date_due' in 'field list'

Which screen exactly do you go to? A contact’s detail view? Does that contacts have any Calls? Do they have start and end dates?

Can you please revert this change, and see if it fixes the problem? Thanks.

https://github.com/salesagility/SuiteCRM/commit/2e5fc11011ecba69060546c1e5524fc17ed7b4ba

Hi pgr, thank you for your concern. Yes, it is contact’s detail view. It happens with many contacts but only when logging in with an specific user. If I login with another user, I can see the same contact perfeclty, with no errors at all.

For instance, here’s a screenshot of the same contact that throws error from another user. From this user I took the screenshot, I can see everything. There are many calls, messages and tasks related to him. All calls have all dates filled correctly: start date, creation date and modification date. In the call’s detail view there is no field such as “end date”. In the database structure there is a field called “date_end” which has ALWAYS the same value as “date_start”. There is no field such as “date_due” in the database structure.

I have reverted the commit you linked and it started working fine!. It seems the problem was that commit!

Ok, great. Can you please try this version instead?


	  'date_end'=>array(
            'vname' => 'LBL_LIST_DUE_DATE',
		    'vname' => 'LBL_LIST_DUE_DATE',
            'width' => '10%',
		    'width' => '10%',
        ),
		    'alias' => 'date_due',
		    'sort_by' => 'date_end'
		),

Check if you get the error, and also check if you can sort the list by Due date, please.

Sorry pgr, should I add this portion of code as is? Because it seems to me that the brackets are unbalanced and vname and width properties are duplicated. Sorry maybe it’s just my ignorance…

Sorry, maybe I didn’t copy all the brackets.

The only thing to change is the sort_by field, make it ‘date_end’ instead of ‘date_due’

Hi pgr. I tried this modification and it won’t work. But if I change ‘alias’ property to ‘date_end’ and keep ‘sort_by’ as ‘date_due’ it will work correctly. Isn’t it strange? At least it’s working…

When you say working, what do you mean? I’m trying to get two things to work:

Sorry, this thread is kinda messy. I meant my FATAl error. My problem was that the subpanels would not appear in a Contact’s detail view and it would throw MySQL error 1054: Unknown column ‘calls.date_due’ in ‘field list’.

Now that is working, this MySQL error is not thrown anymore and all the subpanels appear correctly.

Previously in 7.10.5, I reported seeing these error messages in the log
https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discussion/19332-mysqli-query-failed-in-suitecrm-log-scheduled-jobs-related#67051

Then I found the fix PR 5953
https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discussion/19332-mysqli-query-failed-in-suitecrm-log-scheduled-jobs-related#67234

I updated the system to 7.10.9 recently and there is a problem of not receiving email notification from the workflow module. I checked the log and saw the error messages are back again.

I checked the file modules/AOW_WorkFlow/AOW_WorkFlow.php and noticed that the change made at line 430 is different from PR 5953 (line 401). Can someone verify if the codes are correct?

Just realised that the error messages are a bit different this time.

Thu Oct  4 13:44:02 2018 [1049427][1][FATAL] Mysqli_query failed.
Thu Oct  4 13:44:02 2018 [1049427][1][FATAL]  Query Failed: SELECT tasks.id AS id FROM tasks  WHERE tasks.date_start <= DATE_ADD(tasks., INTERVAL   ) AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id='9ff2096b-9eab-8fec-9384-5389e201ebf5' AND aow_processed.parent_id=tasks.id AND aow_processed.status = 'Complete' AND aow_processed.deleted = 0) AND tasks.deleted = 0 : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' INTERVAL   ) AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.ao' at line 1
Thu Oct  4 13:44:02 2018 [1049427][1][FATAL] Mysqli_query failed.
Thu Oct  4 13:44:02 2018 [1049427][1][FATAL]  Query Failed: SELECT aos_contracts.id AS id FROM aos_contracts  WHERE aos_contracts.renewal_reminder_date <= DATE_ADD(aos_contracts., INTERVAL   ) AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id='261a09a3-a91d-4f92-c618-588384df0677' AND aow_processed.parent_id=aos_contracts.id AND aow_processed.status = 'Complete' AND aow_processed.deleted = 0) AND aos_contracts.deleted = 0 : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' INTERVAL   ) AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.ao' at line 1

It seems it is related to this reported bug

https://github.com/salesagility/SuiteCRM/issues/6328