| Bug #86926 | The field table_name (varchar(64)) from mysql.innodb_table_stats can overflow. | ||
|---|---|---|---|
| Submitted: | 4 Jul 2017 11:24 | Modified: | 29 Jun 2018 11:41 |
| Reporter: | Jean-François Gagné | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
| Version: | 5.7.17-log | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[4 Jul 2017 15:56]
MySQL Verification Team
Hi, verified as reported. Thanks for reporting! all best Bogdan
[29 Jun 2018 11:41]
Jon Stephens
Documented fix in the MySQL 5.7.23 changelog as follows:
When creating partitioned InnoDB tables with very long names,
the corresponding entries in the mysql.innodb_index_stats and
mysql.innodb_table_stats system tables were truncated. To fix
this issue, the length of the table_name column in each of these
tables has been increased from 64 to 199 characters. In both
cases, this is now the same as the lengths of these columns in
MySQL 8.0 and later.
When upgrading to this release, use mysql_upgrade to effect
these changes in your MySQL installation. In the event that you
fail to do this, MySQL generates the warning -Table
mysql/innodb_table_stats has length mismatch in the column name
table_name. Please run mysql_upgrade.- in the error log.
NOTE: Some platforms, such as Microsoft Windows, may restrict path
lengths (MAX_PATH) to a maximum of 260, which can cause
creation of partitioned tables with long names to fail. You
can avoid this problem on Windows systems by enabling NTFS
long path names; see your system documentation for information
on how to do this.
Closed.
[29 Jun 2018 11:49]
Jon Stephens
Also added a note regarding upgrades to the preamble of the 5.7.23 changelog.
Description: Hi, the field table_name from the mysql.innodb_table_stats is a varchar(64). But with creating a partitioned table, I was able to end-up with more than 64 characters in this field. This looks like a ticking bomb... Many thanks for looking into that, JFG How to repeat: > select version(); +------------+ | version() | +------------+ | 5.7.17-log | +------------+ 1 row in set (0.00 sec) > select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'test_jfg'; Empty set (0.00 sec) > create database test_jfg; Query OK, 1 row affected (0.01 sec) > CREATE TABLE test_jfg.test_jfg_table_name_with_64_chars_123456789012345678901234567890 ( -> id int(10) unsigned NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC -> PARTITION BY RANGE ( id ) ( -> PARTITION p1000 VALUES LESS THAN (1000) ENGINE = InnoDB, -> PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); Query OK, 0 rows affected (0.01 sec) > select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'test_jfg'; +---------------+--------------------------------------------------------------------------+--------------------+ | database_name | table_name | length(table_name) | +---------------+--------------------------------------------------------------------------+--------------------+ | test_jfg | test_jfg_table_name_with_64_chars_123456789012345678901234567890#P#p1000 | 72 | | test_jfg | test_jfg_table_name_with_64_chars_123456789012345678901234567890#P#pmax | 71 | +---------------+--------------------------------------------------------------------------+--------------------+ 2 rows in set (0.00 sec) > 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)