Re: MySQL Connection Error

Hi Willy,

I thought the same that it might be because i am exceeding the connection. But In MySQL max_connection=200 only.

Also although there Error in App (communication link failure) i am able to do "mysql -u <username> -p<password> -h 192.168.0.100". from any host which are allowed to connect mysql over HAproxy works fine on command-line.

So I am under strong impression that something need to fix either in App or in HAProxy so that it understand JDBC connection. But not able to figure out where is the problem.

Because only connection over JDBC to HAProxy giving problem. so does there is any need to add some parameter in my config of HAProxy?

Thanks,

On Sun, Dec 4, 2011 at 3:27 AM, Willy Tarreau <w#1wt.eu> wrote:

> Hi Prasad,
>
> On Thu, Dec 01, 2011 at 07:34:42PM +0530, Prasad Wani wrote:
> > I am doing Basic Failover testing of MySQL Tcp Port with HAProxy as
> > frontend.
> >
> > 192.168.0.100 (HAProxy Machine) with Config (No Java,MySQL Client on this
> > machine)
> >
> > global
> > daemon
> > maxconn 1024
> >
> > defaults
> > mode http
> > timeout connect 5000ms
> > timeout client 50000ms
> > timeout server 50000ms
> >
> > listen mysql 0.0.0.0:3306
> > mode tcp
> > balance source
> > server server1 192.168.0.10:3306 maxconn 200 check inter 5000 fall 3
> > server server1 192.168.0.20:3306 maxconn 200 check inter 5000 fall 3
> backup
> >
> >
> > 192.168.0.10 (MySQL 5.5.10 Master1)
> > 192.168.0.20 (MySQL 5.5.10 Master2)
> >
> >
> > with Application on 192.168.0.2 (Tomcat6, java 1.6.0_24) with MySQL
> > Connector/J 5.1.17
> >
> > If I connect directly to MySQL Server then there is no exception seen.
> But
> > when I connect using HAProxy I am getting following exception.
> >
> > com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications
> > link failure
> >
> > Last packet sent to the server was 0 ms ago.
> >
> > at sun.reflect.GeneratedConstructorAccessor18.newInstance(Unknown Source)
> >
> > at
> >
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
> >
> > at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
> >
> > at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
> >
> > at
> com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
> >
> > at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2873)
> >
> > at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2763)
> >
> > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3299)
> >
> > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
> >
> > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
> >
> > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2537)
> >
> > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2466)
> >
> > at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:735)
> >
> > at
> com.webaroo.smsnew.db.Connection$PollingThread.run(Connection.java:402)
> >
> > 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:2332)
> >
> > at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2774)
> >
> > ... 8 more
> >
> >
> > What could be the cause of these exception.
>
> I don't know this code, but it looks like a timeout. Maybe you're
> trying to establish more than 200 connections and the extra ones
> die in the queue ? Or maybe some requests time out after 50 seconds ?
> You did not enable logging so it's hard to give a diagnostic :-/
>
> Regards,
> Willy
>
>

-- 
Prasad S. Wani