Ruutert Ruutert - 1 year ago 44
MySQL Question

MySql clients are using or haven't closed the table properly

I have a database with about 65 tables. One of the tables is a large MyISAM table called order_line. Now and then this table is corrupt. When I use the CHECK TABLE command I get the messages:

Op | Msg_type | Msg_Text
check | warning | "Table is marked as crashed"
check | warning | "3 clients are using or havent's closed the table properly"
check | error | "Found 1149921 keys of 114948"
check | error | "Corrupt"

I read a lot of this on the internet the most common answer is "you should repair the table". That works for me, but I want to know what the REASON is why this happens and also how can I reproduce this.

I have a webapplication and we have one database per customer (300+). We run scripts to update all these databases. I'm affraid when I do this, a lot of these tables get corrupt.

I also want to know what the best way is to update all these databases. Do I have to shut down MySQL and start it again to make sure no user is using the tables / databases?

Please help.

Answer Source

The reason why MyISAM tables get corrupted is inherent to the implementation of this engine. It relies on the operating system to flush database buffers back to disk instead of using a Write-Ahead-Log and a dedicated strategy to flush dirty buffers. MyISAM is also not transactional.

I strongly suggest using the InnoDB engine which seems much more suitable for your case (table order_line). MyISAM is just not the right engine for your use case.