Help MariaDb No connection

[wongerlt]

Hello,

maybe some one had this error?:

java.sql.SQLException: No connection available within the specified time (option 'connectTimeout': 30,000 ms)
	at org.mariadb.jdbc.pool.Pool.getPoolConnection(Pool.java:382)
	at org.mariadb.jdbc.MariaDbPoolDataSource.getConnection(MariaDbPoolDataSource.java:79)
	at net.sf.l2j.commons.pool.ConnectionPool.getConnection(ConnectionPool.java:47)
    .....
    ...
    ..
    ..
    .
    

what can cause this error? Can't figure out

it start appear only maybe after half a day. Only in game server, on login server everything fine.
acis 401

 

what i have tried:

1. Update mariadb connector to 3.1.2 ver.
2. Restart vps.
3. reinstall mariadb

[wongerlt]

2 hours ago, Trance said:

It's unusual to have connection issues on localhost. When this happens, check your computer's memory and processor usage. If they're too high, it might cause timeouts. Also, look at your MySQL settings, especially max_allowed_packet and wait_timeout. If they're too low, increase them. Have you changed any settings before?
 

Poorly written queries can also cause connection issues. You can perform a Java Flight Recording: https://maxcheaters.com/topic/247610-jdk-mission-control-performance-analysis-tool/

I think i found where is problem.

 

// with this i get errors (No connection available within the specified time (option 'connectTimeout': 30,000 ms)

private void updateDatabase()
{
   try {
      Connection con = ConnectionPool.getConnection();
      PreparedStatement stm = con.prepareStatement("DELETE FROM dungeon");
      stm.executeUpdate();
   }catch (Exception e)
   {
      e.printStackTrace();
   }
}

// with this no errors

private void updateDatabase()
{
	try(Connection con = ConnectionPool.getConnection()) {
	 PreparedStatement stm = con.prepareStatement("DELETE FROM dungeon");
	 stm.executeUpdate();
	}catch (Exception e)
	{
	e.printStackTrace();
	}
}

 

who can explain why?

[Friko]

check your mariadb logs in vps, it might give you the reason, also if it only starts to appear after half a day, you might exceed max connections of mariadb and it blocks any further connections, by default it's set to 150, and if you added some custom to 401 acis it might not be closing them properly.
in this case you can increase the amount based on your need.

you can check current max connections with 

SHOW VARIABLES LIKE "max_connections";

you can also run below which will shows current connections and max used connections since mariadb session started:
 

show status where variable_name = 'threads_connected' OR variable_name = 'max_used_connections';

hope this helps.

[wongerlt]

3 minutes ago, Friko said:

check your mariadb logs in vps, it might give you the reason, also if it only starts to appear after half a day, you might exceed max connections of mariadb and it blocks any further connections, by default it's set to 150, and if you added some custom to 401 acis it might not be closing them properly.
in this case you can increase the amount based on your need.

you can check current max connections with 

SHOW VARIABLES LIKE "max_connections";

you can also run below which will shows current connections and max used connections since mariadb session started:
 

show status where variable_name = 'threads_connected' OR variable_name = 'max_used_connections';

hope this helps.

No players in server.

max connections: 256.

active coneections: 3

max_used_connections: 70

 

so problem not there. and in mariadb logs no errors 

[Trance]

Ensure that the following information in your server properties is accurate: the database URL, login credentials, and password.  If you are using Linux, it's a good idea to verify if any firewall rules might be blocking the loopback interface (lo) for localhost, as this could be the cause of the issue.

[Friko]

54 minutes ago, wongerlt said:

No players in server.

max connections: 256.

active coneections: 3

max_used_connections: 70

 

so problem not there. and in mariadb logs no errors 

players don't need to be in server to drain max connections in some packs.

then check if db user itself has rate limits enabled, I would assume db is in local vps as your gameserver, so no limits are needed as long as you use it only for server itself.
database connections per hour, user connections per hour etc.
if you copied some guides how to create DB user they may have included rate limiters.

[wongerlt]

37 minutes ago, Friko said:

players don't need to be in server to drain max connections in some packs.

then check if db user itself has rate limits enabled, I would assume db is in local vps as your gameserver, so no limits are needed as long as you use it only for server itself.
database connections per hour, user connections per hour etc.
if you copied some guides how to create DB user they may have included rate limiters.

there no limits for user.

https://prnt.sc/eJB2oWTgdo8G

1 hour ago, Trance said:

