问题重现:前几天因为有人删除了数据库中的记录,今天关闭了数据库的远程访问功能,今天接到开发报告,说出现 The user specified as a definer (‘air’@'%’) does not exist错误,他们定位是一张视图不能访问,利用实验重现了他们的情况.
原因分析:因为创建视图使用的是xff@%用户(目前已经不存在),然后登录用户使用的是xff@localhost用户,导致mysql认为现在的用户无权限访问该视图,解决方法就是在当前用户下重建该视图.
我使用的代码,代码如下:
- [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)
1.注意事项
创建视图存在如下注意事项：
（1） 运行创建视图的语句需要用户具有创建视图（CRATE VIEW）的权限，若加了[OR REPLACE]时，还需要用户具有删除视图（DROP VIEW）的权限；
（2） SELECT语句不能包含FROM子句中的子查询；
（3） SELECT语句不能引用系统或用户变量；
（4） SELECT语句不能引用预处理语句参数；
（5） 在存储子程序内，定义不能引用子程序参数或局部变量；
（6） 在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句；
（7） 在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图；
（8） 在视图定义中命名的表必须已存在；
（9） 不能将触发程序与视图关联在一起；
（10） 在视图定义中允许使用ORDER BY，但是，如果从特定视图进行了选择，而该视图使用了具有自己ORDER BY的语句，它将被忽略。
补充一下mysql视图基本知识
创建视图——CREATE VIEW
1.语法,代码如下:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
通过该语句可以创建视图，若给定了[OR REPLACE]，则表示当已具有同名的视图时，将覆盖原视图。select_statement是一个查询语句，这个查询语句可从表或其它的视图中查询。视图属于数据库，因此需要指定数据库的名称，若未指定时，表示在当前的数据库创建新视图。
表和数据库共享数据库中相同的名称空间,因此,数据库不能包含相同名称的表和视图，并且,视图的列名也不能重复.
1.使用举例
Eg.本例创建一个产品表(product)和一个购买记录表(purchase),再通过视图purchase_detail查询出购买的详细信息,代码如下:
- CREATE TABLE product
- (
- product_id INT NOT NULL,
- name VARCHAR(50) NOT NULL,
- price DOUBLE NOT NULL
- );
- INSERT INTO product VALUES(1, 'apple ', 5.5);
- CREATE TABLE purchase
- (
- id INT NOT NULL,
- product_id INT NOT NULL,
- qty INT NOT NULL DEFAULT 0,
- gen_time DATETIME NOT NULL
- );
- INSERT INTO purchase VALUES(1, 1, 10, NOW());
- CREATE VIEW purchase_detail AS SELECT product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;
创建成功后,输入,SELECT * FROM purchase_detail;
运行效果如下:
- +
- | name | price | qty | total_value |
- +
- | apple | 5.5 | 10 | 55 |
- +
- 1 row in set (0.01 sec)
|
