Table already exists | NestJS | Page 1

verbal barn Jun 7, 2023, 1:54 PM

#

Hi, could anybody know why this error occurred? Every time except first time run “npm run start:dev”, the terminal says: the table already exists

#

I’m use typeorm and MySQL

rustic forge Jun 7, 2023, 1:55 PM

#

which version of typeorm? run npm ls typeorm

#

what about this https://stackoverflow.com/questions/62434734/

Stack Overflow

TypeORM throws QueryFailedError Table already exists on MySQL when ...

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.

verbal barn Jun 7, 2023, 1:58 PM

#

Let me try

#

Not working, sir

#

The error is still there

#

@rustic forge

rustic forge Jun 7, 2023, 2:03 PM

#

you can enable typeorm logs to see the sql queries
logging: ['query']

verbal barn Jun 7, 2023, 2:03 PM

#

#

My typeorm

rustic forge Jun 7, 2023, 2:03 PM

#

what I know is that that error is related with synchronize: true
not sure why tho

verbal barn Jun 7, 2023, 2:06 PM

#

The logging

#

query: START TRANSACTION
query: SELECT DATABASE() AS db_name
query:
SELECT TABLE_SCHEMA,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'distributionUniapp'
AND TABLE_NAME = 'product_entity'

query:
SELECT
*
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = 'distributionuniapp'
AND
TABLE_NAME = 'product_entity'

query: SELECT * FROM (
SELECT
*
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE
kcu.TABLE_SCHEMA = 'distributionuniapp'
AND
kcu.TABLE_NAME = 'product_entity'
) kcu WHERE CONSTRAINT_NAME = 'PRIMARY'
query:
SELECT
SCHEMA_NAME,
DEFAULT_CHARACTER_SET_NAME as CHARSET,
DEFAULT_COLLATION_NAME AS COLLATION
FROM INFORMATION_SCHEMA.SCHEMATA

query:
SELECT
s.*
FROM (
SELECT
*
FROM INFORMATION_SCHEMA.STATISTICS
WHERE
TABLE_SCHEMA = 'distributionuniapp'
AND
TABLE_NAME = 'product_entity'
) s
LEFT JOIN (
SELECT
*
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE
CONSTRAINT_SCHEMA = 'distributionuniapp'
AND
TABLE_NAME = 'product_entity'
) rc
ON
s.INDEX_NAME = rc.CONSTRAINT_NAME
AND

#

s.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA
WHERE
s.INDEX_NAME != 'PRIMARY'
AND
rc.CONSTRAINT_NAME IS NULL

query:
SELECT
kcu.TABLE_SCHEMA,
kcu.TABLE_NAME,
kcu.CONSTRAINT_NAME,
kcu.COLUMN_NAME,
kcu.REFERENCED_TABLE_SCHEMA,
kcu.REFERENCED_TABLE_NAME,
kcu.REFERENCED_COLUMN_NAME,
rc.DELETE_RULE ON_DELETE,
rc.UPDATE_RULE ON_UPDATE
FROM (
SELECT
*
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE
kcu.TABLE_SCHEMA = 'distributionuniapp'
AND
kcu.TABLE_NAME = 'product_entity'
) kcu
INNER JOIN (
SELECT
*
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE
CONSTRAINT_SCHEMA = 'distributionuniapp'
AND
TABLE_NAME = 'product_entity'
) rc
ON
rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND
rc.TABLE_NAME = kcu.TABLE_NAME
AND
rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME

query: SELECT VERSION() AS version
query: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'distributionUniapp' AND TABLE_NAME = 'typeorm_metadata'
query: CREATE TABLE product_entity (id int NOT NULL AUTO_INCREMENT, createTime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id)) ENGINE=Inn
oDB
query: ROLLBACK

#

@rustic forge

#

I’m newer for nestjs, could you help me?

rustic forge Jun 7, 2023, 2:23 PM

#

that's not related to nestjs but typeorm configuration only

#

I have no clue on what's happening as I prefer not using synchronize: true

#

try searching for that error on google, idk :/

verbal barn Jun 7, 2023, 2:25 PM

#

If not using synchronize: true, means I need to create the table myself?

rustic forge Jun 7, 2023, 2:31 PM

#

yes, or you can leverage on database migrations

#

for production environment, having synchronize as true is pretty bad
you are under the risk of loosing data

#Table already exists