| Bug #31473 | CSV engine does not work with NULL value in datetime fields | ||
|---|---|---|---|
| Submitted: | 9 Oct 2007 11:03 | Modified: | 23 Oct 2007 2:36 |
| Reporter: | Cyril SCETBON | ||
| Status: | Closed | ||
| Category: | Server: CSV | Severity: | S2 (Serious) |
| Version: | 5.1.22 | OS: | Any |
| Assigned to: | Antony Curtis | Target Version: | 5.1.23 |
[9 Oct 2007 11:03]
Cyril SCETBON
[9 Oct 2007 12:46]
Sveta Smirnova
Thank you for the report. Verified as described.
[15 Oct 2007 23: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 23:37]
Calvin Sun
please upload the file Brian asked. Thanks!
[16 Oct 2007 1:24]
Sveta Smirnova
test case
Attachment: bug31473.test (application/octet-stream, text), 110 bytes.
[16 Oct 2007 1:26]
Sveta Smirnova
csm
Attachment: toto.CSM (application/octet-stream, text), 35 bytes.
[16 Oct 2007 1:27]
Brian Aker
Hi! What we are looking for is the .CSV file which contains the table. Cheers, -Brian
[16 Oct 2007 1:27]
Sveta Smirnova
csv
Attachment: toto.CSV (text/plain), 4 bytes.
[16 Oct 2007 1:27]
Sveta Smirnova
frm
Attachment: toto.frm (application/octet-stream, text), 8.37 KiB.
[16 Oct 2007 1: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
[16 Oct 2007 1: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 22: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 23: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 4: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 8: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 23: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 23: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.
[19 Oct 2007 0:16]
Antony Curtis
queued to 5.1-engines and 6.0-engines repositories
[22 Oct 2007 19:03]
Bugs System
Pushed into 5.1.23-beta
[23 Oct 2007 2:36]
Paul DuBois
Noted in 5.1.23 changelog.
