MySQL Bugs: #86927: Renaming a partitioned table does not update mysql.innodb_table

Bug #86927	Renaming a partitioned table does not update mysql.innodb_table_stats.
Submitted:	4 Jul 2017 11:46	Modified:	10 Oct 2017 13:46
Reporter:	Jean-François Gagné	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.17-log	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

when renaming a non-partitioned table, mysql.innodb_table_stats is updated.  However, when updating a partitioned table, mysql.innodb_table_stats is not updated.

Many thanks for looking into that,

JFG

How to repeat:
> select version();
+------------+
| version()  |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.00 sec)

> create database test_jfg;
Query OK, 1 row affected (0.03 sec)

> CREATE TABLE test_jfg.test_jfg1 (
    ->   id int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.00 sec)

> CREATE TABLE test_jfg.test_jfg2 (
    ->   id int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
    -> PARTITION BY RANGE ( id ) (
    ->   PARTITION p1000 VALUES LESS THAN (1000) ENGINE = InnoDB,
    ->   PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.01 sec)

> select database_name, table_name from mysql.innodb_table_stats where database_name = 'test_jfg';
+---------------+-------------------+
| database_name | table_name        |
+---------------+-------------------+
| test_jfg      | test_jfg1         |
| test_jfg      | test_jfg2#P#p1000 |
| test_jfg      | test_jfg2#P#pmax  |
+---------------+-------------------+
3 rows in set (0.00 sec)

> rename table test_jfg.test_jfg1 to test_jfg.test_jfg11;
Query OK, 0 rows affected (0.00 sec)

> rename table test_jfg.test_jfg2 to test_jfg.test_jfg12;
Query OK, 0 rows affected (0.00 sec)

> select database_name, table_name from mysql.innodb_table_stats where database_name = 'test_jfg';
+---------------+-------------------+
| database_name | table_name        |
+---------------+-------------------+
| test_jfg      | test_jfg11        |
| test_jfg      | test_jfg2#P#p1000 |
| test_jfg      | test_jfg2#P#pmax  |
+---------------+-------------------+
3 rows in set (0.00 sec)

This might not be a bug. After analysing the table the stats are updated.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.18    |
+-----------+
1 row in set (0.00 sec)

mysql> create database test_jfg;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE test_jfg.test_jfg1 (
    ->   id int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE test_jfg.test_jfg2 (
    ->   id int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
    -> PARTITION BY RANGE ( id ) (
    ->   PARTITION p1000 VALUES LESS THAN (1000) ENGINE = InnoDB,
    ->   PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.05 sec)

mysql> select database_name, table_name from mysql.innodb_table_stats where database_name = 'test_jfg';
+---------------+-------------------+
| database_name | table_name        |
+---------------+-------------------+
| test_jfg      | test_jfg1         |
| test_jfg      | test_jfg2#P#p1000 |
| test_jfg      | test_jfg2#P#pmax  |
+---------------+-------------------+
3 rows in set (0.00 sec)

mysql> rename table test_jfg.test_jfg1 to test_jfg.test_jfg11;
Query OK, 0 rows affected (0.01 sec)

mysql> rename table test_jfg.test_jfg2 to test_jfg.test_jfg12;
Query OK, 0 rows affected (0.01 sec)

mysql> select database_name, table_name from mysql.innodb_table_stats where database_name = 'test_jfg';
+---------------+-------------------+
| database_name | table_name        |
+---------------+-------------------+
| test_jfg      | test_jfg11        |
| test_jfg      | test_jfg2#P#p1000 |
| test_jfg      | test_jfg2#P#pmax  |
+---------------+-------------------+
3 rows in set (0.00 sec)

mysql> analyze table test_jfg.test_jfg11
    -> ;
+---------------------+---------+----------+----------+
| Table               | Op      | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| test_jfg.test_jfg11 | analyze | status   | OK       |
+---------------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> analyze table test_jfg.test_jfg12
    -> ;
+---------------------+---------+----------+----------+
| Table               | Op      | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| test_jfg.test_jfg12 | analyze | status   | OK       |
+---------------------+---------+----------+----------+
1 row in set (0.03 sec)

mysql> select database_name, table_name from mysql.innodb_table_stats where database_name = 'test_jfg';
+---------------+--------------------+
| database_name | table_name         |
+---------------+--------------------+
| test_jfg      | test_jfg11         |
| test_jfg      | test_jfg12#P#p1000 |
| test_jfg      | test_jfg12#P#pmax  |
| test_jfg      | test_jfg2#P#p1000  |
| test_jfg      | test_jfg2#P#pmax   |
+---------------+--------------------+
5 rows in set (0.00 sec)

However the old table_name doesn't seem to be removed, which looks wrong to me.

Hi!

I managed to repeat the bug:

atabase_name	table_name
test_jfg	test_jfg1
test_jfg	test_jfg2#P#p1000
test_jfg	test_jfg2#P#pmax
database_name	table_name
test_jfg	test_jfg11
test_jfg	test_jfg2#P#p1000
test_jfg	test_jfg2#P#pmax

and it does look like a bug to me.

Posted by developer:
 
Fixed as of the upcoming 5.7.21, 8.0.4, 9.0.0 release, and here's the changelog entry:

 The innodb_table_stats data dictionary table was not updated with new
partition names when renaming a partitioned table.