NodeJs跟MySQL連線過久時的錯誤引發_Error: Cannot enqueue Query after fatal error.

 
由於我在應用中只有開一個connection用到底的關係
當connection   timeout時就會報錯






設想如果今天有個表單要塞到DB
使用者填寫一半去洗手間或是開會回來
才完成表單填寫傳送時會發生的窘境

因此需要加上錯誤捕捉和重新連線的機制

Before
就是只產生唯一的連線


After

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
//啟動mysql連線
//connect.connect();

connect.connect(function(err){
    if(err) {
        console.log("\n\t *** Cannot establish a connection with the database. ***");
        connect = reconnect(connect);
    }else {
        console.log("\n\t *** New connection established with the database. ***")
    }
});

function reconnect(connection){
    console.log("\n New connection tentative...");
    //- Destroy the current connection variable
    if(connection) connection.destroy();
    //- Create a new one
    var connection = mysql.createConnection(db_config);
    //- Try to reconnect
    connection.connect(function(err){
        if(err) {
            //- Try to connect every 2 seconds.
            setTimeout(reconnect, 2000);
        }else {
            console.log("\n\t *** New connection established with the database. ***")
            return connection;
        }
    });
}

//- Error listener
connect.on('error', function(err) {
    //- The server close the connection.
    if(err.code === "PROTOCOL_CONNECTION_LOST"){    
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        connect = reconnect(connect);
    }

    //- Connection in closing
    else if(err.code === "PROTOCOL_ENQUEUE_AFTER_QUIT"){
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        connect = reconnect(connect);
    }

    //- Fatal error : connection variable must be recreated
    else if(err.code === "PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR"){
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        connect = reconnect(connect);
    }

    //- Error because a connection is already being established
    else if(err.code === "PROTOCOL_ENQUEUE_HANDSHAKE_TWICE"){
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
    }

    //- Anything else
    else{
        console.log("/!\\ Cannot establish a connection with the database. /!\\ ("+err.code+")");
        connect = reconnect(connect);
    }

});

//將該連線拋給外部進行訪問
module.exports = connect;














Ref:

Error: Cannot enqueue Query after fatal error. #1694

Cannot enqueue Query after fatal error #832

mysql Unable to log the fatal error, Cannot enqueue Query after fatal error




留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

(2021年度)駕訓學科筆試準備題庫歸納分析_法規是非題