Ensure that the following information in your server properties is accurate: the database URL, login credentials, and password.  If you are using Linux, it's a good idea to verify if any firewall rules might be blocking the loopback interface (lo) for localhost, as this could be the cause of the issue.

db url:
jdbc:mariadb://127.0.0.1:3306/SERVER_DB?user=hidden&password=hidden

everything fine with user/pass/db.

im using linux it have iptables and firewalld but everything fine here.

10 minutes ago, Trance said:

 

Temporarily stop them.

 

sudo systemctl stop firewalld
sudo systemctl stop iptables

 

hm, i will try, now i enabled all logs which i found , will see tomorrow

[Trance]

34 minutes ago, wongerlt said:

there no limits for user.

https://prnt.sc/eJB2oWTgdo8G

db url:
jdbc:mariadb://127.0.0.1:3306/SERVER_DB?user=hidden&password=hidden

everything fine with user/pass/db.

im using linux it have iptables and firewalld but everything fine here.

 

Temporarily stop them:

 

sudo systemctl stop firewalld
sudo systemctl stop iptables

Check if localhost can reach MySQL on port 3306:

 

mysql -u root -h localhost -p<your password> -e "SELECT 'Connected Successfully'"

[wongerlt]

8 minutes ago, Trance said:

 

Temporarily stop them:

 

sudo systemctl stop firewalld
sudo systemctl stop iptables

Check if localhost can reach MySQL on port 3306:

 

mysql -u root -h localhost -p<your password> -e "SELECT 'Connected Successfully'"

 

yes localhost and 127.0.01 connect successfully. Like i said error start throwing only after some hours.

[Trance]

5 minutes ago, wongerlt said:

yes localhost and 127.0.01 connect successfully. Like i said error start throwing only after some hours.

 

That is not normal. However, you can increase connection timeout to 60 seconds for testing purposes.
 

jdbc:mariadb://127.0.0.1:3306/SERVER_DB?connectTimeout=60000?user=hidden&password=hidden

All you can do is wait for the logging results.

[LoVe+]

4 hours ago, wongerlt said:

yes localhost and 127.0.01 connect successfully. Like i said error start throwing only after some hours.

so you db is up , ls is still online without any problem but your gs is having trouble connecting to db sometimes ?

[Brado]

6 hours ago, wongerlt said:

yes localhost and 127.0.01 connect successfully. Like i said error start throwing only after some hours.

Can u share your linux distro and if you have applied any settings on it?

[wongerlt]

9 hours ago, LoVe+ said:

so you db is up , ls is still online without any problem but your gs is having trouble connecting to db sometimes ?

yes.

13 hours ago, Trance said:

 

That is not normal. However, you can increase connection timeout to 60 seconds for testing purposes.
 

jdbc:mariadb://127.0.0.1:3306/SERVER_DB?connectTimeout=60000?user=hidden&password=hidden

All you can do is wait for the logging results.

i got one error:
[Warning] Aborted connection 3 to db: 'SERVER_DB' user: 'root' host: 'localhost' (Got timeout reading communication packets)

[Trance]

5 hours ago, wongerlt said:

yes.

i got one error:
[Warning] Aborted connection 3 to db: 'SERVER_DB' user: 'root' host: 'localhost' (Got timeout reading communication packets)

It's unusual to have connection issues on localhost. When this happens, check your OS' RAM and CPU usage. If they're too high, it might cause timeouts. Also, look at your MySQL settings, especially max_allowed_packet and wait_timeout. If they're too low, increase them. Have you changed any settings before?
 

Poorly written queries can also cause connection issues. You can perform a Java Flight Recording: https://maxcheaters.com/topic/247610-jdk-mission-control-performance-analysis-tool/

Edited March 23, 2023 by Trance

[Trance]

12 minutes ago, wongerlt said:

I think i found where is problem.

 

// with this i get errors (No connection available within the specified time (option 'connectTimeout': 30,000 ms)

private void updateDatabase()
{
   try {
      Connection con = ConnectionPool.getConnection();
      PreparedStatement stm = con.prepareStatement("DELETE FROM dungeon");
      stm.executeUpdate();
   }catch (Exception e)
   {
      e.printStackTrace();
   }
}

// with this no errors

private void updateDatabase()
{
	try(Connection con = ConnectionPool.getConnection()) {
	 PreparedStatement stm = con.prepareStatement("DELETE FROM dungeon");
	 stm.executeUpdate();
	}catch (Exception e)
	{
	e.printStackTrace();
	}
}

 

who can explain why?

 

It is closing the connection automatically because the Connection object is being created within the try block.

Edited March 23, 2023 by Trance