1

I am using node-db-migrate in my project to handle the database migrations. It works fine when I am using only one statement but if multiple statements are used, sometimes it runs while it fails most of the time.

Here is my database.json

{
    "dev": {
    "driver": "mysql",
    "host":"localhost",
    "user": "root",
    "database": "PMS",
    "password": "jay123!",
    "multipleStatements": true
  },

  "test": {
    "driver": "mysql",
    "user": "jay123!",
    "host":"localhost",
    "database": "PMS_test",
    "password": "jay123!",
    "multipleStatements": true
  },

  "prod": {
    "driver": "mysql",
    "user": "jay123!",
    "host":"localhost",
    "database": "PMS",
    "password": "jay123!",
    "multipleStatements": true
  },
  "sql-file" : true
}

My up file

CREATE TABLE `user_types` (
  `id` int(14) NOT NULL AUTO_INCREMENT,
  `name` char(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `users` (
  `id` int(14) NOT NULL AUTO_INCREMENT,
  `name` char(255) NOT NULL,
  `email` char(255) NOT NULL,
  `phone` int(10) NOT NULL,
  `password` varchar(255) NOT NULL,
  `user_type_id` int(3) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My down file

DROP TABLE users;
DROP TABLE user_types;

The down file works fine. but whenever use up file I get the error as below:

Error: ER_TABLE_EXISTS_ERROR: Table 'user_types' already exists
at Query.Sequence._packetToError (/var/www/html/PMS/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
at Query.ErrorPacket (/var/www/html/PMS/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
at Protocol._parsePacket (/var/www/html/PMS/node_modules/mysql/lib/protocol/Protocol.js:279:23)
at Parser.write (/var/www/html/PMS/node_modules/mysql/lib/protocol/Parser.js:76:12)
at Protocol.write (/var/www/html/PMS/node_modules/mysql/lib/protocol/Protocol.js:39:16)
at Socket.<anonymous> (/var/www/html/PMS/node_modules/mysql/lib/Connection.js:103:28)
at emitOne (events.js:115:13)
at Socket.emit (events.js:210:7)
at addChunk (_stream_readable.js:266:12)
at readableAddChunk (_stream_readable.js:253:11)
at Socket.Readable.push (_stream_readable.js:211:10)
at TCP.onread (net.js:587:20)
--------------------
at Protocol._enqueue (/var/www/html/PMS/node_modules/mysql/lib/protocol/Protocol.js:145:48)
at Connection.query (/var/www/html/PMS/node_modules/mysql/lib/Connection.js:208:25)
at /var/www/html/PMS/node_modules/db-migrate-mysql/index.js:371:29
at Promise._execute (/var/www/html/PMS/node_modules/bluebird/js/release/debuggability.js:180:9)
at Promise._resolveFromExecutor (/var/www/html/PMS/node_modules/bluebird/js/release/promise.js:462:18)
at new Promise (/var/www/html/PMS/node_modules/bluebird/js/release/promise.js:77:14)
at Object.runSql (/var/www/html/PMS/node_modules/db-migrate-mysql/index.js:366:12)
at /var/www/html/PMS/migrations/20171013202756-users.js:32:15
at tryCatcher (/usr/local/lib/node_modules/db-migrate/node_modules/bluebird/js/release/util.js:16:23)
at Promise._settlePromiseFromHandler (/usr/local/lib/node_modules/db-migrate/node_modules/bluebird/js/release/promise.js:512:31)
at Promise._settlePromise (/usr/local/lib/node_modules/db-migrate/node_modules/bluebird/js/release/promise.js:569:18)
at Promise._settlePromise0 (/usr/local/lib/node_modules/db-migrate/node_modules/bluebird/js/release/promise.js:614:10)
at Promise._settlePromises (/usr/local/lib/node_modules/db-migrate/node_modules/bluebird/js/release/promise.js:693:18)
at Async._drainQueue (/usr/local/lib/node_modules/db-migrate/node_modules/bluebird/js/release/async.js:133:16)
at Async._drainQueues (/usr/local/lib/node_modules/db-migrate/node_modules/bluebird/js/release/async.js:143:10)
at Immediate.Async.drainQueues [as _onImmediate] (/usr/local/lib/node_modules/db-migrate/node_modules/bluebird/js/release/async.js:17:14)
at runCallback (timers.js:781:20)
at tryOnImmediate (timers.js:743:5)
at processImmediate [as _immediateCallback] (timers.js:714:5)

To debug the same I called the same with --dry-run flag then I observed that the create queries are getting generated twice which might be the cause of error. Please help me how to sort it.

Dry run output

[INFO] dry run
CREATE TABLE IF NOT EXISTS `migrations` (`id` INTEGER  PRIMARY KEY AUTO_INCREMENT NOT NULL, `name` VARCHAR (255) NOT NULL, `run_on` DATETIME  NOT NULL) ;
SELECT * FROM `migrations` ORDER BY run_on DESC, name DESC;
SET AUTOCOMMIT=0;;
START TRANSACTION;;
received data: CREATE TABLE `user_types` (
  `id` int(14) NOT NULL AUTO_INCREMENT,
  `name` char(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `users` (
  `id` int(14) NOT NULL AUTO_INCREMENT,
  `name` char(255) NOT NULL,
  `email` char(255) NOT NULL,
  `phone` int(10) NOT NULL,
  `password` varchar(255) NOT NULL,
  `user_type_id` int(3) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_types` (
  `id` int(14) NOT NULL AUTO_INCREMENT,
  `name` char(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `users` (
  `id` int(14) NOT NULL AUTO_INCREMENT,
  `name` char(255) NOT NULL,
  `email` char(255) NOT NULL,
  `phone` int(10) NOT NULL,
  `password` varchar(255) NOT NULL,
  `user_type_id` int(3) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
;
INSERT INTO `migrations` (`name`, `run_on`) VALUES (?, ?) [ [ '/20171013202756-users', '2017-10-14 20:50:48' ] ]
[INFO] Processed migration 20171013202756-users
COMMIT;;
SET AUTOCOMMIT=0;;
START TRANSACTION;;
received data: CREATE TABLE `user_types` (
  `id` int(14) NOT NULL AUTO_INCREMENT,
  `name` char(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_types` (
  `id` int(14) NOT NULL AUTO_INCREMENT,
  `name` char(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
;
INSERT INTO `migrations` (`name`, `run_on`) VALUES (?, ?) [ [ '/20171013205017-user-types', '2017-10-14 20:50:48' ] ]
[INFO] Processed migration 20171013205017-user-types
COMMIT;;
[INFO] Done

While searching a bit I landed here: https://stackoverflow.com/questions/3302476/mysql-1050-error-table-already-exists-when-in-fact-it-does-not and I thought that it might be a case of Schroedinger's table. I changed the database config and repeated the process but the result was same.

1

Abandon that tool! It has multiple errors.

START TRANSACTION is closed (COMMITTed) implicitly by any DDL (CREATE TABLE, in your case.

Syntax error at received data:.

This shows up twice, causing the error you asked about: CREATE TABLE user_types -- Is that your fault? Or the tool's?

Don't use CHAR(...) for variable length fields; use VARCHAR(...).

The (14) in INT(14) means nothing. An INT is always 4 bytes and has a range of up to a few billion.

0

The problem was not with node-db-migrate plugin. I had written two migrations the second migration was conflicting with the first one. Once I removed the second migration file, everything worked like charm

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.