部署Node.js+Express+MySQL后端到Heroku遇连接拒绝及H10错误

终端错误信息 #

2023-02-04T02:23:34.535599+00:00 app[web.1]: ConnectionRefusedError [SequelizeConnectionRefusedError]: connect ECONNREFUSED 127.0.0.1:3306
2023-02-04T02:23:34.535615+00:00 app[web.1]: at ConnectionManager.connect (/app/node_modules/sequelize/lib/dialects/mysql/connection-manager.js:92:17)
2023-02-04T02:23:34.535615+00:00 app[web.1]: at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
2023-02-04T02:23:34.535616+00:00 app[web.1]: at async ConnectionManager._connect (/app/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:220:24)
2023-02-04T02:23:34.535616+00:00 app[web.1]: at async /app/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:174:32
2023-02-04T02:23:34.535616+00:00 app[web.1]: at async ConnectionManager.getConnection (/app/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:197:7)
2023-02-04T02:23:34.535618+00:00 app[web.1]: at async /app/node_modules/sequelize/lib/sequelize.js:304:26
2023-02-04T02:23:34.535618+00:00 app[web.1]: at async MySQLQueryInterface.tableExists (/app/node_modules/sequelize/lib/dialects/abstract/query-interface.js:102:17)
2023-02-04T02:23:34.535618+00:00 app[web.1]: at async Albums.sync (/app/node_modules/sequelize/lib/model.js:939:21)
2023-02-04T02:23:34.535618+00:00 app[web.1]: at async Sequelize.sync (/app/node_modules/sequelize/lib/sequelize.js:376:9) {
2023-02-04T02:23:34.535619+00:00 app[web.1]: parent: Error: connect ECONNREFUSED 127.0.0.1:3306
2023-02-04T02:23:34.535619+00:00 app[web.1]: at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1494:16) {
2023-02-04T02:23:34.535620+00:00 app[web.1]: errno: -111,
2023-02-04T02:23:34.535622+00:00 app[web.1]: code: 'ECONNREFUSED',
2023-02-04T02:23:34.535622+00:00 app[web.1]: syscall: 'connect',
2023-02-04T02:23:34.535623+00:00 app[web.1]: address: '127.0.0.1',
2023-02-04T02:23:34.535624+00:00 app[web.1]: port: 3306,
2023-02-04T02:23:34.535624+00:00 app[web.1]: fatal: true
2023-02-04T02:23:34.535624+00:00 app[web.1]: },
2023-02-04T02:23:34.535624+00:00 app[web.1]: original: Error: connect ECONNREFUSED 127.0.0.1:3306
2023-02-04T02:23:34.535624+00:00 app[web.1]: at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1494:16) {
2023-02-04T02:23:34.535625+00:00 app[web.1]: errno: -111,
2023-02-04T02:23:34.535625+00:00 app[web.1]: code: 'ECONNREFUSED',
2023-02-04T02:23:34.535625+00:00 app[web.1]: syscall: 'connect',
2023-02-04T02:23:34.535625+00:00 app[web.1]: address: '127.0.0.1',
2023-02-04T02:23:34.535625+00:00 app[web.1]: port: 3306,
2023-02-04T02:23:34.535625+00:00 app[web.1]: fatal: true
2023-02-04T02:23:34.535626+00:00 app[web.1]: }
2023-02-04T02:23:34.535626+00:00 app[web.1]: }
2023-02-04T02:23:34.543110+00:00 app[web.1]: npm notice
2023-02-04T02:23:34.543220+00:00 app[web.1]: npm notice New minor version of npm available! 9.3.1 -> 9.4.1
2023-02-04T02:23:34.543597+00:00 app[web.1]: npm notice Changelog: <https://github.com/npm/cli/releases/tag/v9.4.1>
2023-02-04T02:23:34.543655+00:00 app[web.1]: npm notice Run `npm install -g npm@9.4.1` to update!
2023-02-04T02:23:34.543847+00:00 app[web.1]: npm notice
2023-02-04T02:23:34.680163+00:00 heroku[web.1]: Process exited with status 0
2023-02-04T02:23:34.725119+00:00 heroku[web.1]: State changed from starting to crashed
2023-02-04T02:23:36.383791+00:00 heroku[router]: at=error code=H10 desc="App crashed" method=GET path="/" host=sq-photos-api.herokuapp.com request_id=68108bed-86b9-4d92-9a08-5c693cd4f9e4 fwd="50.38.34.64" dyno= connect= service= status=503 bytes= protocol=https
2023-02-04T02:23:36.800226+00:00 heroku[router]: at=error code=H10 desc="App crashed" method=GET path="/favicon.ico" host=sq-photos-api.herokuapp.com request_id=1c4e1c2b-023c-4d9e-bc7b-3e4f24f0fb50 fwd="50.38.34.64" dyno= connect= service= status=503 bytes= protocol=https

