mysql - Nodejs，mysql {[错误: connection lost: the server closed the connection.]

Question

当我输入 localhost:3000

20~30秒后，错误

错误控制台:{[错误:连接丢失:服务器关闭了连接。] falta:true，代码:'PROTOCOL_CONNECTION_LOST'}

我的代码

var express = require('express');
var mysql = require('mysql');
var app = express();

var pool      =    mysql.createPool({
    connectionLimit : 2, //important
    host: 'xxxxx',
    user: 'xxxxx',
    password: 'xxx',
    database: 'xxxx',
    debug    :  false
});

pool.on('connection', function(c) {
   console.log('connection');
});

pool.on('enqueue', function () {
    console.log('Waiting for available connection slot');
});



function handle_database(req,res) {

    pool.getConnection(function (err, connection) {


        if(err) {
            console.log(err);
        }

        connection.query( 'SELECT * FROM clientes limit 20', function(err, rows) {

            if(err) {
                console.log(err);
            }

            connection.release();

            res.json(rows);
            res.end();
        });

        connection.on('error', function(err) {
           console.log(err);
        });
    });
}

app.get("/",function(req,res){
    handle_database(req,res);
});

app.listen(3000);

请帮忙？我是 Node 新手

依赖关系

{
  "name": "ChatSuporte",
  "version": "0.0.1",
  "description": "Chat para suporte",
  "dependencies": {
    "express": "^4.12.3",
    "mysql": "^2.6.1"
  }
}

Answer 1

This works for me (non pooled though)..


var app = require('express')();             // Express App include
var http = require('http').Server(app);     // http server
var mysql = require('mysql');               // Mysql include
var bodyParser = require("body-parser");    // Body parser for fetch posted data
var util = require('util');


var db_config = { host : 'localhost', user : 'youruser', password : 'yourpassword', database : 'yourdatabase',};

var connection = mysql.createConnection(db_config);

function handleDisconnect() {
    console.log('handleDisconnect()');
    connection = mysql.createConnection(db_config); // Recreate the connection, since
                                                    // the old one cannot be reused.
    connection.connect(function(err) {              // The server is either down
    if(err) {                                      // or restarting (takes a while sometimes).
        console.log(' Error when connecting to db:', err);
        setTimeout(handleDisconnect, 1000);         // We introduce a delay before attempting to reconnect,
    }                                               // to avoid a hot loop, and to allow our node script to
    });                                             // process asynchronous requests in the meantime.
                                                    // If you're also serving http, display a 503 error.

    connection.on('  Database Error', function(err) {
        console.log('db error: ' + err.code, err);
        if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually
            handleDisconnect();                       // lost due to either server restart, or a
        } else {                                      // connnection idle timeout (the wait_timeout
            throw err;                                // server variable configures this)
        }
    });

}

// connection.destroy(); // Uncomment this to simulate a lost connection

app.post('/your/api/call',function(req,res){

            //connection.destroy();  // Uncomment this to simulate a lost connection

            connection.connect(function(err) {
            if(err) {
                console.log('Connection is asleep (time to wake it up): ', err);
                setTimeout(handleDisconnect, 1000);
            }
            });
            console.log(' Connection up?');
            // If you're also serving http, display a 503 error.

            var varparam1 = 'a@b.com'
            connection.query("SELECT * from tablename WHERE email = ?",[varparam1],function(err, rows, fields){
                if (err) {

                    console.log(" 503 Service unavailable (database error)");
                    errData = {"Status": "503", "Error": "Service unavailable (database error)"};
                    res.statusCode = 503;
                    res.send(errData);  
                    res.end();

                } else {

                    if (rows.length != 0) {

                        var filed1 = rows[0].filed1;

                        // etc data returned

                    } else {

                        // etc no data returned
                    }
                }

            });

});

http.listen(3000,function(){
    console.log("Connected & Listen to port 3000 at /your/api/ ..");
});