MySQL Bugs: #32817: though CSV is marked as supported create table is rejected with error 1005.

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

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.

Thank you for the bug report. Verified as described.

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.

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.

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.

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

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.