Bug #32817 though CSV is marked as supported create table is rejected with error 1005.
Submitted: 28 Nov 2007 15:18 Modified: 4 Jan 2008 10:56
Reporter: Horst Hunger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: CSV Severity:S1 (Critical)
Version:5.1.23-rc OS:Linux (suse 10.1)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Triage: D1 (Critical)

[28 Nov 2007 15:18] Horst Hunger
Description:
look at the following dialogue with mysql:

mysql> select * from information_schema.engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| ENGINE     | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA  | SAVEPOINTS |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO  | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO  | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO  | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO  | NO         |
| FEDERATED  | YES     | Federated MySQL storage engine                                 | NO           | NO  | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO  | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO  | NO         |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
8 rows in set (0.00 sec)

mysql> select engine from information_schema.engines where engine='csv';
+--------+
| engine |
+--------+
| CSV    |
+--------+
1 row in set (0.00 sec)

mysql> create table t4 (i int) engine csv;
ERROR 1046 (3D000): No database selected
mysql> use test;
Database changed
mysql> create table t4 (i int) engine csv;
ERROR 1005 (HY000): Can't create table 'test.t4' (errno: -1)

How to repeat:
repeat the sql statements above in mysql.
[28 Nov 2007 15:54] Miguel Solorzano
Thank you for the bug report. Verified as described.
[28 Nov 2007 20:02] Sergey Vojtovich
See also BUG#31473 and BUG#32050. With fix for BUG#31473 it is not possible to create CSV table with nullable field, but the error message is definitly far from perfect.
[30 Nov 2007 12:17] 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/38954

ChangeSet@1.2673, 2007-11-30 16:19:09+04:00, svoj@mysql.com +3 -0
  BUG#32817 - though CSV is marked as supported create table is rejected
              with error 1005.
  
  CSV doesn't support nullable fields. Report a warning if create table
  with nullable field is requested.
[6 Dec 2007 13:39] 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/39409

ChangeSet@1.2677, 2007-12-06 17:40:40+04:00, svoj@mysql.com +3 -0
  BUG#32817 - though CSV is marked as supported create table is rejected
              with error 1005.
  
  CSV doesn't support nullable fields. Report descriptive error if create
  table with nullable field is requested.
[7 Dec 2007 10:43] 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/39510

ChangeSet@1.2677, 2007-12-07 14:44:03+04:00, svoj@mysql.com +3 -0
  BUG#32817 - though CSV is marked as supported create table is rejected
              with error 1005.
  
  CSV doesn't support nullable fields. Report descriptive error if create
  table with nullable field is requested.
[12 Dec 2007 23:00] Bugs System
Pushed into 6.0.5-alpha
[12 Dec 2007 23:02] Bugs System
Pushed into 5.1.23-rc
[4 Jan 2008 10:56] MC Brown
A note has been added to the 6.0.5 and 5.1.23 changelogs: 

Incompatible Change: Inserting a row with a NULL value for a DATETIME column results in a CSV file that the storage engine cannot read.

All CSV tables now need to be defined with each column marked as NOT NULL. An error is raised if you try to create a CSV table with columns that are not defined with NOT NULL.