Mysql查询视图：ERROR 1449

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认为现在的用户无权限访问该视图，解决方法就是在当前用户下重建该视图