2015年11月22日 星期日

Laravel - Invalid default value for 'created_at'

@mnpenner
mnpenner commented on 19 Feb 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 andupdated_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
arvidbjorkstrom commented on 19 Feb 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
arvidbjorkstrom commented on 19 Feb 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
mnpenner commented on 20 Feb 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
carbontwelve commented on 20 Feb 2014
@mnbayazit if you have a suggestion for improving the docs, you could always fork them, make your edits and issue a pull request :)
@DSpeichertDSpeichert referenced this issue in twostairs/paperwork on 7 Apr
 Closed

Migration fails: Invalid default value for 'created_at' (table: users) #337

@kingpabel
kingpabel commented on 31 May
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
mnpenner commented on 1 Jun
@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
cbier commented on 10 Jun
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?
@uberbradyuberbrady referenced this issue in snipe/snipe-it 20 days ago
 Closed

Docker setup fails during install step #1303

@miclf
miclf commented 8 days ago
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
Owner
 taylorotwell commented 8 days ago
@miclf
miclf commented 7 days ago
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
Owner
 taylorotwell commented 7 days ago
@riebschlager
riebschlager commented 6 days ago
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
Stayallive commented 6 days ago
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
miclf commented 6 days ago
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
Stayallive commented 6 days ago
Ah, I have found the issue a fix, probably.
My "old" database.php config file does not have strict set to false ('strict' => false) aslaravel/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
miclf commented 6 days ago
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
Stayallive commented 6 days ago
Wait wut... this is not the Laravel magic I heard about 😜
@sorinstanila
sorinstanila commented 6 days ago
I have the same problem after upgrade. My solution:
  • use nullableTimestamps() instead of timestamps()
  • for custom timestamps add nullable , ex: timestamp('xxxx')->nullable()
@mnpenner
 mnpenner commented 6 days ago
@justenh
justenh commented 5 days ago
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
kokokurak commented 5 days ago
I just updated Laravel Homestead, that is using MySQL 5.7.9 now, and got the same issue.
@GrahamCampbell
Collaborator
GrahamCampbell commented 5 days ago
It's fixed in the very latest 5.1.x dev version atm.
@GrahamCampbell
Collaborator
GrahamCampbell commented 5 days ago
It'll be available in 5.1.25 once it's released.
@kokokurak
kokokurak commented 5 days ago
I see it's going to use default timestamp as the default. Wouldn't it be better to just skip default value entirely?
@GrahamCampbell
Collaborator
GrahamCampbell commented 5 days ago
That's what the fix is I think.
@miclf
miclf commented 5 days ago
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
sander3 commented 4 days ago
You could set explicit_defaults_for_timestamp to TRUE in your MySQL server configuration file.
@jdkschang
jdkschang commented 3 days ago
I have set explicit_defaults_for_timestamp = TRUE in the my.cnf file and restarted the mysqld server, but I still get the same error, but when I set the config/database.php strict to true, this issue #3602 gets resolved and another (unrelated issue?) crops up.
Currently on Laravel 5.0.33
edit: Went back and revised any changes I made, then set nullable() to every custom timestamp('xxxx') I had as well as changing some default seed parameters, and finally have it running
@kokokurak
kokokurak commented 3 days ago
As @sorinstanila already mentioned, there's also nullableTimestamps() method as a workaround for now.
@joshuaziering
joshuaziering commented 3 days ago
Strict set to false fixed this for me. Thanks for the help.
@hillelcorenhillelcoren referenced this issue in invoiceninja/invoiceninja a day ago
 Closed

Error on installation #542

@hillelcoren
hillelcoren commented a day ago
MySQL released a new version of their docker container which runs in a more strict mode. If you specifically pick out a MySQL version running 5.6 or earlier, it won't happen. I'm updating documentation to note that.

reference : https://github.com/laravel/framework/issues/3602
張貼者： 於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)

wibiya widget