MySQL Bugs: #72921: note 1592: unsafe statement...on duplicate key update issued for safe statements

Bug #72921	note 1592: unsafe statement...on duplicate key update issued for safe statements
Submitted:	9 Jun 2014 17:10	Modified:	10 Jun 2014 14:53
Reporter:	Jeff Marr	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S4 (Feature request)
Version:	5.5+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	insert on duplicate key update, replication, unsafe

Description:
The "Note 1592: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe" warning is given even when the statement only specifies a single key within the insert values, in which case this warning does not apply. This unnecessarily bloats error logs and raises false error conditions within client-code when warnings are treated as errors.

This erroneous behavior seems to have been introduced in this related bug report. Note that other users are also complaining about this issue:
http://bugs.mysql.com/bug.php?id=58637

How to repeat:
CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=MyISAM AUTO_INCREMENT=45036 DEFAULT CHARSET=utf8;

INSERT INTO wp_options(
    option_name
   ,option_value
   ,autoload
) VALUES (
    'myuniquename'
   ,'blahblahblah'
   ,'yes'
) ON DUPLICATE KEY UPDATE
    option_id = LAST_INSERT_ID(option_id)
   ,option_value = VALUES(option_value)
   ,autoload = VALUES(autoload)
;

SHOW WARNINGS;

Suggested fix:
This warning should not be shown when only one unique key is given in the insert statement.

C:\dbs>net start mysqld55
The MySQLD55 service is starting.
The MySQLD55 service was started successfully.

C:\dbs>55

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.38-log Source distribution

Copyright (c) 2000, 2014, 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 5.5 > drop database dd;
Query OK, 1 row affected (0.11 sec)

mysql 5.5 > CREATE DATABASE dd;
Query OK, 1 row affected (0.01 sec)

mysql 5.5 > USE dd
Database changed
mysql 5.5 > SET GLOBAL binlog_format = 'STATEMENT';
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 > CREATE TABLE `wp_options` (
    ->   `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `option_name` varchar(64) NOT NULL DEFAULT '',
    ->   `option_value` longtext NOT NULL,
    ->   `autoload` varchar(20) NOT NULL DEFAULT 'yes',
    ->   PRIMARY KEY (`option_id`),
    ->   UNIQUE KEY `option_name` (`option_name`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=45036 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.27 sec)

mysql 5.5 > INSERT INTO wp_options(
    ->     option_name
    ->    ,option_value
    ->    ,autoload
    -> ) VALUES (
    ->     'myuniquename'
    ->    ,'blahblahblah'
    ->    ,'yes'
    -> ) ON DUPLICATE KEY UPDATE
    ->     option_id = LAST_INSERT_ID(option_id)
    ->    ,option_value = VALUES(option_value)
    ->    ,autoload = VALUES(autoload)
    -> ;
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql 5.5 > SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe
1 row in set (0.00 sec)

Thank you for the bug report.

I am trying to understand  if this bug is affecting database where every single database table does have a PRIMARY KEY. Why does mysql choose arbitrarily the key to work with? Shouldn't it always check the PRIMARY KEY of  a table for uniqueness? After all that is what PRIMARY KEYS are all about, "Uniquely Identifying Records". 

1) Is this warning safe to ignore, if you have only one unique key on the table and it is the PRIMARY KEY? 

2) What does happen when there are more than one unique keys on the table from which one is the PRIMARY KEY? 

Case 1 might be safe, as the database doesn't have a choice. Case 2 might bi dodgy? Non-deterministic? Is that  why it is not safe? I thought the database will trust only the PRIMARY KEY, why does it even consider the others? 

Anyone any opinion on this? 

Many thanks

Kubilay Tsil Kara