Description:
Hi,
Killing a mysql process doing OPTIMIZE TABLE on a MyISAM table will leave it corrupted.
Test table, attached to this report, has only one field: c char(0), no indexes.
Tested with 4.1.12 and 5.0.6-beta.
Regards,
Geert
How to repeat:
-- Attached is a small table
SHOW TABLE STATUS LIKE 'small'\G
/*
CREATE TABLE `small` (
`c` char(0) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
*************************** 1. row ***************************
Name: small
Engine: MyISAM
Version: 9
Row_format: Fixed
Rows: 6619136
Avg_row_length: 5
Data_length: 33095680
[..]
*/
-- delete a row so it gets changed:
DELETE FROM small LIMIT 1;
-- Have an extra terminal ready to kill the process using the
-- connection id running OPTIMIZE TABLE.
-- Or make the table bigger if you want more time :)
-- shell> mysqladmin -uroot kill <connid>
OPTIMIZE TABLE small;
-- when killed 'succesfully', it reports following:
/*
+---------------+----------+----------+-------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+-------------------------------+
| bigdata.small | optimize | error | 22 for record at pos 15699435 |
| bigdata.small | optimize | status | Operation failed |
+---------------+----------+----------+-------------------------------+
*/
CHECK TABLE small;
/*
+---------------+-------+----------+-------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+-------+----------+-------------------------------------------------------------+
| bigdata.small | check | warning | Table is marked as crashed and last repair failed |
| bigdata.small | check | error | Record-count is not ok; is 6619131 Should be: 3139887 |
| bigdata.small | check | warning | Found 5 deleted space. Should be 0 |
| bigdata.small | check | warning | Found 1 deleted blocks Should be: 0 |
| bigdata.small | check | warning | Found 6619132 parts Should be: 3139887 parts |
| bigdata.small | check | error | Corrupt |
+---------------+-------+----------+-------------------------------------------------------------+
From 5.0:
Table: bigdata.small
Op: check
Msg_type: error
Msg_text: Table './bigdata/small' is marked as crashed and last (automatic?) repair failed
*/
REPAIR TABLE small;
/*
+---------------+--------+----------+------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------+----------+------------------------------------------------+
| bigdata.small | repair | warning | Number of rows changed from 3139887 to 6619131 |
| bigdata.small | repair | status | OK |
+---------------+--------+----------+------------------------------------------------+
*/
Description: Hi, Killing a mysql process doing OPTIMIZE TABLE on a MyISAM table will leave it corrupted. Test table, attached to this report, has only one field: c char(0), no indexes. Tested with 4.1.12 and 5.0.6-beta. Regards, Geert How to repeat: -- Attached is a small table SHOW TABLE STATUS LIKE 'small'\G /* CREATE TABLE `small` ( `c` char(0) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; *************************** 1. row *************************** Name: small Engine: MyISAM Version: 9 Row_format: Fixed Rows: 6619136 Avg_row_length: 5 Data_length: 33095680 [..] */ -- delete a row so it gets changed: DELETE FROM small LIMIT 1; -- Have an extra terminal ready to kill the process using the -- connection id running OPTIMIZE TABLE. -- Or make the table bigger if you want more time :) -- shell> mysqladmin -uroot kill <connid> OPTIMIZE TABLE small; -- when killed 'succesfully', it reports following: /* +---------------+----------+----------+-------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+-------------------------------+ | bigdata.small | optimize | error | 22 for record at pos 15699435 | | bigdata.small | optimize | status | Operation failed | +---------------+----------+----------+-------------------------------+ */ CHECK TABLE small; /* +---------------+-------+----------+-------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------+-------+----------+-------------------------------------------------------------+ | bigdata.small | check | warning | Table is marked as crashed and last repair failed | | bigdata.small | check | error | Record-count is not ok; is 6619131 Should be: 3139887 | | bigdata.small | check | warning | Found 5 deleted space. Should be 0 | | bigdata.small | check | warning | Found 1 deleted blocks Should be: 0 | | bigdata.small | check | warning | Found 6619132 parts Should be: 3139887 parts | | bigdata.small | check | error | Corrupt | +---------------+-------+----------+-------------------------------------------------------------+ From 5.0: Table: bigdata.small Op: check Msg_type: error Msg_text: Table './bigdata/small' is marked as crashed and last (automatic?) repair failed */ REPAIR TABLE small; /* +---------------+--------+----------+------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------+--------+----------+------------------------------------------------+ | bigdata.small | repair | warning | Number of rows changed from 3139887 to 6619131 | | bigdata.small | repair | status | OK | +---------------+--------+----------+------------------------------------------------+ */