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.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
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.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2253)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:718)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
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.ConnectionImpl.getInstance(ConnectionImpl.java:302)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
at com.mysql.jdbc.ReplicationConnection.<init>(ReplicationConnection.java:95)
at com.mysql.jdbc.NonRegisteringReplicationDriver.connect(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(PlainSocketImpl.java:195)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java: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(StandardSocketFactory.java:253)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:280)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.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.

 

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.

 

No there are not synonyms but completely separate databases

 

You mean to say that the data is duplicated in the slaves and masters, which means you have to copies of the same data?

 

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???