Mysql警告代码1592使用语句格式将不安全语句写入二进制日志 [英] Mysql Warning Code 1592 Unsafe statement written to the binary log using statement format
问题描述
注释(代码1592):由于BINLOG_FORMAT = STATEMENT，因此使用语句格式将不安全的语句写入二进制日志.从另一个表中进行选择后，使用自动递增列写入表的语句是不安全的，因为检索行的顺序决定了要写入的行(如果有).此顺序无法预测，并且主机和从机上的顺序可能不同.
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.
推荐答案
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.
当谈到MySQL复制中语句的安全性"时，我们指的是使用基于语句的格式是否可以正确复制一条语句及其影响.如果该声明是正确的，则我们将该声明称为安全".否则，我们将其称为不安全.
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/zh_/replication-rbr-safe-unsafe.html >
您正在执行的语句原则上是不安全的 ，因为您正在将INSERT ... SELECT用于带有自动增量列的表中.如果在基于STATEMENT的环境中使用该通用形式的查询 ，并且SELECT不在主服务器和从服务器上以相同的顺序返回行，则可以在以下位置选择行:顺序不同，最终得到不同的自动增量值.
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.
实际上，您正在执行的特定于的查询是确定性的，因为您只插入一行，并且显式指定了自动递增值.我怀疑这是您感到困惑的原因.但是，您似乎仍在触发警告，因为您正在对具有自动增量的表执行INSERT ... SELECT，并且原则上服务器似乎对查询应用了通用的不安全"确定，而不是精度.
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.
将binlog_format切换为MIXED应该可以消除警告，因为服务器可以自行决定是否切换模式...并且不太可能产生负面影响.如果不是因为STATEMENT一直是默认值(因为最初是唯一可用的复制类型)，我怀疑他们早就将MIXED设为默认值了……实际上，如果您熟悉二进制日志的内部知识，您可能会倾向于像我一样做，并在所有内容上使用ROW……它倾向于提供更有用的二进制日志，用于进行故障排除和退出自我麻烦，因为旧"行数据记录在DELETE和UPDATE上.
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.
这篇关于Mysql警告代码1592使用语句格式将不安全语句写入二进制日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
