framework: Invalid default value for 'created_at'

Ah, I have found the issue a fix, probably.

My “old” database.php config file does not have strict set to false ('strict' => false) as laravel/laravel does. When I add that line to the mysql config, all migrations work just fine (in my case)…

Edit: I noticed it doesn’t matter what the value of strict is, as long as it’s present it won’t fail (checked on clean Laravel install).

For me on all my apps the solution was to set strict to true in my MySQL config (in config/database.php).

    'connections' => [

        'mysql' => [

            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => env('DB_DATABASE', 'homestead'),
            'username'  => env('DB_USERNAME', 'homestead'),
            'password'  => env('DB_PASSWORD', 'secret'),
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
            'strict'    => true

        ]

    ],

@Stayallive

Wow. Doing exactly the opposite seems to fix the issue on my side. This setting was already set to false (its default value) and switching it to true allows my migrations to run without any problem.

Now I don’t understand anything any more…

@kingpabel Did you read my initial post? That’s exactly what I said. Disabling NO_ZERO_DATE is not a good solution - dates should never be 0, why would I allow it?

Only way to properly fix is to override the blue print class and fix the timestamps() method. I don’t know why Laravel’s default behavior is to punish us for using strict settings.

I have the same problem after upgrade. My solution:

• use nullableTimestamps() instead of timestamps()
• for custom timestamps add nullable , ex: timestamp('xxxx')->nullable()

Migrating old tables works like that:

Schema::table(
            'table',
            function (Blueprint $table) {
                $table->dateTime('created_at')->nullable()->default(NULL)->change();
                $table->dateTime('updated_at')->nullable()->default(NULL)->change();
            }
        );

This is due to MySQL not accepting zero as a valid defalt date and thus the table creation fails a constraint check on creation.

You probably have NO_ZERO_DATE enabled in your MySQL configuration. Setting this to off will allow you to create the table (or alternatively remove the default 0 value or change it to CURRENT_TIMESTAMP.

May this help?

sudo vi /etc/mysql/my.cnf

add (in [mysqld] section) ​sql_mode=“ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

restart

More info at http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html . The configuration above may depend on your mysql version.

Weird. I’m running into this same issue when trying to run migrations after updating Homestead to 0.3.3 with Laravel 5.0.16

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created_at'

For what it’s worth, replacing $table->timestamps() with the following seems to be a workaround:

$table->timestamp('created_at')->default(\DB::raw('CURRENT_TIMESTAMP'));
$table->timestamp('updated_at')->default(\DB::raw('CURRENT_TIMESTAMP'));

$this->capsule->addConnection( [ ‘driver’ => ‘mysql’, ‘host’ => ‘127.0.0.1’, ‘database’ => ‘xxxx’, ‘username’ => ‘xxxx’, ‘password’ => ‘xxxx’, ‘charset’ => ‘utf8’, ‘collation’ => ‘utf8_unicode_ci’, ‘port’ => ‘33060’, ‘prefix’ => ‘’, ‘strict’ => true ]);

Setting strict => true solved the problem for me.

The exception is thrown when creating the table itself, before inserting any data. This happens because the default value for timestamp fields is set to be 0.

Example of a crashing migration:

// ...

public function up()
{
    Schema::create('users', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('email')->unique()->nullable();
        $table->string('name');
        $table->timestamps();// This line causes the issue.
        $table->softDeletes();
    });
}

//...

The error that is thrown when running it:

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created_at'
(SQL: create table `users` (
    `id` int unsigned not null auto_increment primary key, 
    `email` varchar(255) null, 
    `name` varchar(255) not null, 
    `created_at` timestamp default 0 not null, 
    `updated_at` timestamp default 0 not null, 
    `deleted_at` timestamp null
) default character set utf8 collate utf8_unicode_ci)

Laravel 5.1.8, running on the newest Homestead box (version 0.3.3).

Setting strict => true fixed it for me as well on MySQL 5.7

@Stayallive thank you for saving my time. It works.

As discussed in the MySQL docs for 5.7, MySQL 5.7.8+ enables NO_ZERO_DATE by default, but strict mode does not include NO_ZERO_DATE. Thus the workaround to enable strict mode really does work. However, it is only a temporary fix and will in the future cause the very same issues to return.

Thus the final, long-lasting solution will be to create your timestamp columns with $table->timestamp('created_at')->useCurrent(); followed by $table->timestamp('updated_at')->useCurrent(); instead of using $table->timestamps()

I also encountered this issue. I’m thinking about using:

$table->timestamp('created_at')->nullable();
$table->timestamp('updated_at')->nullable();

Instead of $table->timestamps();

What do you guys think?

For people like me who have just updated to mysql 5.7 and faced this issue and google search results landed them here… use the below link https://mattstauffer.co/blog/how-to-disable-mysql-strict-mode-on-laravel-forge-ubuntu

He doesn’t seem to care to understand this one.

@GrahamCampbell @taylorotwell - sorry - this is still broken on 5.1.28 on the current versions of Homestead and Forge installations (with the default mySQL 5.6 config that both use)

You can do a migration “ok”.

But if you then run a mysqldump (which is common for backups) - then try and import that same SQL file into that same server (or another) - it will fail. This is important - because I just had to do an emergency migration - and the migration failed due to this issue.

The problem is only with $table->timestamps() because they are still creating a field of:

  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

So I had to manually “find and replace” the contents of the SQL file and change them to this:

  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

I tested running a new migration on 5.1.28, then dumping the file, and trying to import, and the issue remains. And I cant use ->useCurrent() because that does not work on $table->timestamps()

So the only option at the moment is to remove $table->timestamps() and manually write both fields for new migrations:

            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->useCurrent();

@hithereimhere try

SET GLOBAL sql_mode = ‘modes’;

but if it works for you know be aware next time mysql restarts you may lose the change, unless yoy modify mysql config file/s

We already added a new helper ->useCurrent() but it may not be tagged yet.

On Mon, Nov 23, 2015 at 2:19 PM, Mark Penner notifications@github.com wrote:

@taylorotwell Why don’t you add a new method called currentTimestamps which has defaults as shown in my original query? 0 is not a good default because it doesn’t work in strict mode/NO_ZERO_DATE and null doesn’t work either if you want those columns to always be non-null.

I’d say you should update the base timestamps but multiple CURRENT_TIMESTAMPs doesn’t work on older versions of MySQL, so for backwards compat I’d just make it a new method.

Reply to this email directly or view it on GitHub: https://github.com/laravel/framework/issues/3602#issuecomment-159052364

Strict set to false fixed this for me. Any updates on a permanent fix?