JDBC Troubleshooting Guide

See the full chapter index for ordered links to every lesson.

Sooner or later, every JDBC application hits a wall: a cryptic stack trace, a connection that silently dies, or a query that takes minutes instead of milliseconds. This chapter is a field manual for the most common failures you will encounter. It covers driver issues, network problems, authentication errors, encoding mismatches, and performance bottlenecks.

Prerequisites

  • This guide assumes you are working through the JDBC tutorial series
  • A Maven project with mysql-connector-j
  • The jdbc_demo database running on MySQL 8

ClassNotFoundException: com.mysql.cj.jdbc.Driver

This is the most common first-run error. It means the JVM cannot find the MySQL driver class on the classpath.

Verify the Dependency

Open pom.xml and confirm the dependency exists:

xml
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>

Run a fresh compile to force Maven to download the artifact:

bash
mvn clean compile

Check for Version Conflicts

Another library in your project might pull in an old or conflicting driver version. Use the dependency tree to find it:

bash
mvn dependency:tree | grep mysql

If you see both mysql-connector-java (the old artifact name) and mysql-connector-j (the new one), exclude the old one:

xml
<dependency>
    <groupId>some.other.library</groupId>
    <artifactId>library-with-old-driver</artifactId>
    <version>1.0.0</version>
    <exclusions>
        <exclusion>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </exclusion>
    </exclusions>
</dependency>

NoClassDefFoundError After It Worked

If the code ran successfully yesterday and now throws NoClassDefFoundError, the driver JAR was likely removed or corrupted. Run mvn clean install -U to refresh all artifacts from the remote repository.

This error means the JDBC driver could not establish a TCP connection to MySQL. The root cause is almost always outside your Java code.

Checklist

  1. Is MySQL running?

    • Native install: sudo systemctl status mysql (Linux) or brew services list (macOS) or Services panel (Windows).
    • Docker: docker ps should show your jdbc-mysql container with status Up.

  2. Is the port correct?

    • docker port jdbc-mysql should show 3306/tcp -> 0.0.0.0:3306.
    • Verify with telnet 127.0.0.1 3306 or nc -zv 127.0.0.1 3306.

  3. Are you using localhost instead of 127.0.0.1?

    • On some systems, localhost resolves to IPv6 (::1) while MySQL listens on IPv4 (0.0.0.0). Use 127.0.0.1 in your JDBC URL.

  4. Firewall or VPN?

    • Corporate firewalls and VPN split tunneling can block port 3306. If MySQL is on a remote server, confirm the port is open.

  5. MySQL closed an idle connection?

    • MySQL's wait_timeout setting closes idle connections after a period of inactivity. If your application wakes up after a long pause and finds the connection dead, either lower the pool's maxLifetime or raise MySQL's wait_timeout.

Access denied for user

Authentication errors fall into three categories.

Wrong Credentials

Double-check the username, password, and database name in the JDBC URL. The smallest typo here produces exactly the same error as a major configuration problem.

Host Restrictions

MySQL grants privileges to a specific user@host pair. The user created as 'jdbc_user'@'localhost' cannot connect from a Docker container or another machine.

sql
-- For connections from the same machine
CREATE USER 'jdbc_user'@'localhost' IDENTIFIED BY 'jdbcpass';
 
-- For connections from anywhere (useful for Docker and remote clients)
CREATE USER 'jdbc_user'@'%' IDENTIFIED BY 'jdbcpass';
 
-- Grant privileges and apply
GRANT ALL PRIVILEGES ON jdbc_demo.* TO 'jdbc_user'@'localhost';
GRANT ALL PRIVILEGES ON jdbc_demo.* TO 'jdbc_user'@'%';
FLUSH PRIVILEGES;

Authentication Plugin Issues

MySQL 8 defaults to caching_sha2_password. If you see an access-denied error on an otherwise correct setup, add allowPublicKeyRetrieval=true to the JDBC URL:

text
jdbc:mysql://127.0.0.1:3306/jdbc_demo?useSSL=false&allowPublicKeyRetrieval=true

Too many connections

MySQL's default max_connections is 151. Once that limit is reached, new connection requests are rejected.

Diagnosis

Log into MySQL as root and run:

sql
SHOW VARIABLES LIKE 'max_connections';
SHOW PROCESSLIST;

