How to set up Tomcat for one Database Connection per Request

Question

I have a Sparkjava app which I have deployed on a Tomcat server. It uses SQL2O to interface with the MySQL-database. After some time I start to have trouble connecting to the database. I've tried connecting directly from SQL2O, connecting through HikariCP and connecting through JNDI. They all work for about a day, before I start getting Communications link failure. This app gets hit a handful of times a day at best, so performance is a complete non issue. I want to configure the app to use one database connection per request. How do I go about that?

The app doesn't come online again afterwards until I redeploy it (overwrite ROOT.war again). Restarting tomcat or the entire server does nothing.

Currently every request creates a new Sql2o object and executes the query using withConnection. I'd be highly surprised if I was leaking any connections.

Here's some example code (simplified).

public class UserRepositry {
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    protected Sql2o sql2o = new Sql2o("jdbc:mysql://mysql.server.name/dbname?serverTimezone=UTC", "username", "password");

    public List<Users> getUsers() {
        return sql2o.withConnection((c, o) -> {
            return c.createQuery(
                    "SELECT\n" +
                            "  id,\n" +
                            "  name\n" +
                            "FROM users"
            )
                    .executeAndFetch(User.class);
        });

    }
}


public class Main {
    public static void main(String[] args) {
        val gson = new Gson();

        port(8080);

        get("/users", (req, res) -> {
            return new UserRepository().getUsers();
        }, gson::toJson);
    }
}

It's a more generic question. All the aforementioned methods of connecting to the database seem to do some sort of pooling, or connection re-use. I want to find a way, that doesn't do that. — Philippe, Feb 19 '18 at 07:07

Olaf Kock · Accepted Answer · 2018-02-22T19:00:43.000

If you rely on Tomcat to provide the connection to you: It's coming from a pool. Just go with plain old JDBC and open that connection yourself (and make sure to close it as well) if you don't like that.

So much for the answer to your question, to the letter. Now for the spirit: There's nothing wrong with connections coming from a pool. In all cases, it's your responsibility to handle it properly: Get access to a connection and free it up (close) when you're done with it. It doesn't make a difference if the connection is coming from a pool or has been created manually.

As you say performance is not an issue: Note that the creation of a connection may take some time, so even if the computer is largely idle, creating a new connection per request may have a notable effect on the performance. Your server won't overheat, but it might add a second or two to the request turnaround time.

Check configurations for your pool - e.g. validationQuery (to detect communication failures) or limits for use per connection. And make sure that you don't run into those issues because of bugs in your code. You'll need to handle communication errors anyways. And, again, that handling doesn't differ whether you use pools or not.

Edit: And finally: Are you extra extra sure that there indeed is no communication link failure? Like: Database or router unplugged every night to connect the vacuum cleaner? (no pun intended), Firewall dropping/resetting connections etc?

I added some information. I don't know why I still get this error if I'm opening and closing a connection on every request. — Philippe, Feb 22 '18 at 15:33
I mean they might shut down the database, why should that mater? The connections only last for less than a second and are closed afterwards. The app doesn't come online again afterwards until I redeploy it. Restarting tomcat or the entire server does nothing. — Philippe, Feb 23 '18 at 11:55
Well, you don't show code - I can't assume anything with regards to the implementation that you do. Plus, a pool might have a connection open and hand it out (if you didn't configure a validationQuery) even when the connection broke down. One more idea is a long-idle connection that gets killed by a router/firewall. In other words: I can only give you more or less educated guesses - you'll have to do the actual investigation on your own. Sorry if it's not enough, but that's about all I (or we) can do here with the amount of information we have. — Olaf Kock, Feb 23 '18 at 14:48
It ended up being something along the lines of `Database or router unplugged every night to connect the vacuum cleaner` — Philippe, May 22 '18 at 14:01

How to set up Tomcat for one Database Connection per Request

1 Answers1