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!