[MDEV-13274] mysql_upgrade fails if dbname+tablename+partioname > 64 chars - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0(EOL), 10.1(EOL)
Fix Version/s: 10.1.26, 10.0.32, 10.2.8
Component/s: Server
Labels:
- upstream
Environment:
Ubuntu 16.04

Description

CREATE TABLE `extralongname_extralongname_extralongname_extralongname_ext` (

`id` int(10) unsigned NOT NULL,

`created_date` date NOT NULL DEFAULT '1970-01-01' COMMENT 'DATE(created), for index & partitions',

`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`created`,`id`,`created_date`)

) ENGINE=InnoDB STATS_PERSISTENT=1 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

/*!50100 PARTITION BY RANGE ( YEAR(created_date))

SUBPARTITION BY HASH ( MONTH(created_date))

SUBPARTITIONS 2

(PARTITION p2007 VALUES LESS THAN (2008) ENGINE = InnoDB,

PARTITION p2008 VALUES LESS THAN (2009) ENGINE = InnoDB

) */

Phase 4/7: Running 'mysql_fix_privilege_tables'

ERROR 1062 (23000)  Duplicate entry 'extralongname_extralongname_extralongname_extralongname_ext#P#p2' for key 'PRIMARY'

ERROR 1062 (23000)  Duplicate entry 'extralongname_extralongname_extralongname_extralongname_ext#P#p2' for key 'PRIMARY'

FATAL ERROR: Upgrade failed

After

truncate mysql.innodb_table_stats;

mysql_upgrade runs fine.

Attachments

Issue Links

is caused by

MDEV-13360 too long values in mysql.innodb_table_stats.table_name

Stalled

links to

Bug #86926 mysql 5.7

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2017-07-11 11:59

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

Alice Sherepa added a comment - 2017-07-11 11:59 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

Sergei Golubchik added a comment - 2017-07-20 18:21

mysql_upgrade is fixed. InnoDB isn't, it's MDEV-13360

Sergei Golubchik added a comment - 2017-07-20 18:21 mysql_upgrade is fixed. InnoDB isn't, it's MDEV-13360

MariaDB Server

mysql_upgrade fails if dbname+tablename+partioname > 64 chars

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration