Keeping Connection Open in MySql Transactions

[maplion]

I am building a set of live database JUnit tests for MySQL. I am trying to build the tests to make all inserts and tests within a transaction so that the database can be tested without changing its state.

This works fine when I make my inserts sequentially. But I have a test that sets autocommit to false to initiate a transaction, makes a bunch of inserts sequentially, goes and does some other code, then makes some more inserts. The next time it comes around, even though my pool connectionId is the same, the connection is closed sometime prior to the second insert attempt and the result is I lose my session and the previous inserts can't be seen.

My question is if there is a way to explicitly tell Hikari to leave my connection open until I explicitly tell it to close?

Environment

HikariCP version: x.x.x not sure, will update when I can check
JDK version     : 1.8.0_111
Database        : MySQL
Driver version  : x.x.x not sure, will update when I can check

---

[maplion]

My apologies, the title is misleading: the inserts are all made in the same transaction and try-catch, they are just not all done at the same time.

[brettwooldridge]

@maplion Once a connection is borrowed (getConnection()), HikariCP will not close it, so something else is going on.

I recommend enabling DEBUG level logging for the package com.zaxxer.hikari, using whatever logging framework you are using. HikariCP is not particularly "noisy" at DEBUG, but will log every major driver interaction such as connection acquisition and connection close.

[maplion]

@brettwooldridge Okay, thanks for the information. I'll continue down my rabbit hole with that DEBUG level on and see what I can find.

[brettwooldridge]

@maplion You might find this interesting.

Apparently, the three leading causes of connections lost in the middle of a transaction are:

• Holding a connection without performing SQL for longer than the MySQL wait_timeout.
• Deadlocks/lock timeouts.
• Switching databases/catalogs where the access permissions for the connected user.

[maplion]

@brettwooldridge Thanks again. I can rule out 2 and 3. The first one could be possible if our timeout is low, but it would have to be under 10 seconds or so; another thing I'll investigate tomorrow.

[brettwooldridge]

@maplion If the connection is cutting out in the middle of use, presumably an exception is being thrown, right? What is it?

@maplion If you are getting a "Server has gone away" error, you might also check the max_allowed_packet size.

[maplion]

@brettwooldridge It's not cutting out and no errors. We have a function that is called if the connection is null or closed and when it comes back around for the second batch of inserts, the connection has been closed, so it reinitializes. We're using a Play framework and now that I am digging into some of our base classes, it appears we're using Play to get the dbconnection and not Hikari, which might be the problem.

    private void initConn() throws SQLException {
        if ((_dbCon == null) || (_dbCon.isClosed())) {
            MyLog.debug(MessageFormat.format("MyDb getting connection for: {0}", _dbCon));
            _dbCon = DB.getConnection(_dbName); // Play DB

            _dbCon.setAutoCommit(_autoCommit);
        }
    }

[maplion]

@brettwooldridge I inherited this implementation of Hikari with Play and I'm trying to figure out if there is something that was done in a way that is not correct and is causing the issue (for example, do you think Play should manage the getConnection or Hikari, as mentioned above? I know Play is supposed to have Hikari under the hood for its database management, but just trying to figure this out). Do you think you could share some on the suggested method of how Hikari and Play should be set up to get along? I'm thinking it may just be a really old version of Play using deprecated feature too, potentially. I'll update when I figure something out; but I feel like I'm on the right track.

[maplion]

Yup, I managed to find the issue, which was non-Hikari/Play related at all. As you correctly stated, "something else is going on". The connection was being closed by a read statement, but was being kept open for write statements. I was probably a little tunnel-visioned, but I appreciate your input as it helped me work through things.

[brettwooldridge]

Glad you figured it out. Working with unfamiliar code can be tricky.