jdbc和主从复制的奇怪问题

realmon

按照mysql的ref设置了一下
用jdbc:mysql://master,slave/testdb 连接
发现只要master或者slave fail掉了,都无法update了(可以select)可用性反而降低了?

测试代码

1. 
   
2. package test;
   
3. 
   
4. import java.sql.Connection;
   
5. import java.sql.ResultSet;
   
6. import java.util.Properties;
   
7. 
   
8. public class SerivceTest {
   
9. 
   
10.   public static void main(String[] args) throws Exception {
    
11.     com.mysql.jdbc.Driver driver = new com.mysql.jdbc.Driver();
    
12. //    ReplicationDriver driver = new ReplicationDriver();
    
13.     Properties props = new Properties();
    
14. 
    
15. //    // We want this for failover on the slaves
    
16. //    props.put("autoReconnect", "true");
    
17. //
    
18. //    // We want to load balance between the slaves
    
19. //    props.put("roundRobinLoadBalance", "true");
    
20. 
    
21.     props.put("user", "crud");
    
22.     props.put("password", "");
    
23. 
    
24.     //
    
25.     // Looks like a normal MySQL JDBC url, with a
    
26.     // comma-separated list of hosts, the first
    
27.     // being the 'master', the rest being any number
    
28.     // of slaves that the driver will load balance against
    
29.     //
    
30. 
    
31.     Connection conn =
    
32.         driver.connect("jdbc:mysql://master,slave/testdb",
    
33.             props);
    
34. 
    
35.     //
    
36.     // Perform read/write work on the master
    
37.     // by setting the read-only flag to "false"
    
38.     //
    
39. 
    
40.     conn.setReadOnly(false);
    
41.     conn.setAutoCommit(false);
    
42.     //conn.createStatement().executeUpdate("UPDATE some_table ....");
    
43.     conn.commit();
    
44. 
    
45.     //
    
46.     // Now, do a query from a slave, the driver automatically picks one
    
47.     // from the list
    
48.     //
    
49. 
    
50. //    conn.setReadOnly(true);
    
51. 
    
52.     ResultSet rs =
    
53.       conn.createStatement().executeQuery("SELECT * FROM test ");
    
54.     while(rs.next()) {
    
55.       System.out.println(rs.getString("id"));
    
56.     }
    
57.   }
    
58. 
    
59. }
    
60. 
    

复制代码

conn.commit();
以后没反应了.
一直在等待,貌似在等待连接已经fail的那个mysql

realmon

大家都不是使用jdbc的么

horizonhyg

回复 1# realmon

realmon

ReplicationDriver,com.mysql.jdbc.Driver我都试过
按官方的解释,ReplicationDriver是可以把readonly的请求route到slave去,这个功能我可要可不要.
不能忍受的是 为什么slave fail了以后,master 也不能用了呢

horizonhyg

回复 4# realmon


    以前遇到过,主坏了不行,从坏了也不行,主从关系坏了也不行

realmon

找到问题了,原来是个bug
Bug #56429 Failover functionality establishes useless connections to non-primary servers
http://bugs.mysql.com/bug.php?id=56429
把驱动升级到5.1.14以上就OK了