New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Invalid default value for 'created_at' #3602

Closed
mnpenner opened this Issue Feb 19, 2014 · 69 comments

Comments

Projects
None yet
@mnpenner
Copy link

mnpenner commented Feb 19, 2014

I was getting this error when using $table->timestamps() in a migration:

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created_at' (SQL: create table vehicles (id int unsigned not null auto_increment primary key, name varchar(255) not null, code varchar(255) not null, description text not null, capacity int not null, company_id int not null, created_at timestamp default 0 not null, updated_at timestamp default 0 not null) default character set utf8 collate utf8_unicode_ci)

with a MySQL backend. Turns out it's because I had NO_ZERO_DATE enabled. This actually makes sense though: created_at should either always contain a valid date, or it needs to be nullable. 0 does not make sense.

I think a more sensible default is this:

public function timestamps()
{
    $this->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
    $this->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));
}

But it only works on MySql 5.6+, I believe (you're only allowed one CURRENT_TIMESTAMP prior to that, for whatever reason). In which case maybe we should make created_at default to CURRENT_TIMESTAMP and updated_at be nullable? I'm just starting to learn Laravel, I'm not sure if updated_at is intended to start out blank and be updated after the first modify, or always contain a value.

@arvidbjorkstrom

This comment has been minimized.

Copy link
Contributor

arvidbjorkstrom commented Feb 19, 2014

I believe it was set to something like that back in some version of the beta or pre-beta but I think it created more problems than it solved, and that's why we ended up with the current version

@arvidbjorkstrom

This comment has been minimized.

Copy link
Contributor

arvidbjorkstrom commented Feb 19, 2014

As of now all the handling and logic controlling how and when the updated_at column is updated is now controlled in php code. So I think there are a lot of places in need of rewrite if this is changed.
For example to get the ability to update a row without updating the updated_at you either need to change the table, temporarily removing the on update clause or maybe setting the updated_at=updated_at will override the on update. Not sure if the latter will work.

@mnpenner

This comment has been minimized.

Copy link

mnpenner commented Feb 19, 2014

Hrm... would it be possible to give a better warning at least? I probably never would have figured it out if I didn't stumble across this blog post in Japanese. If not in artisan, at least in the Quickstart Tutorial which I was having a heck of a time following along with :-)

@carbontwelve

This comment has been minimized.

Copy link

carbontwelve commented Feb 19, 2014

@mnbayazit if you have a suggestion for improving the docs, you could always fork them, make your edits and issue a pull request :)

@kingpabel

This comment has been minimized.

Copy link

kingpabel commented May 31, 2015

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.

@mnpenner

This comment has been minimized.

Copy link

mnpenner commented May 31, 2015

@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.

@cbier

This comment has been minimized.

Copy link

cbier commented Jun 10, 2015

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?

@miclf

This comment has been minimized.

Copy link
Contributor

miclf commented Nov 15, 2015

The issue popped up again with the newest version of Homestead. I guess the new version of MySQL shipped with it has different settings than in previous Homestead versions.

@taylorotwell

This comment has been minimized.

Copy link
Member

taylorotwell commented Nov 15, 2015

Are you inserting records with no time stamp?

On Sun, Nov 15, 2015 at 5:45 AM, Michaël Lecerf notifications@github.com
wrote:

The issue popped up again with the newest version of Homestead. I guess the new version of MySQL shipped with it has different settings than in previous Homestead versions.

Reply to this email directly or view it on GitHub:
#3602 (comment)

@miclf

This comment has been minimized.

Copy link
Contributor

miclf commented Nov 15, 2015

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).

@taylorotwell

This comment has been minimized.

Copy link
Member

taylorotwell commented Nov 15, 2015

Hmm, I'm having trouble recreating this on the latest Homestead. I can run
the default user migration fine and insert data.

On Sun, Nov 15, 2015 at 12:15 PM, Michaël Lecerf notifications@github.com
wrote:

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 latest Homestead version (v0.3.3).


