Bug #98944 SHOW CREATE TABLE sys.schema_unused_indexes produces a warning
Submitted: 13 Mar 2020 16:32 Modified: 14 Mar 2020 4:42
Reporter: Sergey Kuzmichev Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: SYS Schema Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[13 Mar 2020 16:32] Sergey Kuzmichev
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.
[14 Mar 2020 4:42] MySQL Verification Team

Thank you for the bug report and test case.
Verified as described.