CODEWITHSUNDEEP
laraveldatetimestampmigration
Igor Ostapiuk
Igor Ostapiuk

Reputation: 619

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();
    }

Upvotes: 20

Views: 23890

Answers (5)

Timothy Mach
Timothy Mach

Reputation: 171

Basically, running an update Migration tries to add a default deadline of 0000-00-00 00:00:00 to the existing records. However, mysql => 5.7 is strict hence does not permit this.

Solution

  1. Fastest way is to empty the table (if the data is not very important)
  2. Add. a default time now() when creating the migration like:
    $table->dateTime('deadline')->default(now());
  3. Make it a nullable field.
  4. or go with @Hossein's answer above

Upvotes: 0

Hossein Bagherzadegan Talkhoun
Hossein Bagherzadegan Talkhoun

Reputation: 21

This error Cause by STRICT_TRANS_TABLES and NO_ZERO_DATE in sqlmode for mysql 5.7

disabled it and in this way all things work correctly .

Upvotes: 2

Roger Thomas Riche
Roger Thomas Riche

Reputation: 41

I like creating the default as NOW() anyways.

So $table->datetime('expires_at')->default(NOW()); is also a good option.

Upvotes: 4

mukade
mukade

Reputation: 660

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.

Upvotes: 5

patricus
patricus

Reputation: 62278

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.

Upvotes: 43

Related Questions