MySQL timestamp columns default to ON UPDATE CURRENT TIMESTAMP
A quick note about a footgun in MySQL
with timestamp colummns, which I happened to notice when using migrations in
the knex.js library.
TLDR: older versions of MySQL (<= 5.7) default to adding a
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP clause to timestamp
columns unless otherwise specified.
Documentation here: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
For example, if you create a table like this in older versions of MySQL:
CREATE TABLE `foobar_table`
(
`foobar_column` timestamp
)
You’ll actually get a column like this:
`foobar_column` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Note the DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. This means it
will automatically be initialised to the current time stamp on new rows, and
also updated to the current timestamp whenever other columns on a row are
updated.
Similarly, if you use the timestamp() method in a Knex migration like this
for a MySQL database:
exports.up = async (knex) => {
await knex.schema.createTable("foobar_table", async (table) => {
table.timestamp("foobar_column");
});
};
It will create the column with the default and update trigger as above.
This may be the behaviour you were expecting, but it was a surprise to me. I
discovered this behaviour because I needed a timestamp column to store
incoming timestamps on messages from another system, and was expecting to
manage the values in this column myself. Later I noticed that this timestamp
column was getting refreshed on any update to a row and spotted the unexpected
behaviour.
Other than configuring MySQL differently or using a more recent version of
MySQL, the simplest workaround to avoid this behaviour from MySQL might be to
use a datetime column instead of a timestamp:
CREATE TABLE `foobar_table`
(
`foobar_column` datetime
)
exports.up = async (knex) => {
await knex.schema.createTable("foobar_table", async (table) => {
table.dateTime("foobar_column");
});
};
Then you don’t get the automatic default and update trigger.