MySQL Bugs: #61061: Unsafe updates written to binlog in STATEMENT format when binlog

Bug #61061	Unsafe updates written to binlog in STATEMENT format when binlog_format=MIXED
Submitted:	4 May 2011 17:32	Modified:	9 Jul 2013 17:59
Reporter:	Chris Calender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.56, 5.5.10, 5.6.3	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	auto_increment, binlog_format, mixed, row-based, unsafe

Description:
Unsafe updates/inserts to tables with an AUTO_INCREMENT column are written to the binary log in STATEMENT format when binlog_format=MIXED.

On the following page in the manual:

http://dev.mysql.com/doc/refman/5.1/en/replication-rbr-safe-unsafe.html

It says:

"If the binary logging format is MIXED, statements flagged as unsafe are logged using the row-based format; statements regarded as safe are logged using the statement-based format."

It later goes on to say:

"Statements considered unsafe.   Statements having the following characteristics are considered unsafe:
...
Updates a table having an AUTO_INCREMENT column.  This is unsafe because the order in which the rows are updated may differ on the master and the slave.
For more information, see Section 15.4.1.1, “Replication and AUTO_INCREMENT”."

Based upon those 2 statements, I'd expect any UPDATE or INSERT to a table which has an AUTO_INCREMENT column to be considered "unsafe".  Hence if one has binlog_format=MIXED, then any such statement should be written to the binary log in the row-based format, as opposed to the statement-based format.

However, this is not what occurs, nor is a warning issued.

How to repeat:
1. In config file, ensure "log_bin" is enabled and that "binlog_format=MIXED".

2. Run following statements:

create table t (id int not null auto_increment, primary key (id)) engine=innodb;
insert into t values (null),(null);
insert into t (select rand());
update t set id=4 where id=3;

3. Now, observe binlog output from those 4 statements (you can see only the 3rd statement is written in the row-based format, since it calls rand()):

> mysqlbinlog Chris-bin.000003
...
# at 314
#110503 19:12:24 server id 1  end_log_pos 456   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1304464344/*!*/;
create table t (id int not null auto_increment, primary key (id)) engine=innodb
/*!*/;
# at 552
#110503 19:12:35 server id 1  end_log_pos 649   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1304464355/*!*/;
insert into t values (null),(null)
# at 784
#110503 19:12:50 server id 1  end_log_pos 784   Table_map: `test`.`t` mapped to number 53
#110503 19:12:50 server id 1  end_log_pos 818   Write_rows: table id 53 flags: STMT_END_F

BINLOG '
8ovATRMBAAAAKAAAABADAAAAADUAAAAAAAEABHRlc3QAAXQAAQMAAA==
8ovATRcBAAAAIgAAADIDAAAAADUAAAAAAAEAAf/+AwAAAA==
'/*!*/;
# at 913
#110503 19:13:28 server id 1  end_log_pos 1004  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1304464408/*!*/;
update t set id=4 where id=3
/*!*/;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Suggested fix:
Treat updates to tables with an auto_increment column as "unsafe" (which is correct, btw), just as the manual describes.

Thank you for the report.

Verified as described.

We discussed this bug internally and found this is actually documentation issue after fix of bug #50192

The fix for BUG#50192 was never pushed to 5.1, AFAICT. So the 5.1 documentation should be correct...?

@Jon:

The 5.1 documentation is not correct, at least not in regards to this specific bug I opened.  Note the page I quoted is from the 5.1 version of the manual.  Furthermore, it was noted "Verified as described".

@Sveta:

I don't see how "this is actually documentation issue after fix of bug #50192".  Or in other words, that bug (#50192) only deals with autoincrement updates/inserts occurring via a trigger or stored function.  However, this bug I opened (#61061) simply refers to the the normal case, no triggers or stored functions involved, and it does not appear to have been addressed by anything (at least that I can read) in that bug report.

Chris,

actually INSERT/UPDATEs on AUTO_INC column in top level statement are safe. So behavior described here is correct, documentation is wrong. They became to be safe after fix of bug #50192

Hi Sveta,

Thanks for the clarification!  I'll be glad to see this updated in the manual .. it'll make this issue much more clear.

Thanks again, and best wishes :)

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Fixed in mysqldoc rev 35610, Closed.