• Mats Kindahl's avatar
    Bug #37221: SET AUTOCOMMIT=1 does not commit binary log · 124e8301
    Mats Kindahl authored
    When setting AUTOCOMMIT=1 after starting a transaction, the binary log
    did not commit the outstanding transaction. The reason was that the binary
    log commit function saw the values of the new settings, deciding that there
    were nothing to commit.
    
    Fixed the problem by moving the implicit commit to before the thread option
    flags were changed, so that the binary log sees the old values of the flags
    instead of the values they will take after the statement.
    
    
    mysql-test/extra/binlog_tests/implicit.test:
      New test file to check implicit commits both inside and outside transactions.
    mysql-test/suite/binlog/t/binlog_implicit_commit.test:
      Test for implicit commit of SET AUTOCOMMIT and LOCK/UNLOCK TABLES.
    sql/set_var.cc:
      Adding code to commit pending transaction before changing option flags.
    124e8301
binlog_implicit_commit.result 12.9 KB
CREATE TABLE t1 (id INT) ENGINE = InnoDB;
SET BINLOG_FORMAT = STATEMENT;
RESET MASTER;
SET AUTOCOMMIT = 0;
INSERT INTO t1 VALUES (1);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 1;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (1)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 0;
BEGIN;
INSERT INTO t1 VALUES (2);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 1;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (2)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
INSERT INTO t1 VALUES (3);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (2)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (3)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (2)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (3)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 1;
INSERT INTO t1 VALUES (1);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (1)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
SET AUTOCOMMIT = 1;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (1)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 1;
BEGIN;
INSERT INTO t1 VALUES (2);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 1;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
INSERT INTO t1 VALUES (3);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (2)
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (3)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 0;
INSERT INTO t1 VALUES (1);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 0;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 0;
BEGIN;
INSERT INTO t1 VALUES (2);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 0;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
INSERT INTO t1 VALUES (3);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (2)
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (3)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 1;
INSERT INTO t1 VALUES (1);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (1)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
SET AUTOCOMMIT = 0;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (1)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 1;
BEGIN;
INSERT INTO t1 VALUES (2);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 0;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
INSERT INTO t1 VALUES (3);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (2)
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (3)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
SET BINLOG_FORMAT = ROW;
RESET MASTER;
SET AUTOCOMMIT = 0;
INSERT INTO t1 VALUES (1);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 1;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 0;
BEGIN;
INSERT INTO t1 VALUES (2);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 1;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
INSERT INTO t1 VALUES (3);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 1;
INSERT INTO t1 VALUES (1);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
SET AUTOCOMMIT = 1;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 1;
BEGIN;
INSERT INTO t1 VALUES (2);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 1;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
INSERT INTO t1 VALUES (3);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 0;
INSERT INTO t1 VALUES (1);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 0;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 0;
BEGIN;
INSERT INTO t1 VALUES (2);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 0;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
INSERT INTO t1 VALUES (3);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 1;
INSERT INTO t1 VALUES (1);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
SET AUTOCOMMIT = 0;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 1;
BEGIN;
INSERT INTO t1 VALUES (2);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
SET AUTOCOMMIT = 0;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
INSERT INTO t1 VALUES (3);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
RESET MASTER;
SET AUTOCOMMIT = 0;
INSERT INTO t1 VALUES (1);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
LOCK TABLES t1 WRITE;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
INSERT INTO t1 VALUES (2);
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
UNLOCK TABLES;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
DROP TABLE t1;