Failure cases due to improper isolation level and row log format setup

The test platform that recently took over a new project uses the spring transaction mechanism, always prompts for authentication timeout when user authenticates login, and then looks at the corresponding application server logs and finds the reported exception:

Binary logging not possible. Message:transaction level ' read-committed ' in InnoDB isn't safe for Binlog mode ' STATEMENT ' ...

......

A front-end application server reported the following error:

{"Returnno": 0, "ListField": [{"Usercode": "admin", "NewPassword": "", "SessionKey": "", "Password": " 670B14728AD9902AECBA32E22FA4F6BD "," IP ":" 192.168.1.75 "}]," Returnobject ": null," Commondid ":" 8000 "}

Java.io.IOException:Server returned HTTP response code:500 for url:http://xxxxx/xxxxxx

At Sun.net.www.protocol.http.HttpURLConnection.getInputStream (httpurlconnection.java:1313)

In fact, through the top of the error can determine where the problem. Due to InnoDB and binlog incompatibility ... MySQL Recommendation: When setting the isolation level of read-commited must be set row log format for rows, now MySQL official also clearly stated that statement this has not been recommended!

The current system transaction ISOLATION level is: read-committed, the row log format is: STATEMENT

There are two ways to fix this:

One is to elevate the isolation level of repeatable-read, which is the default transaction isolation level of MySQL;

Mysql> set GLOBAL transaction ISOLATION level repeatable read;

One is to keep the current transaction isolation level and modify the row log format to MIXED or row.

This modifies the configuration file:

Binlog_format = row

#binlog_format = MIXED

Restart Service

One particular issue to note is that if Binlog_format is MIXED and the default isolation level Repeatable-read is used, it can result in inconsistent master and subordinate data.