MySQL binlog 为 statement 格式考虑是不安全（slave端不一致）的操作

Statements considered unsafe.  Statements with the following characteristics are considered unsafe:

• Statements containing system functions that may return a different value on the slave.  These functions include FOUND_ROWS(), GET_LOCK(), IS_FREE_LOCK(), IS_USED_LOCK(), LOAD_FILE(), MASTER_POS_WAIT(),RAND(), RELEASE_LOCK(), ROW_COUNT(), SESSION_USER(), SLEEP(), SYSDATE(), SYSTEM_USER(), USER(),UUID(), and UUID_SHORT().
  Nondeterministic functions not considered unsafe.  Although these functions are not deterministic, they are treated as safe for purposes of logging and replication: CONNECTION_ID(), CURDATE(), CURRENT_DATE(),CURRENT_TIME(), CURRENT_TIMESTAMP(), CURTIME(),, LAST_INSERT_ID(), LOCALTIME(),LOCALTIMESTAMP(), NOW(), UNIX_TIMESTAMP(), UTC_DATE(), UTC_TIME(), and UTC_TIMESTAMP().For more information, see Section 17.5.1.14, “Replication and System Functions”.
• References to system variables.  Most system variables are not replicated correctly using the statement-based format. See Section 17.5.1.38, “Replication and Variables”. For exceptions, see Section 5.4.4.3, “Mixed Binary Logging Format”.
• UDFs.  Since we have no control over what a UDF does, we must assume that it is executing unsafe statements.
• Fulltext plugin.  This plugin may behave differently on different MySQL servers; therefore, statements depending on it could have different results. For this reason, all statements relying on the fulltext plugin are treated as unsafe in MySQL.
• Trigger or stored program updates a table having an AUTO_INCREMENT column.  This is unsafe because the order in which the rows are updated may differ on the master and the slave.In addition, an INSERT into a table that has a composite primary key containing an AUTO_INCREMENT column that is not the first column of this composite key is unsafe.For more information, see Section 17.5.1.1, “Replication and AUTO_INCREMENT”.
• INSERT … ON DUPLICATE KEY UPDATE statements on tables with multiple primary or unique keys.  When executed against a table that contains more than one primary or unique key, this statement is considered unsafe, being sensitive to the order in which the storage engine checks the keys, which is not deterministic, and on which the choice of rows updated by the MySQL Server depends.
  An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is marked as unsafe for statement-based replication. (Bug #11765650, Bug #58637)
• Updates using LIMIT.  The order in which rows are retrieved is not specified, and is therefore considered unsafe. See Section 17.5.1.18, “Replication and LIMIT”.
• Accesses or references log tables.  The contents of the system log table may differ between master and slave.
• Nontransactional operations after transactional operations.  Within a transaction, allowing any nontransactional reads or writes to execute after any transactional reads or writes is considered unsafe.For more information, see Section 17.5.1.34, “Replication and Transactions”.
• Accesses or references self-logging tables.  All reads and writes to self-logging tables are considered unsafe. Within a transaction, any statement following a read or write to self-logging tables is also considered unsafe.
• LOAD DATA statements.  LOAD DATA is treated as unsafe and when binlog_format=MIXED the statement is logged in row-based format. When binlog_format=STATEMENT LOAD DATA does not generate a warning, unlike other unsafe statements.
• XA transactions.  If two XA transactions committed in parallel on the master are being prepared on the slave in the inverse order, locking dependencies can occur with statement-based replication that cannot be safely resolved, and it is possible for replication to fail with deadlock on the slave. When binlog_format=STATEMENT is set, DML statements inside XA transactions are flagged as being unsafe and generate a warning. Whenbinlog_format=MIXED or binlog_format=ROW is set, DML statements inside XA transactions are logged using row-based replication, and the potential issue is not present.
• DEFAULT clause that refers to a nondeterministic function.  If an expression default value refers to a nondeterministic function, any statement that causes the expression to be evaluated is unsafe for statement-based replication. This includes statements such as INSERT, UPDATE, and ALTER TABLE. Unlike most other unsafe statements, this category of statement cannot be replicated safely in row-based format. When binlog_format is set to STATEMENT, the statement is logged and executed but a warning message is written to the error log. When binlog_format is set to MIXED or ROW, the statement is not executed and an error message is written to the error log. For more information on the handling of explicit defaults, see Handling of Explicit Defaults as of MySQL 8.0.13.

以下为Google翻译版本：

• 包含系统函数的语句，这些函数可能在从属服务器上返回不同的值。  这些功能包括 FOUND_ROWS()， GET_LOCK()， IS_FREE_LOCK()， IS_USED_LOCK()， LOAD_FILE()， MASTER_POS_WAIT()， RAND()， RELEASE_LOCK()， ROW_COUNT()， SESSION_USER()， SLEEP()， SYSDATE()， SYSTEM_USER()， USER()， UUID()，和 UUID_SHORT()。
  非确定性功能不被认为是不安全的。  虽然这些功能是不确定的，它们被视为安全的记录和复制的目的： CONNECTION_ID()， CURDATE()， CURRENT_DATE()， CURRENT_TIME()， CURRENT_TIMESTAMP()， CURTIME()，， ， LAST_INSERT_ID()， LOCALTIME()， LOCALTIMESTAMP()， NOW()， UNIX_TIMESTAMP()， UTC_DATE()， UTC_TIME()和 UTC_TIMESTAMP()。有关更多信息，请参见 第17.5.1.14节“复制和系统功能”。
• 对系统变量的引用。  使用基于语句的格式不能正确复制大多数系统变量。请参见 第17.5.1.38节“复制和变量”。有关例外，请参见第5.4.4.3节“混合二进制日志记录格式”。
• UDF。  由于我们无法控制UDF的工作，因此我们必须假定它正在执行不安全的语句。
• 全文插件。  该插件在不同的MySQL服务器上的行为可能有所不同。因此，取决于它的语句可能会有不同的结果。因此，在MySQL中，所有依赖于全文插件的语句都被视为不安全。
• 触发器或存储的程序将更新具有AUTO_INCREMENT列的表。  这是不安全的，因为在主服务器和从服务器上，行的更新顺序可能不同。此外，INSERT到包含复合主键的表中的表AUTO_INCREMENT是不安全的，该主键包含的 列不是该复合键的第一列。有关更多信息，请参见 第17.5.1.1节“复制和AUTO_INCREMENT”。
• 对具有多个主键或唯一键的表执行INSERT … ON DUPLICATE KEY UPDATE语句。  当对包含多个主键或唯一键的表执行该语句时，该语句被认为是不安全的，它对存储引擎检查键的顺序（不确定的）以及由行更新的行的选择敏感。 MySQL Server取决于。
  INSERT ... ON DUPLICATE KEY UPDATE针对具有多个唯一键或主键的表 的 语句对于基于语句的复制被标记为不安全。（缺陷＃11765650，错误＃58637）
• 使用LIMIT更新。  未指定行的检索顺序，因此被认为是不安全的。请参见 第17.5.1.18节“复制和限制”。
• 访问或引用日志表。  主服务器和从服务器之间的系统日志表的内容可能有所不同。
• 交易操作之后的非交易操作。  在事务内，允许任何非事务性读取或写入在任何事务性读取或写入之后执行是不安全的。有关更多信息，请参见 第17.5.1.34节“复制和事务”。
• 访问或引用自记录表。  对自记录表的所有读取和写入均被视为不安全。在事务中，对自记录表进行读取或写入之后的任何语句也被认为是不安全的。
• LOAD DATA语句。  LOAD DATA当binlog_format=MIXED该语句以基于行的格式记录时，被视为不安全 。与其他不安全的语句不同，何时 不生成警告。 binlog_format=STATEMENT LOAD DATA
• XA事务。  如果正在从服务器上以相反的顺序准备在主服务器上并行提交的两个XA事务，则无法安全解决的基于语句的复制可能会发生锁定依赖性，并且复制可能会因从服务器上的死锁而失败。当 binlog_format=STATEMENT 设置，XA事务内部DML语句被标记为不安全的，并产生一个警告。当 binlog_format=MIXED或 binlog_format=ROW设置，XA事务内的DML语句使用基于行的复制记录，以及潜在的问题是不存在的。
• DEFAULT子句指的是不确定性函数。  如果表达式默认值引用的是不确定函数，则任何导致对该表达式求值的语句对于基于语句的复制都是不安全的。这包括语句，如 INSERT， UPDATE和 ALTER TABLE。与大多数其他不安全的语句不同，此类语句不能以基于行的格式安全地复制。当 binlog_format设置为时 STATEMENT，将记录并执行该语句，但是将警告消息写入错误日志。何时binlog_format 设置为MIXED或 ROW，则不会执行该语句，并且会将错误消息写入错误日志。有关处理显式默认值的更多信息，请参见 从MySQL 8.0.13开始的显式默认值的处理。