Node v12 breaks addForeignKey

[lukemuller]

I'm submitting a...

• Bug report
• Feature request
• Question

Current behavior

On node v12 when trying to add a foreign key the util.format() function no longer formats the SQL correctly and an error occurs.

ALTER TABLE `users` ADD CONSTRAINT `users_userID_fk` FOREIGN KEY ([ '`referredBy`' ]) REFERENCES `users` ([ '`userID`' ]) ON DELETE SET NULL ON UPDATE CASCADE
[ERROR] AssertionError [ERR_ASSERTION]: ifError got unwanted exception: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[ '`referredBy`' ]) REFERENCES `users` ([ '`userID`' ]) ON DELETE SET NULL ON UP' at line 1

Expected behavior

On node version's before v12 the util.format() function that is used in addForeignKey works successfully and format the SQL correctly.

Minimal reproduction of the problem with instructions

Install node v12 and try creating a table with a foreign key or adding a foreign key to an exisiting table.

Environment

db-migrate version: 0.11.6
plugins with versions: db-migrate-mysql 1.1.10
db-migrate driver with versions: mysql2 1.6.5

Additional information:
- Node version: 12
- Platform:  Linux

Others:

---

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[wzrdtales]

Might be a similar issue as on one of the other drivers. I will check this. Thank you for reporting!

[lukemuller]

I think I’ve found the issue. I checked the v12 changelogs to the util. And it appears this change was implemented: nodejs/node#23162

It looks like the formatter now checks if the value is a function and uses util.inspect rather than returning a string before. So the ddlescape function in the formatter is now causing an issue.

[grappetite-ali]

I am facing same issue.

[coffeestains1908]

This is also happening on Windows 10

db-migrate: 0.11.6
db-migrate-mysql: 1.1.10
nodejs: v12.13.0

// add foreign key
exports.up = function(db, cb) {
  db.addForeignKey('adverts', 'users', 'fk_adverts_user_id_1',
  {
    'user_id': 'id'
  },
  {
    onDelete: 'CASCADE',
    onUpdate: 'RESTRICT'
  }, cb)
};

db-migrate up Error:

[ERROR] AssertionError [ERR_ASSERTION]: ifError got unwanted exception: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[ '`user_id`' ]) REFERENCES `users` ([ '`id`' ]) ON DELETE CASCADE ON UPDATE RES' at line 1

db-migrate up --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;;
ALTER TABLE `adverts` ADD CONSTRAINT `fk_adverts_user_id_1` FOREIGN KEY ([ '`user_id`' ]) REFERENCES `users` ([ '`id`' ]) ON DELETE CASCADE ON UPDATE RESTRICT;
INSERT INTO `migrations` (`name`, `run_on`) VALUES (?, ?) [ [ '/20191102071140-alter-adverts-fk', '2019-11-02 18:42:17' ] ]
[INFO] Processed migration 20191102071140-alter-adverts-fk
COMMIT;;
[INFO] Done

[khanisak]

I'm facing this issue too. Any solution guys? thank you.

[fredleroy]

Same issue here, will use plain sql in the meantime

[wzrdtales]

there is a new driver version of mysql 2.0.0. it is major release since all drivers signal incompatibility to versions older than node 8.

[rob-johansen]

Will this be fixed for postgresql too?

[fredleroy]

Hi For mysql it is. Bumpimg db-migrate mysql plugin (or driver dunno what it’s called) version fixed it for me. No idea about postgres. Regards Le jeu. 30 avr. 2020 à 13:10, StickMan077 <notifications@github.com> a écrit :

Is this Issue resolved? — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#635 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AANLKXKIGYBW2LO7P7HQIHLRPFMEDANCNFSM4IDEAKGQ> .

-- Frédéric Leroy +33 6 85 84 49 07

[wzrdtales]

As @fredleroy said. It is fixed, but if you @stenrap doesn't provide us with any details, we wont be able to help. Please post your environment.