[MDEV-11089] Enabling page compression with ALTER TABLE does not compress existing pages - Jira

XML

Word

Printable

Let's say that we create a table that does not use page compression, and we insert some data:

CREATE TABLE `tab` (

  `id` int(11) NOT NULL,

  `str` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO tab VALUES (1, 'str1');

And then let's say that we want to enable page compression for this table:

SET GLOBAL innodb_compression_algorithm=zlib;

ALTER TABLE tab PAGE_COMPRESSED=1;

Many users would expect the table's existing pages to be compressed after this point, but it appears that they are not:

MariaDB [db1]> SHOW GLOBAL STATUS LIKE 'Innodb_num_pages_page_compressed';

+----------------------------------+-------+

| Variable_name                    | Value |

+----------------------------------+-------+

| Innodb_num_pages_page_compressed | 0     |

+----------------------------------+-------+

1 row in set (0.00 sec)

Is this a bug, or a known limitation of page compression? If it's the latter, then it would be good to have that documented:

I do see that new inserts into the table do result in compressed pages:

MariaDB [db1]> INSERT INTO tab VALUES (2, 'str2');

Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> SHOW GLOBAL STATUS LIKE 'Innodb_num_pages_page_compressed';

+----------------------------------+-------+

| Variable_name                    | Value |

+----------------------------------+-------+

| Innodb_num_pages_page_compressed | 3     |

+----------------------------------+-------+

1 row in set (0.00 sec)

relates to

MDEV-11090 Copying table with OPTIMIZE/ALTER TABLE does not compress existing uncompressed pages

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.