Liferay mysql connection issues


Liferay mysql connection issues – resolution

“com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure” : some times liferay faces connectivity issues and we even faced this liferay mysql communication link failure issue in  one of our projects

Error Log:

 Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure__The last packet successfully received from the server was 102,013 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago. [Sanitized]
                at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
                at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
                at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
                at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
                at com.mysql.jdbc.Util.handleNewInstance(Util.java:389)
                at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1038)
                at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3422)
                at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3322)
                at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3762)
                at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
                at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
                at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531)
           
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
                at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2914)
                at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3332)
                ... 175 more

Solution:

  1. check version of  mysql connector jar
  2. add autoReconnect=true,maxReconnects,rewriteBatchedStatements and failOverReadOnly parameters to JDBC url : jdbc:mysql://localhost:3306/lportal?autoReconnect=true&failOverReadOnly=false &maxReconnects=10 &rewriteBatchedStatements=true&useServerPrepStmts=false&useUnicode=yes&useFastDateParsing=false
  3. Update the below parameters in mysql and you need to edit theme in my.cnf file
    • global wait_timeout=3600;
      global interactive_timeout=230400;
      interactive_timeout = 28800
      connect_timeout=300
      wait_timeout = 57600
      max_connections,
      max_connect_errors
  4. set max_allowed_packet=1024  huge size if you are doing larger database writes and read.
  5. set  bind-address=”127.0.0.1″  or bind-address=”0.0.0.0″  in my.cnf file
  6.  Now restart the mysql and liferay servers.

Hope this will resolve your issues(still you may need to look into proxy network settings also if problem persists).