I'm building a web project using Tomcat, where I have to fetch some dates from a MySQL database, for which I'm using HikariCP. Unfortunately, after too many fetches I get a SQLTransientConnectionException.
13:34:33.915 [http-nio-8081-exec-14] ERROR de.intranet.data.db.DBAccess - SQL error in getLatestModification for category hausnachrichten: Cannot invoke "java.sql.Connection.prepareStatement(String)" because the return value of "de.intranet.data.db.DBAccess.getConnection()" is null
13:34:33.915 [http-nio-8081-exec-14] DEBUG de.intranet.data.db.DBAccess - Existing getLatestModification for category hausnachrichten: null
13:34:38.957 [http-nio-8081-exec-13] ERROR de.intranet.data.db.DBConnection - Failed to retrieve a connection from the connection pool.
java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 30010ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696) ~[HikariCP-5.0.1.jar:?]
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:181) ~[HikariCP-5.0.1.jar:?]
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:146) ~[HikariCP-5.0.1.jar:?]
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100) ~[HikariCP-5.0.1.jar:?]
at de.intranet.data.db.DBConnection.getConnection(DBConnection.java:131) [classes/:?]
at de.intranet.data.db.DBAccess.getLatestModification(DBAccess.java:735) [classes/:?]
at de.intranet.http.ServletDashboardPrepopulate.doPost(ServletDashboardPrepopulate.java:80) [classes/:?]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:590) [servlet-api.jar:6.0]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) [servlet-api.jar:6.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:205) [catalina.jar:10.1.10]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) [catalina.jar:10.1.10]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) [tomcat-websocket.jar:10.1.10]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174) [catalina.jar:10.1.10]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) [catalina.jar:10.1.10]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:166) [catalina.jar:10.1.10]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) [catalina.jar:10.1.10]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482) [catalina.jar:10.1.10]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115) [catalina.jar:10.1.10]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) [catalina.jar:10.1.10]
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:676) [catalina.jar:10.1.10]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [catalina.jar:10.1.10]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:341) [catalina.jar:10.1.10]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:391) [tomcat-coyote.jar:10.1.10]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) [tomcat-coyote.jar:10.1.10]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:894) [tomcat-coyote.jar:10.1.10]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1741) [tomcat-coyote.jar:10.1.10]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) [tomcat-coyote.jar:10.1.10]
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) [tomcat-util.jar:10.1.10]
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) [tomcat-util.jar:10.1.10]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:10.1.10]
at java.base/java.lang.Thread.run(Thread.java:1589) [?:?]
To me, it seems like the connections get closed properly, as far as I'm using try-with-resources, when suddenly the SQLException comes:
13:33:28.868 [http-nio-8081-exec-11] DEBUG de.intranet.http.ServletDashboardPrepopulate - Connection Pool Stats - Active: 10, Idle: null
13:33:33.903 [http-nio-8081-exec-14] DEBUG de.intranet.http.ServletDashboardPrepopulate - Connection Pool Stats - Active: 10, Idle: null
13:33:38.934 [http-nio-8081-exec-13] DEBUG de.intranet.http.ServletDashboardPrepopulate - Connection Pool Stats - Active: 10, Idle: null
13:33:43.960 [http-nio-8081-exec-2] DEBUG de.intranet.http.ServletDashboardPrepopulate - Connection Pool Stats - Active: 10, Idle: null
13:33:48.995 [http-nio-8081-exec-16] DEBUG de.intranet.http.ServletDashboardPrepopulate - Connection Pool Stats - Active: 10, Idle: null
13:33:53.856 [http-nio-8081-exec-9] ERROR de.intranet.data.db.DBConnection - Failed to retrieve a connection from the connection pool.
java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 30001ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696) ~[HikariCP-5.0.1.jar:?]
(rest is same as above)
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try {
// Initialize a map to store category names and their corresponding latest
// modification dates
Map<String, String> dateMap = new HashMap<>();
// Retrieve the ServletContext and DBAccess instance from the request
ServletContext servletContext = request.getServletContext();
DBAccess dbAccess = (DBAccess) servletContext.getAttribute("dbConnection");
// Define a date format for formatting the modification dates in the response
SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
try {
Map<String, Integer> poolStats = dbAccess.getConnectionPoolStats();
logger.debug("Connection Pool Stats - Active: {}, Idle: {}", poolStats.get("active"),
poolStats.get("idle"));
} catch (Exception e) {
logger.error("Error retrieving connection pool stats: {}", e.getMessage());
}
// Iterate through each file category and retrieve the latest modification date
for (DBFilesTableCategory category : DBFilesTableCategory.values()) {
Date lastDate = dbAccess.getLatestModification(category);
// If a modification date is available, add it to the dateMap
if (lastDate != null) {
dateMap.put(category.name(), sdf.format(lastDate));
} else {
dateMap.put(category.name(), "Keine Dateien vorhanden");
}
}
// Convert the dateMap to a JSON response using Gson
Gson gson = new Gson();
String jsonResponse = gson.toJson(dateMap);
// Write the JSON response to the HttpServletResponse
response.getWriter().write(jsonResponse);
} catch (Exception e) {
// Log any exceptions that occur during the processing of the request
logger.error("Error while prepopulating dashboard: {}", e.getMessage());
// Send an internal server error response to the client
response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
}
}
public class DBAccess extends DBConnection {
public Date getLatestModification(DBFilesTableCategory category) {
// Variable to store the latest modification date
Date latestDate = null;
// Convert the category enum to a string
String categoryString = category.toString();
// SQL query to retrieve the maximum upload date for files in the specified
// category
String query = "SELECT MAX(f_uploadDate) AS latestDate FROM files WHERE f_category = ?";
try (Connection connection = this.getConnection();
PreparedStatement preparedStatement = this.getConnection().prepareStatement(query)) {
// Set the category as a parameter in the prepared statement
preparedStatement.setString(1, categoryString);
logger.debug("Executing query: {}", preparedStatement);
// Execute the query and retrieve the result set
try (ResultSet resultSet = preparedStatement.executeQuery()) {
// If a row is returned, retrieve the latest date
if (resultSet.next()) {
latestDate = resultSet.getDate("latestDate");
}
} catch (SQLException e) {
// Handle any SQL exceptions by printing the stack trace
logger.error("SQL error in getLatestModification for category {}: {}", categoryString, e.getMessage());
}
} catch (Exception e) {
// Handle any SQL exceptions by printing the stack trace
logger.error("SQL error in getLatestModification for category {}: {}", categoryString, e.getMessage());
}
logger.debug("Existing getLatestModification for category {}: {}", categoryString, latestDate);
// Return the latest modification date, or null if no files are found
return latestDate;
}
}
Maybe there is a problem in how I start the connections in the initialization of the instance?
public class DBConnection {
// The HikariConfig object for configuring the connection pool
private HikariConfig config;
private HikariDataSource dataSource;
// Static initializer for loading the MySQL JDBC driver
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException("MySQL JDBC driver not found", e);
}
}
public DBConnection(String url, String username, String password) {
// Create a new DBAccess instance with the specified database connection details
config = new HikariConfig();
// Set the URL, username, and password for the database connection
config.setJdbcUrl(url);
config.setUsername(username);
config.setPassword(password);
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
dataSource = new HikariDataSource(config);
}
}
@WebListener
public class WebServletContextListener implements ServletContextListener {
// Attribute name for the database connection
private static final String DB_CONNECTION_ATTR = "dbConnection";
@Override
public void contextInitialized(ServletContextEvent event) {
// Pass the servlet context to ConfigUtil
ConfigUtil.setServletContext(event.getServletContext());
// Create a new DBAccess instance with the specified database connection details
DBAccess dbConnection = new DBAccess(ConfigUtil.getDatabaseUrl(), ConfigUtil.getDatabaseUsername(),
ConfigUtil.getDatabasePassword());
// Set the DBAccess instance as a servlet context attribute
event.getServletContext().setAttribute(DB_CONNECTION_ATTR, dbConnection);
}
The logs of the database are completely empty and I don't know what I can do. Maybe some of you have an idea on how to isolate the error or even had similar problems before and have a solution.
You are leaking connections here:
try (Connection connection = this.getConnection();
PreparedStatement preparedStatement = this.getConnection().prepareStatement(query)) {
This fragment opens two connections but only one is declared as resource that is to be closed. The second resource is the PreparedStatement that is created on the second connection.
Try-with-resources can only call close() on variables that you declare in the resource specification block. When you write this.getConnection().prepareStatement(query) you get a new connection but don't assign it to a variable. Try-with-resources therefore cannot close it.
The solution is to open only one connection and to use that connection to call prepareStatement():
try (Connection connection = this.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(query)) {