当前位置: > 数据库 > MySQL >

Mysql数据库“Connection is read-only”问题

时间:2015-06-24 22:31来源:未知 作者:IT

昨天配置好Mysql集群后,把程序放在上面跑,测试没有什么问题,可是今天来公司之后,打开系统,想添加一点信息,突然发现添加失败,查看了tomcat的日志信息,发现了下面这段异常:

 
 1 Dec 30, 2011 4:42:32 AM org.apache.catalina.core.StandardWrapperValve invoke
 2 SEVERE: Servlet.service() for servlet assetscenter threw exception
 3 java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
 4         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
 5         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
 6         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
 7         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
 8         at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2349)
 9         at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2316)
10         at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2301)
11         at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
12         at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:826)
13         at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
14         at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:824)
15         at com.cisco.assetscenter.dao.impl.DefaultCaseDAO.insert(DefaultCaseDAO.java:29)
16         at com.cisco.assetscenter.service.asset.impl.DefaultCaseService.createCase(DefaultCaseService.java:18)
17         at com.cisco.assetscenter.web.cases.CaseAddController.handleRequest(CaseAddController.java:77)
18         at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
19         at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
20         at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
21         at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
22         at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
23         at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
24         at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
25         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
26         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
27         at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
28         at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
29         at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
30         at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
31         at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
32         at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:291)
33         at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
34         at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
35         at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
36         at java.lang.Thread.run(Thread.java:662)
37 Dec 30, 2011 4:43:41 AM org.apache.catalina.core.StandardWrapperValve invoke
 

很郁闷,在想昨天还好好的,什么都没有动,为什么今天就Read Only了呢,我有查看了一下,Mysql Cluster的状态信息:

 
 1 -- NDB Cluster -- Management Client --
 2 ndb_mgm> show
 3 Connected to Management Server at: localhost:1186
 4 Cluster Configuration
 5 ---------------------
 6 [ndbd(NDB)]     2 node(s)
 7 id=2    @10.74.59.166  (mysql-5.1.56 ndb-7.1.17, Nodegroup: 0, Master)
 8 id=3 (not connected, accepting connect from 10.74.59.247)
 9 
10 [ndb_mgmd(MGM)] 1 node(s)
11 id=1    @10.74.59.175  (mysql-5.1.56 ndb-7.1.17)
12 
13 [mysqld(API)]   2 node(s)
14 id=4    @10.74.59.166  (mysql-5.1.56 ndb-7.1.17)
15 id=5    @10.74.59.247  (mysql-5.1.56 ndb-7.1.17)
 

发现有一台机器不知道怎么回事,Data Node宕掉了,可能问题出现在这里了。

其实,MySQL官方倡导的是Master-To-Slave的数据库备份方案,而我们的运行环境是Master-To-Master,如果前一个Master挂掉的话 往Slave中去写,等到Master恢复再对Master操作的时候数据就沉于大海,那么数据就不一致了,恢复起来会非常的麻烦,当前一个挂掉的时候,MySQL驱动会认为你的Master挂了,如果继续写将会写入Slave,所以默认的状态是ReadOnly=true,失败后重连,但重连后对下一台操作的是只读(ReadOnly)。

这就是问题所在了,所以我的解决方案就是重启10.74.59.247服务器Data Node服务,并且修改了连接串参数:

1 jdbc.url=jdbc:mysql:loadbalance://10.74.59.176:3306,10.74.59.247:3306/assetscenterdb?failOverReadOnly=false&autoReconnect=true&roundRobinLoadBalance=true

Mysql链接有很多的参数可以配置,你可以查看Mysql链接手册

此时你再访问程序,读写操作都一切正常了。

 

参考资料:http://www.javabloger.com/article/mysql-read-only-ibatis-dbcp-hibernate.html



(责任编辑:IT)
------分隔线----------------------------
栏目列表
推荐内容