Wednesday, July 5, 2017

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 bus.

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

No comments:

Post a Comment