attribute CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP need to apply for the field updated_at
_users table_

Schema::create('users', function (Blueprint $table)
        {
            $table->increments('id');
            $table->string('name', 32);
            $table->string('email')->unique();
            $table->string('password', 60);
            $table->rememberToken();
            $table->timestamps();
        });

image description

PheRum on 25 Dec 2015

This is by design.

GrahamCampbell on 25 Dec 2015

@GrahamCampbell I do not quite understand, this is a mistake or a well conceived?

PheRum on 25 Dec 2015

I'm sorry, I don't quite understand what it is you're saying is incorrect here?

GrahamCampbell on 25 Dec 2015

Please look carefully at the post https://github.com/laravel/framework/issues/11518#issuecomment-167168168

there migration, which creates a column with dates

$table->timestamps();  // => created_at and updated_at column

after migration to created_at field is added to the attribute CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP and it is wrong

example:
There is an article. I use the created_at column value as the publication date, but when updating the counter view the created_at column value is updated to the current time. But should be updated updated_at field

Controller

public function show ($id)
    {
        $news = News::findOrFail($id);
        $news->views++;
        $news->save();

        return view('news.show', compact('news'));
    }

Similarly, if the migration was to create a field with the timestamp and attribute CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP then the created_at column value will be created without attribute CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

in laravel 5.1 of the $table->timestamps(); were created alike:

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

Perhaps the best idea is to return as

PheRum on 25 Dec 2015

Are you sure this change is only on 5.2? I can't see any differences between the current 5.1 dev and 5.2 dev?

GrahamCampbell on 25 Dec 2015

What database are you using please?

GrahamCampbell on 25 Dec 2015

I've investigated this for you, and this breaking change was made in 5.2, and it was definitely intentional.

GrahamCampbell on 25 Dec 2015

I deliberately rolled round of audit on its project to ensure that the error was confirmed. the only problem is to laravel 5.2

presumably there https://github.com/laravel/framework/blob/5.2/src/Illuminate/Database/Schema/Grammars/MySqlGrammar.php#L532

DataBase Mysql

please return the creation of fields with attributes that were previously

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

PheRum on 25 Dec 2015

https://www.diffchecker.com/yahgywri

GrahamCampbell on 25 Dec 2015

Yep, we changed the mysql grammar.

GrahamCampbell on 25 Dec 2015

https://github.com/laravel/framework/commit/4223167cad4cdee03b5d838be1c3069fa3313f83

GrahamCampbell on 25 Dec 2015

Ping @taylorotwell.

GrahamCampbell on 25 Dec 2015

results of creating a table of migration

Schema::create('users', function (Blueprint $table)
{
    $table->increments('id');
    $table->string('name', 32);
    $table->string('email')->unique();
    $table->string('password', 60);
    $table->rememberToken();
    $table->timestamps();
});

image description

Schema::create('users', function (Blueprint $table)
{
    $table->increments('id');
    $table->string('name', 32);
    $table->string('email')->unique();
    $table->string('password', 60);
    $table->rememberToken();
    $table->timestamp('new_time');
    $table->timestamps();
});

image description

PheRum on 25 Dec 2015

I'm unable to recreate this... :/

taylorotwell on 25 Dec 2015

👎1

I'm on Laravel 5.2.3 and get the following declaration:

  `created_at` timestamp NOT NULL,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),

taylorotwell on 25 Dec 2015

