mysql-5.7-upgrade.md

Updating to mysql-server-5.7 (5.7.28-0ubuntu0.18.04.4) on Ubuntu produces the following error:

mysql_upgrade: [ERROR] 1833: Cannot change column 'table_name': used in a foreign key constraint 'innodb_index_stats_ibfk_1' of table 'mysql.innodb_index_stats'

The update appears to be changing the varchar length of the table_name column:

Important Change; Partitioning: After creating partitioned InnoDB tables with very long names, the table_name columns in 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.

So I ended deleting that constraint:

ALTER TABLE `innodb_index_stats`
  DROP FOREIGN KEY `innodb_index_stats_ibfk_1`

running the mysql_upgrade command and adding the constraint back in:

ALTER TABLE `innodb_index_stats` 
  ADD CONSTRAINT `innodb_index_stats_ibfk_1` 
  FOREIGN KEY (`database_name`, `table_name`) 
  REFERENCES `innodb_table_stats` (`database_name`, `table_name`)

This allowed the upgrade script to change the length of the table_name column to 199. Here is the final table schema:

CREATE TABLE `innodb_index_stats` (
    `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `table_name` varchar(199) 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`)
    CONSTRAINT `innodb_index_stats_ibfk_1` FOREIGN KEY (`database_name`, `table_name`) REFERENCES `innodb_table_stats` (`database_name`, `table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

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;