mysql pool.getConnection fails breaks the code

user_5796284 0
Viewed 583

im new to nodejs so bare with me , im trying to to some connection heavy task like a game server i have a mysql poll and a function create_game which will use the poll to get a new connection

so i noticed sometimes the nodejs will stop working for no reason , i traced to problem in create_game function by putting numbers on each line like

 console.log(` @-> ${debug_id} :1 `);
 console.log(` @-> ${debug_id} : 2 `);

to see which line is breaking my code

var pool  = mysql.createPool({
    connectionLimit : 20 ,
    host            : myEnv.parsed.DB_HOST,
    user            : myEnv.parsed.DB_USERNAME,
    password        : myEnv.parsed.DB_PASSWORD,
    database        : myEnv.parsed.DB_DATABASE
});





function create_game( debug_id ) {

    console.log(` @-> ${debug_id} : 1 `);

    let promise =  new Promise(function(resolve , reject ) {
    
        console.log(` @-> ${debug_id} : 2 `);

        pool.getConnection(function(err, connection) {
            console.log(` @-> ${debug_id} : 3 `);
            if (err) {
                console.log(` @-> ${debug_id} : 3E `);
                reject(err.sqlMessage);
            }
            else
            {
                connection.beginTransaction(function (err) { 
                
                  /// do stufff 

                 console.log(` @-> ${debug_id} : success `);


                });

            }

        });
    })

    .then(function (res) {

        consoleLog('create success');
    })
    .catch(function (error) {
        consoleLog('///////////ERROR///////////');
        consoleLog(error);

     });

}

this is what i got in the console

 @-> 7 : 1
 @-> 7 : 2
 @-> 7 : 3
 @-> 7 : 4
 @-> 7 : 5
 @-> 7 : 6
 @-> 7 : 7
 @-> 7 : 8
 @-> 7 : 9
 @-> 7 : 10
 @-> 7 : 11
 @-> 7 : 12
 @-> 7 : success

 @-> 8 : 1
 @-> 8 : 2

so based on the output it seems pool.getConnection is failing i assume becuz it hit the connectionLimit : 20 at that point

so what im doing wrong ? why the code breaks .. i mean i do have a code to handle pool.getConnection fail in here so why im not getting 3 and 3E output ?

        pool.getConnection(function(err, connection) {
            console.log(` @-> ${debug_id} : 3 `);
            if (err) {
                console.log(` @-> ${debug_id} : 3E `);
                reject(err.sqlMessage);
            }

i still see the socket connections in terminal being made so the node script still works ... but all the database related operations will stop working after this without any error or output

promise node.js mysql javascript
2 Answers

You need to provide more information. You have not mentioned what mysql library you are using? The logs you are showing do not match the code. i.e. where are logs 4-12?

So I am assuming you are using mysqljs/mysql.

An observation from the code that you have shown which could be a possible cause of your problem is that when you take a connection from the pool, you also need to release it back. You haven't shown that you are releasing the connection so I can only assume that you are not.

pool.getConnection(function(err, connection) {
    connection.query('SELECT * FROM sometable', function (error, results, fields) {
    // When done with the connection, release it.
    connection.release();

    // Handle error after the release.
    if (error) throw error;

    // Don't use the connection here, it has been returned to the pool.
  });
});

See https://github.com/mysqljs/mysql#pooling-connections

If you do not release the connection, you will use up all connections in the pool and the other requests for a connection will be queued and it would exhibit the behavior that you are describing.

On a side, you can also query directly on the pool which will handle releasing connections back to the pool once finished. That may not be a suitable use case for above as it looks like you are using the connection as part of a transaction - where you do need a reference to the underlying connection.

user_98700140

the function pool.getConnection() also requires an allocation pool, you might need add another error handler if this function call fails.

user_83489340
Related