|
Thanks for the report.
1) Column table_name from the mysql.innodb_table_stats and mysql.innodb_index_stats is a varchar(64).
But with creating a partitioned table, length is longer.
Please see mysql bug 86926 https://bugs.mysql.com/bug.php?id=86926
MariaDB [(none)]> select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'test';
|
+---------------+---------------------------------------------------------------------------------+--------------------+
|
| database_name | table_name | length(table_name) |
|
+---------------+---------------------------------------------------------------------------------+--------------------+
|
| test | extralongname_extralongname_extralongname_extralongname_ext#P#p2007#SP#p2007sp0 | 79 |
|
| test | extralongname_extralongname_extralongname_extralongname_ext#P#p2007#SP#p2007sp1 | 79 |
|
| test | extralongname_extralongname_extralongname_extralongname_ext#P#p2008#SP#p2008sp0 | 79 |
|
| test | extralongname_extralongname_extralongname_extralongname_ext#P#p2008#SP#p2008sp1 | 79 |
|
+---------------+---------------------------------------------------------------------------------+--------------------+
|
4 rows in set (0.00 sec)
|
2) if we upgrade from 10.0.19 to 10.1.24 and use mysql_upgrade, it fails. But no errors when upgrading from 10.19 to 10.1.19, 10.1.23
Phase 4/7: Running 'mysql_fix_privilege_tables'
|
ERROR 1062 (23000) at line 586: Duplicate entry 'test-extralongname_extralongname_extralongname_extralongname_ext' for key 'PRIMARY'
|
ERROR 1062 (23000) at line 590: Duplicate entry 'test-extralongname_extralongname_extralongname_extralongname_ext' for key 'PRIMARY'
|
ERROR 1062 (23000) at line 593: Duplicate entry 'test-extralongname_extralongname_extralongname_extralongname_ext' for key 'PRIMARY'
|
FATAL ERROR: Upgrade failed
|
In tables innodb_table_stats and innodb_index_stats column table_name is varchar(64), it is shortened to 64 symbols and
as it is a part of primary key, we got an error about duplicate entry. That is why after truncating this to tables upgrade did not fail.
Structures of innodb_table_stats and innodb_index_stats:
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;
|
|
CREATE TABLE `innodb_index_stats` (
|
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
|
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
|
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
|
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
|
`stat_value` bigint(20) unsigned NOT NULL,
|
`sample_size` bigint(20) unsigned DEFAULT NULL,
|
`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
|
PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
|
|
2) While upgrading from 10.1.24 to 10.2.6 with mysql_upgrade:
Phase 4/7: Running 'mysql_fix_privilege_tables'
|
Phase 5/7: Fixing table and database names
|
Processing databases
|
information_schema
|
mysql
|
performance_schema
|
test
|
Phase 6/7: Checking and upgrading tables
|
Processing databases
|
information_schema
|
performance_schema
|
test
|
test.extralongname_extralongname_extralongname_extralongname_ext OK
|
Phase 7/7: Running 'FLUSH PRIVILEGES'
|
OK
|
In error log there are errors, that the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats
are not present or have unexpected structure. Using transient stats instead, so mysql_upgrade did not failed.
2017-07-11 12:17:53 139956946478848 [ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch).
|
2017-07-11 12:17:53 139956946478848 [ERROR] InnoDB: Fetch of persistent statistics requested for table `test`.`extralongname_extralongname_extralongname_extralongname_ext` /* Partition `p2007`, Subpartition `p2007sp0` */ 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.
|
2017-07-11 12:17:53 139956946478848 [ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch).
|
2017-07-11 12:17:53 139956946478848 [ERROR] InnoDB: Fetch of persistent statistics requested for table `test`.`extralongname_extralongname_extralongname_extralongname_ext` /* Partition `p2007`, Subpartition `p2007sp1` */ 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.
|
2017-07-11 12:17:53 139956946478848 [ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch).
|
2017-07-11 12:17:53 139956946478848 [ERROR] InnoDB: Fetch of persistent statistics requested for table `test`.`extralongname_extralongname_extralongname_extralongname_ext` /* Partition `p2008`, Subpartition `p2008sp0` */ 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.
|
2017-07-11 12:17:53 139956946478848 [ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch).
|
2017-07-11 12:17:53 139956946478848 [ERROR] InnoDB: Fetch of persistent statistics requested for table `test`.`extralongname_extralongname_extralongname_extralongname_ext` /* Partition `p2008`, Subpartition `p2008sp1` */ 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
|
|