[Commits] Rev 4484: MDEV-4255 MIXED replication: Unique key updates are not marked unsafe, replication fails in lp:~maria-captains/maria/10.0

Sergei Golubchik serg at mariadb.org
Mon Nov 17 01:49:00 EET 2014


At lp:~maria-captains/maria/10.0

------------------------------------------------------------
revno: 4484
revision-id: sergii at pisem.net-20141116234900-747qdhm9ti9xh070
parent: sergii at pisem.net-20141113161325-mdz5ox7e86iyazss
fixes bug: https://mariadb.atlassian.net/browse/MDEV-4255
committer: Sergei Golubchik <sergii at pisem.net>
branch nick: 10.0
timestamp: Mon 2014-11-17 00:49:00 +0100
message:
  MDEV-4255 MIXED replication: Unique key updates are not marked unsafe, replication fails
=== modified file 'mysql-test/r/multi_update.result'
--- a/mysql-test/r/multi_update.result	2014-05-09 10:35:11 +0000
+++ b/mysql-test/r/multi_update.result	2014-11-16 23:49:00 +0000
@@ -270,6 +270,8 @@ CREATE TABLE t2 (a int not null primary
 INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
 INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
 update t1,t2 set t1.a=t1.a+100;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
 select * from t1;
 a	b
 101	1
@@ -282,6 +284,8 @@ a	b
 108	8
 109	9
 update t1,t2 set t1.a=t1.a+100 where t1.a=101;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
 select * from t1;
 a	b
 201	1
@@ -329,6 +333,8 @@ a	b
 8	8
 9	9
 update t1,t2 set t1.b=t2.b, t1.a=t2.a where t1.a=t2.a and not exists (select * from t2 where t2.a > 10);
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
 drop table t1,t2;
 CREATE TABLE t3 (  KEY1 varchar(50) NOT NULL default '',  PARAM_CORR_DISTANCE_RUSH double default NULL,  PARAM_CORR_DISTANCE_GEM double default NULL,  PARAM_AVG_TARE double default NULL,  PARAM_AVG_NB_DAYS double default NULL,  PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL,  PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL,  PARAM_SCENARIO_COSTS varchar(50) default NULL,  PARAM_DEFAULT_WAGON_COST double default NULL,  tmp int(11) default NULL,  PRIMARY KEY  (KEY1)) ENGINE=MyISAM;
 INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL);
@@ -751,6 +757,8 @@ PRIMARY KEY (pk)
 ) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (0,0);
 UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
 
 # Should be (1,2)
 SELECT * FROM t1;
@@ -781,14 +789,20 @@ primary key (pk_1,pk_2)
 INSERT INTO t2 VALUES (1,2,3,4);
 
 UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
 
 SELECT * FROM t1;
 col_int_key	pk	col_int
 1	7	3
 
 UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
 
 UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
 
 SELECT * FROM t2;
 col_int_key	pk_1	pk_2	col_int

=== modified file 'mysql-test/r/ps.result'
--- a/mysql-test/r/ps.result	2014-05-13 09:53:30 +0000
+++ b/mysql-test/r/ps.result	2014-11-16 23:49:00 +0000
@@ -42,6 +42,8 @@ a	b
 prepare stmt4 from 'update t1 set a=? where b=?';
 set @arg1=55, @arg2='five';
 execute stmt4 using @arg1, @arg2;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
 select * from t1 where a>3;
 a	b
 4	four

=== modified file 'mysql-test/suite/binlog/r/binlog_unsafe.result'
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result	2014-09-15 21:00:47 +0000
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result	2014-11-16 23:49:00 +0000
@@ -2713,4 +2713,67 @@ DROP TABLE replace_table;
 DROP TABLE create_ignore_test;
 DROP TABLE create_replace_test;
 DROP TABLE insert_2_keys;
