Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.7
-
Fix Version/s: 10.0.13
-
Component/s: None
-
Labels:None
-
Environment:$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 12.04.3 LTS
Release: 12.04
Codename: precise
Description
Start with the following CONNECT table definition:
CREATE TABLE `test_alter` ( `ts` date NOT NULL `date_format`='YYYY-MM-DD' `flag`=1, `merchant` varchar(128) NOT NULL `flag`=2, `amount` decimal(10,2) NOT NULL `flag`=3, `category` varchar(65) NOT NULL `flag`=5 ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=csv `file_name`='/var/lib/mysql/Export_v2.csv' `header`=0 `quoted`=1;
Now convert to InnoDB: ALTER TABLE test_alter ENGINE=InnoDB;
The resulting table definition looks like:
CREATE TABLE `test_alter` ( `ts` date NOT NULL `date_format`='YYYY-MM-DD' `flag`=1, `merchant` varchar(128) NOT NULL `flag`=2, `amount` decimal(10,2) NOT NULL `flag`=3, `category` varchar(65) NOT NULL `flag`=5 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 `table_type`=csv `file_name`='/var/lib/mysql/Export_v2.csv' `header`=0 `quoted`=1;
Basic tests show that the new 'test_alter' table is a fully functional InnoDB table. However, the CREATE TABLE statement from the SHOW CREATE TABLE output is unusable and will fail.
It should be possible to change the storage engine and the ALTER TABLE operation should 'clean up' the invalid options.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
On the other hand, doing that in mysqldump will make dumps incompatible with MySQL
Another idea — do not show unsupported options in SHOW CREATE TABLE unless IGNORE_BAD_TABLE_OPTIONS is set.
Sergey Vojtovich, could you please review this fix? Emails are in the commit list and also linked above. The bug fix is in the second, but it relies on the cleanup, that was done in the first patch.
I'm mainly interested to know whether the approach itself is good.
Another option would be to print invalid options inside a comment. Might look confusing though:
CREATE TABLE `test_alter` ( `ts` date NOT NULL /* `date_format`='YYYY-MM-DD' `flag`=1 */, `merchant` varchar(128) NOT NULL /* `flag`=2 */, `amount` decimal(10,2) NOT NULL /* `flag`=3 */, `category` varchar(65) NOT NULL /* `flag`=5 */ ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /* `table_type`=csv `file_name`='/var/lib/mysql/Export_v2.csv' `header`=0 `quoted`=1 /*;
and we don't normally use comments in SHOW CREATE TABLE (yet).
Asked on maria-developers, users seem to prefer the last option with the comments. I'll do that.
This is intentional, generally it allows to alter the engine back and have all options preserved.
But I agree that this behavior is not always desirable.
As a workaround one can set sql_mode=IGNORE_BAD_TABLE_OPTIONS before applying this CREATE TABLE statement. Then it won't fail. Perhaps, mysqldump should always do it in the dumps.