Error 1149: You have an error in your SQL syntax
Error Code | SQLSTATE | Error | Description |
---|---|---|---|
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 |
Possible Causes and Solutions
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, but here are a few common ones;
Delimiters
Delimiters need to be present at the end of each statement. See Delimiters. For example:
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 2
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:
SELECT 1; SELECT 2;
Delimiters in Stored Programs
When creating stored programs from the command-line, it is likely you will need to differentiate between the regular delimiter and a delimiter inside a BEGIN END block. See Delimiters.
For example, take the following, seemingly-valid declaration:
CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC BEGIN DECLARE x TINYINT; SET x = 42; RETURN x; END;
Attempting to run will result in an error:
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 3
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:
DELIMITER // CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC BEGIN DECLARE x TINYINT; SET x = 42; RETURN x; END // DELIMITER ;
Reserved Words
Reserved words cannot be used as identifiers unless they are quoted. For example:
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 1
The error was picked up after the accessible
identifier, leading one to suspect that it's causing the problem. And indeed, it's a reserved word. There are a number of possible solutions.
Use a new, non-reserved, identifier:
CREATE TABLE accessible_status(id INT);
or quote the identifier:
CREATE TABLE `accessible`(id INT);
or fully qualify the identifier:
CREATE TABLE test.accessible(id INT);