Bug #86926 The field table_name (varchar(64)) from mysql.innodb_table_stats can overflow.
Submitted: 4 Jul 2017 11:24 Modified: 29 Jun 11:41
Reporter: Jean-François Gagné Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.7.17-log OS:Any
Assigned to: CPU Architecture:Any

[4 Jul 2017 11:24] Jean-François Gagné
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)
[4 Jul 2017 15:56] Bogdan Kecman
Hi,

verified as reported. Thanks for reporting!

all best
Bogdan
[29 Jun 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 11:49] Jon Stephens
Also added a note regarding upgrades to the preamble of the 5.7.23 changelog.