SHOW PROCESSLIST lists every open connection. If you see dozens of Sleep connections from your application's host, you have a connection leak: your code is opening connections and never closing them.

Fixes

  • Audit every getConnection() call to ensure it lives inside a try-with-resources block.
  • Reduce your connection pool's maximumPoolSize. A value of 10–20 is usually plenty for a single application instance.
  • If you genuinely need more than 151 connections, raise the server limit in my.cnf:
ini
[mysqld]
max_connections = 300

Chinese Character Encoding Issues

If Chinese characters display as question marks or gibberish, three layers must agree on UTF-8: the database, the table, and the connection.

Verify the Database Charset

sql
SHOW CREATE DATABASE jdbc_demo;

You should see utf8mb4. If not, recreate the database:

sql
CREATE DATABASE jdbc_demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Verify the Table Charset

sql
SHOW CREATE TABLE users;

Look for utf8mb4 in the output. If the table was created with a different charset, character data may already be corrupted.

Force the Connection Encoding

Add characterEncoding=utf8 to the JDBC URL as a safety net:

text
jdbc:mysql://127.0.0.1:3306/jdbc_demo?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8&allowPublicKeyRetrieval=true

Modern MySQL Connector/J defaults to utf8mb4, but explicit is better than implicit when debugging encoding issues.

Driver Version Conflicts

Multiple versions of the same driver on the classpath can cause ClassNotFoundException, NoSuchMethodError, or silent misbehavior. Always resolve conflicts with the dependency tree.

bash
# Show every occurrence of mysql in the dependency tree
mvn dependency:tree | grep mysql
 
# Or write the full tree to a file for inspection
mvn dependency:tree > tree.txt

If a transitive dependency pulls in an old driver, add an <exclusion> block as shown in the ClassNotFoundException section above.

Performance Problems

Slow database code is rarely the driver's fault. Before blaming JDBC, investigate the query and the schema.

Missing Indexes

If a SELECT with a WHERE clause is slow, check whether MySQL is scanning the entire table:

sql
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

Look for type: ALL in the output. That means a full table scan. Add an index:

sql
CREATE INDEX idx_users_email ON users(email);

Run EXPLAIN again. You should see type: ref or better.

The N+1 Query Problem

This happens when you run one query to fetch N rows, then run an additional query per row inside a loop:

java
List<User> users = userDao.findAll();          // 1 query
for (User u : users) {
    List<Order> orders = orderDao.findByUserId(u.getId()); // N queries
}

For 100 users, that is 101 round-trips. The fix is a single JOIN that fetches users and orders together, or a batched IN (...) query for the orders.

Large Result Sets

Fetching millions of rows into an ArrayList will exhaust heap memory. Solutions:

  • Pagination: Use LIMIT and OFFSET (or keyset pagination for large offsets).
  • Streaming: For MySQL, set the fetch size to Integer.MIN_VALUE to enable row-by-row streaming:
java
Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                                      java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
ResultSet rs = stmt.executeQuery("SELECT * FROM huge_table");
  • Projection: Select only the columns you need instead of SELECT *.

FAQ

How do I reset MySQL to a completely clean state?

If you are using Docker:

bash
docker stop jdbc-mysql
docker rm jdbc-mysql
docker run -d --name jdbc-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=rootpass -e MYSQL_DATABASE=jdbc_demo -e MYSQL_USER=jdbc_user -e MYSQL_PASSWORD=jdbcpass mysql:8

For native installs, drop and recreate the database:

sql
DROP DATABASE IF EXISTS jdbc_demo;
CREATE DATABASE jdbc_demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Should I use localhost or 127.0.0.1 in the JDBC URL?

Use 127.0.0.1. It avoids IPv6 resolution issues and behaves identically across Windows, macOS, and Linux.

Can I connect to a remote MySQL server?

Yes. Replace 127.0.0.1 with the server's IP or hostname. Ensure the server allows remote connections (bind-address in my.cnf), the port is open in the firewall, and the user is created with '%' or the specific client host.

Why does my application work on my machine but fail in production?

The most common culprits are: different connection URLs, missing environment variables or secrets, stricter firewall rules, and different MySQL versions or authentication plugins. Externalize configuration and verify every layer independently.