Reply to this email directly or view it on GitHub
#3602 (comment).

@riebschlager

This comment has been minimized.

Copy link

riebschlager commented Nov 16, 2015

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'));
@stayallive

This comment has been minimized.

Copy link
Contributor

stayallive commented Nov 16, 2015

I have the exact same issue since installing Homestead yesterday...

Looks like this has been a problem before, http://stackoverflow.com/a/30555920

Would love to see this fixed or made as a comment in the documentation that you should be wary for this and maybe change the setting mentioned in the stackoverflow answer for the Homestead boxes!?

@miclf

This comment has been minimized.

Copy link
Contributor

miclf commented Nov 16, 2015

Hmm, I'm having trouble recreating this on the latest Homestead. I can run the default user migration fine and insert data.

It also works for me on a blank Laravel install (5.1.24). The two default migrations run without any problem and create the tables in the homestead database. Weird.

Did you tweak anything in MySQL’s configuration on your own Homestead instance? As far as I get it, the issue seems related to this SQL mode (NO_ZERO_DATE). Which is deprecated in the latest MySQL versions (such as the one shipped with the latest Homestead box).

@stayallive

This comment has been minimized.

Copy link
Contributor

stayallive commented Nov 16, 2015

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).

@miclf

This comment has been minimized.

Copy link
Contributor

miclf commented Nov 16, 2015

@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…

@stayallive

This comment has been minimized.

Copy link
Contributor

stayallive commented Nov 16, 2015

Wait wut... this is not the Laravel magic I heard about 😜

@sorinstanila

This comment has been minimized.

Copy link

sorinstanila commented Nov 17, 2015

I have the same problem after upgrade. My solution:

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

This comment has been minimized.

Copy link

mnpenner commented Nov 17, 2015

It's deprecated but included as part of strict mode.

Laravel should be strict compliant by default.
On Nov 16, 2015 12:31 PM, "Michaël Lecerf" notifications@github.com wrote:

Hmm, I'm having trouble recreating this on the latest Homestead. I can run
the default user migration fine and insert data.

It also works for me on a blank Laravel install (5.1.24). The two default
migrations run without any problem and create the tables in the homestead
database. Weird.

Did you tweak anything in MySQL’s configuration on your own Homestead
instance? As far as I get it, the issue seems related to this SQL mode
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
(NO_ZERO_DATE). Which is deprecated in the latest MySQL versions (such as
the one shipped with the latest Homestead box).


Reply to this email directly or view it on GitHub
#3602 (comment).

@justenh

This comment has been minimized.

Copy link

justenh commented Nov 17, 2015

Running into the same issue on an application built using Laravel 4.2.17 & laravel/homestead' (v0.3.3). I've tried adding the 'strict'=>false setting to the database config, but that doesn't seem to do the trick.

@kokokurak

This comment has been minimized.

Copy link

kokokurak commented Nov 17, 2015

I just updated Laravel Homestead, that is using MySQL 5.7.9 now, and got the same issue.

@GrahamCampbell

This comment has been minimized.

Copy link
Member

GrahamCampbell commented Nov 17, 2015

It's fixed in the very latest 5.1.x dev version atm.

@GrahamCampbell

This comment has been minimized.

Copy link
Member

GrahamCampbell commented Nov 17, 2015

It'll be available in 5.1.25 once it's released.

@kokokurak

This comment has been minimized.

Copy link

kokokurak commented Nov 17, 2015

I see it's going to use default timestamp as the default. Wouldn't it be better to just skip default value entirely?

@GrahamCampbell

This comment has been minimized.

Copy link
Member

GrahamCampbell commented Nov 17, 2015

That's what the fix is I think.

@miclf

This comment has been minimized.

Copy link
Contributor

miclf commented Nov 18, 2015

It's fixed in the very latest 5.1.x dev version atm.

Any plan/possibility to backport a fix to 4.x and/or 5.0?

@sander3

This comment has been minimized.

Copy link

sander3 commented Nov 18, 2015

