Description:
56 and 57 can create PK, UK, prefix index, etc. on the spatial type field, and the types of these indexes are all BTREE.
Since 80 does not support the spatial type to create a BTREE index, when the above situation occurs in 57, it will cause an error exit and upgrade failure. The error log information is as follows：
2023-06-28T09:39:51.181311Z 2 [ERROR] [MY-013140] [Server] The index type BTREE is not supported for spatial indexes.
2023-06-28T09:39:51.181388Z 2 [ERROR] [MY-013140] [Server] Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
2023-06-28T09:39:51.181445Z 2 [ERROR] [MY-013140] [Server] Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
2023-06-28T09:39:51.181494Z 2 [ERROR] [MY-013140] [Server] Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
2023-06-28T09:39:51.181547Z 2 [ERROR] [MY-013140] [Server] Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
2023-06-28T09:39:51.284546Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
There are two problems here:
1. The log information is unclear, and the log cannot tell which table or index caused the upgrade failure.
2. It cannot be detected by mysqlcheck.
I think one of two issues needs to be fixed so users know which tables need to be processed
How to repeat:
# =============================== step 1 =============================== #
# start mysql-server 5.7, create table with spatial column and BTREE-index
create database test;
use test;
create table t1( id int not null auto_increment, rangevalue point not null, primary key(id), key range_idx(rangevalue(5)))ENGINE=InnoDB;
create table t2( id int not null auto_increment, rangevalue point not null, key(id), primary key range_idx(rangevalue))ENGINE=InnoDB;
create table t3( id int not null auto_increment, rangevalue point not null, primary key(id), unique key range_idx(rangevalue))ENGINE=InnoDB;
create table t4( id int not null auto_increment, rangevalue point not null, primary key(id), key range_idx(rangevalue ASC))ENGINE=InnoDB;
create table t5( id int not null auto_increment, rangevalue point not null, key(id), key range_idx(rangevalue) using btree)ENGINE=InnoDB;
# =============================== step 2 =============================== #
#./mysqlcheck --databases test -h -P -u -p, and output like:
test.t1 OK
test.t2 OK
test.t3 OK
test.t4 OK
test.t5 OK
It seems like everything is OK.
# =============================== step 3 =============================== #
# shutdown 5.7 and start 8.0 with datadir from 5.7, fail to upgrade, error log like:
# 2023-06-28T09:39:51.181311Z 2 [ERROR] [MY-013140] [Server] The index type BTREE is not supported for spatial indexes.
# 2023-06-28T09:39:51.181388Z 2 [ERROR] [MY-013140] [Server] Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
# 2023-06-28T09:39:51.181445Z 2 [ERROR] [MY-013140] [Server] Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
# 2023-06-28T09:39:51.181494Z 2 [ERROR] [MY-013140] [Server] Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
# 2023-06-28T09:39:51.181547Z 2 [ERROR] [MY-013140] [Server] Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys
# 2023-06-28T09:39:51.284546Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
Suggested fix:
1. Add additional information about tables and indexes in the log of the upgrade process
2. Add corresponding check logic in mysqlcheck
In my opinion, either do both or just one.
