[MDEV-5867] ALTER TABLE t1 ENGINE=InnoDB keeps bad options when t1 ENGINE is CONNECT Created: 2014-03-14  Updated: 2014-07-08  Resolved: 2014-07-08

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.7
Fix Version/s: 10.0.13

Type: Bug Priority: Major
Reporter: Gerardo Narvaja (Inactive) Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
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.



 Comments   
Comment by Sergei Golubchik [ 2014-03-17 ]

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.

Comment by Sergei Golubchik [ 2014-07-04 ]

On the other hand, doing that in mysqldump will make dumps incompatible with MySQL

Comment by Sergei Golubchik [ 2014-07-04 ]

Another idea — do not show unsupported options in SHOW CREATE TABLE unless IGNORE_BAD_TABLE_OPTIONS is set.

Comment by Sergei Golubchik [ 2014-07-06 ]

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.

Comment by Sergei Golubchik [ 2014-07-08 ]

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).

Comment by Sergei Golubchik [ 2014-07-08 ]

Asked on maria-developers, users seem to prefer the last option with the comments. I'll do that.

Generated at Thu Feb 08 07:07:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.