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.