Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5867

ALTER TABLE t1 ENGINE=InnoDB keeps bad options when t1 ENGINE is CONNECT

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.7
    • 10.0.13
    • 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

        Activity

          serg Sergei Golubchik added a comment - - edited

          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.

          serg Sergei Golubchik added a comment - - edited 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.

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

          serg Sergei Golubchik added a comment - On the other hand, doing that in mysqldump will make dumps incompatible with MySQL
          serg Sergei Golubchik added a comment - - edited

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

          serg Sergei Golubchik added a comment - - edited Another idea — do not show unsupported options in SHOW CREATE TABLE unless IGNORE_BAD_TABLE_OPTIONS is set.
          serg Sergei Golubchik added a comment - - edited

          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.

          serg Sergei Golubchik added a comment - - edited 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.

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

          serg Sergei Golubchik added a comment - 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.

          serg Sergei Golubchik added a comment - Asked on maria-developers, users seem to prefer the last option with the comments. I'll do that.

          People

            serg Sergei Golubchik
            gerry Gerardo Narvaja (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.