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

Failure 1478: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE' upon OPTIMIZE TABLE after Recovery

Details

    Description

      origin/bb-10.6-release 6dd07fbd042132729e5dea0a6a9b35f07a33e9e5 2022-02-03T16:51:08+02:00
       
      Scenario:
      1. Start the server (default storage engine is InnoDB)
      2. On session runs SET INNODB_STRICT_MODE = 0 ;
      3. The same session runs in some roughly endless loop:
           DROP TABLE t1 ; CREATE TABLE t1 (col1 INT) ROW_FORMAT=COMPRESSED ;
      4. During 3. is ongoing the server gets killed (SIGKILL)
      5. Restart with success
      6. CHECK TABLE t1 with success
      7. OPTIMIZE TABLE t1 harvests the following output in RQG
           'test.t1', 'optimize', 'note', 'Table does not support optimize, doing recreate + analyze instead'
           'test.t1', 'optimize', 'error', 'Table storage engine \'InnoDB\' does not support the create option \'ROW_TYPE\''
       
      Why
      - does the OPTIMIZE fail with some create option which is nowhere set?
      - is ROW_TYPE mentioned. I cannot find it in https://mariadb.com/kb/en/create-table
       
      Observations:
      - SHOW CREATE TABLE t1 shows the expected
        CREATE TABLE `t1` (`col1` int(11) DEFAULT NULL)
        ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED'
        after the CHECK TABLE (6. above)
      - No replay of the problem in case of
        - ROW_FORMAT in (COMPACT,REDUNDANT,DYNAMIC)
        or
        - INNODB_STRICT_MODE sticks to the default which is 1
       
      pluto:/data/results/1675431713/TBR-1775B/_RR_TRACE_DIR=./1/rr/ rr replay --mark-stdio
      

      Attachments

        1. RC-TBR-1775B.cfg
          44 kB
          Matthias Leich
        2. RC-TBR-1775-micro.yy
          0.2 kB
          Matthias Leich

        Issue Links

          Activity

            If this is like the case what I analyzed earlier, it should be like this (untested mtr test):

            --source include/have_innodb.inc
            SET innodb_strict_mode=OFF;
            SET GLOBAL innodb_file_per_table=0;
            CREATE TABLE t1(a SERIAL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
            ALTER TABLE t1 FORCE;
            SET innodb_strict_mode=ON;
            --error …
            ALTER TABLE t1 FORCE;
            SET GLOBAL innodb_file_per_table=1;
            ALTER TABLE t1 FORCE;
            DROP TABLE t1;
            

            Due to innodb_file_per_table=0, the ROW_FORMAT=COMPRESSED would be refused by InnoDB but stored in the t1.frm file. The innodb_strict_mode=OFF allows the table to be created in this way. Because the attribute was stored in the t1.frm file, it would be part of the table definition passed to InnoDB in any subquent table-rebuilding ALTER TABLE or OPTIMIZE TABLE. If innodb_file_per_table=0 is still set, then the table would not be moved from the InnoDB system tablespace to a t1.ibd file, and hence the ROW_FORMAT=COMPRESSED cannot be fulfilled. If innodb_strict_mode is enabled (it is by default), this would result in the described error.

            TRUNCATE could be more lenient about this, because we wanted to avoid any change of behavior due to MDEV-13564.

            marko Marko Mäkelä added a comment - If this is like the case what I analyzed earlier, it should be like this (untested mtr test): --source include/have_innodb.inc SET innodb_strict_mode= OFF ; SET GLOBAL innodb_file_per_table=0; CREATE TABLE t1(a SERIAL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; ALTER TABLE t1 FORCE ; SET innodb_strict_mode= ON ; --error … ALTER TABLE t1 FORCE ; SET GLOBAL innodb_file_per_table=1; ALTER TABLE t1 FORCE ; DROP TABLE t1; Due to innodb_file_per_table=0 , the ROW_FORMAT=COMPRESSED would be refused by InnoDB but stored in the t1.frm file. The innodb_strict_mode=OFF allows the table to be created in this way. Because the attribute was stored in the t1.frm file, it would be part of the table definition passed to InnoDB in any subquent table-rebuilding ALTER TABLE or OPTIMIZE TABLE . If innodb_file_per_table=0 is still set, then the table would not be moved from the InnoDB system tablespace to a t1.ibd file, and hence the ROW_FORMAT=COMPRESSED cannot be fulfilled. If innodb_strict_mode is enabled (it is by default), this would result in the described error. TRUNCATE could be more lenient about this, because we wanted to avoid any change of behavior due to MDEV-13564 .

            It indeed is as simple as I assumed. I successfully ran the following test case against 10.5.19:

            --source include/have_innodb.inc
            SET innodb_strict_mode=OFF;
            SET GLOBAL innodb_file_per_table=0;
            CREATE TABLE t1(a SERIAL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
            ALTER TABLE t1 FORCE;
            SET innodb_strict_mode=ON;
            --error ER_ILLEGAL_HA_CREATE_OPTION
            ALTER TABLE t1 FORCE;
            SET GLOBAL innodb_file_per_table=1;
            ALTER TABLE t1 FORCE;
            DROP TABLE t1;
            

            This particular case would be fixed by MDEV-29985. But, something similar should be possible when using innodb_page_size=32k or innodb_page_size=64k:

            --source include/have_innodb.inc
            --source include/innodb_page_size.inc
            SET innodb_strict_mode=OFF;
            CREATE TABLE t1(a SERIAL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
            SET innodb_strict_mode=ON;
            ALTER TABLE t1 FORCE;
            DROP TABLE t1;
            

            mariadb-10.5.19

            mysqltest: At line 7: query 'ALTER TABLE t1 FORCE' failed: 1478: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE'
            

            With the page size combinations 4k, 8k, 16k, the test would pass. This be fixed by MDEV-22367 if it had not been cancelled.

            marko Marko Mäkelä added a comment - It indeed is as simple as I assumed. I successfully ran the following test case against 10.5.19: --source include/have_innodb.inc SET innodb_strict_mode= OFF ; SET GLOBAL innodb_file_per_table=0; CREATE TABLE t1(a SERIAL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; ALTER TABLE t1 FORCE ; SET innodb_strict_mode= ON ; --error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 FORCE ; SET GLOBAL innodb_file_per_table=1; ALTER TABLE t1 FORCE ; DROP TABLE t1; This particular case would be fixed by MDEV-29985 . But, something similar should be possible when using innodb_page_size=32k or innodb_page_size=64k : --source include/have_innodb.inc --source include/innodb_page_size.inc SET innodb_strict_mode= OFF ; CREATE TABLE t1(a SERIAL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; SET innodb_strict_mode= ON ; ALTER TABLE t1 FORCE ; DROP TABLE t1; mariadb-10.5.19 mysqltest: At line 7: query 'ALTER TABLE t1 FORCE' failed: 1478: Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE' With the page size combinations 4k, 8k, 16k, the test would pass. This be fixed by MDEV-22367 if it had not been cancelled.

            I think that we may need to evaluate whether our documentation needs to be clarified in this area.

            marko Marko Mäkelä added a comment - I think that we may need to evaluate whether our documentation needs to be clarified in this area.

            People

              greenman Ian Gilfillan
              mleich Matthias Leich
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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