在Heroku部署Strapi+ClearDB时遇无法获取连接错误求助

系统信息 #

• Node.js版本：v18.16.0
• NPM版本：v9.5.1
• Strapi版本：v4.11.0
• 数据库：ClearDB
• 操作系统：macOS Monterey 12.6.6
• 项目语言：JS

问题描述 #

在Heroku上部署搭配ClearDB的Strapi应用时失败，本地运行npm run develop报错“无法获取连接”，Heroku日志显示类型错误。

复现步骤 #

1. 注册Heroku账号；
2. 遵循Strapi-Heroku部署指南创建应用；
3. 安装ClearDB插件；
4. 修改./config/database.js为以下代码：

const path = require('path');

module.exports = ({ env }) => {
  const client = 'mysql';
  
const connections = {
    mysql: {
      connector: 'bookshelf',
      settings: {
        client: 'mysql',
        host: 'XX',
        port: '3306',
        database: 'XXX', 
        username: 'XXX',
        password: 'XXX',
        options: {
          dialectOptions: {
            supportBigNumbers: false,
            bigNumberStrings: false,
          },
          ssl:false
        },
      },
    },
  };

  return {
    connection: {
      client,
      ...connections[client],
    },
  };
};

package.json内容如下：

"name": "my-api",
  "private": true,
  "version": "0.1.0",
  "description": "A Strapi application",
  "scripts": {
    "develop": "strapi develop",
    "start": "strapi start",
    "build": "strapi build",
    "strapi": "strapi"
  },
  "dependencies": {
    "@strapi/plugin-i18n": "^4.11.0",
    "@strapi/plugin-users-permissions": "4.11.0",
    "@strapi/strapi": "4.11.0",
    "dotenv": "^16.1.4",
    "knex": "^2.4.2",
    "mysql": "^2.18.1"
    "mysql2": "^3.5.0"
  },
  "author": {
    "name": "A Strapi developer"
  },
  "strapi": {
    "uuid": "b106d56a-d879-475a-ab20-8ba1926a3c0b"
  },
  "engines": {
    "node": "18.16.0",
    "npm": "9.5.1"
  },
  "license": "MIT"
}

预期结果 #

部署成功并能正常打开管理后台。

错误信息 #

本地运行npm run develop报错 #

Error: Unable to acquire a connection
at Client_MySQL.acquireConnection (/Users/xx/node_modules/@strapi/database/node_modules/knex/lib/client.js:303:13)
at Runner.ensureConnection (/Users/xx/node_modules/@strapi/database/node_modules/knex/lib/execution/runner.js:287:46)
at Runner.run (/Users/xx/node_modules/@strapi/database/node_modules/knex/lib/execution/runner.js:30:30)
at Target.then (/Users/xx/node_modules/@strapi/database/node_modules/knex/lib/builder-interface-augmenter.js:24:43)

Heroku日志错误 #

debug: ⛔️ Server wasn't able to start properly.
error: Cannot set properties of undefined (setting 'supportBigNumbers')
TypeError: Cannot set properties of undefined (setting 'supportBigNumbers')
at MysqlDialect.configure (/app/node_modules/@strapi/database/lib/dialects/mysql/index.js:20:60)
at new Database (/app/node_modules/@strapi/database/lib/index.js:32:18)
at Database.init (/app/node_modules/@strapi/database/lib/index.js:121:14)
at Strapi.bootstrap (/app/node_modules/@strapi/strapi/lib/Strapi.js:421:30)
at Strapi.load (/app/node_modules/@strapi/strapi/lib/Strapi.js:492:16)
at async Strapi.start (/app/node_modules/@strapi/strapi/lib/Strapi.js:218:9)
npm notice
npm notice New minor version of npm available! 9.5.1 -> 9.7.1
npm notice Changelog: https://github.com/npm/cli/releases/tag/v9.7.1
npm notice Run npm install -g npm@9.7.1 to update!
npm notice
Process exited with status 1

其他上下文 #

• Heroku配置变量已包含用户名、密码、数据库、端口、主机、ssl:false等信息；
• 使用Heroku默认SQLite数据库时应用运行正常；
• 通过Sequel Pro使用代码中的硬编码凭证可成功连接ClearDB数据库，为调试已将凭证直接写入database.js而非使用.env。

修复方案 #

1. 修正数据库配置结构 #

Heroku日志的类型错误是因为Strapi v4的MySQL配置中，dialectOptions不需要嵌套在options下，直接放在settings层级即可。修改后的database.js：

const path = require('path');

module.exports = ({ env }) => {
  const client = 'mysql';
  
  const connections = {
    mysql: {
      connector: 'bookshelf',
      settings: {
        client: 'mysql',
        host: 'XX',
        port: '3306',
        database: 'XXX', 
        username: 'XXX',
        password: 'XXX',
        dialectOptions: {
          supportBigNumbers: false,
          bigNumberStrings: false,
        },
        ssl: false
      },
    },
  };

  return {
    connection: {
      client,
      ...connections[client],
    },
  };
};

2. 修复package.json语法错误 #

原package.json中mysql依赖行末尾缺少逗号，导致JSON解析失败。修改后的依赖部分：

"dependencies": {
  "@strapi/plugin-i18n": "^4.11.0",
  "@strapi/plugin-users-permissions": "4.11.0",
  "@strapi/strapi": "4.11.0",
  "dotenv": "^16.1.4",
  "knex": "^2.4.2",
  "mysql": "^2.18.1",
  "mysql2": "^3.5.0"
},

3. 推荐使用环境变量（可选） #

为了安全性和可维护性，建议从Heroku配置变量中读取数据库信息，修改database.js：

module.exports = ({ env }) => ({
  connection: {
    client: 'mysql',
    connector: 'bookshelf',
    settings: {
      host: env('DATABASE_HOST'),
      port: env.int('DATABASE_PORT', 3306),
      database: env('DATABASE_NAME'),
      username: env('DATABASE_USERNAME'),
      password: env('DATABASE_PASSWORD'),
      dialectOptions: {
        supportBigNumbers: false,
        bigNumberStrings: false,
      },
      ssl: env.bool('DATABASE_SSL', false),
    },
  },
});

4. 重新部署 #

提交修改后的代码并推送到Heroku：

git add . && git commit -m "Fix database config and package.json syntax"
git push heroku main

内容的提问来源于stack exchange，提问作者Paul Matthew