MySQL Bugs: #69450: Unnecessary replication warning messages in error logs

Bug #69450	Unnecessary replication warning messages in error logs
Submitted:	12 Jun 2013 13:13	Modified:	21 Jul 2014 19:09
Reporter:	George Kaz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5.29	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	logging, replication

Description:
With reference to closed bug #58637

Although I appreciate the reason for the warning being added, in the case of only specifying a single key within the insert values (the other key being an auto-increment field), this warning is unnecessary. In my case, it's bloating my error logs.

How to repeat:
Example of when this implements wrongly (in this case a wordpress table but the problem is generic)

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', 'autoload') ON DUPLICATE KEY UPDATE

The above produces an unsafe statement warning when it's not unsafe because the only key being referenced in the insert is option_name.

Suggested fix:
Check that multiple key fields are referenced in the insert statement or allow suppression of this type of warning completely or per table.

Thank you for the bug report. Please provide a complete test case your INSERT...ON DUPLICATE KEY UPDATE is incomplete . Thanks.

Thanks for the reply. Here's one example from my err log. There are 1000s of these, all of the same format.

130408  9:18:21 [Warning] 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 Statement: INSERT INTO `smwp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_site_transient_timeout_theme_roots', '1365410901', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)

Note on my last reply. The table name is different from the one I gave in the original create statement but the table schema is the same. The error relates to the same table; the difference is only in what I have entered on this bug thread.

Thank you for the report.

> INSERT INTO wp_options(option_name, option_value, autoload) Values ('myuniquename', 'blahblahblah', 'autoload') ON DUPLICATE KEY UPDATE
>
> The above produces an unsafe statement warning when it's not unsafe because the only key being referenced in the insert is option_name.

It does not matter how many key you are referencing in the INSERT statement. It is still unsafe and can lead to unpredictable result if your table has more than 1 unique key. And your table has two.

Closing as "Not a Bug".