#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server

Question

I was trying to run following Query on my sql server :

CREATE TABLE `e_store`.`products`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(250) NOT NULL ,
    `brand_id` INT UNSIGNED NOT NULL ,
    `category_id` INT UNSIGNED NOT NULL ,
    `attributes` JSON NOT NULL ,
    PRIMARY KEY(`id`) ,
    INDEX `CATEGORY_ID`(`category_id` ASC) ,
    INDEX `BRAND_ID`(`brand_id` ASC) ,
    CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
    CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

I have already brands and categories tables on my e_store database.

But I got the following Error :

#1064 - 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 'JSON NOT NULL ,
    PRIMARY KEY(`id`) ,
    INDEX `CATEGORY_ID`('category_id' ' at line 6

@denny MariaDB is still close enough dialectically. This is a rare divergence — Strawberry, Feb 12 '17 at 08:43

score 17 · Answer 1 · edited Nov 01 '21 at 18:01

For those who are facing this issue similar to me:

MariaDB does not natively implement the JSON data type but it uses it as an alias for LONGTEXT for compatibility reasons. According to the documentation (https://mariadb.com/kb/en/library/json-data-type/):

JSON is an alias for LONGTEXT introduced for compatibility reasons with MySQL's JSON data type. MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at least equivalent.

In order to ensure that a a valid json document is inserted, the JSON_VALID function can be used as a CHECK constraint.

So if you are having issues with the JSON data type in MariaDB, simply just change to LONGTEXT. ;-)

score 3 · Answer 2 · answered Feb 12 '17 at 09:09

3

I think you are getting error for JSON datatype.

For Mysql 5.7 you can get help from below link.

https://dev.mysql.com/doc/refman/5.7/en/json.html

You can check vesrion using below query.

select version() as 'mysql version'

answered Feb 12 '17 at 09:09

Tajinder

2,248
4
33
54

@scaisEdge the [JSON *table* type](https://mariadb.com/kb/en/mariadb/connect-json-table-type/) in MariaDB 10.0.16 is completely unrelated to JSON columns. – Michael - sqlbot Feb 12 '17 at 13:20

score 3 · Answer 3 · answered Feb 12 '17 at 20:49

3

"JSON" is parsed in the server. JSON is one of the points of divergence.

MySQL 5.7 introduced the JSON datatype, which matches your syntax.

MariaDB 10.0.16 introduced a ENGINE=CONNECT table_type=JSON which does not match your attempted syntax.

answered Feb 12 '17 at 20:49

Rick James

135,179
13
127
222

score 1 · Answer 4 · answered Feb 12 '17 at 08:56

You have given single quotes in your index definitions instead of backticks

Try this:

CREATE TABLE `e_store`.`products`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(250) NOT NULL ,
    `brand_id` INT UNSIGNED NOT NULL ,
    `category_id` INT UNSIGNED NOT NULL ,
    `attributes` JSON NOT NULL ,
    PRIMARY KEY(`id`) ,
    INDEX `CATEGORY_ID`(`category_id` ASC) ,  -- Changed single quotes to backticks
    INDEX `BRAND_ID`(`brand_id` ASC) ,   -- Changed single quotes to backticks
    CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
    CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

that's not the solution I guess, because I have already checked with that ..nothing came out. ..means same error i'm getting — User57, Feb 12 '17 at 08:59

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server

4 Answers4

Linked