作者:王向
爱可生 DBA 团队成员,负责公司 DMP 产品的运维和客户 MySQL 问题的处理。擅长数据库故障处理。对数据库技术和 python 有着浓厚的兴趣。
背景
mysql> SELECT * FROM sys.processlist;
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
排查常见问题
首先排查权限问题是否有权限。
mysql> SHOW GRANTS FOR root@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.proc;
Empty set (0.00 sec)
mysql> SHOW PROCEDURE STATUS WHERE Db = 'sys';
Empty set (0.00 sec)
mysql> SHOW FUNCTION STATUS WHERE Db = 'sys';
Empty set (0.00 sec)
带着疑问于开始漫长的排查过程。经过对客户的刨根问题,发现并没有上述情况的发生。用户备份习惯都是全备(-A),且都是备份恢复后出现 sys 库 ERROR 1356,检查用户 MySQL 环境主要几大版本分布 MySQL 5.7.13,5.7.25,5.7.28。于是把问题定位到了 mysqldump 的备份上。
先备份还原一把看看
笔者强烈认为是客户跨版本造成的,给客户来点证据。先验证一波同版本 MySQL 使用 mysqldump 全备恢复后,到底会不会出现 sys 库 ERROR 1356。
mysql> SELECT * FROM sys.version;
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 1.5.2 | 5.7.31-log |
+-------------+---------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM sys.processlist;
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> SELECT COUNT(*) FROM mysql.proc;
+----------+
| COUNT(*) |
+----------+
| 48 |
+----------+
1 row in set (0.00 sec)
mysqldump --all-databases --set-gtid-purged=OFF \
--master-data=2 --single-transaction --routines \
--events --triggers --max_allowed_packet=256M > all.sql
mysql -uroot -S /tmp/mysql.sock < all.sql
mysql> SELECT * FROM sys.processlist;
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> SELECT COUNT(*) FROM mysql.proc;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW PROCEDURE STATUS WHERE Db = 'sys';
Empty set (0.00 sec)
mysql> SHOW FUNCTION STATUS WHERE Db = 'sys';
Empty set (0.00 sec)
再看看其它版本
经过对 MySQL 5.7.13,5.7.21,5.7.25,5.7.28,5.7.31 等几个版本测试全备躺枪。奇怪的现象是他们唯一的共性就是无论怎么备份怎么还原只要使用了 –all-databases (-A) 就报 ERROR 1356。这不禁让笔者陷入了沉思。
寻找突破点
--
-- Table structure for table `proc`
--
DROP TABLE IF EXISTS `proc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `proc` (
`db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`name` char(64) NOT NULL DEFAULT '',
`type` enum('FUNCTION','PROCEDURE') NOT NULL,
`specific_name` char(64) NOT NULL DEFAULT '',
`language` enum('SQL') NOT NULL DEFAULT 'SQL',
`sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
`is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
`security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
`param_list` blob NOT NULL,
`returns` longblob NOT NULL,
`body` longblob NOT NULL,
`definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`body_utf8` longblob,
PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `proc`
--
LOCK TABLES `proc` WRITE;
/*!40000 ALTER TABLE `proc` DISABLE KEYS */;
/*!40000 ALTER TABLE `proc` ENABLE KEYS */;
UNLOCK TABLES;
真相大白
sys:
https://dev.mysql.com/doc/refman/5.7/en/sys-schema.html
mysqldump:
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
mysqldump --databases --routines sys > sys_dump.sql
mysqlpump sys > sys_dump.sql
mysql < sys_dump.sql
BUG 连接:
https://bugs.mysql.com/bug.php?id=86807
https://bugs.mysql.com/bug.php?id=92631
https://bugs.mysql.com/bug.php?id=83259
https://github.com/mysql/mysql-server/commit/ded3155def2ba3356017c958c49ff58c2cae1830
解决方案和使用场景
针对这个 BUG 整理了 4 个解决方案可供参考,根据实际环境场景进行选择使用。
1、mysql_upgrade install or upgrade sys schema
# 删除 sys schema (An error occurs if a sys schema exists but has no version view)
mysql> DROP DATABASE sys;
# 这个时候 sys schema 不应该存在
mysql> SHOW DATABASES;
# 最后,执行 mysql_upgrade sys schema 以恢复正常
mysql_upgrade --upgrade-system-tables --skip-verbose --force
mysql> SHOW DATABASES;
mysql> SELECT COUNT(*) FROM mysql.proc;
2、全备时同时备份 sys 库
mysqldump -A --set-gtid-purged=OFF --master-data=2 --single-transaction --routines --events --triggers > all.sql
mysqldump --databases --routines sys > sys_dump_`mysql -V|awk '{print $5}'|cut -b 1-6`.sql
3、使用 databases 全备
select_databases="
SELECT
GROUP_CONCAT(schema_name SEPARATOR ' ')
FROM
information_schema.schemata
WHERE
schema_name NOT IN ('performance_schema','information_schema');"
databases=`mysql -NBe "$select_databases"`
mysqldump --set-gtid-purged=OFF --master-data=2 \
--single-transaction --routines --events --triggers \
--max_allowed_packet=256M --databases > all.sql
4、使用 mysql-sys 开源代码
mysql-sys:
https://github.com/mysql/mysql-sys
# 安装前操作,内容是禁用掉 sql_log_bin,不记录到日志中。
mysql> source before_setup.sql
# 创建 sys 库,实际会调用其他文件夹中的 sql 语句
# 来进行表、视图、存储过程、触发器的创建
mysql> source sys_57.sql
# 安装后的操作,内容是将 sql_log_bin 恢复到操作前的状态
mysql> source after_setup.sql
1. data-dictionary-usage-differences:
https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-usage-differences.html
2. news-8-0-0:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html
【加餐 2】如果还有疑问?
/**
First mysql version supporting the information schema.
*/
#define FIRST_INFORMATION_SCHEMA_VERSION 50003
/**
Name of the information schema database.
*/
#define INFORMATION_SCHEMA_DB_NAME "information_schema"
/**
First mysql version supporting the performance schema.
*/
#define FIRST_PERFORMANCE_SCHEMA_VERSION 50503
/**
Name of the performance schema database.
*/
#define PERFORMANCE_SCHEMA_DB_NAME "performance_schema"
/**
First mysql version supporting the sys schema.
*/
#define FIRST_SYS_SCHEMA_VERSION 50707 /* 最早出现sys schema的MySQL版本 5.7.7 */
/**
Name of the sys schema database.
*/
#define SYS_SCHEMA_DB_NAME "sys"
.........
/* 执行dump_all_databases的条件 */
if (opt_alldbs)
{
if (!opt_alltspcs && !opt_notspcs)
dump_all_tablespaces();
dump_all_databases();
}
.........
/* dump_all_databases */
static int dump_all_databases()
{
MYSQL_ROW row;
MYSQL_RES *tableres;
int result=0
/* 获取所有数据库:SHOW DATABASES */
if (mysql_query_with_error_report(mysql, &tableres, "SHOW DATABASES"))
return 1;
while ((row= mysql_fetch_row(tableres)))
{
/* 排除information_schema */
if (mysql_get_server_version(mysql) >= FIRST_INFORMATION_SCHEMA_VERSION &&
!my_strcasecmp(&my_charset_latin1, row[0], INFORMATION_SCHEMA_DB_NAME))
continue;
/* 排除performance_schema */
if (mysql_get_server_version(mysql) >= FIRST_PERFORMANCE_SCHEMA_VERSION &&
!my_strcasecmp(&my_charset_latin1, row[0], PERFORMANCE_SCHEMA_DB_NAME))
continue;
/* 排除sys */
/* 检查当前MySQL的版本是否 >= 最早支持SYS_SCHEMA的版本号。&& row[0] 为 SYS_SCHEMA_DB_NAME 就跳过,不进行备份*/
if (mysql_get_server_version(mysql) >= FIRST_SYS_SCHEMA_VERSION &&
!my_strcasecmp(&my_charset_latin1, row[0], SYS_SCHEMA_DB_NAME))
continue;
if (is_ndbinfo(mysql, row[0]))
continue;
/* dump库中所有表 */
/* 逐一dump每个表 dump_all_tables_in_db */
if (dump_all_tables_in_db(row[0]))
result=1;
}
.........
/** 此处 --all-databases sys 库不会传入 dump_routines_for_db 这个函数。
所以函数里面的备份过程跳过了sys库,也就造成了.sql文件里mysql.proc没有CREATE ROUTINE sys库的现象 */
static uint dump_routines_for_db(char *db)
{
........
/* 0, retrieve and dump functions, 1, procedures */
for (i= 0; i <= 1; i++)
{
/* 执行SHOW FUNCTION/PROCEDURE STATUS WHERE Db = xx,获取所有functions和procedures */
my_snprintf(query_buff, sizeof(query_buff),
"SHOW %s STATUS WHERE Db = '%s'",
routine_type[i], db_name_buff);
if (mysql_query_with_error_report(mysql, &routine_list_res, query_buff))
DBUG_RETURN(1);
if (mysql_num_rows(routine_list_res))
{
while ((routine_list_row= mysql_fetch_row(routine_list_res)))
{
routine_name= quote_name(routine_list_row[1], name_buff, 0);
DBUG_PRINT("info", ("retrieving CREATE %s for %s", routine_type[i],
name_buff));
/* 执行SHOW CREATE FUNCTION/PROCEDURE xxx,获取所有functions、procedures创建语句 */
my_snprintf(query_buff, sizeof(query_buff), "SHOW CREATE %s %s",
routine_type[i], routine_name);
........