Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

After upgrade to laravel 5.3 error invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00'

I upgraded my project to laravel 5.3 from 5.2. Now when I want run php artisan migrate I receive error:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at' at row 1 (SQL: alter table messages add deleted_at timestamp null).

My migration:

  Schema::table(Models::table('messages'), function (Blueprint $table) {
        $table->softDeletes();
  });

In Blueprint.php:

    public function softDeletes()
    {
        return $this->timestamp('deleted_at')->nullable();
    }
like image 374
Igor Ostapiuk Avatar asked Dec 22 '17 19:12

Igor Ostapiuk


2 Answers

Laravel 5.3 was updated to use MySQL "strict" mode by default, which includes the NO_ZERO_DATE mode.

The issue is that your existing data was allowed to have '0000-00-00 00:00:00' as a datetime value. But, now your connection is using a sql mode that does not allow that value (NO_ZERO_DATE). When you attempt to alter the table to add the deleted_at column, it is complaining about the existing data violations in the created_at column.

The ideal solution would be to fix all the data in the database. That is, go through your database and update your datetime/timestamp fields so that they are nullable, and convert their data from '0000-00-00 00:00:00' to null.

However, the quick option is to just disable "strict" mode on your database connection. Open your config/database.php file, and make sure your database connection shows 'strict' => false.

like image 114
patricus Avatar answered Sep 17 '22 13:09

patricus


I've had the same problem when adding a datetime column in a table with timestamps (created and updated_at).

I solved just adding the '->nullable($value = true)' modifier in my migration:

$table->datetime('expires_at')->nullable($value = true);

I understood that the database complained about inserting all that zeros if the input dont come. Add the instruction to be nullable did the trick.

like image 39
mukade Avatar answered Sep 20 '22 13:09

mukade