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?
I’ve seen this mostly happen when the underlying file system goes out of free inodes. Check with df -hi. Usually something is filling it with loads of small files, like e.g. php session files that don’t get cleaned up.
This discussion is probably aimed at MyISAM tables only.
– Rick JamesApr 13, 2021 at 16:01
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- My disk filled up while doing this, so it failed. After clearing space and trying it again, I was told it couldn’t create “table_file_name.TMD”. I guessed that it was because it already existed from when it failed before. After removing that file, it worked. – Tyler Collier Sep 18, 2013 at 13:07
This solved my problem as well thank you. Using the –force option is what allows the repair to run even if something with the stucture or information is incorrect.
–
I ran into the error myisamchk: error: Not enough memory for blob at 541031156 (need 1953787962). The command myisamchk --max-record-length=1048576 -r -v -f $TABLE_NAME helped. I also created a backup copy of the file first; maybe a good general precaution.
–
I’m getting similar type but not for single table, mysql crashed after few seconds of started. Would anybody can help to solve this issue. I’ve followed some solutions but unfortunately replaced by backup folder.
–
Try running the following query:
repair table <table_name>;
I had the same issue and it solved me the problem.
This was a quick fix! Had a “bad” options table. Make sure that you select the correct database first !
– jyllstuartJan 10, 2019 at 15:54
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.
One small comment, taking this solution that worked for me too may require you yo redefine MySQL password. From XAMPP UI open “shell” and use the command # mysqladmin -u root password Type the password you previously used and confirm password. Not it works again. – Gilad Tiram Jun 5, 2021 at 10:44
- top answer, worked out smooth without any errors – Sparkm4n Jan 7, 2022 at 22:05
none of the solutions worked for me. only this solution worked. its weird cause other people said the same thing… – Daniel_Ranjbar Aug 16, 2022 at 4:59
This si the only solution worked for me. – Nijat Mursali Dec 9, 2022 at 9:27
Yeap that’s the one Key is to always check mysql log file last few lines for error and you’ll get answer within one minute 😀
– VipertecproDec 14, 2022 at 5:19
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>Perfect, I was getting the permissions error. This sorted it.
– lee_mcmullenJun 14, 2022 at 10:25
I needed to add USE_FRM to the repair statement to make it work.
REPAIR TABLE <table_name> USE_FRM;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=2GGo to data_dir and remove the Your_table.TMP file after repairing <Your_table> table.
search for it with this commandgrep -r datadir /etc/mysql/. Should be/var/lib/mysqlunder debian and ubuntu. – ThorstenS Feb 10, 2016 at 6:30
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/.
‘s solution worked for me, only in my case the table was ./mysql/db. I tried running myisamchk, but if pass ‘db’ as table name, it says ‘file not found’, if a specify the extension, i get “…is not a db file”
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
Please don’t add “thank you” as an answer. Instead, vote up the answers that you find helpful. – From Review
This really works for the crash table situation, the only note I would like to add – replace $TABLE_NAME by your table file name (without extension).
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
enter to your mysql and select your database then
repair table ;
this work with me
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
source : https://stackoverflow.com/questions/8843776/mysql-table-is-marked-as-crashed-and-last-automatic-repair-failed