MySQL Bugs: #64758: Error 1592 is not selective enough

Bug #64758	Error 1592 is not selective enough
Submitted:	25 Mar 2012 15:07	Modified:	25 Mar 2012 15:32
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5.20, 5.5.22	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Error 1592 (Unsafe statement written to the binary log using statement format) is too sensitive. It warns in cases where there is no danger of an unpredictable result.

How to repeat:
mysql> create table test(a int not null auto_increment primary key);
mysql> create table test2 like test;
mysql> insert into test(a) values(1);
mysql> create trigger tr after insert on test for each row replace into test2(a) values(new.a);
mysql> insert into test(a) values(2);
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> show warnings;
| Note  | 1592 | 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. | 

In this case the value of the auto_increment column is explicitly specified. Such a warning should be raised only when really needed.

Thank you for the bug report. Verified with 5.5.22 also:

macbook-pro:mysql-5.5.22-osx10.5-x86_64 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log MySQL Community Server (GPL)

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 test(a int not null auto_increment primary key);
Query OK, 0 rows affected (0.18 sec)

mysql> create table test2 like test;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into test(a) values(1);
Query OK, 1 row affected (0.02 sec)

mysql> create trigger tr after insert on test for each row replace into test2(a)    -> values(new.a);
Query OK, 0 rows affected (0.19 sec)

mysql> insert into test(a) values(2);
Query OK, 1 row affected, 1 warning (0.02 sec)

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)