1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Issue with read queries not being directed to slaves in MMM configuration

Discussion in 'MySQL' started by Salil, Dec 11, 2009.

  1. Salil

    Salil New Member

    Joined:
    Mar 23, 2007
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  2. venami

    venami New Member

    Joined:
    Dec 26, 2008
    Messages:
    195
    Likes Received:
    10
    Trophy Points:
    0
    Occupation:
    Software Professional
    Location:
    India, Tamil Nadu, Cuddalore
    Home Page:
    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.
     
  3. Salil

    Salil New Member

    Joined:
    Mar 23, 2007
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    0
    No there are not synonyms but completely separate databases
     
  4. venami

    venami New Member

    Joined:
    Dec 26, 2008
    Messages:
    195
    Likes Received:
    10
    Trophy Points:
    0
    Occupation:
    Software Professional
    Location:
    India, Tamil Nadu, Cuddalore
    Home Page:
    You mean to say that the data is duplicated in the slaves and masters, which means you have to copies of the same data?
     
  5. Loogreple

    Loogreple New Member

    Joined:
    Jan 24, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Kyrgyzstan
    Location:
    Kyrgyzstan
    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???
     

Share This Page