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.