04 October 2009

PITA 3: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

I was trying to update around 70k records in a MySQL table through a loop, and kept getting the following exception after 16k+ records:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 0 ms ago.

Tried everything which I could get on the web, but nothing happened. Few suggestions which I found on the web are:

1. replace localhost with 127.0.0.1 in MySQL connection string... In some cases this works because Windows Defender removes localhost entry from C:\Windows\system32\drivers\etc\hosts file therefore MySQL won't be able to resolve the host and throw that exception.

2. modify registry to connect from TCP ports greater than 5000... this also didn't work for me, you can see the opened connection by typing the command netstat -b.
Details about this are given here http://support.microsoft.com/kb/196271

3. add wait_timeout = X in my.ini (or my.cnf on *nix env.)... this will help if you have problems with MySQL itself, for that check MySQL error log located in mysql Data directory. If you have any errors about connections setting wait_timeout may help, but in my case there are no errors from MySQL site.

How did I resolve it??... Instead of processing all the records in one execution I processed only 1000 records at a time.

However the exact reason is still unknown.