When we migrated our MYSQL 5.7.22 database to 8.0.13 in AWS
we have faced following issues
Issue 1: The following objects use the utf8mb3
character set. It is recommended to convert them to use utf8mb4 instead, for
improved Unicode support.
Solution 1:
We used following commands to convert all the character type
column’s character set to utf8mb4
a. Following query will generate an alter script
for All varchar columns belongs to utf8_unicode_ci collation. Change the
database name and run. Take the output and execute into the database to convert
SELECT concat("ALTER TABLE
`",table_schema,"`.`",table_name, "` CHANGE
`",column_name,"` `",column_name,"`
",data_type,"(",character_maximum_length,") CHARACTER SET
utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql FROM `columns` where table_schema like <> and data_type in ('varchar') and collation_name='utf8_unicode_ci';
b.Following query will generate an alter script
for All text, tinytext, mediumtext, longtext columns belongs to utf8_unicode_ci
collation. Change the database name and run. Take the output and execute into
the database to convert
SELECT concat("ALTER TABLE
`",table_schema,"`.`",table_name, "` CHANGE
`",column_name,"` `",column_name,"` ",data_type,"
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql FROM `COLUMNS`
where table_schema like "<>" and data_type
in ('text','tinytext','mediumtext','longtext') and
collation_name='utf8_unicode_ci';
c. Following query will generate an alter script
for All varchar columns belongs to utf8_general_ci collation. Change the
database name and run. Take the output and execute into the database to convert
SELECT concat("ALTER TABLE
`",table_schema,"`.`",table_name, "` CHANGE
`",column_name,"` `",column_name,"`
",data_type,"(",character_maximum_length,") CHARACTER SET
utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql FROM `columns` where table_schema like <> and data_type in ('varchar') and collation_name= utf8_general_ci ';
d. Following query will generate an alter script
for All text, tinytext, mediumtext, longtext columns belongs to utf8_general_ci
collation. Change the database name and run. Take the output and execute into
the database to convert
SELECT concat("ALTER TABLE
`",table_schema,"`.`",table_name, "` CHANGE
`",column_name,"` `",column_name,"` ",data_type,"
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql FROM `COLUMNS`
where table_schema like "<>" and data_type
in ('text','tinytext','mediumtext','longtext') and collation_name= utf8_general_ci';
e.If you are using views, views character columns also
will give a similar collation issue. To address
this issue, we can create by using following command will help
SET
character_set_client = latin1;
SET
character_set_results = latin1;
SET
character_set_connection = latin1;
DROP VIEW your_view;
CREATE VIEW your_view as
(
here_goes_your_view_query);
reference link
Issue2:
Schema inconsistencies resulting from file removal or corruption
Following
tables show signs that either table datadir directory or frm file was
removed/corrupted. Please check server logs, examine datadir to detect the
issue and fix it before upgrade
Solution 2:
MySQL Shell's upgrade checker utility
checkForServerUpgrade() could incorrectly report a schema inconsistency error
for a table whose name included a special character such as a hyphen.
Remove “-“ hypen from table name will solve this issue
Reference link
Issue 3: Issues
reported by 'check table x for upgrade' command
Solution 3:
Within the view if you are using database reference will get
some of the error like No database is selected either we
can remove database reference from view/ remove before migrate those view and
recreate again after migrate
