Node.js MySQL 错误处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40141332/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Node.js MySQL Error Handling
提问by Dave
I've read several examples for using mysql in node.js and I have questions about the error handling.
我已经阅读了几个在 node.js 中使用 mysql 的示例，我对错误处理有疑问。
Most examples do error handling like this (perhaps for brevity):
大多数示例都像这样进行错误处理（可能是为了简洁）：
app.get('/countries', function(req, res) {
pool.createConnection(function(err, connection) {
if (err) { throw err; }
connection.query(sql, function(err, results) {
if (err) { throw err; }
connection.release();
// do something with results
});
});
});
This causes the server to crash every time there's an sql error. I'd like to avoid that and keep the server running.
这会导致服务器在每次出现 sql 错误时崩溃。我想避免这种情况并保持服务器运行。
My code is like this:
我的代码是这样的：
app.get('/countries', function(req, res) {
pool.createConnection(function(err, connection) {
if (err) {
console.log(err);
res.send({ success: false, message: 'database error', error: err });
return;
}
connection.on('error', function(err) {
console.log(err);
res.send({ success: false, message: 'database error', error: err });
return;
});
connection.query(sql, function(err, results) {
if (err) {
console.log(err);
res.send({ success: false, message: 'query error', error: err });
return;
}
connection.release();
// do something with results
});
});
});
I'm not sure if this is the best way to handle it. I'm also wondering if there should be a connection.release()in the query's errblock. Otherwise the connections might stay open and build up over time.
我不确定这是否是处理它的最佳方式。我还想知道connection.release()查询err块中是否应该有 a 。否则，连接可能会保持打开状态并随着时间的推移而建立。
I'm used to Java's try...catch...finallyor try-with-resourceswhere I can "cleanly" catch any errors and close all my resources at the end. Is there a way to propagate the errors up and handle them all in one place?
我已经习惯了 Javatry...catch...finally或者try-with-resources我可以“干净地”捕获任何错误并在最后关闭我的所有资源的地方。有没有办法将错误向上传播并在一个地方处理它们？
回答by Dave
I've decided to handle it using es2017 syntax and Babel to transpile down to es2016, which Node 7 supports.
我决定使用 es2017 语法和 Babel 来处理它，以将其转换为 Node 7 支持的 es2016。
Newer versions of Node.js support this syntax without transpiling.
较新版本的 Node.js 无需转译即可支持此语法。
Here is an example:
下面是一个例子：
'use strict';
const express = require('express');
const router = express.Router();
const Promise = require('bluebird');
const HttpStatus = require('http-status-codes');
const fs = Promise.promisifyAll(require('fs'));
const pool = require('./pool'); // my database pool module, using promise-mysql
const Errors = require('./errors'); // my collection of custom exceptions
////////////////////////////////////////////////////////////////////////////////
// GET /v1/provinces/:id
////////////////////////////////////////////////////////////////////////////////
router.get('/provinces/:id', async (req, res) => {
try {
// get a connection from the pool
const connection = await pool.createConnection();
try {
// retrieve the list of provinces from the database
const sql_p = `SELECT p.id, p.code, p.name, p.country_id
FROM provinces p
WHERE p.id = ?
LIMIT 1`;
const provinces = await connection.query(sql_p);
if (!provinces.length)
throw new Errors.NotFound('province not found');
const province = provinces[0];
// retrieve the associated country from the database
const sql_c = `SELECT c.code, c.name
FROM countries c
WHERE c.id = ?
LIMIT 1`;
const countries = await connection.query(sql_c, province.country_id);
if (!countries.length)
throw new Errors.InternalServerError('country not found');
province.country = countries[0];
return res.send({ province });
} finally {
pool.releaseConnection(connection);
}
} catch (err) {
if (err instanceof Errors.NotFound)
return res.status(HttpStatus.NOT_FOUND).send({ message: err.message }); // 404
console.log(err);
return res.status(HttpStatus.INTERNAL_SERVER_ERROR).send({ error: err, message: err.message }); // 500
}
});
////////////////////////////////////////////////////////////////////////////////
// GET /v1/provinces
////////////////////////////////////////////////////////////////////////////////
router.get('/provinces', async (req, res) => {
try {
// get a connection from the pool
const connection = await pool.createConnection();
try {
// retrieve the list of provinces from the database
const sql_p = `SELECT p.id, p.code, p.name, p.country_id
FROM provinces p`;
const provinces = await connection.query(sql_p);
const sql_c = `SELECT c.code, c.name
FROM countries c
WHERE c.id = ?
LIMIT 1`;
const promises = provinces.map(async p => {
// retrieve the associated country from the database
const countries = await connection.query(sql_c, p.country_id);
if (!countries.length)
throw new Errors.InternalServerError('country not found');
p.country = countries[0];
});
await Promise.all(promises);
return res.send({ total: provinces.length, provinces });
} finally {
pool.releaseConnection(connection);
}
} catch (err) {
console.log(err);
return res.status(HttpStatus.INTERNAL_SERVER_ERROR).send({ error: err, message: err.message }); // 500
}
});
////////////////////////////////////////////////////////////////////////////////
// OPTIONS /v1/provinces
////////////////////////////////////////////////////////////////////////////////
router.options('/provinces', async (req, res) => {
try {
const data = await fs.readFileAsync('./options/provinces.json');
res.setHeader('Access-Control-Allow-Methods', 'HEAD,GET,OPTIONS');
res.setHeader('Allow', 'HEAD,GET,OPTIONS');
res.send(JSON.parse(data));
} catch (err) {
res.status(HttpStatus.INTERNAL_SERVER_ERROR).send({ error: err, message: err.message });
}
});
module.exports = router;
Using async/awaitalong with this try { try { } finally { } } catch { } patternmakes for clean error handling, where you can collect and deal with all your errors in one place. The finally block closes the database connection no matter what.
使用async/await伴随此try { try { } finally { } } catch { } pattern品牌清洁的错误处理，在那里你可以收集和处理在一个地方所有的错误。无论如何，finally 块都会关闭数据库连接。
You just have to make sure you're dealing with promises all the way through. For database access, I use the promise-mysqlmodule instead of plain mysqlmodule. For everything else, I use the bluebirdmodule and promisifyAll().
你只需要确保你一直在处理承诺。对于数据库访问，我使用promise-mysql模块而不是普通mysql模块。对于其他一切，我使用bluebird模块和promisifyAll().
I also have custom Exception classes that I can throw under certain circumstances and then detect those in the catch block. Depending on which exceptions can get thrown in the try block, my catch block might look something like this:
我还有自定义 Exception 类，我可以在某些情况下抛出这些类，然后在 catch 块中检测它们。根据 try 块中可以抛出哪些异常，我的 catch 块可能如下所示：
catch (err) {
if (err instanceof Errors.BadRequest)
return res.status(HttpStatus.BAD_REQUEST).send({ message: err.message }); // 400
if (err instanceof Errors.Forbidden)
return res.status(HttpStatus.FORBIDDEN).send({ message: err.message }); // 403
if (err instanceof Errors.NotFound)
return res.status(HttpStatus.NOT_FOUND).send({ message: err.message }); // 404
if (err instanceof Errors.UnprocessableEntity)
return res.status(HttpStatus.UNPROCESSABLE_ENTITY).send({ message: err.message }); // 422
console.log(err);
return res.status(HttpStatus.INTERNAL_SERVER_ERROR).send({ error: err, message: err.message });
}
pool.js:
池.js：
'use strict';
const mysql = require('promise-mysql');
const pool = mysql.createPool({
connectionLimit: 100,
host: 'localhost',
user: 'user',
password: 'password',
database: 'database',
charset: 'utf8mb4',
debug: false
});
module.exports = pool;
errors.js:
错误.js：
'use strict';
class ExtendableError extends Error {
constructor(message) {
if (new.target === ExtendableError)
throw new TypeError('Abstract class "ExtendableError" cannot be instantiated directly.');
super(message);
this.name = this.constructor.name;
this.message = message;
Error.captureStackTrace(this, this.contructor);
}
}
// 400 Bad Request
class BadRequest extends ExtendableError {
constructor(m) {
if (arguments.length === 0)
super('bad request');
else
super(m);
}
}
// 401 Unauthorized
class Unauthorized extends ExtendableError {
constructor(m) {
if (arguments.length === 0)
super('unauthorized');
else
super(m);
}
}
// 403 Forbidden
class Forbidden extends ExtendableError {
constructor(m) {
if (arguments.length === 0)
super('forbidden');
else
super(m);
}
}
// 404 Not Found
class NotFound extends ExtendableError {
constructor(m) {
if (arguments.length === 0)
super('not found');
else
super(m);
}
}
// 409 Conflict
class Conflict extends ExtendableError {
constructor(m) {
if (arguments.length === 0)
super('conflict');
else
super(m);
}
}
// 422 Unprocessable Entity
class UnprocessableEntity extends ExtendableError {
constructor(m) {
if (arguments.length === 0)
super('unprocessable entity');
else
super(m);
}
}
// 500 Internal Server Error
class InternalServerError extends ExtendableError {
constructor(m) {
if (arguments.length === 0)
super('internal server error');
else
super(m);
}
}
module.exports.BadRequest = BadRequest;
module.exports.Unauthorized = Unauthorized;
module.exports.Forbidden = Forbidden;
module.exports.NotFound = NotFound;
module.exports.Conflict = Conflict;
module.exports.UnprocessableEntity = UnprocessableEntity;
module.exports.InternalServerError = InternalServerError;
回答by Jo?o Pimentel Ferreira
Another elegant solution is to use async.series, and its way of managing errors
另一个优雅的解决方案是使用async.series, 及其管理错误的方式
const mysql = require('mysql')
const async = require('async')
async.series([
function (next) {
db = mysql.createConnection(DB_INFO)
db.connect(function(err) {
if (err) {
// this callback/next function takes 2 optional parameters:
// (error, results)
next('Error connecting: ' + err.message)
} else {
next() // no error parameter filled => no error
}
})
},
function (next) {
var myQuery = ....
db.query(myQuery, function (err, results, fields) {
if (err) {
next('error making the query: ' + err.message)
return // this must be here
}
// do something with results
// ...
next(null, results) // send the results
})
},
function (next) {
db.close()
}],
//done after all functions were executed, except if it was an error
function(err, results) {
if (err) {
console.log('There was an error: ', err)
}
else {
//read the results after everything went well
... results ....
}
})
回答by Matthew
In order to handle specific error handling cases that have returned from the sql connection you can look at the the 'error' object returned from the callback.
为了处理从 sql 连接返回的特定错误处理情况，您可以查看从回调返回的“错误”对象。
so..
所以..
const mysql = require('mysql')
let conn = mysql.createConnection(connConfig)
conn.query(query, function(error, result, fields){
if (error){
console.log(typeof(error));
for(var k in error){
console.log(`${k}: ${error[k]}`)
}
}
the console.log statement in the for loop above will output something like:
上面 for 循环中的 console.log 语句将输出如下内容：
object
目的
code: ER_TABLE_EXISTS_ERROR
errno: 1050
sqlMessage: Table 'table1' already exists
sqlState: 42S01
index: 0
sql: CREATE TABLE table1 (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
City varchar(255)
);
using these keys you can pass off the values to a handler
使用这些键，您可以将值传递给处理程序
回答by Ezio Blaze
I guess this method is more approachable. In this case even if you fail to gain connection you throw an Internal Server Error status to client(helpful if you building a Rest Api Server) and in case there is a query error after releasing connection your sending the error. Please correct me if am wrong anywhere.
我想这种方法更平易近人。在这种情况下，即使您无法获得连接，您也会向客户端抛出内部服务器错误状态（如果您构建 Rest Api 服务器会很有帮助），并且如果在释放连接后出现查询错误，您会发送错误。如果哪里有错，请纠正我。
pool.getConnection(function(err, connection){
if(err){
console.log(err);
return res.status(500).json();
};
connection.query('SELECT * FROM TABLE', function(err,results,fields){
connection.release();
if(err){
console.log(err);
return (res.status(500).json());
};
res.status(201).send('OK');
});
});
回答by Dylan Law
I think you can do something like this. No matter how, the connection will be released once it is done querying and the server will not crash because of the error.
我认为你可以做这样的事情。不管怎样，一旦查询完成就会释放连接，服务器不会因为错误而崩溃。
var queryString = "SELECT * FROM notification_detail nd LEFT JOIN notification n ON nd.id_notification = n.uuid WHERE login_id = ? id_company = ?;";
var filter = [loginId, idCompany];
var query = connection.query({
sql: queryString,
timeout: 10000,
}, filter );
query
.on('error', function(err) {
if (err) {
console.log(err.code);
// Do anything you want whenever there is an error.
// throw err;
}
})
.on('result', function(row) {
//Do something with your result.
})
.on('end', function() {
connection.release();
});
This can be an alternative solution which is much simpler.
这可以是一种更简单的替代解决方案。
var query = connection.query({
sql: queryString,
timeout: 10000,
}, function(err, rows, fields) {
if (err) {
//Do not throw err as it will crash the server.
console.log(err.code);
} else {
//Do anything with the query result
}
connection.release()
});
回答by razu
This is a function to return available pool upon successful MySQL connection. So before I proceed with any query, I'll await this function to check whether connection is OK. This will not crash the server even if there's no connection to MySQL.
这是一个在成功连接 MySQL 后返回可用池的函数。因此，在继续进行任何查询之前，我将等待此函数来检查连接是否正常。即使没有连接到 MySQL，这也不会使服务器崩溃。
connect: function ()
{
return new Promise((resolve, reject) => {
let pool = Mysql.createPool({
connectionLimit: config.mysql.connectionLimit,
host: config.mysql.host,
user: config.mysql.user,
password: config.mysql.password,
database: config.mysql.database
});
pool.getConnection((err, con) =>
{
try
{
if (con)
{
con.release();
resolve({"status":"success", "message":"MySQL connected.", "con":pool});
}
}
catch (err)
{
reject({"status":"failed", "error":`MySQL error. ${err}`});
}
resolve({"status":"failed", "error":"Error connecting to MySQL."});
});
});
}
MySQL package used: https://www.npmjs.com/package/mysql
使用的 MySQL 包：https: //www.npmjs.com/package/mysql
Native Promise async/await ES2017
原生 Promise 异步/等待 ES2017
