Bug #22080 CSV Engine CHECK fails to identify some corruption
Submitted: 7 Sep 2006 11:23 Modified: 10 Oct 2006 18:43
Reporter: MC Brown Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: CSV Severity:S3 (Non-critical)
Version:5.1.11 OS:Linux (Linux 2.6 (Gentoo))
Assigned to: Antony Curtis CPU Architecture:Any

[7 Sep 2006 11:23] MC Brown
Description:
I created a simple table:

create table doctest (id int,string varchar(64)) Engine=CSV;

Inserted three rows:

insert into doctest values(1,'string');
insert into doctest values(2,'string');
insert into doctest values(3,'string');

Checked the table:

 check table doctest;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.doctest | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)

Corrupting a single row:

2","string

Produces a corrupt table:

check table doctest;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.doctest | check | error    | Corrupt  |
+--------------+-------+----------+----------+
1 row in set (0.01 sec)

Different corruption on the same initial table:

"2",string"

This is not identified as bad, and in fact reads bad data:

check table doctest;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.doctest | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> select * from doctest;
+------+--------+
| id   | string |
+------+--------+
|    1 | string |
|    2 | 3      |
|    3 | string |
+------+--------+
3 rows in set (0.00 sec)

How to repeat:
See above. 

Edit the corresponding .CSV while MySQL is running to 'force' the corruption. 

Suggested fix:
CSV Engine should be checking for matching quots within fields
[8 Sep 2006 19:39] Sveta Smirnova
Thank you for the report.

Verified as described on Linux using last BK sources.
[8 Sep 2006 19:46] Sveta Smirnova
While I test this report, I found problem with REPAIR. I think the resolution of these 2 issues is likely to be the same, so I paste "how to reproduce" here:

-- use table doctest from the original testcase filled by original 3 records,
-- then corrupt a single row as in first example:

2","string

Try to repair it:

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

mysql> repair table doctest;
+--------------+--------+----------+----------+
| Table        | Op     | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| test.doctest | repair | status   | OK       |
+--------------+--------+----------+----------+
1 row in set (0.13 sec)

mysql> select * from doctest;
+------+--------+
| id   | string |
+------+--------+
|    1 | string |
+------+--------+
1 row in set (0.09 sec)
[22 Sep 2006 18:36] 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/12426

ChangeSet@1.2341, 2006-09-22 11:35:45-07:00, acurtis@xiphis.org +5 -0
  Bug#22080
    "CHECK fails to identify some corruption"
    Amended code to recogise corruption as illustrated in bug report.
  Support NULL in CSV
[25 Sep 2006 14:10] Calvin Sun
Sent comment via email.
[25 Sep 2006 16:06] 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/12486

ChangeSet@1.2323, 2006-09-25 09:04:19-07:00, acurtis@xiphis.org +3 -0
  Bug#22080
    "CHECK fails to identify some corruption"
    change to allow it to detect more errors in data format
[3 Oct 2006 20:06] Antony Curtis
approved by Brian via email.
[4 Oct 2006 2:56] Antony Curtis
pushed to mysql-5.1-engines tree
[9 Oct 2006 11:17] Sergey Vojtovich
Fixed in 5.1.12.
[10 Oct 2006 18:43] Paul Dubois
Noted in 5.1.12 changelog.