syncSchema attempts to sync schema multiple times about typeorm

[pleerock]

hi @jon-walton
sorry for late response, was very busy last week.

I need at least a code snippet to understand how do you use connections.
For now what I get is that you are trying to create multiple connections with the same name.
If so, then you should not.

[jon-walton]

Hi @pleerock

Sorry, yep, I should have provided some repro code.

import 'reflect-metadata';

import { Connection, createConnection, ConnectionOptions, getConnectionManager } from 'typeorm';
import { Table } from 'typeorm/tables';
import { PrimaryColumn, Column } from 'typeorm/columns';

@Table('testTable')
export class TestTable {

  @PrimaryColumn('int', { generated: true })
  public id: number;

  @Column()
  public name: string;
}

const mysqlOptions: ConnectionOptions = {
  connectionName: `mysql`,
  driver: {
    type: 'mysql',
    host: 'localhost',
    port: 3306,
    username: 'libCore',
    password: 'libCore',
    database: 'libCoreTest',
    logging: {
      logger: (message: string, level: string) => console.log(level, message),
      logQueries: true,
      logFailedQueryError: true,
    },
  },
  autoSchemaCreate: false,
  dropSchemaOnConnection: false,
  entities: [TestTable],
};

async function test(): Promise<Connection> {
  let connection: Connection;
  try {
    connection = getConnectionManager().get(mysqlOptions.connectionName);
    console.log('got existing connection');
  } catch (err) {
    console.log('creating connection');
    connection = await createConnection(mysqlOptions);
  }

  if (!connection.isConnected) {
    console.log('connecting...');
    await connection.connect();
  }

  console.log('sync - dropBeforeSync = true');
  await connection.syncSchema(true);

  console.log('finished test suit, cleanup');

  console.log('dropping database');
  await connection.dropDatabase();

  console.log('disconnecting');
  await connection.close();

  return connection;
}

test()
  .then(() => console.log('finished test 1\n\n'))

  .then(() => test())
  .then(() => console.log('finished test 2\n\n'));

outputs the following

creating connection
sync - dropBeforeSync = true
log executing query: SET FOREIGN_KEY_CHECKS = 0;
log executing query: SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') AS q FROM information_schema.tables WHERE table_schema = 'libCoreTest';
log executing query: DROP TABLE IF EXISTS testTable;
log executing query: SET FOREIGN_KEY_CHECKS = 1;
log executing query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND REFERENCED_COLUMN_NAME IS NOT NULL
log executing query: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: CREATE TABLE `testTable` (id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(255) NOT NULL) ENGINE=InnoDB;
log executing query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND REFERENCED_COLUMN_NAME IS NOT NULL
log executing query: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND CONSTRAINT_TYPE = 'UNIQUE'
log executing query: SHOW INDEX FROM testTable
log executing query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND REFERENCED_COLUMN_NAME IS NOT NULL
finished test suit, cleanup
dropping database
log executing query: SET FOREIGN_KEY_CHECKS = 0;
log executing query: SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') AS q FROM information_schema.tables WHERE table_schema = 'libCoreTest';
log executing query: DROP TABLE IF EXISTS testTable;
log executing query: SET FOREIGN_KEY_CHECKS = 1;
disconnecting
finished test 1


got existing connection
connecting...
sync - dropBeforeSync = true
log executing query: SET FOREIGN_KEY_CHECKS = 0;
log executing query: SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') AS q FROM information_schema.tables WHERE table_schema = 'libCoreTest';
log executing query: SET FOREIGN_KEY_CHECKS = 1;
log executing query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND REFERENCED_COLUMN_NAME IS NOT NULL
log executing query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND REFERENCED_COLUMN_NAME IS NOT NULL
log executing query: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: CREATE TABLE `testTable` (id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(255) NOT NULL) ENGINE=InnoDB;
log executing query: CREATE TABLE `testTable` (id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(255) NOT NULL) ENGINE=InnoDB;
error query failed: CREATE TABLE `testTable` (id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(255) NOT NULL) ENGINE=InnoDB;
error error during executing query:
error { Error: ER_TABLE_EXISTS_ERROR: Table 'testTable' already exists
    at Query.Sequence._packetToError (C:\Users\jonwa\Documents\gitlab\core\node_modules\mysql\lib\protocol\sequences\Sequence.js:51:14)
    at Query.ErrorPacket (C:\Users\jonwa\Documents\gitlab\core\node_modules\mysql\lib\protocol\sequences\Query.js:83:18)
    at Protocol._parsePacket (C:\Users\jonwa\Documents\gitlab\core\node_modules\mysql\lib\protocol\Protocol.js:280:23)
    at Parser.write (C:\Users\jonwa\Documents\gitlab\core\node_modules\mysql\lib\protocol\Parser.js:74:12)
    at Protocol.write (C:\Users\jonwa\Documents\gitlab\core\node_modules\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (C:\Users\jonwa\Documents\gitlab\core\node_modules\mysql\lib\Connection.js:109:28)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:172:18)
    at Socket.Readable.push (_stream_readable.js:130:10)
    --------------------
    at Protocol._enqueue (C:\Users\jonwa\Documents\gitlab\core\node_modules\mysql\lib\protocol\Protocol.js:141:48)
    at Connection.query (C:\Users\jonwa\Documents\gitlab\core\node_modules\mysql\lib\Connection.js:214:25)
    at Promise (C:\Users\jonwa\Documents\gitlab\core\node_modules\typeorm\driver\driver\MysqlDriver.ts:143:66)
    at MysqlDriver.query (C:\Users\jonwa\Documents\gitlab\core\node_modules\typeorm\driver\driver\MysqlDriver.ts:143:16)
    at MysqlSchemaBuilder.query (C:\Users\jonwa\Documents\gitlab\core\node_modules\typeorm\schema-builder\schema-builder\MysqlSchemaBuilder.ts:153:28)
    at MysqlSchemaBuilder.createTableQuery (C:\Users\jonwa\Documents\gitlab\core\node_modules\typeorm\schema-builder\schema-builder\MysqlSchemaBuilder.ts:145:21)
    at schemaBuilder.checkIfTableExist.then.exist (C:\Users\jonwa\Documents\gitlab\core\node_modules\typeorm\schema-creator\schema-creator\SchemaCreator.ts:114:43)
    at process._tickDomainCallback (internal/process/next_tick.js:129:7)
  code: 'ER_TABLE_EXISTS_ERROR',
  errno: 1050,
  sqlState: '42S01',
  index: 0 }

