Case study of improper transaction isolation level and row log format

Recently, the test platform that took over a new project adopts the spring transaction mechanism. During User Authentication logon, the system always prompts authentication timeout. When the user checks the logs of the corresponding application servers, an exception is reported:

Binary logging not possible. Message: Transaction level 'read-committed' in InnoDB is not safe for binlog mode 'Statement '...

......

An application server at the front end reports 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, the above error can be used to determine the problem. caused by incompatibility between innodb and binlog... mySQL recommended: when setting the isolation level for the READ-COMMITED must be set to ROW log format, now MySQL official also clearly stated STATEMENT this is not recommended to use!

The transaction isolation level of the current system is READ-COMMITTED. The row log format is STATEMENT.

There are two solutions:

One is to improve the transaction isolation level to the REPEATABLE-READ, which is the default transaction isolation level of MySQL;

Mysql> set global transaction isolation level repeatable read;

One is to maintain the current transaction isolation level and modify the ROW log format to MIXED or ROW.

Modify the configuration file here:

Binlog_format = row

# Binlog_format = MIXED

Restart service

There is a special note that if binlog_format is MIXED and uses a default isolation level REPEATABLE-READ, it will cause Master/Slave Data inconsistency.

Refer to he's blog: http://hcymysql.blog.51cto.com/5223301/1021174

This article is from the "->" blog, please be sure to keep this source http://opsmysql.blog.51cto.com/2238445/1201544