1、问题重现
前几天因为有人删除了数据库中的记录，今天关闭了数据库的远程访问功能，今天接到开发报告，说出现 The user specified as a definer (‘air’@’%’) does not exist错误，他们定位是一张视图不能访问。利用实验重现了他们的情况
[root@ECP-UC-DB1 ~]# mysql -uxff -pxifenfei
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8846
Server version: 5.5.14-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user,host from mysql.user;
+------+---------------+
| user | host |
+------+---------------+
| xff | % |
| root | 127.0.0.1 |
| repl | 192.168.11.10 |
| root | ::1 |
| | ECP-UC-DB1 |
| root | ECP-UC-DB1 |
| root | localhost |
+------+---------------+
7 rows in set (0.08 sec)
mysql> use xifenfei;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create view v_users as select * from wp_users;
Query OK, 0 rows affected (0.14 sec)
mysql> select count(*) from xifenfei.v_users;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.03 sec)
mysql> update mysql.user set host='localhost' where user='xff' and host='%';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.12 sec)
mysql> exit
Bye
[root@ECP-UC-DB1 ~]# mysql -uxff -pxifenfei
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8847
Server version: 5.5.14-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use xff;
ERROR 1049 (42000): Unknown database 'xff'
mysql> use xifenfei;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from v_users ;
ERROR 1449 (HY000): The user specified as a definer ('xff'@'%') does not exist
2、解决方法
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | xifenfei | +--------------------+ 5 rows in set (0.00 sec) mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> desc VIEWS; +----------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+--------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | VIEW_DEFINITION | longtext | NO | | NULL | | | CHECK_OPTION | varchar(8) | NO | | | | | IS_UPDATABLE | varchar(3) | NO | | | | | DEFINER | varchar(77) | NO | | | | | SECURITY_TYPE | varchar(7) | NO | | | | | CHARACTER_SET_CLIENT | varchar(32) | NO | | | | | COLLATION_CONNECTION | varchar(32) | NO | | | | +----------------------+--------------+------+-----+---------+-------+ 10 rows in set (0.02 sec) mysql> select TABLE_SCHEMA,TABLE_NAME,DEFINER from views; +--------------+------------+---------+ | TABLE_SCHEMA | TABLE_NAME | DEFINER | +--------------+------------+---------+ | xifenfei | v_users | xff@% | +--------------+------------+---------+ 1 row in set (0.16 sec) mysql> create or replace view v_users as select * from wp_users; ERROR 1044 (42000): Access denied for user 'xff'@'localhost' to database 'information_schema' mysql> create or replace view xifenfei.v_users as select * from xifenfei.wp_users; Query OK, 0 rows affected (0.02 sec) mysql> select TABLE_SCHEMA,TABLE_NAME,DEFINER from views; +--------------+------------+---------------+ | TABLE_SCHEMA | TABLE_NAME | DEFINER | +--------------+------------+---------------+ | xifenfei | v_users | xff@localhost | +--------------+------------+---------------+ 1 row in set (0.01 sec) mysql> select count(*) from xifenfei.v_users; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.03 sec)
3、原因分析
因为创建视图使用的是xff@%用户(目前已经不存在)，然后登录用户使用的是xff@localhost用户，导致mysql认为现在的用户无权限访问该视图，解决方法就是在当前用户下重建该视图
加我QQ(107644445)