If the connection already exists, it is reused. When calling syncSchema on an existing connection, it attempts to create the schema multiple times

log executing query: CREATE TABLE `testTable` (id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(255) NOT NULL) ENGINE=InnoDB;
log executing query: CREATE TABLE `testTable` (id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(255) NOT NULL) ENGINE=InnoDB;
error query failed: CREATE TABLE `testTable` (id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(255) NOT NULL) ENGINE=InnoDB;

I'm doing this because my test suit re-uses existing connections but drops the schema after each test run

[jon-walton]

A quick update, I just noticed that this works as expected (re-use existing connection, call createSchema()) if the connection is not closed.

creating connection
sync - dropBeforeSync = true
log executing query: SET FOREIGN_KEY_CHECKS = 0;
log executing query: SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') AS q FROM information_schema.tables WHERE table_schema = 'libCoreTest';
log executing query: DROP TABLE IF EXISTS testTable;
log executing query: SET FOREIGN_KEY_CHECKS = 1;
log executing query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND REFERENCED_COLUMN_NAME IS NOT NULL
log executing query: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: CREATE TABLE `testTable` (id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(255) NOT NULL) ENGINE=InnoDB;
log executing query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND REFERENCED_COLUMN_NAME IS NOT NULL
log executing query: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND CONSTRAINT_TYPE = 'UNIQUE'
log executing query: SHOW INDEX FROM testTable
log executing query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND REFERENCED_COLUMN_NAME IS NOT NULL
finished test suit, cleanup
dropping database
log executing query: SET FOREIGN_KEY_CHECKS = 0;
log executing query: SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') AS q FROM information_schema.tables WHERE table_schema = 'libCoreTest';
log executing query: DROP TABLE IF EXISTS testTable;
log executing query: SET FOREIGN_KEY_CHECKS = 1;
finished test 1


got existing connection
sync - dropBeforeSync = true
log executing query: SET FOREIGN_KEY_CHECKS = 0;
log executing query: SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') AS q FROM information_schema.tables WHERE table_schema = 'libCoreTest';
log executing query: SET FOREIGN_KEY_CHECKS = 1;
log executing query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND REFERENCED_COLUMN_NAME IS NOT NULL
log executing query: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: CREATE TABLE `testTable` (id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(255) NOT NULL) ENGINE=InnoDB;
log executing query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'libCoreTest' AND TABLE_NAME = 'testTable'
log executing query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND REFERENCED_COLUMN_NAME IS NOT NULL
log executing query: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND CONSTRAINT_TYPE = 'UNIQUE'
log executing query: SHOW INDEX FROM testTable
log executing query: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "libCoreTest" AND TABLE_NAME = "testTable" AND REFERENCED_COLUMN_NAME IS NOT NULL
finished test suit, cleanup
dropping database
log executing query: SET FOREIGN_KEY_CHECKS = 0;
log executing query: SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') AS q FROM information_schema.tables WHERE table_schema = 'libCoreTest';
log executing query: DROP TABLE IF EXISTS testTable;
log executing query: SET FOREIGN_KEY_CHECKS = 1;
finished test 2

[pleerock]

👍

[pleerock]

i guess it can be closed

[jon-walton]

this may be fixed in 0.0.2-alpha.62

[pleerock]

@jon-walton please confirm if its working, if not reopen this issue

[Freysi21]

Think i found my answer here.
This can be closed.
#1861