I use laravel 5.2.5 and MySql Server 10.1.9-MariaDB and always get such a result (((

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

PheRum on 25 Dec 2015

👍4

I am able to reproduce this from the standard migrations (5.2.5)

Users
timestamps

Resets
timestamps1

mikerockett on 25 Dec 2015

Hi Taylor,

I am using Laravel 5.2.5 too and i have a same problem.

I was using 5.1.27 and that problem is not exist. I upgraded to 5.2.5 then run php artisan migrate:refresh and created_at field properties changed.

Migration command:

$table->timestamps();

SQL:

created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
deleted_at timestamp NULL DEFAULT NULL

ozgurkaragoz on 25 Dec 2015

I can reproduce the issue (Laravel 5.2.5, MySQL 5.5.45, apparently not affected by strict mode setting).

Interestingly, the SQL code generated by Laravel looks fine:

array(2) {
  [0]=>
  string(322) "create table `users` (`id` int unsigned not null auto_increment primary key, `name` varchar(32) not null, `email` varchar(255) not null, `password` varchar(60) not null, `remember_token` varchar(100) null, `created_at` timestamp not null, `updated_at` timestamp not null) default character set utf8 collate utf8_general_ci"
  [1]=>
  string(60) "alter table `users` add unique `users_email_unique`(`email`)"
}

So it's MySQL who adds the default values and the ON UPDATE CURRENT_TIMESTAMP extra.
To those who haven't yet, would you state your MySQL versions please?

See: MySQL 5.5 - Automatic Initialization and Updating for TIMESTAMP

vlakoff on 25 Dec 2015

@vlakoff, I'm on 5.6.21.

mikerockett on 25 Dec 2015

Any news on this?

Using

$table->timestamp('created_at')->default(0);
$table->timestamp('updated_at')->default(0);

in the meantime.

mikerockett on 27 Dec 2015

👍1

Does this work on mysql 5.7, since laravel 5.2 only officially supports mysql 5.7 now?

GrahamCampbell on 27 Dec 2015

@GrahamCampbell - Was not aware of this. Should the docs not be updated?

mikerockett on 27 Dec 2015

@GrahamCampbell - Just upgraded my server, which now uses MariaDB 10.1.9. Issue persists.

mikerockett on 27 Dec 2015

Also, am unable to set 0 as the default now. '0000-00-00 00:00:00' is required as a default.

mikerockett on 27 Dec 2015

which now uses MariaDB 10.1.9. Issue persists.

We don't officially support mariadb, so if there are inconsistencies between mysql and them, they need to fix it, a bit like hhvm and php.

Do you have this issue on an actual mysql 5.7 server?

GrahamCampbell on 27 Dec 2015

NB if mariadb works, that's fine. I don't discourage you using it with laravel.

GrahamCampbell on 27 Dec 2015

This is actually somewhat problematic: MySQL 5.7 was made available only in October. Why would it become a requirement now? Are you telling me that PHP7 will become a requirement in March 2016?

And why would this change be by design?

mikerockett on 27 Dec 2015

Why are people saying Laravel 5.2 ONLY supports MySQL 5.7? That is not true
and I'm not sure what "fix" you are wanting here? Everything is working
correctly. MySQL 5.7 no longer allows 0000-00-00 as a valid timestamp with
strict mode turned on (which it is by default). So either use
->nullableTimestamps() or ->timestamp()->useCurrent().

On Sun, Dec 27, 2015 at 9:01 AM, Mike Rockétt [email protected]
wrote:

This is actually somewhat problematic: MySQL 5.7 was made available only
in October. Why would it become a requirement now? Are you telling me that
PHP7 will become a requirement in March 2016?

And why would this change be by design?

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

taylorotwell on 27 Dec 2015

👍1

Why are people saying Laravel 5.2 ONLY supports MySQL 5.7?

The json change broke support for mysql 5.1->5.6.

GrahamCampbell on 27 Dec 2015

At the time, it was proposed we try to support both mysql 5.1->5.6 and 5.7, together, but you decided to just support 5.7 syntax.

GrahamCampbell on 27 Dec 2015

What JSON change? Just use the "text" column which is what 5.1 was doing?

On Sun, Dec 27, 2015 at 9:21 AM, Graham Campbell [email protected]
wrote:

At the time, it was proposed we try to support both mysql 5.1->5.6 and
5.7, together, but you deicded to just support 5.7 syntax.

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

taylorotwell on 27 Dec 2015

When someone writes migrations, they do not write them in the knowledge of what database server consumers of the application are using. Someone should be able to write json and have it work correctly on all database servers.

GrahamCampbell on 27 Dec 2015

👍1

MySQL 5.7 no longer allows 0000-00-00 as a valid timestamp with strict mode turned on (which it is by default)

Would it not then be possible to have the framework check? Perhaps the ideal situation would be for the framework to apply the default '0000-00-00 00:00:00' on <5.7 and NULL on 5.7 for timestamps()?

Alternatively, the documentation should make mention of this in both the upgrade guide and migration docs.

mikerockett on 27 Dec 2015

👍1

I've updated the upgrade guide.

On Sun, Dec 27, 2015 at 9:31 AM, Mike Rockétt [email protected]
wrote:

MySQL 5.7 no longer allows 0000-00-00 as a valid timestamp with strict
mode turned on (which it is by default)

Would it not then be possible to have the framework check? Perhaps the
ideal situation would be for the framework to apply the default '0000-00-00
00:00:00' on <5.7 and NULL on 5.7 for timestamps()?

Alternatively, the documentation should make mention of this in both the
upgrade guide and migration docs.

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

taylorotwell on 27 Dec 2015

Sorry about hijacking this a bit with the json stuff. I've sent a PR to address my concerns: https://github.com/laravel/framework/pull/11556.

GrahamCampbell on 27 Dec 2015

Thank you, Taylor. :+1:

mikerockett on 27 Dec 2015

Problem is, many people will continue to use ->timestamps() as they're used to it, and encounter the above issue.

vlakoff on 27 Dec 2015

Yeah, we've noted it in the upgrade guide now.

On Sun, Dec 27, 2015 at 11:18 AM, vlakoff [email protected] wrote:

Problem is, many people will continue to use ->timestamps() as they're
used to it, and encounter the above issue.

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

taylorotwell on 27 Dec 2015

For MySQL versions prior to 5.6.5, _"...at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time."_

https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html#mysqld-5-6-5-data-types

So to mitigate conflicts with MySQL < 5.6.5, we could override the Blueprint@timestamps method to create the updated_at column before the created_at column. Doing so the updated_at would have the ON UPDATE CURRENT_TIMESTAMP bound to it and created_atwould keep its expected behavior.

    public function timestamps()
    {
        // change order to avoid unexpected behavior with MySQL < 5.6.5
        $this->timestamp('updated_at');
        $this->timestamp('created_at');
    }

I just don't know how we could change it only for MySQL as the Blueprint class is database agnostic.

rodrigopedra on 28 Dec 2015

I'm not actually sure that's an option, as a believe the framework sets the updated_at column itself, and does not rely on MySQL to do it. The frameworks' default has always been 0.

mikerockett on 28 Dec 2015

I agree that it shouldn't be considered as an option. It just crossed my mind that it could be related to the issue reported. Which is a MySQL issue not a Laravel one.

But to be correct, the framework sets the updated_at automatically if you are using Eloquent ORM.

If you're using only migrations and the DB façade you should do it manually or delegate to the database using a method cases like this one (ON UPDATE ...).

But in such cases my opinion is that the developer could manually set whatever is needed.

rodrigopedra on 28 Dec 2015

Getting this issue too, fresh install of Laravel 5.2.5 with MySQL 5.6.17, just ran make:auth then migrate

mysql> describe users;
+----------------+------------------+------+-----+---------------------+-----------------------------+
| Field          | Type             | Null | Key | Default             | Extra                       |
+----------------+------------------+------+-----+---------------------+-----------------------------+
| id             | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| name           | varchar(255)     | NO   |     | NULL                |                             |
| email          | varchar(255)     | NO   | UNI | NULL                |                             |
| password       | varchar(60)      | NO   |     | NULL                |                             |
| remember_token | varchar(100)     | YES  |     | NULL                |                             |
| created_at     | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at     | timestamp        | NO   |     | 0000-00-00 00:00:00 |                             |
+----------------+------------------+------+-----+---------------------+-----------------------------+

>>> App\User::create(['name' => 'Dan'])
=> App\User {#631
     name: "Dan",
     updated_at: "2015-12-28 11:56:03",
     created_at: "2015-12-28 11:56:03",
     id: 1,
   }
>>> App\User::find(1)->update(['name' => 'Bob'])
=> true
>>> App\User::find(1)
=> App\User {#637
     id: 1,
     name: "Bob",
     email: "",
     created_at: "2015-12-28 11:56:39",   # was "2015-12-28 11:56:03"
     updated_at: "2015-12-28 11:56:39",
   }

danharper on 28 Dec 2015

Laravel never tells MySQL to use the current time stamp on update, and I
don't get that when I run the migrations, so not sure why you are getting
that.
On Mon, Dec 28, 2015 at 6:01 AM Dan Harper [email protected] wrote:

Getting this issue too, fresh install of Laravel 5.2.5 with MySQL 5.6.17,
just ran make:auth then migrate

mysql> describe users;
+----------------+------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| email | varchar(255) | NO | UNI | NULL | |
| password | varchar(60) | NO | | NULL | |
| remember_token | varchar(100) | YES | | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| updated_at | timestamp | NO | | 0000-00-00 00:00:00 | |
+----------------+------------------+------+-----+---------------------+-----------------------------+

AppUser::create(['name' => 'Dan'])=> AppUser {#631 name: "Dan", updated_at: "2015-12-28 11:56:03", created_at: "2015-12-28 11:56:03", id: 1, }>>> AppUser::find(1)->update(['name' => 'Bob'])=> true>>> AppUser::find(1)=> AppUser {#637 id: 1, name: "Bob", email: "", created_at: "2015-12-28 11:56:39", updated_at: "2015-12-28 11:56:39", }

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

taylorotwell on 28 Dec 2015

From: https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

Alternatively, if explicit_defaults_for_timestamp is disabled (the default), do either of the following:

Define the column with a DEFAULT clause that specifies a constant default value.

Specify the NULL attribute. This also causes the column to permit NULL values, which means that you cannot assign the current timestamp by setting the column to NULL. Assigning NULL sets the column to NULL.

So if explicit_defaults_for_timestamp is disabled and as Illuminate\Database\Schema\Grammars\MySqlGrammar@typeTimestamp does not define a default value, MySQL will automatically set the column to DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

rodrigopedra on 28 Dec 2015

Interesting. So sounds like if that is disabled you need to either use
->nullableTimestamps() or ->timestamp('foo')->useCurrent().

On Mon, Dec 28, 2015 at 8:19 AM, Rodrigo Pedra Brum <
[email protected]> wrote:

From:
https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

Alternatively, if explicit_defaults_for_timestamp is disabled (the default), do either of the following:

Define the column with a DEFAULT clause that specifies a constant default value.

Specify the NULL attribute. This also causes the column to permit NULL values, which means that you cannot assign the current timestamp by setting the column to NULL. Assigning NULL sets the column to NULL.

So if explicit_defaults_for_timestamp is disables and as
IlluminateDatabaseSchemaGrammarsMySqlGrammar@typeTimestamp does not
define a default value, MySQL will automatically set the column to DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

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

taylorotwell on 28 Dec 2015

Here is more information on this from MySQL page:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

It appears they deprecated all of this implicit default value stuff, which
is good.

On Mon, Dec 28, 2015 at 8:43 AM, Taylor Otwell [email protected]
wrote:

Interesting. So sounds like if that is disabled you need to either use
->nullableTimestamps() or ->timestamp('foo')->useCurrent().

On Mon, Dec 28, 2015 at 8:19 AM, Rodrigo Pedra Brum <
[email protected]> wrote:

From:
https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

Alternatively, if explicit_defaults_for_timestamp is disabled (the default), do either of the following:

Define the column with a DEFAULT clause that specifies a constant default value.

Specify the NULL attribute. This also causes the column to permit NULL values, which means that you cannot assign the current timestamp by setting the column to NULL. Assigning NULL sets the column to NULL.

So if explicit_defaults_for_timestamp is disables and as
IlluminateDatabaseSchemaGrammarsMySqlGrammar@typeTimestamp does not
define a default value, MySQL will automatically set the column to DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

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

taylorotwell on 28 Dec 2015

I love that Graham closed this for no reason. When are you going to get rid of him Taylor?

jaketoolson on 1 Jan 2016

👎7

I love that Graham closed this for no reason.

In case you have't noticed, this issue isn't even closed...

GrahamCampbell on 1 Jan 2016

@GrahamCampbell You mention:

The json change broke support for mysql 5.1->5.6.

and @rodrigopedra mentions

So to mitigate conflicts with MySQL < 5.6.5

FYI, I still get the following default values:

created_at on update CURRENT_TIMESTAMP
updated_at 0000-00-00 00:00:00

using MySQL 5.6.28 and 5.7.10, with PHP 5.5.30, and Laravel 5.2.6.

As suggested by Taylor, I've been using the following as a workaround:

$table->nullableTimestamps()

for now, which has reverted the timestamps to Larave 5.1.* behavior:

created_at 0000-00-00 00:00:00
updated_at 0000-00-00 00:00:00

but with the addition of them being nullable columns.

bridgeport on 1 Jan 2016

In case you have't noticed, this issue isn't even closed...

Never mind the troll @GrahamCampbell, you've been doing a splendid job so far.

phanan on 2 Jan 2016

Taylor's suggestion doesn't work for table users.

$table->nullableTimestamps()

luanrodrigues on 4 Jan 2016

Just FYI, Amazon RDS doesn't support MySQL 5.7 yet, so out-of-the-box, Laravel 5.2 doesn't behave correctly (without making modifications to all migrations, I assume)

danharper on 4 Jan 2016

Why wouldn't it behave correctly? AFAIK, the 5.2 defaults are still valid
in earlier editions of MySQL. Can you provide details as to what
specifically goes wrong?

On Mon, Jan 4, 2016 at 5:37 AM, Dan Harper [email protected] wrote:

Just FYI, Amazon RDS doesn't support MySQL 5.7 yet, so out-of-the-box,
Laravel 5.2 doesn't behave correctly (without making modifications to all
migrations, I assume)

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

taylorotwell on 4 Jan 2016

I have the same problem in MySQL 5.6.21... So it's not limited to 5.7.

acasar on 4 Jan 2016

Right, so it's the explicit_defaults_for_timestamp option in 5.6.6+ (as mentioned here).

MySQL 5.6 from Homebrew on OSX uses the default value of explicit_defaults_for_timestamp=FALSE (meaning MySQL will add DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP to your first TIMESTAMP column).

As far as I can tell, the default is still _false_ in 5.7 (meaning you get the auto-updating timestamp).

However on Amazon RDS (running 5.6), this option is enabled by default. I'm not sure if Homestead comes with the setting set to true too?

With Homebrew, I've changed the setting:

sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf

then at the bottom of /etc/my.cnf, add:

explicit_defaults_for_timestamp=TRUE

and reboot mysql.

danharper on 5 Jan 2016

👍1

laravel should handle this without any mysql configuration changes

messiahUA on 5 Jan 2016

Ok, I've just installed Homestead, and MySQL 5.7 has explicit_defaults_for_timestamp enabled in /etc/mysql/my.cnf, so you wouldn't get the issue there.

danharper on 5 Jan 2016

Do you have the problem if you do...

->timestamp(created_at)->useCurrent();
->timestamp(updated_at)->useCurrent();

On Jan 5, 2016, 6:31 AM -0600, Dan [email protected], wrote:

Ok, I've just installed Homestead, and MySQL 5.7 hasexplicit_defaults_for_timestampenabled in/etc/mysql/my.cnf, so you wouldn't get the issue there.

—
Reply to this email directly orview it on GitHub(https://github.com/laravel/framework/issues/11518#issuecomment-168987011).

taylorotwell on 5 Jan 2016

🎉2

Yep, adding useCurrent() to Illuminate\Database\Schema\Blueprint::timestamps() fixes the issue.

# BEFORE
mysql> describe users;
+----------------+------------------+------+-----+---------------------+-----------------------------+
| Field          | Type             | Null | Key | Default             | Extra                       |
+----------------+------------------+------+-----+---------------------+-----------------------------+
| id             | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| name           | varchar(255)     | NO   |     | NULL                |                             |
| email          | varchar(255)     | NO   | UNI | NULL                |                             |
| password       | varchar(60)      | NO   |     | NULL                |                             |
| remember_token | varchar(100)     | YES  |     | NULL                |                             |
| created_at     | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at     | timestamp        | NO   |     | 0000-00-00 00:00:00 |                             |
+----------------+------------------+------+-----+---------------------+-----------------------------+

# AFTER
mysql> describe users;
+----------------+------------------+------+-----+-------------------+----------------+
| Field          | Type             | Null | Key | Default           | Extra          |
+----------------+------------------+------+-----+-------------------+----------------+
| id             | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| name           | varchar(255)     | NO   |     | NULL              |                |
| email          | varchar(255)     | NO   | UNI | NULL              |                |
| password       | varchar(60)      | NO   |     | NULL              |                |
| remember_token | varchar(100)     | YES  |     | NULL              |                |
| created_at     | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at     | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
+----------------+------------------+------+-----+-------------------+----------------+

danharper on 5 Jan 2016

Hmm, I would be open to maybe making that the default behavior for
->timestamps() method in Laravel since it would solve the problem for
people whose MySQL may be defaulting to the ON UPDATE stuff. Thoughts?

On Tue, Jan 5, 2016 at 8:23 AM, Dan Harper [email protected] wrote:

Yep, adding useCurrent() to
IlluminateDatabaseSchemaBlueprint::timestamps() fixes the issue.

BEFORE

mysql> describe users;
+----------------+------------------+------+-----+---------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------------------+-----------------------------+| id | int(10) unsigned | NO | PRI | NULL | auto_increment || name | varchar(255) | NO | | NULL | || email | varchar(255) | NO | UNI | NULL | || password | varchar(60) | NO | | NULL | || remember_token | varchar(100) | YES | | NULL | || created_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || updated_at | timestamp | NO | | 0000-00-00 00:00:00 | |
+----------------+------------------+------+-----+---------------------+-----------------------------+

AFTER

mysql> describe users;
+----------------+------------------+------+-----+-------------------+----------------+| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+----------------+| id | int(10) unsigned | NO | PRI | NULL | auto_increment || name | varchar(255) | NO | | NULL | || email | varchar(255) | NO | UNI | NULL | || password | varchar(60) | NO | | NULL | || remember_token | varchar(100) | YES | | NULL | || created_at | timestamp | NO | | CURRENT_TIMESTAMP | || updated_at | timestamp | NO | | CURRENT_TIMESTAMP | |
+----------------+------------------+------+-----+-------------------+----------------+

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

taylorotwell on 5 Jan 2016

@taylorotwell - I think it would be necessary to try and make timestamps() compatible with as much of the MySQL eco-system (including MariaDB, even though you do not officially support it [many are switching over, as I understand it]) as possible. If it means useCurrent() should be used as a default for both, then I agree with you.

mikerockett on 5 Jan 2016

@taylorotwell :+1: for making it the default.

acasar on 5 Jan 2016

:+1: seems a sensible default

danharper on 5 Jan 2016

@taylorotwell thats a good idea.

luanrodrigues on 5 Jan 2016

+1 for default. Because after changing mysql to 5.7 basicly all migrations started to fail. Was changing to nullableTimestamps()

huglester on 6 Jan 2016

+1 for default.

Please change the default. If you don't plan to change the default, at least consider changing the default order they are added in to updated_at, created_at instead of created_at, updated_at.

I realize this is very very dated, but apparently the functionality has not changed.
Taken from the MySQL 3.23, 4.0, 4.1 Reference Manual page 701.

The following items summarize TIMESTAMP initialization and updating properties prior to MySQL 4.1.2:
• The first TIMESTAMP column in table row automatically is set to the current timestamp when the
record is created if the column is set to NULL or is not specified at all.
• The first TIMESTAMP column in table row automatically is updated to the current timestamp when
the value of any other column in the row is changed, unless the TIMESTAMP column explicitly is
assigned a value other than NULL.
• If a DEFAULT value is specified for the first TIMESTAMP column when the table is created, it is silently
ignored.
• Other TIMESTAMP columns in the table can be set to the current TIMESTAMP by assigning NULL to
them, but they do not update automatically.

Beginning with MySQL 4.1.2, you have more flexible control over when automatic TIMESTAMP
initialization and updating occur and which column should have those behaviors:
• For one TIMESTAMP column in a table, you can assign the current timestamp as the default value
and the auto-update value. It is possible to have the current timestamp be the default value for
initializing the column, for the auto-update value, or both. It is not possible to have the current
timestamp be the default value for one column and the auto-update value for another column.
• Any single TIMESTAMP column in a table can be used as the one that is initialized to the current date
and time, or updated automatically. This need not be the first TIMESTAMP column.
In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following
ways:
• With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the
column has the current timestamp for its default value, and is automatically updated.
• With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
• With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the
current timestamp for its default value but is not automatically updated.
• With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has
a default of 0 and is automatically updated.
• With a constant DEFAULT value, the column has the given default and is not automatically
initialized to the current timestamp. If the column also has an ON UPDATE CURRENT_TIMESTAMP
clause, it is automatically updated; otherwise, it has a constant default and is not automatically
updated.

If no default value is provided, the first timestamp column in a table will always be converted to update on update to the current timestamp.

ikari7789 on 7 Jan 2016

Default has been applied in https://github.com/laravel/framework/commit/0c2b7da2635f7bbbaf63d1b93fa817232bdd9d65.

Would timestampsTz() need it too?

vlakoff on 8 Jan 2016

I don't know. I don't use those columns or know what would be best there.

On Thu, Jan 7, 2016 at 7:15 PM, vlakoff [email protected] wrote:

Default has been applied in 0c2b7da
https://github.com/laravel/framework/commit/0c2b7da2635f7bbbaf63d1b93fa817232bdd9d65
.

Would timestampsTz() need it too?

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

taylorotwell on 8 Jan 2016

For the MySQL grammar, both timestamp and timestampTz use the same syntax as far as SQL goes, so the issue will happen for those who use MySQL and use timestampsTz(), but since timestamps() itself is grammar agnostic, I'm unsure if it would be wise to add ->useCurrent() or perhaps just add a comment explaining the situation in MySQL.

On the other hand, for consistency purposes, it may be better to make both timestamps() and timestampsTz() create the same output as they're both the same column name, and someone who implements them would probably assume they work in the same way.

ikari7789 on 8 Jan 2016

the bug is still not fixed (((

@taylorotwell whether to wait for a fix, or to fall back on laravel 5.1?

PheRum on 10 Jan 2016

@PheRum Fixed here: https://github.com/laravel/framework/commit/0c2b7da2635f7bbbaf63d1b93fa817232bdd9d65

rap2hpoutre on 11 Jan 2016

@GrahamCampbell I'm not sure this is entirely solved due to an inconclusion on timestampsTz()

ikari7789 on 11 Jan 2016

Isn't timestampsTz really only for Postgre? (Shoot me if I'm wrong.)

mikerockett on 12 Jan 2016

It looked like SqlServer also had different definitions as well.

Sent from my iPhone

On Jan 12, 2016, at 15:03, Mike Rockétt [email protected] wrote:

Isn't timestampTz really only for Postgre? (Shoot me if I'm wrong.)

—
Reply to this email directly or view it on GitHub.

ikari7789 on 12 Jan 2016

@ikari7789 Maybe you should open a new ticket (or a PR) with a reference to this issue. The original problem seems to be solved (but what you added is interesting too).

rap2hpoutre on 12 Jan 2016

@PheRum I agree that issue has been resolved, but I am still having this issue.

There is no useCurrent() method used here:
https://github.com/laravel/framework/blob/v5.2.7/src/Illuminate/Database/Schema/Blueprint.php

If I do:

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

I get the error:

[PDOException]                                                                                                                                                 
SQLSTATE[HY000]: General error: 1293 Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Laravel Application verison: 5.2.7

MySQL Version:

Server version: 5.5.46-0ubuntu0.14.04.2 (Ubuntu)

PHP:

PHP 5.6.17-1+deb.sury.org~trusty+1 (cli) 
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies
    with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2015, by Zend Technologies
    with Xdebug v2.3.3, Copyright (c) 2002-2015, by Derick Rethans

For the fix, I am using $table->nullableTimestamps();

MehulBawadia on 12 Jan 2016

I am using laravel 5.2 I updated to the latest version and still $table->timestamps reproduces the abovementioned error.

After changing the schema to $table->nullableTimestamps(); everything works fine.

hootlex on 12 Jan 2016

@hootlex this fix was made just after tagging 5.2.7, so it should be in the next version...

messiahUA on 12 Jan 2016

@messiahUA according to composer I am using 5.2.7

  - Removing laravel/framework (v5.2.6)
  - Installing laravel/framework (v5.2.7)

hootlex on 12 Jan 2016

@hootlex that's what i'm talking about, this fix is not present in 5.2.7 :) you can use master or wait for next release (i guess 5.2.8)

messiahUA on 12 Jan 2016

@messiahUA nah.. okay thanks!

hootlex on 12 Jan 2016

Removed in 5.2.9?
https://github.com/laravel/framework/commit/cdae53877b554e3c829904ade6b8f663e548fb3c

asapo on 14 Jan 2016

See comment above, and #11861. That MySQL stuff is really giving us an hard time :-

If it were up to me, I might do:

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

Pros:

Should fix MySQL issues for good.
modified_at can be empty, which lets representing never edited records.

Cons:

May hurt consistency, in particular across DB drivers.

vlakoff on 14 Jan 2016

The fact still remains that making it

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

would solve the problem.

Sent from my iPhone

On Jan 14, 2016, at 12:04, vlakoff [email protected] wrote:

See #11861. That MySQL stuff is really giving us an hard time :-

If it were up to me, I might do:

$table->timestamp('created_at')->useCurrent();
$table->timestamp('modified_at')->nullable();
Pros:

Should fix MySQL issues for good.
modified_at can be empty, which lets representing never edited records.
Cons:

May hurt consistency, in particular across DB drivers.
―
Reply to this email directly or view it on GitHub.

ikari7789 on 14 Jan 2016

Relying on order of definition sounds somehow fragile to me, also I don't want updated_at to be before created_at in my SQL editor :p

Just some thought, we could move to DATETIME and forget about that automatically added ON UPDATE crap. :smiling_imp: Explicit default or null still needed though. And introducing timezone concerns.

$table->datetime('created_at')->useCurrent();
$table->datetime('updated_at')->nullable();

Bad idea though, because it brings timezones.

Therefore still voting for my suggestion above.

vlakoff on 14 Jan 2016

laravel 5.2.8 - the problem is corrected
laravel 5.2.10 - the problem is back

PheRum on 14 Jan 2016

@ikari7789 Not necessarily. As I understand it, if one were to create their own timestamp column before the updated_at/created_at fields, it would get the "CURRENT TIMESTAMP" definition, and neither updated_at nor created_at would.

patrickcarlohickman on 14 Jan 2016

It was intentionally reverted because it breaks more stuff than it fixed.

GrahamCampbell on 14 Jan 2016

Inside of Blueprint.php they have changed timestamps method.
Solution:
$this->timestamp('created_at')->useCurrent();
$this->timestamp('updated_at')->nullable();
Solved problem for me.

wojciechmruk on 14 Jan 2016

@patrickcarlohickman This is true, but I feel that extends beyond the problem being discussed here. I honestly can't see a great solution for this problem outside of not using the timestamps() function. Since the grammar is version indifferent, and they removed 0 as a default due to MySQL 5.7 making strict mode the default, I believe the bigger problem is that Laravel over specialized the grammar code to accomodate for the newest version. If anything, MySQL 5.7 should have a workaround made for it while retaining the same functionality as before. Perhaps creating a strictTimestamps() function would be better? I'm personally against all of the solutions that suggest to make the timestamps nullable as I don't think that if you're adding timestamps specifically, they should be nullable unless you specifically declare nullableTimestamps().

ikari7789 on 14 Jan 2016

On the other hand, there should be a way for updated_at to have an empty value (i.e. never edited record). If MySQL doesn't allow "datetime 0" anymore, the only remaining alternative is null.

My other concerns:

All of these changes affect all DB drivers, this should be kept in mind. And no, don't make timestamps() driver-varying, that would make surprises to occur when switching DB driver.
About the API, yeah, timestamps() and nullableTimestamps() won't be as cleanly set apart from each other as before.

vlakoff on 14 Jan 2016

+1 on @vlakoff suggestion. Nullable before seeding.

panoply on 14 Jan 2016

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

solved the problem for me :-)

jgrossi on 19 Jan 2016

jgrossi, this throws an error in MySQL < 5.6.5, look at previous messages and links.

vlakoff on 19 Jan 2016

Just use ->nullableTimestamps() if you are having a problem.

On Tue, Jan 19, 2016 at 8:35 AM, vlakoff [email protected] wrote:

jgrossi, this throws an error in MySQL < 5.6.5, look at previous messages
and links.

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

taylorotwell on 19 Jan 2016

@vlakoff I had no MySQL error using $table->timestamp('created_at')->useCurrent();. :-)

jgrossi on 19 Jan 2016

They solved this problem. Just composer update.

wojciechmruk on 20 Jan 2016

@jarodrejestracyjny i don't see any new commits, what do you mean by 'they solved this problem?'

Pezhvak on 20 Jan 2016

I think ->nullableTimestamps() instead of ->timestamps() is the better solution right now.And we can wait for next release,to got a good news.

kingpabel on 17 Feb 2016

hi every body i fixed problem create_at please do it: vendor/laravel/framework/srs/illuminate/database/schema/blueprint.php line:792

public function timestamps()
{
$this->timestamp('updated_at');

$this->timestamp('created_at')->useCurrent();

}
please do it exactly i write. and fix this problem.

arashlaghaeiyahoo on 3 Mar 2016

I don't quite understand the status of this bug.

I just tried the two migrations from the default install (using both 5.2.22 and 5.2.x-dev) on MySQL 5.5 (which is still the default in Debian stable and most other distributions) and the result is this:

CREATE TABLE `users` (
    ...
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    ...
);

CREATE TABLE `password_resets` (
    ...
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ...
);

All 3 columns came out wrong.

@GrahamCampbell I think this bug should be reopened.

tobia on 4 Mar 2016

@arashlaghaei if you think it solves the problem, you can open a pull request. Do not forget to explain in it why you have done this fix, what does it fix, etc.

rap2hpoutre on 4 Mar 2016

I just tried performing the same migrations on PostgreSQL and the timestamp columns are created without any option or trigger to auto-populate them. They are just plain timestamp columns with NULL default value:

CREATE TABLE users (
    ...
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone,
    ...
)

CREATE TABLE password_resets (
    ...
    created_at timestamp(0) without time zone NOT NULL
    ...
)

So are timestamp columns supported by any driver?

tobia on 4 Mar 2016

Over a year since this issue has been opened.
Currently running Laravel v5.3.28 in combination with MySQL 5.6.33, and still encountering the same issues.

Using the default function for adding created_at and updated_at fields in my tables, it leaves me with a 'on update CURRENT_TIMESTAMP' on the first encountered timestamp of the table. Which isn't even close to the right one.

How, after all this time, is this still an issue?

EDIT: The issue only occurs when using another Column of the Type 'TIMESTAMP' besides the 'updated_at' and 'created_at' ones in the table.

bojnys on 4 Jan 2017

I'm having a bit of a different issue here.

I have project that started out in 5.1, and has since been upgraded to 5.3.

Most of the tables were created in 5.1, so they have the old "not nullable" timestamps(). I also have some tables which were created in 5.3, and they have the new "nullable" timestamps(), and they work fine.

The problem is, when I try to run a migration to ALTER, the OLD pre-5.2 tables.

  [Illuminate\Database\QueryException]                                              
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value fo  
  r 'created_at' (SQL: alter table `products` add `brand_id` int unsigned null)     



  [Doctrine\DBAL\Driver\PDOException]                                               
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value fo  
  r 'created_at'                                                                    



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

Even though the fields I'm changing have nothing to do with timestamps, I'm unable to alter the table.

Additionally, I cannot migrate the timestamps to the new "nullable" ones because:

Not really sure what to do about this.

Nikita240 on 13 Jan 2017

@Nikita240 What version of MySQL are you using?

antonkomarev on 13 Jan 2017

@a-komarev v5.6.27

Nikita240 on 13 Jan 2017

Since timestamps() actually functions correctly and this is more of an "upgrade" issue, I decided to put this in a separate issue.

17313

Nikita240 on 13 Jan 2017

In your migration file please define first the "update_at" column then define the "create_at" column. In Laravel first coming column with default time_stamp value automatically set the attribute "on update". Now your migration file looks like below:

$table->timestamp('update_at');
$table->integer('created_by');
$table->timestamp('creation_at');

rahulkhannagar on 27 Aug 2019

Wow still had this issue on mysql5.7 and laravel 7.x...

Just one timestamp in the schema

$table->timestamp('date')

Makes it CURRENT_TIMESTAMP on update.. I was very surprised because data this behavior overwrites data.. This should not happen by default, only if explicitly enabled

Eg

$table->timestamp('date')->onUpdate('CURRENT_TIMESTAMP');

Tofandel on 8 Aug 2020

Framework: [5.2] Update column created_at, updated_at

Most helpful comment

All 112 comments

BEFORE

AFTER

17313

Related issues