node-odbc: "Communication link failure" state:"08S01" on a connection pool

@markdirish We spoke at PowerUp about this issue and you invited me to tag you on it. The essence is that clients connecting from off the IBM i (Linux, Windows, etc.) time out and lose connection. People are currently coding around it to catch the error, disconnect and reconnect.

Any changes should just have to be done in JavaScript: Connections have a .connected property that calls SQLGetConnectAttr with the SQL_ATTR_CONNECTION_DEAD option. Returns true if SQL_ATTR_CONNECTION_DEAD returns false (since it makes much more sense for .connected to return true if its… connected). I imagine the code would just check:

if(!connection.connected) {
// close connection
// open a new connection and add it to pool
}

Maybe the issue is here: https://github.com/markdirish/node-odbc/blob/69f49bd4703e569ef01693f4d9241bcdeffa5b00/lib/Pool.js#L253

I think an assumption is being made that the connection that’s being popped is a good connection, and perhaps connection.connected should be checked on the popped connection. This will make sure the connection is good and if its not, to create a new connection.

Unless the developer using the node-odbc pool is expected to try catch when they run pool.query and catch the connection error message

State 08S01 Code 10054 [IBM][System i Access ODBC Driver]Communication link failure. comm rc=10054 - CWBC01047 - The IBM i server application disconnected the connection.

Then reinstantiate the pool. Which seems odd to me since I feel the pool should be gracefully handling dead connections without the developer needing to do anything.

 pool = await odbc.pool({
    connectionString,
    initialSize: 5,
    maxSize: 10,
  });

Going back to the php_odbc.c source comparison. node-odbc doesn’t have a

goto try_and_get_another_connection;

It just has a function to check for a dead connection. It would be nice if node-odbc realized it had a dead connection and replaced it with a new connection.

Ah! sorry you’re right this code looks right. I’ll have to keep looking around to try and figure out why I’m getting

State 08S01 Code 10054 [IBM][System i Access ODBC Driver]Communication link failure. comm rc=10054 - CWBC01047 - The IBM i server application disconnected the connection.

After the application is running for an entire day and request is made the next day.

These are the Db2 CLI docs, which is 90-95% the same as ODBC, but for some reason don’t seem to have this timeout…

The option can be found at the ODBC docs (which are annoyingly merged with SQL Server docs): https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetconnectattr-function?view=sql-server-ver15

@am2222 The ODBC timeout may work for the client side, but I don’t know if the ODBC connections in a connection pool will send keep-alives which means the connections to the DB2 server jobs for each connection can still die even though they may stay active on the i. If you’re running a remote server like I believe Liam is, I think the IBMi TCP keep-alive setting (on CHGTCPA) will be very important to set because of WAN/VPN connectivity and potential router timeout issue… I think the default IBMi keepalive may be 120 minutes. I changed mine to 2 and it resolved issues I was having with JDBC and other connections types like RDI so I’m guessing it will help for you as well.

I am getting this issue too and it’s urgent. Even with connectionTimeout as 0, after some idle time it seems to disconnect the connections. I went ahead and built node-odbc with DEBUG mode so we could see the log. You can also see a log of the pool object before the debug output.

0|app      | Pool {
0|app      |   isOpen: true,
0|app      |   freeConnections: [
0|app      |     Connection { odbcConnection: ODBCConnection {} },
0|app      |     Connection { odbcConnection: ODBCConnection {} },
0|app      |     Connection { odbcConnection: ODBCConnection {} },
0|app      |     Connection { odbcConnection: ODBCConnection {} },
0|app      |     Connection { odbcConnection: ODBCConnection {} },
0|app      |     Connection { odbcConnection: ODBCConnection {} },
0|app      |     Connection { odbcConnection: ODBCConnection {} },
0|app      |     Connection { odbcConnection: ODBCConnection {} },
0|app      |     Connection { odbcConnection: ODBCConnection {} },
0|app      |     Connection { odbcConnection: ODBCConnection {} }
0|app      |   ],
0|app      |   connectionString: 'Driver=IBM i Access ODBC Driver;System=xxx;UID=xxx;Password=xxx',
0|app      |   initialSize: 10,
0|app      |   incrementSize: 10,
0|app      |   maxSize: null,
0|app      |   shrink: true,
0|app      |   connectionTimeout: 0,
0|app      |   loginTimeout: 0
0|app      | }
0|app      |
0|app      | [SQLHENV: 0x4bad8c0][SQLHDBC: 0x7fe28c0173e0] ODBCConnection::Query()
0|app      | [SQLHENV: 0x4bad8c0][SQLHDBC: 0x7fe28c0173e0] ODBCConnection::QueryAsyncWorker::Execute(): Running SQL '
0|app      |       select 
0|app      |       --REDACTED--
0|app      |     '
0|app      | [SQLHENV: 0x4bad8c0][SQLHDBC: 0x7fe28c0173e0] ODBCConnection::QueryAsyncWorker::Execute(): Running SQLAllocHandle(HandleType = SQL_HANDLE_STMT, InputHandle = 0x7fe28c0173e0, OutputHandlePtr = 0x4e5a560)

There is a long pause here before this error comes out:

0|app  | [SQLHENV: 0x4bad8c0][SQLHDBC: 0x7fe28c0173e0] ODBCConnection::QueryAsyncWorker::Execute(): Running SQLAllocHandle(HandleType = SQL_HANDLE_STMT, InputHandle = 0x7fe28c0173e0, OutputHandlePtr = 0x4e5a560)
0|app  | [SQLHENV: 0x4bad8c0][SQLHDBC: 0x7fe28c0173e0][SQLHSTMT: 0x7fe2a0021240] ODBCConnection::QueryAsyncWorker::Execute(): SQLPrepare returned -1
0|app  | ODBC::GetSQLError : handleType=3, handle=0x7fe2a0021240
0|app  | ODBC::GetSQLError : called SQLGetDiagField; ret=0, statusRecCount=1
0|app  | ODBC::GetSQLError : calling SQLGetDiagRec; i=0, statusRecCount=1
0|app  | ODBC::GetSQLError : errorMessage=[IBM][System i Access ODBC Driver]Communication link failure. comm rc=8413 - CWBCO1054 - A user-specified time-out occurred while sending or receiving data, errorSQLState=08S01
0|app  | [SQLHENV: 0x4bad8c0][SQLHDBC: 0x7fe28c0173e0] ODBCConnection::Close()