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.
