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

Question

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.

Answer 1

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

Answer 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.

Answer 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.