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'));

[aa-ndrej]

Another workaround I have used is to set the default values manually:

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

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

That way the columns do not have to be nullable.

[guoyunhe]

@aa-ndrej many ORMs allow your update a model's mics attributes without updating timestamps. I am afraid ON UPDATE CURRENT_TIMESTAMP will break these behavior. So here is my version:

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

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

[rodrigocorreard]

i have the same problem here. Was the problem not detected by the maintainers? It has been reported for some time and the topic remains unanswered "@adonisjs/lucid": "^18.3.0", MariaDB 10.3.34

[cpe-abdullah]

With database debugging enabled, it can be noticed after multiple tries that Lucid always sets created_at field as null during records updates, which conflicts with the very way by which MariaDB handles null values passed for TIMESTAMP fields as clearly stated here by always setting the field value to the current timestamp

I conducted the tests using Lucid v18.4.0 and MariaDB 10

This is an actual database debug output following an update statement using MariaDB:

"mysql" Author (10 ms) UPDATE `authors` SET `name` = ?, `updated_at` = ?, `biography` = ?, `created_at` = ? WHERE `id` = ? [ 'Test Author Mod', '2023-07-02 10:25:00', null, null, '12345' ]

I tried to pass the created_at field its original value using SQL client (without Lucid) and it behaves sanely, whereas, not passing the field a value at all will be considered as passing it a null value

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[RomainLanz]

Could you please add a proper test and setup a mariadb test suite?

[thetutlage]

I am not sure what exactly needs to be done here. If I read the original post, it mentions MariaDB self deciding to stick an ON UPDATE trigger for the first non-nullable timestamp field.

If my assumption is right, then there isn't much we could do apart from mentioning it explicitly inside the docs with this workaround. https://github.com/adonisjs/lucid/issues/924#issuecomment-1518926511

[jhbertra]

This was the default behavior for MariaDB until 10.10 https://mariadb.com/kb/en/timestamp/#automatic-values. This is thankfully no longer the case.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.