MySQL Query view: ERROR 1449 (HY000) solution

Recurring problem

A few days ago because someone deleted the records in the database, today shut down the remote access function of the database, received a development report today, said that the user specified as a definer (' air ' @ '% ') does not exist error, They locate a view that cannot be accessed. Using experiments to recreate their situation.

cause Analysis

Because the view is created using the xff@% user (no longer exists), and then the logged-on user is using the Xff@localhost user, MySQL believes that the current user does not have permission to access the view, the solution is to rebuild the view under the present user

The code I use

The code is as follows

Copy Code

[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), the Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark the Oracle Corporation and/or its Affiliates. Names may 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 the 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), the Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark the Oracle Corporation and/or its Affiliates. Names may 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 the 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. Solution method

The code is as follows

Copy Code

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 the 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 |         |       | | +----------------------+--------------+------+-----+---------+-------+ 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. Matters needing attention

Creating a view has the following considerations:
(1) The statement that runs the CREATE view requires the user to have permission to create the view (Crate view), and if [or REPLACE] is added, the user has permission to delete the view (drop view);
(2) A SELECT statement cannot contain a subquery in the FROM clause;
(3) A SELECT statement cannot refer to a system or user variable;
(4) A SELECT statement cannot reference a preprocessing statement parameter;
(5) Within the storage subroutine, the definition cannot refer to subroutine parameters or local variables;
(6) The table or view referenced in the definition must exist. However, after you create a view, you can discard the table or view that defines the reference. To check whether the view definition has such a problem, use a check table statement;
(7) The temporary table cannot be referenced in the definition, and the temporary view cannot be created;
(8) The table named in the view definition must already exist;
(9) The trigger cannot be associated with the view;
(10) The order by is allowed in the view definition, but is ignored if a selection is made from a particular view that uses a statement with its own order.

Add the basics of MySQL view

Create views--create view
1. Grammar

The code is as follows	Copy Code
CREATE [OR REPLACE] [algorithm = {UNDEFINED | MERGE | TempTable}] VIEW [db_name.] view_name [(column_list)] as select_statement [with [cascaded | Local] CHECK OPTION

This statement allows you to create a view that, given [OR REPLACE], overwrites the original view when it already has a view with the same name. Select_statement is a query statement that can be queried from a table or other view. The view belongs to the database, so you need to specify the name of the database, and if not specified, the new view is created in the current database.
Tables and databases share the same namespaces in the database, so the database cannot contain tables and views of the same name, and the view's column names cannot be duplicated.
1. Use examples
Eg. This example creates a product table (products) and a Purchase record table (purchase), and then queries the purchase details through the view Purchase_detail.

code is as follows

copy code

create TABLE Product ( product_id INT not NULL, name VARCHAR (x) 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, and now ()); CREATE VIEW Purchase_detail as SELECT product.name as name, product. Price as Price, Purchase.qty as qty, product. PR Ice * Purchase.qty as Total_value from product, purchase where product.product_id = purchase.product_id;

Upon successful creation, enter: SELECT * from Purchase_detail;
The operation effect is as follows:

The code is as follows	Copy Code
+-------+-------+-----+-------------+ | name | Price | Qty | Total_value | +-------+-------+-----+-------------+ | Apple | 5.5 | 10 | 55 | +-------+-------+-----+-------------+ 1 row in Set (0.01 sec)