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

DYNAMIC rather than default row format applied when ROW_FORMAT=FIXED and innodb_strict_mode=0

Details

    Description

      With innodb_strict_mode set to OFF, and innodb_default_row_format set to COMPACT, when creating a table and specifying ROW_FORMAT=FIXED, the table is created with the DYNAMIC row format instead.

      SET innodb_strict_mode=0;
      SET GLOBAL innodb_default_row_format='COMPACT'
       
      CREATE TABLE `__syssequences` (
         `systemName` CHAR(50) NOT NULL,
         `sequenceName` CHAR(50) NOT NULL,
         `sequenceStep` INT(11) DEFAULT '1',
         `lastValue` INT(11) DEFAULT '1',
         `lastInsert` DATETIME DEFAULT '0000-00-00 00:00:00',
          `lastUpdate` DATETIME DEFAULT '0000-00-00 00:00:00',
          PRIMARY KEY (`systemName`,`sequenceName`)
        ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
       
      SHOW TABLE STATUS LIKE '__syssequences'\G
      *************************** 1. row ***************************
                 Name: __syssequences
               Engine: InnoDB
              Version: 10
           Row_format: Dynamic
                 Rows: 0
       Avg_row_length: 0
          Data_length: 16384
      Max_data_length: 0
         Index_length: 0
            Data_free: 0
       Auto_increment: NULL
          Create_time: 2016-11-19 17:58:28
          Update_time: NULL
           Check_time: NULL
            Collation: utf8_general_ci
             Checksum: NULL
       Create_options: row_format=FIXED
              Comment: 
      1 row in set (0.00 sec)
      

      Attachments

        Activity

          Same with MySQL 5.7.
          My guess is it's actually by design, but is wrongly documented in MySQL manual.

          The manual says:

          As of MySQL 5.7.9, the default row format is defined by innodb_default_row_format, which has a default setting of DYNAMIC. The default row format is used when the ROW_FORMAT option is not defined or when ROW_FORMAT=DEFAULT is used.

          That is, it does not cover the case with ROW_FORMAT=FIXED.
          This case is described separately in the manual:

          ROW_FORMAT=FIXED is not supported. If ROW_FORMAT=FIXED is specified while innodb_strict_mode is disabled, InnoDB issues a warning and assumes ROW_FORMAT=COMPACT.

          Of course, it's wrong. I think they forgot to change it when they changed the "favorit" format from COMPACT to DYNAMIC, but the idea probably remains – if ROW_FORMAT=FIXED is specified, it's not the default that is applied, but a specific replacement – COMPACT in 5.6, DYNAMIC in 5.7.

          Of course, it's me reading between the lines, so I'll assign it to jplindst for the expert opinion.

          elenst Elena Stepanova added a comment - Same with MySQL 5.7. My guess is it's actually by design, but is wrongly documented in MySQL manual. The manual says: As of MySQL 5.7.9, the default row format is defined by innodb_default_row_format, which has a default setting of DYNAMIC. The default row format is used when the ROW_FORMAT option is not defined or when ROW_FORMAT=DEFAULT is used. That is, it does not cover the case with ROW_FORMAT=FIXED . This case is described separately in the manual: ROW_FORMAT=FIXED is not supported. If ROW_FORMAT=FIXED is specified while innodb_strict_mode is disabled, InnoDB issues a warning and assumes ROW_FORMAT=COMPACT. Of course, it's wrong. I think they forgot to change it when they changed the "favorit" format from COMPACT to DYNAMIC, but the idea probably remains – if ROW_FORMAT=FIXED is specified, it's not the default that is applied, but a specific replacement – COMPACT in 5.6, DYNAMIC in 5.7. Of course, it's me reading between the lines, so I'll assign it to jplindst for the expert opinion.

          I think that we should file this as a MySQL documentation bug. The implemented behaviour makes sense.
          There should be no benefit of using ROW_FORMAT=COMPACT instead of ROW_FORMAT=DYNAMIC.
          Full disclosure: I designed and wrote the initial implementation of WL#8307 in MySQL 5.7.

          marko Marko Mäkelä added a comment - I think that we should file this as a MySQL documentation bug. The implemented behaviour makes sense. There should be no benefit of using ROW_FORMAT=COMPACT instead of ROW_FORMAT=DYNAMIC. Full disclosure: I designed and wrote the initial implementation of WL#8307 in MySQL 5.7.

          The upstream documentation issue has been filed as http://bugs.mysql.com/bug.php?id=84006.

          greenman, if you think there is a place to document it on our side as well, please do; otherwise, it can be closed in any way you like.

          elenst Elena Stepanova added a comment - The upstream documentation issue has been filed as http://bugs.mysql.com/bug.php?id=84006 . greenman , if you think there is a place to document it on our side as well, please do; otherwise, it can be closed in any way you like.
          greenman Ian Gilfillan added a comment -

          I have documented this case in detail on the CREATE TABLE page, so will close this issue for now. I still think this behaviour doesn't make sense, as perhaps a user is wanting to use COMPACT for compatibility reasons, and unexpectedly creating a DYNAMIC table, a format they've explicitly moved away from, is odd.

          greenman Ian Gilfillan added a comment - I have documented this case in detail on the CREATE TABLE page, so will close this issue for now. I still think this behaviour doesn't make sense, as perhaps a user is wanting to use COMPACT for compatibility reasons, and unexpectedly creating a DYNAMIC table, a format they've explicitly moved away from, is odd.

          I am the author of ROW_FORMAT=COMPACT in MySQL 5.0.3 and ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED in the InnoDB Plugin for MySQL 5.1.

          Ian Gilfillan, compatibility with what? MySQL versions older than 5.6 do not really support importing *.ibd files. In InnoDB, ROW_FORMAT=DYNAMIC is almost the same as ROW_FORMAT=COMPACT, with the difference that long columns that are stored off-page will be stored completely off-page, without a local 768-byte prefix in the clustered index leaf page. In MySQL 5.1, the InnoDB Plugin introduced support for ROW_FORMAT=DYNAMIC, but the built-in InnoDB does not support it. In MySQL 5.5 (which the earliest versions of MariaDB is based on), ROW_FORMAT=DYNAMIC is supported.

          I do not see much reason for the ROW_FORMAT=COMPACT to exist at all. Maybe some future version of MariaDB could take the step of treating COMPACT and DYNAMIC the same (never store a BLOB prefix locally). This would of course require tagging the *.ibd file in some way that prevents import into earlier versions.

          marko Marko Mäkelä added a comment - I am the author of ROW_FORMAT=COMPACT in MySQL 5.0.3 and ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED in the InnoDB Plugin for MySQL 5.1. Ian Gilfillan, compatibility with what? MySQL versions older than 5.6 do not really support importing *.ibd files. In InnoDB, ROW_FORMAT=DYNAMIC is almost the same as ROW_FORMAT=COMPACT, with the difference that long columns that are stored off-page will be stored completely off-page, without a local 768-byte prefix in the clustered index leaf page. In MySQL 5.1, the InnoDB Plugin introduced support for ROW_FORMAT=DYNAMIC, but the built-in InnoDB does not support it. In MySQL 5.5 (which the earliest versions of MariaDB is based on), ROW_FORMAT=DYNAMIC is supported. I do not see much reason for the ROW_FORMAT=COMPACT to exist at all. Maybe some future version of MariaDB could take the step of treating COMPACT and DYNAMIC the same (never store a BLOB prefix locally). This would of course require tagging the *.ibd file in some way that prevents import into earlier versions.
          greenman Ian Gilfillan added a comment -

          That's more of an argument for doing away with the innodb_default_row_format variable altogether. I don't know if it's ever useful to set it, or whether there can never be any consequences to having an unexpected mix of DYNAMIC and COMPACT/REDUNDANT, but the variable exists, and if someone did set it, they'd probably expect it to apply in this case. But then it's such an extreme edge case (they do all the above, and then mistakenly specify FIXED when creating), as long as it's documented I'm happy.

          greenman Ian Gilfillan added a comment - That's more of an argument for doing away with the innodb_default_row_format variable altogether. I don't know if it's ever useful to set it, or whether there can never be any consequences to having an unexpected mix of DYNAMIC and COMPACT/REDUNDANT, but the variable exists, and if someone did set it, they'd probably expect it to apply in this case. But then it's such an extreme edge case (they do all the above, and then mistakenly specify FIXED when creating), as long as it's documented I'm happy.

          Someone could want to use ROW_FORMAT=REDUNDANT (the original InnoDB format) instead of ROW_FORMAT=DYNAMIC. There are clear differences between those two. ROW_FORMAT=REDUNDANT records can be parsed without having access to the index definition, so using that format may increase data file size and reduce redo log volume and speed up operations.

          I would say that the treatment of ROW_FORMAT=FIXED is a borderline case. In non-strict mode, InnoDB is interpreting an invalid value as some valid value. We can see what upstream does. I would guess that they would treat it as a documentation bug rather than modifying the code in a GA version.

          marko Marko Mäkelä added a comment - Someone could want to use ROW_FORMAT=REDUNDANT (the original InnoDB format) instead of ROW_FORMAT=DYNAMIC. There are clear differences between those two. ROW_FORMAT=REDUNDANT records can be parsed without having access to the index definition, so using that format may increase data file size and reduce redo log volume and speed up operations. I would say that the treatment of ROW_FORMAT=FIXED is a borderline case. In non-strict mode, InnoDB is interpreting an invalid value as some valid value. We can see what upstream does. I would guess that they would treat it as a documentation bug rather than modifying the code in a GA version.

          People

            greenman Ian Gilfillan
            greenman Ian Gilfillan
            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.