Migrations failing [db2]

[Hermaniandr]

What you are doing?

yarn sequelize-cli db:migrate

What do you expect to happen?

Successful command run

What is actually happening?

Command fails, as Sequelize CLI tries to create table "SequelizeMeta" twice, second creation fails as table with such name already exists.

Sequelize CLI [Node: 17.6.0, CLI: 6.4.1, ORM: 6.17.0]

Loaded configuration file "config/config.json".
Using environment "development".
Executing (default): SELECT 1+1 AS result
Executing (default): SELECT TABNAME AS "tableName", TRIM(TABSCHEMA) AS "tableSchema" FROM SYSCAT.TABLES WHERE TABSCHEMA = USER AND TYPE = 'T' ORDER BY TABSCHEMA, TABNAME
Executing (default): CREATE TABLE "SequelizeMeta" ("name" VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY ("name"));
Executing (default): SELECT NAME AS "name", TBNAME AS "tableName", UNIQUERULE AS "keyType", COLNAMES, INDEXTYPE AS "type" FROM SYSIBM.SYSINDEXES WHERE TBNAME = 'SequelizeMeta' ORDER BY NAME;
Executing (default): SELECT "name" FROM "SequelizeMeta" AS "SequelizeMeta" ORDER BY "SequelizeMeta"."name" ASC;
Executing (default): CREATE TABLE "SequelizeMeta" ("name" VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY ("name"));

ERROR: [IBM][CLI Driver][DB2/LINUXX8664] SQL0541W  The referential, primary key, or unique constraint "name..." is ignored because it is a duplicate constraint.  SQLSTATE=01543


error Command failed with exit code 1.

Dialect: db2
Database version: 11.5.7.0
Sequelize CLI version: 6.4.1
Sequelize version: 6.17.0

[WikiRik]

Hi! We don't officially support DB2 for the CLI yet, but we will be running our tests on it soon and see what changes we have to make.
Just to make sure, can you run the migration on sqlite or another dialect and confirm that that works?

[Hermaniandr]

Hey! Thanks for the quick answer!

Tested with mysql, everything works perfect:

Sequelize CLI [Node: 17.6.0, CLI: 6.4.1, ORM: 6.17.0]

Loaded configuration file "config/config.json".
Using environment "development".
Executing (default): SELECT 1+1 AS result
Executing (default): SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'big_data';
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;
Executing (default): SHOW INDEX FROM `SequelizeMeta`
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;
Executing (default): SHOW INDEX FROM `SequelizeMeta`
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;
Executing (default): SHOW INDEX FROM `SequelizeMeta`
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;
== 20220301095308-create-user-data: migrating =======
Executing (default): CREATE TABLE IF NOT EXISTS `UserData` (`id` INTEGER NOT NULL auto_increment , `user_id` INTEGER, `phrase` VARCHAR(255), `emotions` VARCHAR(255), PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;
Executing (default): SHOW INDEX FROM `SequelizeMeta`
Executing (default): INSERT INTO `SequelizeMeta` (`name`) VALUES (?);
== 20220301095308-create-user-data: migrated (0.121s)

Done in 0.61s.

I did a quick debug, and seems that issue takes place somewhere here:

cli/src/core/migrator.js

Line 91 in d69a27f

if (tableNames.indexOf(tableName) === -1) {

• In case with mysql tableNames has the the following value: [ 'SequelizeMeta'].
• In case with db2 tableNames has the following value: [ { tableName: 'SequelizeMeta', tableSchema: 'DB2INST1' } ]

[Hermaniandr]

Hey @WikiRik!

Do you have any updates on this one?

[WikiRik]

Not yet, I'll hope to be able to take a look at this in the weekend

[WikiRik]

I've been taking a small look at this in #1049 but I'm having some difficulties with getting the test suite to recognize the DB2 server. Probably just need to take a fresh look at this later

About the actual issue related to this, feel free to make a PR that solves this issue.