Problem with import SQL queries to phpmyadmin

Question

I have this mysql:

CREATE TABLE security_user
(
    id INT AUTO_INCREMENT NOT NULL,
    email VARCHAR(180) NOT NULL,
    roles JSON NOT NULL,
    password VARCHAR(255) NOT NULL,
    UNIQUE INDEX UNIQ_52825A88E7927C74 (email),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB

When I try use it in php error I have error:

1064 - Something is wrong in your syntax obok 'JSON NOT NULL, password VARCHAR(255) NOT NULL, UNIQUE INDEX UNIQ_52825A88E7927C7' in line 1

I have installed Xampp Server: https://www.apachefriends.org/pl/download.html

How can I repair this?

'As of MySQL 5.7.8, MySQL supports a native JSON data type' dev.mysql.com/doc/refman/5.7/en/json.html — P.Salmon
– P.Salmon, Commented Apr 29, 2019 at 9:16
XAMPP is using MariaDB 10.1.X - JSON is available from MariaDB 10.2 - Check you exact version with SELECT VERSION() — Paul Spiegel
– Paul Spiegel, Commented Apr 29, 2019 at 9:17
Then you are lucky, because now you have more reasons to create a clean normalized design. Don't store roles in a JSON array. Create a many-to-many relation instead. — Paul Spiegel
– Paul Spiegel, Commented Apr 29, 2019 at 9:47

Cid · Accepted Answer · 2019-04-29 12:17:34Z

1

The current version (actually 7.1.28, 7.2.17 and 7.3.4) of XAMPP is using MariaDB 10.1.38. This version doesn't support the JSON datatype. At least version 10.2.7 is required (see JSON Data Type).

In your case I would consider to define a clean normalized relation (user <--> role) instead of of storing the roles in a JSON array.

edited Apr 29, 2019 at 12:17

Cid

15.3k4 gold badges33 silver badges49 bronze badges

answered Apr 29, 2019 at 10:24

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cid Over a year ago

Paul, I edited your answer and added the current version (as the date of today, april the 29th 2019) of XAMPP (This answer may change on futurs versions)

Paul Spiegel Over a year ago

@Cid Tanks - That's more accurate then just "current".

Collectives™ on Stack Overflow

Problem with import SQL queries to phpmyadmin

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related