MySQL – Connections, URLs and Pooling

Typing into the mysql client is one connection. An application is a different problem: it opens many, holds them open, shares them between threads, and has to survive the database restarting. This lesson covers the connection URL, pooling, and the timeouts that turn into failures overnight rather than during your tests.

Reading a JDBC URL

This is the demo application's real one:

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/pizza?useSSL=false&allowPublicKeyRetrieval=true&connectionTimeZone=LOCAL&preserveInstants=false
spring.datasource.username=root
spring.datasource.password=

Piece by piece: jdbc:mysql:// selects the driver, then host, port, and /pizza — the default database for the session, which is what lets queries say product instead of pizza.product. Everything after ? is driver configuration.

The time zone parameter is not cosmetic

This is the one to read twice, because it corrupts data silently. The comment in the demo app's configuration records what happened:

# connectionTimeZone=LOCAL + preserveInstants=false stop the MySQL driver converting
# DATETIME values between zones. With LocalDateTime (which has no zone) any conversion
# is silent corruption: serverTimezone=UTC made a row stored as 2026-01-01 00:00 read
# back as 2025-12-31 17:00.

A DATETIME column stores wall-clock time with no zone attached, and Java's LocalDateTime has no zone either. That is a consistent pairing — until the driver is told a zone and starts "helpfully" converting between it and the JVM's. Seven hours appear or disappear from every timestamp, no error is raised, and the values still look like plausible dates.

The rule: if you store zone-less DATETIME and map it to LocalDateTime, tell the driver not to convert. If you genuinely need instants, use TIMESTAMP with Instant/OffsetDateTime and let the conversion happen deliberately. What you must not do is mix the two. See date and time types.

The server here reports @@time_zone as SYSTEM and @@system_time_zone as UTC. Check both on any server you did not configure yourself:

SELECT @@time_zone, @@system_time_zone;

Connection pooling

Opening a MySQL connection means a TCP handshake plus authentication — a few milliseconds, which is enormous next to a query that takes under one. A pool opens some connections up front and lends them out. Spring Boot ships HikariCP and uses it by default; you configure it rather than install it:

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.max-lifetime=1740000
spring.datasource.hikari.idle-timeout=600000

Bigger is not better. The pool size is a limit on how much work the database does at once, and a database has a fixed number of cores and disks. Past that point, more concurrent queries means each one is slower and none finishes sooner — you have moved the queue from your application into the database, where it is harder to see and it holds locks while it waits.

Start around (2 × cores) + effective spindle count — often 10 to 20, not 200 — then measure. And count every instance: ten application containers with a pool of 20 each is 200 connections arriving at one server.

Setting minimum-idle equal to maximum-pool-size gives a fixed-size pool. That is usually what you want in a server: it avoids a latency spike when traffic arrives and the pool has to grow.

The timeouts, and which failure each one causes

SELECT @@max_connections, @@wait_timeout, @@connect_timeout;

wait_timeout is the one that produces the classic overnight bug. The server closes an idle connection; the pool does not notice, because nothing told it; the first request next morning borrows that dead connection and fails with Communications link failure. Then it works fine all day, because everything is warm.

The fix is on the pool, not the server. Keep the pool's max-lifetime comfortably below the server's wait_timeout so connections are retired before the server kills them — 1,740,000 ms (29 minutes) above is deliberately under the common 30-minute idle timeout on managed databases and load balancers. Raising wait_timeout instead just moves the failure to whichever middlebox has the shortest idle timeout.

Reserve a connection for yourself

MySQL keeps one extra slot beyond max_connections for an account with the CONNECTION_ADMIN privilege (or the older SUPER). That is what makes it possible to log in and run SHOW PROCESSLIST when the application has consumed everything else. It is a good reason for your admin account to be a different account from the one the application uses — see users and privileges and running queries in production.

Where connections actually leak

A pool only helps if connections come back. In modern Java the pattern that guarantees it is try-with-resources:

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(
         "SELECT name FROM product WHERE type = ?")) {
    ps.setString(1, "PIZZA");
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }
}

Every one of those is closed even if the query throws. Miss one — an early return, an exception between getConnection() and the try — and that connection is gone until max-lifetime reclaims it. A pool that exhausts itself slowly under load, and recovers after a restart, is almost always a leak rather than a sizing problem.

The ? placeholder matters for a second reason: a PreparedStatement sends the value separately from the SQL, so it cannot be read as SQL. String-concatenating user input into a query is how SQL injection happens, and no amount of escaping is as reliable as not doing it.

What to remember

• useSSL=false belongs on your laptop and nowhere else.
• Zone-less DATETIME plus a driver that converts zones is silent data corruption.
• Size the pool to what the database can do concurrently, and count every instance.
• Keep pool max-lifetime under the server's wait_timeout, or you get a failure every morning.
• Close connections with try-with-resources; use placeholders, never concatenation.