Timestamp behavior on MariaDB is strange but MySQL is fine

[guoyunhe]

Package version

18.3.0

Node.js and npm version

18.15.0 9.5.0

Sample Code (to reproduce the issue)

Make a very simple model with created_at and updated_at:

export default class Post extends BaseModel {
  /** ID */
  @column({ isPrimary: true })
  public id: number;

  @column()
  public title: string;

  /** Timestamp of creation */
  @column.dateTime({ autoCreate: true, autoUpdate: false })
  public createdAt: DateTime;

  /** Timestamp of modification */
  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime;
}

And table migration:

export default class extends BaseSchema {
  protected tableName = 'posts';

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id');
      table.string('title');
      table.timestamp('created_at', { useTz: true });
      table.timestamp('updated_at', { useTz: true });
    });
  }

  public async down() {
    this.schema.dropTable(this.tableName);
  }
}

In controller, I update an existing model:

post.title = 'foobar';
await post.save();

In MySQL, created_at stays the same while updated_at get updated. Work as expected.

But in MariaDB, both created_at and updated_at got updated, which is unexpected.

Table scheme in MariaDB:

+-------------+------------------+------+-----+---------------------+-------------------------------+
| Field       | Type             | Null | Key | Default             | Extra                         |
+-------------+------------------+------+-----+---------------------+-------------------------------+
| id          | int(10) unsigned | NO   | PRI | NULL                | auto_increment                |
| title       | varchar(255)     | YES  |     | NULL                |                               |
| created_at  | timestamp        | NO   |     | current_timestamp() | on update current_timestamp() |
| updated_at  | timestamp        | NO   |     | 0000-00-00 00:00:00 |                               |
+-------------+------------------+------+-----+---------------------+-------------------------------+

created_at is clearly wrong.

I checked MariaDB's doc and it seems by design that MariaDB will make first not null timestamp column auto-update:

CREATE TABLE t3 (id INT, ts1 TIMESTAMP, ts2 TIMESTAMP);

INSERT INTO t3(id)  VALUES (1),(2);

SELECT * FROM t3;
+------+---------------------+---------------------+
| id   | ts1                 | ts2                 |
+------+---------------------+---------------------+
|    1 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 |
|    2 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 |
+------+---------------------+---------------------+

DESC t3;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id    | int(11)   | YES  |     | NULL                |                             |
| ts1   | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| ts2   | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------+-----------+------+-----+---------------------+-----------------------------+

Well, in MySQL doc, you have to manually specify default and auto update... (if i understand it correctly)

In short, MySQL and MariaDB treat your first TIMESTAMP NOT NULL column differently:

# Your SQL
created_at TIMESTAMP NOT NULL;
updated_at TIMESTAMP NOT NULL;

# MySQL creates (Good)
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| ts1   | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
| ts2   | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------+-----------+------+-----+---------------------+-----------------------------+

# MariaDB creates (Bad)
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| ts1   | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| ts2   | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------+-----------+------+-----+---------------------+-----------------------------+

A workaround can be, force the column to be nullable:

table.timestamp('created_at', { useTz: true }).nullable();
table.timestamp('updated_at', { useTz: true }).nullable();

If you don't like nullable, you can set a raw default:

table.timestamp('created_at', { useTz: true }).notNullable().defaultTo(this.raw('CURRENT_TIMESTAMP'));
table.timestamp('updated_at', { useTz: true }).notNullable().defaultTo(this.raw('CURRENT_TIMESTAMP'));