java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.

[ItsVinnyX]

Environment

HikariCP version: 3.0.0
JDK version     : 1.8.0_111
Database        : MySQL
Driver version  : Not sure.

^{⚠️ Please verify that your issue still occurs on the latest version of HikariCP before reporting.}

---

Have you searched the CLOSED issues already? How about checking stackoverflow?

Yes.

I have two errors which are the same but in different classes.
https://hastebin.com/banobifipe.md

LINE 744:
ps2 = getHikari().getConnection().prepareStatement( "UPDATE PlayersSET1v1Rating=?, 2v2Rating=?, FFARating=?, FFAKills=?, FFADeaths=?, Coins=?, Spins=?, Skeleton=?, Creeper=?, Zombie=?, Enderman=?, Herobrine=?, Dreadlord=?, Arcanist=?, Shaman=?, Golem=?, Pigman=?, Blaze=?, Spider=?, Block=?, VanityArmor=?, Trail=?, PlayerName=? WHERE UUID=?");

LINE 153:
PreparedStatement sql2 = Corrupted.getInstance().getHikari().getConnection().prepareStatement("SELECT * FROM Players");

My configuration:
hikari = new HikariDataSource(); hikari.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource"); hikari.setMaximumPoolSize(10); hikari.addDataSourceProperty("serverName", "localhost"); hikari.addDataSourceProperty("port", "3306"); hikari.addDataSourceProperty("databaseName", "DB"); hikari.addDataSourceProperty("user", "username"); hikari.addDataSourceProperty("password", "mypass");

If this helps I have another program using the same database with HikariCP and works perfectly fine.

[brettwooldridge]

Enable the leakDetectionThreshold and watch the log for messages.

[ItsVinnyX]

Okay I’ll enable that. I changed my code around completely and now I seem to only be getting primary key duplicate error.

[ItsVinnyX]

What should I use for that leakDetectionThreshold?

Also here's the duplicated entry error:
https://hastebin.com/ifutocofor.md

[brettwooldridge]

Two things...

One, try 30 seconds (30000ms) for the leakDetectionThreshold.

Two, duplicate key errors have nothing to do with the pool, but represent an application bug. If it was working on another pool, it is likely due to a “lucky” side-effect, such as commit-on-close behavior. You’re on your own chasing that one down.

[ItsVinnyX]

Okay I'll try that out

[ItsVinnyX]

Fixed it. Thanks.

[ItsVinnyX]

Okay looks like the leakDetectionThreshold picked this

https://hastebin.com/gasiyifota.vbs

I also provided my code and line number

[ItsVinnyX]

Not sure what caused that but it hasn't happened since.

[brettwooldridge]

@ItzVinex Looking at the code, the following pattern will leak a connection:

try {
   conn = hikari.getConnection();  <-- Possible leak.  If an exception is thrown, the Connection is never closed.
   sql = conn.prepareStatement(...);
   ...

   conn = hikari.getConnection();   <-- Leak.  Previous Connection referenced by conn is overwritten and never closed.
   sql = conn.prepareStatement(...);
} catch (SQLException e) {
   ...
} finally {
}

First, you do not need two Connections here. Second, use Java's try-with-resources to handle closing Connections and Statements.

Better:

try (Connection conn = hikari.getConnection();
      PreparedStatement sql1 = conn.prepareStatement(...);
      PreparedStatement sql2 = conn.prepareStatement(...)) {

   sql1.setXXX(...);
   ...
   sql1.executeUpdate();

   // No need to close 'conn', 'sql1' or 'sql2' explicitly.  The try guarantees they will be closed.
}
catch (SQLException e) {
   ...
}

[ItsVinnyX]

`public void newDatabaseEntry(Player p) {
final String uuid = p.getUniqueId().toString();
final String name = p.getName();
Bukkit.getScheduler().scheduleAsyncDelayedTask(plugin, new Runnable() {

		@Override
		public void run() {
			try (Connection conn = hikari.getConnection();
				      PreparedStatement sql1 = conn.prepareStatement("INSERT INTO `Players` values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?");
				      PreparedStatement sql2 = conn.prepareStatement("INSERT INTO `KitCustomizer` values(?,?,?,?,?,?,?,?,?,?,?,?,?)")) {
				sql1.setString(1, uuid);
				sql1.setInt(2, 1000);
				sql1.setInt(3, 1000);
				sql1.setInt(4, 1000);
				sql1.setInt(5, 0);
				sql1.setInt(6, 0);
				sql1.setInt(7, 0);
				sql1.setInt(8, 1);
				sql1.setString(9, "1|0|0");
				sql1.setString(10, "1|0|0");
				sql1.setString(11, "0|0|0");
				sql1.setString(12, "0|0|0");
				sql1.setString(13, "0|0|0");
				sql1.setString(14, "0|0|0");
				sql1.setString(15, "0|0|0");
				sql1.setString(16, "0|0|0");
				sql1.setString(17, "0|0|0");
				sql1.setString(18, "0|0|0");
				sql1.setString(19, "0|0|0");
				sql1.setString(20, "0|0|0");
				sql1.setString(21, "1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|WOOD");
				sql1.setString(22, "1|0|0|0|0|NONE");
				sql1.setString(23, "1|0|0|0|0|0|0|0|0|0|NONE");
				sql1.setString(24, name);
				sql1.execute();
				sql1.close();

				sql2.setString(1, "1|2|3|4|5|6|7|8|9");
				sql2.setString(2, "1|2|3|4|5|6|7|8|9");
				sql2.setString(3, "1|2|3|4|5|6|7|8|9");
				sql2.setString(4, "1|2|3|4|5|6|7|8|9");
				sql2.setString(5, "1|2|3|4|5|6|7|8|9");
				sql2.setString(6, "1|2|3|4|5|6|7|8|9");
				sql2.setString(7, "1|2|3|4|5|6|7|8|9");
				sql2.setString(8, "1|2|3|4|5|6|7|8|9");
				sql2.setString(9, "1|2|3|4|5|6|7|8|9");
				sql2.setString(10, "1|2|3|4|5|6|7|8|9");
				sql2.setString(11, "1|2|3|4|5|6|7|8|9");
				sql2.setString(12, "1|2|3|4|5|6|7|8|9");
				sql2.setString(13, uuid);
				sql2.execute();
				sql2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}, 0L);`

Updated the code. So you're saying I don't need to close them at all?

[ItsVinnyX]

Worked perfectly! Wow I never new you could do that with try-and-catches.

[ItsVinnyX]

Take that back it ended up giving me this error.

https://hastebin.com/owexebaxag.vbs

Provided code and line number within the log.

[ItsVinnyX]

I also converted my SQLManager class to your try-with-resource statement suggestion and this happen.

CODE and ERROR as well as LINE NUMBER included.

https://paste.ee/p/wJDpn

[ItsVinnyX]

All fixed, but if you can explain why you can't use RS in the resource.

[brettwooldridge]

Line 81 is executing the query before you have set the query parameters on line 82. ResultSets are automatically closed when the Statement is closed, so don’t worry about it.

[ItsVinnyX]

Ok I appreciate it. Thanks!

[tharindu-kalhara]

from https://stackoverflow.com/questions/50390414/hikaricp-timeout-after-30000-waiting-for-a-connection

it is correct.