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!