Syntax error or access violation: 1064 You have an error in your SQL In symfony

7
votes

When i run comand php bin/console doctrine:migration:migrate i got this error I don't know where is come from.

command line error : In AbstractMySQLDriver.php line 99:

An exception occurred while executing 'CREATE TABLE user (id INT AUTO_INCREMENT NOT NULL, email VARCHAR(180) NOT NU LL, roles JSON NOT NULL, password VARCHAR(255) NOT NULL, UNIQUE INDEX UNIQ_8D93D649E7927C74 (email), PRIMARY KEY(id )) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB':

SQLSTATE[42000]: Syntax error or access violation: 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, password VARCHAR(255) NOT NULL, UNIQUE INDEX UNIQ_8D93D649E7927C7' at line 1

In PDOConnection.php line 109:

SQLSTATE[42000]: Syntax error or access violation: 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, password VARCHAR(255) NOT NULL, UNIQUE INDEX UNIQ_8D93D649E7927C7' at line 1

In PDOConnection.php line 107:

SQLSTATE[42000]: Syntax error or access violation: 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, password VARCHAR(255) NOT NULL, UNIQUE INDEX UNIQ_8D93D649E7927C7' at line 1

My env my env

4
symfonysymfony4doctrine-migrations
What mysql version are you using? JSON was introduced in MySQL 5.7. – Andrei Dumitrescu-Tudor
10.1.25-MariaDB – Abdo Abo
JSON was added in MariaDB 10.2.7 as per mariadb.com/kb/en/library/json-data-type – Andrei Dumitrescu-Tudor

4 Answers

10
votes

Andrei is right. You either have to upgrade your database or (much easier) config your symfony to use the lower Version of MySQL. 

config/packages/doctrine.yaml

doctrine:
dbal:
    # configure these for your database server
    driver: 'pdo_mysql'
    server_version: '5.6'

here you can find a table with the compatibilitys of MySQL and MariaDB.

0
votes

Update your MySQL's version to MySQL 5.7. Because your current version does not support MySQL 5.7's JSON objects.

0
votes

In this case you can simply paste this code in config/packages/doctrine.yaml :

doctrine:
  dbal:
    # configure these for your database server
    driver: 'pdo_mysql'
    server_version: '5.5'
-1
votes

Please note that the version information can conflict with the one defined in the database URL

If you added the server version information to the doctrine.yaml and the problem persisted, try checking "serverVersion" at the end if your database URL and set it to 5.6 or the compatible vresion you found