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

[20 Jun 2007 22:01] Shane Bester
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:
.
[20 Jun 2007 22:05] Shane Bester
see top of file for user, host, password, gcc compile example.

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

[12 Jul 2007 15:25] 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/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.
[13 Jul 2007 19:13] 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/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.
[19 Jul 2007 15:48] Bugs System
Pushed into 5.1.21-beta
[23 Jul 2007 1:06] Paul Dubois
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.
[3 Oct 2008 19:21] yur delacru
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?