Sergey, hello.
The patch is okay. I only have a suggestion wrt to comments.
> 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.
>
ok
I think we are better to mention a possible inefficiency side effect
in the future doc line. In order to help to a documenter
I would suggest to put down comments from a one of the following hunks
right here in the cset comments.
> + 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.
Could you please mention other forms of *deterministic* order-by like
insert select constant.
cheers,
Andrei
> 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);
>
