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

ROW_FORMAT column in I_S.INNODB_SYS_TABLESPACES is NULL for innodb_checksum_algorithm=full_crc32 tablespaces

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
    • N/A
    • None

    Description

      ROW_FORMAT column is NULL in information_schema.innodb_sys_tablespaces starting from 10.6. Should be documented

      I think it is related to MDEV-22343

      "Remove SYS_TABLESPACES and SYS_DATAFILES"
      "For compatibility with older versions, we might want to preserve the INFORMATION_SCHEMA views, but make them reflect the fil_system cache."

      It should be documented, that the value of column ROW_FORMAT in information_schema.innodb_sys_tablespaces is always "NULL" in 10.6

      MariaDB [d1]> create table t1 (id int);
      Query OK, 0 rows affected (0.061 sec)
       
      MariaDB [d1]> select * from information_schema.innodb_sys_tablespaces where name = "d1/t1";
      +-------+-------+------+------------+-----------+-------------+---------------+-----------+----------------+
      | SPACE | NAME  | FLAG | ROW_FORMAT | PAGE_SIZE | FILENAME    | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
      +-------+-------+------+------------+-----------+-------------+---------------+-----------+----------------+
      |    20 | d1/t1 |   21 |       NULL |     16384 | ./d1/t1.ibd |          4096 |     65536 |          65536 |
      +-------+-------+------+------------+-----------+-------------+---------------+-----------+----------------+
      1 row in set (0.002 sec) 
      

      Attachments

        Issue Links

          Activity

            what do you mean "ROW_FORMAT in NULL" and "ROW_FORMAT is every NULL" ?

            serg Sergei Golubchik added a comment - what do you mean "ROW_FORMAT in NULL" and "ROW_FORMAT is every NULL" ?

            serg

            I changed the text to make it more clearly.

            Richard Richard Stracke added a comment - serg I changed the text to make it more clearly.

            MDEV-22343 did not change anything in this regard. The introduction of MDEV-12026 innodb_checksum_algorithm=full_crc32 did.

            The original motivation for encoding ROW_FORMAT in the tablespace flags was to avoid trouble when downgrading from the InnoDB Plugin for MySQL 5.1 to the built-in MySQL 5.1 that would not recognize ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED.

            At least starting with MariaDB Server 10.3, we have a better way of detecting the compatibility of a table: opening and examining the clustered index root page (page 3 in the .ibd file). MariaDB Server 10.3 would nicely refuse to open a table where MDEV-15562 had been used (and the MDEV-20590 compatibility constraint innodb_instant_alter_column_allowed=add_last was not set).

            I tried the following in MariaDB Server 10.4:

            --source include/have_innodb.inc
            select * from information_schema.innodb_sys_tablespaces;
            

            The ROW_FORMAT of the system tables (in the mysql schema) will be reported as either Dynamic or NULL depending on how I invoke the test:

            ./mtr --mysqld=--innodb-sys-tablespaces MDEV-32230
            ./mtr --mysqld=--innodb-sys-tablespaces --mysqld=--innodb-checksum-algorithm=full_crc32 MDEV-32230
            

            MDEV-19534 made innodb_checksum_algorithm=full_crc32 the default in MariaDB Server 10.5. In the MariaDB Enterprise Server, it is the default already starting with 10.4.

            marko Marko Mäkelä added a comment - MDEV-22343 did not change anything in this regard. The introduction of MDEV-12026 innodb_checksum_algorithm=full_crc32 did. The original motivation for encoding ROW_FORMAT in the tablespace flags was to avoid trouble when downgrading from the InnoDB Plugin for MySQL 5.1 to the built-in MySQL 5.1 that would not recognize ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED . At least starting with MariaDB Server 10.3, we have a better way of detecting the compatibility of a table: opening and examining the clustered index root page (page 3 in the .ibd file). MariaDB Server 10.3 would nicely refuse to open a table where MDEV-15562 had been used (and the MDEV-20590 compatibility constraint innodb_instant_alter_column_allowed=add_last was not set). I tried the following in MariaDB Server 10.4: --source include/have_innodb.inc select * from information_schema.innodb_sys_tablespaces; The ROW_FORMAT of the system tables (in the mysql schema) will be reported as either Dynamic or NULL depending on how I invoke the test: ./mtr --mysqld=--innodb-sys-tablespaces MDEV-32230 ./mtr --mysqld=--innodb-sys-tablespaces --mysqld=--innodb-checksum-algorithm=full_crc32 MDEV-32230 MDEV-19534 made innodb_checksum_algorithm=full_crc32 the default in MariaDB Server 10.5. In the MariaDB Enterprise Server, it is the default already starting with 10.4.
            greenman Ian Gilfillan added a comment -

            ROW_FORMAT is not always NULL in 10.6:

            On 10.6.15:

            SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;
            +----------+----------------------------+------+--------+-------+------------+---------------+------------+
            | TABLE_ID | NAME                       | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
            +----------+----------------------------+------+--------+-------+------------+---------------+------------+
            |       18 | test/t1                    |   33 |      4 |     5 | Dynamic    |             0 | Single     |
            |       20 | test/t2                    |    1 |      5 |     7 | Compact    |             0 | Single     |
            |       21 | test/t3                    |    0 |      5 |     8 | Redundant  |             0 | Single     |
            |       22 | test/t4                    |   41 |      5 |     9 | Compressed |          8192 | Single     |
            +----------+----------------------------+------+--------+-------+------------+---------------+------------+
            

            The documentation on https://mariadb.com/kb/en/information-schema-innodb_sys_tables-table/ appears to be correct, so I'm unclear what needs to be updated.

            greenman Ian Gilfillan added a comment - ROW_FORMAT is not always NULL in 10.6: On 10.6.15: SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES; +----------+----------------------------+------+--------+-------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+----------------------------+------+--------+-------+------------+---------------+------------+ | 18 | test/t1 | 33 | 4 | 5 | Dynamic | 0 | Single | | 20 | test/t2 | 1 | 5 | 7 | Compact | 0 | Single | | 21 | test/t3 | 0 | 5 | 8 | Redundant | 0 | Single | | 22 | test/t4 | 41 | 5 | 9 | Compressed | 8192 | Single | +----------+----------------------------+------+--------+-------+------------+---------------+------------+ The documentation on https://mariadb.com/kb/en/information-schema-innodb_sys_tables-table/ appears to be correct, so I'm unclear what needs to be updated.

            The innodb_checksum_algorithm=full_crc32 is not available for ROW_FORMAT=COMPRESSED tables. At the time we implemented the full_crc32 format, I did not want to introduce a new ROW_FORMAT=COMPRESSED file format.

            Those tables originally used the zlib Adler32, which was working fine. I just realized that the current default (which is the only option after MDEV-25105) made things less secure, by making the checksum a XOR of 3 CRC-32 checksum. A single checksum over the entire page would be faster and better.

            marko Marko Mäkelä added a comment - The innodb_checksum_algorithm=full_crc32 is not available for ROW_FORMAT=COMPRESSED tables. At the time we implemented the full_crc32 format, I did not want to introduce a new ROW_FORMAT=COMPRESSED file format. Those tables originally used the zlib Adler32, which was working fine. I just realized that the current default (which is the only option after MDEV-25105 ) made things less secure, by making the checksum a XOR of 3 CRC-32 checksum. A single checksum over the entire page would be faster and better.

            greenman, this bug is about INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES and not INFORMATION_SCHEMA.INNODB_SYS_TABLES. The last 3 columns are kind of "virtual", probably derived from FLAG, except SPACE_TYPE, which is derived from SPACE.

            marko Marko Mäkelä added a comment - greenman , this bug is about INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES and not INFORMATION_SCHEMA.INNODB_SYS_TABLES . The last 3 columns are kind of "virtual", probably derived from FLAG , except SPACE_TYPE , which is derived from SPACE .

            People

              greenman Ian Gilfillan
              Richard Richard Stracke
              Votes:
              0 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.