Error retrieve connection from pool timeout

[crbon]

Hi there,
Not sure why I'm getting these two errors, when I'm basically using the example ECMAScript 2017 code provided.

The MariaDB server on web host instance and the NodeJS server IP is allowed to connect to the SQL DB.

Error: retrieve connection from pool timeout
    at Object.module.exports.createError (E:\node-project\node_modules\mariadb\lib\misc\errors.js:55:10)
    at timeoutTask (E:\node-project\node_modules\mariadb\lib\pool-base.js:297:16)
    at Timeout.rejectAndResetTimeout [as _onTimeout] (E:\node-project\node_modules\mariadb\lib\pool-base.js:317:5)
    at listOnTimeout (internal/timers.js:533:17)
    at processTimers (internal/timers.js:475:7)
SQLError: (conn=-1, no: 45012, SQLState: 08S01) Connection timeout
    at Object.module.exports.createError (E:\node-project\node_modules\mariadb\lib\misc\errors.js:55:10)
    at Timeout._connectTimeoutReached [as _onTimeout] (E:\node-project\node_modules\mariadb\lib\connection.js:964:14)
    at listOnTimeout (internal/timers.js:533:17)
    at processTimers (internal/timers.js:475:7)
 From event:
    at _registerHandshakeCmd (E:\node-project\node_modules\mariadb\lib\connection.js:632:11)
    at E:\node-project\node_modules\mariadb\lib\connection.js:59:11
    at new Promise (<anonymous>)
    at Connection.connect (E:\node-project\node_modules\mariadb\lib\connection.js:58:16)
    at createConnectionPoolPromise (E:\node-project\node_modules\mariadb\lib\pool-promise.js:30:8)
    at creationTryout (E:\node-project\node_modules\mariadb\lib\pool-base.js:347:9)
    at listOnTimeout (internal/timers.js:531:17)
    at processTimers (internal/timers.js:475:7) {
  fatal: true,
  errno: 45012,
  sqlState: '08S01',
  code: 'ER_CONNECTION_TIMEOUT'
}

and/or

{ Error: (conn=-1, no: 45012, SQLState: 08S01) Connection timeout
    at Object.module.exports.createError (/root/node-project/node_modules/mariadb/lib/misc/errors.js:55:10)
    at Timeout._connectTimeoutReached [as _onTimeout] (/root/node-project/node_modules/mariadb/lib/connection.js:964:14)
    at ontimeout (timers.js:438:13)
    at tryOnTimeout (timers.js:300:5)
    at listOnTimeout (timers.js:263:5)
    at Timer.processTimers (timers.js:223:10)
 From event:
    at _registerHandshakeCmd (/root/node-project/node_modules/mariadb/lib/connection.js:632:11)
    at /root/node-project/node_modules/mariadb/lib/connection.js:59:11
    at new Promise (<anonymous>)
    at Connection.connect (/root/node-project/node_modules/mariadb/lib/connection.js:58:16)
    at createConnectionPoolPromise (/root/node-project/node_modules/mariadb/lib/pool-promise.js:30:8)
    at creationTryout (/root/node-project/node_modules/mariadb/lib/pool-base.js:347:9)
    at ontimeout (timers.js:436:11)
    at tryOnTimeout (timers.js:300:5)
    at listOnTimeout (timers.js:263:5)
    at Timer.processTimers (timers.js:223:10)
  fatal: true,
  errno: 45012,
  sqlState: '08S01',
  code: 'ER_CONNECTION_TIMEOUT' }

[crbon]

I'm definitely doing something wrong, because I got pooling to work with mysql2

[rusher]

Could you indicate configuration options and indicate if this occurs immediatly of after some time ?

Connection timeout implementation completely differ from mysql2. It can occur when all connections from pool are used for example.

sorry for delay !

[thanhtung6824]

@rusher I get same error sometime after execute 10-15 query. Here is my config

   async init() {
     try {
      this.pool = await mariadb.createPool( {
         host: process.env.DB_HOST,
         user: process.env.DB_USER,
         password: process.env.DB_PWD,
         port: process.env.DB_PORT,
         database: process.env.DB_NAME,
         connectionLimit: 5
       } );
     } catch (err) {
       throw err;
     }
   }
 
   get connection() {
     return ( async() => {
       this.conn = await this.pool.getConnection();
       return this.conn;
     } )();
   }

   releaseConnection() {
     if (this.conn) {
       this.conn.release();
     }
   }
 
module.exports = new Database();

Here is using

try {
      const conn = await Database.connection;
     //using
} catch (err) {

} finally {
      Database.releaseConnection();
 }

[crbon]

@rusher As far as I remember, the errors wouldn't start on the first few executions, but a bit later while the code was running,

Is this what you mean by the configuration?
getChannelData() gets call from another async function.

const pool = mariadb.createPool({
	host: process.env.DB_HOST,
	user: process.env.DB_USER,
	password: process.env.DB_PWD,
	database: process.env.DB_NAME,
	connectionLimit: 5
})

async function getChannelData(ch_id) {
	let conn, channel_name, network
	try {
		conn = await pool.getConnection()
		const rows = await conn.query("SELECT name, network FROM channel_list WHERE channel_id = ?", [ch_id])
		channel_name = rows[0].name
		network = rows[0].network
	} catch (err) {
		throw err
	} finally {
		if (conn) conn.release()
	}
}

