事件:前段时间把多个测试库统一迁移到新的数据库里,也没在意view啥的,直接导出,导入。然而,在新库上使用mysqldump备份的时候,发生了如下报错:
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SHOW FIELDS FROM `v_xxxxx`': SELECT command denied to user ''@'%' for column 'GroupID' in table 'xxxxx' (1143)
刚开始百思不得其解,我是用root用户dump的,怎么会有这样报错?
经过一番思考和百度,最终总结出下面的解决方法:
mysql> select * from information_schema.tables where TABLE_NAME LIKE '%v_xxxxx%' AND table_type='view'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: xxx_db
TABLE_NAME: v_xxxxx
TABLE_TYPE: VIEW
ENGINE: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: NULL
AUTO_INCREMENT: NULL
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: NULL
CHECKSUM: NULL
CREATE_OPTIONS: NULL
TABLE_COMMENT: VIEW
1 row in set (0.00 sec)
mysql> use xxx_db;
Database changed
mysql> show create view v_xxxxx\G
*************************** 1. row ***************************
View: v_xxxxx
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`user_name`@`%` SQL SECURITY DEFINER VIEW `v_xxxxx` ......
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
#从上面输出可以看出此view与user_name用户有关,此时去mysql.user表查看是否存在该用户,如果不存在,就需要新建一个名为user_name的用户,并授权。问题解决。
#也有可能是因为create view时相关的表缺少字段导致。
欢迎各位大佬批评指正。