mysql报错处理 View 'sys.xxx' references invalid table(s) or column(s) or function(s)

原创

大大刺猬

发布于 2022-10-11 10:21:18

7.2K0

发布于 2022-10-11 10:21:18

文章被收录于专栏：大大刺猬

环境

使用巡检脚本巡检数据库的时候报错的.

mysql: 5.7.27 (如下教程为复现过程. mysql版本为:5.7.38)

报错

Process Process-2:
Traceback (most recent call last):
  File "/usr/lib64/python3.6/multiprocessing/process.py", line 258, in _bootstrap
    self.run()
  File "/usr/lib64/python3.6/multiprocessing/process.py", line 93, in run
    self._target(*self._args, **self._kwargs)
  File "/root/ddcw_inspection_v2.1/source/inspection/work_inspection.py", line 331, in inspection
    baseinfo['mem_total'] = data2['sys.memory_global_total']['data'].values[0][0]
AttributeError: 'DatabaseError' object has no attribute 'values'

处理过程

找到相关SQL

根据报错找到对应的代码是 select * from sys.memory_global_total. 此报错为无返回数据.

原因有2个:

无权限.
该视图有问题

排除无权限问题

查询sys.memory_global_total视图需要查询权限和执行权限(含函数). 使用的用户含此权限, 故排除.

(root@127.0.0.1) [(none)]> show grants for 'u1'@'%';
+--------------------------------------------------------------+
| Grants for u1@%                                              |
+--------------------------------------------------------------+
| GRANT SELECT, EXECUTE, REPLICATION CLIENT ON *.* TO 'u1'@'%' |
+--------------------------------------------------------------+

确认视图有问题

直接使用该用户登录mysql执行相关查询sql.

根据报错提示, 得出可能为: 1. 表或者字段有问题. 2. 函数或者definer有问题

也就是这个视图有问题.

(u1@127.0.0.1) [(none)]> select * from sys.memory_global_total;
ERROR 1356 (HY000): View 'sys.memory_global_total' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

根据DDL确定函数有问题

查询该视图的DDL, 得到sql: select sys.format_bytes(sum(performance_schema.memory_summary_global_by_event_name.CURRENT_NUMBER_OF_BYTES_USED)) AS total_allocated from performance_schema.memory_summary_global_by_event_name

show create table sys.memory_global_total;

手动执行该sql, 得到报错函数sys.format_bytes 不存在. 问题到这里就很清楚了. 接下来就是解决办法.

(u1@127.0.0.1) [(none)]> select `sys`.`format_bytes`(sum(`performance_schema`.`memory_summary_global_by_event_name`.`CURRENT_NUMBER_OF_BYTES_USED`)) AS `total_allocated` from `performance_schema`.`memory_summary_global_by_event_name`;
ERROR 1305 (42000): FUNCTION sys.format_bytes does not exist

解决办法

既然问题是函数sys.format_bytes不存在, 那解决办法就很多了.

重建sys库.
手动创建相关函数
使用mysql_upgrade修复.
从其它地方导出sys库, 然后导入到本实例(就是1)

本次使用第二种办法, 最快最简单. 找到该函数的DDL. (@@basedir/share/mysql_sys_schema.sql)

CREATE DEFINER='mysql.sys'@'localhost' FUNCTION format_bytes ( bytes TEXT ) RETURNS TEXT COMMENT '\n Description\n \n Takes a raw bytes value, and converts it to a human readable format.\n \n Parameters\n \n bytes (TEXT):\n A raw bytes value.\n \n Returns\n \n TEXT\n \n Example\n \n mysql> SELECT sys.format_bytes(2348723492723746) AS size;\n +----------+\n | size     |\n +----------+\n | 2.09 PiB |\n +----------+\n 1 row in set (0.00 sec)\n \n mysql> SELECT sys.format_bytes(2348723492723) AS size;\n +----------+\n | size     |\n +----------+\n | 2.14 TiB |\n +----------+\n 1 row in set (0.00 sec)\n \n mysql> SELECT sys.format_bytes(23487234) AS size;\n +-----------+\n | size      |\n +-----------+\n | 22.40 MiB |\n +-----------+\n 1 row in set (0.00 sec)\n ' SQL SECURITY INVOKER DETERMINISTIC NO SQL BEGIN IF bytes IS NULL THEN RETURN NULL; ELSEIF bytes >= 1125899906842624 THEN RETURN CONCAT(ROUND(bytes / 1125899906842624, 2), ' PiB'); ELSEIF bytes >= 1099511627776 THEN RETURN CONCAT(ROUND(bytes / 1099511627776, 2), ' TiB'); ELSEIF bytes >= 1073741824 THEN RETURN CONCAT(ROUND(bytes / 1073741824, 2), ' GiB'); ELSEIF bytes >= 1048576 THEN RETURN CONCAT(ROUND(bytes / 1048576, 2), ' MiB'); ELSEIF bytes >= 1024 THEN RETURN CONCAT(ROUND(bytes / 1024, 2), ' KiB'); ELSE RETURN CONCAT(ROUND(bytes, 0), ' bytes'); END IF; END//

验证:

(u1@127.0.0.1) [(none)]> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 140.77 MiB      |
+-----------------+