mysql
今天生产环境遇到一个问题，如下:
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
环境描述：
mysql版本： 5.6.33-log
BINLOG_FORMAT:默认STATEMENT
框架是resty。
查找原因：resty默认事务级别是：READ_COMMITTED (第二级)【看下边Transaction类】，而mysql默认事无级别是：REPEATABLE-READ（第三级），InnoDB 在事务级别是READ COMMITTED 或者READ UNCOMMITTED时，BINLOG会采用ROW方式，与默认的日志格式STATEMENT不一致，最终导致异常的发生。
package cn.dreampie.orm.transaction; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; import java.sql.Connection; /** * Created by ice on 14-12-30. */ @Retention(RetentionPolicy.RUNTIME) @Target({ElementType.METHOD}) public @interface Transaction { String[] name() default {}; boolean[] readonly() default false; int[] level() default Connection.TRANSACTION_READ_COMMITTED; }
解决方案：提高jdbc事务等级,即修改Transaction 类，把
int[] level() default Connection.TRANSACTION_READ_COMMITTED;//第二级 2
改为
int[] level() default Connection.TRANSACTION_REPEATABLE_READ;//第三级 4
即可
注：网上有见修改binlog_format=mixed，个人测试发现如果不提升事务等级，会出现事务已经提交了，但是事务改动的数据没有写入二进制日志。
测试库test，表pwd
CREATE TABLE `pwd` ( `pwd` varchar(255) DEFAULT NULL, `no` varchar(100) NOT NULL, PRIMARY KEY (`no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql配置
log-bin=mysql-bin log-bin=C:\Program Files\MySQL\MySQL Server 5.5\data # [不是必须]启用二进制日志 server-id=226 #[必须]服务器唯一ID，默认是1，一般取IP最后一段 binlog_format=mixed
查看状态：
mysql> show master status; +-------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------+----------+--------------+------------------+ | data.000006 | 107 | | | +-------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
查看日志：
C:\Program Files\MySQL\MySQL Server 5.5>MySQLbinlog data.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#161109 13:00:22 server id 226 end_log_pos 107 Start: binlog v 4, server v 5.5.31-log created 161109 13:00:22 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
Zq0iWA/iAAAAZwAAAGsAAAABAAQANS41LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABmrSJYEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
执行插入java代码
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class JdbcTest { public static final String DRIVER="com.mysql.jdbc.Driver"; public static final String URL="jdbc:mysql://localhost:3306/test"; public static void main(String[] args) { Connection c = null; PreparedStatement ps; try { Class.forName("com.mysql.jdbc.Driver"); c = DriverManager.getConnection(URL, "root", "root"); c.setAutoCommit(false); c.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); // System.out.println(c.getAutoCommit()); // System.out.println(c.getTransactionIsolation()); ps = c.prepareStatement("insert into pwd values (?,?)"); ps.setString(1, "25551"); ps.setString(2, "25551"); ps.executeUpdate(); c.commit(); } catch (Exception e) { e.printStackTrace(); try { c.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } } }
执行之后 ：数据库插入了一条数据，如图
二进制日志如下
C:\Program Files\MySQL\MySQL Server 5.5>MySQLbinlog data.000006 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #161109 13:00:22 server id 226 end_log_pos 107 Start: binlog v 4, server v 5.5.31-log created 161109 13:00:22 at st # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' Zq0iWA/iAAAAZwAAAGsAAAABAAQANS41LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABmrSJYEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 107 #161109 13:03:45 server id 226 end_log_pos 175 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1478667825/*!*/; SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1344274432/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 175 # at 222 #161109 13:03:45 server id 226 end_log_pos 222 Table_map: `test`.`pwd` mapped to number 33 #161109 13:03:45 server id 226 end_log_pos 266 Write_rows: table id 33 flags: STMT_END_F BINLOG ' Ma4iWBPiAAAALwAAAN4AAAAAACEAAAAAAAEABHRlc3QAA3B3ZAACDw8E/QIsAQE= Ma4iWBfiAAAALAAAAAoBAAAAACEAAAAAAAEAAv/8BQAyNTU1MQUAMjU1NTE= '/*!*/; # at 266 #161109 13:03:45 server id 226 end_log_pos 293 Xid = 14 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; C:\Program Files\MySQL\MySQL Server 5.5>
把session事务变成TRANSACTION_REPEATABLE_READ，修改java测试类（同时修改测试数据）：
c.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
// System.out.println(c.getAutoCommit());
// System.out.println(c.getTransactionIsolation());
ps = c.prepareStatement("insert into pwd values (?,?)");
ps.setString(1, "35551");
ps.setString(2, "35551");
执行结果
日志结果，会发现刚才执行的sql语句：
C:\Program Files\MySQL\MySQL Server 5.5>MySQLbinlog data.000006 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #161109 13:00:22 server id 226 end_log_pos 107 Start: binlog v 4, server v 5.5.31-log created 161109 13:00:22 at startu # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' Zq0iWA/iAAAAZwAAAGsAAAABAAQANS41LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABmrSJYEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 107 #161109 13:03:45 server id 226 end_log_pos 175 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1478667825/*!*/; SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1344274432/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 175 # at 222 #161109 13:03:45 server id 226 end_log_pos 222 Table_map: `test`.`pwd` mapped to number 33 #161109 13:03:45 server id 226 end_log_pos 266 Write_rows: table id 33 flags: STMT_END_F BINLOG ' Ma4iWBPiAAAALwAAAN4AAAAAACEAAAAAAAEABHRlc3QAA3B3ZAACDw8E/QIsAQE= Ma4iWBfiAAAALAAAAAoBAAAAACEAAAAAAAEAAv/8BQAyNTU1MQUAMjU1NTE= '/*!*/; # at 266 #161109 13:03:45 server id 226 end_log_pos 293 Xid = 14 COMMIT/*!*/; # at 293 #161109 13:10:50 server id 226 end_log_pos 361 Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1478668250/*!*/; BEGIN /*!*/; # at 361 #161109 13:10:50 server id 226 end_log_pos 464 Query thread_id=7 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1478668250/*!*/; insert into pwd values ('35551','35551') /*!*/; # at 464 #161109 13:10:50 server id 226 end_log_pos 491 Xid = 35 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
