How to recover closed connection after XA_RBTIMEOUT?

[magicxie]

My program got XA_RBTIMEOUT error:

**failed { Error: XA_RBTIMEOUT: Transaction branch was rolled back: took too long**
2017-05-29 06:02:34|109545|ERROR|    at Packet.asError (/userver/bin/src/node_modules/mysql2/lib/packets/packet.js:667:13)
2017-05-29 06:02:34|109545|ERROR|    at Query.Command.execute (/userver/bin/src/node_modules/mysql2/lib/commands/command.js:29:22)
2017-05-29 06:02:34|109545|ERROR|    at Connection.handlePacket (/userver/bin/src/node_modules/mysql2/lib/connection.js:417:28)
2017-05-29 06:02:34|109545|ERROR|    at PacketParser.onPacket (/userver/bin/src/node_modules/mysql2/lib/connection.js:93:16)
2017-05-29 06:02:34|109545|ERROR|    at PacketParser.executeStart (/userver/bin/src/node_modules/mysql2/lib/packet_parser.js:73:14)
2017-05-29 06:02:34|109545|ERROR|    at Socket.<anonymous> (/userver/bin/src/node_modules/mysql2/lib/connection.js:101:29)
2017-05-29 06:02:34|109545|ERROR|    at emitOne (events.js:96:13)
2017-05-29 06:02:34|109545|ERROR|    at Socket.emit (events.js:191:7)
2017-05-29 06:02:34|109545|ERROR|    at readableAddChunk (_stream_readable.js:178:18)
2017-05-29 06:02:34|109545|ERROR|    at Socket.Readable.push (_stream_readable.js:136:10)
2017-05-29 06:02:34|109545|ERROR|    at TCP.onread (net.js:561:20) **code: 'ER_XA_RBTIMEOUT', errno: 1613, sqlState: '#XA106' }**
2017-05-29 06:02:34|109545|ERROR|Sun, 28 May 2017 22:02:34 GMT knex:client releasing connection to pool: __knexUid82
2017-05-29 06:02:34|109545|ERROR|Sun, 28 May 2017 22:02:34 GMT knex:pool INFO pool mysql:mysql2:client0 - dispense() clients=6 available=1
2017-05-29 06:02:34|109545|ERROR|Sun, 28 May 2017 22:02:34 GMT knex:client acquired connection from pool: __knexUid82
2017-05-29 06:02:34|109545|ERROR|Sun, 28 May 2017 22:02:34 GMT knex:query select my_sql_query
2017-05-29 06:02:34|109545|ERROR|Sun, 28 May 2017 22:02:34 GMT knex:bindings [ '183', 'xxxx', '*', 2000 ]

After that moment, all sql query on this process failed because Can't add new command when connection is in closed state

