[MDEV-19523] Document values of FLAG column in I_S.innodb_sys_tables and I_S.innodb_sys_tablespaces properly Created: 2019-05-19  Updated: 2019-05-26  Resolved: 2019-05-20

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Information Schema, Storage Engine - InnoDB
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Valerii Kravchuk Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: None


 Description   

Our KB article, https://mariadb.com/kb/en/library/information-schema-innodb_sys_tables-table/, says the following about the FLAG column:

"0 (InnoDB system table) or 1 (user table)."

This is not true, as I can easily see values 0, 33 and 41 also for user tables:

MariaDB [test2]> select * from information_schema.innodb_sys_tables where name like '%ti%';
 
+----------+----------------------------+------+--------+-------+------------+---------------+------------+
 
| TABLE_ID | NAME                       | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
 
+----------+----------------------------+------+--------+-------+------------+---------------+------------+
 
|       19 | mysql/gtid_slave_pos       |   33 |      7 |     4 | Dynamic    |             0 | Single     |
 
|       18 | mysql/transaction_registry |   33 |      8 |     3 | Dynamic    |             0 | Single     |
 
|       21 | test/ti1                   |    0 |      5 |     6 | Redundant  |             0 | Single     |
 
|       22 | test/ti2                   |    1 |      5 |     7 | Compact    |             0 | Single     |
 
|       23 | test/ti3                   |   33 |      5 |     8 | Dynamic    |             0 | Single     |
 
|       24 | test/ti4                   |   41 |      5 |     9 | Compressed |          8192 | Single     |
 
|       25 | test/ti5                   |   33 |      5 |    10 | Dynamic    |             0 | Single     |
 
|       28 | test2/ti0                  |    1 |      5 |    11 | Compact    |             0 | Single     |
 
+----------+----------------------------+------+--------+-------+------------+---------------+------------+
 
8 rows in set (0.000 sec)

Upstream manual is a bit more correct in explanation of flags, but also lacks specific values explained.

 Comments   
Comment by Marko Mäkelä [ 2019-05-20 ]

First of all, the table flags are not the same as tablespace flags (FSP_SPACE_FLAGS). There are some MariaDB specific changes to both.

Also, the dict_table_t::flags do not fully correspond to SYS_TABLES.TYPE, and INNODB_SYS_TABLES.FLAG is actually not reporting SYS_TABLES.TYPE, but dict_table_t::flags that corresponds to the data dictionary record.

Bit 0 (the least significant) bit is set if ROW_FORMAT is not REDUNDANT.

Bits 1‥4 are 0, except for ROW_FORMAT=COMPRESSED, where they will determine the KEY_BLOCK_SIZE (the compressed page size).

Bit 5 is set for ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED.

Bit 6 is set if the DATA DIRECTORY attribute was present when the table was originally created.

Bit 7 is set if the page_compressed attribute is present.

Bits 8‥11 determine the page_compression_level.

Bits 12‥13 are normally 00, but 11 for "no-rollback tables" (MariaDB 10.3 CREATE SEQUENCE). In MariaDB 10.1, these bits could be 01 or 10 for ATOMIC_WRITES=ON or ATOMIC_WRITES=OFF.

Please check the validity of these claims with some examples.

Comment by Valerii Kravchuk [ 2019-05-26 ]

May I ask where Bit 0 (the least significant) comes from, exactly, on disk? It does not seem to be set in the .ibd file for the table with row_format=Compact (see also INNODB_SYS_TABLESPACES), so where exactly something is stored (on disk)?

Also, what about this KB page, https://mariadb.com/kb/en/library/information-schema-innodb_sys_tablespaces-table/, that still says wrong thing:

"FLAG 1 if a DATA DIRECTORY option has been specified in CREATE TABLE, otherwise 0."

The above is surely incorrect:

MariaDB [test]> select * from information_schema.innodb_sys_tablespaces where name like 'test/ti%';
 
+-------+----------+------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+
 
| SPACE | NAME     | FLAG | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
 
+-------+----------+------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+
 
|    55 | test/ti1 |    0 | Compact or Redundant |     16384 |         16384 | Single     |          4096 |     98304 |          98304 |
 
|    56 | test/ti2 |    0 | Compact or Redundant |     16384 |         16384 | Single     |          4096 |     98304 |          98304 |
 
|    57 | test/ti3 |   33 | Dynamic              |     16384 |         16384 | Single     |          4096 |     98304 |          98304 |
 
|    58 | test/ti4 |   41 | Compressed           |     16384 |          8192 | Single     |          4096 |     65536 |          65536 |
 
|    59 | test/ti5 |   33 | Dynamic              |     16384 |         16384 | Single     |          4096 |     98304 |          98304 |
 
+-------+----------+------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+
 
5 rows in set (0,001 sec)
 
 
 
MariaDB [test]> show create table test.ti5\G
 
*************************** 1. row ***************************
 
       Table: ti5
 
Create Table: CREATE TABLE `ti5` (
 
  `id` int(11) NOT NULL,
 
  `c1` int(11) DEFAULT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
1 row in set (0,001 sec)

Generated at Thu Feb 08 08:52:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.