All pages
Powered by GitBook
1 of 1

Loading...

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 (and in most cases they're simple typos), but here are a few common ones;

Delimiters

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:

Delimiters in Stored Programs

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:

Reserved Words

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:

Syntax Doesn't Match SQL_MODE

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:

Oracle 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.

Delimiters
stored programs
BEGIN END
Delimiters
Reserved words
identifiers
reserved word
fully qualify
SQL_MODE
SQL_MODE
quote identifiers
Oracle mode
SQL_MODE
Oracle mode
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
SELECT 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 3
DELIMITER //

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 1
CREATE 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 1
select @@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 1
SELECT @@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