问题描述
我不明白上面的错误消息.以下是涉及的语句/表.
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.
通常，如果语句是确定性的，则它是安全的；如果不是确定性的，则它是不安全的.
In general, a statement is safe if it deterministic, and unsafe if it is not.
您正在执行的语句原则上是不安全的 ，因为您正在将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使用语句格式将不安全语句写入二进制日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！