Fun with InnoDB Persistent Statistics

Something interesting happened to me in the last days, and it is worth sharing.  I was upgrading MariaDB (MySQL also impacted) to a new major version and mysql_upgrade showed something like this:

[...]
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1062 (23000) at line 586: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
ERROR 1062 (23000) at line 590: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
ERROR 1062 (23000) at line 593: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
FATAL ERROR: Upgrade failed

The first very frustrating thing from above is that it is hard to find the origin of those errors.  After some investigation, I gathered that this is coming from the mysql.innodb_table_stats table: a long table name, combined with partitioning and truncation, ended up causing "Duplicate entry" errors.  I tried renaming the table with a long name to a shorter one but it did not solve the problem.  To move forward, I ended up truncating the mysql.innodb_table_stats table but that was not satisfactory to me.  I needed to investigate more to fully understand why things failed (and report bugs).

My investigations lead me to run the following query with a surprising result (the schema, table and partition names are modified/shortened, but the length is as reported by the query):

> SELECT database_name, table_name, LENGTH(table_name)
    -> FROM mysql.innodb_table_stats
    -> WHERE database_name = 'schema' AND table_name LIKE 'table_name%'
    -> LIMIT 4;
+---------------+------------------------------+--------------------+
| database_name | table_name                   | length(table_name) |
+---------------+------------------------------+--------------------+
| schema        | table_name#P#partition_name1 |                 70 |
| schema        | table_name#P#partition_name2 |                 70 |
| schema        | table_name#P#partition_name3 |                 71 |
| schema        | table_name#P#partition_name4 |                 71 |
+---------------+------------------------------+--------------------+

The surprising part is that the column table_name of the mysql.innodb_table_stats table is a varchar(64) and we have more than 64 characters in this column !  I reported Bug#86926 for that, thanks Bogdan Kecman for verifying this bug.

Now the problem becomes obvious: mysql_upgrade is running operations on the mysql.innodb_table_stats table and data truncation at 64 characters is causing "Duplicate entry" errors.  However, renaming the table should have solved the problem.  Investigating a little more, I find that renaming a partitioned table does not update the mysql.innodb_table_stats table.  I reported Bug#86927 for that, thanks Sinisa Milivojevic for verifying this bug.

There is more to say about this but I will stop here for now.

UPDATE: the more to say about this is in the following post: A crashing bug in MySQL: the CREATE TABLE of death (more fun with InnoDB Persistent Statistics).