SHOW CREATE TABLE sys.schema_unused_indexes produces a warning
General
Escalation
General
Escalation
Description
Environment
None
AFFECTED CS IDs
274505
Smart Checklist
Activity
Show:
Julia Vural March 4, 2025 at 9:09 PM
It appears that this issue is no longer being worked on, so we are closing it for housekeeping purposes. If you believe the issue still exists, please open a new ticket after confirming it's present in the latest release.
Lalit Choudhary March 18, 2020 at 7:50 AM
Thank you for the report.
Won't Do
Details
Details
Assignee
Unassigned
UnassignedReporter
Sergey Kuzmichev
Sergey Kuzmichev(Deactivated)Labels
Affects versions
Priority
LowSmart Checklist
Open Smart Checklist
Smart Checklist
Open Smart Checklist
Created March 13, 2020 at 4:37 PM
Updated March 4, 2025 at 9:09 PM
Resolved March 4, 2025 at 9:09 PM
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.