Get 'ER_TOO_LONG_KEY error running migrations in the utf8mb4 mysql database

[tiaozi0912]

When running migrations, get the ER_TOO_LONG_KEY, probably because the mysql database charset is utf8mb4. How to make it work?

Here is the database config:

"staging": {
    "username": "USERNAME",
    "password": "PASSWORD",
    "database": "DATABASE",
    "host": "HOST",
    "dialect": "mysql",
    "port": 3306,
    "define": {
      "charset": "utf8mb4",
      "collate": "utf8mb4_general_ci"
    }
  }

The table trying to create:

module.exports = {
  up: function (queryInterface, Sequelize) {
    return queryInterface.createTable('users', {
      id: {
        type:Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      openid: Sequelize.STRING,
      nickname: Sequelize.STRING,
      sex: {
        type: Sequelize.INTEGER,
        defaultValue: 1
      },
      headimgurl: Sequelize.STRING,
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE
    });
  },

  down: function (queryInterface, Sequelize) {
    return queryInterface.dropTable('users');
  }
};

And the sql that executed and got the error was

CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;

Based on the discussion sequelize/sequelize#4171 (comment), @janmeier suggested it was an issue of the migrator thus open an issue here.

[janmeier]

Just to clarify, the sequelizeMeta table should probably explicitly set its charset to utf8 to prevent this issue. The issue is that the unique key of 255 chars is too long for utf8mb4

[tiaozi0912]

Or there should be a way to configure the storageOptions.columnType in sequelize.js in config.json

[evantahler]

Just a note that the "common practice" (as defined by Ruby's Active Record for migrations) is that for UTF8MB4 tables, the VARCHAR key length should be shrunk from 255 to 191. This allows keys to fit in the proper space without any other error.

Here's some musings on the migration path regarding a ruby project: http://tech.taskrabbit.com/blog/2014/04/24/active-record-mysql-and-emoji/