[MySQLv5.7] Updating existing records with no changes breaks when DATEONLY field is '0000-00-00'

Issue Creation Checklist

• I understand that my issue will be automatically closed if I don't fill in the requested information
• I have read the contribution guidelines

Bug Description

The project described in this report is using MySQL 5.7,

I have a DATEONLY field in my model:

  const Contact = sequelize.define(
    'Contact',
    {
      id: {
        type: DataTypes.STRING,
        primaryKey: true,
      },
      dateOfBirth: {
        type: DataTypes.DATEONLY,
        allowNull: true,
      },
    },
  );

The project was recently upgraded from Sequelize v3 to Sequelize v6.27.0, and now update operations fail on records containing zero values for this date field. The update happens in a findOne.then block:

        const updateContact = await Contact.findOne({
          where: {
            id: inputContact.id,
          },
        }).then((contact) => {
          contact.dateOfBirth = inputContact.dateOfBirth || null;

          return contact.save();
        });

The problem I'm having is with records that have '0000-00-00' as their dateOfBirth and that field along with the rest of inputContact matches the existing record exactly (so the update should be empty, as no columns are actually being changed). An UPDATE is attempted, and fails because of some date parsing issue (see the expected/actual results sections below)

Reproducible Example

Example code given in this bug report report description

What do you expect to happen?

I expect, in terms of SQL, for nothing to happen. The DB row is unchanged, as the supplied values match the stored record in the database.

What is actually happening?

I can tell from logs that an UPDATE is being attempted, and is failing because of an invalid value in the dateOfBirth field. The value being supplied to the update function is the string '0000-00-00', but somehow this is being translated into the string 'Invalid date'. The error message and stack trace that is shown on the terminal:

Deprecation warning: value provided is not in a recognized RFC2822 or ISO format. moment construction falls back to js Date(), which is not reliable across all browsers and versions. Non RFC2822/ISO date formats are discouraged. Please refer to http://momentjs.com/guides/#/warnings/js-date/ for more info.
Arguments: 
[0] _isAMomentObject: true, _isUTC: false, _useUTC: false, _l: undefined, _i: Invalid date, _f: undefined, _strict: undefined, _locale: [object Object]
Error
    at Function.createFromInputFallback (/home/alata/code/mad/node_modules/moment/moment.js:324:25)
    at configFromString (/home/alata/code/mad/node_modules/moment/moment.js:2550:19)
    at configFromInput (/home/alata/code/mad/node_modules/moment/moment.js:2993:13)
    at prepareConfig (/home/alata/code/mad/node_modules/moment/moment.js:2976:13)
    at createFromConfig (/home/alata/code/mad/node_modules/moment/moment.js:2943:44)
    at createLocalOrUTC (/home/alata/code/mad/node_modules/moment/moment.js:3037:16)
    at createLocal (/home/alata/code/mad/node_modules/moment/moment.js:3041:16)
    at hooks (/home/alata/code/mad/node_modules/moment/moment.js:16:29)
    at DATEONLY._stringify (/home/alata/code/mad/node_modules/sequelize/lib/data-types.js:352:12)
    at DATEONLY.stringify (/home/alata/code/mad/node_modules/sequelize/lib/data-types.js:22:19)
    at DATEONLY.bindParam (/home/alata/code/mad/node_modules/sequelize/lib/data-types.js:30:35)
    at MySQLQueryGenerator.format (/home/alata/code/mad/node_modules/sequelize/lib/dialects/abstract/query-generator.js:746:29)
    at MySQLQueryGenerator.updateQuery (/home/alata/code/mad/node_modules/sequelize/lib/dialects/abstract/query-generator.js:330:58)
    at MySQLQueryInterface.update (/home/alata/code/mad/node_modules/sequelize/lib/dialects/abstract/query-interface.js:352:37)
    at model.save (/home/alata/code/mad/node_modules/sequelize/lib/model.js:2432:79)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
Executing (default): UPDATE `contact` SET `dateOfBirth`=?,`updatedAt`=? WHERE `id` = ?
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

