MySQL Bugs: #63677: Statement is incorrectly tagged as unsafe with triggers and auto increments

Bug #63677	Statement is incorrectly tagged as unsafe with triggers and auto increments
Submitted:	8 Dec 2011 17:50	Modified:	9 Dec 2011 17:35
Reporter:	Stephane Bakhos	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.5.17, 5.5.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	replication unsafe statement auto_increment

Description:
The following error

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. 

This erroneously occurs when DELETE statements happen inside the trigger.

How to repeat:
Create some tables:

CREATE TABLE `u1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `dummy` int(11) unsigned NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

SET GLOBAL BINLOG_FORMAT=statement;

CREATE TABLE `d1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `dummy` int(11) unsigned NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

DELIMITER $$
CREATE definer=root@localhost TRIGGER test AFTER UPDATE ON u1
FOR EACH ROW BEGIN
    DELETE FROM d1 WHERE id = NEW.id;
END
$$
DELIMITER ;
insert into u1 values (1,1);
insert into d1 values (1,1);

UPDATE u1 SET dummy = 0 WHERE id = 1; <-- this says it is unsafe because of the trigger.

Suggested fix:
Only count INSERT / REPLACE statements for the autoincrement check.
Also it would be nice if they are counted only if they get executed. It is possible with IF blocks to create triggers that will only sometime insert.

Thank you for the problem report. Verified just as described:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.17-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `u1` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `dummy` int(11) unsigned NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.19 sec)

mysql> 
mysql> SET GLOBAL BINLOG_FORMAT=statement;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE TABLE `d1` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `dummy` int(11) unsigned NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.23 sec)

mysql> 
mysql> DELIMITER $$
mysql> CREATE definer=root@localhost TRIGGER test AFTER UPDATE ON u1
    -> FOR EACH ROW BEGIN
    ->     DELETE FROM d1 WHERE id = NEW.id;
    -> END
    -> $$
Query OK, 0 rows affected (1.64 sec)

mysql> DELIMITER ;
mysql> insert into u1 values (1,1);
Query OK, 1 row affected (0.24 sec)

mysql> insert into d1 values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE u1 SET dummy = 0 WHERE id = 1; 
Query OK, 1 row affected, 1 warning (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.
1 row in set (0.00 sec)