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

KEY_BLOCK_SIZE strangeness in ALTER TABLE

Details

    Description

      There's a weird asymmetry in changing KEY_BLOCK_SIZE with ALTER TABLE. There is table-level value of KEY_BLOCK_SIZE and index-level values.

      ALTER TABLE t1 KEY_BLOCK_SIZE=10
      

      sets the table-level value and it's automatically copied to index-level values (assuming they weren't set before). Now

      ALTER TABLE t1 KEY_BLOCK_SIZE=0
      

      is supposed to revert the default value of the KEY_BLOCK_SIZE (in particular, SHOW CREATE TABLE shouldn't show KEY_BLOCK_SIZE anymore). But, again, it changes the table-level value and only those index-level values that didn't have values before. And, alas, all index-level KEY_BLOCK_SIZE have values (namely, for all indexes KEY_BLOCK_SIZE=10), so one need to drop and recreate every single index to reset index-level KEY_BLOCK_SIZE values to defaults.

      So, one needs one ALTER TABLE on the table level to set KEY_BLOCK_SIZE, but needs to do it on table level and on index level for every index to remove KEY_BLOCK_SIZE settings. This is unexpected.

      Also, there are engine-specific issues. MyISAM always sets index-level KEY_BLOCK_SIZE values to be the actual MYI index block sizes. InnoDB uses table-level KEY_BLOCK_SIZE value for all indexes, ignoring index-level values.

      Attachments

        Issue Links

          Activity

            jeanfrancois.gagne Jean-François Gagné added a comment - - edited

            I understand this mostly applies to ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; of MDEV-11757.

            However, MDEV-11757 also mentioned below. Shoudn't above just "fail" with InnoDB and below be the right way to uncompress a table (should not produce a warning though) ?

            mysql> ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
            Query OK, 0 rows affected, 1 warning (0.21 sec)
            Records: 0  Duplicates: 0  Warnings: 1
            

            jeanfrancois.gagne Jean-François Gagné added a comment - - edited I understand this mostly applies to ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; of MDEV-11757 . However, MDEV-11757 also mentioned below. Shoudn't above just "fail" with InnoDB and below be the right way to uncompress a table (should not produce a warning though) ? mysql> ALTER TABLE t1 ROW_FORMAT=DYNAMIC; Query OK, 0 rows affected, 1 warning (0.21 sec) Records: 0 Duplicates: 0 Warnings: 1

            I tend to agree with that. There's IGNORE_BAD_TABLE_OPTIONS sql_mode, but it's off by default, so by default one should not end up with a table that has ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8.

            serg Sergei Golubchik added a comment - I tend to agree with that. There's IGNORE_BAD_TABLE_OPTIONS sql_mode, but it's off by default, so by default one should not end up with a table that has ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8 .

            People

              serg Sergei Golubchik
              serg Sergei Golubchik
              Votes:
              2 Vote for this issue
              Watchers:
              5 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.