MySQL Timestamp Initialization

During testing of a new migration, we discovered that timestamps in an audit table were suddenly reset to the same timestamp (close to now). That tripped up quite some nerves. After some investigation, it turned out that MySQL and MariaDB may have dangerous default behavior when working with columns of type timestamp. Suppose you declare a an audit event table like this:

CREATE TABLE `Event` (
  `id` varchar(128) NOT NULL,
  `createdOn` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

When reading back the table definition (e.g. via MySQL Workbench), you will find it’s actually:

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Note the automatic addition of DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP for the createdOn column. This creates two problems:

• the timestamp values set by your code may not match the timestamps stored in the database
• the timestamps on the table become mutable, i.e. in case a migration runs an UPDATE on the table all existing timestamps will be overwritten.

The source of this behavior is Automatic Initialization and Updating for TIMESTAMP and DATETIME, which is controlled by the explicit_defaults_for_timestamp configuration variable and also depends on the active SQL Mode.

To see your active configuration, run this SQL on your active connection

SHOW Variables WHERE Variable_name = "explicit_defaults_for_timestamp";
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

In our case, explicit_defaults_for_timestamp was off, which specifically results in the observed behavior:

The first TIMESTAMP column in a table, if not explicitly declared with the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is automatically declared with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

Another caveat: Your database team or provider may not have explicitly configured explicit_defaults_for_timestamp. To add insult to injury, the default value for this variable depends on your MySQL Version.

Explicitly Controlling Timestamp Initialization

It\’s bad news when the behavior of your application depends on a database configuration variable outside of your team\’s direct control. We thus have to fix up our tables right after creating them as suggested in this stackoverflow answer and adapted for MariaDB:

ALTER TABLE `Event` MODIFY COLUMN `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `Event` ALTER COLUMN `createdOn` DROP DEFAULT;

Note: These statements are idempotent, i.e. we can safely run them even if explicit_defaults_for_timestamp is ON and our table will have the desired state, i.e. a timestamp column with no DEFAULT and no ON_UPDATE clause.

We also added a test to our migration test suite that verifies all timestamp columns are created as intended and no hidden behavior messes with our column definitions.