MYSQL 5.7 to 8..0 Migration issues I faced

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

https://dba.stackexchange.com/questions/8239/how-to-easily-convert-utf8-tables-to-utf8mb4-in-mysql-5-5

https://stackoverflow.com/questions/9422189/why-is-my-view-utf8-and-how-can-i-change-it-to-latin1

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

https://bugs.mysql.com/bug.php?id=94303

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