Description:
Running mysqlpump on a clean fresh installation of MySQL of versions 8.0.x results in various number of warnings being output regarding usage of utf8 alias. For 8.0.19, only one such warning is printed. Reviewing the general log, narrowed this down to a single view in sys schema: sys.schema_unused_indexes. With the view fixed, mysqlpump shouldn't complain about system objects any more.
However, just trying to create and replace this view to convert(...using utf8mb4) doesn't work, as the new view will have (convert(convert(`t`.`INDEX_NAME` using utf8mb4) using utf8) or any other number of converts.
How to repeat:
Install MySQL 8.0.19. Run two commands.
mysql> show create table sys.schema_unused_indexes\G
*************************** 1. row ***************************
View: schema_unused_indexes
Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`schema_unused_indexes` (`object_schema`,`object_name`,`index_name`) AS select `t`.`OBJECT_SCHEMA` AS `object_schema`,`t`.`OBJECT_NAME` AS `object_name`,`t`.`INDEX_NAME` AS `index_name` from (`performance_schema`.`table_io_waits_summary_by_index_usage` `t` join `information_schema`.`STATISTICS` `s` on(((`t`.`OBJECT_SCHEMA` = convert(`s`.`TABLE_SCHEMA` using utf8mb4)) and (`t`.`OBJECT_NAME` = convert(`s`.`TABLE_NAME` using utf8mb4)) and (convert(`t`.`INDEX_NAME` using utf8) = `s`.`INDEX_NAME`)))) where ((`t`.`INDEX_NAME` is not null) and (`t`.`COUNT_STAR` = 0) and (`t`.`OBJECT_SCHEMA` <> 'mysql') and (`t`.`INDEX_NAME` <> 'PRIMARY') and (`s`.`NON_UNIQUE` = 1) and (`s`.`SEQ_IN_INDEX` = 1)) order by `t`.`OBJECT_SCHEMA`,`t`.`OBJECT_NAME`
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set, 1 warning (0.01 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 3719
Message: '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.
1 row in set (0.01 sec)
Suggested fix:
Change (convert(`t`.`INDEX_NAME` using utf8) to (convert(`t`.`INDEX_NAME` using utf8mb4) as in other cases in this view.
Description: Running mysqlpump on a clean fresh installation of MySQL of versions 8.0.x results in various number of warnings being output regarding usage of utf8 alias. For 8.0.19, only one such warning is printed. Reviewing the general log, narrowed this down to a single view in sys schema: sys.schema_unused_indexes. With the view fixed, mysqlpump shouldn't complain about system objects any more. However, just trying to create and replace this view to convert(...using utf8mb4) doesn't work, as the new view will have (convert(convert(`t`.`INDEX_NAME` using utf8mb4) using utf8) or any other number of converts. How to repeat: Install MySQL 8.0.19. Run two commands. mysql> show create table sys.schema_unused_indexes\G *************************** 1. row *************************** View: schema_unused_indexes Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`schema_unused_indexes` (`object_schema`,`object_name`,`index_name`) AS select `t`.`OBJECT_SCHEMA` AS `object_schema`,`t`.`OBJECT_NAME` AS `object_name`,`t`.`INDEX_NAME` AS `index_name` from (`performance_schema`.`table_io_waits_summary_by_index_usage` `t` join `information_schema`.`STATISTICS` `s` on(((`t`.`OBJECT_SCHEMA` = convert(`s`.`TABLE_SCHEMA` using utf8mb4)) and (`t`.`OBJECT_NAME` = convert(`s`.`TABLE_NAME` using utf8mb4)) and (convert(`t`.`INDEX_NAME` using utf8) = `s`.`INDEX_NAME`)))) where ((`t`.`INDEX_NAME` is not null) and (`t`.`COUNT_STAR` = 0) and (`t`.`OBJECT_SCHEMA` <> 'mysql') and (`t`.`INDEX_NAME` <> 'PRIMARY') and (`s`.`NON_UNIQUE` = 1) and (`s`.`SEQ_IN_INDEX` = 1)) order by `t`.`OBJECT_SCHEMA`,`t`.`OBJECT_NAME` character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci 1 row in set, 1 warning (0.01 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 3719 Message: '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. 1 row in set (0.01 sec) Suggested fix: Change (convert(`t`.`INDEX_NAME` using utf8) to (convert(`t`.`INDEX_NAME` using utf8mb4) as in other cases in this view.