MYSQL: SequelizeForeignKeyError can't fill the fields array if the foreign key constraint error message returned by mysql exceeds 260 characters.

[clayton-at-switch]

I have a mysql table with a foreign key, and the identifier for that foreign key is quite long. This pushes the error message returned by mysql to 260 characters, at which point it begins to truncate the message. This may be a mysql setting or may be default behaviour, either way it is stupid but it exists and Sequelize doesn't handle it. The messages look like this:

NOT TOO LONG:

"Cannot add or update a child row: a foreign key constraint fails (swch.merchant_site_to_merchant_site_tag_links, CONSTRAINT iamputtingjusttheboringeststuff8lettersandrandomshitt FOREIGN KEY (merchant_site_tag_id) REFERENCES merchant_site_tags (id)"

TOO LONG (note the extra yy on the constraint identifier, also notice that somehow their dumb string builder still ends with a ) but truncates the inside of the column identifier on the target table.....):

"Cannot add or update a child row: a foreign key constraint fails (swch.merchant_site_to_merchant_site_tag_links, CONSTRAINT iamputtingjusttheboringeststuff8lettersandrandomshittyy FOREIGN KEY (merchant_site_tag_id) REFERENCES merchant_site_tags (`i)"

The erroneous code where you handle the error from mysql is on line 236 of lib/dialects/mysql/query.js:

