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: | |
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
[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)