React+NodeJS+MySQL邮件订阅服务POST 500错误排查与解决

问题场景 #

使用React作为客户端、NodeJS作为后端、MySQL作为数据库搭建邮件订阅服务，需求是用户输入邮箱提交后，后端将邮箱存入数据库。但调用接口时出现POST http://localhost:3001/subscribe 500（Internal Server Error）错误，API端点可正常访问，但无法完成数据库写入操作。

后端NodeJS代码 #

require('dotenv').config();
const express = require('express');
const nodemailer = require('nodemailer');
const app = express();
const cors = require("cors");
const mysql = require("mysql");
const bodyParser = require("body-parser");

app.use(cors());
app.use(express.json());
app.use(bodyParser.json());

/* Email Subscription for website owner! */

// MySQL credentials

const db = mysql.createPool({
  host: "localhost",
  port: 3306,
  user: "root",
  password: process.env.DB_PASSWORD,
  database: "emailSubscriptions",
});

// API endpoint to handle email subscription
app.post("/subscribe", (req, res) => {
const email = req.body.email;

// Insert email address into MySQL database
const query = `INSERT INTO emails (email) VALUES ("${email}")`;
  db.query(query, [email], (error, results) => {
  console.log(query + email);
  if (error) {
      console.error(error);
      res.sendStatus(500);
    } else {
      res.sendStatus(200);
    }
  });
});

前端ReactJS代码 #

import { useState } from 'react';

export default function Subscribe() {
const [email, setEmail] = useState("");

const handleSubmit = async (e) => {
  e.preventDefault();
  console.log(email);
  try {
    const response = await fetch("http://localhost:3001/subscribe", {
      method: "POST",
      headers: {
        "Content-Type": "application/json",
      },
      body: JSON.stringify({ email }),
    });
    if (response.ok) {
      alert("Successfully subscribed!");
      setEmail("");
    } else {
      alert("Failed to subscribe. Please try again later.");
    }
  } catch (error) {
    console.error(error);
  }
};


return (
    <section className="bg-subscribe-bg bg-cover bg-bottom bg-no-repeat p-[150px] h-[80vh] flex flex-col items-start justify-center sm:p-[20px]">
        <div>
            <h2 className="text-slate-400 mb-[15px] text-4xl">Pridružite se našoj mail list</h2>
            <p className="text-slate-400 mb-[70px] text-xl">Ne propustite najnovije vijesti iz arhitekture i naših budućih projekata</p>
            <form onSubmit={handleSubmit}>
            <div className="flex w-[100%]">
                <input type="email" value={email} onChange={(e) => setEmail(e.target.value)} placeholder="Vaš email" className="h-[45px] w-[70%] border-[1px]"/>
                <button type="submit" className="h-[45px] bg-cyan-600 hover:bg-cyan-900 text-white w-[30%]">Pošalji</button>
            </div>
            </form>
        </div>
    </section>
    )
}

错误原因与解决方法 #

错误信息 #

errno: 1251
sqlMessage: '客户端不支持服务器请求的认证协议，请考虑升级MySQL客户端'

解决步骤 #

将NodeJS项目中的mysql依赖替换为mysql2：

1. 卸载原依赖：npm uninstall mysql
2. 安装mysql2：npm install mysql2
3. 修改代码中的引入语句：

// 替换前
const mysql = require("mysql");
// 替换后
const mysql = require("mysql2");

修改完成后，重新启动后端服务，即可正常完成邮箱写入数据库的操作。

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