mysql 1356_MySQL ERROR 1356 (HY000)

一、现象

使用备份软件备份报错

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

二、原因

检查sys表是否损坏

mysql> 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');

returns

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

三、解决方法

如果主从库都有此问题,先从从库执行,再执行主库,mysql_upgrade不会使gtid变化,但是drop database 命令会使gtid变化,所以我们要关闭掉基于会话的sql_log_bin。

STOP SLAVE;

SET SQL_LOG_BIN=0;

DROP DATABASE sys;

SET SQL_LOG_BIN=1;

Run mysql_upgrade

START SLAVE;

来源:51CTO

作者:roidba

链接:https://blog.51cto.com/roidba/2506959

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值