mysqldump: Got error: 145: Table ‘./wordpress/bot_prli_clicks’ is marked as crashed and should be repaired when using LOCK TABLES
Earlier I was in the process of upgrading MySQL on a CentOS Linux server and ran into an error when I attempted to backup all of the MySQL databases. When issuing the mysqldump command I received an error 145 noting that one of the mysql database tables was marked as crashed and needed to be repaired. Below I have noted the error in more detail as well as how to repair any tables that are marked as crashed.
mysqldump Error 145 Table Marked As Crashed:
[root@dev 02282011]# mysqldump -u root -p -A > mysql-02282011.sql Enter password: mysqldump: Got error: 145: Table './wordpress/bot_prli_clicks' is marked as crashed and should be repaired when using LOCK TABLES [root@dev 02282011]#
The table can be repaired using the myisamchk command as displayed in the below example output. It is highly likely that is one table is showing marked as crashed during mysqldump output that there are multiple tables that are marked as crashed so simply run the below command for each of the tables that have crashed. The output of repairing each table could be different so I have listed the myisamchk repair command for a couple tables to show the possible differences in output. You should run myisamchk from the directory where the table files are stored for the specific database. The below example is a default installation of MySQL on a CentOS Linux server and the database with the crashed tables was named wordpress so the directory the below commands were run from was the /var/lib/mysql/wordpress directory. If you list the contents of the directory you should see multiple files per table so for the bot_prli_clicks table you would see bot_prli_clicks.frm, bot_prli_clicks.MYD, and bot_prli_clicks.MYI files. If the path to your MySQL database is different you could run a search using locate for the database name dot MYD to locate the correct path.
Repair MySQL Crashed Table With myisamchk:
[root@dev wordpress]# myisamchk -r bot_prli_clicks - recovering (with sort) MyISAM-table 'bot_prli_clicks' Data records: 879 - Fixing index 1 Found block that points outside data file at 147388 - Fixing index 2 - Fixing index 3 [root@dev wordpress]#
As you can see multiple items were fixed when running myisamchk against the bot_prli_clicks tables located in the wordpress database. Below is another example of a table in the wordpress database that needed to be repaired.
Repair Another MySQL Crashed Table With myisamchk:
[root@dev wordpress]# myisamchk -r bot_redirection_logs - recovering (with sort) MyISAM-table 'bot_redirection_logs' Data records: 47649 - Fixing index 1 - Fixing index 2 - Fixing index 3 - Fixing index 4 - Fixing index 5 - Fixing index 6 - Fixing index 7 [root@dev wordpress]
I ended up having to repair 3 different tables and located each table that needed to be repaired by repairing the table reported in the mysqldump error, running mysqldump again, repairing the next table, etc. Once all of the tables were repaired I was able to backup all of the MySQL databases as shown in the below output.
Backup MySQL Using mysqldump After Crashed Tables Repaired:
[root@dev 02282011]# mysqldump -u root -p -A > mysql-02282011.sql Enter password: [root@dev 02282011]#
After backing up MySQL I proceeded with the MySQL upgrade from MySQL 5.0.X to MySQL 5.1.X.