135

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?

Brian Tompsett - 汤莱恩
  • 5,195
  • 62
  • 50
  • 120
CryptoMiningPoolSetupYiimp
  • 1,603
  • 3
  • 14
  • 13
  • 1
    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. – Zrin Oct 21 '14 at 10:24
  • This discussion is probably aimed at MyISAM tables only. – Rick James Apr 13 '21 at 16:01

10 Answers10

279

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
cubuspl42
  • 6,301
  • 4
  • 33
  • 56
Aleksandar Vucetic
  • 13,899
  • 7
  • 48
  • 52
  • 3
    I tried above command I get this error Can't create new tempfile: 'xzclf_ads.TMD' – CryptoMiningPoolSetupYiimp Jan 12 '12 at 23:26
  • Thanks I tried it seemed to work but stopped giving this error? "myisamchk: Disk is full writing '/tmp/STp7Q4qR' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space) myisamchk: Retry in 60 secs. Message reprinted in 600 secs" – CryptoMiningPoolSetupYiimp Jan 12 '12 at 23:32
  • 2
    Well, is disk really full? Can you try running "df -h" ? Most likely it is and you should free up some space...During table repair, it uses some disk space to write to temporary table. As you can see from the error message it writes to your /tmp/ folder – Aleksandar Vucetic Jan 12 '12 at 23:33
  • 13
    I had to navigate to `/var/lib/mysql/` and run `myisamchk -r -v -f ./`.
     – Linus Oleander Mar 19 '13 at 15:32
  • 5
    I've got a `myisam_sort_buffer_size is too small` error, so this command worked for me: `myisamchk -r -v --sort_buffer_size=2G ` – dusan May 22 '13 at 19:33
  • 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 '13 at 13:07
  • 1
    Wow, I thought my whole website was hosed. Such a simple fix. You are my hero! – Anthony Sep 29 '13 at 03:49
  • 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. – tribulant Aug 16 '17 at 13:27
  • 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. – Denis Drescher Jun 03 '19 at 21:34
  • You saved my day. Thanks ! – spacebiker Dec 09 '20 at 09:10
  • Thank's this answer works for me. – José Ayrám Dec 09 '20 at 20:29
115

Try running the following query:

repair table <table_name>;

I had the same issue and it solved me the problem.

cREcker
  • 1,923
  • 1
  • 14
  • 13
12

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>
crazycrv
  • 2,225
  • 2
  • 18
  • 20
6

I needed to add USE_FRM to the repair statement to make it work.

REPAIR TABLE <table_name> USE_FRM;
Mark
  • 321
  • 3
  • 5
4

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:

  1. went to location C:\xampp\mysql, For you, location may be different, make sure you are in right file location.
  2. created backup of the data folder as data-old.
  3. copied folder "mysql" from C:\xampp\mysql\backup
  4. 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.

ashish
  • 2,159
  • 1
  • 14
  • 19
3

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
ThorstenS
  • 282
  • 1
  • 5
2

Go to data_dir and remove the Your_table.TMP file after repairing <Your_table> table.

TLama
  • 71,521
  • 15
  • 192
  • 348
Humbertosms
  • 21
  • 3
  • 1
    search for it with this command `grep -r datadir /etc/mysql/`. Should be `/var/lib/mysql` under debian and ubuntu. – ThorstenS Feb 10 '16 at 06:30
1

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

cryptox1
  • 11
  • 1
1

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/.

Qrzysio
  • 986
  • 2
  • 9
  • 20
  • 1
    '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" – Marlon Jan 26 '21 at 15:29
0

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
Eric Aya
  • 68,765
  • 33
  • 165
  • 232
user3417257
  • 1