I am running a javafx desktop app to access a remote mariadb database over internet + openvpn. I can log in OK via a mysql command line interface but the java app times out when logging in. If you retry repeatedly it eventually logs in OK and after that the application works fine. So the timeout is only when logging in. I can see many stackoverflow posts talking about mariadb JDBC timeouts but nothing specific to logins.
Here is a test program illustrating the problem (I have changed sensitive strings to anonomise them):
public void login()
{
Connection connection = null;
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yyyy SSS");
boolean connected = false;
int attempt = 0;
String url = "jdbc:mariadb://hostxxx:3306/databasexxx";
while (!connected)
{
try
{
attempt++;
System.out.println("connecting: attempt " + attempt + " at "
+ LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS")));
connection = DriverManager.getConnection(url, "userxxx", "passwordxxx");
connected = true;
System.out.println("connected");
return;
}
catch (SQLException ex)
{
System.out.println(ex.getClass().getSimpleName() + ": " + ex.getMessage());
}
}
}
Here is the output:
started
connecting: attempt 1 at 2022-09-16 14:46:01.352
SQLNonTransientConnectionException: Could not connect to address=(host=hostxxx)(port=3306)(type=master) : Read timed out
connecting: attempt 2 at 2022-09-16 14:46:03.658
SQLNonTransientConnectionException: Could not connect to address=(host=hostxxx)(port=3306)(type=master) : Read timed out
connecting: attempt 3 at 2022-09-16 14:46:05.524
SQLNonTransientConnectionException: Could not connect to address=(host=hostxxx)(port=3306)(type=master) : Read timed out
...
connecting: attempt 19 at 2022-09-16 14:46:36.735
SQLNonTransientConnectionException: Could not connect to address=(host=hostxxx)(port=3306)(type=master) : Read timed out
connecting: attempt 20 at 2022-09-16 14:46:38.633
connected
In this case it manged to login after 20 attempts. It seems to time out after about 2 seconds. If I run the program against a local database it works fine. I have tried Mariadb jdbc driver versions 2.7.2 and 2.7.6. I tried adding ?socketTimeout=30000 and ?connectTimeout=30000" to the url but that made no difference.
Here is the full stacktrace:
java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=hostxxx)(port=3306)(type=master) : Read timed out
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:73)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:197)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1394)
at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:635)
at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:150)
at org.mariadb.jdbc.Driver.connect(Driver.java:89)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
at mariadblogin.Login.login(Login.java:29)
at mariadblogin.Main.main(Main.java:17)
Caused by: java.sql.SQLNonTransientConnectionException: Read timed out
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.handleIoException(AbstractQueryProtocol.java:2091)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1541)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1520)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.readPipelineAdditionalData(AbstractConnectProtocol.java:1033)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.postConnectionQueries(AbstractConnectProtocol.java:885)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createConnection(AbstractConnectProtocol.java:600)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1389)
... 7 more
Caused by: java.net.SocketTimeoutException: Read timed out
at java.base/java.net.SocketInputStream.socketRead0(Native Method)
at java.base/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:168)
at java.base/java.net.SocketInputStream.read(SocketInputStream.java:140)
at java.base/java.io.FilterInputStream.read(FilterInputStream.java:133)
at org.mariadb.jdbc.internal.io.input.ReadAheadBufferedStream.fillBuffer(ReadAheadBufferedStream.java:131)
at org.mariadb.jdbc.internal.io.input.ReadAheadBufferedStream.read(ReadAheadBufferedStream.java:104)
at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(StandardPacketInputStream.java:247)
at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacket(StandardPacketInputStream.java:218)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1539)
... 12 more
I would be very grateful for anyone's ideas about how to solve this problem.