[FIXED] Error: "SQLSTATE[42000]: Syntax error or access violation: 1064

Issue

I got a Laravel MySQL migration problem.

Migration:

 public function up()
{
    Schema::create('capsule_packages', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->tinyInteger('type')->unsigned();
        $table->json('data')->nullable();
        $table->decimal('price', 19, 4);
        $table->text('description')->nullable();
        $table->timestamps();
    });
}

Error:

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 null, price decimal(19, 4) not null, description text null, created_at' at line 1 (SQL: create tablecapsule_packages(idint unsigned not null auto_increment primary key,namevarchar(255) not null,typetinyint unsigned not null,datajson null,pricedecimal(19, 4) not null,descriptiontext null,created_attimestamp null,updated_at` timestamp null) default character set utf8 collate 'utf8_unicode_ci')

Solution

This is probably due to your MySQL version. Did some digging around, and seems like since Laravel 5.2, the $table->json() method will try to create an actual json field in the database. But, in your example, the json ins't available in your current MySQL version. You can use this field only from MySQL version 5.7.8. and higher. If you're using version that's lower than this, you can resolve this error by creating a text field, instead of json.

You can see this in you error code:

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

Source: dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html#mysqld-5-7-8-json

EDIT:

As per MariaDB version 10.1.x and lower does not support json data type

Source: github.com/laravel/framework/issues/13622

Answered By - zlatan