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

I am using NestJS, TypeORM, and MySQL to build a web application.

I am using a .env file to pass in some environment variables for connecting to my local database. Synchronize is set to true.

app.module.ts

@Module({
    imports: [
        ConfigModule.forRoot({ envFilePath: '.env' }),
        TypeOrmModule.forRootAsync({
            imports: [ConfigModule],
            useFactory: (configService: ConfigService) => ({
                type: 'mysql',
                host: configService.get('TYPEORM_HOST'),
                port: configService.get('TYPEORM_PORT'),
                username: configService.get('TYPEORM_USERNAME'),
                password: configService.get('TYPEORM_PASSWORD'),
                database: configService.get('TYPEORM_DATABASE'),
                autoLoadEntities: true,
                synchronize: configService.get('TYPEORM_SYNCHRONIZE'),
            }),
            inject: [ConfigService],
        }),
    ],
    controllers: [AppController],
    providers: [AppService],
})
export class AppModule {
    constructor(private connection: Connection) {}
}

In addition, I have a Users module which imports a user entity.

users.module.ts

import { User } from './Entities/User.entity';

@Module({
    imports: [TypeOrmModule.forFeature(
        [User]
    )],
    controllers: [UsersController],
    providers: [UsersService],
})
export class UsersModule { }

User.entity.ts

@Entity({ name: "Users"})
export class User {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    userName: string;

    @Column()
    firstName: string;

    @Column()
    lastName: string;

    @Column()
    email: string;
}

Assuming, my database is empty, when starting up the application for the very first time, TypeORM synchronization will automatically create the User table for me with the appropriate columns and data types. However, when closing the application and trying to restart, I get this error:

[Nest] 14876   - 06/17/2020, 12:37:33 PM   [ExceptionHandler] Table 'users' already exists +3ms
QueryFailedError: Table 'users' already exists
    at new QueryFailedError (C:\MyProject\Server\node_modules\typeorm\error\QueryFailedError.js:11:28)
    at Query.onResult (C:\MyProject\Server\node_modules\typeorm\driver\mysql\MysqlQueryRunner.js:170:45)
    at Query.execute (C:\MyProject\Server\node_modules\mysql2\lib\commands\command.js:30:14)
    at PoolConnection.handlePacket (C:\MyProject\Server\node_modules\mysql2\lib\connection.js:417:32)
    at PacketParser.onPacket C:\MyProject\Server\node_modules\mysql2\lib\connection.js:75:12)
    at PacketParser.executeStart (C:\MyProject\Server\node_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket.<anonymous> (C:\MyProject\Server\node_modules\mysql2\lib\connection.js:82:25)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:302:12)
    at readableAddChunk (_stream_readable.js:278:9)

If I then turn synchronization off and start it up again, I get the same error. My only workaround is to drop the users table, turn synchronization back on in order to recreate it and then turn it back off. Is there a reason why TypeORM tries to re-create a table that already exists? For ease-of-use, I'd like to not have to always remember to toggle synchronization every time.

Solution 1:^[1]

In my case the problem was caused by

  synchronize: true
  migrationsRun: true

typeOrmConfig

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

Solution 2:^[2]

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.

Solution 3:^[3]

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.

Solution 4:^[4]

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

Solution 5:^[5]

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.

Solution 6:^[6]

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.

Solution 7:^[7]

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

Solution 8:^[8]

In my case I just set

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

instead

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

and 'Oh magic', error come out.

Solution 9:^[9]

Removing the dist folder solved it for me

Solution 10:^[10]

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?

Solution 11:^[11]

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

Solution 12:^[12]

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"})

Solution 13:^[13]

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.

Solution 14:^[14]

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

Solution 15:^[15]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	kashlo
Solution 2	Shamshiel
Solution 3	Yoran
Solution 4	Tlotlo
Solution 5	Alex Santiago Durango García
Solution 6	Bhuwan Adhikari
Solution 7	kerimrohs
Solution 8	mark
Solution 9	DÅ¾an Operta
Solution 10	bashleigh
Solution 11	Nelson Bwogora
Solution 12	Vasil Enchev
Solution 13	guyanglovexuqing
Solution 14
Solution 15	Brijesh Kumar

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

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Solution 12:[12]

Solution 13:[13]

Solution 14:[14]