TypeORM throws QueryFailedError Table already exists on MySQL when synchronize is true

In my case the problem was caused by

  synchronize: true
  migrationsRun: true

in

typeOrmConfig

It should be only synchronize or migrationsRun set to true, not both

I had the same issue and I tried all the answers in this post but nothing worked.

This was my entity:

@Entity()
export class Photo {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ length: 500 })
    name: string;
}

This created a table called photo with the schema public.

What fixed it for me was actually providing the table name and the schema :

@Entity({ name: 'photo', schema: 'public' })
export class Photo {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ length: 500 })
    name: string;
}

For some reason the synchronize feature of TypeORM has a problem if the table is uppercase and if the schema is not set. After setting the schema and the table name (lower case) it worked with synchronize: true.

For me the solution was to change the name of the database in the config of TypOrmModule to lowercase to match the created schema on the mysql server.

For some reason it's allowed to be different on first setup of TypeOrm.

I can almost guarantee you that issue has to do with your entity name being uppercase i.e.

@Entity({ name: "Users"})

TypeORM doesn't seem to like this an should rather use:

@Entity({ name: "users"})

Have a look at this thread for more info https://github.com/typeorm/typeorm/issues/4420

I had the same issue, but I just set the migrationsRun to false on my ormconfig.ts file and it worked.

synchronize: false,
migrationsRun: false,

I think this issue is caused by the orm trying to automatically run migrations when we have already done it manually.

I had similar issue and I solved it finally, what I found was I had set synchronize to false at first and then true and did some tests, created/generated migrations keeping synchronize true. I also had some migrations generated but not applied. Later I set synchronize to false and when I tried to apply migration, it gave me error like you mentioned. I think the unapplied piles of generated migrations gave me the issues.

So, for me what solved the issue is, I deleted all the migration files and then regenerated the migration and applied that migration. That solved the issue.

I had the exact same error. I tried everything:

• reduce entities/tables to a minimum
• change TypeOrm options (synchronize, autoLoadEntites, entities, migrationsRun,...)
• moved TypeOrm configuration into main.ts or .json
• updated MySQL version (including a complete removal on MAC)
• re-initialized the entire project with new versions of nestJS and TypeOrm

=> Conclusion: with "synchronize":true it never worked when the tables already existed. But for development you really want that feature to be enabled...

=> I had installed 'mysql2' (see package.json), because 'mysql' did not work with my MySQL (setup). I always got an AUTHENTICATION ERROR with mysql.

=> Solution: Use 'mysql' (not 2) and - if it doesn't work (AUTH ERROR) - re-install (or configure manually) MySQL (newest version) and select old authentication method during install.

Hope that helps (I did not find this solution anywhere). Cheers

In my case I just set

@Entity({ name: 'User' })
export class User {...} 

instead

@Entity()
export class User {...}

and 'Oh magic', error come out.

Removing the dist folder solved it for me

Been a while, but a possible cause could be you've not added an entities glob? https://typeorm.io/#/using-ormconfig/using-environment-variables

  entities: '*.entity.ts',

I think that should shove the issues as it'll create an entities map. If not try dropping the table, let it create it and then try sync again?

rebuild your app before running the migration i.e run nest build or yarn build depending on how you set the command in your package.json

If all the other answers don't help, for me the issue was I copied a model and forgot to change the name!

Silly mistake but don't have 2 models with the same name :D

@Entity({ name: "users"})

TypeOrmModule.forRoot({
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'gy0601xq',
      database: 'mydev',
      entities: [Coffee, Flavor],
      synchronize: true,
      dropSchema: true,//this option maybe helpful
    }

Drops the schema each time connection is being established. Be careful with this option and don't use this in production - otherwise you'll lose all production data. This option is useful during debug and development.

I'm using MySql as a database.

My issue was related to the name of entities.

All letters of the table name should be lowercase

In my case, the table name is proclamationHistory but does not work.

@Entity ({name: "proclamationHistory"})
export class ProclamationHistoryEntity {
  ...
}

It worked when I changed the name to proclamationhistory or proclamation_history

check out the name of all entities in your project to be lowercase.

NOTE: I did not have this problem with PostgreSQL.

Please checkout this link

Mysql table name not working in uppercase

1. Please Check TypeOrmModule.forRoot({ ..., ...., database: 'test', //All lower case entities: ['dist/**/*.entity{.ts,.js}'], synchronize: true, })
2. Keep @Entity() empty.

Those works for me.

in my case the solution is that the object name should be write in lowercase

@Entity('profileSkills') // don't work for me
export class ProfileSkillsEntity {
  @PrimaryGeneratedColumn()
  id: number;
}


@Entity('profile-skills') // solve my problem
export class ProfileSkillsEntity {
  @PrimaryGeneratedColumn()
  id: number;
}  

also check that your name of database, it should be in lowercase too.

MYSQL_DATABASE=testDb // don't know but don't work for me

MYSQL_DATABASE=testdb // solved my issue

in app.module.ts should be

{
...
  useFactory: (configService: ConfigService) => ({
    ...
    logging: true,
    migrationsRun: false,
    synchronize: true,
}

in my case, i import databaseProvider twice

// database.module.ts
import { Module } from "@nestjs/common";
import { databaseProviders } from "./database.provider";

@Module({
  providers: [...databaseProviders],
  exports: [...databaseProviders],
})
export class DatabaseModule {}

// app.module.ts
@Module({
  imports: [DatabaseModule],
  controllers: [],
  providers: [...databaseProviders],
})
export class AppModule {}

i remove databaseProvider in app.module.ts and it work

For everyone else struggling with this synchronize: true alone did not the job for me and I had to add dropSchema: true.

Note that those settings are meant to be use for development purposes only, using those on production will make you lose data 100%. Use migrations instead.

nestjs don't remove all files of dist folder when do rebuild with start:dev, for solve this issue you can do this thinks:

• delete dist folder and rebuild project
• set synchronize to false (if you want to create another entity you need to do fist option)

Is so probably u have 2 User.entity.js in dist folder.

(sorry if are some orthography error)

It works fine for me

        TypeOrmModule.forRoot(
        {
            type: 'mysql',
            host: process.env.MYSQL_HOST,
            port: +process.env.MYSQL_PORT,
            username: process.env.MYSQL_USERNAME,
            password: process.env.MYSQL_PASSWORD,
            database: database, // database name should be lowercase
            autoLoadEntities: true,
            synchronize: true,
            migrationsRun: false
        }),

and

@Entity({ name: 'accounts' }) // table name should be lowercase

making synchronize: false worked for me. However, with this approach is, it won't create a table automatically anymore. It assumes that the table already exists.