mysql 存储过程中查询其他库失败,由于sys库函数不存在问题导致的备份失败问题处理...

问题现象

备份时提示：ERROR 1356 (HY000): View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

处理过程

查看视图定义

mysql> show create table sys.host_summary\G

*************************** 1. row ***************************

View: host_summary

Create View: CREATE ALGORITHM=TEMPTABLE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`host_summary` AS select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`) AS `host`,sum(`stmt`.`total`) AS `statements`,`sys`.`format_time`(sum(`stmt`.`total_latency`)) AS `statement_latency`,`sys`.`format_time`(ifnull((sum(`stmt`.`total_latency`) / nullif(sum(`stmt`.`total`),0)),0)) AS `statement_avg_latency`,sum(`stmt`.`full_scans`) AS `table_scans`,sum(`io`.`ios`) AS `file_ios`,`sys`.`format_time`(sum(`io`.`io_latency`)) AS `file_io_latency`,sum(`performance_schema`.`accounts`.`CURRENT_CONNECTIONS`) AS `current_connections`,sum(`performance_schema`.`accounts`.`TOTAL_CONNECTIONS`) AS `total_connections`,count(distinct `performance_schema`.`accounts`.`USER`) AS `unique_users`,`sys`.`format_bytes`(sum(`mem`.`current_allocated`)) AS `current_memory`,`sys`.`format_bytes`(sum(`mem`.`total_allocated`)) AS `total_memory_allocated` from (((`performance_schema`.`accounts` join `sys`.`x$host_summary_by_statement_latency` `stmt` on((`performance_schema`.`accounts`.`HOST` = `stmt`.`host`))) join `sys`.`x$host_summary_by_file_io` `io` on((`performance_schema`.`accounts`.`HOST` = `io`.`host`))) join `sys`.`x$memory_by_host_by_current_bytes` `mem` on((`performance_schema`.`accounts`.`HOST` = `mem`.`host`))) group by if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`)

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set, 1 warning (0.00 sec)

查询视图定义中存在什么问题

执行视图定义中AS后面的内容，对原始数据进行查询

select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`) AS `host`,sum(`stmt`.`total`) AS `statements`,`sys`.`format_time`(sum(`stmt`.`total_latency`)) AS `statement_latency`,`sys`.`format_time`(ifnull((sum(`stmt`.`total_latency`) / nullif(sum(`stmt`.`total`),0)),0)) AS `statement_avg_latency`,sum(`stmt`.`full_scans`) AS `table_scans`,sum(`io`.`ios`) AS `file_ios`,`sys`.`format_time`(sum(`io`.`io_latency`)) AS `file_io_latency`,sum(`performance_schema`.`accounts`.`CURRENT_CONNECTIONS`) AS `current_connections`,sum(`performance_schema`.`accounts`.`TOTAL_CONNECTIONS`) AS `total_connections`,count(distinct `performance_schema`.`accounts`.`USER`) AS `unique_users`,`sys`.`format_bytes`(sum(`mem`.`current_allocated`)) AS `current_memory`,`sys`.`format_bytes`(sum(`mem`.`total_allocated`)) AS `total_memory_allocated` from (((`performance_schema`.`accounts` join `sys`.`x$host_summary_by_statement_latency` `stmt` on((`performance_schema`.`accounts`.`HOST` = `stmt`.`host`))) join `sys`.`x$host_summary_by_file_io` `io` on((`performance_schema`.`accounts`.`HOST` = `io`.`host`))) join `sys`.`x$memory_by_host_by_current_bytes` `mem` on((`performance_schema`.`accounts`.`HOST` = `mem`.`host`))) group by if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`)

#结果

ERROR 1305 (42000): FUNCTION sys.format_time does not exist

初步判断是由于sys.format_time这个函数不存在导致的问题

进一步进行查询是否还有其他问题

查看sys库中的其他函数

mysql> select db,name from mysql.proc where type='FUNCTION';

查看sys库中的存储过程

select db,name from mysql.proc where type='PROCEDURE';

查看sys库中的触发器

SHOW TRIGGERS FROM sys;

#或者

select TRIGGER_SCHEMA,TRIGGER_NAME from information_schema.triggers where TRIGGER_SCHEMA='sys';

解决方案

通过二进制目录下的mysql_sys_schema.sql进行处理

在MySQL @@basedir/share/目录下存在mysql_sys_schema.sql文件，里面记录了sys库的创建语句。可以根据该文件中的语句对问题进行处理。

但是需要注意的是这个文件里面没有设置语句结束符号，直接source或者执行里面的语句有报错，需要手动处理一下。

通过gtihub上的mysql-sys开源代码进行处理

github mysql-sys 中也记录了sys库的创建语句

将文件下载到本地，然后根据数据库版本，执行以下命令即可

# 安装前操作，内容是禁用掉sql_log_bin，不记录到日志中。

source before_setup.sql

# 创建sys库，实际会调用其他文件夹中的sql语句来进行表、视图、存储过程、触发器的创建

source sys_57.sql

# 或者 source sys_56.sql #mysql 5.6

# 安装后的操作，内容是将sql_log_bin恢复到操作前的状态

source after_setup.sql

其他 2020-11-15 更新

之前只写了问题如何处理，但是没有深究原因，最近在爱可生开源社区公众号看到了mysqldump可能导致sys库函数和存储过程丢失，估计是相同的问题

原文链接如下:

故障分析 | MySQL5.7 使用 mysqldump 重要闭坑事项