cli Migrations failing [db2]

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

Mar 02 '22 12:03 Hermaniandr

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?

Mar 02 '22 14:03 WikiRik

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: https://github.com/sequelize/cli/blob/d69a27ff04f5f1246af1053d8395e06d925b66cc/src/core/migrator.js#L91

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

Mar 02 '22 15:03 Hermaniandr

Hey @WikiRik!

Do you have any updates on this one?

Mar 10 '22 10:03 Hermaniandr

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

Mar 10 '22 10:03 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.

Mar 15 '22 14:03 WikiRik