| 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: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.15/4.0BK/5.0BK/5.1BK/4.1BK | OS: | Windows (WinXP SP2/Linux Suse 10) |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
| Tags: | corruption, myisam | ||
[28 Mar 2006 16:49]
MySQL Verification Team
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]
MySQL Verification Team
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.
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.