MySQL Bugs: #34147: can't repair crashed tables

Bug #34147	can't repair crashed tables
Submitted:	29 Jan 2008 20:19	Modified:	17 Apr 2008 9:43
Reporter:	Lubomir Host	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.51-3-log (Debian)	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	repair table, repair table extended

Description:
(root@localhost) [mysql]> update tables_priv set host = 'localhost';
ERROR 126 (HY000): Incorrect key file for table './mysql/tables_priv.MYI'; try to repair it
(root@localhost) [mysql]> repair table tables_priv extended;
+-------------------+--------+----------+----------+
| Table             | Op     | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| mysql.tables_priv | repair | status   | OK       |
+-------------------+--------+----------+----------+
1 row in set (0.00 sec)

(root@localhost) [mysql]> flush tables;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [mysql]> update tables_priv set host = 'localhost';
ERROR 126 (HY000): Incorrect key file for table './mysql/tables_priv.MYI'; try to repair it
(root@localhost) [mysql]> drop table tables_priv;
Query OK, 0 rows affected (0.00 sec)

How to repeat:
This can be reproduced with any table, not just mysql.tables_priv. Corrupted mysql.* tables can be corrupted if they are very very old (2-3 years, old mysql versions 3.x, 4.x).

MySQL will also crash in case of corrupted mysql.* tables. 

Suggested fix:
Make sure 'REPAIR TABLE <table_name> EXTENDED;'  ***really*** repair table and all related objects (e.g. table indexes).

Workaround:
-----------

$ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock  --skip-grant-tables
$ mysqldump --extended-insert=0 -u root -h localhost  mysql > mysql.sql
$ vim mysql.sql
# repair data yourself
mysql> DROP TABLE table_name;
mysql> CREATE TABLE table_name (...);
$ mysql -u root -h localhost mysql < mysql.sql
$ /etc/init.d/mysql restart

Thank you for the report.

REPAIR TABLE does not guarantee repair in any case, but OK message is incorrect if table has not repaired. Please upload example of broken table you have problem with. We need *.MYD, *.MYI AND *.frm files

I can't provide *.MYD, *.MYI and *.frm files, sorry. I didn't make backup of them, only mysqldump has been done. Anyway, these tables contains private data (username, passwords) so there is not a good idea to provide them.

I can provide mysqldump with scrambled username/passwords. There was spaces and \0 characters on the end of column values. Is this information helpfull for you?

You are right, message is wrong. I didn't try USE_FRM flag for REPAIR TABLE, because I didn't know about them. I can't tell if USE_FRM will help me. It is a new flag?

Working REPAIR TABLE command from SQL shell is important for service availability.

Thanks for your quick reply

Thank you for the feedback.

USE_FRM exists for a long time already. See also: http://mysql.bkbits.net:8080/mysql-5.0/?PAGE=cset&REV=3c8f8a51A9zzvmwNXHNH4tCKwr8zVQ

mysqldump output with changed passwords could help us to identify the problem only if reloading it can create broken tables which is unlikely. But if it can, please, provide such dump for us.

According to private data. You indicated you had problems with tables_priv which does not contain passwords. We don't need all tables. Additionally you can upload files privately and only we could see user names.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

We are still waiting for your mysqldump.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".