2017-05-29 06:02:34|109545|ERROR| **{ Error: Can't add new command when connection is in closed state**
2017-05-29 06:02:34|109545|ERROR|    at Connection._addCommandClosedState (/userver/bin/src/node_modules/mysql2/lib/connection.js:145:13)
2017-05-29 06:02:34|109545|ERROR|    at Connection.query (/userver/bin/src/node_modules/mysql2/lib/connection.js:515:15)
2017-05-29 06:02:34|109545|ERROR|    at /userver/bin/src/node_modules/knex/lib/dialects/mysql/index.js:152:18
2017-05-29 06:02:34|109545|ERROR|    at Promise._execute (/userver/bin/src/node_modules/bluebird/js/release/debuggability.js:300:9)
2017-05-29 06:02:34|109545|ERROR|    at Promise._resolveFromExecutor (/userver/bin/src/node_modules/bluebird/js/release/promise.js:483:18)
2017-05-29 06:02:34|109545|ERROR|    at new Promise (/userver/bin/src/node_modules/bluebird/js/release/promise.js:79:10)
2017-05-29 06:02:34|109545|ERROR|    at Client_MySQL2._query (/userver/bin/src/node_modules/knex/lib/dialects/mysql/index.js:146:12)
2017-05-29 06:02:34|109545|ERROR|    at Client_MySQL2.query (/userver/bin/src/node_modules/knex/lib/client.js:197:17)
2017-05-29 06:02:34|109545|ERROR|    at Runner.<anonymous> (/userver/bin/src/node_modules/knex/lib/runner.js:146:36)
2017-05-29 06:02:34|109545|ERROR|    at Runner.tryCatcher (/userver/bin/src/node_modules/bluebird/js/release/util.js:16:23)
2017-05-29 06:02:34|109545|ERROR|    at Runner.query (/userver/bin/src/node_modules/bluebird/js/release/method.js:15:34)
2017-05-29 06:02:34|109545|ERROR|    at /userver/bin/src/node_modules/knex/lib/runner.js:65:21
2017-05-29 06:02:34|109545|ERROR|    at tryCatcher (/userver/bin/src/node_modules/bluebird/js/release/util.js:16:23)
2017-05-29 06:02:34|109545|ERROR|    at /userver/bin/src/node_modules/bluebird/js/release/using.js:185:26
2017-05-29 06:02:34|109545|ERROR|    at tryCatcher (/userver/bin/src/node_modules/bluebird/js/release/util.js:16:23)
2017-05-29 06:02:34|109545|ERROR|    at Promise._settlePromiseFromHandler (/userver/bin/src/node_modules/bluebird/js/release/promise.js:512:31)
2017-05-29 06:02:34|109545|ERROR|    at Promise._settlePromise (/userver/bin/src/node_modules/bluebird/js/release/promise.js:569:18)
2017-05-29 06:02:34|109545|ERROR|    at Promise._settlePromise0 (/userver/bin/src/node_modules/bluebird/js/release/promise.js:614:10)
2017-05-29 06:02:34|109545|ERROR|    at Promise._settlePromises (/userver/bin/src/node_modules/bluebird/js/release/promise.js:693:18)
2017-05-29 06:02:34|109545|ERROR|    at Promise._fulfill (/userver/bin/src/node_modules/bluebird/js/release/promise.js:638:18)
2017-05-29 06:02:34|109545|ERROR|    at PromiseArray._resolve (/userver/bin/src/node_modules/bluebird/js/release/promise_array.js:126:19)
2017-05-29 06:02:34|109545|ERROR|    at PromiseArray._promiseFulfilled (/userver/bin/src/node_modules/bluebird/js/release/promise_array.js:144:14)
2017-05-29 06:02:34|109545|ERROR|    at Promise._settlePromise (/userver/bin/src/node_modules/bluebird/js/release/promise.js:574:26)
2017-05-29 06:02:34|109545|ERROR|    at Promise._settlePromise0 (/userver/bin/src/node_modules/bluebird/js/release/promise.js:614:10)
2017-05-29 06:02:34|109545|ERROR|    at Promise._settlePromises (/userver/bin/src/node_modules/bluebird/js/release/promise.js:693:18)
2017-05-29 06:02:34|109545|ERROR|    at Async._drainQueue (/userver/bin/src/node_modules/bluebird/js/release/async.js:133:16)
2017-05-29 06:02:34|109545|ERROR|    at Async._drainQueues (/userver/bin/src/node_modules/bluebird/js/release/async.js:143:10)
2017-05-29 06:02:34|109545|ERROR|    at Immediate.Async.drainQueues (/userver/bin/src/node_modules/bluebird/js/release/async.js:17:14) fatal: true }
2017-05-29 06:02:34|109545|ERROR|Sun, 28 May 2017 22:02:34 GMT knex:client releasing connection to pool: __knexUid82
2017-05-29 06:02:34|109545|ERROR|Sun, 28 May 2017 22:02:34 GMT knex:pool INFO pool mysql:mysql2:client0 - dispense() clients=2 available=1

I think mysql2 may be a way to recover connection from XA timeout error, or how can I reconnect to mysql/reopen the connection programmatically.

[sidorares]

Preferred way is to use connections pool.

It's not possible to re-connect existing connection instance, you must discard it and create new one (if you are using pool this happens automatically)

Note that some errors are not 'critical' and you can just continue to issue commands to same connection after error in that case. You can check this using error.isFatal flag. You you believe you have example where this flag is set incorrectly please let us know

[magicxie]

thanks @sidorares in fact it is acquired by a connection pool, I should check the pool lib.

[sidorares]

@magicxie is there any way to reduce to some smaller case I can reproduce?

Usually you get Can't add new command when connection is in closed state error when you close the pool and after that some other code tries to get connection from it

[magicxie]

My program uses Knex, set up connection pool internally by generic-pool, connect to a cloud mysql(not sure it is maria or mysql) Now I'm trying reproduce it on my dev machine, once it is done I will send the case to you.

[novemberborn]

I think the problem is that mysql2 transitions connections to a broken state, and the dialect in Knex does not recognize this. As such this is a problem with Knex, see https://github.com/tgriesser/knex/pull/2175 which seems to fix it for me.

[yunfan]

i had met this problem too, since its still open state, were there any recommendation with less code changing?

[noodleswww]

i had met this problem too,, is there a solution to this problem?