AnsweredAssumed Answered

Select Query Failing Due to Data Corruption Error

Question asked by Jane Jose on Oct 25, 2019
Latest reply on Oct 28, 2019 by André Lopes

Hi Team,


I am facing some issue with my SugarCRM CE application which is having version 6.5.14 and MySQL version 5.7.20. Which is throwing the database errors sometimes. I have given the sugarcrm logs for reference. When I checked with DB team, they have confirmed everything is fine at their end. But I am frequently getting the following errors and it's able reuse the system after MySQL service restart. Kindly help.

 

Error Logs:

------------------------------------------------------------------Fri Jan 25 05:23:25 2019--------------------------------------------------------------

Fri Jan 25 05:23:25 2019 [2495][88696f0a-26b8-a9cf-dbfa-5c34275ff971][FATAL] Retrieving record by id accounts:533d6e29-85e9-05f0-bbc7-5c4a2cfe3c7a found Query Failed: SELECT accounts.*,accounts_cstm.* FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c WHERE accounts.id = '533d6e29-85e9-05f0-bbc7-5c4a2cfe3c7a' AND accounts.deleted=0 LIMIT 0,1: MySQL error 1877: Operation cannot be performed. The table 'CRMIC.accounts_cstm' is missing, corrupt or contains bad data.

 


-------------------------------------------------------------------Fri Aug 30 10:06:13 2019-------------------------------------------------------------

Fri Aug 30 10:06:13 2019 [30364][5fc51e15-9604-36c9-c5dd-5cdcd92575ef][FATAL] Query Failed: SELECT accounts.id ,accounts_cstm.account_number_c,accounts_cstm.msisdn_c,accounts_cstm.nric_c,accounts_cstm.persona_id_c, accounts.name , accounts.billing_address_city , accounts.billing_address_state , accounts.assigned_user_id FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c where (( accounts_cstm.persona_id_c like 'M_RAJ99@YES.MY%' )) AND accounts.deleted=0 ORDER BY accounts.date_entered DESC LIMIT 0,21: MySQL error 1034: Incorrect key file for table 'accounts'; try to repair it

 

 

---------------------------------------------------------------------Fri Aug 30 15:40:15 2019-----------------------------------------------------------

Fri Aug 30 15:40:15 2019 [31988][99ff3336-7db1-36b5-ed39-5cdccd79a63e][FATAL] Error retrieving Account list: Query Failed: (SELECT cases.id , cases.case_number , cases.name , cases.status , cases.priority , cases.date_entered , LTRIM(RTRIM(CONCAT(IFNULL(jt0.first_name,''),' ',IFNULL(jt0.last_name,'')))) assigned_user_name , cases.assigned_user_id , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, cases.created_by , 'cases' panel_name FROM cases LEFT JOIN cases_cstm ON cases.id = cases_cstm.id_c LEFT JOIN users jt0 ON cases.assigned_user_id=jt0.id AND jt0.deleted=0

AND jt0.deleted=0 INNER JOIN accounts cases_rel ON cases.account_id=cases_rel.id AND cases_rel.deleted=0

where ( cases.account_id='bfb8c934-e145-7ec4-15ad-5d189cd9f886') AND cases.deleted=0) ORDER BY cases.case_number desc LIMIT 0,11: MySQL error 1877: Operation cannot be performed. The table 'CRMIC.accounts' is missing, corrupt or contains bad data.





------------------------------------------------------------------------Wed Sep 4 15:37:21 2019--------------------------------------------------------

Wed Sep 4 15:37:21 2019 [25527][1445a694-267e-8b7d-00a0-56c4302e500f][FATAL] Query Failed: SELECT cases.id ,cases_cstm.sr_number_c, cases.name , accounts.name account_name , accounts.assigned_user_id account_name_owner , 'Accounts' account_name_mod, cases.account_id , cases.priority , cases.status , LTRIM(RTRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')))) assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, cases.date_entered , LTRIM(RTRIM(CONCAT(IFNULL(jt2.first_name,''),' ',IFNULL(jt2.last_name,'')))) created_by_name , jt2.created_by created_by_name_owner , 'Users' created_by_name_mod, cases.assigned_user_id FROM cases LEFT JOIN cases_cstm ON cases.id = cases_cstm.id_c LEFT JOIN accounts accounts ON cases.account_id=accounts.id AND accounts.deleted=0

AND accounts.deleted=0 LEFT JOIN users jt1 ON cases.assigned_user_id=jt1.id AND jt1.deleted=0

AND jt1.deleted=0 LEFT JOIN users jt2 ON cases.created_by=jt2.id AND jt2.deleted=0

AND jt2.deleted=0 where cases.deleted=0 ORDER BY cases_cstm.sr_number_c LIMIT 0,21: MySQL error 1034: Incorrect key file for table 'accounts'; try to repair it





------------------------------------------------------------------------Wed Sep 4 15:37:22 2019--------------------------------------------------------

Wed Sep 4 15:37:22 2019 [25561][3761f4b7-aa10-e606-8588-5a1bb5f46eaf][FATAL] Retrieving record WHERE id = '6195cca7-85dc-2ec0-f98a-558dc33f8516' AND deleted=0: Query Failed: SELECT accounts.*,accounts_cstm.* FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c WHERE id = '6195cca7-85dc-2ec0-f98a-558dc33f8516' AND deleted=0 LIMIT 0,1: MySQL error 1877: Operation cannot be performed. The table 'CRMIC.accounts' is missing, corrupt or contains bad data.

  

Outcomes