Innodb Question

[mahdy_sharifi]

Hello :

sometimes that MySQL server restart many time some innodb table broke and We get Error as like :

130701 22:00:32 [ERROR] Cannot find or open table "asasmsin_shop/skh_aff_commissions" from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See MySQL :: MySQL 5.1 Reference Manual :: 14.6.12.3 InnoDB General Troubleshooting

Is it possible prevent this issue when database server restart or other times ?

Thanks

 

[MaestriaNick]

You can check the mysql logs /var/lib/mysql/mysql-error.err and /var/lib/mysql/HOSTNAME.err to get an idea of what causing the mysql server restarts. The corruptions as above usually occur if mysql server shutdown/ exit before the data in buffer is properly write back to the files / manually move across the innodb files. This causes mismatch in the data records causing failure. In most cases, these corruptions are beyond repair and we may need to restore the db from backup.

 

[cPanelMichael]

Hello

Yes, finding the cause of the MySQL restarts would be helpful. As mentioned in the previous post, check the MySQL error log to see if there were any specific errors before MySQL restarted.

Thank you.