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

ALTER TABLE does not remove KEY_BLOCK_SIZE for non-Compressed InnoDB tables

    XMLWordPrintable

Details

    Description

      For InnoDB tables, the KEY_BLOCK_SIZE table option is only used for the Compressed row format. The Compressed row format is read-only in 10.6, so many users will probably want to convert to other row formats.

      When the KEY_BLOCK_SIZE table option is set for an InnoDB table, it seems a bit difficult to get rid of.

      Let's say that we start with this table:

      CREATE TABLE innodb_test (id int PRIMARY KEY) KEY_BLOCK_SIZE=4;
      

      You cannot set KEY_BLOCK_SIZE=DEFAULT at the same time you try to change the row format:

      MariaDB [test]> ALTER TABLE innodb_test KEY_BLOCK_SIZE=DEFAULT, ROW_FORMAT=DYNAMIC;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DEFAULT, ROW_FORMAT=DYNAMIC' at line 1
      

      You can almost set KEY_BLOCK_SIZE=0 at the same time you try to change the row format:

      MariaDB [test]> ALTER TABLE innodb_test KEY_BLOCK_SIZE=0, ROW_FORMAT=DYNAMIC;
      Query OK, 0 rows affected (0.027 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      

      But for some weird reason, that seems to move the original KEY_BLOCK_SIZE option to the PK:

      MariaDB [test]> SHOW CREATE TABLE innodb_test\G
      *************************** 1. row ***************************
             Table: innodb_test
      Create Table: CREATE TABLE `innodb_test` (
        `id` int(11) NOT NULL,
        PRIMARY KEY (`id`) KEY_BLOCK_SIZE=4
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
      1 row in set (0.000 sec)
      

      Shouldn't there be a more reliable method to remove the KEY_BLOCK_SIZE option from an InnoDB table?

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 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.