+create table t1 (a int primary key, b int, c int, d int, e int,
+unique (c,b), key(d,c), key (b,e,d));
+insert t1 values (0,0,0,0,0);
+update t1 set a=1;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update t1 set b=2;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update t1 set c=3;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update t1 set d=4;
+update t1 set e=5;
+update t1 set e=6, d=6;
+update t1 set c=7, e=7;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update t1 set a=8, e=8;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update t1 set b=9, e=9, d=9;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+create table t2 (x int primary key, y int, z int);
+insert t2 values (0,0,0);
+update t1,t2 set a=1, y=1;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update t1,t2 set b=2, y=2;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update t1,t2 set c=3, y=3;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update t1,t2 set d=4, y=4;
+update t1,t2 set e=5, y=5;
+update t1,t2 set e=6, d=6, y=6;
+update t1,t2 set c=7, e=7, y=7;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update t1,t2 set a=8, e=8, y=8;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update t1,t2 set b=9, e=9, d=9, y=9;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update t1,t2 set e=10, d=10, x=10;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+create view v1 as select * from t1, t2 where z=e;
+update v1 set a=20;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update v1 set e=21;
+update v1 set b=22, e=22, d=22;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+update v1,t2 set v1.c=30;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated.
+drop view v1;
+drop table t1, t2;
 "End of tests"

=== modified file 'mysql-test/suite/binlog/t/binlog_unsafe.test'
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test	2012-05-21 13:30:25 +0000
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test	2014-11-16 23:49:00 +0000
@@ -760,4 +760,46 @@ DROP TABLE create_ignore_test;
 DROP TABLE create_replace_test;
 DROP TABLE insert_2_keys;
 
+#
+# MDEV-4255 MIXED replication: Unique key updates are not marked unsafe, replication fails      
+#
+create table t1 (a int primary key, b int, c int, d int, e int,
+  unique (c,b), key(d,c), key (b,e,d));
+insert t1 values (0,0,0,0,0);
+update t1 set a=1;
+update t1 set b=2;
+update t1 set c=3;
+update t1 set d=4;
+update t1 set e=5;
+update t1 set e=6, d=6;
+update t1 set c=7, e=7;
+update t1 set a=8, e=8;
+update t1 set b=9, e=9, d=9;
+
+create table t2 (x int primary key, y int, z int);
+insert t2 values (0,0,0);
+
+update t1,t2 set a=1, y=1;
+update t1,t2 set b=2, y=2;
+update t1,t2 set c=3, y=3;
+update t1,t2 set d=4, y=4;
+update t1,t2 set e=5, y=5;
+update t1,t2 set e=6, d=6, y=6;
+update t1,t2 set c=7, e=7, y=7;
+update t1,t2 set a=8, e=8, y=8;
+update t1,t2 set b=9, e=9, d=9, y=9;
+
+update t1,t2 set e=10, d=10, x=10;
+
+create view v1 as select * from t1, t2 where z=e;
+
+update v1 set a=20;
+update v1 set e=21;
+update v1 set b=22, e=22, d=22;
+
+update v1,t2 set v1.c=30;
+
+drop view v1;
+drop table t1, t2;
+
 --echo "End of tests"

=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt	2014-11-13 16:13:25 +0000
+++ b/sql/share/errmsg-utf8.txt	2014-11-16 23:49:00 +0000
@@ -7111,3 +7111,5 @@ ER_SLAVE_SKIP_NOT_IN_GTID
 	eng "When using GTID, @@sql_slave_skip_counter can not be used. Instead, setting @@gtid_slave_pos explicitly can be used to skip to after a given GTID position."
 ER_TABLE_DEFINITION_TOO_BIG
         eng "The definition for table %`s is too big"
