Created_at column is marked with ON UPDATE TIMESTAMP in MariaDB

[wallysonn]

I noticed that my records in the database all have their creation dates changed. I noticed that when executing a "save", as per the code example below, even if I am just editing a record, the creation date is also changed and it does not respect my timezone.

let attendance: Attendance | null = await Attendance.query()
      .where('platform_identification', data.platformIdentification)
      .where('platform', data.platform)
      .where('service_name', data.serviceName)
      .first()

if (attendance) {
await attendance.merge({
"duration": duration
}).save()
}

This is my table after migration:

CREATE TABLE `attendances` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `start_at` timestamp NULL DEFAULT NULL,
  `status_at` timestamp NULL DEFAULT NULL,
  `end_at` timestamp NULL DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `await_time` int(11) DEFAULT NULL,
  `platform_identification` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `platform` varchar(255) NOT NULL,
  `channel` varchar(255) DEFAULT NULL,
  `channel_alias` varchar(255) DEFAULT NULL,
  `service_name` varchar(255) NOT NULL,
  `input` varchar(255) NOT NULL,
  `platform_status` varchar(255) DEFAULT NULL,
  `platform_attendance_status` varchar(255) DEFAULT NULL,
  `status_id` int(10) unsigned NOT NULL,
  `kanban_phase_id` int(10) unsigned DEFAULT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `store_id` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `attendances_uuid_unique` (`uuid`) USING BTREE,
  KEY `attendances_status_id_foreign` (`status_id`) USING BTREE,
  KEY `attendances_kanban_phase_id_foreign` (`kanban_phase_id`) USING BTREE,
  KEY `attendances_customer_id_foreign` (`customer_id`) USING BTREE,
  KEY `attendances_store_id_foreign` (`store_id`) USING BTREE,
  KEY `attendances_user_id_foreign` (`user_id`) USING BTREE,
  CONSTRAINT `attendances_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `attendances_kanban_phase_id_foreign` FOREIGN KEY (`kanban_phase_id`) REFERENCES `kanban_phases` (`id`) ON DELETE CASCADE,
  CONSTRAINT `attendances_status_id_foreign` FOREIGN KEY (`status_id`) REFERENCES `attendance_statuses` (`id`),
  CONSTRAINT `attendances_store_id_foreign` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE,
  CONSTRAINT `attendances_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Package version

"@adonisjs/core": "^5.8.0",
"@adonisjs/lucid": "^18.4.0",

Node.js and npm version

18.17.0

Sample Code (to reproduce the issue)

In my migration, the timestamp columns are created like this:

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

[wallysonn]

I solved it in my case just by changing it

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

to
table.timestamps(true, true)
in the migration files. In any case, I would like to know if this behavior is expected or if it really is a bug that will be resolved in the future. Thank you for your great work.

[thetutlage]

This is weird, because the table.timestamp('created_at') should not be adding current_timestamp() ON UPDATE current_timestamp().

Can you please share the complete migration file for this table?

[wallysonn]

This issue with dates is screwing me over. Now when I try to use a "merge" to update a record, I get this error:

update sales set created_at = '2023-12-14T15:36:27.000-03:00', updated_at = '2023-12 -14 15:52:22', status = 'sale' where id = 2 - Incorrect datetime value: '2023-12-14T15:36:27.000-03:00' for column bx.sales .created_at at row 1

This is my code:

let saleModel = await Sale.findByOrFail('uuid', sale.uuid)
saleModel.merge({
...sale,
status: state,
})
await saleModel.save()

[wallysonn]

Isso é estranho, porque table.timestamp('created_at') não deveria adicionar current_timestamp() ON UPDATE current_timestamp().

Você pode compartilhar o arquivo de migração completo desta tabela?

Hello. The problem happens with all tables. Apparently those who use Postgre do not have this problem, but MySQL or MariaDB users are suffering.

import BaseSchema from '@IOC:Adonis/Lucid/Schema'
export default class extends BaseSchema {
protected tableName = 'attendances'

public async up() {
this.schema.createTable(this.tableName, (table) => {
table.increments('id')
table.uuid('uuid').notNullable().unique()
table.timestamp('created_at', { useTz: true }).notNullable()
table.timestamp('updated_at', { useTz: true }).notNullable()
table.timestamp('deleted_at' ).nullable()
table.timestamp('start_at').nullable()
table.timestamp('status_at').nullable()
table.timestamp('end_at' ).nullable()
table.integer('duration').nullable()
table.integer('await_time').nullable()
table.string('platform_identification').notNullable()
table.string('type').notNullable()
table.string('platform').notNullable()
table.string('channel').nullable()
table.string('channel_alias').nullable()
table.string('service_name').notNullable()
table.string('input').notNullable()
table.string('platform_status').nullable()
table.string('platform_attendance_status').nullable()
table
.integer('status_id')
.unsigned()
.notNullable()
.references('id')
.inTable('attendance_statuses') //status do atendimento
table
.integer('kanban_phase_id')
.unsigned()
.nullable()
.references('id')
.inTable('kanban_phases')
.onDelete('CASCADE') //fase do kanban
table
.integer('customer_id')
.unsigned()
.notNullable()
.references('id')
.inTable('customers')
.onDelete('CASCADE')
table
.integer('store_id')
.unsigned()
.nullable()
.references('id')
.inTable('stores')
.onDelete('CASCADE')
table
.integer('user_id')
.unsigned()
.notNullable()
.references('id')
.inTable('users')
.onDelete('CASCADE')
})
}

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

[thetutlage]

Duplicate of #924