Synchronize issue with UUID (MySQL)

[drjamesj]

Issue type:

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

Database system/driver:

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

TypeORM version:

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

Steps to reproduce or a small repository showing the problem:

I am running into issues with synchronize setting on TypeORM with UUID PrimaryGeneratedColumns on MySQL driver. It seems each time application loads it tries to re-index, so it drops the id column and tries to add it again. Issue is that since it's UUID, for MySQL the column type is varchar(36) NOT NULL PRIMARY KEY. So we run into duplicate entry '' for key 'PRIMARY' because UUID is not set.

To recreate issue, you can do it like this:

typeorm init --name MyProject --database mysql

Edit your User.ts entity to change primary key to UUID.

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm";

@Entity()
export class User {

    @PrimaryGeneratedColumn('uuid')
    id: string;

    @Column()
    firstName: string;

    @Column()
    lastName: string;

    @Column()
    age: number;

}

Ensure synchronize is set to true in ormconfig.json. Start the app and it will insert sample User. Restart app and synchronize will run and try and drop and re-add the column and give error.

  message: 'ER_DUP_ENTRY: Duplicate entry \'\' for key \'PRIMARY\'',
  code: 'ER_DUP_ENTRY',
  errno: 1062,
  sqlMessage: 'Duplicate entry \'\' for key \'PRIMARY\'',
  sqlState: '23000',
  index: 0,
  sql:
   'ALTER TABLE `user` ADD `id` varchar(36) NOT NULL PRIMARY KEY',
  name: 'QueryFailedError',
  query:
   'ALTER TABLE `user` ADD `id` varchar(36) NOT NULL PRIMARY KEY',
  parameters: []

[pleerock]

please provide some reproduction

[drjamesj]

Hi @pleerock , I wrote some steps above to reproduce the issue, did I miss something?

[chudnyi]

Same issue

[pleerock]

do you have records in your database before you change it to uuid?

[drjamesj]

No records before changing it. But once set to UUID, I then insert sample data which all works fine. Upon restarting app it tries to synchronize by dropping the column and adding it again, which fails because it's not generating a UUID at that point.

[pleerock]

Sounds like uuid isn't being generated

[drjamesj]

Thanks, how to fix?

[pleerock]

first we need to write a test that will with this issue, then we need to find a place where its being generated and see why its not generating (if its an issue) then fix it.

[vladimir-kanev]

Hitting the same problem.

Do you mean it is expected to drop the primary key column? What if its values are referenced and used somewhere else?

[sangheestyle]

We have a similar problem.

By the way, I have a question. Bipolar said 'Upon restarting app it tries to synchronize by dropping the column and adding it again'. I saw the same log with mysql 5.7. Can anybody tell me why columns are dropped?

[sangheestyle]

@pleerock Hi! Could you tell me where do I start to fix this problem? I want to spend time to solve this issue.

[pleerock]

fix in #3234 caused this bug. I pushed fix and released 0.2.12-rc.2 with this fix.

[tlimp]

The issue is closed, but ist fixed? I am experiencing the exact same issue with the aurora-data-api driver.
Is this a general issue, or some implementation details in the driver?

[Cobular]

Yep experienced the same in the postgres driver, it doesn't seem to be dropping everything in the synchronize.

[pleerock]

Please create a new issue with the new details if you face problems.

[N-Andronopoulos]

Regression 0.3.16 related to #10090