相关代码文件 #

server.js #

const express = require("express");
const app = express();
const cors = require("cors");
//For Heroku
require("dotenv").config();
const port = process.env.PORT || 6060;

app.use(express.json());
app.use(cors());
const db = require("./models");

//Routers
//Album Router
const albumRouter = require("./routes/Albums");
app.use("/albums", albumRouter);
//Teams Router
const teamRouter = require("./routes/Teams");
app.use("/teams", teamRouter);
//User Router
const userRouter = require("./routes/Users");
app.use("/auth", userRouter);

db.sequelize
  .sync()
  .then(() => {
    //Heroku runs process.env.PORT and our own port
    app.listen(port, () => {
      console.log(`Server started on 6060`);
    });
  })
  .catch((err) => {
    console.log(err);
  });

config.json #

{
  "development": {
    "username": "root",
    "password": "password",
    "database": "sq_photo_db",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

package.json #

{
  "name": "server",
  "version": "1.0.0",
  "description": "",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node server.js",
    "devStart": "nodemon server.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "bcryptjs": "^2.4.3",
    "cookie-parser": "^1.4.6",
    "cors": "^2.8.5",
    "dotenv": "^16.0.3",
    "express": "^4.18.2",
    "jsonwebtoken": "^9.0.0",
    "mysql2": "^3.1.0",
    "nodemon": "^2.0.20",
    "sequelize": "^6.28.0",
    "sequelize-cli": "^6.6.0"
  }
}

已尝试的解决步骤 #

• 在server.js中设置端口变量并传入app.listen()，未解决H10错误
• 确认本地MySQL服务正常运行
• 修改config.json中development的host为"localhost"，问题仍存在

问题分析与解决方案指引 #

核心问题：Heroku的dyno环境中没有本地MySQL服务，应用尝试连接127.0.0.1:3306必然失败。

解决步骤 #

1. 添加Heroku MySQL插件
   在Heroku控制台为应用添加MySQL服务插件（如ClearDB MySQL），插件会自动生成数据库连接信息并注入到应用的环境变量中。

2. 修改数据库配置

   • 方式一：更新config.json的production配置，使用环境变量填充数据库信息：

     "production": {
       "username": process.env.CLEARDB_DATABASE_USERNAME,
       "password": process.env.CLEARDB_DATABASE_PASSWORD,
       "database": process.env.CLEARDB_DATABASE_NAME,
       "host": process.env.CLEARDB_DATABASE_HOST,
       "dialect": "mysql"
     }
     

   • 方式二：直接在sequelize初始化时读取环境变量（推荐），避免硬编码配置。

3. 设置Heroku环境变量
   如果插件未自动注入变量，手动在Heroku应用的「Settings」-「Config Vars」中添加数据库连接所需的变量，包括用户名、密码、主机、数据库名。

4. 确认生产环境模式
   确保Heroku运行应用时使用production环境，可通过设置环境变量NODE_ENV=production实现，在Heroku控制台或命令行执行：

   heroku config:set NODE_ENV=production
   

5. 重新部署应用
   修改配置后，重新推送代码到Heroku，或通过Heroku控制台重启应用，验证数据库连接是否正常。

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