BUG#34768 - nondeterministic INSERT using LIMIT logged in stmt mode if

            binlog_format=mixed

Statement-based replication of DELETE ... LIMIT, UPDATE ... LIMIT,
INSERT ... SELECT ... LIMIT is not safe as order of rows is not
defined.

With this fix, we issue a warning that this statement is not safe to
replicate in statement mode, or go to row-based mode in mixed mode.

Note that we may consider a statement as safe if ORDER BY primary_key
is present. However it may confuse users to see very similiar statements
replicated differently.

Note 2: regular UPDATE statement (w/o LIMIT) is unsafe as well, but
this patch doesn't address this issue. See comment from Kristian
posted 18 Mar 10:55.


mysql-test/suite/binlog/r/binlog_stm_ps.result:
  Updated a test case according to fix for BUG#34768:
  INSERT ... SELECT ... LIMIT is now replicated in row mode.
mysql-test/suite/binlog/r/binlog_unsafe.result:
  A test case for BUG#34768.
mysql-test/suite/binlog/t/binlog_unsafe.test:
  A test case for BUG#34768.
sql/sql_delete.cc:
  Statement-based replication of DELETE ... LIMIT is not safe as order of
  rows is not defined, so in mixed mode we go to row-based.
sql/sql_insert.cc:
  Statement-based replication of INSERT ... SELECT ... LIMIT is not safe
  as order of rows is not defined, so in mixed mode we go to row-based.
sql/sql_update.cc:
  Statement-based replication of UPDATE ... LIMIT is not safe as order of
  rows is not defined, so in mixed mode we go to row-based.

1 files changed, 13 insertions, 0 deletions

diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 2be932a6040..58acf40964b 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -2764,6 +2764,19 @@ bool mysql_insert_select_prepare(THD *thd)
   DBUG_ENTER("mysql_insert_select_prepare");
 
   /*
+    Statement-based replication of INSERT ... SELECT ... LIMIT is not safe
+    as order of rows is not defined, so in mixed mode we go to row-based.
+
+    Note that we may consider a statement as safe if ORDER BY primary_key
+    is present or we SELECT a constant. However it may confuse users to
+    see very similiar statements replicated differently.
+  */
+  if (lex->current_select->select_limit)
+  {
+    lex->set_stmt_unsafe();
+    thd->set_current_stmt_binlog_row_based_if_mixed();
+  }
+  /*
     SELECT_LEX do not belong to INSERT statement, so we can't add WHERE
     clause if table is VIEW
   */