Description:
The document has the following description(from https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html):
"5. There must be no tables that have foreign key constraint names longer than 64 characters. Use this query to identify tables with constraint names that are too long:
>>>>>
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN
(SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);
>>>>>
For a table with a constraint name that exceeds 64 characters, drop the constraint and add it back with constraint name that does not exceed 64 characters (use ALTER TABLE)."
But it is wrong to use function "LENGTH" here, function "CHAR_LENGTH" should be used to get the length of chars.
How to repeat:
1. A simple method is to create the following tables on MySQL Server 5.7:
```
CREATE DATABASE mytest;
USE mytest;
CREATE TABLE `t1` (
`fld1` int NOT NULL,
KEY `idx_1` (`fld1`)
);
CREATE TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_к` (
`fld1` int(11) DEFAULT NULL,
`fld2` int(11) NOT NULL,
PRIMARY KEY (`fld2`),
CONSTRAINT FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
);
```
2. And use the above query, the result is:
```SQL
MySQL [mytest]> SELECT TABLE_SCHEMA, TABLE_NAME
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_NAME IN
-> (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
-> INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
-> FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
-> WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);
+--------------+--------------------------------------------------------------------------------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+--------------------------------------------------------------------------------------------------------------------+
| mytest | имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_к |
+--------------+--------------------------------------------------------------------------------------------------------------------+
```
This means upgrade from 57 to 80 will be failed for the too long fk.
3. Now do upgrade from 57 to 80. No error happen. And we can find in MySQL Server 8.0:
```
MySQL [(none)]> select LENGTH(SUBSTR(ID,INSTR(ID,'/')+1)) from INFORMATION_SCHEMA.INNODB_FOREIGN where id like "mytest%";
+------------------------------------+
| LENGTH(SUBSTR(ID,INSTR(ID,'/')+1)) |
+------------------------------------+
| 107 |
+------------------------------------+
1 row in set (0.01 sec)
MySQL [(none)]> select CHAR_LENGTH(SUBSTR(ID,INSTR(ID,'/')+1)) from INFORMATION_SCHEMA.INNODB_FOREIGN where id like "mytest%";
+-----------------------------------------+
| CHAR_LENGTH(SUBSTR(ID,INSTR(ID,'/')+1)) |
+-----------------------------------------+
| 64 |
+-----------------------------------------+
1 row in set (0.00 sec)
```
Suggested fix:
Modify LENGTH to CHAR_LENGTH.
