MySQL Bugs: #29253: csv table reportedly marked as crashed

Bug #29253	csv table reportedly marked as crashed
Submitted:	20 Jun 2007 22:01	Modified:	23 Jul 2007 1:06
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: CSV	Severity:	S1 (Critical)
Version:	5.1.20	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	corruption

Description:
if you have multiple threads doing insert/delete into a CSV table, check table will report it to be crashed sometimes.

mysql> show processlist;
+----+------+------+------+---------+------+--------+-----------------------------
| Id | User | Host | db   | Command | Time | State  | Info
+----+------+------+------+---------+------+--------+-----------------------------
| 31 | root |      | test | Query   |    0 | NULL   | show processlist
| 40 | root |      | test | Sleep   |    4 |        | NULL
| 41 | root |      | test | Execute |    0 | Locked | insert into t1 values (?,?)
| 42 | root |      | test | Execute |    0 | Locked | insert into t1 values (?,?)
| 43 | root |      | test | Execute |    0 | Locked | insert into t1 values (?,?)
| 44 | root |      | test | Execute |    0 | Locked | insert into t1 values (?,?)
| 45 | root |      | test | Execute |    0 | Locked | insert into t1 values (?,?)
| 46 | root |      | test | Query   |    0 | Locked | delete from t1 limit 5
| 47 | root |      | test | Execute |    0 | Locked | insert into t1 values (?,?)
| 48 | root |      | test | Query   |    0 | end    | delete from t1 limit 5
| 49 | root |      | test | Query   |    0 | Locked | delete from t1 limit 5
| 50 | root |      | test | Execute |    0 | Locked | insert into t1 values (?,?)
+----+------+------+------+---------+------+--------+-----------------------------
12 rows in set (0.00 sec)

mysql> check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | error    | Corrupt  |
+---------+-------+----------+----------+
1 row in set (1.03 sec)

10 threads running, 0002189 successful queries.  0000000 failed queries (182.416667 QPS
mysql_stmt_execute failed: Table 't1' is marked as crashed and should be repaired (1194
host 0, thread 3 failed in prepared_query_execute
mysql_stmt_execute failed: Table 't1' is marked as crashed and should be repaired (1194
mysql_stmt_execute failed: Table 't1' is marked as crashed and should be repaired (1194
mysql_stmt_execute failed: Table 't1' is marked as crashed and should be repaired (1194
mysql_stmt_execute failed: Table 't1' is marked as crashed and should be repaired (1194
mysql_stmt_execute failed: Table 't1' is marked as crashed and should be repaired (1194
host 0, thread 2 failed in prepared_query_execute
host 0, thread 0 failed in prepared_query_execute
host 0, thread 4 failed in prepared_query_execute
mysql_stmt_execute failed: Table 't1' is marked as crashed and should be repaired (1194
mysql_stmt_execute failed: Table 't1' is marked as crashed and should be repaired (1194
mysql_stmt_execute failed: Table 't1' is marked as crashed and should be repaired (1194
mysql_stmt_execute failed: Table 't1' is marked as crashed and should be repaired (1194

How to repeat:
will attach a testcase

Suggested fix:
.

see top of file for user, host, password, gcc compile example.

Attachment: bug29253.c (text/plain), 5.95 KiB.

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/30794

ChangeSet@1.2556, 2007-07-12 20:24:45+05:00, ramil@mysql.com +2 -0
  Fix for bug #29253: csv table reportedly marked as crashed
  
  Problem: the data file changes made during delete/update are not visible to 
  parallel working threads as the file is reopened, so reading data 
  with old descriptors might miss the changes.
  
  Fix: reopen the data file before reading if it was reopened during 
  delete/update to ensure there's no data behind.
  
  Note: there's no simple test case.

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/30910

ChangeSet@1.2556, 2007-07-14 00:13:37+05:00, ramil@mysql.com +2 -0
  Fix for bug #29253: csv table reportedly marked as crashed
  
  Problem: the data file changes made during delete/update are not visible to 
  other threads as the file is reopened, so reading data 
  with old descriptors might miss the changes.
  
  Fix: reopen the data file before reading if it was reopened during 
  delete/update to ensure there's no data behind.
  
  Note: there's no simple test case.

Pushed into 5.1.21-beta

Noted in 5.1.21 changelog.

CHECK TABLE could erroneously report table corruption for a CSV table
if multiple threads were modifying the table at the same time.

so, i've read the bug and description - but i don't really understand what the fix is?!  can somebody simplify the solution so i can use it?