MySQL Bugs: #10901: analyze table with a table lock always corrupts the MyISAM table

Bug #10901	analyze table with a table lock always corrupts the MyISAM table
Submitted:	27 May 2005 2:48	Modified:	10 Jun 2005 17:48
Reporter:	Matt Clay	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.0.24	OS:	Linux (Linux 2.4.26 (Slackware 8.1))
Assigned to:	Mikael Ronström	CPU Architecture:	Any

Description:
Running 'analyze table' with a table lock on a newly created MyISAM always causes the MyISAM table to become corrupted.  Running 'analyze table' should never corrupt a MyISAM table.

How to repeat:
drop table if exists t;
create table t ( a int );
lock tables t write;
delete from t;
insert into t values(0);
analyze table t;
unlock tables;
repair table t;

Verified on Slackware:

miguel@hegel:~/dbs/4.0$ bin/mysqladmin -uroot create db4
miguel@hegel:~/dbs/4.0$ bin/mysql -uroot db4
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.24-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t ( a int );
Query OK, 0 rows affected (0.01 sec)

mysql> lock tables t write;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(0);
Query OK, 1 row affected (0.00 sec)

mysql> analyze table t;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| db4.t | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> check table t;
+-------+-------+----------+---------------------------------------------+
| Table | Op    | Msg_type | Msg_text                                    |
+-------+-------+----------+---------------------------------------------+
| db4.t | check | warning  | Size of datafile is: 5       Should be: 0   |
| db4.t | check | error    | Record-count is not ok; is 1   Should be: 0 |
| db4.t | check | error    | Corrupt                                     |
+-------+-------+----------+---------------------------------------------+
3 rows in set (0.00 sec)

mysql> 

Thank you for the bug report.

The bug has been analysed now and a patch is reviewed and pending push
The problem occurred when performing analyze table while holding a write lock on the
table. If this was done the state of the table got corrupted if an insert or delete was performed
on the table before analyze table was called.

Thus
lock tables t1 write;
insert into t1 values(0);
analyze table t1;
unlock tables;

will corrupt the table and will be discovered at least when
calling
check table t1;
after unlock tables;

Noted in 4.0.25 changelog.