Laravel & MySQL auto-adding “on update current_timestamp()” to timestamp fields

Mattias Geniar, Sunday, March 11, 2018

We hit an interesting Laravel "issue" while developing Oh Dear! concerning a MySQL table.

Consider the following database migration to create a new table with some timestamp fields.

Schema::create('downtime_periods', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('site_id');
    $table->foreign('site_id')->references('id')->on('sites')->onDelete('cascade');
    $table->timestamp('started_at');
    $table->timestamp('ended_at')->nullable();
    $table->timestamps();
});

This turns into a MySQL table like this.

mysql> DESCRIBE downtime_periods;
+------------+------------------+------+-----+-------------------+-----------------------------+
| Field      | Type             | Null | Key | Default           | Extra                       |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id         | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| site_id    | int(10) unsigned | NO   | MUL | NULL              |                             |
| started_at | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| ended_at   | timestamp        | YES  |     | NULL              |                             |
| created_at | timestamp        | YES  |     | NULL              |                             |
| updated_at | timestamp        | YES  |     | NULL              |                             |
+------------+------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

Notice the Extra column on the started_at field? That was unexpected. On every save/modification to a row, the started_at would be auto-updated to the current timestamp.

The fix in Laravel to avoid this behaviour is to add nullable() to the migration, like this.

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

To fix an already created table, remove the Extra behaviour with a SQL query.

MySQL> ALTER TABLE downtime_periods
CHANGE started_at started_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

Afterwards, the table looks like you'd expect:

mysql> describe downtime_periods;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| site_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| started_at | timestamp        | YES  |     | NULL    |                |
| ended_at   | timestamp        | YES  |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Lesson learned!



Hi! My name is Mattias Geniar. I'm a Support Manager at Nucleus Hosting in Belgium, a general web geek & public speaker. Currently working on DNS Spy & Oh Dear!. Follow me on Twitter as @mattiasgeniar.

Share this post

Did you like this post? Will you help me share it on social media? Thanks!

Comments

Gabriela Ferrara Friday, March 16, 2018 at 20:49 - Reply

I am going to assume you are using 5.7 here.

This is the documented behavior for columns that are TIMESTAMP and are defined as NOT NULL (only if they are the first on the table).

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

> The first TIMESTAMP column in a table, if not explicitly declared with the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is automatically declared with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.


developerbmw Thursday, July 5, 2018 at 12:00 - Reply

Thanks MySQL for the most idiotic shit ever. Wasted 30 mins trying to debug this BS. Sure I should have read the docs but that shit is seriously retarded. Why the hell would they arbitrarily assume you want the “first TIMESTAMP column in a table” reset on every update????


Leave a Reply

Your email address will not be published. Required fields are marked *