Bug #93069 innodb_strict_mode disabled leads to incorrect table definition
Submitted: 2 Nov 2018 20:14 Modified: 3 Nov 2018 5:03
Reporter: Agustín G Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7.24 OS:Any
Assigned to: CPU Architecture:Any

[2 Nov 2018 20:14] Agustín G
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!
[2 Nov 2018 20:20] Agustín G
I have found a previous bug that mentions this at https://bugs.mysql.com/bug.php?id=56628:

"""
Likewise, if there is a natural conflict between CREATE_OPTIONS like; ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=4,  the 'ignored' warning should happen and the ignored setting should be erased from the CREATE_OPTIONS.  Do you agree?
"""

but it seems it has been overlooked in the actual implementation.
[3 Nov 2018 5:03] Umesh Shastry
Hello Agustín,

Thank you for the report.
Verified as described with 5.7.24.

thanks,
Umesh