MDEV-7635: Update tests to adapt to the new default sql_mode

9 files changed, 43 insertions, 27 deletions

diff --git a/mysql-test/extra/binlog_tests/binlog.test b/mysql-test/extra/binlog_tests/binlog.test
index 831c6c886d5..810a402580c 100644
--- a/mysql-test/extra/binlog_tests/binlog.test
+++ b/mysql-test/extra/binlog_tests/binlog.test
@@ -272,7 +272,7 @@ create table if not exists t3 like tt1;
 
 --disable_warnings
 USE mysql;
-INSERT INTO user SET host='localhost', user='@#@', password=password('Just a test');
+INSERT IGNORE INTO user SET host='localhost', user='@#@', password=password('Just a test');
 UPDATE user SET password=password('Another password') WHERE host='localhost' AND user='@#@';
 DELETE FROM user WHERE host='localhost' AND user='@#@';
 --enable_warnings
diff --git a/mysql-test/extra/binlog_tests/ctype_cp932.test b/mysql-test/extra/binlog_tests/ctype_cp932.test
index 33247550d62..f42bffd30df 100644
--- a/mysql-test/extra/binlog_tests/ctype_cp932.test
+++ b/mysql-test/extra/binlog_tests/ctype_cp932.test
@@ -436,9 +436,9 @@ drop table t2;
 #
 create table t1 (col1 varchar(1)) character set cp932;
 insert into t1 values ('a');
-insert into t1 values ('ab');
+insert ignore into t1 values ('ab');
 select * from t1;
-insert into t1 values ('abc');
+insert ignore into t1 values ('abc');
 select * from t1;
 drop table t1;
 
diff --git a/mysql-test/extra/rpl_tests/rpl_binlog_errors.inc b/mysql-test/extra/rpl_tests/rpl_binlog_errors.inc
index c595d70daa1..29731e823ce 100644
--- a/mysql-test/extra/rpl_tests/rpl_binlog_errors.inc
+++ b/mysql-test/extra/rpl_tests/rpl_binlog_errors.inc
@@ -192,6 +192,11 @@ RESET MASTER;
 ###            is written to the current binary log.
 
 SET GLOBAL debug_dbug="+d,error_unique_log_filename";
+
+# Disable logging Annotate_rows events to preserve events count.
+let $binlog_annotate_row_events_saved= `SELECT @@binlog_annotate_row_events`;
+SET @@binlog_annotate_row_events= 0;
+
 SELECT count(*) FROM t4;
 -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
 -- error ER_NO_UNIQUE_LOGFILE
@@ -210,6 +215,11 @@ if (!$binlog_limit)
 
 # clean up and move to next test
 DELETE FROM t4;
+
+--disable_query_log
+eval SET @@binlog_annotate_row_events= $binlog_annotate_row_events_saved;
+--enable_query_log
+
 RESET MASTER;
 
 -- echo ###################### TEST #8
diff --git a/mysql-test/extra/rpl_tests/rpl_checksum_cache.inc b/mysql-test/extra/rpl_tests/rpl_checksum_cache.inc
index a10c9721f70..e04f618b81e 100644
--- a/mysql-test/extra/rpl_tests/rpl_checksum_cache.inc
+++ b/mysql-test/extra/rpl_tests/rpl_checksum_cache.inc
@@ -8,8 +8,8 @@
 -- source include/master-slave.inc
 
 --disable_warnings
-call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. Statement: insert into t2 set data=repeat.*'a', @act_size.*");
-call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. Statement: insert into t1 values.* NAME_CONST.*'n',.*, @data .*");
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. .*Statement: insert into t2 set data=repeat.*'a', @act_size.*");
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. .*Statement: insert into t1 values.* NAME_CONST.*'n',.*, @data .*");
 --enable_warnings
 
 connection master;
diff --git a/mysql-test/extra/rpl_tests/rpl_extra_col_master.test b/mysql-test/extra/rpl_tests/rpl_extra_col_master.test
index 0559e93676b..02645a577f5 100644
--- a/mysql-test/extra/rpl_tests/rpl_extra_col_master.test
+++ b/mysql-test/extra/rpl_tests/rpl_extra_col_master.test
@@ -253,9 +253,9 @@ some var char';
    INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:
 binary data';
    INSERT into t31 set f1=1, f2=1, f3=1, f4='first';
-   INSERT into t31 set f1=1, f2=1, f3=2, f4='second',
+   insert ignore into t31 set f1=1, f2=1, f3=2, f4='second',
      f9=2.2,  f10='seven samurai', f28=222.222, f35='222';
