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

Oh Dear! monitors your entire site, not just the homepage. We crawl and search for broken pages and mixed content, send alerts when your site is down and notify you on expiring SSL certificates.

Start your free 10 day trial! »

Image of Mattias Geniar

Mattias Geniar, March 11, 2018

Follow me on Twitter as @mattiasgeniar

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!

Will you help me share this post?

It would mean a lot to me if you could help share this post on social media. 🤗