Removing table compression leaves compressed keys

[monty solomon]

Removing the compression from a table leaves KEY_BLOCK_SIZE=8 on each of the keys.

[monty solomon (monty+launchpad)]

mysql> CREATE TABLE `test` (
    -> `id` bigint(20) NOT NULL,
    -> `portal_id` bigint(20) NOT NULL,
    -> PRIMARY KEY (`id`),
    -> UNIQUE KEY `portal_id` (`portal_id`)
    -> ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (1.76 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `portal_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `portal_id` (`portal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)

mysql> alter table test ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
Query OK, 0 rows affected (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `portal_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) KEY_BLOCK_SIZE=8,
  UNIQUE KEY `portal_id` (`portal_id`) KEY_BLOCK_SIZE=8
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

[monty solomon (monty+launchpad)]

Server version: 5.5.31-30.3-log Percona Server (GPL), Release rel30.3, Revision 520

[monty solomon (monty+launchpad)]

innodb_file_format = Barracuda

innodb_file_format_check = 1

innodb_strict_mode = 1

sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

[monty solomon (monty+launchpad)]

Created upstream bug

http://bugs.mysql.com/bug.php?id=70534

[Valerii Kravchuk (valerii-kravchuk)]

This is easy to verify in PS (same as upstream):

[openxs@chief p5.1]$ bin/mysql --no-defaults -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.68 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `test` (
    -> `id` bigint(20) NOT NULL,
    -> `portal_id` bigint(20) NOT NULL,
    -> PRIMARY KEY (`id`),
    -> UNIQUE KEY `portal_id` (`portal_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.20 sec)

mysql> alter table test ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `portal_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) KEY_BLOCK_SIZE=8,
  UNIQUE KEY `portal_id` (`portal_id`) KEY_BLOCK_SIZE=8
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1433