Mysql查询视图：ERROR 1449

1、问题重现
前几天因为有人删除了数据库中的记录，今天关闭了数据库的远程访问功能，今天接到开发报告，说出现 The user specified as a definer (‘air’@'%’) does not exist错误，他们定位是一张视图不能访问。利用实验重现了他们的情况

[root@ECP-UC-DB1 ~]# mysql -uxff -pxifenfei

Welcome tothe MySQL monitor.  Commands endwith ; or\g.

Your MySQL connectionid is 8846

Server version: 5.5.14-log MySQL Community Server (GPL)

  

Copyright (c) 2000, 2010, Oracleand/orits affiliates. Allrights reserved.

  

Oracle isa registered trademark ofOracle Corporation and/orits

affiliates. Other names may be trademarksof their respective

owners.

  

Type 'help;'or '\h' for help. Type '\c'to clear the currentinput statement.

  

mysql> selectuser,host frommysql.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 rowsin set (0.08 sec)

  

mysql> use xifenfei;

Reading tableinformation for completion of tableand column names

You can turn off this feature to get a quicker startupwith -A

  

Databasechanged

  

mysql> createview v_users asselect * fromwp_users;

Query OK, 0 rows affected (0.14 sec)

  

mysql> selectcount(*) fromxifenfei.v_users;

+----------+

| count(*) |

+----------+

|        2 |

+----------+

1 row inset (0.03 sec)

  

mysql> updatemysql.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 tothe MySQL monitor.  Commands endwith ; or\g.

Your MySQL connection id is8847

Server version: 5.5.14-log MySQL Community Server (GPL)

  

Copyright (c) 2000, 2010, Oracleand/orits affiliates. Allrights reserved.

  

Oracle isa registered trademark ofOracle Corporation and/orits

affiliates. Other names may be trademarksof their respective

owners.

  

Type 'help;'or '\h' for help. Type '\c'to clear the currentinput statement.

  

mysql> use xff;

ERROR 1049 (42000): Unknowndatabase 'xff'

mysql> use xifenfei;

Reading tableinformation for completion of tableand column names

You can turn off this feature to get a quicker startupwith -A

  

Databasechanged

mysql> select* from v_users ;

ERROR 1449 (HY000): Theuser specified as a definer ('xff'@'%') doesnot exist

2、解决方法

mysql> show databases;

+--------------------+

| Database          |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| xifenfei           |

+--------------------+

5 rowsin set (0.00 sec)

  

mysql> use information_schema;

Reading tableinformation for completion of tableand column names

You can turn off this feature to get a quicker startupwith -A

  

Databasechanged

  

mysql> descVIEWS;

+----------------------+--------------+------+-----+---------+-------+

| 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 rowsin set (0.02 sec)

  

mysql> selectTABLE_SCHEMA,TABLE_NAME,DEFINER fromviews;

+--------------+------------+---------+

| TABLE_SCHEMA | TABLE_NAME | DEFINER |

+--------------+------------+---------+

| xifenfei     | v_users    | xff@%   |

+--------------+------------+---------+

1 row inset (0.16 sec)

  

mysql> createor replace view v_users asselect * fromwp_users;

ERROR 1044 (42000): Access deniedfor user 'xff'@'localhost'to database 'information_schema'

mysql> createor replace view xifenfei.v_users asselect * fromxifenfei.wp_users;

Query OK, 0 rows affected (0.02 sec)

  

mysql> selectTABLE_SCHEMA,TABLE_NAME,DEFINER fromviews;

+--------------+------------+---------------+

| TABLE_SCHEMA | TABLE_NAME | DEFINER       |

+--------------+------------+---------------+

| xifenfei     | v_users    | xff@localhost |

+--------------+------------+---------------+

1 row inset (0.01 sec)

  

mysql> selectcount(*) fromxifenfei.v_users;

+----------+

| count(*) |

+----------+

|        2 |

+----------+

1 row inset (0.03 sec)

3、原因分析
因为创建视图使用的是xff@%用户(目前已经不存在)，然后登录用户使用的是xff@localhost用户，导致mysql认为现在的用户无权限访问该视图，解决方法就是在当前用户下重建该视图