I’ve just started using the CSV engine for a practical application and I’ve come across a few subtle and unexpected issues/limitations.
First, you can’t create any columns in your CSV table nullable.
mysql> create table t1(i INT) ENGINE=CSV; ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns
RTFM shows this was introduced in 5.1.23. See CSV Limitations
The second and more annoying was creating a CSV table, inserting a sample row (just to check the syntax), then replacing the file ([datadir]/[schema]/[table].CSV) with the same format, but with additional generated rows. This was to no avail when attempting to SELECT from the table.
The solution was to do a REPAIR TABLE [table] in order to see the
newly refreshed data.
Futhermore, some more RTFM shows in …