Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.7
-
None
-
None
-
$ 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.
Attachments
Issue Links
Activity
| Field | Original Value | New Value |
|---|---|---|
| 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. |
Start with the following CONNECT table definition: {code:sql} 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; {code} Now convert to InnoDB: ALTER TABLE test_alter ENGINE=InnoDB; The resulting table definition looks like: {code:sql} 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; {code} 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. |
| Fix Version/s | 10.0.11 [ 15200 ] |
| Fix Version/s | 10.0.12 [ 15201 ] | |
| Fix Version/s | 10.0.11 [ 15200 ] |
| Assignee | Sergei Golubchik [ serg ] |
| Workflow | defaullt [ 36814 ] | MariaDB v2 [ 43741 ] |
| Fix Version/s | 10.0.13 [ 16000 ] | |
| Fix Version/s | 10.0.12 [ 15201 ] |
| Fix Version/s | 10.0.13 [ 16300 ] | |
| Fix Version/s | 10.0 [ 16000 ] |
On the other hand, doing that in mysqldump will make dumps incompatible with MySQL
| Status | Open [ 1 ] | In Progress [ 3 ] |
Another idea — do not show unsupported options in SHOW CREATE TABLE unless IGNORE_BAD_TABLE_OPTIONS is set.
svoj, 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.
| Assignee | Sergei Golubchik [ serg ] | Sergey Vojtovich [ svoj ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Remote Link | This issue links to "Rev 4274: small cleanup of the SHOW CREATE TABLE code (Web Link)" [ 18300 ] |
| Remote Link |
This issue links to "Rev 4275: |
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).
| Assignee | Sergey Vojtovich [ svoj ] | Sergei Golubchik [ serg ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
Asked on maria-developers, users seem to prefer the last option with the comments. I'll do that.
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v2 [ 43741 ] | MariaDB v3 [ 64248 ] |
| Workflow | MariaDB v3 [ 64248 ] | MariaDB v4 [ 147638 ] |
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.