const match = err.message.match(/CONSTRAINT (["])(.)\1 FOREIGN KEY (\1(.)\1) REFERENCES \1(.)\1 (\1(.)\1)/);`

If the message is truncated, match comes back null, and thus fields is unable to be filled out. It looks to me like the developer here fell into the old regex trap. It's too specific and doesn't handle error cases. I highly recommend building an object here programmatically and working with what you can from the message rather than just silently failing a regex match.

You know what they say:

When you use to regex to solve a problem, now you've got a problem.

[sushantdhiman]

This is the only information available to us

{ Error: Cannot add or update a child row: a foreign key constraint fails (`sequelize_test`.`tasks`, CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`primaryUserId`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE)
    at Packet.asError (/var/www/github/sequelize/node_modules/mysql2/lib/packets/packet.js:714:13)
    at Execute.Command.execute (/var/www/github/sequelize/node_modules/mysql2/lib/commands/command.js:28:22)
    at Connection.handlePacket (/var/www/github/sequelize/node_modules/mysql2/lib/connection.js:513:28)
    at PacketParser.onPacket (/var/www/github/sequelize/node_modules/mysql2/lib/connection.js:81:16)
    at PacketParser.executeStart (/var/www/github/sequelize/node_modules/mysql2/lib/packet_parser.js:76:14)
    at Socket.<anonymous> (/var/www/github/sequelize/node_modules/mysql2/lib/connection.js:89:29)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    at Socket.Readable.push (_stream_readable.js:208:10)
    at TCP.onread (net.js:597:20)
  code: 'ER_NO_REFERENCED_ROW_2',
  errno: 1452,
  sqlState: '23000',
  sqlMessage: 'Cannot add or update a child row: a foreign key constraint fails (`sequelize_test`.`tasks`, CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`primaryUserId`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE)',
  sql: 'INSERT INTO `tasks` (`id`,`title`,`createdAt`,`updatedAt`,`primaryUserId`) VALUES (DEFAULT,?,?,?,?);' }

Only way to get information about error is in message so we have to use regex. But we can certainly handle too long case or non matching case in general.

Can you submit SSCCE so this can be resolved or send PR, looks like a good first issue

[Tolsee]

I am working on it..

[Tolsee]

@sushantdhiman Please let me know your thoughts on the PR.

[Jessicalau93]

#9838

[sushantdhiman]

I believe this error is related to fields not truncation of error message. From my SSCCE you can see even with constraint name larger than OP, there was no truncation.

(async () => {
  class Project extends Sequelize.Model {}
  Project.init({}, { sequelize });

  class Task extends Sequelize.Model {}
  Task.init(
    {
      ProjectId: {
        type: Sequelize.INTEGER,
        field: "merchant_site_to_merchant_site_tag_links",
      },
    },
    { sequelize }
  );

  Task.belongsTo(Project);
  Project.hasOne(Task);

  await sequelize.sync({ force: true });
  await sequelize.queryInterface.addConstraint(Task.getTableName(), {
    name: "khetkabirsunobhaisadhokargujrangaribimeinmanlagoyaarfakirimein",
    type: "FOREIGN KEY",
    fields: ["merchant_site_to_merchant_site_tag_links"],
    references: {
      table: Project.getTableName(),
      field: "id",
    },
  });
  await sequelize.queryInterface.removeConstraint(
    Task.getTableName(),
    "Tasks_ibfk_1"
  );

  const task = await Task.create({
    ProjectId: 5,
  });
})();

ForeignKeyConstraintError [SequelizeForeignKeyConstraintError]: Cannot add or update a child row: a foreign key constraint fails (`sequelize_test`.`Tasks`, CONSTRAINT `khetkabirsunobhaisadhokargujrangaribimeinmanlagoyaarfakirimein` FOREIGN KEY (`merchant_site_to_merchant_site_tag_links`) REFERENCES `Projects` (`id`))
    at Query.formatError (/var/projects/oss/sequelize/lib/dialects/mysql/query.js:228:16)
    at Execute.handler [as onResult] (/var/projects/oss/sequelize/lib/dialects/mysql/query.js:50:23)
    at Execute.execute (/var/projects/oss/sequelize/node_modules/mysql2/lib/commands/command.js:30:14)
    at Connection.handlePacket (/var/projects/oss/sequelize/node_modules/mysql2/lib/connection.js:408:32)
    at PacketParser.onPacket (/var/projects/oss/sequelize/node_modules/mysql2/lib/connection.js:70:12)
    at PacketParser.executeStart (/var/projects/oss/sequelize/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/var/projects/oss/sequelize/node_modules/mysql2/lib/connection.js:77:25)
    at Socket.emit (events.js:311:20)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11) {
  name: 'SequelizeForeignKeyConstraintError',
  parent: Error: Cannot add or update a child row: a foreign key constraint fails (`sequelize_test`.`Tasks`, CONSTRAINT `khetkabirsunobhaisadhokargujrangaribimeinmanlagoyaarfakirimein` FOREIGN KEY (`merchant_site_to_merchant_site_tag_links`) REFERENCES `Projects` (`id`))
      at Packet.asError (/var/projects/oss/sequelize/node_modules/mysql2/lib/packets/packet.js:708:17)
      at Execute.execute (/var/projects/oss/sequelize/node_modules/mysql2/lib/commands/command.js:28:26)
      at Connection.handlePacket (/var/projects/oss/sequelize/node_modules/mysql2/lib/connection.js:408:32)
      at PacketParser.onPacket (/var/projects/oss/sequelize/node_modules/mysql2/lib/connection.js:70:12)
      at PacketParser.executeStart (/var/projects/oss/sequelize/node_modules/mysql2/lib/packet_parser.js:75:16)
      at Socket.<anonymous> (/var/projects/oss/sequelize/node_modules/mysql2/lib/connection.js:77:25)
      at Socket.emit (events.js:311:20)
      at addChunk (_stream_readable.js:294:12)
      at readableAddChunk (_stream_readable.js:275:11)
      at Socket.Readable.push (_stream_readable.js:209:10) {
    code: 'ER_NO_REFERENCED_ROW_2',
    errno: 1452,
    sqlState: '23000',
    sqlMessage: 'Cannot add or update a child row: a foreign key constraint fails (`sequelize_test`.`Tasks`, CONSTRAINT `khetkabirsunobhaisadhokargujrangaribimeinmanlagoyaarfakirimein` FOREIGN KEY (`merchant_site_to_merchant_site_tag_links`) REFERENCES `Projects` (`id`))',
    sql: 'INSERT INTO `Tasks` (`id`,`merchant_site_to_merchant_site_tag_links`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?);',
    parameters: [ 5, '2020-05-16 08:26:43', '2020-05-16 08:26:43' ]
  },
  original: Error: Cannot add or update a child row: a foreign key constraint fails (`sequelize_test`.`Tasks`, CONSTRAINT `khetkabirsunobhaisadhokargujrangaribimeinmanlagoyaarfakirimein` FOREIGN KEY (`merchant_site_to_merchant_site_tag_links`) REFERENCES `Projects` (`id`))
      at Packet.asError (/var/projects/oss/sequelize/node_modules/mysql2/lib/packets/packet.js:708:17)
      at Execute.execute (/var/projects/oss/sequelize/node_modules/mysql2/lib/commands/command.js:28:26)
      at Connection.handlePacket (/var/projects/oss/sequelize/node_modules/mysql2/lib/connection.js:408:32)
      at PacketParser.onPacket (/var/projects/oss/sequelize/node_modules/mysql2/lib/connection.js:70:12)
      at PacketParser.executeStart (/var/projects/oss/sequelize/node_modules/mysql2/lib/packet_parser.js:75:16)
      at Socket.<anonymous> (/var/projects/oss/sequelize/node_modules/mysql2/lib/connection.js:77:25)
      at Socket.emit (events.js:311:20)
      at addChunk (_stream_readable.js:294:12)
      at readableAddChunk (_stream_readable.js:275:11)
      at Socket.Readable.push (_stream_readable.js:209:10) {
    code: 'ER_NO_REFERENCED_ROW_2',
    errno: 1452,
    sqlState: '23000',
    sqlMessage: 'Cannot add or update a child row: a foreign key constraint fails (`sequelize_test`.`Tasks`, CONSTRAINT `khetkabirsunobhaisadhokargujrangaribimeinmanlagoyaarfakirimein` FOREIGN KEY (`merchant_site_to_merchant_site_tag_links`) REFERENCES `Projects` (`id`))',
    sql: 'INSERT INTO `Tasks` (`id`,`merchant_site_to_merchant_site_tag_links`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?);',
    parameters: [ 5, '2020-05-16 08:26:43', '2020-05-16 08:26:43' ]
  },
  sql: 'INSERT INTO `Tasks` (`id`,`merchant_site_to_merchant_site_tag_links`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?);',
  parameters: [ 5, '2020-05-16 08:26:43', '2020-05-16 08:26:43' ],
  fields: [ 'merchant_site_to_merchant_site_tag_links' ],
  table: 'Projects',
  value: undefined,
  index: 'khetkabirsunobhaisadhokargujrangaribimeinmanlagoyaarfakirimein',
  reltype: 'child'
}