Bug #31473 CSV engine does not work with NULL value in datetime fields
Submitted: 9 Oct 2007 9:03 Modified: 23 Oct 2007 0:36
Reporter: Cyril SCETBON Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: CSV Severity:S2 (Serious)
Version:5.1.22 OS:Any
Assigned to: Antony Curtis CPU Architecture:Any

[9 Oct 2007 9:03] Cyril SCETBON 
Description:
when inserting a null value in a datetime field the table is marked as crashed

How to repeat:
mysql> create table toto(thedate datetime default null) engine=CSV;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into toto values();
Query OK, 1 row affected (0.07 sec)

mysql> select * from toto;
ERROR 1194 (HY000): Table 'toto' is marked as crashed and should be repaired

in 5.1.20 version the null value was changed to 0000-00-00 00:00:00 and warnings appeared, see below :

mysql> select * from toto;
+---------------------+
| thedate             |
+---------------------+
| 0000-00-00 00:00:00 | 
+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0' for column 'thedate' at row 0 | 
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)
[9 Oct 2007 10:46] Sveta Smirnova 
Thank you for the report.

Verified as described.
[15 Oct 2007 21:28] Brian Aker 
Interesting problem. CSV does not support nulls (it converts them to zero values). 

From encode_quote():
    if ((*field)->is_null())
    {
      buffer.append(STRING_WITH_LEN("\"0\","));
      continue;
    }

Keep in mind, that with a CSV file, there is no possible way to store a NULL, because the format won't support it. 

Now, why would this create a crashed file?  My suggestion is to upload the .csv file that was created and from there we can see what the file looked like. CRASH should only happen if an error reading the file occurred or if somehow the file was not shutdown correctly and the meta file was left in a crashed state.
[15 Oct 2007 21:37] Calvin Sun 
please upload the file Brian asked. Thanks!
[15 Oct 2007 23:24] Sveta Smirnova 
test case

Attachment: bug31473.test (application/octet-stream, text), 110 bytes.
[15 Oct 2007 23:26] Sveta Smirnova 
csm

Attachment: toto.CSM (application/octet-stream, text), 35 bytes.
[15 Oct 2007 23:27] Brian Aker 
Hi!

What we are looking for is the .CSV file which contains the table.

Cheers,
   -Brian
[15 Oct 2007 23:27] Sveta Smirnova 
csv

Attachment: toto.CSV (text/plain), 4 bytes.
[15 Oct 2007 23:27] Sveta Smirnova 
frm

Attachment: toto.frm (application/octet-stream, text), 8.37 KiB.
[15 Oct 2007 23:29] Sveta Smirnova 
CSV file has been attached. Please run the test. There is interesting situation: when you run

create table toto(thedate datetime default null) engine=CSV;

insert into toto values();

attached table is created.

After running "select * from toto;" there is no table in the data dir
[15 Oct 2007 23:36] Brian Aker 
Just what I suspect:

1) Either the code for updating a timestamp in write_row() is not being called, or... more likely...

2) When value is set by timestamp update code, null flag is not reset on field.

Lame, any store on a field ought to set the null flag off.
[16 Oct 2007 20:37] 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/35714

ChangeSet@1.2574, 2007-10-16 13:37:16-07:00, antony@pcg5ppc.xiphis.org +3 -0
  Bug#31473
    "CSV does not work with NULL value in datetime fields"
    Attempting to insert a row with a NULL value for a DATETIME field
    results in a CSV file which the storage engine cannot read.
    Don't blindly assume that "0" is acceptable for all field types,
    Since CSV does not support NULL, we find out from the field the
    default non-null value.
[16 Oct 2007 21:30] Brian Aker 
Please extend the test case to cover another type of default. An example would be say a test with a bad ENUM insert, and another with an insert of NULL into a varchar and an int.

After that, should be good.
[17 Oct 2007 2:30] 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/35719

ChangeSet@1.2574, 2007-10-16 19:30:15-07:00, antony@pcg5ppc.xiphis.org +3 -0
  Bug#31473
    "CSV does not work with NULL value in datetime fields"
    Attempting to insert a row with a NULL value for a DATETIME field
    results in a CSV file which the storage engine cannot read.
    Don't blindly assume that "0" is acceptable for all field types,
    Since CSV does not support NULL, we find out from the field the
    default non-null value.
    Handle ENUM as a special case because Field_enum::val_str() can
    emit a value which Field_enum::store() will flag as erronous
[18 Oct 2007 6:04] 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/35812

ChangeSet@1.2574, 2007-10-17 23:03:43-07:00, antony@pcg5ppc.xiphis.org +3 -0
  Bug#31473
    "CSV does not work with NULL value in datetime fields"
    Attempting to insert a row with a NULL value for a DATETIME field
    results in a CSV file which the storage engine cannot read.
    Don't blindly assume that "0" is acceptable for all field types,
    Since CSV does not support NULL, we find out from the field the
    default non-null value.
    Do not permit the creation of a table with a nullable ENUM.
[18 Oct 2007 21:00] 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/35869

ChangeSet@1.2574, 2007-10-18 13:59:48-07:00, antony@pcg5ppc.xiphis.org +7 -0
  Bug#31473
    "CSV does not work with NULL value in datetime fields"
    Attempting to insert a row with a NULL value for a DATETIME field
    results in a CSV file which the storage engine cannot read.
    Don't blindly assume that "0" is acceptable for all field types,
    Since CSV does not support NULL, we find out from the field the
    default non-null value.
    Do not permit the creation of a table with a nullable columns.
[18 Oct 2007 21:48] 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/35872

ChangeSet@1.2574, 2007-10-18 14:48:04-07:00, antony@pcg5ppc.xiphis.org +7 -0
  Bug#31473
    "CSV does not work with NULL value in datetime fields"
    Attempting to insert a row with a NULL value for a DATETIME field
    results in a CSV file which the storage engine cannot read.
    Don't blindly assume that "0" is acceptable for all field types,
    Since CSV does not support NULL, we find out from the field the
    default non-null value.
    Do not permit the creation of a table with a nullable columns.
[18 Oct 2007 22:16] Antony Curtis 
queued to 5.1-engines and 6.0-engines repositories
[22 Oct 2007 17:03] Bugs System 
Pushed into 5.1.23-beta
[23 Oct 2007 0:36] Paul DuBois 
Noted in 5.1.23 changelog.