-   INSERT into t31 values (1, 1, 3, 'third',
+   insert ignore into t31 values (1, 1, 3, 'third',
       /* f5  BIGINT,  */            333333333333333333333333,
       /* f6  BLOB,  */              '3333333333333333333333',
       /* f7  DATE,  */              '2007-07-18',
@@ -289,7 +289,7 @@ binary data';
       /*f35 VARCHAR(257),*/         'three times three'
       );
    
-   INSERT into t31 values (1, 1, 4, 'fourth',
+   insert ignore into t31 values (1, 1, 4, 'fourth',
        /* f5  BIGINT,  */            333333333333333333333333,
        /* f6  BLOB,  */              '3333333333333333333333',
        /* f7  DATE,  */              '2007-07-18',
@@ -408,7 +408,7 @@ connection master;
    update t31 set f5=555555555555555 where f3=6;
    update t31 set f2=2 where f3=2;
    update t31 set f1=NULL where f3=1;
-   update t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3;
+   update ignore t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3;
 
 --echo
 --echo ** Delete from Master **
diff --git a/mysql-test/extra/rpl_tests/rpl_gtid_basic.inc b/mysql-test/extra/rpl_tests/rpl_gtid_basic.inc
index ab7d23f70ac..68a5d05ffe9 100644
--- a/mysql-test/extra/rpl_tests/rpl_gtid_basic.inc
+++ b/mysql-test/extra/rpl_tests/rpl_gtid_basic.inc
@@ -455,9 +455,13 @@ SELECT IF(LOCATE("2-1-1022", @@GLOBAL.gtid_slave_pos)>0, "Ok", CONCAT("ERROR! ex
 --connection server_1
 SET gtid_domain_id=2;
 SET gtid_seq_no=1030;
+# Disable logging Annotate_rows events to preserve events count.
+let $binlog_annotate_row_events_saved= `SELECT @@binlog_annotate_row_events`;
+SET @@binlog_annotate_row_events= 0;
 INSERT INTO t1 VALUES (17);
 INSERT INTO t1 VALUES (18);
 INSERT INTO t1 VALUES (19);
+eval SET @@binlog_annotate_row_events= $binlog_annotate_row_events_saved;
 --save_master_pos
 
 --connection server_2
diff --git a/mysql-test/extra/rpl_tests/rpl_record_compare.test b/mysql-test/extra/rpl_tests/rpl_record_compare.test
index b674b76dfdf..2bc088d17b4 100644
--- a/mysql-test/extra/rpl_tests/rpl_record_compare.test
+++ b/mysql-test/extra/rpl_tests/rpl_record_compare.test
@@ -12,7 +12,7 @@
 INSERT INTO t1 ( c5, c6 ) VALUES ( 1   , 35 );
 INSERT INTO t1 ( c5, c6 ) VALUES ( NULL, 35 );
 -- disable_warnings
-UPDATE t1 SET c5 = 'a';
+UPDATE IGNORE t1 SET c5 = 'a';
 -- enable_warnings
 -- sync_slave_with_master
 
@@ -33,7 +33,7 @@ DROP TABLE t1;
 INSERT INTO t1 ( c5, c6 ) VALUES ( 1   , 35 );
 INSERT INTO t1 ( c5, c6 ) VALUES ( NULL, 35 );
 -- disable_warnings
-UPDATE t1 SET c5 = 'a';
+UPDATE IGNORE t1 SET c5 = 'a';
 -- enable_warnings
 -- sync_slave_with_master
 
diff --git a/mysql-test/extra/rpl_tests/rpl_row_basic.test b/mysql-test/extra/rpl_tests/rpl_row_basic.test
index 2e1007b1c04..33d5dd328f3 100644
--- a/mysql-test/extra/rpl_tests/rpl_row_basic.test
+++ b/mysql-test/extra/rpl_tests/rpl_row_basic.test
@@ -623,33 +623,33 @@ eval CREATE TABLE t1 (a bit) ENGINE=$type;
 INSERT IGNORE INTO t1 VALUES (NULL);
 INSERT INTO t1 ( a ) VALUES ( 0 );
 UPDATE t1 SET a = 0 WHERE a = 1 LIMIT 3;
-INSERT INTO t1 ( a ) VALUES ( 5 );
+INSERT IGNORE INTO t1 ( a ) VALUES ( 5 );
 DELETE FROM t1 WHERE a < 2 LIMIT 4;
 DELETE FROM t1 WHERE a < 9 LIMIT 4;
-INSERT INTO t1 ( a ) VALUES ( 9 );
+INSERT IGNORE INTO t1 ( a ) VALUES ( 9 );
 UPDATE t1 SET a = 8 WHERE a = 0 LIMIT 6;
-INSERT INTO t1 ( a ) VALUES ( 8 );
+INSERT IGNORE INTO t1 ( a ) VALUES ( 8 );
 UPDATE t1 SET a = 0 WHERE a < 6 LIMIT 0;
-INSERT INTO t1 ( a ) VALUES ( 4 );
-INSERT INTO t1 ( a ) VALUES ( 3 );
+INSERT IGNORE INTO t1 ( a ) VALUES ( 4 );
+INSERT IGNORE INTO t1 ( a ) VALUES ( 3 );
 UPDATE t1 SET a = 0 WHERE a = 7 LIMIT 6;
 DELETE FROM t1 WHERE a = 4 LIMIT 7;
-UPDATE t1 SET a = 9 WHERE a < 2 LIMIT 9;
+UPDATE IGNORE t1 SET a = 9 WHERE a < 2 LIMIT 9;
 UPDATE t1 SET a = 0 WHERE a < 9 LIMIT 2;
 DELETE FROM t1 WHERE a < 0 LIMIT 5;
-INSERT INTO t1 ( a ) VALUES ( 5 );
-UPDATE t1 SET a = 4 WHERE a < 6 LIMIT 4;
-INSERT INTO t1 ( a ) VALUES ( 5 );
-UPDATE t1 SET a = 9 WHERE a < 5 LIMIT 8;
+INSERT IGNORE INTO t1 ( a ) VALUES ( 5 );
+UPDATE IGNORE t1 SET a = 4 WHERE a < 6 LIMIT 4;
+INSERT IGNORE INTO t1 ( a ) VALUES ( 5 );
+UPDATE IGNORE t1 SET a = 9 WHERE a < 5 LIMIT 8;
 DELETE FROM t1 WHERE a < 8 LIMIT 8;
-INSERT INTO t1 ( a ) VALUES ( 6 );
+INSERT IGNORE INTO t1 ( a ) VALUES ( 6 );
 DELETE FROM t1 WHERE a < 6 LIMIT 7;
 UPDATE t1 SET a = 7 WHERE a = 3 LIMIT 7;
 UPDATE t1 SET a = 8 WHERE a = 0 LIMIT 6;
-INSERT INTO t1 ( a ) VALUES ( 7 );
+INSERT IGNORE INTO t1 ( a ) VALUES ( 7 );
 DELETE FROM t1 WHERE a < 9 LIMIT 4;
-INSERT INTO t1 ( a ) VALUES ( 7 );
-INSERT INTO t1 ( a ) VALUES ( 6 );
+INSERT IGNORE INTO t1 ( a ) VALUES ( 7 );
+INSERT IGNORE INTO t1 ( a ) VALUES ( 6 );
 UPDATE t1 SET a = 8 WHERE a = 3 LIMIT 4;
 DELETE FROM t1 WHERE a = 2 LIMIT 9;
 DELETE FROM t1 WHERE a = 1 LIMIT 4;
@@ -658,7 +658,7 @@ INSERT INTO t1 ( a ) VALUES ( 0 );
 DELETE FROM t1 WHERE a < 3 LIMIT 0;
 UPDATE t1 SET a = 8 WHERE a = 5 LIMIT 2;
 INSERT INTO t1 ( a ) VALUES ( 1 );
-UPDATE t1 SET a = 9 WHERE a < 5 LIMIT 3;
+UPDATE IGNORE t1 SET a = 9 WHERE a < 5 LIMIT 3;
 --enable_warnings
 
 sync_slave_with_master;
diff --git a/mysql-test/extra/rpl_tests/rpl_switch_stm_row_mixed.inc b/mysql-test/extra/rpl_tests/rpl_switch_stm_row_mixed.inc
index e74fd6828c6..31b80732c60 100644
--- a/mysql-test/extra/rpl_tests/rpl_switch_stm_row_mixed.inc
+++ b/mysql-test/extra/rpl_tests/rpl_switch_stm_row_mixed.inc
@@ -156,12 +156,14 @@ insert into t1 select "yesterday_24_";
 create table t2 ENGINE=MyISAM select rpad(UUID(),100,' ');
 create table t3 select 1 union select UUID();
 --disable_warnings
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3);
 --enable_warnings
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
 # what if UUID() is first:
 --disable_warnings
-insert into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4);
+insert ignore into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4);
 --enable_warnings
 
 # inside a stored procedure