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

[27 May 2005 2:48] Matt Clay
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;
[27 May 2005 2:59] MySQL Verification Team
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.
[3 Jun 2005 21:03] Mikael Ronström
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;
[10 Jun 2005 17:48] Paul DuBois
Noted in 4.0.25 changelog.