Laravel changes created_at on update

Question

I found this answer on the subject, but it doesn't work for me.

So, I make an entry in the database:

// Write lead to database
$lead = Lead::create($lead_data);

And the timestamps look like this, which is good:

| 2016-01-08 10:34:15 | 2016-01-08 10:34:15 |

But then I make a request to an external server, and I need to update the row:

$lead->user_id = $response['user_id'];
$lead->broker_id = $response['broker_id'];
$lead->save();

and the created_at field gets changed:

| 2016-01-08 04:34:17 | 2016-01-08 10:34:17 |

How do I solve this problem?

EDIT

I need a solution that would just modify the behavior without dropping columns or resetting migrations. The fix has to be performed on a live database without touching the data. As suggested below, I tried the following migration:

$table->datetime('created_at')->default(DB::raw('CURRENT_TIMESTAMP'))->change();

but nothing happens. The created_at field still gets modified on update.

Solution 1

If you're on Laravel 5.2 and using MySQL, there was a bit of a "bug" introduced with the timestamps. You can read all about the issue on github here. It has to do with the timestamp defaults, and MySQL automatically assigning DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes under certain conditions.

Basically, you have three options.

1. Update MySQL variable:

If you set the explicit_defaults_for_timestamp variable to TRUE, no timestamp column will be assigned the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes automatically. You can read more about the variable here.

2. Use nullable timestamps:

Change $table->timestamps() to $table->nullableTimestamps(). By default, the $table->timestamps() command creates timestamp fields that are not nullable. By using $table->nullableTimestamps(), your timestamp fields will be nullable, and MySQL will not automatically assign the first one the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes.

3. Define the timestamps yourself:

Instead of using $table->timestamps, use $table->timestamp('updated_at'); $table->timestamp('created_at'); yourself. Make sure your 'updated_at' field is the first timestamp in the table, so that it will be the one that is automatically assign the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes.

Solution 2

Skatch - I think your solution above is not quite right, but is probaby fine in this case.

The issue is that you are getting the PHP date, not the default MYSQL timestamp for your default. When you run that migration you end up with a statement like this:

alter table alter column created_at default '2016:02:01 12:00:00';

Notice the string for your date. When you run your migration THAT date will always be used for your created_at, not the current date when a record is added days later.

Instead of doing "date('Y:m:d H:i:s')", you can do DB::raw('current_timestamp') to address this issue.

(sry, couldn't just add a comment above - my reputation is not high enough yet...)

Solution 3

That must be an issue on your table field definition. I faced the same issue recently, and when I check my table, the created_at has an "EXTRA" with value on update CURRENT_TIMESTAMP, to have Laravel behave normally just update the field definition with something like:

ALTER TABLE table_name CHANGE created_at created_at timestamp NOT NULL default CURRENT_TIMESTAMP;

Solution 4

Posting this as a top level answer, summarizing our comment discussion.

First, there is a date bug introduced in Laravel - as noted by @patricus. The suggested solutions in the bug discussion are to either use nullableTimestamps() instead of just timestamps(), or to create the created_at and updated_at fields directly - $table->timestamp('updated_at')->change().

This can also be fixed with raw SQL. An ALTER statement similar to this

alter table loader_rawvalues MODIFY column updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

That can be applied directly to your existing DB tables (Test it first, or course!). OR you can use DB::unprepared() to apply it from your migration - for example:

class CreateMyTable extends Migration
{
    public function up()
    {
        Schema::create('mytable', function (Blueprint $table) {
            $table->bigIncrements('id');
            // ...
            $table->timestamps();
        });

        DB::unprepared('alter table mytable MODIFY column updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');
    }
}

View more solutions

Comments

[Skatch]

I need a solution I can implement on a live running database. Resetting migrations and dropping columns is not an option.

[Skatch]

You are right, I didn't think of that. Anyway, I tried everything, including your suggestion $table->datetime('created_at')->default(DB::raw('CURRENT_TIM‌​ESTAMP'))->change();‌​, but this was the only way it worked... Next time I'll try better to do it with SQL.

[sgrover]

Do you need to do this via a migration? The native SQL command is straight forward: "alter table mytable modify column mycolumn timestamp default current_timestamp NOT NULL on update current_timestamp;" Run that on the DB directly and everything should be fixed. (test first!). If you MUST do it via a migration, you can do it with DB::unprepared($MyRawSQL);. Something like public function up() { Schema::create(....); DB::unprepared($MySQL); }

[sgrover]

- That also bypasses the bug that @patricus mentioned.

[Skatch]

Yeah, raw DB query should fix this... thanks for your input, will come in handy for the next project (this one works just fine now). Write a complete solution in the answer and I'll give you the tick :)

[Skatch]

I think you need to alter the created_at field without an ON UPDATE statement, updated_at works fine. :)

[Phillip Davies]

To confirm, if you need to run a migration to fix the updated_at issue, based on sgrover's solution you can add the following to your migration for all the tables that require fixing without dropping: DB::unprepared('alter table faqs modify column created_at timestamp default current_timestamp NOT NULL'); along with DB::unprepared('alter table faqs modify column updated_at timestamp default current_timestamp NOT NULL'); This will set the TIMESTAMP to default to the CURRENT TIMESTAMP and remove the extra ON UPDATE rule.

[ymakux]

This silly bug still exists in Lara8. I had only "created_at" field, so I had to override the UPDATED_AT constant. If it's null - "created_at" changes on every update. FALSE solved the problem