MySQL Bugs: #88673: Regression CREATE TBL from 5.7.17 to 20 (part #1: innodb_file_per

Bug #88673	Regression CREATE TBL from 5.7.17 to 20 (part #1: innodb_file_per_table = ON).
Submitted:	28 Nov 2017 6:11	Modified:	28 Nov 2017 9:30
Reporter:	Jean-François Gagné	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

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 command 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_jfg2;
Query OK, 1 row affected (0.00 sec)

> CREATE TABLE test_jfg2.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_çççççç123456789  VALUES LESS THAN (1000),
    ->   PARTITION pmax VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.20 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 which should ends in "ç123456789", not in "ç1".

> SELECT database_name, table_name, LENGTH(table_name) as len
    -> FROM mysql.innodb_table_stats WHERE database_name = 'test_jfg2';
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| database_name | table_name                                                                                                                                                                                                | len |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| test_jfg2     | test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé#P#pmax#SP#pmaxsp0                                                                                                                        | 102 |
| test_jfg2     | test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé#P#pmax#SP#pmaxsp1                                                                                                                        | 102 |
| test_jfg2     | test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé#P#test_jfg_partition_name_with_xx_chars_çççççç123456789#SP#test_jfg_partition_name_with_xx_chars_çççççç1                                 | 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_jfg2;
Query OK, 1 row affected (0.00 sec)

> CREATE TABLE test_jfg2.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_çççççç123456789  VALUES LESS THAN (1000),
    ->   PARTITION pmax VALUES LESS THAN MAXVALUE);
ERROR 1680 (HY000): The path specified for test_jfg2/test_jfg_table_name_with_64_chars_1234567890@0p@0p@0p@ is too long.

Note that the data on disk looks very strange/corrupted (no frm file in below but the ibd are created even if the CREATE TABLE failed):

# ( cd "$(sed -ne '/^datadir/s/.* //p' /etc/my.cnf)"; ls -1 test_jfg2; )
db.opt
test_jfg_table_name_with_64_chars_1234567890@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p#P#pmax#SP#pmaxsp0.ibd
test_jfg_table_name_with_64_chars_1234567890@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p#P#pmax#SP#pmaxsp1.ibd
test_jfg_table_name_with_64_chars_1234567890@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p#P#test_jfg_partition_name_with_xx_chars_@0n@0n@0n@0n@0n@0n123456789#SP#test_jfg_partition_name_with_xx_chars_@0n@0n@0n@0n@0n@0n123456789sp0.ibd
test_jfg_table_name_with_64_chars_1234567890@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p@0p#P#test_jfg_partition_name_with_xx_chars_@0n@0n@0n@0n@0n@0n123456789#SP#test_jfg_partition_name_with_xx_chars_@0n@0n@0n@0n@0n@0n123456789sp1.ibd

And if I run "DROP DATABASE test_jfg2", I have the following in the error logs:

2017-11-27T19:32:13.722186Z 6 [Warning] InnoDB: Orphan table encountered during DROP DATABASE. This is possible if '`test_jfg2`.`test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé` /* Partition `pmax`, Subpartition `pmaxsp0` */.frm' was lost.
2017-11-27T19:32:13.724240Z 6 [Warning] InnoDB: Orphan table encountered during DROP DATABASE. This is possible if '`test_jfg2`.`test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé` /* Partition `pmax`, Subpartition `pmaxsp1` */.frm' was lost.
2017-11-27T19:32:13.726065Z 6 [Warning] InnoDB: Orphan table encountered during DROP DATABASE. This is possible if '`test_jfg2`.`test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé` /* Partition `test_jfg_partition_name_with_xx_chars_çççççç123456789`, Subpartition `test_jfg_partition_name_with_xx_chars_çççççç123456789sp0` */.frm' was lost.
2017-11-27T19:32:13.727793Z 6 [Warning] InnoDB: Orphan table encountered during DROP DATABASE. This is possible if '`test_jfg2`.`test_jfg_table_name_with_64_chars_1234567890éééééééééééééééééééé` /* Partition `test_jfg_partition_name_with_xx_chars_çççççç123456789`, Subpartition `test_jfg_partition_name_with_xx_chars_çççççç123456789sp1` */.frm' was lost.

Probably related to Bug#88674.  But in Bug#88674, the CREATE TABLE fails with innodb_file_per_table = ON, succeeds with innodb_file_per_table = OFF in 5.7.17 but fails in 5.7.20.

Hello Jean,

Thank you for the report and feedback.

Thanks,
Umesh

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.

-- 5.6.38 seems not affected

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.38: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'innodb_file%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
| innodb_file_per_table    | ON       |
+--------------------------+----------+
4 rows in set (0.00 sec)

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

mysql> CREATE TABLE test_jfg2.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_çççççç123456789  VALUES LESS THAN (1000),
    ->    PARTITION pmax VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.01 sec)

-- with barracuda

mysql> show variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Antelope  |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

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

mysql> CREATE TABLE test_jfg2.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_çççççç123456789  VALUES LESS THAN (1000),
    ->    PARTITION pmax VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.01 sec)