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

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

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12152 KEY_BLOCK_SIZE strangeness in ALTER TABLE

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11757 KEY_BLOCK_SIZE strangeness when UNCOMPRESSing COMPRESSed InnoDB tables

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-22367 Remove write support for ROW_FORMAT=COMPRESSED

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-23497 make ROW_FORMAT=COMPRESSED read-only by default

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25652 Add support for SI suffixes in SET statement for configuring size-related variables

          • Major - Major loss of function.
          • Open
          links to

          Web Link Bug #56628 ALTER TABLE .. KEY_BLOCK_SIZE=0 produces untrue warning or unnecessary error

          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.