Upgrading 5.7 to 8.0.21 getting errors when starting up

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hi everyone,

I’m getting errors when mysql 8 starts to do the inplace upgrade.

How do I find which tables are giving me the [ERROR] [MY-013140] [Server] BLOB/TEXT column ‘id’ used in key specification without a key length errors?

2020-12-11T19:20:51.447877Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21-12) starting as process 4301

2020-12-11T19:20:51.495429Z 0 [Warning] [MY-013242] [Server] --character-set-server: ‘utf8’ is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.

2020-12-11T19:20:51.541495Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.

2020-12-11T19:20:51.541591Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

2020-12-11T19:21:24.303887Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

2020-12-11T19:21:26.673453Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060, socket: /var/lib/mysql/mysqlx.sock

2020-12-11T19:21:26.673910Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we’re sending the information to the error-log instead: MY-001287 - ‘validate password plugin’ is deprecated and will be removed in a future release. Please use validate_password component instead

2020-12-11T19:22:31.033652Z 2 [ERROR] [MY-013140] [Server] BLOB/TEXT column ‘id’ used in key specification without a key length

2020-12-11T19:22:31.732431Z 2 [ERROR] [MY-013140] [Server] BLOB/TEXT column ‘id’ used in key specification without a key length

2020-12-11T19:22:33.806794Z 2 [ERROR] [MY-013140] [Server] BLOB/TEXT column ‘id’ used in key specification without a key length

2020-12-11T19:22:42.906158Z 2 [ERROR] [MY-010520] [Server] Invalid (old?) table or database name ‘lost+found’

2020-12-11T19:22:42.906668Z 2 [Warning] [MY-010772] [Server] db.opt file not found for lost+found database. Using default Character set.

2020-12-11T19:22:42.912252Z 2 [Warning] [MY-010772] [Server] db.opt file not found for mplustemp database. Using default Character set.

2020-12-11T19:22:53.653014Z 2 [Warning] [MY-010772] [Server] db.opt file not found for tmp database. Using default Character set.

2020-12-11T19:23:07.181430Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.

2020-12-11T19:23:07.182187Z 0 [ERROR] [MY-010119] [Server] Aborting

2020-12-11T19:23:08.750182Z 0 [Warning] [MY-011068] [Server] The syntax ‘validate password plugin’ is deprecated and will be removed in a future release. Please use validate_password component instead.

2020-12-11T19:23:12.268221Z 0 [Warning] [MY-010738] [Server] Plugin ‘audit_log’ will be forced to shutdown

2020-12-11T19:23:12.277628Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21-12) Percona Server (GPL), Release 12, Revision 7ddfdfe.

4
  • Steps taken:

    Looked for columns named id which were text/blob. Found 6 tables with id column as tinyint.

  • dropped tables before upgrading.
  • upgraded successfully.
  • tried to recreate the tables. 3 would not recreate because of error specified key was too long max key length is 255 bytes. the tables had id (tinytext) which can only accommodate 255 bytes but the primary key was being created as PRIMARY KEY (`id`(255)) with utf8 characterset. Had to create primary key with PRIMARY KEY (`id`(85))
  • database successfully upgraded.

  • Solution:

    recreated primary key to correct length on the 3 tables .PRIMARY KEY (id(85))

  • stopped mysql and was able to upgrade successfully.