この記事は最終更新から6年以上経過しています。内容が古くなっている可能性があります。
はじめに
MySQL 5.7 で追加された sys スキーマを使おうとしたところ、以下のようなエラーが出力されて参照できませんでした。
|
1 2 3 4 |
mysql> USE sys Database changed mysql> SELECT * FROM user_summary_by_statement_latency; ERROR 1356 (HY000): View 'sys.user_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
この原因について調査した結果を以下にまとめます。
目次
環境
- OS：CentOS 6.9
- DBMS：MySQL 5.7.24
※データは別サーバーから取得した mysqldump を使用してリストアしています。
調査内容
まずは ERROR の内容について確認します。
|
1 2 |
mysql> system perror 1356; MySQL error code 1356 (ER_VIEW_INVALID): View '%-.192s.%-.192s' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
どうやら DDL に不正な情報が混じっているようなので、対象のビューの再作成を試みます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SHOW CREATE VIEW user_summary_by_statement_latency\G *************************** 1. row *************************** View: user_summary_by_statement_latency Create View: CREATE ALGORITHM=TEMPTABLE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `user_summary_by_statement_latency` AS select if(isnull(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`USER`),'background',`performance_schema`.`events_statements_summary_by_user_by_event_name`.`USER`) AS `user`,sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`COUNT_STAR`) AS `total`,`sys`.`format_time`(sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_TIMER_WAIT`)) AS `total_latency`,`sys`.`format_time`(sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`MAX_TIMER_WAIT`)) AS `max_latency`,`sys`.`format_time`(sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_LOCK_TIME`)) AS `lock_latency`,sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_ROWS_SENT`) AS `rows_sent`,sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_ROWS_EXAMINED`) AS `rows_examined`,sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_ROWS_AFFECTED`) AS `rows_affected`,(sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_NO_INDEX_USED`) + sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_NO_GOOD_INDEX_USED`)) AS `full_scans` from `performance_schema`.`events_statements_summary_by_user_by_event_name` group by if(isnull(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`USER`),'background',`performance_schema`.`events_statements_summary_by_user_by_event_name`.`USER`) order by sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_TIMER_WAIT`) desc character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set, 1 warning (0.00 sec) mysql> DROP VIEW user_summary_by_statement_latency; Query OK, 0 rows affected (0.00 sec) mysql> CREATE ALGORITHM=TEMPTABLE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `user_summary_by_statement_latency` AS select if(isnull(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`USER`),'background',`performance_schema`.`events_statements_summary_by_user_by_event_name`.`USER`) AS `user`,sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`COUNT_STAR`) AS `total`,`sys`.`format_time`(sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_TIMER_WAIT`)) AS `total_latency`,`sys`.`format_time`(sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`MAX_TIMER_WAIT`)) AS `max_latency`,`sys`.`format_time`(sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_LOCK_TIME`)) AS `lock_latency`,sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_ROWS_SENT`) AS `rows_sent`,sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_ROWS_EXAMINED`) AS `rows_examined`,sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_ROWS_AFFECTED`) AS `rows_affected`,(sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_NO_INDEX_USED`) + sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_NO_GOOD_INDEX_USED`)) AS `full_scans` from `performance_schema`.`events_statements_summary_by_user_by_event_name` group by if(isnull(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`USER`),'background',`performance_schema`.`events_statements_summary_by_user_by_event_name`.`USER`) order by sum(`performance_schema`.`events_statements_summary_by_user_by_event_name`.`SUM_TIMER_WAIT`) desc; ERROR 1305 (42000): FUNCTION sys.format_time does not exist |
sys.format_time() が存在しないことが原因で失敗しているようです。そのため、sys スキーマ内のストアドファンクションの情報を確認します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SHOW FUNCTION STATUS; Empty set (0.00 sec) mysql> SHOW PROCEDURE STATUS; Empty set (0.00 sec) mysql> SELECT count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) |
ストアドファンクションのみならず、ストアドプロシージャも存在していませんでした。これがエラーの直接的な原因であると考えられます。
原因と対策
上記の内容をもとにバグ情報を確認したところ、Bug #83259：All routines in the sys schema are deleted by restoringに該当していることがわかりました。
mysqldump --all-databases で取得したデータを MySQL 5.7 にリストアするとこのバグに該当します。ソースコード上は以下の内容が実行されており、 –all-databases オプション(opt_alldbs)が有効である場合、mysql.proc テーブル内に保持されている sys スキーマの ストアドプロシージャやストアドファンクションが取得されなくなるようです。
|
1 2 3 4 5 6 7 8 9 10 11 |
【mysqldump.c】 (...) If table is mysql.proc then do not dump routines which belong to sys schema */ else if ((!my_strcasecmp(charset_info, db, "mysql")) && (!my_strcasecmp(charset_info, table, "proc")) && opt_alldbs) { dynstr_append_checked(&query_string, " WHERE db != 'sys'"); } |
公式の mysqldump マニュアルには以下の記載がありますが、既に存在している sys スキーマの情報が上書きされることまでは示されていないので、注意する必要があります。
引用：4.5.4 mysqldump — A Database Backup Program
mysqldump does not dump the INFORMATION_SCHEMA, performance_schema, or sys schema by default. To dump any of these, name them explicitly on the command line. You can also name them with the –databases option.
この問題の回避方法については、以下の 2 つの方法があります。
- mysql_upgrade を実行する。
- sys スキーマの定義のみを別サーバーから取得してリストアする。
今回は mysql_upgrade によるテーブルロックが許容できなかったため、検証環境で初期インストールした MySQL 5.7 から sys スキーマの定義を mysqldump で取得して、リストアする方法を取りました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
【検証環境】 [root@kensyo ~]# mysqldump --no-data --routines --databases sys > sys_dump.sql 【本番(バグ再現)環境】 [root@ss-test ~]# mysql < sys_dump.sql [root@ss-test ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 57 Server version: 5.7.24 MySQL Community Server (GPL) (...) mysql> select count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys'; +----------+ | count(*) | +----------+ | 48 | +----------+ 1 row in set (0.01 sec) mysql> USE sys Database changed mysql> SELECT * FROM user_summary_by_statement_latency; +------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+ | user | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans | +------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+ | root | 42004 | 17.06 s | 1.23 s | 1.13 s | 14944 | 38458 | 32154 | 1754 | | background | 0 | 0 ps | 0 ps | 0 ps | 0 | 0 | 0 | 0 | +------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+ 2 rows in set (0.01 sec) |
リストアすることで、正常に参照できるようになりました。
まとめと余談
- データを移行した後には mysql_upgrade を実行して、問題がないか確認するようにしてください。
- MySQL 8.0 ではバグの原因となる mysql.proc テーブルが存在しないため、この問題は発生しなくなります。
- 以下のビューは sys スキーマ内のストアドファンクションやストアドプロシージャを使用していないので、問題なく参照することができます。
| metrics | ps_check_lost_instrumentation |
|---|---|
| schema_auto_increment_columns | schema_redundant_indexes |
| schema_unused_indexes | session_ssl_status |
| sys_config | version |
