Hikaricp: Data source rejected establishment of connection
Environment
HikariCP version: 2.5.1
JDK version : 1.7.0_79
Database : MySQL| 5.5.57
Apache Tomcat 7
Eclipse IDE: Luna Services Relase 2 (4.4.2)
Windows: 8.1 version
I am a fresher in java and started using HikariCP database pooling in my Java project. Every 30 seconds an ajax with send request to Servlet with location updates/changes. System works successfully for 37 minutes and after that it throws error ( attached the error) logged in console. I don't know what to do.
Error snippet
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
Below you can see the pooling logs from console below when it was working fine.
Sep 12, 2017 11:51:22 AM com.zaxxer.hikari.HikariDataSource
INFO: MyPool- Starting...
Tue Sep 12 11:51:22 NZST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Sep 12, 2017 11:51:22 AM com.zaxxer.hikari.HikariDataSource
INFO: MyPool - Start completed.
get data connection
Sep 12, 2017 11:51:22 AM com.zaxxer.hikari.HikariDataSource close
INFO: MyPool - Shutdown initiated...
Sep 12, 2017 11:51:22 AM com.zaxxer.hikari.pool.HikariPool shutdown
INFO: MyPool - Close initiated...
Sep 12, 2017 11:51:22 AM com.zaxxer.hikari.pool.HikariPool shutdown
INFO: MyPool - Closed.
Sep 12, 2017 11:51:22 AM com.zaxxer.hikari.HikariDataSource close
INFO: MyPool - Shutdown completed.
Sep 12, 2017 11:51:22 AM com.zaxxer.hikari.HikariDataSource
INFO: MyPool - Starting...
Below is my HikariDatabasePooling class:
public class HikariDatabasePooling implements ServletContextListener{
public static HikariDataSource mDataSource;
public static String POOL_NAME = "MyPool";
public static boolean isDebuggable;
public HikariDatabasePooling(){
HikariConfig sqlConfig = new HikariConfig();
sqlConfig.setJdbcUrl("jdbc:mysql://localhost:3307/mydatabase");
sqlConfig.setUsername("my_username");
sqlConfig.setPassword("my_password");
sqlConfig.setDriverClassName("com.mysql.jdbc.Driver");
sqlConfig.setMaxLifetime(1800000l);
sqlConfig.setMinimumIdle(1);
sqlConfig.setMaximumPoolSize(2);
sqlConfig.setPoolName(POOL_NAME);
sqlConfig.setAutoCommit(true);
sqlConfig.setConnectionTimeout(34000);
sqlConfig.setLeakDetectionThreshold(30000);
sqlConfig.addDataSourceProperty("cachePrepStmts", "true");
sqlConfig.addDataSourceProperty("prepStmtCacheSize", "250");
sqlConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
mDataSource = new HikariDataSource(sqlConfig);
Connection connection = null;
}
@Override
public void contextDestroyed(ServletContextEvent servletContextEvent) {
System.out.println("Shutting down!");
mDataSource.close();
}
public static Connection getConnection() throws SQLException{
System.out.println("get data connection");
return mDataSource.getConnection();
}
@Override
public void contextInitialized(ServletContextEvent arg0) {
System.out.println("CP has been initilizing");
String parm = arg0.getServletContext().getInitParameter("debugging");
isDebuggable = Boolean.valueOf(parm);
}
}
scrumvisualize
All 5 comments
Can you show the code where you are using the Connection? It sounds like you might not be closing the connections after you execute the statements.
billoneil
on 12 Sep 2017
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html");
String userLatitude = request.getParameter("currentLat");
String userLongitude = request.getParameter("currentLong");
System.out.println("LAT="+request.getParameter("currentLat"));
System.out.println("LON="+request.getParameter("currentLong"));
if(userLatitude!=null && userLongitude!=null){
double userLati = Double.parseDouble(userLatitude);
double userLong = Double.parseDouble(userLongitude);
DecimalFormat dFormat = new DecimalFormat("#.########");
userLati= Double.valueOf(dFormat.format(userLati));
userLong= Double.valueOf(dFormat.format(userLong));
String userTitle = request.getParameter("title");
String email = request.getParameter("email");
String userQuotes = request.getParameter("quote");
System.out.print(userLati +", " + userLong + ", " + userTitle + ", " +email + ", " +userQuotes);
PlayerCoordinates myCoordinates = new PlayerCoordinates();
myCoordinates.setLatitude(userLati);
myCoordinates.setLongitude(userLong);
myCoordinates.setTitle(userTitle);
String jsonResponse1 = new Gson().toJson(myCoordinates);
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");
response.getWriter().write(jsonResponse1.toString());
PrintWriter out=response.getWriter();
out.write(jsonResponse1.toString());
out.println(jsonResponse1);
response.setContentType("text/html");
PreparedStatement preparedStatement4 = null;
PreparedStatement preparedStatement5 = null;
PreparedStatement preparedStatement7 = null;
Connection connection = null;
ResultSet rs2 = null;
ResultSet rs3 = null;
try {
HikariDatabasePooling hikariDatabasePooling = new HikariDatabasePooling();
connection = hikariDatabasePooling.getConnection();
preparedStatement4 = connection
.prepareStatement("SELECT user_id FROM mycoolmap.registeruser where email = ?");
preparedStatement4.setString(1, email);
rs2 = preparedStatement4.executeQuery();
long userId = 0L;
while (rs2.next()) {
userId = rs2.getLong("user_id");
}
if (userId > 0) {
preparedStatement5 = connection
.prepareStatement("SELECT count(user_id) as count FROM mycoolmap.playerlocation where user_id = ?");
preparedStatement5.setLong(1, userId);
rs3 = preparedStatement5.executeQuery();
int count = 0;
while (rs3.next()) {
count= rs3.getInt("count");
}
if (count > 0) { /** count > 0 means the we already have a record there.*/
preparedStatement7 = connection
.prepareStatement("UPDATE mycoolmap.playerlocation SET name=?, latitude=?, longitude=?, soccerquotes=? WHERE user_id=?");
preparedStatement7.setString(1, userTitle);
preparedStatement7.setDouble(2, userLati);
preparedStatement7.setDouble(3, userLong);
preparedStatement7.setString(4, userQuotes);
preparedStatement7.setLong(5, userId);
preparedStatement7.executeUpdate();
} else {
preparedStatement7 = connection
.prepareStatement("insert into mycoolmap.playerlocation values(?,?,?,?,?)");
preparedStatement7.setLong(1, userId);
preparedStatement7.setString(2, userTitle);
preparedStatement7.setDouble(3, userLati);
preparedStatement7.setDouble(4, userLong);
preparedStatement7.setString(5, userQuotes);
preparedStatement7.executeUpdate();
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (rs2 != null) {
rs2.close();
}
if (rs3 != null) {
rs3.close();
}
if (preparedStatement4 != null) {
try {
preparedStatement4.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement5 != null) {
try {
preparedStatement5.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement7 != null) {
try {
preparedStatement7.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
HikariDatabasePooling.mDataSource.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
It's a little difficult to read but you shouldn't be making a new connection pool on every request. You have a static variable for the Connection pool but overwrite it every request when you call the constructor which is also not thread safe. You need to make sure to only create a single connection pool.
billoneil
on 12 Sep 2017
I am calling the HikariDatabasePooling like below in try {},
HikariDatabasePooling hikariDatabasePooling = new HikariDatabasePooling();
connection = hikariDatabasePooling.getConnection();
If this is not the way, could you please show me on how to call this ?
scrumvisualize
on 12 Sep 2017
You need to make a singleton of the HikariDatabasePooling class. You can either initialize it statically once, use dependency injection, or some other form. But you want to be reusing the same pool on every request not creating a new one. You should also only shutdown the pool when you kill the webserver not on every request.
billoneil
on 12 Sep 2017
Related issues
adriandegenhardt
路
4Comments
hsnamed
路
3Comments
sooyoung32
路
5Comments
mohsen
路
6Comments
sumeetpri
路
3Comments
Most helpful comment
You need to make a singleton of the
HikariDatabasePoolingclass. You can either initialize it statically once, use dependency injection, or some other form. But you want to be reusing the same pool on every request not creating a new one. You should also only shutdown the pool when you kill the webserver not on every request.