MySQL Database "connection is read-only"

After configuring the MySQL cluster yesterday, I ran the program on it. There was no problem in the test. But after I came to the company today, I opened the system and wanted to add some information. Then I suddenly found that the addition failed, view the Tomcat log information and find the following exception:

 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

I was very depressed. I was wondering why I read only today. I checked the status of 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)

I found that there was a machine that did not know what was going on. The data node went down, and the problem may occur here.

In fact, MySQL officially advocates the master-to-slave database backup solution, while our runtime environment is master-to-Master, if the previous master fails, write it to slave. When the master recovers and then the master operation is performed, the data will sink into the sea, so the data will be inconsistent and recovery will be very troublesome, when the current master fails, the MySQL driver will think that your master is down. If you continue writing, it will be written to slave. Therefore, the default status is readonly = true, and re-connect after failure, however, after the reconnection, the next operation is read-only ).

This is the problem, so my solution is to restart the data node service on the 10.74.59.247 server and modify the connection string parameters:

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

The MySQL link can be configured with many parameters. You can view the MySQL link manual.

Now you can access the program and read and write operations are all normal.

 

References: http://www.javabloger.com/article/mysql-read-only-ibatis-dbcp-hibernate.html