Mysql Warning Code 1592 Unsafe statement written to the binary log using statement format

Question

Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

I don't understand the above error message. Below are the statements/tables involved.

mysql> show create table phppos_app_config;
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                  |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_app_config | CREATE TABLE `phppos_app_config` (
  `key` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `value` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> \W
Show warnings enabled.
mysql> CREATE TABLE IF NOT EXISTS `phppos_locations` (
    ->   `location_id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` text COLLATE utf8_unicode_ci,
    ->   `address` text COLLATE utf8_unicode_ci,
    ->   `phone` text COLLATE utf8_unicode_ci,
    ->   `fax` text COLLATE utf8_unicode_ci,
    ->   `email` text COLLATE utf8_unicode_ci,
    ->   `receive_stock_alert` text COLLATE utf8_unicode_ci,
    ->   `stock_alert_email` text COLLATE utf8_unicode_ci,
    ->   `return_policy` text COLLATE utf8_unicode_ci,
    ->   `timezone` text COLLATE utf8_unicode_ci,
    ->   `mailchimp_api_key` text COLLATE utf8_unicode_ci,
    ->   `enable_credit_card_processing` text COLLATE utf8_unicode_ci,
    ->   `merchant_id` text COLLATE utf8_unicode_ci,
    ->   `merchant_password` text COLLATE utf8_unicode_ci,
    ->   `default_tax_1_rate` text COLLATE utf8_unicode_ci,
    ->   `default_tax_1_name` text COLLATE utf8_unicode_ci,
    ->   `default_tax_2_rate` text COLLATE utf8_unicode_ci,
    ->   `default_tax_2_name` text COLLATE utf8_unicode_ci,
    ->   `default_tax_2_cumulative` text COLLATE utf8_unicode_ci,
    ->   `deleted` int(1) DEFAULT '0',
    ->   PRIMARY KEY (`location_id`),
    ->   KEY `deleted` (`deleted`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
Query OK, 0 rows affected (0.02 sec)

mysql> -- -------------------------------------------------
mysql> -- Migrate app config to location ---
mysql> -- -------------------------------------------------
mysql> 
mysql> INSERT INTO `phppos_locations` (`location_id`, `name`, `address`, `phone`, `fax`, `email`,
    -> `receive_stock_alert`, `stock_alert_email`, `return_policy`, `timezone`, `mailchimp_api_key`,
    ->  `enable_credit_card_processing`, `merchant_id`, `merchant_password`, `default_tax_1_rate`,
    -> `default_tax_1_name`,`default_tax_2_rate`, `default_tax_2_name`, `default_tax_2_cumulative`) VALUES(
    -> 1,
    -> 'Default',
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'address'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'phone'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'fax'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'email'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'receive_stock_alert'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'stock_alert_email'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'return_policy'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'timezone'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'mailchimp_api_key'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'enable_credit_card_processing'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'merchant_id'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'merchant_password'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'default_tax_1_rate'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'default_tax_1_name'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'default_tax_2_rate'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'default_tax_2_name'),
    -> (SELECT `value` FROM phppos_app_config WHERE `key` = 'default_tax_2_cumulative')
    -> );
Query OK, 1 row affected, 1 warning (0.00 sec)

Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

Answer 1

You are presumably familiar with the two formats of binary logging, statement-based -- which logs the actual queries that modify data on the master so that they can be executed on the slave, and row-based -- which logs before- and/or after-images of the actual row data that was changed by the query, so that the slave can directly apply those changes to its data... and mixed-mode, where the optimizer and the storage engine determine which format is the optimal format on a query-by-query basis.

When speaking of the “safeness” of a statement in MySQL Replication, we are referring to whether a statement and its effects can be replicated correctly using statement-based format. If this is true of the statement, we refer to the statement as safe; otherwise, we refer to it as unsafe.

In general, a statement is safe if it deterministic, and unsafe if it is not.

^{— http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html>}

The statement you are executing is unsafe in principle because you are using INSERT ... SELECT into a table with an auto-increment column. If a query of that general form were used in a STATEMENT-based environment, and the SELECT did not return the rows in the same order on master and slave, the rows could be selected in a different order, and thus end up with different auto-increment values.

In practice, the specific query you're executing is deterministic because you're only inserting one row, and you're explicitly specifying the auto-increment value. I suspect that's the cause of your confusion. However, it appears you're still triggering the warning because you're doing INSERT ... SELECT into a table with an auto-increment, and the server appears to be applying the generalized "unsafe" determination to the query as a matter of principle, rather than precision.

Switching your binlog_format to MIXED should make the warning go away, since the server can switch modes at its discretion... and is very unlikely to have negative side effects. If it were not for the fact that STATEMENT has always been the default (since initially that was the only kind of replication available), I suspect they would have made MIXED the default long ago... in fact, if you familiarize yourself with the internals of binary logs, you'd probably be inclined to do as I do and use ROW on just about everything... it tends to make for a much more useful binary log for troubleshooting and backing yourself out of trouble, because the "old" row data is logged on DELETE and UPDATE.