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

KEY_BLOCK_SIZE strangeness when UNCOMPRESSing COMPRESSed InnoDB tables

    XMLWordPrintable

Details

    • 10.0.30

    Description

      Summary: If you uncompress a compressed InnoDB table, that you originally specified KEY_BLOCK_SIZE, you cannot eliminate KEY_BLOCK_SIZE from the table's structure. (It may indeed be ignored, but you cannot remove it, unles you re-create the table.)

      Say you create the following compressed table:

      CREATE TABLE `t1` (
      `id1` bigint(20) NOT NULL,
      `id2` bigint(20) NOT NULL,
      PRIMARY KEY (`id1`),
      UNIQUE KEY `id2` (`id2`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
      

      ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
      

      This throws a warning:

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

      mysql> SHOW WARNINGS;
      +---------+------+-----------------------------------------------------------------+
      | Level   | Code | Message                                                         |
      +---------+------+-----------------------------------------------------------------+
      | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8 unless ROW_FORMAT=COMPRESSED. |
      +---------+------+-----------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      So now try the following instead to eliminate the warning (start with fresh `t1`):

      ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
      

      It runs fine, with no warnings:

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

      But, now look at the SHOW CREATE TABLE output:

      mysql> SHOW CREATE TABLE `t1`\G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `id1` bigint(20) NOT NULL,
        `id2` bigint(20) NOT NULL,
        PRIMARY KEY (`id1`) KEY_BLOCK_SIZE=8,
        UNIQUE KEY `id2` (`id2`) KEY_BLOCK_SIZE=8
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
      1 row in set (0.00 sec)
      

      This removed KEY_BLOCK_SIZE from the end of the table structure, but instead it now added KEY_BLOCK_SIZE=8 to both KEY definitions.

      So no matter what you do when uncompressing the table, it seems to retain the original KEY_BLOCK_SIZE somewhere, and I cannot seem to eliminate it.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              ccalender Chris Calender (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              6 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.