Ubuntu服务器运行Blocktogether时Sequelize迁移报错：编码未识别undefined

问题场景 #

在Ubuntu服务器部署Blocktogether，执行数据库迁移命令：

./node_modules/.bin/sequelize --config=config/sequelize.json db:migrate --env=production

触发编码未识别错误，完整错误信息如下：

Sequelize CLI [Node: 12.22.9, CLI: 5.5.1, ORM: 5.22.5]
已加载配置文件"config/sequelize.json"。
使用环境"production"。
== 20160117144545-init: migrating =======
query: /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /
query:
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /
query:
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /
query:
/!40101 SET NAMES utf8 /
query:
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */

ERROR: Encoding not recognized: 'undefined' (searched as: 'undefined')

at Object.getCodec(/etc/blocktogether/node_modules/mysql2/node_modules/iconv-lite/lib/index.js:106:23)
at Object.getEncoder(/etc/blocktogether/node_modules/mysql2/node_modules/iconv-lite/lib/index.js:117:23)
at Object.exports.encode (/etc/blocktogether/node_modules/mysql2/lib/parsers/string.js:23:25)
at Query.toPacket (/etc/blocktogether/node_modules/mysql2/lib/packets/query.js:16:30)
at Query.start (/etc/blocktogether/node_modules/mysql2/lib/commands/query.js:55:38)
at Query.execute (/etc/blocktogether/node_modules/mysql2/lib/commands/command.js:39:22)
at Connection.handlePacket (/etc/blocktogether/node_modules/mysql2/lib/connection.js:408:32)
at Connection.addCommand (/etc/blocktogether/node_modules/mysql2/lib/connection.js:430:12)
at Connection.query (/etc/blocktogether/node_modules/mysql2/lib/connection.js:493:17)
at /etc/blocktogether/node_modules/sequelize/lib/dialects/mysql/query.js:60:20
at Promise._execute (/etc/blocktogether/node_modules/bluebird/js/release/debuggability.js:384:9)
at Promise._resolveFromExecutor (/etc/blocktogether/node_modules/bluebird/js/release/promise.js:518:18)
at new Promise (/etc/blocktogether/node_modules/bluebird/js/release/promise.js:103:10)
at Query.run (/etc/blocktogether/node_modules/sequelize/lib/dialects/mysql/query.js:39:12)
at /etc/blocktogether/node_modules/sequelize/lib/sequelize.js:655:29
at tryCatcher (/etc/blocktogether/node_modules/bluebird/js/release/util.js:16:23)
at Promise._settlePromiseFromHandler (/etc/blocktogether/node_modules/bluebird/js/release/promise.js:547:31)
at Promise._settlePromise (/etc/blocktogether/node_modules/bluebird/js/release/promise.js:604:18)
at Promise._settlePromise0 (/etc/blocktogether/node_modules/bluebird/js/release/promise.js:649:10)
at Promise._settlePromises (/etc/blocktogether/node_modules/bluebird/js/release/promise.js:729:18)
at _drainQueueStep (/etc/blocktogether/node_modules/bluebird/js/release/async.js:93:12)
at _drainQueue (/etc/blocktogether/node_modules/bluebird/js/release/async.js:86:9)

当前使用的sequelize.json配置：

{
  "development": {
    "username": "root",
    "password": "Password",
    "database": "Twitter",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": "Password",
    "database": "Twitter",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

解决方法 #

1. 补充数据库编码配置 #

错误根源是Sequelize连接MySQL时未明确指定编码，导致mysql2尝试使用undefined作为编码格式。修改sequelize.json，为每个环境添加charset和dialectOptions配置：

{
  "development": {
    "username": "root",
    "password": "Password",
    "database": "Twitter",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "charset": "utf8mb4",
    "dialectOptions": {
      "charset": "utf8mb4"
    }
  },
  "production": {
    "username": "root",
    "password": "Password",
    "database": "Twitter",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "charset": "utf8mb4",
    "dialectOptions": {
      "charset": "utf8mb4"
    }
  }
}

utf8mb4支持完整Unicode字符（包括emoji），若不需要可替换为utf8。

2. 重新执行迁移命令 #

保存配置后，再次运行：

./node_modules/.bin/sequelize --config=config/sequelize.json db:migrate --env=production

3. 备选：修复依赖版本兼容性 #

若上述方法无效，可能是mysql2与Sequelize版本不兼容。当前Sequelize版本为5.22.5，锁定mysql2到兼容版本：

npm install mysql2@1.6.5 --save

安装完成后重新执行迁移命令。

内容的提问来源于stack exchange，提问作者Avand Fardi