Bug #35135 5.1 document incorrect about binlog in trigger generated AUTO_INCREMENT
Submitted: 7 Mar 2008 6:48 Modified: 10 Mar 2008 17:13
Reporter: Zhenxing He Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Tags: auto_increment, binlog

[7 Mar 2008 6:48] Zhenxing He
Description:
In the 5.1 document, 15.3.1.1. Replication and AUTO_INCREMENT:
      http://dev.mysql.com/doc/refman/5.1/en/replication-features-autoincid.html
...
When a trigger causes an INSERT into an AUTO_INCREMENT column, the generated AUTO_INCREMENT value is not written into the binary log, so a different value can in some cases be inserted on the slave.
...

This statement is not correct since version 5.1.12, the generated AUTO_INCREMENT value is written into the binary log for both funtions and triggers. 

How to repeat:
Run the following test case on the latest mysql-5.1.23:

------------------------------------------------------
create table t1 (id int auto_increment primary key);
create table t2 (id int auto_increment primary key);

delimiter //;

CREATE TRIGGER tr1 BEFORE DELETE
    ON t1 FOR EACH ROW 
    BEGIN
        INSERT INTO t2  VALUES (NULL);
    END//

delimiter ;//
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (NULL);
DELETE FROM t1 WHERE id = 1;
show binlog events;
-------------------------------------------------------

The following log events will be written for the last three statements:

 INSERT_ID=1
 use `test`; INSERT INTO t1 VALUES (NULL)
 INSERT_ID=2
 use `test`; INSERT INTO t1 VALUES (NULL)
 INSERT_ID=1
 use `test`; DELETE FROM t1 WHERE id = 1

Suggested fix:
Correct the document by remove this line.
[7 Mar 2008 6:53] Jon Stephens
Assigning to myself for investigation/resolution.
[7 Mar 2008 14:25] Jon Stephens
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.
[10 Mar 2008 14:31] Zhenxing He
....
Prior to MySQL 5.1.12, when a trigger caused an INSERT into an AUTO_INCREMENT column, the generated AUTO_INCREMENT value was not written into the binary log, so a different value could in some cases be inserted on the slave.
...

This applies to  functions too, so we should replace  'a trigger' with ' a trigger or a function' in the above text.
[10 Mar 2008 17:13] Jon Stephens
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.