MySQL 打开视图 1449_Mysql查询视图：ERROR 1449 (HY000)

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