Bug #19586 Index corruption (errno 144) doesn't get handled by auto-recover
Submitted: 6 May 2006 20:46 Modified: 25 Jun 2006 18:38
Reporter: Alexey Polyakov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S4 (Feature request)
Version:4.1.18, probably affects other versions OS:Linux (RHEL4)
Assigned to: CPU Architecture:Any

[6 May 2006 20:46] Alexey Polyakov
Description:
After a mysqld or an OS crash, MyISAM indexes often get corrupt.
Setting
myisam_recover = BACKUP,FORCE
helps a lot (most repairs are done without user intervention), it repairs indexes with a message like that in log:
060504 16:41:17 [ERROR] /usr/sbin/mysqld: Can't open file: 'phpbb_users.MYI' (errno: 145)
060504 16:41:17 [Warning] Checking table:   './goatmen_phpb1/phpbb_users'

But, sometimes the error is 144 (not 145), and the table doesn't get repaired and if its some important table, service stops working:

060429 12:21:27 [ERROR] /usr/sbin/mysqld: Can't open file: 'user.MYI' (errno: 144)
060429 12:21:27 [ERROR] /usr/sbin/mysqld: Can't open file: 'user.MYI' (errno: 144)
060429 12:21:27 [ERROR] Fatal error: Can't open and lock privilege tables: Can't open file: 'user.MYI' (errno: 144)
060429 12:21:27 [ERROR] /usr/sbin/mysqld: Can't open file: 'user.MYI' (errno: 144)
060429 12:21:27 [ERROR] /usr/sbin/mysqld: Can't open file: 'user.MYI' (errno: 144)

How to repeat:
I think that killing mysqld with signal 9 and then erasing .MYI file should work.

Suggested fix:
Make the code that handles error 145 and does autorepair also handle error 144.
[22 May 2006 11:25] Valeriy Kravchuk
Thank you for a problem report. I am sure current behaviour is correct and should not be changed. Look (perror results):

MySQL error code 145: Table was marked as crashed and should be repaired
MySQL error code 144: Table is crashed and last repair failed.

If error 144 will be handled by auto-repair, you can get infinite loop of unsuccessfull repairs. Do you really need it?
[22 May 2006 11:48] Alexey Polyakov
Well, if it's a mysql/user.MYI file, what I get doesn't differ much from an infinite loop (technically it does, but it usually renders the whole service useless).

Regarding meaning of the error: 1) I had no messages in error log saying anything about unsuccessful repair attempts; 2) Doing a simple mysqlcheck -r <db> solved the problem every time I encountered it, with no data loss.

I think that the error wasn't reported correctly in my case. Anyway, it's been long since I encountered this error (though I get mysqld crashes a few times daily often, all lead to errors 145).
[25 Jun 2006 18:38] Valeriy Kravchuk
I finally managed to find a way to repeat the behaviour you described (error 144, not any other), at will. Instructions are the following:

1. Add myisam_recover = BACKUP,FORCE to my.cnf and start server.
2. Create any MyISAM-based table, say, t19586, with primary key and another column.
3. Insert two rows into the table.
4. Exit from command line client.
5. Corrupt index:

echo 12345 > var/test/t19586.MYI

6. Try to repair table in mysql, with:

REPAIR TABLE t19586 extended;

7. Stop server, start it again.
8. In mysql, execute:

mysql> select count(*) from t19586 ;
ERROR 1016 (HY000): Can't open file: 't19586.MYI' (errno: 144)
mysql> repair table t19586 use_frm;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.t19586 | repair | warning  | Number of rows changed from 0 to 2 |
| test.t19586 | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.01 sec)

mysql> select count(*) from t19586 ;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> exit
Bye

In the error log you'll get:

openxs@suse:~/dbs/4.1> tail var/suse.err
060625 18:38:28  InnoDB: Started; log sequence number 0 17259660
/home/openxs/dbs/4.1/libexec/mysqld: ready for connections.
Version: '4.1.21'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
060625 18:38:28 [Note] Slave SQL thread initialized, starting replication in log
 'mysql-bin.008' at position 221, relay log './suse-relay-bin.000035' position:
4
060625 18:38:28 [ERROR] Slave I/O thread: error connecting to master 'repl@local
host:3340': Error: 'Lost connection to MySQL server during query'  errno: 2013
retry-time: 60  retries: 86400
060625 18:38:38 [ERROR] /home/openxs/dbs/4.1/libexec/mysqld: Can't open file: 't
19586.MYI' (errno: 144)
060625 18:38:38 [ERROR] /home/openxs/dbs/4.1/libexec/mysqld: Can't open file: 't
19586.MYI' (errno: 144)
060625 18:38:42 [ERROR] /home/openxs/dbs/4.1/libexec/mysqld: Can't open file: 't
19586.MYI' (errno: 144)
060625 18:38:42 [ERROR] /home/openxs/dbs/4.1/libexec/mysqld: Can't open file: 't
19586.MYI' (errno: 144)
060625 18:38:47 [Note] Found 2 of 0 rows when repairing './test/t19586'

So, we proved that error 144 is not auto-recovered, while it is possible (but with use_frm option only). 

Sounds like a reasonable feature request for me.
[27 Dec 2007 15:13] Jonathan Lampe
This happened to one of our customers running a MySQL 4.0.x series database on _Windows_ on Christmas.  A machine crash followed by a reboot brought the application that depended on MySQL back up, but the corrupt table caused the application to repost/rebill a number of items (because the corrupted table help track items already posted).   

The customer's original error report said this:
"We are having a major problem and I need someone to assist.  We're getting this error when looking at the (list of recent runs): error 144 And this error when I try to log in to the server: runtime error"

The MySQL .err file was filled with cheerful items like (note the different "errno"):
071224 22:10:42 MySQL: Can't open file: 'taskruns.MYI'. (errno: 130)
071224 22:57:37 MySQL: Can't open file: 'audit.MYI'. (errno: 145)
071224 22:57:38 Warning: Checking table:   './micstats/audit'
071224 22:57:38 Warning: Recovering table: './micstats/audit'
071224 22:57:38 Error: Couldn't repair table: micstats.audit
...
071225 19:37:39 MySQL: Can't open file: 'audit.MYI'. (errno: 144)
071225 19:37:39 MySQL: Can't open file: 'taskruns.MYI'. (errno: 144)