Bug #18544 LOCK TABLES timeout causes MyISAM table corruption
Submitted: 27 Mar 2006 15:16 Modified: 27 Apr 2006 13:47
Reporter: Bertin Colpron Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0.15/4.0BK/5.0BK/5.1BK/4.1BK OS:Microsoft Windows (WinXP SP2/Linux Suse 10)
Assigned to: Ingo Strüwing CPU Architecture:Any
Tags: corruption, myisam

[27 Mar 2006 15:16] Bertin Colpron
Description:
I found this very simple procedure that reproduces the problem:

How to repeat:
1. Create the following tables:

CREATE TABLE t1 (c varchar(255), PRIMARY KEY(c)) ENGINE=InnoDB;
CREATE TABLE t2 (c varchar(255), PRIMARY KEY(c)) ENGINE=MyISAM;

2. Open two connections to that database. I used two command prompts running MYSQL.EXE

3. From connection 1, run:

SET autocommit=0;
INSERT INTO t1 VALUES ('anything');
INSERT INTO t2 VALUES ('anything');

4. From connection 2, run:

SET autocommit=0;
LOCK TABLES t1 READ, t2 READ;

This statement blocks because of the InnoDB lock taken by connection 1.
Wait until the statement timeouts... (~50 seconds).

5. From connection 1, run:

INSERT INTO t2 VALUES ('anything else');

From this point on, table t2 is corrupted (according to CHECK TABLE t2;) 

Further note/hint: If I issue a "FLUSH TABLES" or "CHECK TABLE t2" request between steps 4 and 5, the corruption does not show up.
[28 Mar 2006 16:49] Miguel Solorzano
Thank you for the bug report and test case.

mysql> check table t2;
+---------+-------+----------+---------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                    |
+---------+-------+----------+---------------------------------------------+
| db25.t2 | check | warning  | Size of datafile is: 40       Should be: 20 |
| db25.t2 | check | error    | Found 2 keys of 1                           |
| db25.t2 | check | error    | Corrupt                                     |
+---------+-------+----------+---------------------------------------------+
3 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.20-debug |
+--------------+
1 row in set (0.00 sec)

mysql> 

mysql> check table t2;
+---------+-------+----------+---------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                    |
+---------+-------+----------+---------------------------------------------+
| db25.t2 | check | warning  | Size of datafile is: 40       Should be: 20 |
| db25.t2 | check | error    | Found 2 keys of 1                           |
| db25.t2 | check | error    | Corrupt                                     |
+---------+-------+----------+---------------------------------------------+
3 rows in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.8-beta-debug |
+------------------+
1 row in set (0.01 sec)

mysql> check table t2;
+---------+-------+----------+---------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                    |
+---------+-------+----------+---------------------------------------------+
| db25.t2 | check | warning  | Size of datafile is: 40       Should be: 20 |
| db25.t2 | check | error    | Found 2 keys of 1                           |
| db25.t2 | check | error    | Corrupt                                     |
+---------+-------+----------+---------------------------------------------+
3 rows in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.19-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> check table t2;
+---------+-------+----------+---------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                    |
+---------+-------+----------+---------------------------------------------+
| db25.t2 | check | warning  | Size of datafile is: 40       Should be: 20 |
| db25.t2 | check | error    | Found 2 keys of 1                           |
| db25.t2 | check | error    | Corrupt                                     |
+---------+-------+----------+---------------------------------------------+
3 rows in set (0.01 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.27-debug-log |
+------------------+
1 row in set (0.00 sec)
[28 Mar 2006 16:50] Miguel Solorzano
I forgot to include 4.1's version.
[5 Apr 2006 19:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4515
[11 Apr 2006 11:31] Ingo Strüwing
My test script

Attachment: bug18544-1.sh (application/x-sh, text), 5.75 KiB.

[11 Apr 2006 11:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4783
[12 Apr 2006 8:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4840
[19 Apr 2006 17:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5161
[26 Apr 2006 8:41] Ingo Strüwing
After a locking error the open table(s) were not fully
  cleaned up for reuse. But they were put into the open table
  cache even before the lock was tried. The next statement
  reused the table(s) with a wrong lock type set up. This
  tricked MyISAM into believing that it don't need to update
  the table statistics. Hence CHECK TABLE reported a mismatch
  of record count and table size.
  
  Fortunately nothing worse has been detected yet. The effect
  of the test case was that the insert worked on a read locked
  table. (!)
  
  I added a new function that clears the lock type from all
  tables that were prepared for a lock. I call this function
  when a lock failes.
  
  No test case. One test would add 50 seconds to the
  test suite. Another test requires file mode modifications.
  I added a test script to the bug report. It contains three
  cases for failing locks. All could reproduce a table
  corruption. All are fixed by this patch.
  
  This bug was not lock timeout specific.

Pushed and merged by Sergey Vojtovich and Evgeny Potemkin to 4.1.19, 5.0.21, and 5.1.10.
[27 Apr 2006 13:47] Paul Dubois
Noted in 4.1.19, 5.0.21, 5.1.10 changelogs.