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

KEY_BLOCK_SIZE strangeness in ALTER TABLE

    XMLWordPrintable

    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

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              serg Sergei Golubchik
              Votes:
              2 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: