Mysql 警告代码 1592 使用语句格式写入二进制日志的不安全语句

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.

我不明白上面的错误信息。以下是涉及的语句/表。

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

您大概熟悉two formats of binary logging ，基于语句的——记录修改主服务器上数据的实际查询，以便它们可以在从服务器上执行，以及基于行的——记录实际行数据的之前和/或之后的图像由查询更改，以便从站可以直接将这些更改应用于其数据......和混合模式，优化器和存储引擎在逐个查询的基础上确定哪种格式是最佳格式。

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>}

您正在执行的语句是不安全的原则上，因为您将 INSERT ... SELECT 用于具有自动增量列的表中。如果在基于 STATEMENT 的环境中使用了该通用形式的查询，并且 SELECT 没有以相同的顺序返回行master 和 slave，可以以不同的顺序选择行，因此最终得到不同的自动增量值。

实际上，您正在执行的特定查询是确定性的，因为您只插入一行，并且明确指定了自动增量值。我怀疑这是你困惑的原因。但是，您似乎仍在触发警告，因为您正在将 INSERT ... SELECT 放入具有自动增量的表中，并且服务器似乎正在应用广义的“不安全”确定将查询作为原则问题而不是精度问题。

将您的 binlog_format 切换为 MIXED 应该会使警告消失，因为服务器可以自行决定切换模式......并且不太可能产生负面影响.如果不是因为 STATEMENT 一直是默认值(因为最初那是唯一可用的复制)，我怀疑他们会将 MIXED 设为默认值很久以前...事实上，如果您熟悉二进制日志的内部结构，您可能会像我一样倾向于在几乎所有内容上使用 ROW... 它往往制作一个更有用的二进制日志来排除故障并让自己摆脱麻烦，因为“旧”行数据记录在 DELETE 和 UPDATE 上。