MySQL table is marked as crashed and last (automatic?) repair failed
MysqlRepairMysql Problem Overview
I was repairing this table suddenly server hanged and when I returned back all tables are ok but this one showing 'in use' and when I try to repair it doesn't proceed.
> ERROR 144 - Table './extas_d47727/xzclf_ads' is marked as crashed and last (automatic?) repair failed
What can I do to repair it?
Mysql Solutions
Solution 1 - Mysql
If your MySQL process is running, stop it. On Debian:
sudo service mysql stop
Go to your data folder. On Debian:
cd /var/lib/mysql/$DATABASE_NAME
Try running:
myisamchk -r $TABLE_NAME
If that doesn't work, you can try:
myisamchk -r -v -f $TABLE_NAME
You can start your MySQL server again. On Debian:
sudo service mysql start
Solution 2 - Mysql
Try running the following query:
repair table <table_name>;
I had the same issue and it solved me the problem.
Solution 3 - Mysql
This was my experience resolving this issue. I'm using XAMPP. I was getting the error below
Fatal error: Can't open and lock privilege tables: Table '.\mysql\db' is marked as crashed and last (automatic?) repair failed
This is what I did to resolve it, step by step:
- went to location C:\xampp\mysql, For you, location may be different, make sure you are in right file location.
- created backup of the data folder as data-old.
- copied folder "mysql" from C:\xampp\mysql\backup
- pasted it inside C:\xampp\mysql\data\ replacing the old mysql folder.
And it worked. Keep in mind, I have already tried around 10 solutions and they didnt work for me. This solutions may or may not work for you but regardless, make backup of your data folder before you do anything.
Note: I would always opt to resolve this with repair command but in my case, i wasnt able to get mysql started at all and i wasnt able to get myisamchk command to work.
Regardless of what you do, create a periodic backup of your database.
Solution 4 - Mysql
If it gives you permission denial while moving to /var/lib/mysql then use the following solution
$ cd /var/lib/
$ sudo -u mysql myisamchk -r -v -f mysql/<DB_NAME>/<TABLE_NAME>
Solution 5 - Mysql
I needed to add USE_FRM to the repair statement to make it work.
REPAIR TABLE <table_name> USE_FRM;
Solution 6 - Mysql
I got myisamchk: error: myisam_sort_buffer_size is too small as error.
The solution
myisamchk -r -v mysql/<DB_NAME>/<TABLE_NAME> --sort_buffer_size=2G
Solution 7 - Mysql
Go to data_dir and remove the Your_table.TMP file after repairing <Your_table> table.
Solution 8 - Mysql
If this happend to your XAMPP installation, just copy global_priv.MAD and global_priv.MAI files from ./xampp/mysql/backup/mysql/ to ./xampp/mysql/data/mysql/.
Solution 9 - Mysql
This is a 100% solution. I tried it myself.
myisamchk -r -v -f --sort_buffer_size=128M --key_buffer_size=128M /var/lib/mysql/databasename/tabloname
Solution 10 - Mysql
I tried the options in the existing answers, mainly the one marked correct which did not work in my scenario. However, what did work was using phpMyAdmin. Select the database and then select the table, from the bottom drop down menu select "Repair table".
- Server type: MySQL
- Server version: 5.7.23 - MySQL Community Server (GPL)
- phpMyAdmin: Version information: 4.7.7
Solution 11 - Mysql
Without stopping the database I go to this folder
cd /var/lib/mysql/$DATABASE_NAME
and then execute the following command
myisamchk -r -v -f $TABLE_NAME
Without having any issue the command successfully completed and resolve the issue
Thank you