Migration keeps changing @CreateDateColumn/@UpdateDateColumn timestamp column to same definition

[cowpewter]

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[ ] latest
[ ] @next
[x] 0.2.16 (or put your version here)

Steps to reproduce or a small repository showing the problem:

I have a bunch of tables with my create- and updateTimestamp columns defined as follows:

  @CreateDateColumn({ type: 'timestamp', precision: null, default: () => 'CURRENT_TIMESTAMP' })
  createTimestamp: Date;

  @UpdateDateColumn({ type: 'timestamp', precision: null, default: () => 'CURRENT_TIMESTAMP' })
  updateTimestamp: Date;

When I run migration:generate, typeorm is repeatedly (even after running it) generating a migration file that changes the column definition to the same definition it already has. You can tell because the up and down methods do the exact same thing. I would expect the migration to be empty.

Example migration file

import {MigrationInterface, QueryRunner} from "typeorm";

export class TestTimestamps1555253092006 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("ALTER TABLE `album` CHANGE `createTimestamp` `createTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP");
        await queryRunner.query("ALTER TABLE `album` CHANGE `updateTimestamp` `updateTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP");
    }

    public async down(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("ALTER TABLE `album` CHANGE `updateTimestamp` `updateTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP");
        await queryRunner.query("ALTER TABLE `album` CHANGE `createTimestamp` `createTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP");
    }

}

I can work around it by defining my create- and updateTimestamp columns as follows

  @Column('timestamp', {
    default: () => 'CURRENT_TIMESTAMP',
  })
  createTimestamp: Date;

  @Column('timestamp', {
    default: () => 'CURRENT_TIMESTAMP',
    onUpdate: 'CURRENT_TIMESTAMP',
  })
  updateTimestamp: Date;

But it's a shame that it's not working with the @CreateDateColumn and @UpdateDateColumn decorators.

[guileen]

same issue. also index .

export class patch21558112496598 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("DROP INDEX `post_refPostId_likeCount_index` ON `post`");
        await queryRunner.query("DROP INDEX `post_createAt_index` ON `post`");
        await queryRunner.query("ALTER TABLE `post` DROP COLUMN `createAt`");
        await queryRunner.query("ALTER TABLE `post` ADD `createAt` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)");
        await queryRunner.query("ALTER TABLE `post` DROP COLUMN `updateAt`");
        await queryRunner.query("ALTER TABLE `post` ADD `updateAt` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)");
        await queryRunner.query("CREATE INDEX `post_refPostId_likeCount_index` ON `post` (`refPostId`, `likeCount`)");
        await queryRunner.query("CREATE INDEX `post_createAt_index` ON `post` (`createAt`)");
    }

    public async down(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("DROP INDEX `post_createAt_index` ON `post`");
        await queryRunner.query("DROP INDEX `post_refPostId_likeCount_index` ON `post`");
        await queryRunner.query("ALTER TABLE `post` DROP COLUMN `updateAt`");
        await queryRunner.query("ALTER TABLE `post` ADD `updateAt` datetime(6) NOT NULL DEFAULT 'CURRENT_TIMESTAMP(6)'");
        await queryRunner.query("ALTER TABLE `post` DROP COLUMN `createAt`");
        await queryRunner.query("ALTER TABLE `post` ADD `createAt` datetime(6) NOT NULL DEFAULT 'CURRENT_TIMESTAMP(6)'");
        await queryRunner.query("CREATE INDEX `post_createAt_index` ON `post` (`createAt`)");
        await queryRunner.query("CREATE INDEX `post_refPostId_likeCount_index` ON `post` (`refPostId`, `likeCount`)");
    }

}

[sandokanelcojo]

EDIT: Probably a duplicate of #2737.

Same problem, happens also with foreign keys.

await queryRunner.query("ALTER TABLE `session` DROP FOREIGN KEY `FK_8e05f295cd772ec97ef56642192`");
await queryRunner.query("ALTER TABLE `session` ADD CONSTRAINT `FK_8e05f295cd772ec97ef56642192` FOREIGN KEY (`user`) REFERENCES `user`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION");

[jeremija]

@sandokanelcojo can you confirm the relevant fk in the session table already has on delete no action on update no action clause? Because I think that was added in a recent version of TypeORM and that's why the migration generator wants to recreate the fk.

I wrote about the problem with createDate/updateDate in #2737, looks like a recent version of MariaDB started returning default value current_timestamp(6) in lowercase, and it used to be in uppercase so the comaprison fails.

[sandokanelcojo]

Hi @jeremija :
Yes, it does. The session FK has both clauses, and yet it tries to recreate them each time.
I'll take a look at #2737, thanks.

[koldoon]

Any progress on this? Really annoying thing.