Mysql create view error

MySQL Create View WITH CTE

I have successfully created a VIEW in SQL Server as follows. But when I try the same in MySql, Error coming.


1064 — You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘cte AS( SELECT a._Id, a._parentId, a._name, ‘ at line 1

Server version: 5.6.41-84.1 — Percona Server (GPL), Release 84.1, Revision b308619

2 Answers 2

You need to use 8.0 for CTE in MySQL

First of all, MySQL has been supporting CTEs since relatively recently, starting from version 8.0 (as has been mentioned by others). The syntax error you are getting seems to indicate that you are using an older version. Therefore, your database server requires an upgrade 1 .

Apart from that, there are issues with your CREATE VIEW statement that need to be addressed in order to make it work in MySQL.

Square bracket delimiters.

Use of square brackets to delimit names, like in the statement’s first line:

is specific to Transact-SQL. For MySQL, you need to replace them with either double quotes ( » ) or backticks ( ` ). Or, in this case, you can just omit them altogether, because the name contains only letters and an underscore, which are allowed to be used in names without delimiting.

Schema name.

In SQL Server, databases contain schemas, and schemas contain objects (tables, views, functions, stored procedures etc.). Therefore, the full name of an object consists of three parts:

When it only has two parts, like in this case ( [dbo].[vw_PurchParent] ), then it is interpreted as

However, MySQL does not support schemas in databases. When an object reference has two parts, it is interpreted as

Therefore, it is likely that you will want to drop the dbo. part of the view name when adjusting the script for MySQL.

Missing keyword RECURSIVE .

The CTE in this view’s definition is a recursive CTE 2 . MySQL supports recursive CTEs but, unlike SQL Server, it requires that the RECURSIVE keyword be specified when one or more CTEs in the WITH clause are recursive.

Therefore, the WITH line of the definition will need to be rewritten as

The GO keyword.

The GO keyword at the end of your script is specific to the SQL Server platform, although it is not part of the Transact-SQL dialect per se. Rather it is an instruction interpreted by some of SQL Server client tools (SSMS, sqlcmd, the now deprecated osql). It indicates the end of a batch of statements. In this case, it should simply be replaced with a semicolon ( ; ).

Note that it is also a good practice in general to always end a statement with a semicolon, which is the standard statement delimiter in SQL.

1 Alternatively, you could also consider switching to MariaDB. MariaDB is a fork of MySQL, and as such it is compatible with MySQL in many respects, including the SQL dialect aspect. At the same time, though, MariaDB is known to often introduce advanced language features – which includes CTE, window functions and others – before MySQL does. For instance, as already mentioned, CTEs have been introduced to MySQL in version 8.0, which was released one year ago (in April 2018). In contrast, MariaDB has been supporting CTEs for about 2-3 years, as of writing this. (CTEs were first added to version 10.2 in 2016; the version became a stable release in 2017.)

2 It represents a UNION ALL query with a self-reference in the second leg, which is the specific structure that a recursive CTE is required to have.


Mysql create view error

The CREATE VIEW statement creates a new view, or replaces an existing view if the OR REPLACE clause is given. If the view does not exist, CREATE OR REPLACE VIEW is the same as CREATE VIEW . If the view does exist, CREATE OR REPLACE VIEW replaces it.

Читайте также:  Error spawning command line dbus launch

For information about restrictions on view use, see Section 23.9, “Restrictions on Views”.

The select_statement is a SELECT statement that provides the definition of the view. (Selecting from the view selects, in effect, using the SELECT statement.) The select_statement can select from base tables or other views.

The view definition is “ frozen ” at creation time and is not affected by subsequent changes to the definitions of the underlying tables. For example, if a view is defined as SELECT * on a table, new columns added to the table later do not become part of the view, and columns dropped from the table result in an error when selecting from the view.

The ALGORITHM clause affects how MySQL processes the view. The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time. The WITH CHECK OPTION clause can be given to constrain inserts or updates to rows in tables referenced by the view. These clauses are described later in this section.

The CREATE VIEW statement requires the CREATE VIEW privilege for the view, and some privilege for each column selected by the SELECT statement. For columns used elsewhere in the SELECT statement, you must have the SELECT privilege. If the OR REPLACE clause is present, you must also have the DROP privilege for the view. If the DEFINER clause is present, the privileges required depend on the user value, as discussed in Section 23.6, “Stored Object Access Control”.

When a view is referenced, privilege checking occurs as described later in this section.

A view belongs to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, use db_name.view_name syntax to qualify the view name with the database name:

Unqualified table or view names in the SELECT statement are also interpreted with respect to the default database. A view can refer to tables or views in other databases by qualifying the table or view name with the appropriate database name.

Within a database, base tables and views share the same namespace, so a base table and a view cannot have the same name.

Columns retrieved by the SELECT statement can be simple references to table columns, or expressions that use functions, constant values, operators, and so forth.

A view must have unique column names with no duplicates, just like a base table. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. To define explicit names for the view columns, specify the optional column_list clause as a list of comma-separated identifiers. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement.

A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION , and subqueries. The SELECT need not even refer to any tables:

The following example defines a view that selects two columns from another table as well as an expression calculated from those columns:

A view definition is subject to the following restrictions:

The SELECT statement cannot refer to system variables or user-defined variables.

Within a stored program, the SELECT statement cannot refer to program parameters or local variables.

The SELECT statement cannot refer to prepared statement parameters.

Any table or view referred to in the definition must exist. If, after the view has been created, a table or view that the definition refers to is dropped, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement.

The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.

You cannot associate a trigger with a view.

Aliases for column names in the SELECT statement are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).

Читайте также:  Error in install packages unable to install packages

ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY .

For other options or clauses in the definition, they are added to the options or clauses of the statement that references the view, but the effect is undefined. For example, if a view definition includes a LIMIT clause, and you select from the view using a statement that has its own LIMIT clause, it is undefined which limit applies. This same principle applies to options such as ALL , DISTINCT , or SQL_SMALL_RESULT that follow the SELECT keyword, and to clauses such as INTO , FOR UPDATE , LOCK IN SHARE MODE , and PROCEDURE .

The results obtained from a view may be affected if you change the query processing environment by changing system variables:

The DEFINER and SQL SECURITY clauses determine which MySQL account to use when checking access privileges for the view when a statement is executed that references the view. The valid SQL SECURITY characteristic values are DEFINER (the default) and INVOKER . These indicate that the required privileges must be held by the user who defined or invoked the view, respectively.

If the DEFINER clause is present, the user value should be a MySQL account specified as ‘ user_name ‘@’ host_name ‘ , CURRENT_USER , or CURRENT_USER() . The permitted user values depend on the privileges you hold, as discussed in SectionВ 23.6, “Stored Object Access Control”. Also see that section for additional information about view security.

If the DEFINER clause is omitted, the default definer is the user who executes the CREATE VIEW statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.

Within a view definition, the CURRENT_USER function returns the view’s DEFINER value by default. For views defined with the SQL SECURITY INVOKER characteristic, CURRENT_USER returns the account for the view’s invoker. For information about user auditing within views, see SectionВ 6.2.18, “SQL-Based Account Activity Auditing”.

Within a stored routine that is defined with the SQL SECURITY DEFINER characteristic, CURRENT_USER returns the routine’s DEFINER value. This also affects a view defined within such a routine, if the view definition contains a DEFINER value of CURRENT_USER .

MySQL checks view privileges like this:

At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition. If the definition refers to a stored function, only the privileges needed to invoke the function can be checked. The privileges required at function invocation time can be checked only as it executes: For different invocations, different execution paths within the function might be taken.

The user who references a view must have appropriate privileges to access it ( SELECT to select from it, INSERT to insert into it, and so forth.)

When a view has been referenced, privileges for objects accessed by the view are checked against the privileges held by the view DEFINER account or invoker, depending on whether the SQL SECURITY characteristic is DEFINER or INVOKER , respectively.

If reference to a view causes execution of a stored function, privilege checking for statements executed within the function depend on whether the function SQL SECURITY characteristic is DEFINER or INVOKER . If the security characteristic is DEFINER , the function runs with the privileges of the DEFINER account. If the characteristic is INVOKER , the function runs with the privileges determined by the view’s SQL SECURITY characteristic.

Example: A view might depend on a stored function, and that function might invoke other stored routines. For example, the following view invokes a stored function f() :

Suppose that f() contains a statement such as this:

The privileges required for executing statements within f() need to be checked when f() executes. This might mean that privileges are needed for p1() or p2() , depending on the execution path within f() . Those privileges must be checked at runtime, and the user who must possess the privileges is determined by the SQL SECURITY values of the view v and the function f() .

Читайте также:  Php fatal error class ziparchive

The DEFINER and SQL SECURITY clauses for views are extensions to standard SQL. In standard SQL, views are handled using the rules for SQL SECURITY DEFINER . The standard says that the definer of the view, which is the same as the owner of the view’s schema, gets applicable privileges on the view (for example, SELECT ) and may grant them. MySQL has no concept of a schema “ owner ” , so MySQL adds a clause to identify the definer. The DEFINER clause is an extension where the intent is to have what the standard has; that is, a permanent record of who defined the view. This is why the default DEFINER value is the account of the view creator.

The optional ALGORITHM clause is a MySQL extension to standard SQL. It affects how MySQL processes the view. ALGORITHM takes three values: MERGE , TEMPTABLE , or UNDEFINED . For more information, see Section 23.5.2, “View Processing Algorithms”, as well as Section, “Optimizing Derived Tables and View References with Merging or Materialization”.

Some views are updatable. That is, you can use them in statements such as UPDATE , DELETE , or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable.

A generated column in a view is considered updatable because it is possible to assign to it. However, if such a column is updated explicitly, the only permitted value is DEFAULT . For information about generated columns, see Section, “CREATE TABLE and Generated Columns”.

The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true.

In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. The LOCAL keyword restricts the CHECK OPTION only to the view being defined. CASCADED causes the checks for underlying views to be evaluated as well. When neither keyword is given, the default is CASCADED .

Views created before MySQL 5.7.3 containing ORDER BY integer can result in errors at view evaluation time. Consider these view definitions, which use ORDER BY with an ordinal number:

In the first case, ORDER BY 2 refers to a named column y . In the second case, it refers to a constant 1. For queries that select from either view fewer than 2 columns (the number named in the ORDER BY clause), an error occurs if the server evaluates the view using the MERGE algorithm. Examples:

As of MySQL 5.7.3, to handle view definitions like this, the server writes them differently into the .frm file that stores the view definition. This difference is visible with SHOW CREATE VIEW . Previously, the .frm file contained this for the ORDER BY 2 clause:

As of 5.7.3, the .frm file contains this:

That is, for v1 , 2 is replaced by a reference to the name of the column referred to. For v2 , 2 is replaced by a constant string expression (ordering by a constant has no effect, so ordering by any constant works).

If you experience view-evaluation errors such as just described, drop and recreate the view so that the .frm file contains the updated view representation. Alternatively, for views like v2 that order by a constant value, drop and recreate the view with no ORDER BY clause.


Оцените статью