Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Corbin's avatar

PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; (MySQL 5.5.42. and json not null)

I'm using MAMP and MYSQL I seem to be having trouble with JSON columns.

I am using MAMP version 3.4 and MySQL version 5.5.42. I'm trying to migrate Laravel media libraries package:

 Illuminate\Database\QueryException  : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'json not null, `custom_properties` json not null, `responsive_images` json not n' at line 1 (SQL: create table `media` (`id` int unsigned not null auto_increment primary key, `model_type` varchar(191) not null, `model_id` bigint unsigned not null, `collection_name` varchar(191) not null, `name` varchar(191) not null, `file_name` varchar(191) not null, `mime_type` varchar(191) null, `disk` varchar(191) not null, `size` int unsigned not null, `manipulations` json not null, `custom_properties` json not null, `responsive_images` json not null, `order_column` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

  at /Applications/MAMP/htdocs/community/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668| 

  Exception trace:

  1   PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'json not null, `custom_properties` json not null, `responsive_images` json not n' at line 1")
      /Applications/MAMP/htdocs/community/vendor/laravel/framework/src/Illuminate/Database/Connection.php:452

  2   PDO::prepare("create table `media` (`id` int unsigned not null auto_increment primary key, `model_type` varchar(191) not null, `model_id` bigint unsigned not null, `collection_name` varchar(191) not null, `name` varchar(191) not null, `file_name` varchar(191) not null, `mime_type` varchar(191) null, `disk` varchar(191) not null, `size` int unsigned not null, `manipulations` json not null, `custom_properties` json not null, `responsive_images` json not null, `order_column` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'")
      /Applications/MAMP/htdocs/community/vendor/laravel/framework/src/Illuminate/Database/Connection.php:452
0 likes
4 replies
jlrdw's avatar

Have you checked over all the errors that the error message is calling out to you.

It's telling you what's wrong.

Corbin's avatar

@jlrdw, I have looked through:

/Applications/MAMP/htdocs/community/vendor/laravel/framework/src/Illuminate/Database/Connection.php:452

and

/Applications/MAMP/htdocs/community/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664

But I'm 100% I just figured it out. I was under the assumption that json columns were fine for the version I have with MySQL and even saw that it should be. Nope, needs 5.7.

jlrdw's avatar

/Applications/MAMP/htdocs/community/vendor/laravel/framework/src/Illuminate/Database/Connection.php:452

and

/Applications/MAMP/htdocs/community/vendor/laravel/framework/src/Illuminate/Database/Connection.php:452

Isn't the error, that just means that the code did not go through those. Or those line could not process something due to an error.

It says your error

check the manual that corresponds to your MySQL server version for the right syntax to use near 'json not null, custom_properties json not null, responsive_images json not n' at line 1

near 'json not null, custom_properties json not null, responsive_images json not n' at line 1

Your sql is not right check it re-write as needed.

Try shorter query and add to it till error happens and you will see where it is.

Corbin's avatar
Corbin
OP
Best Answer
Level 9

Update verison of mysql to mysql 5.7.

Please or to participate in this conversation.