bk commit into 5.1 tree (svoj:1.2558) BUG#34768

[Sergey Vojtovich]

Below is the list of changes that have just been committed into a local
5.1 repository of svoj.  When svoj does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-03-13 12:47:24+04:00, svoj@stripped +6 -0
  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.

  mysql-test/suite/binlog/r/binlog_stm_ps.result@stripped, 2008-03-13 12:47:21+04:00,
svoj@stripped +2 -1
    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@stripped, 2008-03-13 12:47:21+04:00,
svoj@stripped +14 -0
    A test case for BUG#34768.

  mysql-test/suite/binlog/t/binlog_unsafe.test@stripped, 2008-03-13 12:47:21+04:00,
svoj@stripped +10 -1
    A test case for BUG#34768.

  sql/sql_delete.cc@stripped, 2008-03-13 12:47:21+04:00, svoj@stripped +13 -0
    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@stripped, 2008-03-13 12:47:21+04:00, svoj@stripped +13 -0
    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@stripped, 2008-03-13 12:47:21+04:00, svoj@stripped +13 -0
    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.

diff -Nrup a/mysql-test/suite/binlog/r/binlog_stm_ps.result
b/mysql-test/suite/binlog/r/binlog_stm_ps.result
--- a/mysql-test/suite/binlog/r/binlog_stm_ps.result	2007-06-27 17:27:24 +05:00
+++ b/mysql-test/suite/binlog/r/binlog_stm_ps.result	2008-03-13 12:47:21 +04:00
@@ -16,5 +16,6 @@ master-bin.000001	#	Query	#	#	use `test`
 master-bin.000001	#	User var	#	#	@`a`=98
 master-bin.000001	#	Query	#	#	use `test`; insert into t1 values (@a),(98)
 master-bin.000001	#	Query	#	#	use `test`; insert into t1 values (99)
-master-bin.000001	#	Query	#	#	use `test`; insert into t1 select 100 limit 100
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
 drop table t1;
diff -Nrup a/mysql-test/suite/binlog/r/binlog_unsafe.result
b/mysql-test/suite/binlog/r/binlog_unsafe.result
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result	2008-01-11 16:27:13 +04:00
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result	2008-03-13 12:47:21 +04:00
@@ -11,3 +11,17 @@ Level	Warning
 Code	1592
 Message	Statement is not safe to log in statement format.
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b));
+INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+UPDATE t1 SET a=1 LIMIT 1;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+DELETE FROM t1 LIMIT 1;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+DROP TABLE t1;
diff -Nrup a/mysql-test/suite/binlog/t/binlog_unsafe.test
b/mysql-test/suite/binlog/t/binlog_unsafe.test
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test	2008-01-11 16:27:03 +04:00
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test	2008-03-13 12:47:21 +04:00
@@ -15,4 +15,13 @@ query_vertical SHOW WARNINGS;
 
 DROP TABLE t1,t2,t3;
 
-
+#
+# BUG#34768 - nondeterministic INSERT using LIMIT logged in stmt mode if
+#             binlog_format=mixed
+#
+CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b));
+INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
+REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
+UPDATE t1 SET a=1 LIMIT 1;
+DELETE FROM t1 LIMIT 1;
+DROP TABLE t1;
diff -Nrup a/sql/sql_delete.cc b/sql/sql_delete.cc
--- a/sql/sql_delete.cc	2008-02-19 16:45:16 +04:00
+++ b/sql/sql_delete.cc	2008-03-13 12:47:21 +04:00
@@ -418,6 +418,19 @@ bool mysql_prepare_delete(THD *thd, TABL
   DBUG_ENTER("mysql_prepare_delete");
   List<Item> all_fields;
 
+  /*
+    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.
+
+    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.
+  */
+  if (thd->lex->current_select->select_limit)
+  {
+    thd->lex->set_stmt_unsafe();
+    thd->set_current_stmt_binlog_row_based_if_mixed();
+  }
   thd->lex->allow_sum_func= 0;
   if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context,
                                     &thd->lex->select_lex.top_join_list,
diff -Nrup a/sql/sql_insert.cc b/sql/sql_insert.cc
--- a/sql/sql_insert.cc	2008-02-19 16:45:16 +04:00
+++ b/sql/sql_insert.cc	2008-03-13 12:47:21 +04:00
@@ -2764,6 +2764,19 @@ bool mysql_insert_select_prepare(THD *th
   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. 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
   */
diff -Nrup a/sql/sql_update.cc b/sql/sql_update.cc
--- a/sql/sql_update.cc	2008-02-19 17:39:12 +04:00
+++ b/sql/sql_update.cc	2008-03-13 12:47:21 +04:00
@@ -859,6 +859,19 @@ bool mysql_prepare_update(THD *thd, TABL
   SELECT_LEX *select_lex= &thd->lex->select_lex;
   DBUG_ENTER("mysql_prepare_update");
 
+  /*
+    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.
+
+    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.
+  */
+  if (thd->lex->current_select->select_limit)
+  {
+    thd->lex->set_stmt_unsafe();
+    thd->set_current_stmt_binlog_row_based_if_mixed();
+  }
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   table_list->grant.want_privilege= table->grant.want_privilege= 
     (SELECT_ACL & ~table->grant.privilege);