You could set explicit_defaults_for_timestamp to TRUE in your MySQL server configuration file.

@alex-petrea

This comment has been minimized.

Copy link

alex-petrea commented Jan 4, 2016

I had the same problem.

vagrant ssh
sudo vi /etc/mysql/my.cnf
add sql-mode="allow_invalid_dates"
save
sudo service nginx restart

works like a charm :)

*** Note
If it doesn't work after nginx restart do a vagrant reload --provision

@jwilson8767

This comment has been minimized.

Copy link

jwilson8767 commented Jan 23, 2016

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

@laurencei

This comment has been minimized.

Copy link
Member

laurencei commented Jan 24, 2016

@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();
@taylorotwell

This comment has been minimized.

Copy link
Member

taylorotwell commented Jan 25, 2016

It sounds like you needed to turn off MySQL strict mode?

On Jan 24, 2016, 3:49 AM -0600, Laurence Ioannounotifications@github.com, wrote:

@GrahamCampbell(https://github.com/GrahamCampbell)@taylorotwell(https://github.com/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.


Reply to this email directly orview it on GitHub(#3602 (comment)).

@craighooghiem

This comment has been minimized.

Copy link

craighooghiem commented Jan 25, 2016

Typically it requires enabling strict mode.
The issue is that an out of the box homestead install and an out of the box Laravel install with migrations based on the docs will not work.

@laurencei

This comment has been minimized.

Copy link
Member

laurencei commented Jan 25, 2016

@taylorotwell - yes, that would also solve it. But the point is the "default" Forge and "default" Homestead configs dont have that - so the "default" Laravel 5.1.28 runs into issues.

It would be nice if something changed so all the "defaults" align? Currently they dont...

@jwilson8767

This comment has been minimized.

Copy link

jwilson8767 commented Jan 25, 2016

@taylorotwell As mentioned in my previous post NO_ZERO_DATE is currently a feature in flux. TheShiftExchange is correct in his explanation of the workaround.

@junibrosas

This comment has been minimized.

Copy link

junibrosas commented Jan 25, 2016

Credits to @dbillionlabs for his answer. It removed the maggots in my head.

@wunc

This comment has been minimized.

Copy link

wunc commented Feb 15, 2016

@taylorotwell Could you make $table->timestamps() default to either nullable() or useCurrent() on 5.1, like you did in commit 720a116 to 5.2?

@craighooghiem

This comment has been minimized.

Copy link

craighooghiem commented Feb 15, 2016

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

mafernando added a commit to mafernando/laravel-app that referenced this issue Mar 9, 2016

Update database.php
Needs to be set true for migrations to run. 

laravel/framework#3602 (comment)
@andela-tolotin

This comment has been minimized.

Copy link

andela-tolotin commented Mar 15, 2016

$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.

wjgilmore added a commit to wjgilmore/todoparrot that referenced this issue Mar 31, 2016

@atanl

This comment has been minimized.

Copy link

atanl commented Apr 28, 2016

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

@casoetan

This comment has been minimized.

Copy link

casoetan commented May 16, 2016

@sankety

This comment has been minimized.

Copy link

sankety commented Aug 5, 2016

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

@ctf0

This comment has been minimized.

Copy link

ctf0 commented Sep 7, 2016

for the issue with strict mode true , check
https://laravel.com/docs/5.3/upgrade#upgrade-5.2.0

MySQL Dates

Starting with MySQL 5.7, 0000-00-00 00:00:00 is no longer considered a valid date, since strict mode is enabled by default. All timestamp columns should receive a valid default value when you insert records into your database. You may use the useCurrent method in your migrations to default the timestamp columns to the current timestamps, or you may make the timestamps nullable to allow null values:

$table->timestamp('foo')->nullable();

$table->timestamp('foo')->useCurrent();

$table->nullableTimestamps();

kinda dump that something like that wasnt properly documented

@tholu

This comment has been minimized.

Copy link

tholu commented Jan 8, 2017

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

This comment has been minimized.

Copy link

robfrancken commented Apr 5, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment