List:Commits« Previous MessageNext Message »
From:Andrei Elkin Date:March 14 2008 9:03am
Subject:Re: bk commit into 5.1 tree (svoj:1.2558) BUG#34768
View as plain text  
Sergey,

Actually, I have not found how do we test a stored function or a
trigger having a order-by statement. We are better to have a demo of
that it works safely.

Andrei

> 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);
Thread
bk commit into 5.1 tree (svoj:1.2558) BUG#34768Sergey Vojtovich13 Mar
  • Re: bk commit into 5.1 tree (svoj:1.2558) BUG#34768Andrei Elkin14 Mar
  • Re: bk commit into 5.1 tree (svoj:1.2558) BUG#34768Andrei Elkin14 Mar