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

Document how page_compression_level and page_compression table options interact for InnoDB

    XMLWordPrintable

Details

    Description

      If the table is having page_compressed=on and page_compression_level=1 - any value from 1-9, are unable to disable the page compression. With just page_compressed=on, we can disable though.

      Here are the worklogs for your reference.
      Observed same behavior with MariaDB 10.4.13.

      MariaDB [test]> select @@version;
      +-----------------+
      | @@version |
      +-----------------+
      | 10.3.23-MariaDB |
      +-----------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> create table tab1 (a int) page_compressed='on';
      Query OK, 0 rows affected (0.025 sec)
       
      MariaDB [test]> create table tab2 (a int) page_compressed='on', page_compression_level=1;
      Query OK, 0 rows affected (0.016 sec)
       
      MariaDB [test]> show create table tab1;
      +-------+----------------------------------------------------------------------------------------------------------------+
      | Table | Create Table |
      +-------+----------------------------------------------------------------------------------------------------------------+
      | tab1 | CREATE TABLE `tab1` (
      `a` int(11) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`='on' |
      +-------+----------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> show create table tab2;
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------+
      | tab2 | CREATE TABLE `tab2` (
      `a` int(11) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`='on' `page_compression_level`=1 |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> insert into tab1 values (1),(1),(1);
      Query OK, 3 rows affected (0.002 sec)
      Records: 3 Duplicates: 0 Warnings: 0
       
      MariaDB [test]> insert into tab2 values (1),(1),(1);
      Query OK, 3 rows affected (0.003 sec)
      Records: 3 Duplicates: 0 Warnings: 0
       
      MariaDB [test]> alter table tab1 page_compressed='off';
      Query OK, 0 rows affected (0.022 sec)
      Records: 0 Duplicates: 0 Warnings: 0
       
      MariaDB [test]> alter table tab2 page_compressed='off';
      ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'PAGE_COMPRESSION_LEVEL'
       
      MariaDB [test]> show create table tab1;
      +-------+-----------------------------------------------------------------------------------------------------------------+
      | Table | Create Table |
      +-------+-----------------------------------------------------------------------------------------------------------------+
      | tab1 | CREATE TABLE `tab1` (
      `a` int(11) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`='off' |
      +-------+-----------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> show create table tab2;
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------+
      | tab2 | CREATE TABLE `tab2` (
      `a` int(11) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`='on' `page_compression_level`=1 |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
      

      Attachments

        Activity

          People

            JoeCotellese Joe Cotellese
            suresh.ramagiri@mariadb.com suresh ramagiri
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.