24 April 2014

JDBC: Connection could not be allocated because:

Connection could not be allocated because: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. Please check the server.log for more details.

I stumbled about this Exception and wanted to let you know how I solved it for me. In my case that Exception comes from a GlassFish v4 instance interacting with a MySQL v5.5.37 database over JDBC v5.1.26. GlassFish wants to tell us that he can't receive any packages from the database but doesn't tell us exactly what's the reason. I figured out that the default value for the JDBC idle timeout are 300 secs but for MySQL the default value are (in my case) 180 secs. In this scenario MySQL is the server and GlassFish the client. The client connects to the server and keeps a connection. After 180 secs the server may kills the connection due to inactivity. The client does not get any information about this and keeps the connection for further 120 secs. If you call for data within these 120 secs you probably run into that exception because the connection is already closed and the client (GlassFish) complains with that exception.

So the solution is to lower the idle timeout in GlassFish to a value lower than 180 secs or raise the wait_timeout value in MySQL to a value bigger than 300 secs.

To change the idle timeout in GlassFish:
- Go to http://{ip of server or localhost}:4848/
- Go to Resources -> JDBC -> JDBC Connection Pools and select your pool
- Scroll down to Pool Settings and change the value of Idle Timeout:
- Hit Save

To change the wait_timeout in MySQL:
- Change the dir to /etc/mysql/
- Open the my.cnf file and search for a entry called wait_timeout
- Change the value and save the document (May you have to restart MySQL too).

Hope this helps and...


Happy Coding!

3 comments :

  1. hmm strange. I had already set my idle timeout to 30 seconds, trying to deal with running out of connections and needing to manually flush.

    [2019-04-21T08:47:52.013+1000] [glassfish 4.1] [WARNING] [poolmgr.create_resource_error] [javax.enterprise.resource.resourceadapter.com.sun.enterprise.resource.allocator] [tid: _ThreadID=115 _ThreadName=p: thread-pool-1; w: 7] [timeMillis: 1555800472013] [levelValue: 900] [[
    RAR5038:Unexpected exception while creating resource for pool IngressMysqlPool. Exception : javax.resource.spi.ResourceAllocationException: Connection could not be allocated because: Communications link failure

    Maybe my Max Wait Time is too low?

    ReplyDelete
  2. To change the wait_timeout in MySQL:
    - Change the dir to /etc/mysql/
    - Open the my.cnf file and search for a entry called wait_timeout
    - Change the value and save the document (May you have to restart MySQL too).

    gedit /opt/lampp/etc/my.cnf

    before:
    innodb_lock_wait_timeout=50
    after
    innodb_lock_wait_timeout=300
    is ok
    muchas gracias amigo.

    ReplyDelete