Today i spent a ridiculously amount of time finding out why suddenly my application cannot connect to database. Im using Glassfish 3 connected to MySQL via connection pool on an Ubuntu server with an IP public. What makes it difficult is that it can connect smoothly before but suddenly an error happened.
This is the detail exception from my Glassfish’s log.
[#|2011-11-07T15:26:24.411+0700|WARNING|glassfish3.0.1|javax.enterprise.resource.resourceadapter.com.sun. enterprise.connectors.service|_ThreadID=30;_ThreadName=http-thread-pool-4848-(5);|RAR8054: Exception while creating an unpooled [test] connection for pool [ dbepay ], Connection could not be allocated because: Communications link failure Last packet sent to the server was 0 ms ago.|#] [#|2011-11-07T15:26:30.982+0700|WARNING|glassfish3.0.1|javax.enterprise.resource.resourceadapter.com. sun.enterprise.resource.pool|_ThreadID=31;_ThreadName=http-thread-pool-4848-(2);|RAR8023: Flush Connection Pool did not happen as pool - dbepay is not initialized|#]
After some browsing i found out that it’s due to mysql’s binding issue. Exception happened because MySQL server and Glassfish are installed on the same host, and in MySQL configuration on my.cnf have binded to a public ip address instead of localhost.
This is my ifconfig looks like
root@portal:~/edw/glassfishv3/glassfish/modules# ifconfig eth0 Link encap:Ethernet HWaddr 00:50:56:01:00:0e inet addr:xxx.xx.xxx.xx Bcast:xxx.xx.xxx.xx Mask:255.255.255.128 inet6 addr: xxxxx :e/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:2082924 errors:0 dropped:0 overruns:0 frame:0 TX packets:1065587 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:668840899 (668.8 MB) TX bytes:222095600 (222.0 MB) lo Link encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:16436 Metric:1 RX packets:4888314 errors:0 dropped:0 overruns:0 frame:0 TX packets:4888314 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:406280073 (406.2 MB) TX bytes:406280073 (406.2 MB)
All i do is edit MySQL’s my.cnf, adding a new bind address to 127.0.0.1 and remarking previous binding.
[mysqld] # # * Basic Settings # # # * IMPORTANT # If you make changes to these settings and your system uses apparmor, you may # also need to also adjust /etc/apparmor.d/usr.sbin.mysqld. # user = mysql socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #this is my previous value, binded to a public ip address #bind-address = xxx.xxx.xxx.xxx #this is my new value bind-address = 127.0.0.1
restart both of mysql and glassfish and suddenly, my application run smoothly again.
Thanks uncle Google, you’ve helped me alot.