diff options
| -rw-r--r-- | mysql-test/suite/binlog/r/binlog_stm_ps.result | 3 | ||||
| -rw-r--r-- | mysql-test/suite/binlog/r/binlog_unsafe.result | 28 | ||||
| -rw-r--r-- | mysql-test/suite/binlog/t/binlog_unsafe.test | 22 | ||||
| -rw-r--r-- | sql/sql_delete.cc | 13 | ||||
| -rw-r--r-- | sql/sql_insert.cc | 13 | ||||
| -rw-r--r-- | sql/sql_update.cc | 13 |
6 files changed, 90 insertions, 2 deletions
diff --git a/mysql-test/suite/binlog/r/binlog_stm_ps.result b/mysql-test/suite/binlog/r/binlog_stm_ps.result index 47934665116..2128367deb5 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_ps.result +++ b/mysql-test/suite/binlog/r/binlog_stm_ps.result @@ -16,5 +16,6 @@ master-bin.000001 # Query # # use `test`; create table t1 (a int) 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 --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result index 47284ed8bc3..9d7f359ef15 100644 --- a/mysql-test/suite/binlog/r/binlog_unsafe.result +++ b/mysql-test/suite/binlog/r/binlog_unsafe.result @@ -11,3 +11,31 @@ 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. +CREATE PROCEDURE p1() +BEGIN +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; +END| +CALL p1(); +Warnings: +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +DROP PROCEDURE p1; +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_unsafe.test b/mysql-test/suite/binlog/t/binlog_unsafe.test index f34c22dc5f7..de15f5f6f14 100644 --- a/mysql-test/suite/binlog/t/binlog_unsafe.test +++ b/mysql-test/suite/binlog/t/binlog_unsafe.test @@ -15,4 +15,24 @@ 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; +delimiter |; +CREATE PROCEDURE p1() +BEGIN + 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; +END| +delimiter ;| +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 990f7713561..abf25e96be0 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -418,6 +418,19 @@ bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds) 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 --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 */ diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 7c9ead7591c..77d5fd7421c 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -859,6 +859,19 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list, 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); |