Laravel 5.3 - SQLSTATE[HY000]: General error: 1366 Incorrect integer value

Question

Why is it so that every time I try to post an empty input that has an integer as data type within my migration(s), I get this error:

SQLSTATE[HY000]: General error: 1366 Incorrect integer value

My migration:

public function up()
{
    Schema::table('dossiers', function (Blueprint $table) {             
        $table->integer('amount_of_cilinders')->nullable();         
        $table->integer('amount_of_doors')->nullable();            
        $table->integer('manufacturing_year')->nullable();          
        $table->integer('manufacturing_month')->nullable();
        $table->date('date_first_admission')->nullable();                         
        $table->integer('weight')->nullable();                                            
        $table->integer('power')->nullable();      
    });
}

My controller:

public function update(Request $request, Dossier $dossier)
{
    $this->validate($request, [
        'date_first_admission' => 'date',
    ]);

    $dossier->update($request->all());        

    return redirect('/dossiers/' . $dossier->id);
}

Update:

I decided to change the datatype to string since I'm not using these columns anywhere else..

E.g. $table->string('power')->nullable();

Answer 1

This may be happening because of MySQL is running in strict mode.

Run the following queries in the MySQL CLI, to check if the database is in strict mode:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

If it has something containing STRICT_TRANS_TABLES you could try and run:

SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Answer 2

Null doesn't mean empty string. Either add ->default(0) to all your columns in your migration.

$table->integer('power')->default(0)->nullable(); 

OR

Use a mutator in your model, do a check before you pass the value:

public function setPowerAttribute($value){
  $this->attributes['power'] = (empty($value) ? 0 : $value);
}

Answer 3

When you use nullable(), means default set NULL. So make it default 0 as like below:

$table->integer('COLUMNNAME')->default(0);

Answer 4

I may be completely off, but AFAIK empty values are invalid in numerical fields. Probably the value returned by the input is an empty string if you do not enter anything. This would trigger the SQL error, since you may either

• pass a valid numerical value, like "0", oder
• pass a NULL value (if nullable is true - see SQL "three state" logic)

An empty string is neither 0, nor is it NULL.

Maybe this article helps you, it deals with a similar oddity when using validation rules, I guess this could be another apperance of this behavior.

https://github.com/laravel/framework/issues/11452

Armin.