+ER_BINLOG_UNSAFE_UPDATE_UNIQUE_NONTRANS
+        eng "The statement is unsafe because it updates a UNIQUE key colums in a non-transactional table. This is unsafe because in the case of a conflict a non-predictable part of the table will stay updated."

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2014-09-30 17:31:14 +0000
+++ b/sql/sql_lex.cc	2014-11-16 23:49:00 +0000
@@ -71,7 +71,8 @@ Query_tables_list::binlog_stmt_unsafe_er
   ER_BINLOG_UNSAFE_CREATE_SELECT_AUTOINC,
   ER_BINLOG_UNSAFE_UPDATE_IGNORE,
   ER_BINLOG_UNSAFE_INSERT_TWO_KEYS,
-  ER_BINLOG_UNSAFE_AUTOINC_NOT_FIRST
+  ER_BINLOG_UNSAFE_AUTOINC_NOT_FIRST,
+  ER_BINLOG_UNSAFE_UPDATE_UNIQUE_NONTRANS
 };
 
 

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2014-08-07 16:06:56 +0000
+++ b/sql/sql_lex.h	2014-11-16 23:49:00 +0000
@@ -1189,7 +1189,7 @@ class Query_tables_list
 
     @note The order of elements of this enumeration type must
     correspond to the order of the elements of the @c explanations
-    array defined in the body of @c THD::issue_unsafe_warnings.
+    array Query_tables_list::binlog_stmt_unsafe_errcode
   */
   enum enum_binlog_stmt_unsafe {
     /**
@@ -1319,6 +1319,12 @@ class Query_tables_list
     */
     BINLOG_STMT_UNSAFE_AUTOINC_NOT_FIRST,
 
+    /**
+       INSERT into auto-inc field which is not the first part of composed
+       primary key.
+    */
+    BINLOG_STMT_UPDATE_UNIQUE_NONTRANS,
+
     /* The last element of this enumeration type. */
     BINLOG_STMT_UNSAFE_COUNT
   };

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2014-11-13 12:40:11 +0000
+++ b/sql/sql_update.cc	2014-11-16 23:49:00 +0000
@@ -223,6 +223,41 @@ static void prepare_record_for_error_mes
 }
 
 
+static void check_update_sbr_unique_nontrans(THD *thd, TABLE_LIST *table_list)
+{
+  TABLE *table= table_list->table;
+  /*
+    to avoid scanning all keyparts of all unique keys and to avoid
+    calling decide_logging_format() many times, let's return
+    as soon as possible
+  */
+  if (!mysql_bin_log.is_open() ||
+      !(thd->variables.option_bits & OPTION_BIN_LOG) ||
+      thd->is_current_stmt_binlog_format_row() || thd->lex->is_stmt_unsafe() ||
+      table->file->has_transactions())
+    return; // safe or already warned
+
+  for (uint i=0; i < table->s->keys; i++)
+  {
+    KEY *key_info= table->key_info + i;
+    if (!(key_info->flags & HA_NOSAME))
+      return; // have seen all UNIQUE keys. safe.
+
+    for (uint j=0; j < key_info->user_defined_key_parts; j++)
+    {
+      KEY_PART_INFO *key_part= key_info->key_part + j;
+      if (bitmap_is_set(table->write_set, key_part->field->field_index))
+      {
+        thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UPDATE_UNIQUE_NONTRANS);
+        thd->decide_logging_format(table_list);
+        return; // unsafe;
+      }
+    }
+  }
+  return; // safe
+}
+
+
 /*
   Process usual UPDATE
 
@@ -685,6 +720,8 @@ int mysql_update(THD *thd,
 
   if (ignore)
     table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
+  else
+    check_update_sbr_unique_nontrans(thd, table_list);
   
   if (select && select->quick && select->quick->reset())
     goto err;
@@ -1510,6 +1547,7 @@ int mysql_multi_update_prepare(THD *thd)
         DBUG_RETURN(TRUE);
       }
     }
+    check_update_sbr_unique_nontrans(thd, tl);
   }
   /*
     Set exclude_from_table_unique_test value back to FALSE. It is needed for



More information about the commits mailing list