Description:
If the innodb_strict_mode is disabled, it can lead to inconsistent table definitions being created. A warning will be correctly shown, but the actual table definition will still be stored as the command used.
The warning message mistakenly mentions that the incorrect option will be ignored.
How to repeat:
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.24 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.24 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.12 |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)
-- FIRST, WE DISABLE THE STRICT MODE, AND CREATE A NEW TABLE WITH THE INCORRECT DEFINITION
mysql> set session innodb_strict_mode=0;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test_table (id int primary key) engine = innodb row_format=compact key_block_size=4;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------+
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table test_table\G
*************************** 1. row ***************************
Table: test_table
Create Table: CREATE TABLE `test_table` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=4
1 row in set (0.01 sec)
-- THEN, WE ENABLE IT, AND TRY TO CREATE A COPY FROM IT, AND TRY TO CREATE ANOTHER ONE WITH THE SAME DEFINITION
mysql> set session innodb_strict_mode=1;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test_table2 like test_table;
ERROR 1031 (HY000): Table storage engine for 'test_table2' doesn't have this option
mysql> create table test_table2 (id int primary key) engine = innodb row_format=compact key_block_size=4;
ERROR 1031 (HY000): Table storage engine for 'test_table2' doesn't have this option
-- IF WE DISABLE IT AGAIN, WE CAN SUCCEED
mysql> set session innodb_strict_mode=0;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test_table2 like test_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------+
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table test_table2\G
*************************** 1. row ***************************
Table: test_table2
Create Table: CREATE TABLE `test_table2` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)
Suggested fix:
The warning message suggests that the option KEY_BLOCK_SIZE is ignored, since we are not using compressed row_format (but instead are using compact, which is not compatible with it), but as we can see in the SHOW CREATE outputs, it is not.
Suggested fix is for mysql to really ignore the option, and not include it in the table definition.
Thanks!
Description: If the innodb_strict_mode is disabled, it can lead to inconsistent table definitions being created. A warning will be correctly shown, but the actual table definition will still be stored as the command used. The warning message mistakenly mentions that the incorrect option will be ignored. How to repeat: mysql> show variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.7.24 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.24 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.12 | +-------------------------+------------------------------+ 8 rows in set (0.01 sec) -- FIRST, WE DISABLE THE STRICT MODE, AND CREATE A NEW TABLE WITH THE INCORRECT DEFINITION mysql> set session innodb_strict_mode=0; Query OK, 0 rows affected (0.00 sec) mysql> create table test_table (id int primary key) engine = innodb row_format=compact key_block_size=4; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------+ | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. | +---------+------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table test_table\G *************************** 1. row *************************** Table: test_table Create Table: CREATE TABLE `test_table` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=4 1 row in set (0.01 sec) -- THEN, WE ENABLE IT, AND TRY TO CREATE A COPY FROM IT, AND TRY TO CREATE ANOTHER ONE WITH THE SAME DEFINITION mysql> set session innodb_strict_mode=1; Query OK, 0 rows affected (0.00 sec) mysql> create table test_table2 like test_table; ERROR 1031 (HY000): Table storage engine for 'test_table2' doesn't have this option mysql> create table test_table2 (id int primary key) engine = innodb row_format=compact key_block_size=4; ERROR 1031 (HY000): Table storage engine for 'test_table2' doesn't have this option -- IF WE DISABLE IT AGAIN, WE CAN SUCCEED mysql> set session innodb_strict_mode=0; Query OK, 0 rows affected (0.00 sec) mysql> create table test_table2 like test_table; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------+ | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. | +---------+------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table test_table2\G *************************** 1. row *************************** Table: test_table2 Create Table: CREATE TABLE `test_table2` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=4 1 row in set (0.00 sec) Suggested fix: The warning message suggests that the option KEY_BLOCK_SIZE is ignored, since we are not using compressed row_format (but instead are using compact, which is not compatible with it), but as we can see in the SHOW CREATE outputs, it is not. Suggested fix is for mysql to really ignore the option, and not include it in the table definition. Thanks!