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.

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.