UTF8-related issues when upgrading from 5.7.40-43 to 8.0.31-23

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hello,

I’m trying to do in-place upgrades from 5.7.40-43 to 8.0.31-23 on multiple servers running Percona Server for MySQL. I’ve run into the following error message on several occasions:

2023-03-01T23:10:29.579502Z 3 [ERROR] [MY-013140] [Server] Comment for field 'db.table.language' contains an invalid utf8mb3 character string: '\x92un'.

The invalid characters are displayed in SHOW CREATE results:

CREATE TABLE `table` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'The Drupal path this alias is for; e.g. node/12.',
  `alias` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'The alias for this path; e.g. title-of-the-story.',
  `language` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'The language this alias is for; if �und�, the alias will be used for unknown languages. Each Drupal path can have an alias for each supported language.',
  PRIMARY KEY (`pid`),
  KEY `alias_language_pid` (`alias`,`language`,`pid`),
  KEY `source_language_pid` (`source`,`language`,`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Is there an efficient approach to identify and remove those table and collumn comments with invalid characters before attempting an upgrade? Those comments are not mentioned in the results when running mysqlcheck --check-upgrade.

2

I would try the mysqlsh utility. It performs a far more comprehensive 5.7 → 8.0 upgrade check.

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html

3

Hello,

Thanks for the suggestion. MySQL shell doesn’t seem to detect the aforementioned issues, though.

4

Perhaps this can help. You will still need to ALTER TABLE … MODIFY COLUMN to remove the comment.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_COMMENT FROM
 INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_COMMENT != "";