Model options.charset is overwritten by default options

[rohitsud]

sequelize/lib/model.js

Line 1271 in 8a1429d

options = { ...this.options, ...options };

If I create any model with
charset: 'utf8mb4', collate: 'utf8mb4_general_ci'

sequelize translates it to this for mysql

CREATE TABLE IF NOT EXISTS tablename ... ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8mb4_general_ci;

On stepping through the code, it seems that the issue is here where we don't use the model's charset but the default charset.

[sushantdhiman]

I can confirm this bug on master. I have noticed you are using 4.44.3, if this bug is fixed, it wont be released for v4/v3 branches as they are deprecated.

const Sequelize = require("./index");                                  
const sequelize = require("./test/support").createSequelizeInstance({                                  
  charset: "utf8",                                  
});                                  
                                  
const Model = Sequelize.Model;                                  
                                  
(async () => {                                  
  const User = sequelize.define(                                  
    "User",                                  
    {                                  
      name: Sequelize.STRING,                                  
    },                                  
    {                                  
      charset: "utf8mb4",                                  
      collate: "utf8mb4_general_ci",                                  
    }                                  
  );                                  
                                  
  await sequelize.sync({                                  
    force: true,                                  
  });                                  
})();

DatabaseError [SequelizeDatabaseError]: COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'
    at Query.formatError (/var/projects/oss/sequelize/lib/dialects/mysql/query.js:239:16)
    at Query.handler [as onResult] (/var/projects/oss/sequelize/lib/dialects/mysql/query.js:50:23)
    at Query.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: 'SequelizeDatabaseError',
  parent: Error: COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'
      at Packet.asError (/var/projects/oss/sequelize/node_modules/mysql2/lib/packets/packet.js:708:17)
      at Query.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_COLLATION_CHARSET_MISMATCH',
    errno: 1253,
    sqlState: '42000',
    sqlMessage: "COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'",
    sql: 'CREATE TABLE IF NOT EXISTS `Users` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8mb4_general_ci;',
    parameters: undefined
  },
  original: Error: COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'
      at Packet.asError (/var/projects/oss/sequelize/node_modules/mysql2/lib/packets/packet.js:708:17)
      at Query.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_COLLATION_CHARSET_MISMATCH',
    errno: 1253,
    sqlState: '42000',
    sqlMessage: "COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'",
    sql: 'CREATE TABLE IF NOT EXISTS `Users` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8mb4_general_ci;',
    parameters: undefined
  },
  sql: 'CREATE TABLE IF NOT EXISTS `Users` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8mb4_general_ci;',
  parameters: undefined
}

[mearns]

Is this issue still open? I see a lot of commits referencing it.

[LouiZibdawi]

Any news on this?

[Edu93Jer]

Is this still open?

[iengonline]

Is this still open?

[github-actions]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[rohitsud]

Still an issue