[juicycool92]

i have same error incounted.
any hint for this probloms?

below code is mine if you still no clue of this issue.

• dbinfo.js

const dbData = {
    host : Config.dbIp,
    port : Config.dbPort,
    user : Config.dbUser,
    password : Config.dbPassword,
    database : 'foo'
};

• dbcreator.js

const mariaDb = require( 'mariadb' );
import { dbData } from './dbinfo';
export default class dbModule {
    constructor() {
        this.pool = this.createPool();
        Logger.info( `DB pool created` );
    }

    createPool() {
        return mariaDb.createPool( dbData );
    }

    getPool() {
        return this.pool;
    }
}

• poolModule.js

export default class {
    constructor( pool ) { //pass pool that create from dbcreator.getPool() 
        this._pool = pool;
        Logger.info( 'pool created' );
    }
    set pool( pool ){
        this._pool = pool;
    }
    get pool() {
        return this._pool;
    }
    createConn() { 
        return new Promise( ( resolve, reject ) => {
            this.pool.getConnection()
                .then( conn => resolve( conn ) )
                .catch( e => reject( `${Tag}[createConn]${e}` ) );
        } );
    }

    releaseConn( conn ) { 
        return new Promise( ( resolve, reject ) => {
            conn.release()
                .then( _ => resolve() )
                .catch( e => reject( `${Tag}[releaseConn]${e}` ) );
        } );
    }

}

• actual usage

async insertBoard( conn, seqUser, boardTitle, boardContext, boardType ) {
        let conn;
        try {
            conn = await poolModule.createConn();
            return await conn.query( `INSERT INTO Board (seqUser, boardTitle, boardContext, boardType) VALUES (?, ?, ?, ?)`,
                [ seqUser, boardTitle, boardContext, boardType ] );
        } catch ( e ) {
            throw `[insertBoard] ${e}`;
        } finally {
            await poolModule.releaseConn(conn);
        }
    }

and this is my error logs

[Express] 2020-01-22T12:10:55+09:00 [serverLogger] error: [/exercise/getAllMyPatientExerciseHistoryByLimit] [ExerciseService] [getAllMyPatientExerciseHistoryByLimit] [PoolModule][createConn]Error: retrieve con
nection from pool timeout

Thanks

[rusher]

There is many things in that thread.
@thanhtung6824 your code have a connection leak : you'll close only the last connection (this.conn)

After studying each line of the code, i failed to see how there can be a connection leak in pool implementation. To have more insight of this issue, a new pool option [leakDetectionTimeout](https://github.com/mariadb-corporation/mariadb-connector-nodejs/blob/master/documentation/promise-api.md#pool-options) has been added. This permit to log connection that have been out to pool for some time, for better detection.

Could you test with this option set to have better information ?

So either pool
release 2.2.0 is out with a new option

[juicycool92]

There is many things in that thread.
@thanhtung6824 your code have a connection leak : you'll close only the last connection (this.conn)

After studying each line of the code, i failed to see how there can be a connection leak in pool implementation. To have more insight of this issue, a new pool option [leakDetectionTimeout](https://github.com/mariadb-corporation/mariadb-connector-nodejs/blob/master/documentation/promise-api.md#pool-options) has been added. This permit to log connection that have been out to pool for some time, for better detection.

Could you test with this option set to have better information ?

So either pool
release 2.2.0 is out with a new option

Thanks for reply.
I updated module and append [leakDetectionTimeout] option into my project.
and i tried 90 times of insert with my project.
it was happened about i insert 30 times around...

But oddly, i cannot create same error again. 🤔

Ill keep my eyes on Logger for catching same error from now.
will create issue if i found same error. :/

[rusher]

ok, closing this from now, then.
If you reproduced the issue, please create a new issue and llink to this one

[marcj]

@rusher it seems leakDetectionTimeout is not in the types. Is it still implemented?

I'm having the same issue. I have my own thin connection pool abstraction (on top of mariadb's Pool class), which makes sure that no connection is used twice at the same time. After adding more integration tests I suddenly get this error message as well. After debugging I made sure that there is no dangling connection (by printing when someone acquires vs releases a connection).
The weird behaviour is that when I set connectionLimit: 1 the error disappears. Beginning with connectionLimit: 10, the error appears again. During the integration-tests, it creates over 73 connections, where all are released. It has at max 4 connections open parallel. My MySQL max_connections is set to 151.

When I set minDelayValidation: 1000 I get from the MySQL server SqlError: (conn=-1, no: 1040, SQLState: HY000) Too many connections. And indeed,

show status where `variable_name` = 'Threads_connected';

shows 152, while my connection counter (which is increased when pool.getConnection is called, and decreased when PoolConnection.release is called) shows only 4. Indeed this was an error in my code, not closing the Pool via Pool.end after each test. I assumed that PoolConnection.release closes the connection, but that is obviously not that case (which is fine). So, I'm writing this to tell all future readers: Make sure to close the pool when you are done with it, otherwise you get the error message from the thread author, since pool connections are not closed immediately and running many tests at the same time (all using their own Pool) will lead to exhausting the max_connections setting even when you release all connections properly.