Error
    at Query.run (/home/alata/code/mad/node_modules/sequelize/lib/dialects/mysql/query.js:52:25)
    at /home/alata/code/mad/node_modules/sequelize/lib/sequelize.js:314:28
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at MySQLQueryInterface.update (/home/alata/code/mad/node_modules/sequelize/lib/dialects/abstract/query-interface.js:355:12)
    at model.save (/home/alata/code/mad/node_modules/sequelize/lib/model.js:2432:35) {
  name: 'SequelizeDatabaseError',
  parent: Error: Incorrect date value: 'Invalid date' for column 'dateOfBirth' at row 1
      at Packet.asError (/home/alata/code/mad/node_modules/mysql2/lib/packets/packet.js:728:17)
      at Execute.execute (/home/alata/code/mad/node_modules/mysql2/lib/commands/command.js:29:26)
      at Connection.handlePacket (/home/alata/code/mad/node_modules/mysql2/lib/connection.js:456:32)
      at PacketParser.onPacket (/home/alata/code/mad/node_modules/mysql2/lib/connection.js:85:12)
      at PacketParser.executeStart (/home/alata/code/mad/node_modules/mysql2/lib/packet_parser.js:75:16)
      at Socket.<anonymous> (/home/alata/code/mad/node_modules/mysql2/lib/connection.js:92:25)
      at Socket.emit (node:events:513:28)
      at Socket.emit (node:domain:489:12)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Socket.Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    code: 'ER_TRUNCATED_WRONG_VALUE',
    errno: 1292,
    sqlState: '22007',
    sqlMessage: "Incorrect date value: 'Invalid date' for column 'dateOfBirth' at row 1",
    sql: 'UPDATE `contact` SET `dateOfBirth`=?,`updatedAt`=? WHERE `id` = ?',
    parameters: [
      'Invalid date',
      '2023-01-09 11:35:41',
      '7f35d63a24a7a6b9953610fb83f871a8'
    ]
  },
  original: Error: Incorrect date value: 'Invalid date' for column 'dateOfBirth' at row 1
      at Packet.asError (/home/alata/code/mad/node_modules/mysql2/lib/packets/packet.js:728:17)
      at Execute.execute (/home/alata/code/mad/node_modules/mysql2/lib/commands/command.js:29:26)
      at Connection.handlePacket (/home/alata/code/mad/node_modules/mysql2/lib/connection.js:456:32)
      at PacketParser.onPacket (/home/alata/code/mad/node_modules/mysql2/lib/connection.js:85:12)
      at PacketParser.executeStart (/home/alata/code/mad/node_modules/mysql2/lib/packet_parser.js:75:16)
      at Socket.<anonymous> (/home/alata/code/mad/node_modules/mysql2/lib/connection.js:92:25)
      at Socket.emit (node:events:513:28)
      at Socket.emit (node:domain:489:12)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Socket.Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    code: 'ER_TRUNCATED_WRONG_VALUE',
    errno: 1292,
    sqlState: '22007',
    sqlMessage: "Incorrect date value: 'Invalid date' for column 'dateOfBirth' at row 1",
    sql: 'UPDATE `contact` SET `dateOfBirth`=?,`updatedAt`=? WHERE `id` = ?',
    parameters: [
      'Invalid date',
      '2023-01-09 11:35:41',
      '7f35d63a24a7a6b9953610fb83f871a8'
    ]
  },
  sql: 'UPDATE `contact` SET `dateOfBirth`=?,`updatedAt`=? WHERE `id` = ?',
  parameters: [
    'Invalid date',
    '2023-01-09 11:35:41',
    '7f35d63a24a7a6b9953610fb83f871a8'
  ]
}

The contents of the contact object that is build after the findOne and content change, immediately before the contact.save() shown in the example are logged to the console before this failure (note that the object passed to update this record contains the values shown in _previousDataValues exactly, so the supplied dateOfBirth is '0000-00-00'):

Contact {
  dataValues: {
    id: '7f35d63a24a7a6b9953610fb83f871a8',
    dateOfBirth: 'Invalid date'
  },
  _previousDataValues: {
    id: '7f35d63a24a7a6b9953610fb83f871a8',
    dateOfBirth: '0000-00-00'
  },
  uniqno: 1,
  _changed: Set { 'dateOfBirth' },
  _options: {
    isNewRecord: false,
    _schema: null,
    _schemaDelimiter: '',
    raw: true,
    attributes: [
      'id',
      'dateOfBirth'
    ]
  },
  isNewRecord: false
}

Environment

• Sequelize version: 6.27.0
• Node.js version: 18.12.1
• If TypeScript related: TypeScript version: n/a
• Database & Version: MYSQL 5.7
• Connector library & Version: mysql2@2.3.3

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I will need guidance.
• No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.

---

Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.

Start with the DATEONLY._stringify path in sequelize/lib/data-types.js and the model.save stack shown in the report. Reproduce the findOne/save case against MySQL 5.7 with a stored '0000-00-00' value, then verify that saving unchanged data no longer sends 'Invalid date' or fails the update.