SQLSTATE[42000]: Syntax error or access violation: 1064 in Laravel and MariaDB error in migrations

Question

Environment:

Laravel 5.5.44

MariaDB 10.4.7

I am trying to execute the migrations, and it gives me the following error but only in one table. The migration code is as follows:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Eloquent\SoftDeletes;

class CreateVehicleTable extends Migration
{

    use SoftDeletes;

    public function up()
    {
        Schema::create('vehicles', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('risk_id')->unsigned()->nullable();
            $table->integer('b7code')->unsigned()->nullable();
            $table->integer('b7type')->unsigned()->nullable();
            $table->string('b7class', 2)->nullable();
            $table->string('b7pgclass', 2)->nullable();
            $table->string('brand', 50)->nullable();
            $table->string('model', 100)->nullable();
            $table->string('version', 250)->nullable();
            $table->string('plate', false)->nullable();
            $table->integer('price')->unsigned()->nullable()->default(null);
            $table->string('frame', 100)->nullable();
            $table->integer('power')->unsigned()->nullable()->default(null);
            $table->integer('engine_capacity')->unsigned()->nullable()->default(null);
            $table->integer('vehicle_category_id')->unsigned()->nullable();
            $table->date('registration_date')->nullable();
            $table->char('usage', 1)->default('P')->nullable();
            $table->integer('circulation_region_id')->unsigned()->nullable();
            $table->string('accessories', false)->nullable();
            $table->timestamps();
            $table->softDeletes();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('vehicles');
    }
}

The result of the execution is as follows

In Connection.php line 664:

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 us  
  e near ') default character set latin1 collate latin1_general_ci' at line 1 (SQL: create table `vehicles` () default character set latin1 collate latin1_general_ci)  

In Connection.php line 452:

 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 us  
  e near ') default character set latin1 collate latin1_general_ci' at line 1

Answer 1

I ran the migration in Laravel and it worked fine. You might want to catch the full query and see what that tells you.

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Eloquent\SoftDeletes;

use Illuminate\Support\Facades\DB;

class CreateVehicleTable extends Migration
{

     use SoftDeletes;

    public function up()
    {
        Schema::create('vehicles', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('risk_id')->unsigned()->nullable();
            $table->integer('b7code')->unsigned()->nullable();
            $table->integer('b7type')->unsigned()->nullable();
            $table->string('b7class', 2)->nullable();
            $table->string('b7pgclass', 2)->nullable();
            $table->string('brand', 50)->nullable();
            $table->string('model', 100)->nullable();
            $table->string('version', 250)->nullable();
            $table->string('plate', false)->nullable();
            $table->integer('price')->unsigned()->nullable()->default(null);
            $table->string('frame', 100)->nullable();
            $table->integer('power')->unsigned()->nullable()->default(null);
            $table->integer('engine_capacity')->unsigned()->nullable()>default(null);
            $table->integer('vehicle_category_id')->unsigned()->nullable();
            $table->date('registration_date')->nullable();
            $table->char('usage', 1)->default('P')->nullable();
            $table->integer('circulation_region_id')->unsigned()->nullable();
            $table->string('accessories', false)->nullable();
            $table->timestamps();
            $table->softDeletes();

            DB::listen(function($query) {           
                var_dump( $query->sql . ' [' . implode(', ', $query->bindings) . 
            ']');

            }); 

         });
    }

You should get this (in your case with different collation) :

create table `vehicles` 
(`id` int unsigned not null auto_increment primary key,
`risk_id` int unsigned null,
`b7code` int unsigned null,
`b7type` int unsigned null,
`b7class` varchar(2) null,
`b7pgclass` varchar(2) null,
`brand` varchar(50) null,
`model` varchar(100) null,
`version` varchar(250) null,
`plate` varchar(191) null,
`price` int unsigned null,
`frame` varchar(100) null,
`power` int unsigned null,
`engine_capacity` int unsigned null,
`vehicle_category_id` int unsigned null, 
`registration_date` date null, 
`usage` char(1) null default 'P',
`circulation_region_id` int unsigned null,
`accessories` varchar(191) null,
`created_at` timestamp null,
`updated_at` timestamp null,
`deleted_at` timestamp null)
default character set utf8mb4 collate 'utf8mb4_unicode_ci'

Run this directly in mysql or phpMyAdmin that might make it easier for you to debug it.