Bug #88674 Regression CREATE TBL from 5.7.17 to 20 (part #2: innodb_file_per_table = OFF).

Description:
Hi,

Probably related to Bug#88673 (innodb_file_per_table = ON) but as this is with innodb_file_per_table = OFF, a different code path is used, so this might be a different bug.

Note that the CREATE TABLE in this bug is not the same as in Bug#88673.  The one in Bug#88673 succeeds with innodb_file_per_table = ON but the one in this bug fails with innodb_file_per_table = ON.

I found a regression from MySQL 5.7.17 to 5.7.20: a CREATE TABLE "somehow" working in MySQL 5.7.17 fails in 5.7.20.  See "How to repeat" for the details about this CREATE TABLE.

This looks like a regression in the fix of Bug#86934.  This bug is private but some details can be found in [1].

[1]: https://jfg-mysql.blogspot.com/2017/11/the-create-table-of-death.html

I qualify the 5.7.17 as "somehow" working as the CREATE TABLE succeeds but InnoDB Persistent Statistics are corrupted (probably related to Bug#86926).  See "How to repeat" for details.

Many thanks for looking into that,

JFG

How to repeat:
This sequence of commands ends-up creating a table in MySQL 5.7.17:

> SELECT version();
+------------+
| version()  |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.00 sec)

> CREATE DATABASE test_jfg3;
Query OK, 1 row affected (0.00 sec)

> CREATE TABLE test_jfg3.test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé (
    ->   id int(10) unsigned NOT NULL,
    ->   id2 int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (id, id2)
    -> ) ENGINE=InnoDB PARTITION BY RANGE (id) SUBPARTITION BY HASH (id2) SUBPARTITIONS 2 (
    ->   PARTITION test_jfg_partition_name_with_xx_chars_ççççççççççççççç  VALUES LESS THAN (1000),
    ->   PARTITION pmax VALUES LESS THAN MAXVALUE);
ERROR 1030 (HY000): Got error 168 from storage engine

Oups, above fails with innodb_file_per_table = ON (note that the CREATE table from Bug#88673 succeeds with innodb_file_per_table = ON), let's continue with innodb_file_per_table = OFF:

> SET GLOBAL innodb_file_per_table = OFF;
Query OK, 0 rows affected (0.00 sec)

> CREATE TABLE test_jfg3.test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé (
    ->   id int(10) unsigned NOT NULL,
    ->   id2 int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (id, id2)
    -> ) ENGINE=InnoDB PARTITION BY RANGE (id) SUBPARTITION BY HASH (id2) SUBPARTITIONS 2 (
    ->   PARTITION test_jfg_partition_name_with_xx_chars_ççççççççççççççç  VALUES LESS THAN (1000),
    ->   PARTITION pmax VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.01 sec)

The CREATE TABLE above succeeded in MySQL 5.7.17, however InnoDB Persistent Statistics are corrupted (probably related to Bug#86926:
-four rows expected in query below but only three returned,
-and truncation of the sub-partition name on the third row (should have more than two ç at the end).

> SELECT database_name, table_name, LENGTH(table_name) as len
    -> FROM mysql.innodb_table_stats WHERE database_name = 'test_jfg3';
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| database_name | table_name                                                                                                                                                                                                | len |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| test_jfg3     | test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé#P#pmax#SP#pmaxsp0                                                                                                                        | 102 |
| test_jfg3     | test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé#P#pmax#SP#pmaxsp1                                                                                                                        | 102 |
| test_jfg3     | test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé#P#test_jfg_partition_name_with_xx_chars_ççççççççççççççç#SP#test_jfg_partition_name_with_xx_chars_çç                                      | 201 |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
3 rows in set (0.00 sec)

So above CREATE TABLE "somehow" works in MySQL 5.7.17 but it fails in 5.7.20 as shown below:

> SELECT version();
+------------+
| version()  |
+------------+
| 5.7.20-log |
+------------+
1 row in set (0.00 sec)

> CREATE DATABASE test_jfg3;
Query OK, 1 row affected (0.00 sec)

> CREATE TABLE test_jfg3.test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé (
    ->   id int(10) unsigned NOT NULL,
    ->   id2 int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (id, id2)
    -> ) ENGINE=InnoDB PARTITION BY RANGE (id) SUBPARTITION BY HASH (id2) SUBPARTITIONS 2 (
    ->   PARTITION test_jfg_partition_name_with_xx_chars_ççççççççççççççç  VALUES LESS THAN (1000),
    ->   PARTITION pmax VALUES LESS THAN MAXVALUE);
ERROR 1030 (HY000): Got error 168 from storage engine

> SET GLOBAL innodb_file_per_table = OFF;
Query OK, 0 rows affected (0.00 sec)

> CREATE TABLE test_jfg3.test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé (
    ->   id int(10) unsigned NOT NULL,
    ->   id2 int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (id, id2)
    -> ) ENGINE=InnoDB PARTITION BY RANGE (id) SUBPARTITION BY HASH (id2) SUBPARTITIONS 2 (
    ->   PARTITION test_jfg_partition_name_with_xx_chars_ççççççççççççççç  VALUES LESS THAN (1000),
    ->   PARTITION pmax VALUES LESS THAN MAXVALUE);
ERROR 1680 (HY000): The path specified for test_jfg3/test_jfg_table_name_with_64_chars_1234567890@0p@0p@0p@ is too long.

As with Bug#88673, if I run "DROP DATABASE test_jfg3", I have the following in the error logs:

2017-11-27T20:01:51.135909Z 33 [Warning] InnoDB: Orphan table encountered during DROP DATABASE. This is possible if '`test_jfg3`.`test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé` /* Partition `pmax`, Subpartition `pmaxsp0` */.frm' was lost.
2017-11-27T20:01:51.136959Z 33 [Warning] InnoDB: Orphan table encountered during DROP DATABASE. This is possible if '`test_jfg3`.`test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé` /* Partition `pmax`, Subpartition `pmaxsp1` */.frm' was lost.
2017-11-27T20:01:51.137826Z 33 [Warning] InnoDB: Orphan table encountered during DROP DATABASE. This is possible if '`test_jfg3`.`test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé` /* Partition `test_jfg_partition_name_with_xx_chars_ççççççççççççççç`, Subpartition `test_jfg_partition_name_with_xx_chars_çççççççççççççççsp0` */.frm' was lost.
2017-11-27T20:01:51.138642Z 33 [Warning] InnoDB: Orphan table encountered during DROP DATABASE. This is possible if '`test_jfg3`.`test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé` /* Partition `test_jfg_partition_name_with_xx_chars_ççççççççççççççç`, Subpartition `test_jfg_partition_name_with_xx_chars_çççççççççççççççsp1` */.frm' was lost.

If this is a regression in the fix for Bug#86934, MySQL 5.5.58 and 5.6.38 are probably also affected, but I do not have the resources to test this now.

Hello Jean,

Thank you for the report and feedback.

Thanks,
Umesh