Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   Issue with read queries not being directed to slaves in MMM configuration (http://www.go4expert.com/forums/issue-read-queries-directed-slaves-mmm-t20341/)

Salil 11Dec2009 11:03

Issue with read queries not being directed to slaves in MMM configuration
 
Hello Friends,
We have 5 databases with 2 masters and 3 slaves. If the first master goes down then the writer role is picked up by the second one. The 3 slaves are in reader role alone. In theory the 3 requests are supposed to be served by the 3 slaves. However, what we find is that when both masters goes down then the application server is unable to connect to any of the 3 slaves. Even read requests are not being serviced so essentially instead of 5 DBs we are dependent on just 2.

Here is the output of mmm_control show' when all DBs are up:

Servers status:
bkup1(192.168.5.81): master/ONLINE. Roles: reader(192.168.5.129;), writer(192.168.5.128;)
db1(192.168.5.84): slave/ONLINE. Roles: reader(192.168.5.132;)
db2(192.168.5.87): slave/ONLINE. Roles: reader(192.168.5.133;)
db3(192.168.5.90): slave/ONLINE. Roles: reader(192.168.5.130;)
pri1(192.168.5.78): master/ONLINE. Roles: reader(192.168.5.131;)

Here is the output when two of the masters are down:

Servers status:
bkup1(192.168.5.81): master/HARD_OFFLINE. Roles: None
db1(192.168.5.84): slave/ONLINE. Roles: reader(192.168.5.131;), reader(192.168.5.132;)
db2(192.168.5.87): slave/ONLINE. Roles: reader(192.168.5.129;), reader(192.168.5.133;)
db3(192.168.5.90): slave/ONLINE. Roles: reader(192.168.5.130;)
pri1(192.168.5.78): master/HARD_OFFLINE. Roles: None

As you can see the reader roles have transistioned to the slaves.
I wrote a small program that makes a read request and read only is set to true using conn.setReadOnly(true) however I get this exception:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientC onnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Construc tor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:40 6)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:926)
at com.mysql.jdbc.ConnectionImpl.createNewIO(Connecti onImpl.java:2253)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImp l.java:718)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connect ion.java:46)
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Construc tor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:40 6)
at com.mysql.jdbc.ConnectionImpl.getInstance(Connecti onImpl.java:302)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonReg isteringDriver.java:282)
at com.mysql.jdbc.ReplicationConnection.<init>(Replic ationConnection.java:95)
at com.mysql.jdbc.NonRegisteringReplicationDriver.con nect(NonRegisteringReplicationDriver.java:116)
at DatabaseTest.main(DatabaseTest.java:36)
Caused by: java.net.NoRouteToHostException: No route to host
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl .java:333)
at java.net.PlainSocketImpl.connectToAddress(PlainSoc ketImpl.java:195)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.j ava:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.j ava:366)
at java.net.Socket.connect(Socket.java:519)
at java.net.Socket.connect(Socket.java:469)
at java.net.Socket.<init>(Socket.java:366)
at java.net.Socket.<init>(Socket.java:209)
at com.mysql.jdbc.StandardSocketFactory.connect(Stand ardSocketFactory.java:253)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:280)
at com.mysql.jdbc.ConnectionImpl.createNewIO(Connecti onImpl.java:2177)
... 12 more

The questions is why would I get 'java.net.NoRouteToHostException: No route to host' exception. Are the first two master DBs in some way a gateway to the slave.

venami 11Dec2009 14:23

Re: Issue with read queries not being directed to slaves in MMM configuration
 
This can occur due to the following reason:

The table that you are accessing from 3 slaves may be synonyms of the tables in 2 masters. In such a case, since the masters are down, the database links that the synonyms would use won't be accessible. So you might have got the above error.

The reason for mentioning the database link here is that the three slaves are given only read access.

Please check whether the table that you are accessing is a separate table or a synonym or dependent on the tables in master servers.

Salil 11Dec2009 21:14

Re: Issue with read queries not being directed to slaves in MMM configuration
 
No there are not synonyms but completely separate databases

venami 12Dec2009 10:21

Re: Issue with read queries not being directed to slaves in MMM configuration
 
You mean to say that the data is duplicated in the slaves and masters, which means you have to copies of the same data?

Loogreple 25Jan2010 16:38

Issue with read queries not being directed to slaves in MMM configuration
 
i have problem with mysq_pconnectlink = mysql_pconnectDB_HOST, DB_USER, DB_PASS or dieCould connect: . mysql_error;with same parameters when i use mysql_connect its working.what i must to do for make worked script with mysql_pconnect???


All times are GMT +5.5. The time now is 17:30.