1149
42000
ER_SYNTAX_ERROR
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use
This is one of the most common errors to see. It's caused by an SQL syntax error. The error message will give you a clue as to where the error could be, as MariaDB displays the text following where it picked up an error. There are a huge number of possible causes (and in most cases they're simple typos), but here are a few common ones;
Delimiters need to be present at the end of each statement. See . For example:
Here, there was no delimiter separating the first and the second line. MariaDB picked up the error at the start of the second line, pointing one to the end of the first line. The correct syntax is:
When creating from the command-line, it is likely you will need to differentiate between the regular delimiter and a delimiter inside a block. See .
For example, take the following, seemingly-valid declaration:
Attempting to run will result in an error:
The error was picked up at the end of the DECLARE x TINYINT; line, guiding one to the delimiter found there. The solution is to specify a distinct delimiter for the duration of the process, using the DELIMITER command, for example:
cannot be used as unless they are quoted. For example:
The error was picked up after the accessible identifier, leading one to suspect that it's causing the problem. And indeed, it's a . There are a number of possible solutions.
Use a new, non-reserved, identifier:
or quote the identifier:
or the identifier:
Some syntax may be valid in a particular , but not in others. There are too many to list here, but the page provides a full list. For example:
The error message directs one to the identifier t. Double quotes cannot be used to unless ANSI_MODE is set, which in this case it isn't:
Either quote with the standard quote character, the backtick, or set the appropriate SQL_MODE:
in particular has a large number of differences to the default , for example:
The error is identified just after the VARCHAR2 type is specified, and this type is not valid in the default mode:
See for a full list of syntax differences.
SELECT 1
SELECT 2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MariaDB server version for the right syntax to use near 'SELECT 2' at line 2SELECT 1;
SELECT 2;CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
DECLARE x TINYINT;
SET x = 42;
RETURN x;
END;CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
-> BEGIN
-> DECLARE x TINYINT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MariaDB server version for the right syntax to use near '' at line 3DELIMITER //
CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
DECLARE x TINYINT;
SET x = 42;
RETURN x;
END
//
DELIMITER ;CREATE TABLE accessible(id INT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MariaDB server version for the right syntax to use near 'accessible(id INT)' at line 1CREATE TABLE accessible_status(id INT);CREATE TABLE `accessible`(id INT);CREATE TABLE test.accessible(id INT);CREATE TABLE "t" (id INT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MariaDB server version for the right syntax to use near '"t" (id INT)' at line 1select @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',ANSI_QUOTES');
CREATE TABLE "t" (id INT);CREATE TABLE t (id VARCHAR2(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MariaDB server version for the right syntax to use near '(10))' at line 1SELECT @@sql_mode;
+--------------------------------------------------------------------+
| @@sql_mode |
+--------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER |
+--------------------------------------------------------------------+
SET SQL_MODE='ORACLE';
CREATE TABLE t (id VARCHAR2(10));This page is licensed: CC BY-SA / Gnu FDL