| Bug #94187 | Downgrade minor 5.7 version leaves incorrect innodb_table_stats schema | ||
|---|---|---|---|
| Submitted: | 4 Feb 2019 9:14 | Modified: | 4 Feb 2019 13:12 |
| Reporter: | Przemyslaw Malkowski | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
| Version: | 5.7.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[4 Feb 2019 9:58]
MySQL Verification Team
Hello Przemyslaw, Thank you for the report and feedback. Observed with binary tarballs and in-Place downgrade procedure i.e from https://dev.mysql.com/doc/mysql-installation-excerpt/5.7/en/downgrade-binary-package.html#... regards, Umesh
[4 Feb 2019 13:12]
Przemyslaw Malkowski
Interestingly, with tables with long enough names, which are partitioned, there may be a problem with fixing the table manually with an ALTER:
mysql> alter table `mysql`.`innodb_table_stats` modify `table_name` varchar(64) COLLATE utf8_bin NOT NULL;
ERROR 1406 (22001): Data too long for column 'table_name' at row 5
So, it may be necessary to drop/truncate and re-create/alter both tables `mysql`.`innodb_table_stats` and `mysql`.`innodb_index_stats` later.
However, after downgrading, even in 5.7.21 and varchar(64), automatically filled statistics somehow manage to fit for the long name partitioned tables just fine :)
mysql> show create table mysql.innodb_table_stats\G
*************************** 1. row ***************************
Table: innodb_table_stats
Create Table: CREATE TABLE `innodb_table_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`n_rows` bigint(20) unsigned NOT NULL,
`clustered_index_size` bigint(20) unsigned NOT NULL,
`sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0
1 row in set (0.00 sec)
mysql> select table_name,length(table_name) from mysql.innodb_table_stats;
+-----------------------------------------------------------------------+--------------------+
| table_name | length(table_name) |
+-----------------------------------------------------------------------+--------------------+
| myverylongnametable_myverylongnametable_myverylongnametable_myv | 63 |
| myverylongnametable_myverylongnametable_myverylongnametable_myve#P#p1 | 69 |
| myverylongnametable_myverylongnametable_myverylongnametable_myve#P#p2 | 69 |
| myverylongnametable_myverylongnametable_myverylongnametable_myve#P#p3 | 69 |
| t1 | 2 |
+-----------------------------------------------------------------------+--------------------+
5 rows in set (0.00 sec)
Description: After performing minor 5.7 version downgrade, the mysql_upgrade does not correct the `mysql`.`innodb_table_stats` structure. Tested downgrade from 5.7.24 and 5.7.25 to 5.7.21. The tool reported as if the table was OK: [root@dbc ~]# mysql_upgrade -p*** mysql_upgrade: [Warning] Using a password on the command line interface can be insecure. Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Checking system database. mysql.columns_priv OK mysql.db OK mysql.engine_cost OK mysql.event OK mysql.func OK mysql.general_log OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK The sys schema is already up to date (version 1.5.1). Checking databases. sys.sys_config OK test.t1 OK Upgrade process completed successfully. Checking if update is needed. But the table was not corrected and MySQL keeps complaining about that one: 2019-02-04T08:57:17.588067Z 4 [ERROR] InnoDB: Column table_name in table `mysql`.`innodb_table_stats` is VARCHAR(597) NOT NULL but should be VARCHAR(192) NOT NULL (length mismatch). 2019-02-04T08:57:17.588102Z 4 [ERROR] InnoDB: Fetch of persistent statistics requested for table `sys`.`sys_config` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead. In 5.7.24+, the table is: mysql> SHOW CREATE TABLE `mysql`.`innodb_table_stats`\G *************************** 1. row *************************** Table: innodb_table_stats Create Table: CREATE TABLE `innodb_table_stats` ( `database_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(199) COLLATE utf8_bin NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `n_rows` bigint(20) unsigned NOT NULL, `clustered_index_size` bigint(20) unsigned NOT NULL, `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL, PRIMARY KEY (`database_name`,`table_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 1 row in set (0.00 sec) In 5.7.21, it was: mysql> SHOW CREATE TABLE `mysql`.`innodb_table_stats`\G *************************** 1. row *************************** Table: innodb_table_stats Create Table: CREATE TABLE `innodb_table_stats` ( `database_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `n_rows` bigint(20) unsigned NOT NULL, `clustered_index_size` bigint(20) unsigned NOT NULL, `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL, PRIMARY KEY (`database_name`,`table_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 1 row in set (0.00 sec) Therefore, after downgrade, it needs to be fixed manually, which is not mentioned in the documentation: https://dev.mysql.com/doc/refman/5.7/en/downgrading-to-previous-series.html How to repeat: # yum install mysql-community-server.x86_64 # systemctl start mysqld # mysql -p (change password) # yum downgrade mysql-community-server-5.7.21 mysql-community-common-5.7.21 mysql-community-client-5.7.21 mysql-community-libs-5.7.21 # mysql_upgrade -p mysql> create database test; mysql> create table test.t1 (id int); # tail /var/log/mysqld.log Suggested fix: Make sure the mysql_upgrade tool will check and correct every system table, so that downgrade path works as expected. For old versions where it is not the case, update the downgrade notes accordingly what needs to be done manually.