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

95 files changed, 469 insertions, 380 deletions

diff --git a/mysql-test/t/adddate_454.test b/mysql-test/t/adddate_454.test
index 1d69cdc9558..bbdd89b583d 100644
--- a/mysql-test/t/adddate_454.test
+++ b/mysql-test/t/adddate_454.test
@@ -4,6 +4,6 @@
 create table t1 (d date);
 insert into t1 values ('2012-00-00');
 select * from t1;
-update t1 set d = adddate(d, interval 1 day);
+update ignore t1 set d = adddate(d, interval 1 day);
 select * from t1;
 drop table t1;
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index 5efbd7c8819..bf16757efd6 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -268,7 +268,7 @@ drop table if exists t1, t2;
 # not null columns for primary keys)
 
 create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
-insert into t1 (a) values(1);
+insert ignore into t1 (a) values(1);
 --replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X
 show table status like 't1';
 alter table t1 modify a int;
@@ -276,7 +276,7 @@ alter table t1 modify a int;
 show table status like 't1';
 drop table t1;
 create table t1 (a int not null, b int not null, c int not null, d int not null, e int not null, f int not null, g int not null, h int not null,i int not null, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
-insert into t1 (a) values(1);
+insert ignore into t1 (a) values(1);
 --replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X
 show table status like 't1';
 drop table t1;
diff --git a/mysql-test/t/auto_increment_ranges.inc b/mysql-test/t/auto_increment_ranges.inc
index 1540be0828e..dc60f07a700 100644
--- a/mysql-test/t/auto_increment_ranges.inc
+++ b/mysql-test/t/auto_increment_ranges.inc
@@ -29,7 +29,7 @@ truncate table t1;
 --error HA_ERR_AUTOINC_ERANGE
 eval insert into t1 values($range_max-1),(NULL),(NULL);
 truncate table t1;
-eval insert into t1 values($range_max+1);
+eval insert ignore into t1 values($range_max+1);
 select * from t1;
 --error HA_ERR_AUTOINC_ERANGE
 eval insert into t1 values(NULL);
@@ -60,7 +60,7 @@ truncate table t1;
 --error HA_ERR_AUTOINC_ERANGE
 eval insert into t1 values($range_max-1),(NULL),(NULL);
 truncate table t1;
-eval insert into t1 values($range_max+1);
+eval insert ignore into t1 values($range_max+1);
 select * from t1;
 --error HA_ERR_AUTOINC_ERANGE
 eval insert into t1 values(NULL);
@@ -91,7 +91,7 @@ truncate table t1;
 --error HA_ERR_AUTOINC_ERANGE
 eval insert into t1 values($range_max-1),(NULL),(NULL);
 truncate table t1;
-eval insert into t1 values($range_max+1);
+eval insert ignore into t1 values($range_max+1);
 select * from t1;
 --error HA_ERR_AUTOINC_ERANGE
 eval insert into t1 values(NULL);
@@ -122,7 +122,7 @@ truncate table t1;
 --error HA_ERR_AUTOINC_ERANGE
 eval insert into t1 values($range_max-1),(NULL),(NULL);
 truncate table t1;
-eval insert into t1 values($range_max+1);
+eval insert ignore into t1 values($range_max+1);
 select * from t1;
 --error HA_ERR_AUTOINC_ERANGE
 eval insert into t1 values(NULL);
@@ -153,7 +153,7 @@ truncate table t1;
 --error HA_ERR_AUTOINC_ERANGE
 eval insert into t1 values($range_max-1),(NULL),(NULL);
 truncate table t1;
-eval insert into t1 values($range_max+1);
+eval insert ignore into t1 values($range_max+1);
 select * from t1;
 --error HA_ERR_AUTOINC_ERANGE
 eval insert into t1 values(NULL);
@@ -222,7 +222,7 @@ DROP TABLE t1;
 --echo #
 CREATE TABLE t1 (a smallint AUTO_INCREMENT, PRIMARY KEY (a));
 INSERT INTO t1 VALUES (2);
-INSERT INTO t1 VALUES (32768);
+INSERT IGNORE INTO t1 VALUES (32768);
 --error HA_ERR_AUTOINC_ERANGE
 INSERT INTO t1 VALUES (NULL);
 SELECT * FROM t1;
diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test
index fb18d60edd9..ae325c7d7de 100644
--- a/mysql-test/t/bigint.test
+++ b/mysql-test/t/bigint.test
@@ -38,6 +38,7 @@ insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(1844
 select * from t1;
 select min(big),max(big),max(big)-1 from t1;
 select min(big),max(big),max(big)-1 from t1 group by a;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 modify big bigint unsigned not null;
 select min(big),max(big),max(big)-1 from t1;
 select min(big),max(big),max(big)-1 from t1 group by a;
@@ -48,6 +49,7 @@ select min(big),max(big),max(big)-1 from t1 group by a;
 alter table t1 add key (big);
 select min(big),max(big),max(big)-1 from t1;
 select min(big),max(big),max(big)-1 from t1 group by a;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 modify big bigint not null;
 select * from t1;
 select min(big),max(big),max(big)-1 from t1;
@@ -153,7 +155,7 @@ select * from t1;
 drop table t1;
 
 create table t1 ( quantity decimal(2) unsigned);
-insert into t1 values (500), (-500), (~0), (-1);
+insert ignore into t1 values (500), (-500), (~0), (-1);
 select * from t1;
 drop table t1;
 
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index 5316c12c857..d62612f819e 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -174,6 +174,7 @@ select
   hex(cast(_latin1'a'   AS char(2))) as c5;
 select cast(1000 as CHAR(3));
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1 select
   cast(_latin1'ab'  AS char)    as c1,
   cast(_latin1'a '  AS char)    as c2,
@@ -194,6 +195,7 @@ select
   cast(_koi8r'Æ  ' AS nchar(2)) as c4,
   cast(_koi8r'Æ'   AS nchar(2)) as c5;
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1 select
   cast(_koi8r'ÆÇ'  AS nchar)    as c1,
   cast(_koi8r'Æ '  AS nchar)    as c2,
@@ -421,6 +423,7 @@ DROP TABLE t1;
 --echo # Bug#14096619 UNABLE TO RESTORE DATABASE DUMP
 --echo #
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 AS SELECT CONCAT(CAST(REPEAT('9', 1000) AS SIGNED)),
                           CONCAT(CAST(REPEAT('9', 1000) AS UNSIGNED));
 SHOW CREATE TABLE t1;
diff --git a/mysql-test/t/comment_column.test b/mysql-test/t/comment_column.test
index de1c9b8ce47..a4f3893dd4a 100644
--- a/mysql-test/t/comment_column.test
+++ b/mysql-test/t/comment_column.test
@@ -62,6 +62,7 @@ SHOW CREATE TABLE t1;
 
 
 # comment overflow(warning)
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 ADD COLUMN c11 INTEGER COMMENT 'ABCDEfghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdABCDEfghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdABCDEfghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcd';
 SELECT table_comment,char_length(table_comment) FROM information_schema.tables WHERE table_name='t1';
 SELECT column_comment,char_length(column_comment) FROM information_schema.columns WHERE table_name='t1';
diff --git a/mysql-test/t/comment_index.test b/mysql-test/t/comment_index.test
index 7857d54080e..4ba1f336ddb 100644
--- a/mysql-test/t/comment_index.test
+++ b/mysql-test/t/comment_index.test
@@ -57,6 +57,7 @@ SHOW CREATE TABLE t1;
 
 
 # comment overflow(warning)
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE INDEX i11 ON t1(c11) COMMENT 'abcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcd';
 SELECT table_comment,char_length(table_comment) FROM information_schema.tables WHERE table_name='t1';
 SELECT column_comment,char_length(column_comment) FROM information_schema.columns WHERE table_name='t1';
@@ -65,6 +66,7 @@ let $ENGINE=`select variable_value from information_schema.global_variables wher
 --replace_result $ENGINE ENGINE
 SHOW CREATE TABLE t1;
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 ADD INDEX (c13) COMMENT 'abcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcd';
 SELECT table_comment,char_length(table_comment) FROM information_schema.tables WHERE table_name='t1';
 SELECT column_comment,char_length(column_comment) FROM information_schema.columns WHERE table_name='t1';
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 0be9537350b..36fc4e4e2d0 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -71,6 +71,7 @@ create table t1 (a varchar(5) default 'abcde');
 insert into t1 values();
 select * from t1;
 --error 1067
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 alter column a set default 'abcdef';
 drop table t1;
 
@@ -431,7 +432,9 @@ drop table t1, t2;
 #
 
 create table t1(cenum enum('a'), cset set('b'));
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t2(cenum enum('a','a'), cset set('b','b'));
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t3(cenum enum('a','A','a','c','c'), cset set('b','B','b','d','d'));
 drop table t1, t2, t3;
 
@@ -613,6 +616,7 @@ show create table t1;
 drop table t1;
 
 #--warning 1364
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1 ( 
   a varchar(12) charset utf8 collate utf8_bin not null, 
   b int not null, primary key (a)
@@ -1731,6 +1735,7 @@ drop table t1;
 # MDEV-7050: MySQL#74603 - Assertion `comma_length > 0' failed in mysql_prepare_create_table
 #
 set @@session.collation_server=filename;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1(a enum('',''));
 drop table t1;
 set @@session.collation_server=default;
diff --git a/mysql-test/t/ctype_big5.test b/mysql-test/t/ctype_big5.test
index fe29f769a89..410203c7c86 100644
--- a/mysql-test/t/ctype_big5.test
+++ b/mysql-test/t/ctype_big5.test
@@ -122,7 +122,7 @@ ORDER BY head, tail;
 DROP TEMPORARY TABLE head, tail;
 SHOW CREATE TABLE t1;
 SELECT COUNT(*) FROM t1;
-UPDATE t1 SET a=unhex(code) ORDER BY code;
+UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
 SELECT COUNT(*) FROM t1 WHERE a<>'?';
 #
 # Display all characters that have upper or lower case mapping.
diff --git a/mysql-test/t/ctype_cp932_binlog_stm.test b/mysql-test/t/ctype_cp932_binlog_stm.test
index 1b92006c949..78e5725218a 100644
--- a/mysql-test/t/ctype_cp932_binlog_stm.test
+++ b/mysql-test/t/ctype_cp932_binlog_stm.test
@@ -91,13 +91,13 @@ ORDER BY head, tail;
 # Populate t1 with Half Width Kana [A1..DF]
 #
 --disable_warnings
-INSERT t1 (code) SELECT head FROM head
+INSERT IGNORE t1 (code) SELECT head FROM head
 WHERE (head BETWEEN 'A1' AND 'DF')
 ORDER BY head;
 --enable_warnings
 DROP TEMPORARY TABLE head, tail;
 SHOW CREATE TABLE t1;
-UPDATE t1 SET a=unhex(code) ORDER BY code;
+UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
 SELECT COUNT(*) FROM t1;
 SELECT COUNT(*) FROM t1 WHERE a<>'?' AND OCTET_LENGTH(a)=1;
 SELECT COUNT(*) FROM t1 WHERE a<>'' AND OCTET_LENGTH(a)=2;
diff --git a/mysql-test/t/ctype_eucjpms.test b/mysql-test/t/ctype_eucjpms.test
index bda4cec61cc..c69abc32a03 100644
--- a/mysql-test/t/ctype_eucjpms.test
+++ b/mysql-test/t/ctype_eucjpms.test
@@ -421,13 +421,13 @@ CREATE TABLE t1 AS SELECT 'XXXXXX' AS code, ' ' AS a LIMIT 0;
 # Pupulate JIS-X-0201 range (Half Width Kana)
 # Expected valid code range: [8E][A1..DF]   (1x63 characters)
 #
-INSERT INTO t1 (code) SELECT concat('8E', head) FROM head
+INSERT IGNORE INTO t1 (code) SELECT concat('8E', head) FROM head
 WHERE (head BETWEEN 'A1' AND 'DF') ORDER BY head;
 #
 # Populate JIS-X-0208 range
 # Expected valid codes:  [A1..FE][A1..FE]   (94x94=8836 characters)
 #
-INSERT INTO t1 (code) SELECT concat(head, tail)
+INSERT IGNORE INTO t1 (code) SELECT concat(head, tail)
 FROM head, tail
 WHERE (head BETWEEN '80' AND 'FF') AND (head NOT BETWEEN '8E' AND '8F')
 AND (tail BETWEEN '20' AND 'FF')
@@ -436,14 +436,14 @@ ORDER BY head, tail;
 # Populate JIS-X-0212 range
 # Expected valid codes [8F][A1..FE][A1..FE] (1x94x94=8836 characters)
 #
-INSERT INTO t1 (code) SELECT concat('8F', head, tail)
+INSERT IGNORE INTO t1 (code) SELECT concat('8F', head, tail)
 FROM head, tail
 WHERE (head BETWEEN '80' AND 'FF') AND (tail BETWEEN '20' AND 'FF')
 ORDER BY head, tail;
 DROP TEMPORARY TABLE head, tail;
 SHOW CREATE TABLE t1;
 
-UPDATE t1 SET a=unhex(code) ORDER BY code;
+UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
 SELECT COUNT(*) FROM t1;
 SELECT COUNT(*) FROM t1 WHERE a<>'';
 SELECT COUNT(*) FROM t1 WHERE a<>'' AND a<>'?';
@@ -531,7 +531,7 @@ set collation_connection=eucjpms_bin;
 --echo # MDEV-6776 ujis and eucjmps erroneously accept 0x8EA0 as a valid byte sequence
 --echo #
 CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET eucjpms);
-INSERT INTO t1 VALUES (0x8EA0);
+INSERT IGNORE INTO t1 VALUES (0x8EA0);
 SELECT HEX(a), CHAR_LENGTH(a) FROM t1;
 DROP TABLE t1;
 --error ER_INVALID_CHARACTER_STRING
@@ -575,7 +575,7 @@ DROP TABLE t1;
 --echo # MDEV-9842 LOAD DATA INFILE does not work well with a TEXT column when using sjis
 --echo #
 CREATE TABLE t1 (a TEXT CHARACTER SET eucjpms);
-LOAD DATA INFILE '../../std_data/loaddata/mdev9823.ujis.txt' INTO TABLE t1 CHARACTER SET eucjpms IGNORE 4 LINES;
+LOAD DATA INFILE '../../std_data/loaddata/mdev9823.ujis.txt' IGNORE INTO TABLE t1 CHARACTER SET eucjpms IGNORE 4 LINES;
 SELECT HEX(a) FROM t1;
 DROP TABLE t1;
 
diff --git a/mysql-test/t/ctype_euckr.test b/mysql-test/t/ctype_euckr.test
index b0b1569e540..950c60c47e5 100644
--- a/mysql-test/t/ctype_euckr.test
+++ b/mysql-test/t/ctype_euckr.test
@@ -46,12 +46,12 @@ insert into t1 values (0xA17A);
 insert into t1 values (0xA181);
 insert into t1 values (0xA1FE);
 # Insert some invalid characters
-insert into t1 values (0xA140);
-insert into t1 values (0xA15B);
-insert into t1 values (0xA160);
-insert into t1 values (0xA17B);
-insert into t1 values (0xA180);
-insert into t1 values (0xA1FF);
+insert ignore into t1 values (0xA140);
+insert ignore into t1 values (0xA15B);
+insert ignore into t1 values (0xA160);
+insert ignore into t1 values (0xA17B);
+insert ignore into t1 values (0xA180);
+insert ignore into t1 values (0xA1FF);
 select hex(s1), hex(convert(s1 using utf8)) from t1 order by binary s1;
 drop table t1;
 
@@ -99,7 +99,7 @@ SELECT s as bad_code FROM t2 WHERE a='?' ORDER BY s;
 DELETE FROM t2 WHERE a='?';
 ALTER TABLE t2 ADD u VARCHAR(1) CHARACTER SET utf8, ADD a2 VARCHAR(1) CHARACTER SET euckr;
 --disable_warnings
-UPDATE t2 SET u=a, a2=u;
+UPDATE IGNORE t2 SET u=a, a2=u;
 --enable_warnings
 SELECT s as unassigned_code FROM t2 WHERE u='?';
 DELETE FROM t2 WHERE u='?';
@@ -144,7 +144,7 @@ WHERE (head BETWEEN '80' AND 'FF') AND (tail BETWEEN '20' AND 'FF')
 ORDER BY head, tail;
 DROP TEMPORARY TABLE head, tail;
 SHOW CREATE TABLE t1;
-UPDATE t1 SET a=unhex(code) ORDER BY code;
+UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
 SELECT COUNT(*) FROM t1 WHERE a<>'?';
 #
 # Display all characters that have upper or lower case mapping.
diff --git a/mysql-test/t/ctype_gb2312.test b/mysql-test/t/ctype_gb2312.test
index 8f15f72fad5..4489168a163 100644
--- a/mysql-test/t/ctype_gb2312.test
+++ b/mysql-test/t/ctype_gb2312.test
@@ -68,7 +68,7 @@ WHERE (head BETWEEN '80' AND 'FF') AND (tail BETWEEN '20' AND 'FF')
 ORDER BY head, tail;
 DROP TEMPORARY TABLE head, tail;
 SHOW CREATE TABLE t1;
-UPDATE t1 SET a=unhex(code) ORDER BY code;
+UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
 SELECT COUNT(*) FROM t1 WHERE a<>'?';
 #
 # Display all characters that have upper or lower case mapping.
diff --git a/mysql-test/t/ctype_gbk.test b/mysql-test/t/ctype_gbk.test
index 88bfda46748..aa2d15b9b71 100644
--- a/mysql-test/t/ctype_gbk.test
+++ b/mysql-test/t/ctype_gbk.test
@@ -103,7 +103,7 @@ WHERE (head BETWEEN '80' AND 'FF') AND (tail BETWEEN '20' AND 'FF')
 ORDER BY head, tail;
 DROP TEMPORARY TABLE head, tail;
 SHOW CREATE TABLE t1;
-UPDATE t1 SET a=unhex(code) ORDER BY code;
+UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
 SELECT COUNT(*) FROM t1 WHERE a<>'?';
 #
 # Display all characters that have upper or lower case mapping.
@@ -256,7 +256,7 @@ DELETE FROM t3;
 --echo # Sequences that start with a tail or a bad byte,
 --echo # or end with a bad byte, all should be fixed.
 --echo #
-INSERT INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
+INSERT IGNORE INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
 WHERE type1='tail' OR type1='bad' OR type2='bad'
 ORDER BY b;
 SELECT COUNT(*) FROM t3;
@@ -269,7 +269,7 @@ DELETE FROM t3;
 --echo # Sequences that start with an ASCII or an MB2 character,
 --echo # followed by a non-ASCII tail, all should be fixed.
 --echo #
-INSERT INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
+INSERT IGNORE INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
 WHERE (FIND_IN_SET('mb2',type1) OR FIND_IN_SET('ascii',type1))
       AND (FIND_IN_SET('tail',type2) AND NOT FIND_IN_SET('ascii',type2))
 ORDER BY b;
@@ -325,7 +325,7 @@ DELETE FROM t3;
 --echo # Sequences that start with a tail or a bad byte,
 --echo # or have a bad byte, all should be fixed.
 --echo #
-INSERT INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
+INSERT IGNORE INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
 WHERE type1='tail' OR type1='bad' OR type2='bad' OR type3='bad'
 ORDER BY b;
 SELECT COUNT(*) FROM t3;
@@ -338,7 +338,7 @@ DELETE FROM t3;
 --echo # Sequences that start with an ASCII or an MB2 character,
 --echo # followed by a pure non-ASCII tail, all should be fixed.
 --echo #
-INSERT INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
+INSERT IGNORE INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
 WHERE (FIND_IN_SET('mb2',type1) OR FIND_IN_SET('ascii',type1))
       AND type2='tail'
 ORDER BY b;
@@ -352,7 +352,7 @@ DELETE FROM t3;
 --echo # Sequences that consist of two ASCII or MB2 characters,
 --echo # followed by a pure non-ASCII tail, all should be fixed.
 --echo #
-INSERT INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
+INSERT IGNORE INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
 WHERE (FIND_IN_SET('mb2',type1) OR FIND_IN_SET('ascii',type1)) AND
       (FIND_IN_SET('mb2',type2) OR FIND_IN_SET('ascii',type2)) AND
       type3='tail'
@@ -368,7 +368,7 @@ DELETE FROM t3;
 --echo # Sequences that consist of two MB2 characters,
 --echo # followed by a non-ASCII head or tail, all should be fixed.
 --echo #
-INSERT INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
+INSERT IGNORE INTO t3 (b,c,comment) SELECT b,b,comment FROM t2
 WHERE FIND_IN_SET('mb2',type1) AND FIND_IN_SET('mb2',type2)
       AND NOT FIND_IN_SET('ascii',type3)
       AND NOT FIND_IN_SET('mb2',type3)
@@ -412,7 +412,7 @@ DELETE FROM t3;
 #--echo #
 #--echo # Other sequences
 #--echo #
-INSERT INTO t3 (b,c,comment) SELECT b,b,comment FROM t2 ORDER BY b;
+INSERT IGNORE INTO t3 (b,c,comment) SELECT b,b,comment FROM t2 ORDER BY b;
 SELECT COUNT(*) FROM t3;
 SELECT HEX(c),comment FROM t3 WHERE b=c ORDER BY b;
 SELECT HEX(c),HEX(b),comment FROM t3 WHERE b<>c ORDER BY b;
@@ -428,8 +428,10 @@ DROP TABLE t1;
 --echo #
 --echo # MDEV-7661 Unexpected result for: CAST(0xHHHH AS CHAR CHARACTER SET xxx) for incorrect byte sequences
 --echo #
+set sql_mode='';
 SELECT HEX(CAST(0xA341 AS CHAR CHARACTER SET gb2312));
 SELECT HEX(CONVERT(CAST(0xA341 AS CHAR CHARACTER SET gb2312) USING utf8));
+set sql_mode=default;
 
 
 --echo #
diff --git a/mysql-test/t/ctype_latin1.test b/mysql-test/t/ctype_latin1.test
index 78d5f819f57..d40cc8b459b 100644
--- a/mysql-test/t/ctype_latin1.test
+++ b/mysql-test/t/ctype_latin1.test
@@ -237,7 +237,7 @@ SELECT HEX(CONVERT('Âx' USING utf8));
 SELECT HEX(CONVERT('Âx' USING latin1));
 SET NAMES utf8;
 CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1);
-INSERT INTO t1 VALUES ('Â'),('Â#');
+INSERT IGNORE INTO t1 VALUES ('Â'),('Â#');
 SHOW WARNINGS;
 SELECT HEX(a),a FROM t1;
 DROP TABLE t1;
diff --git a/mysql-test/t/ctype_ldml.test b/mysql-test/t/ctype_ldml.test
index 1d58daa89e4..a6cba4b9780 100644
--- a/mysql-test/t/ctype_ldml.test
+++ b/mysql-test/t/ctype_ldml.test
@@ -120,7 +120,7 @@ DROP TABLE t1;
 # Crash happened with a user-defined utf8 collation,
 # on attempt to insert a string longer than the column can store.
 CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci);
-INSERT INTO t1 SELECT REPEAT('a',11);
+INSERT IGNORE INTO t1 SELECT REPEAT('a',11);
 DROP TABLE t1;
 
 #
diff --git a/mysql-test/t/ctype_many.test b/mysql-test/t/ctype_many.test
index a51ead67eb2..a619b16416c 100644
--- a/mysql-test/t/ctype_many.test
+++ b/mysql-test/t/ctype_many.test
@@ -175,6 +175,7 @@ SET CHARACTER SET utf8;
 ALTER TABLE t1 ADD ucs2_f CHAR(32) CHARACTER SET ucs2;
 
 --disable_warnings
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 CHANGE ucs2_f ucs2_f CHAR(32) UNICODE NOT NULL;
 --enable_warnings
 
@@ -310,7 +311,7 @@ DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32);
 CREATE TABLE t2 (a VARCHAR(10) CHARACTER SET ucs2);
 INSERT INTO t1 VALUES (0x10082), (0x12345);
-INSERT INTO t2 SELECT * FROM t1;
+INSERT IGNORE INTO t2 SELECT * FROM t1;
 SELECT HEX(a) FROM t2;
 DROP TABLE t1;
 DROP TABLE t2;
diff --git a/mysql-test/t/ctype_mb.test b/mysql-test/t/ctype_mb.test
index 6e369e3fa0b..cd5ab503d55 100644
--- a/mysql-test/t/ctype_mb.test
+++ b/mysql-test/t/ctype_mb.test
@@ -10,7 +10,7 @@ SHOW CREATE TABLE t1;
 DELETE FROM t1;
 ALTER TABLE t1 ADD c3 CHAR(4) CHARACTER SET utf8;
 SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ('aaaabbbbccccdddd','aaaabbbbccccdddd','aaaabbbbccccdddd');
+INSERT IGNORE INTO t1 VALUES ('aaaabbbbccccdddd','aaaabbbbccccdddd','aaaabbbbccccdddd');
 SELECT * FROM t1;
 DROP TABLE t1;
 
diff --git a/mysql-test/t/ctype_recoding.test b/mysql-test/t/ctype_recoding.test
index 81c04fc9c30..ba2e65b3626 100644
--- a/mysql-test/t/ctype_recoding.test
+++ b/mysql-test/t/ctype_recoding.test
@@ -129,7 +129,7 @@ CREATE TABLE `goodÐÌÏÈÏ` (a int);
 set names latin1;
 create table t1 (a char(10) character set koi8r, b text character set koi8r);
 insert into t1 values ('test','test');
-insert into t1 values ('ÊÃÕË','ÊÃÕË');
+insert ignore into t1 values ('ÊÃÕË','ÊÃÕË');
 drop table t1;
 
 #
diff --git a/mysql-test/t/ctype_sjis.test b/mysql-test/t/ctype_sjis.test
index a962f69003d..9a8ce414c14 100644
--- a/mysql-test/t/ctype_sjis.test
+++ b/mysql-test/t/ctype_sjis.test
@@ -140,10 +140,10 @@ ORDER BY head, tail;
 #
 # Populate Half Width Kana: [A1..DF]
 # 
-INSERT t1 (code) SELECT head FROM head WHERE (head BETWEEN 'A1' AND 'DF');
+INSERT IGNORE t1 (code) SELECT head FROM head WHERE (head BETWEEN 'A1' AND 'DF');
 DROP TEMPORARY TABLE head, tail;
 SHOW CREATE TABLE t1;
-UPDATE t1 SET a=unhex(code) ORDER BY code;
+UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
 SELECT COUNT(*) FROM t1;
 SELECT COUNT(*) FROM t1 WHERE a<>'?' AND OCTET_LENGTH(a)=1;
 SELECT COUNT(*) FROM t1 WHERE a<>'' AND OCTET_LENGTH(a)=2;
diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test
index 5b8c2f74528..6745d64d11b 100644
--- a/mysql-test/t/ctype_ucs.test
+++ b/mysql-test/t/ctype_ucs.test
@@ -413,7 +413,7 @@ SELECT * FROM t1;
 DROP TABLE t1;
 CREATE TABLE t1 (Field1 int(10) unsigned default '0');
 # this should generate a "Data truncated" warning
-INSERT INTO t1 VALUES ('-1');
+INSERT IGNORE INTO t1 VALUES ('-1');
 DROP TABLE t1;
 SET NAMES latin1;
 
@@ -794,6 +794,7 @@ SET NAMES latin1;
 --echo #
 --echo # Bug #13832953 	MY_STRNXFRM_UNICODE: ASSERTION `SRC' FAILED
 --echo #
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 (c1 SET('','') CHARACTER SET ucs2);
 INSERT INTO t1 VALUES ('');
 SELECT COALESCE(c1) FROM t1 ORDER BY 1;
@@ -897,7 +898,7 @@ SET sql_mode='strict_all_tables';
 INSERT INTO t1 VALUES (CONVERT('9e99999999' USING ucs2));
 SET sql_mode=DEFAULT;
 
-INSERT INTO t1 VALUES (CONVERT('aaa' USING ucs2)); 
+INSERT IGNORE INTO t1 VALUES (CONVERT('aaa' USING ucs2)); 
 
 DROP TABLE t1;
 
diff --git a/mysql-test/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test
index 17aa3850219..ff9c61c05ba 100644
--- a/mysql-test/t/ctype_ujis.test
+++ b/mysql-test/t/ctype_ujis.test
@@ -1254,13 +1254,13 @@ CREATE TABLE t1 AS SELECT 'XXXXXX' AS code, ' ' AS a LIMIT 0;
 # Pupulate JIS-X-0201 range (Half Width Kana)
 # Valid characters: [8E][A1-DF]
 #
-INSERT INTO t1 (code) SELECT concat('8E', head) FROM head
+INSERT IGNORE INTO t1 (code) SELECT concat('8E', head) FROM head
 WHERE (head BETWEEN 'A1' AND 'DF') ORDER BY head;
 #
 # Populate JIS-X-0208 range
 # Expected valid range: [A1..FE][A1..FE]
 #
-INSERT INTO t1 (code) SELECT concat(head, tail)
+INSERT IGNORE INTO t1 (code) SELECT concat(head, tail)
 FROM head, tail
 WHERE (head BETWEEN '80' AND 'FF') AND (head NOT BETWEEN '8E' AND '8F')
 AND   (tail BETWEEN '20' AND 'FF')
@@ -1269,14 +1269,14 @@ ORDER BY head, tail;
 # Populate JIS-X-0212 range
 # Expected valid range: [8F][A1..FE][A1..FE]
 #
-INSERT INTO t1 (code) SELECT concat('8F', head, tail)
+INSERT IGNORE INTO t1 (code) SELECT concat('8F', head, tail)
 FROM head, tail
 WHERE (head BETWEEN '80' AND 'FF') AND (tail BETWEEN '80' AND 'FF')
 ORDER BY head, tail;
 DROP TEMPORARY TABLE head, tail;
 SHOW CREATE TABLE t1;
 
-UPDATE t1 SET a=unhex(code) ORDER BY code;
+UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
 SELECT COUNT(*) FROM t1;
 SELECT COUNT(*) FROM t1 WHERE a<>'?';
 SELECT COUNT(*) FROM t1 WHERE a<>'' AND OCTET_LENGTH(a)=2;
@@ -1361,7 +1361,7 @@ set collation_connection=ujis_bin;
 --echo # MDEV-6776 ujis and eucjmps erroneously accept 0x8EA0 as a valid byte sequence
 --echo #
 CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ujis);
-INSERT INTO t1 VALUES (0x8EA0);
+INSERT IGNORE INTO t1 VALUES (0x8EA0);
 SELECT HEX(a), CHAR_LENGTH(a) FROM t1;
 DROP TABLE t1;
 --error ER_INVALID_CHARACTER_STRING
@@ -1406,7 +1406,7 @@ SELECT HEX(a) FROM t1 ORDER BY a;DROP TABLE t1;
 --echo # MDEV-9842 LOAD DATA INFILE does not work well with a TEXT column when using sjis
 --echo #
 CREATE TABLE t1 (a TEXT CHARACTER SET ujis);
-LOAD DATA INFILE '../../std_data/loaddata/mdev9823.ujis.txt' INTO TABLE t1 CHARACTER SET ujis IGNORE 4 LINES;
+LOAD DATA INFILE '../../std_data/loaddata/mdev9823.ujis.txt' IGNORE INTO TABLE t1 CHARACTER SET ujis IGNORE 4 LINES;
 SELECT HEX(a) FROM t1;
 DROP TABLE t1;
 
diff --git a/mysql-test/t/ctype_ujis_ucs2.test b/mysql-test/t/ctype_ujis_ucs2.test
index 1a8702b2aa9..8616eea36d1 100644
--- a/mysql-test/t/ctype_ujis_ucs2.test
+++ b/mysql-test/t/ctype_ujis_ucs2.test
@@ -1243,7 +1243,7 @@ update t1 set name='User defined range #2' where ujis >= 0x8FF5A1 and ujis <= 0x
 # Other characters are not assigned
 update t1 set name='UNASSIGNED' where name='';
 
-update t1 set ucs2=ujis, ujis2=ucs2;
+update ignore t1 set ucs2=ujis, ujis2=ucs2;
 --echo Characters with safe Unicode round trip
 select hex(ujis), hex(ucs2), hex(ujis2), name from t1 where ujis=ujis2 order by ujis;
 --echo Characters with unsafe Unicode round trip
@@ -1299,7 +1299,7 @@ insert into t1 (ucs2,name) values (0xFFE0,'U+FFE0 FULLWIDTH CENT SIGN');
 insert into t1 (ucs2,name) values (0xFFE1,'U+FFE1 FULLWIDTH POUND SIGN');
 insert into t1 (ucs2,name) values (0xFFE2,'U+FFE2 FULLWIDTH NOT SIGN');
 insert into t1 (ucs2,name) values (0xFFE4,'U+FFE4 FULLWIDTH BROKEN BAR');
-update t1 set ujis=ucs2;
+update ignore t1 set ujis=ucs2;
 select hex(ucs2),hex(ujis),name from t1 order by name;
 drop table t1;
 
diff --git a/mysql-test/t/ctype_utf16.test b/mysql-test/t/ctype_utf16.test
index c986309707e..fc16a4b8400 100644
--- a/mysql-test/t/ctype_utf16.test
+++ b/mysql-test/t/ctype_utf16.test
@@ -570,6 +570,7 @@ drop table t1;
 #
 create table t1 (s1 varchar(50) character set ucs2);
 insert into t1 values (0xdf84);
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 modify column s1 varchar(50) character set utf16;
 select hex(s1) from t1;
 drop table t1;
@@ -578,7 +579,7 @@ drop table t1;
 #
 create table t1 (s1 varchar(5) character set ucs2, s2 varchar(5) character set utf16);
 insert into t1 (s1) values (0xdf84);
-update t1 set s2 = s1;
+update ignore t1 set s2 = s1;
 select hex(s2) from t1;
 drop table t1;
 
@@ -660,7 +661,7 @@ select left('aaa','1');
 #
 create table t1 (a int);
 insert into t1 values ('-1234.1e2');
-insert into t1 values ('-1234.1e2xxxx');
+insert ignore into t1 values ('-1234.1e2xxxx');
 insert into t1 values ('-1234.1e2    ');
 select * from t1;
 drop table t1;
@@ -670,13 +671,14 @@ drop table t1;
 #
 create table t1 (a int);
 insert into t1 values ('1 ');
-insert into t1 values ('1 x');
+insert ignore into t1 values ('1 x');
 select * from t1;
 drop table t1;
 
 #
 # Testing auto-conversion to TEXT
 #
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1 (a varchar(17000) character set utf16);
 show create table t1;
 drop table t1;
diff --git a/mysql-test/t/ctype_utf16le.test b/mysql-test/t/ctype_utf16le.test
index 665443bb278..79cf875852a 100644
--- a/mysql-test/t/ctype_utf16le.test
+++ b/mysql-test/t/ctype_utf16le.test
@@ -60,7 +60,7 @@ CREATE TABLE t1 (a VARCHAR(10), pad INT, b VARCHAR(10)) CHARACTER SET utf16le;
 INSERT INTO t1 VALUES (_ucs2 X'0420', 10, _ucs2 X'0421');
 INSERT INTO t1 VALUES (_ucs2 X'0420', 10, _ucs2 X'04210422');
 INSERT INTO t1 VALUES (_ucs2 X'0420', 10, _ucs2 X'042104220423');
-INSERT INTO t1 VALUES (_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423');
+INSERT IGNORE INTO t1 VALUES (_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423');
 INSERT INTO t1 VALUES (_utf32 X'010000', 10, _ucs2 X'0421');
 INSERT INTO t1 VALUES (_ucs2 X'0421', 10, _utf32 X'010000');
 SELECT a, pad, b, LPAD(a, pad, b), HEX(LPAD(a, pad, b)) FROM t1;
@@ -492,6 +492,7 @@ DROP TABLE t1;
 --echo #
 CREATE TABLE t1 (s1 VARCHAR(50) CHARACTER SET ucs2);
 INSERT INTO t1 VALUES (0xDF84);
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY column s1 VARCHAR(50) CHARACTER SET utf16le;
 SELECT HEX(s1) FROM t1;
 DROP TABLE t1;
@@ -501,7 +502,7 @@ DROP TABLE t1;
 --echo #
 CREATE TABLE t1 (s1 VARCHAR(5) CHARACTER SET ucs2, s2 VARCHAR(5) CHARACTER SET utf16le);
 INSERT INTO t1 (s1) VALUES (0xdf84);
-UPDATE t1 set s2 = s1;
+UPDATE IGNORE t1 set s2 = s1;
 SELECT HEX(s2) FROM t1;
 DROP TABLE t1;
 
@@ -603,7 +604,7 @@ DROP TABLE t1;
 --echo #
 CREATE TABLE t1 (a int);
 INSERT INTO t1 VALUES ('-1234.1e2');
-INSERT INTO t1 VALUES ('-1234.1e2xxxx');
+INSERT IGNORE INTO t1 VALUES ('-1234.1e2xxxx');
 INSERT INTO t1 VALUES ('-1234.1e2    ');
 INSERT INTO t1 VALUES ('123');
 INSERT INTO t1 VALUES ('-124');
@@ -629,7 +630,7 @@ DROP TABLE t1;
 --echo #
 CREATE TABLE t1 (a int);
 INSERT INTO t1 VALUES ('1 ');
-INSERT INTO t1 VALUES ('1 x');
+INSERT IGNORE INTO t1 VALUES ('1 x');
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -637,6 +638,7 @@ DROP TABLE t1;
 --echo #
 --echo # Testing auto-conversion to TEXT
 --echo #
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 (a VARCHAR(17000) CHARACTER SET utf16le);
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
diff --git a/mysql-test/t/ctype_utf32.test b/mysql-test/t/ctype_utf32.test
index f113f046d42..bb2ce1cb6e6 100644
--- a/mysql-test/t/ctype_utf32.test
+++ b/mysql-test/t/ctype_utf32.test
@@ -598,19 +598,19 @@ drop table t1;
 #
 create table t1 (utf32 varchar(2) character set utf32);
 --echo Wrong character with pad
-insert into t1 values (0x110000);
+insert ignore into t1 values (0x110000);
 --echo Wrong chsaracter without pad
-insert into t1 values (0x00110000);
+insert ignore into t1 values (0x00110000);
 --echo Wrong character with pad followed by another wrong character
-insert into t1 values (0x11000000110000);
+insert ignore into t1 values (0x11000000110000);
 --echo Good character with pad followed by bad character
-insert into t1 values (0x10000000110000);
+insert ignore into t1 values (0x10000000110000);
 --echo Good character without pad followed by bad character
-insert into t1 values (0x0010000000110000);
+insert ignore into t1 values (0x0010000000110000);
 --echo Wrong character with the second byte higher than 0x10
-insert into t1 values (0x00800037);
+insert ignore into t1 values (0x00800037);
 --echo Wrong character with pad with the second byte higher than 0x10
-insert into t1 values (0x00800037);
+insert ignore into t1 values (0x00800037);
 drop table t1;
 
 #
@@ -700,7 +700,7 @@ select left('aaa','1');
 #
 create table t1 (a int);
 insert into t1 values ('-1234.1e2');
-insert into t1 values ('-1234.1e2xxxx');
+insert ignore into t1 values ('-1234.1e2xxxx');
 insert into t1 values ('-1234.1e2    ');
 select * from t1;
 drop table t1;
@@ -710,13 +710,14 @@ drop table t1;
 #
 create table t1 (a int);
 insert into t1 values ('1 ');
-insert into t1 values ('1 x');
+insert ignore into t1 values ('1 x');
 select * from t1;
 drop table t1;
 
 #
 # Testing auto-conversion to TEXT
 #
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1 (a varchar(17000) character set utf32);
 show create table t1;
 drop table t1;
@@ -819,10 +820,10 @@ DROP TABLE t1;
 --echo #
 CREATE TABLE t1 (utf32 CHAR(5) CHARACTER SET utf32, latin1 CHAR(5) CHARACTER SET latin1);
 INSERT INTO t1 (utf32) VALUES (0xc581);
-UPDATE t1 SET latin1 = utf32;
+UPDATE IGNORE t1 SET latin1 = utf32;
 DELETE FROM t1;
 INSERT INTO t1 (utf32) VALUES (0x100cc);
-UPDATE t1 SET latin1 = utf32;
+UPDATE IGNORE t1 SET latin1 = utf32;
 DROP TABLE t1;
 
 --echo #
diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test
index 0ac0bcc1ddb..d1fe93d9251 100644
--- a/mysql-test/t/ctype_utf8.test
+++ b/mysql-test/t/ctype_utf8.test
@@ -129,26 +129,26 @@ set LC_TIME_NAMES='en_US';
 #
 set names koi8r;
 create table t1 (s1 char(1) character set utf8);
-insert into t1 values (_koi8r'ÁÂ');
+insert ignore into t1 values (_koi8r'ÁÂ');
 select s1,hex(s1),char_length(s1),octet_length(s1) from t1;
 drop table t1;
 
 create table t1 (s1 tinytext character set utf8);
-insert into t1 select repeat('a',300);
-insert into t1 select repeat('Ñ',300);
-insert into t1 select repeat('aÑ',300);
-insert into t1 select repeat('Ña',300);
-insert into t1 select repeat('ÑÑ',300);
+insert ignore into t1 select repeat('a',300);
+insert ignore into t1 select repeat('Ñ',300);
+insert ignore into t1 select repeat('aÑ',300);
+insert ignore into t1 select repeat('Ña',300);
+insert ignore into t1 select repeat('ÑÑ',300);
 select hex(s1) from t1;
 select length(s1),char_length(s1) from t1;
 drop table t1;
 
 create table t1 (s1 text character set utf8);
-insert into t1 select repeat('a',66000);
-insert into t1 select repeat('Ñ',66000);
-insert into t1 select repeat('aÑ',66000);
-insert into t1 select repeat('Ña',66000);
-insert into t1 select repeat('ÑÑ',66000);
+insert ignore into t1 select repeat('a',66000);
+insert ignore into t1 select repeat('Ñ',66000);
+insert ignore into t1 select repeat('aÑ',66000);
+insert ignore into t1 select repeat('Ña',66000);
+insert ignore into t1 select repeat('ÑÑ',66000);
 select length(s1),char_length(s1) from t1;
 drop table t1;
 
@@ -156,17 +156,17 @@ drop table t1;
 # Bug #2368 Multibyte charsets do not check that incoming data is well-formed
 #
 create table t1 (s1 char(10) character set utf8);
-insert into t1 values (0x41FF);
+insert ignore into t1 values (0x41FF);
 select hex(s1) from t1;
 drop table t1;
 
 create table t1 (s1 varchar(10) character set utf8);
-insert into t1 values (0x41FF);
+insert ignore into t1 values (0x41FF);
 select hex(s1) from t1;
 drop table t1;
 
 create table t1 (s1 text character set utf8);
-insert into t1 values (0x41FF);
+insert ignore into t1 values (0x41FF);
 select hex(s1) from t1;
 drop table t1;
 
@@ -837,6 +837,7 @@ DROP TABLE t1,t2;
 #
 create table t1 (a char(20) character set utf8);
 insert into t1 values ('123456'),('аМЎрей');
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 modify a char(2) character set utf8;
 select char_length(a), length(a), a from t1 order by a;
 drop table t1;
@@ -1215,15 +1216,19 @@ create table t1 (
 insert into t1 (a) values ('abcdefghijklmnopqrstuvwxyz');
 select * from t1;
 # varchar to varchar
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a varchar(20) character set utf8 not null;
 select * from t1;
 # varchar to char
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a char(15) character set utf8 not null;
 select * from t1;
 # char to char
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a char(10) character set utf8 not null;
 select * from t1;
 # char to varchar
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a varchar(5) character set utf8 not null;
 select * from t1;
 drop table t1;
@@ -1235,6 +1240,7 @@ create table t1 (
   a varchar(4000) not null
 ) default character set utf8;
 insert into t1 values (repeat('a',4000));
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a varchar(3000) character set utf8 not null;
 select length(a) from t1;
 drop table t1;
@@ -1674,6 +1680,7 @@ SET NAMES utf8;
 CREATE TABLE t1 (a TEXT CHARACTER SET utf8);
 INSERT INTO t1 VALUES (REPEAT('',100));
 SELECT OCTET_LENGTH(a) FROM t1;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY a TINYTEXT CHARACTER SET utf8;
 SELECT OCTET_LENGTH(a),a FROM t1;
 DROP TABLE t1;
@@ -2007,7 +2014,7 @@ DROP TABLE t1;
 --echo # MDEV-9842 LOAD DATA INFILE does not work well with a TEXT column when using sjis
 --echo #
 CREATE TABLE t1 (a TEXT CHARACTER SET utf8);
-LOAD DATA INFILE '../../std_data/loaddata/mdev9823.utf8mb4.txt' INTO TABLE t1 CHARACTER SET utf8 IGNORE 4 LINES;
+LOAD DATA INFILE '../../std_data/loaddata/mdev9823.utf8mb4.txt' IGNORE INTO TABLE t1 CHARACTER SET utf8 IGNORE 4 LINES;
 SELECT HEX(a) FROM t1;
 DROP TABLE t1;
 
@@ -2015,7 +2022,7 @@ DROP TABLE t1;
 --echo # MDEV-9874 LOAD XML INFILE does not handle well broken multi-byte characters
 --echo #
 CREATE TABLE t1 (a TEXT CHARACTER SET utf8);
-LOAD XML INFILE '../../std_data/loaddata/mdev9874.xml' INTO TABLE t1 CHARACTER SET utf8 ROWS IDENTIFIED BY '<row>';
+LOAD XML INFILE '../../std_data/loaddata/mdev9874.xml' IGNORE INTO TABLE t1 CHARACTER SET utf8 ROWS IDENTIFIED BY '<row>';
 SELECT HEX(a) FROM t1;
 DROP TABLE t1;
 
diff --git a/mysql-test/t/ctype_utf8mb4.test b/mysql-test/t/ctype_utf8mb4.test
index dfed66e7951..94b5d77ad90 100644
--- a/mysql-test/t/ctype_utf8mb4.test
+++ b/mysql-test/t/ctype_utf8mb4.test
@@ -127,26 +127,26 @@ set LC_TIME_NAMES='en_US';
 #
 set names koi8r;
 create table t1 (s1 char(1) character set utf8mb4);
-insert into t1 values (_koi8r'ÁÂ');
+insert ignore into t1 values (_koi8r'ÁÂ');
 select s1,hex(s1),char_length(s1),octet_length(s1) from t1;
 drop table t1;
 
 create table t1 (s1 tinytext character set utf8mb4);
-insert into t1 select repeat('a',300);
-insert into t1 select repeat('Ñ',300);
-insert into t1 select repeat('aÑ',300);
-insert into t1 select repeat('Ña',300);
-insert into t1 select repeat('ÑÑ',300);
+insert ignore into t1 select repeat('a',300);
+insert ignore into t1 select repeat('Ñ',300);
+insert ignore into t1 select repeat('aÑ',300);
+insert ignore into t1 select repeat('Ña',300);
+insert ignore into t1 select repeat('ÑÑ',300);
 select hex(s1) from t1;
 select length(s1),char_length(s1) from t1;
 drop table t1;
 
 create table t1 (s1 text character set utf8mb4);
-insert into t1 select repeat('a',66000);
-insert into t1 select repeat('Ñ',66000);
-insert into t1 select repeat('aÑ',66000);
-insert into t1 select repeat('Ña',66000);
-insert into t1 select repeat('ÑÑ',66000);
+insert ignore into t1 select repeat('a',66000);
+insert ignore into t1 select repeat('Ñ',66000);
+insert ignore into t1 select repeat('aÑ',66000);
+insert ignore into t1 select repeat('Ña',66000);
+insert ignore into t1 select repeat('ÑÑ',66000);
 select length(s1),char_length(s1) from t1;
 drop table t1;
 
@@ -154,17 +154,17 @@ drop table t1;
 # Bug #2368 Multibyte charsets do not check that incoming data is well-formed
 #
 create table t1 (s1 char(10) character set utf8mb4);
-insert into t1 values (0x41FF);
+insert ignore into t1 values (0x41FF);
 select hex(s1) from t1;
 drop table t1;
 
 create table t1 (s1 varchar(10) character set utf8mb4);
-insert into t1 values (0x41FF);
+insert ignore into t1 values (0x41FF);
 select hex(s1) from t1;
 drop table t1;
 
 create table t1 (s1 text character set utf8mb4);
-insert into t1 values (0x41FF);
+insert ignore into t1 values (0x41FF);
 select hex(s1) from t1;
 drop table t1;
 
@@ -856,6 +856,7 @@ DROP TABLE t1,t2;
 #
 create table t1 (a char(20) character set utf8mb4);
 insert into t1 values ('123456'),('аМЎрей');
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 modify a char(2) character set utf8mb4;
 select char_length(a), length(a), a from t1 order by a;
 drop table t1;
@@ -1225,15 +1226,19 @@ create table t1 (
 insert into t1 (a) values ('abcdefghijklmnopqrstuvwxyz');
 select * from t1;
 # varchar to varchar
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a varchar(20) character set utf8mb4 not null;
 select * from t1;
 # varchar to char
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a char(15) character set utf8mb4 not null;
 select * from t1;
 # char to char
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a char(10) character set utf8mb4 not null;
 select * from t1;
 # char to varchar
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a varchar(5) character set utf8mb4 not null;
 select * from t1;
 drop table t1;
@@ -1245,6 +1250,7 @@ create table t1 (
   a varchar(4000) not null
 ) default character set utf8mb4;
 insert into t1 values (repeat('a',4000));
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a varchar(3000) character set utf8mb4 not null;
 select length(a) from t1;
 drop table t1;
@@ -1490,7 +1496,7 @@ create table t1 (utf8mb4 char(1) character set utf8mb4);
 --echo Testing [F0][90..BF][80..BF][80..BF]
 insert into t1 values (0xF0908080);
 insert into t1 values (0xF0BFBFBF);
-insert into t1 values (0xF08F8080);
+insert ignore into t1 values (0xF08F8080);
 select hex(utf8mb4) from t1 order by binary utf8mb4;
 delete from t1;
 
@@ -1503,7 +1509,7 @@ delete from t1;
 --echo Testing [F4][80..8F][80..BF][80..BF]
 insert into t1 values (0xF4808080);
 insert into t1 values (0xF48F8080);
-insert into t1 values (0xF4908080);
+insert ignore into t1 values (0xF4908080);
 select hex(utf8mb4) from t1 order by binary utf8mb4;
 drop table t1;
 
@@ -1581,12 +1587,12 @@ INSERT INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859
 #  Mix of 3-byte and 4-byte chars 
 INSERT INTO t1 VALUES (65131, x'efb9abf09d849ef09d859ef09d859ef09d8480f09d859fefb9abefb9abf09d85a0efb9ab');
 # All from musical chars, but 11 instead of 10 chars. truncated
-INSERT INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859ff09d859ff09d85a0f09d85a0f09d8480f09d85a0');
+INSERT IGNORE INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859ff09d859ff09d85a0f09d85a0f09d8480f09d85a0');
 
 SELECT u_decimal, hex(utf8mb4_encoding) FROM t1 ORDER BY utf8mb4_encoding COLLATE utf8mb4_general_ci, BINARY utf8mb4_encoding;
 
 # First invalid 4 byte value
-INSERT INTO t1 VALUES (1114111, x'f5808080');
+INSERT IGNORE INTO t1 VALUES (1114111, x'f5808080');
 
 SELECT character_maximum_length, character_octet_length FROM information_schema.columns WHERE
        table_name= 't1' AND column_name= 'utf8mb4_encoding';
@@ -1603,13 +1609,13 @@ INSERT INTO t2 VALUES (42856, x'ea9da8');
 # SMALL COMMERCIAL AT
 INSERT INTO t2 VALUES (65131, x'efb9ab');
 # <Plane 16 Private Use, Last> (last 4 byte character)
-INSERT INTO t2 VALUES (1114111, x'f48fbfbf');
+INSERT IGNORE INTO t2 VALUES (1114111, x'f48fbfbf');
 
 SELECT character_maximum_length, character_octet_length FROM information_schema.columns WHERE
        table_name= 't2' AND column_name= 'utf8mb3_encoding';
 
 #  Update a 3-byte char col with a 4-byte char, error
-UPDATE t2 SET utf8mb3_encoding= x'f48fbfbd' where u_decimal= 42856;
+UPDATE IGNORE t2 SET utf8mb3_encoding= x'f48fbfbd' where u_decimal= 42856;
 
 #  Update to a 3-byte char casted to 4-byte, error?
 UPDATE t2 SET utf8mb3_encoding= _utf8mb4 x'ea9da8' where u_decimal= 42856;
@@ -1627,6 +1633,7 @@ SELECT count(*) FROM t1, t2
    WHERE t1.utf8mb4_encoding > t2.utf8mb3_encoding;
 
 # Alter from 4-byte charset to 3-byte charset, error
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 CONVERT TO CHARACTER SET utf8;
 SHOW CREATE TABLE t1;
 SELECT u_decimal,hex(utf8mb4_encoding),utf8mb4_encoding FROM t1;
@@ -1737,6 +1744,7 @@ CREATE TABLE t1 (
 ALTER TABLE t1 ADD INDEX (subject); 
 
 # Alter old 'utf8' table to new 'utf8mb4'
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1
   DEFAULT CHARACTER SET utf8,
   MODIFY subject varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
@@ -1816,6 +1824,7 @@ SET NAMES utf8mb4;
 CREATE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
 INSERT INTO t1 VALUES (REPEAT('😎',100));
 SELECT OCTET_LENGTH(a) FROM t1;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY a TINYTEXT CHARACTER SET utf8mb4;
 SELECT OCTET_LENGTH(a),a FROM t1;
 DROP TABLE t1;
@@ -1892,7 +1901,7 @@ CREATE TABLE t1 (
   a VARCHAR(32) CHARACTER SET utf8mb4,
   b VARCHAR(32) CHARACTER SET utf8
 );
-INSERT INTO t1 SELECT 'a 😁 b', 'a 😁 b';
+INSERT IGNORE INTO t1 SELECT 'a 😁 b', 'a 😁 b';
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -1909,7 +1918,7 @@ CREATE TABLE t1 (
   a VARCHAR(32) CHARACTER SET utf8mb4,
   b VARCHAR(32) CHARACTER SET utf8
 );
-INSERT INTO t1 SELECT 'a 😁 b', 'a 😁 b';
+INSERT IGNORE INTO t1 SELECT 'a 😁 b', 'a 😁 b';
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -1953,7 +1962,7 @@ DROP FUNCTION f1;
 --echo # MDEV-9842 LOAD DATA INFILE does not work well with a TEXT column when using sjis
 --echo #
 CREATE TABLE t1 (a TEXT CHARACTER SET utf8mb4);
-LOAD DATA INFILE '../../std_data/loaddata/mdev9823.utf8mb4.txt' INTO TABLE t1 CHARACTER SET utf8mb4 IGNORE 4 LINES;
+LOAD DATA INFILE '../../std_data/loaddata/mdev9823.utf8mb4.txt' IGNORE INTO TABLE t1 CHARACTER SET utf8mb4 IGNORE 4 LINES;
 SELECT HEX(a) FROM t1;
 DROP TABLE t1;
 
diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test
index af089f8dc94..d75966cd2dc 100644
--- a/mysql-test/t/default.test
+++ b/mysql-test/t/default.test
@@ -106,12 +106,12 @@ drop table t2;
 #      multiple-row statement, the preceding rows will have been inserted.
 #
 create table bug20691 (i int, d datetime NOT NULL, dn datetime not null default '0000-00-00 00:00:00');
-insert into bug20691 values (1, DEFAULT, DEFAULT), (1, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (1, DEFAULT, DEFAULT);
-insert into bug20691 (i) values (2);
+insert ignore into bug20691 values (1, DEFAULT, DEFAULT), (1, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (1, DEFAULT, DEFAULT);
+insert ignore into bug20691 (i) values (2);
 desc bug20691;
-insert into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (3, DEFAULT, DEFAULT);
-insert into bug20691 (i) values (4);
-insert into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (5, DEFAULT, DEFAULT);
+insert ignore into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (3, DEFAULT, DEFAULT);
+insert ignore into bug20691 (i) values (4);
+insert ignore into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (5, DEFAULT, DEFAULT);
 SET sql_mode = 'ALLOW_INVALID_DATES';
 insert into bug20691 values (6, DEFAULT, DEFAULT), (6, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (6, DEFAULT, DEFAULT);
 SET sql_mode = 'STRICT_ALL_TABLES';
@@ -809,7 +809,7 @@ DROP TABLE t1;
 --echo #
 CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_TIME(6)));
 SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES();
+INSERT IGNORE INTO t1 VALUES();
 DROP TABLE t1;
 
 --echo #
@@ -817,7 +817,7 @@ DROP TABLE t1;
 --echo #
 CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_DATE));
 SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES();
+INSERT IGNORE INTO t1 VALUES();
 DROP TABLE t1;
 
 
@@ -852,7 +852,7 @@ CREATE TABLE t1 (
   b DECIMAL(30,0) DEFAULT COALESCE(CURRENT_TIMESTAMP(6))
 );
 SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ();
+INSERT IGNORE INTO t1 VALUES ();
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -1403,13 +1403,13 @@ CREATE TABLE t1 (a DECIMAL(10,3),
                  b VARCHAR(10) DEFAULT CAST(a AS CHAR(10)),
                  c VARCHAR(10) DEFAULT CAST(a AS CHAR(4)));
 SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (123.456);
+INSERT IGNORE INTO t1 (a) VALUES (123.456);
 SELECT * FROM t1;
 DROP TABLE t1;
 
 CREATE TABLE t1 (a INT, b INT UNSIGNED DEFAULT CAST(a AS UNSIGNED));
 SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (-1);
+INSERT IGNORE INTO t1 (a) VALUES (-1);
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -1629,7 +1629,7 @@ CREATE TABLE t1 (
   x VARCHAR(30) DEFAULT EXPORT_SET(bits, v_on, v_off, v_separator, number_of_bits)
 );
 SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (0x50006,'Y','N','',64,DEFAULT);
+INSERT IGNORE INTO t1 VALUES (0x50006,'Y','N','',64,DEFAULT);
 SELECT * FROM t1;
 DROP TABLE t1;
 
diff --git a/mysql-test/t/failed_auth_3909.test b/mysql-test/t/failed_auth_3909.test
index 3179794d155..f72460691ea 100644
--- a/mysql-test/t/failed_auth_3909.test
+++ b/mysql-test/t/failed_auth_3909.test
@@ -7,7 +7,7 @@ source include/not_embedded.inc;
 # the server requests a plugin
 #
 optimize table mysql.user;
-insert mysql.user (user,plugin) values ('foo','bar'),('bar','bar'),('baz','bar');
+insert ignore mysql.user (user,plugin) values ('foo','bar'),('bar','bar'),('baz','bar');
 flush privileges;
 
 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test
index 9dfc49d3dfd..12d36ce645b 100644
--- a/mysql-test/t/fulltext.test
+++ b/mysql-test/t/fulltext.test
@@ -293,6 +293,7 @@ SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück');
 SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
 SELECT t, collation(t),FORMAT(MATCH t AGAINST ('Osnabruck'),6) FROM t1 WHERE MATCH t AGAINST ('Osnabruck');
 #alter table t1 modify t text character set latin1 collate latin1_german2_ci not null;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 modify t varchar(200) collate latin1_german2_ci not null;
 SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück');
 SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index 44b9ad53062..abc86476a6b 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -543,7 +543,7 @@ SELECT LENGTH( GROUP_CONCAT( DISTINCT a ) ) FROM t2;
 CREATE TABLE t3( a TEXT, b INT  );
 INSERT INTO t3 VALUES( REPEAT( 'a', 65534 ), 1 );
 INSERT INTO t3 VALUES( REPEAT( 'a', 65535 ), 2 );
-INSERT INTO t3 VALUES( REPEAT( 'a', 65536 ), 3 );
+INSERT IGNORE INTO t3 VALUES( REPEAT( 'a', 65536 ), 3 );
 SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 1;
 SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 2;
 SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 3;
@@ -566,7 +566,7 @@ drop table t1;
 # Bug #31154: group_concat() and bit fields;
 #
 create table t1(a bit not null);
-insert into t1 values (), (), ();
+insert ignore into t1 values (), (), ();
 select group_concat(distinct a) from t1;
 select group_concat(distinct a order by a) from t1;
 drop table t1;
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index 9e848aa1847..6cbd5a63706 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -477,9 +477,9 @@ CREATE TABLE t1 (
   INDEX(c_datetime), INDEX(c_timestamp), INDEX(c_time), INDEX(c_year),
   INDEX(c_char));
 
-INSERT INTO t1 (c_int) VALUES (1), (2), (3), (4), (5);
-INSERT INTO t1 (c_int) SELECT 0 FROM t1;
-INSERT INTO t1 (c_int) SELECT 0 FROM t1;
+INSERT IGNORE INTO t1 (c_int) VALUES (1), (2), (3), (4), (5);
+INSERT IGNORE INTO t1 (c_int) SELECT 0 FROM t1;
+INSERT IGNORE INTO t1 (c_int) SELECT 0 FROM t1;
 
 --enable_warnings
 
diff --git a/mysql-test/t/func_like.test b/mysql-test/t/func_like.test
index b65bff63298..5026bb76aa3 100644
--- a/mysql-test/t/func_like.test
+++ b/mysql-test/t/func_like.test
@@ -121,7 +121,7 @@ select _cp1251'andre%' like convert('andreÊ%' using cp1251)  escape 'Ê';
 --echo #
 CREATE TABLE t1(a SET('a') NOT NULL, UNIQUE KEY(a));
 CREATE TABLE t2(b INT PRIMARY KEY);
-INSERT INTO t1 VALUES ();
+INSERT IGNORE INTO t1 VALUES ();
 INSERT INTO t2 VALUES (1), (2), (3);
 SELECT 1 FROM t2 JOIN t1 ON 1 LIKE a GROUP BY a;
 DROP TABLE t1, t2;
diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test
index 08349f007e1..f1db36d605f 100644
--- a/mysql-test/t/func_math.test
+++ b/mysql-test/t/func_math.test
@@ -529,7 +529,7 @@ as foo;
 --echo #            my_gcvt: Assertion `width > 0 && to != ((void *)0)' failed
 --echo #
 CREATE TABLE t1(a char(0));
-INSERT INTO t1 (SELECT -pi());
+INSERT IGNORE INTO t1 (SELECT -pi());
 DROP TABLE t1;
 
 --echo #
diff --git a/mysql-test/t/func_regexp_pcre.test b/mysql-test/t/func_regexp_pcre.test
index c9b4c10007d..26294ce2e24 100644
--- a/mysql-test/t/func_regexp_pcre.test
+++ b/mysql-test/t/func_regexp_pcre.test
@@ -168,6 +168,7 @@ DROP TABLE t1;
 EXPLAIN EXTENDED SELECT REGEXP_REPLACE('abc','b','x');
 
 # Check decimals
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 AS SELECT REGEXP_REPLACE('abc','b','x')+0;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
@@ -343,6 +344,7 @@ DROP TABLE t1;
 EXPLAIN EXTENDED SELECT REGEXP_SUBSTR('abc','b');
 
 # Check decimals
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 AS SELECT REGEXP_SUBSTR('abc','b')+0;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
diff --git a/mysql-test/t/func_set.test b/mysql-test/t/func_set.test
index 13f8661db49..47072ba5ffe 100644
--- a/mysql-test/t/func_set.test
+++ b/mysql-test/t/func_set.test
@@ -90,7 +90,7 @@ drop table t1;
 CREATE TABLE t1( a SET('a', 'b', 'c') );
 CREATE TABLE t2( a SET('a', 'b', 'c') );
 
-INSERT INTO t1 VALUES ('d');
+INSERT IGNORE INTO t1 VALUES ('d');
 INSERT INTO t2 VALUES ('');
 
 SELECT CONVERT( a USING latin1 ) FROM t1;
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 48872edcd4b..e039e48a888 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -551,8 +551,8 @@ drop table t1, t2;
 #
 
 create table t1 (c1 INT, c2 INT UNSIGNED);
-insert into t1 values ('21474836461','21474836461');
-insert into t1 values ('-21474836461','-21474836461');
+insert ignore into t1 values ('21474836461','21474836461');
+insert ignore into t1 values ('-21474836461','-21474836461');
 show warnings;
 select * from t1;
 drop table t1;
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 40a6c387448..0905ed48078 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -531,7 +531,7 @@ SELECT EXTRACT(HOUR FROM '10000:02:03');
 # check if we get proper warnings if both input string truncation
 # and out-of-range value occur
 CREATE TABLE t1(f1 TIME);
-INSERT INTO t1 VALUES('916:00:00 a');
+INSERT IGNORE INTO t1 VALUES('916:00:00 a');
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -1294,6 +1294,7 @@ CREATE TABLE t1 (a DATE);
 INSERT INTO t1 VALUES ('2005-05-04');
 SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1; 
 SELECT CHAR_LENGTH(CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10')))) AS f2 FROM t1; 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t2 AS SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1;
 SHOW CREATE TABLE t2;
 SELECT * FROM t2;
diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test
index acd91a91c27..a17d691b6f2 100644
--- a/mysql-test/t/gis-rtree.test
+++ b/mysql-test/t/gis-rtree.test
@@ -169,7 +169,7 @@ CREATE TABLE t1 (st varchar(100));
 INSERT INTO t1 VALUES ("Fake string");
 CREATE TABLE t2 (geom GEOMETRY NOT NULL, SPATIAL KEY gk(geom));
 --error 1416
-INSERT INTO t2 SELECT GeomFromText(st) FROM t1;
+INSERT IGNORE INTO t2 SELECT GeomFromText(st) FROM t1;
 drop table t1, t2;
 
 CREATE TABLE t1 (`geometry` geometry NOT NULL default '',SPATIAL KEY `gndx` (`geometry`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
@@ -842,7 +842,7 @@ CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
 --error 1048
 INSERT INTO t1(foo) VALUES (NULL);
 --error 1416
-INSERT INTO t1() VALUES ();
+INSERT IGNORE INTO t1() VALUES ();
 --error 1416
 INSERT INTO t1(foo) VALUES ('');
 DROP TABLE t1;
diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index f826feff5c0..160b347f870 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -365,7 +365,7 @@ select * from t1 a, t1 b group by a.s1 having s1 is null;
 drop table t1;
 
 create table t1 (s1 char character set latin1 collate latin1_german1_ci);
-insert into t1 values ('Ì'),('y');
+insert ignore into t1 values ('Ì'),('y');
 
 select s1,count(s1) from t1
 group by s1 collate latin1_swedish_ci having s1 = 'y';
diff --git a/mysql-test/t/help.test b/mysql-test/t/help.test
index dc5dc1cee5f..802f24f80a9 100644
--- a/mysql-test/t/help.test
+++ b/mysql-test/t/help.test
@@ -13,22 +13,22 @@
 #      impossible_category_3
 #                            impossible_function_7 
 
-insert into mysql.help_category(help_category_id,name)values(10001,'impossible_category_1');
+insert ignore into mysql.help_category(help_category_id,name)values(10001,'impossible_category_1');
 select @category1_id:= 10001;
-insert into mysql.help_category(help_category_id,name)values(10002,'impossible_category_2');
+insert ignore into mysql.help_category(help_category_id,name)values(10002,'impossible_category_2');
 select @category2_id:= 10002;
-insert into mysql.help_category(help_category_id,name,parent_category_id)values(10003,'impossible_category_3',@category2_id);
+insert ignore into mysql.help_category(help_category_id,name,parent_category_id)values(10003,'impossible_category_3',@category2_id);
 select @category3_id:= 10003;
 
-insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10101,'impossible_function_1',@category1_id,'description of \n impossible_function1\n','example of \n impossible_function1');
+insert ignore into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10101,'impossible_function_1',@category1_id,'description of \n impossible_function1\n','example of \n impossible_function1');
 select @topic1_id:= 10101;
-insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10102,'impossible_function_2',@category1_id,'description of \n impossible_function2\n','example of \n impossible_function2');
+insert ignore into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10102,'impossible_function_2',@category1_id,'description of \n impossible_function2\n','example of \n impossible_function2');
 select @topic2_id:= 10102;
-insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10103,'impossible_function_3',@category2_id,'description of \n impossible_function3\n','example of \n impossible_function3');
+insert ignore into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10103,'impossible_function_3',@category2_id,'description of \n impossible_function3\n','example of \n impossible_function3');
 select @topic3_id:= 10103;
-insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10104,'impossible_function_4',@category2_id,'description of \n impossible_function4\n','example of \n impossible_function4');
+insert ignore into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10104,'impossible_function_4',@category2_id,'description of \n impossible_function4\n','example of \n impossible_function4');
 select @topic4_id:= 10104;
-insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10105,'impossible_function_7',@category3_id,'description of \n impossible_function5\n','example of \n impossible_function7');
+insert ignore into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10105,'impossible_function_7',@category3_id,'description of \n impossible_function5\n','example of \n impossible_function7');
 select @topic5_id:= 10105;
 
 insert into mysql.help_keyword(help_keyword_id,name)values(10201,'impossible_function_1');
diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test
index 2d95dffa17a..206c5553100 100644
--- a/mysql-test/t/insert.test
+++ b/mysql-test/t/insert.test
@@ -98,23 +98,23 @@ use test;
 create table t1(number int auto_increment primary key, original_value varchar(50), f_double double, f_float float, f_double_7_2 double(7,2), f_float_4_3 float (4,3), f_double_u double unsigned, f_float_u float unsigned, f_double_15_1_u double(15,1) unsigned, f_float_3_1_u float (3,1) unsigned);
 
 set @value= "aa";
-insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
+insert ignore into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
 --query_vertical select * from t1 where number =last_insert_id()
 
 set @value= "1aa";
-insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
+insert ignore into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
 --query_vertical select * from t1 where number =last_insert_id()
 
 set @value= "aa1";
-insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
+insert ignore into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
 --query_vertical select * from t1 where number =last_insert_id()
 
 set @value= "1e+1111111111a";
-insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
+insert ignore into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
 --query_vertical select * from t1 where number =last_insert_id()
 
 set @value= "-1e+1111111111a";
-insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
+insert ignore into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
 --query_vertical select * from t1 where number =last_insert_id()
 
 --error 1367
@@ -124,11 +124,11 @@ set @value= -1e+1111111111;
 
 
 set @value= 1e+111;
-insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
+insert ignore into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
 --query_vertical select * from t1 where number =last_insert_id()
 
 set @value= -1e+111;
-insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
+insert ignore into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
 --query_vertical select * from t1 where number =last_insert_id()
 
 set @value= 1;
@@ -136,7 +136,7 @@ insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@val
 --query_vertical select * from t1 where number =last_insert_id()
 
 set @value= -1;
-insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
+insert ignore into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
 --query_vertical select * from t1 where number =last_insert_id()
 
 drop table t1;
@@ -404,7 +404,7 @@ CREATE TABLE t1 (
 );
 
 INSERT INTO t1(a,b,c) VALUES (9.999999e+0, 9.999999e+0, 9.999e+0);
-INSERT INTO t1(a,b,c) VALUES (1.225e-05, 1.225e-05, 1.225e-05);
+INSERT IGNORE INTO t1(a,b,c) VALUES (1.225e-05, 1.225e-05, 1.225e-05);
 INSERT INTO t1(a,b) VALUES (1.225e-04, 1.225e-04);
 INSERT INTO t1(a,b) VALUES (1.225e-01, 1.225e-01);
 INSERT INTO t1(a,b) VALUES (1.225877e-01, 1.225877e-01);
@@ -421,7 +421,7 @@ INSERT INTO t1(a,b) VALUES (1.25e-175, 1.25e-175);
 INSERT INTO t1(a,c) VALUES (1.225e+0, 1.225e+0);
 INSERT INTO t1(a,c) VALUES (1.37e+0, 1.37e+0);
 INSERT INTO t1(a,c) VALUES (-1.37e+0, -1.37e+0);
-INSERT INTO t1(a,c) VALUES (1.87e-3, 1.87e-3);
+INSERT IGNORE INTO t1(a,c) VALUES (1.87e-3, 1.87e-3);
 INSERT INTO t1(a,c) VALUES (-1.87e-2, -1.87e-2);
 INSERT INTO t1(a,c) VALUES (5000e+0, 5000e+0);
 INSERT INTO t1(a,c) VALUES (-5000e+0, -5000e+0);
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index bcd87c2688d..fda89f18d99 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -145,7 +145,7 @@ Select null, Field, Count From t1 Where Month=20030901 and Type=2;
 	  
 create table t2(No int not null, Field int not null, Count int not null);
 	  
-insert into t2 Select null, Field, Count From t1 Where Month=20030901 and Type=2;
+insert ignore into t2 Select null, Field, Count From t1 Where Month=20030901 and Type=2;
 	  
 select * from t2;
 
@@ -329,11 +329,13 @@ DROP TABLE t1, t2;
 # Bug#44306: Assertion fail on duplicate key error in 'INSERT ... SELECT' 
 # statements
 #
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 ( a INT KEY, b INT );
 INSERT INTO t1 VALUES ( 0, 1 );
 --error ER_DUP_ENTRY
 INSERT INTO t1 ( b ) SELECT MAX( b ) FROM t1 WHERE b = 2;
 DROP TABLE t1;
+SET sql_mode = DEFAULT;
 
 #
 # Bug #26207: inserts don't work with shortened index
diff --git a/mysql-test/t/join_outer_innodb.test b/mysql-test/t/join_outer_innodb.test
index 539d85a8b11..fe08689c80b 100644
--- a/mysql-test/t/join_outer_innodb.test
+++ b/mysql-test/t/join_outer_innodb.test
@@ -155,7 +155,7 @@ CREATE TABLE t1 (
   PRIMARY KEY (a1), KEY a2 (a2), KEY a3 (a3), KEY a4 (a4), KEY a6 (a6),
   KEY a5 (a5), KEY a7 (a7), KEY a8 (a8), KEY a9 (a9)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-INSERT INTO t1 VALUES 
+INSERT IGNORE INTO t1 VALUES 
   (3360,5684,2219,1,316832,1,0,NULL,NULL,NULL),
   (3362,2754,597,2,316844,1,0,NULL,NULL,NULL),
   (3363,369,NULL,1,317295,1,0,NULL,NULL,NULL);
diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test
index e2697effeb6..0695dc2eecb 100644
--- a/mysql-test/t/key.test
+++ b/mysql-test/t/key.test
@@ -46,7 +46,7 @@ CREATE TABLE t1 (
 
 INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N');
 INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N');
-INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','','','','');
+INSERT IGNORE INTO t1 VALUES (900,'Vancouver','Shared/Roomate','','','','');
 INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
 INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
 INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
@@ -184,7 +184,7 @@ drop table t1;
 
 CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT,
 UNIQUE (c,i));
-INSERT INTO t1 (c) VALUES (NULL),(NULL);
+INSERT IGNORE INTO t1 (c) VALUES (NULL),(NULL);
 SELECT * FROM t1;
 INSERT INTO t1 (c) VALUES ('a'),('a');
 SELECT * FROM t1;
@@ -201,8 +201,8 @@ drop table t1;
 # longer keys
 #
 create table t1 (i int, a char(200), b text, unique (a), unique (b(300))) charset utf8;
-insert t1 values (1, repeat('a',210), repeat('b', 310));
-insert t1 values (2, repeat(0xD0B1,215), repeat(0xD0B1, 310));
+insert ignore t1 values (1, repeat('a',210), repeat('b', 310));
+insert ignore t1 values (2, repeat(0xD0B1,215), repeat(0xD0B1, 310));
 select i, length(a), length(b), char_length(a), char_length(b) from t1;
 select i from t1 where a=repeat(_utf8 'a',200);
 select i from t1 where a=repeat(_utf8 0xD0B1,200);
diff --git a/mysql-test/t/limit_rows_examined.test b/mysql-test/t/limit_rows_examined.test
index 4997bacec57..29b3b411602 100644
--- a/mysql-test/t/limit_rows_examined.test
+++ b/mysql-test/t/limit_rows_examined.test
@@ -395,7 +395,7 @@ drop table t3,t3i;
 --echo INSERT ... SELECT
 CREATE TABLE t4 (a int);
 INSERT INTO t4 values (1), (2);
-INSERT INTO t4 SELECT a + 2 FROM t4 LIMIT ROWS EXAMINED 0;
+INSERT IGNORE INTO t4 SELECT a + 2 FROM t4 LIMIT ROWS EXAMINED 0;
 select * from t4;
 INSERT INTO t4 SELECT a + 2 FROM t4 LIMIT ROWS EXAMINED 6;
 select * from t4;
diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test
index 7e529194303..f827883af85 100644
--- a/mysql-test/t/loaddata.test
+++ b/mysql-test/t/loaddata.test
@@ -9,12 +9,12 @@ drop table if exists t1, t2;
 --enable_warnings
 
 create table t1 (a date, b date, c date not null, d date);
-load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',';
+load data infile '../../std_data/loaddata1.dat' ignore into table t1 fields terminated by ',';
 load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES;
 SELECT * from t1;
 truncate table t1;
 
-load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d);
+load data infile '../../std_data/loaddata1.dat' ignore into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d);
 SELECT * from t1;
 drop table t1;
 
@@ -129,7 +129,7 @@ select * from t1;
 select @a, @b;
 truncate table t1;
 # Reading of all columns with set
-load data infile '../../std_data/rpl_loaddata.dat' into table t1 set c=b;
+load data infile '../../std_data/rpl_loaddata.dat' ignore into table t1 set c=b;
 select * from t1;
 truncate table t1;
 # now going to test fixed field-row file format
@@ -198,7 +198,7 @@ create table t2(f1 int);
 insert into t2 values(1),(2);
 disable_query_log;
 eval select * into outfile '$MYSQLTEST_VARDIR/tmp/t2' from t2;
-eval load data infile '$MYSQLTEST_VARDIR/tmp/t2' into table t1;
+eval load data infile '$MYSQLTEST_VARDIR/tmp/t2' ignore into table t1;
 enable_query_log;
 select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;
 remove_file $MYSQLTEST_VARDIR/tmp/t2;
@@ -207,7 +207,7 @@ disable_query_log;
 eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t2'
 FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\r\n'
 FROM t2;
-eval load data infile '$MYSQLTEST_VARDIR/tmp/t2' into table t1
+eval load data infile '$MYSQLTEST_VARDIR/tmp/t2' ignore into table t1
 FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\r\n';
 enable_query_log;
 select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;
@@ -675,7 +675,7 @@ SELECT HEX(val) FROM t1;
 CREATE DATABASE d2 CHARSET utf8;
 USE d2;
 CREATE TABLE t1 (val TEXT);
-LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1;
+LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' IGNORE INTO TABLE t1;
 SELECT COUNT(*) FROM t1;
 SELECT HEX(val) FROM t1;
 
diff --git a/mysql-test/t/mdev-504.test b/mysql-test/t/mdev-504.test
index b96c8779c68..76232927cd9 100644
--- a/mysql-test/t/mdev-504.test
+++ b/mysql-test/t/mdev-504.test
@@ -51,7 +51,7 @@ while ($trial)
     let $query = `SELECT CASE rnd3() 
       WHEN 1 THEN 'INSERT INTO A (pk) VALUES (NULL)'
       WHEN 2 THEN 'DELETE FROM A LIMIT 1'
-      ELSE 'UPDATE A SET fdate = 2 LIMIT 1' END`;
+      ELSE 'UPDATE IGNORE A SET fdate = 2 LIMIT 1' END`;
     --eval $query
     --disconnect con3
     --dec $run
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 4e09b2370f5..64e61f7c0b5 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -616,9 +616,9 @@ SET SESSION sql_safe_updates = DEFAULT;
 CREATE FUNCTION f1 () RETURNS BLOB RETURN 1;
 CREATE TABLE t1 (f1 DATE);
 INSERT INTO t1 VALUES('2001-01-01');
-UPDATE (SELECT 1 FROM t1 WHERE f1 = (SELECT f1() FROM t1)) x, t1 SET f1 = 1;
+UPDATE IGNORE (SELECT 1 FROM t1 WHERE f1 = (SELECT f1() FROM t1)) x, t1 SET f1 = 1;
 CREATE view v1 as SELECT f1() FROM t1;
-UPDATE (SELECT 1 FROM t1 WHERE f1 = (select * from v1)) x, t1 SET f1 = 1;
+UPDATE IGNORE (SELECT 1 FROM t1 WHERE f1 = (select * from v1)) x, t1 SET f1 = 1;
 DROP VIEW v1;
 DROP FUNCTION f1;
 DROP TABLE t1;
diff --git a/mysql-test/t/myisam-blob.test b/mysql-test/t/myisam-blob.test
index ac1b45b8c6c..23de8d54ee7 100644
--- a/mysql-test/t/myisam-blob.test
+++ b/mysql-test/t/myisam-blob.test
@@ -30,13 +30,14 @@ check table t1;
 
 INSERT INTO t1 set data=repeat('a',18*1024*1024);
 select length(data) from t1;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 modify data blob;
 select length(data) from t1;
 drop table t1;
 
 CREATE TABLE t1 (data BLOB) ENGINE=myisam;
 INSERT INTO t1 (data) VALUES (NULL);
-UPDATE t1 set data=repeat('a',18*1024*1024);
+UPDATE IGNORE t1 set data=repeat('a',18*1024*1024);
 select length(data) from t1;
 drop table t1;
 
diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test
index dac19c1d395..572fa55e70f 100644
--- a/mysql-test/t/myisam.test
+++ b/mysql-test/t/myisam.test
@@ -42,7 +42,7 @@ begin;
 SET SQL_WARNINGS=0;
 while ($1)
 {
-  eval insert into t1 (b) values(repeat(char(65+$1),65550-$1));
+  eval insert ignore into t1 (b) values(repeat(char(65+$1),65550-$1));
   dec $1;
 }
 commit;
@@ -313,6 +313,7 @@ drop table t1;
 #
 # Test of REPAIR that once failed
 #
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE `t1` (
   `post_id` mediumint(8) unsigned NOT NULL auto_increment,
   `topic_id` mediumint(8) unsigned NOT NULL default '0',
@@ -331,7 +332,7 @@ CREATE TABLE `t1` (
   FULLTEXT KEY `post_text` (`post_text`)
 ) ENGINE=MyISAM;
 
-INSERT INTO t1 (post_text) VALUES ('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test');
+INSERT IGNORE INTO t1 (post_text) VALUES ('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test');
 
 REPAIR TABLE t1;
 CHECK TABLE t1;
@@ -1027,9 +1028,11 @@ source include/varchar.inc;
 
 create table t1 (v varchar(65530), key(v));
 drop table if exists t1;
+set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
 create table t1 (v varchar(65536));
 show create table t1;
 drop table t1;
+set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
 create table t1 (v varchar(65530) character set utf8);
 show create table t1;
 drop table t1;
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 299698ed04b..090ed45c9f9 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -58,7 +58,7 @@ DROP TABLE t1;
 --echo #
 
 CREATE TABLE t1 (a double);
-INSERT INTO t1 VALUES ('-9e999999');
+INSERT IGNORE INTO t1 VALUES ('-9e999999');
 --exec $MYSQL_DUMP --compact test t1
 DROP TABLE t1;
 
diff --git a/mysql-test/t/mysqlslap.opt b/mysql-test/t/mysqlslap.opt
new file mode 100644
index 00000000000..632f5b4c069
--- /dev/null
+++ b/mysql-test/t/mysqlslap.opt
@@ -0,0 +1 @@
+--sql_mode='NO_ENGINE_SUBSTITUTION'
diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test
index 072038fae50..3de35a74a73 100644
--- a/mysql-test/t/null.test
+++ b/mysql-test/t/null.test
@@ -61,29 +61,29 @@ drop table t1;
 # Test inserting and updating with NULL
 #
 CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default 0, c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0);
-INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55";
-UPDATE t1 SET d=1/NULL;
-UPDATE t1 SET d=NULL;
+INSERT IGNORE INTO t1 SET a = "", d= "2003-01-14 03:54:55";
+UPDATE IGNORE t1 SET d=1/NULL;
+UPDATE IGNORE t1 SET d=NULL;
 --error 1048
 INSERT INTO t1 (a) values (null);
 --error 1048
 INSERT INTO t1 (a) values (1/null);
-INSERT INTO t1 (a) values (null),(null);
+INSERT IGNORE INTO t1 (a) values (null),(null);
 --error 1048
 INSERT INTO t1 (b) values (null);
 --error 1048
 INSERT INTO t1 (b) values (1/null);
-INSERT INTO t1 (b) values (null),(null);
+INSERT IGNORE INTO t1 (b) values (null),(null);
 --error 1048
 INSERT INTO t1 (c) values (null);
 --error 1048
 INSERT INTO t1 (c) values (1/null);
-INSERT INTO t1 (c) values (null),(null);
+INSERT IGNORE INTO t1 (c) values (null),(null);
 --error 1048
 INSERT INTO t1 (d) values (null);
 --error 1048
 INSERT INTO t1 (d) values (1/null);
-INSERT INTO t1 (d) values (null),(null);
+INSERT IGNORE INTO t1 (d) values (null),(null);
 select * from t1;
 drop table t1;
 
@@ -118,6 +118,7 @@ insert into t1 select i*2 from t1;
 insert into t1 values(null);
 explain select * from t1 where i=2 or i is null;
 select count(*) from t1 where i=2 or i is null;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change i i int not null;
 explain select * from t1 where i=2 or i is null;
 select count(*) from t1 where i=2 or i is null;
@@ -335,6 +336,7 @@ DROP TABLE t1,t2;
 --error ER_CANT_AGGREGATE_2COLLATIONS
 SELECT NULLIF(_latin1'a' COLLATE latin1_general_ci, _latin1'a' COLLATE latin1_bin);
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 AS SELECT
   NULLIF(1,1),
   NULLIF(1,1.0),
@@ -344,6 +346,7 @@ CREATE TABLE t1 AS SELECT
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 AS SELECT
   NULLIF(1.0,1),
   NULLIF(1.0,1.0),
@@ -353,6 +356,7 @@ CREATE TABLE t1 AS SELECT
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 AS SELECT
   NULLIF(1e0,1),
   NULLIF(1e0,1.0),
@@ -371,6 +375,7 @@ CREATE TABLE t1 AS SELECT
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 AS SELECT
   NULLIF(TIMESTAMP'2001-01-01 00:00:00',1),
   NULLIF(TIMESTAMP'2001-01-01 00:00:00',1.0),
@@ -380,6 +385,7 @@ CREATE TABLE t1 AS SELECT
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 AS SELECT
   NULLIF(DATE'2001-01-01',1),
   NULLIF(DATE'2001-01-01',1.0),
@@ -862,6 +868,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL EL
 DROP TABLE t1;
 
 --echo # Two warnings expected
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 AS SELECT
   NULLIF(TIMESTAMP'2001-01-01 00:00:00',1) AS a,
   CASE WHEN TIMESTAMP'2001-01-01 00:00:00'=1 THEN NULL
diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test
index 695b2835610..c3ebc6f5b94 100644
--- a/mysql-test/t/null_key.test
+++ b/mysql-test/t/null_key.test
@@ -183,7 +183,7 @@ create table t1 (
   index (id),
   index (id2)
 );
-insert into t1 values(null,null),(1,1);
+insert ignore into t1 values(null,null),(1,1);
 select * from t1;
 select * from t1 where id <=> null;
 select * from t1 where id <=> null or id > 0;
diff --git a/mysql-test/t/old-mode.test b/mysql-test/t/old-mode.test
index 0572570d122..b9012a96713 100644
--- a/mysql-test/t/old-mode.test
+++ b/mysql-test/t/old-mode.test
@@ -34,15 +34,15 @@ SELECT CAST(TIME'-10:30:30' AS DATETIME);
 SELECT CAST(TIME'10:20:30' AS DATETIME);
 SELECT CAST(TIME'830:20:30' AS DATETIME);
 CREATE TABLE t1 (a DATETIME);
-INSERT INTO t1 VALUES (TIME'-10:20:30');
+INSERT IGNORE INTO t1 VALUES (TIME'-10:20:30');
 INSERT INTO t1 VALUES (TIME'10:20:30');
 INSERT INTO t1 VALUES (TIME'830:20:30');
 SELECT * FROM t1;
 DROP TABLE t1;
 CREATE TABLE t1 (a TIMESTAMP);
-INSERT INTO t1 VALUES (TIME'-10:20:30');
-INSERT INTO t1 VALUES (TIME'10:20:30');
-INSERT INTO t1 VALUES (TIME'830:20:30');
+INSERT IGNORE INTO t1 VALUES (TIME'-10:20:30');
+INSERT IGNORE INTO t1 VALUES (TIME'10:20:30');
+INSERT IGNORE INTO t1 VALUES (TIME'830:20:30');
 SELECT * FROM t1;
 DROP TABLE t1;
 CREATE TABLE t1 (a TIME);
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 1e32f789806..6baa66112da 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -271,6 +271,7 @@ select * from t1 where a = 1 order by b desc;
 # Test things when we don't have NULL keys
 #
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 modify b int not null, modify c varchar(10) not null;
 explain select * from t1 order by a, b, c;
 select * from t1 order by a, b, c;
diff --git a/mysql-test/t/partition_datatype.test b/mysql-test/t/partition_datatype.test
index a6035fcb592..4017cdada60 100644
--- a/mysql-test/t/partition_datatype.test
+++ b/mysql-test/t/partition_datatype.test
@@ -262,10 +262,10 @@ ALTER TABLE t2 PARTITION BY RANGE (UNIX_TIMESTAMP(a))
 INSERT INTO t1 VALUES (NULL, 'UTC');
 INSERT INTO t1 VALUES ('0000-00-00 00:00:00', 'UTC');
 --echo # Test invalid values
-INSERT INTO t1 VALUES ('1901-01-01 00:00:00', 'UTCI');
-INSERT INTO t1 VALUES ('1969-12-31 23:59:59', 'UTCI');
-INSERT INTO t1 VALUES ('2038-01-19 03:14:08', 'UTCI');
-INSERT INTO t1 VALUES ('1970-01-01 00:00:00', 'UTCI');
+INSERT IGNORE INTO t1 VALUES ('1901-01-01 00:00:00', 'UTCI');
+INSERT IGNORE INTO t1 VALUES ('1969-12-31 23:59:59', 'UTCI');
+INSERT IGNORE INTO t1 VALUES ('2038-01-19 03:14:08', 'UTCI');
+INSERT IGNORE INTO t1 VALUES ('1970-01-01 00:00:00', 'UTCI');
 --echo # Test start range
 INSERT INTO t1 VALUES ('1970-01-01 00:00:01', 'UTC');
 INSERT INTO t1 VALUES ('1974-02-05 21:28:16', 'UTC');
@@ -292,16 +292,16 @@ SET @@session.time_zone = 'Europe/Moscow';
 INSERT INTO t1 VALUES (NULL, 'Moscow');
 INSERT INTO t1 VALUES ('0000-00-00 00:00:00', 'Moscow');
 --echo # Test invalid values
-INSERT INTO t1 VALUES ('0000-00-00 03:00:00', 'MoscowI');
-INSERT INTO t1 VALUES ('1901-01-01 00:00:00', 'MoscowI');
-INSERT INTO t1 VALUES ('1969-12-31 23:59:59', 'MoscowI');
-INSERT INTO t1 VALUES ('1970-01-01 02:29:29', 'MoscowI');
-INSERT INTO t1 VALUES ('2038-01-19 06:14:08', 'MoscowI');
-INSERT INTO t1 VALUES ('1970-01-01 03:00:00', 'MoscowI');
+INSERT IGNORE INTO t1 VALUES ('0000-00-00 03:00:00', 'MoscowI');
+INSERT IGNORE INTO t1 VALUES ('1901-01-01 00:00:00', 'MoscowI');
+INSERT IGNORE INTO t1 VALUES ('1969-12-31 23:59:59', 'MoscowI');
+INSERT IGNORE INTO t1 VALUES ('1970-01-01 02:29:29', 'MoscowI');
+INSERT IGNORE INTO t1 VALUES ('2038-01-19 06:14:08', 'MoscowI');
+INSERT IGNORE INTO t1 VALUES ('1970-01-01 03:00:00', 'MoscowI');
 --echo # values truncated to 03:00:00 due to daylight saving shift
-INSERT INTO t1 VALUES ('2011-03-27 02:00:00', 'MoscowI');
-INSERT INTO t1 VALUES ('2011-03-27 02:00:01', 'MoscowI');
-INSERT INTO t1 VALUES ('2011-03-27 02:59:59', 'MoscowI');
+INSERT IGNORE INTO t1 VALUES ('2011-03-27 02:00:00', 'MoscowI');
+INSERT IGNORE INTO t1 VALUES ('2011-03-27 02:00:01', 'MoscowI');
+INSERT IGNORE INTO t1 VALUES ('2011-03-27 02:59:59', 'MoscowI');
 --echo # Test start range
 INSERT INTO t1 VALUES ('1970-01-01 03:00:01', 'Moscow');
 INSERT INTO t1 VALUES ('1974-02-05 21:28:16', 'Moscow');
@@ -386,12 +386,12 @@ WHERE a BETWEEN '2011-10-29 23:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz;
 
 --echo # Test end range changes
 DELETE FROM t2 WHERE a = 0;
-INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC');
+INSERT IGNORE INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC');
 SELECT COUNT(*) FROM t2;
 SELECT COUNT(*) FROM t2 WHERE a = 0;
 SELECT * FROM t2 ORDER BY a, tz LIMIT 3;
 SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3;
-UPDATE t2 SET a = TIMESTAMPADD(SECOND, 1, a);
+UPDATE IGNORE t2 SET a = TIMESTAMPADD(SECOND, 1, a);
 SELECT MIN(a), MAX(a) FROM t2;
 SELECT COUNT(*) FROM t2;
 SELECT COUNT(*) FROM t2 WHERE a = 0;
@@ -400,12 +400,12 @@ WHERE TABLE_NAME = 't2';
 SELECT * FROM t2 ORDER BY a, tz;
 
 --echo # Test start range changes
-INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC');
+INSERT IGNORE INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC');
 SELECT COUNT(*) FROM t2;
 SELECT COUNT(*) FROM t2 WHERE a = 0;
 SELECT * FROM t2 ORDER BY a, tz LIMIT 3;
 SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3;
-UPDATE t2 SET a = TIMESTAMPADD(SECOND, -1, a);
+UPDATE IGNORE t2 SET a = TIMESTAMPADD(SECOND, -1, a);
 SELECT MIN(a), MAX(a) FROM t2;
 SELECT COUNT(*) FROM t2;
 SELECT COUNT(*) FROM t2 WHERE a = 0;
@@ -511,12 +511,12 @@ WHERE a BETWEEN '2011-10-29 01:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz;
 
 --echo # Test end range changes
 DELETE FROM t2 WHERE a = 0;
-INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow');
+INSERT IGNORE INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow');
 SELECT COUNT(*) FROM t2;
 SELECT COUNT(*) FROM t2 WHERE a = 0;
 SELECT * FROM t2 ORDER BY a, tz LIMIT 3;
 SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3;
-UPDATE t2 SET a = TIMESTAMPADD(SECOND, 1, a);
+UPDATE IGNORE t2 SET a = TIMESTAMPADD(SECOND, 1, a);
 SELECT MIN(a), MAX(a) FROM t2;
 SELECT COUNT(*) FROM t2;
 SELECT COUNT(*) FROM t2 WHERE a = 0;
@@ -525,12 +525,12 @@ WHERE TABLE_NAME = 't2';
 SELECT * FROM t2 ORDER BY a, tz;
 
 --echo # Test start range changes
-INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow');
+INSERT IGNORE INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow');
 SELECT COUNT(*) FROM t2;
 SELECT COUNT(*) FROM t2 WHERE a = 0;
 SELECT * FROM t2 ORDER BY a, tz LIMIT 3;
 SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3;
-UPDATE t2 SET a = TIMESTAMPADD(SECOND, -1, a);
+UPDATE IGNORE t2 SET a = TIMESTAMPADD(SECOND, -1, a);
 SELECT MIN(a), MAX(a) FROM t2;
 SELECT COUNT(*) FROM t2;
 SELECT COUNT(*) FROM t2 WHERE a = 0;
diff --git a/mysql-test/t/partition_error.test b/mysql-test/t/partition_error.test
index 858213b1e30..3ee54605c36 100644
--- a/mysql-test/t/partition_error.test
+++ b/mysql-test/t/partition_error.test
@@ -87,13 +87,13 @@ PARTITION BY RANGE (DAYOFWEEK(a))
 CREATE TABLE t1 (a DATE)
 PARTITION BY RANGE (DAYOFWEEK(a))
 (PARTITION a1 VALUES LESS THAN (60));
-INSERT INTO t1 VALUES ('test'),('a'),('5');
+INSERT IGNORE INTO t1 VALUES ('test'),('a'),('5');
 SHOW WARNINGS;
 DROP TABLE t1;
 CREATE TABLE t1 (a DATETIME)
 PARTITION BY RANGE (DAYOFWEEK(a))
 (PARTITION a1 VALUES LESS THAN (60));
-INSERT INTO t1 VALUES ('test'),('a'),('5');
+INSERT IGNORE INTO t1 VALUES ('test'),('a'),('5');
 SHOW WARNINGS;
 DROP TABLE t1;
 --error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
@@ -2056,6 +2056,7 @@ SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_COMMENT FROM INFORMATION_SCH
 WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test';
 DROP TABLE t1;
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1
 (a INT ,
  KEY inx_a (a) )
diff --git a/mysql-test/t/plugin_auth.test b/mysql-test/t/plugin_auth.test
index c5d96e0d612..81b8ba6e0a3 100644
--- a/mysql-test/t/plugin_auth.test
+++ b/mysql-test/t/plugin_auth.test
@@ -449,7 +449,7 @@ DROP USER uplain@localhost;
 --echo # Bug #59038 : mysql.user.authentication_string column 
 --echo #   causes configuration wizard to fail
 
-INSERT INTO mysql.user(
+INSERT IGNORE INTO mysql.user(
   Host, 
   User, 
   Password,  
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 74ab7659ac6..549814724d2 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -841,13 +841,13 @@ drop table t1;
 prepare stmt from 'create table t1 (a varchar(10) character set utf8)';
 execute stmt;
 --disable_warnings
-insert into t1 (a) values (repeat('a', 20));
+insert ignore into t1 (a) values (repeat('a', 20));
 --enable_warnings
 select length(a) from t1;
 drop table t1;
 execute stmt;
 --disable_warnings
-insert into t1 (a) values (repeat('a', 20));
+insert ignore into t1 (a) values (repeat('a', 20));
 --enable_warnings
 # Check that the data is truncated to the same length
 select length(a) from t1;
diff --git a/mysql-test/t/round.test b/mysql-test/t/round.test
index d018fa7e34e..4ba65c08fcf 100644
--- a/mysql-test/t/round.test
+++ b/mysql-test/t/round.test
@@ -6,13 +6,13 @@ CREATE TABLE t1 (sint8 tinyint not null);
 INSERT INTO t1 VALUES ('0.1');
 INSERT INTO t1 VALUES ('0.5');
 INSERT INTO t1 VALUES ('127.4');
-INSERT INTO t1 VALUES ('127.5');
+INSERT IGNORE INTO t1 VALUES ('127.5');
 INSERT INTO t1 VALUES ('-0.1');
 INSERT INTO t1 VALUES ('-0.5');
 INSERT INTO t1 VALUES ('-127.4');
 INSERT INTO t1 VALUES ('-127.5');
 INSERT INTO t1 VALUES ('-128.4');
-INSERT INTO t1 VALUES ('-128.5');
+INSERT IGNORE INTO t1 VALUES ('-128.5');
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -22,9 +22,9 @@ INSERT INTO t1 VALUES ('0.5');
 INSERT INTO t1 VALUES ('127.4');
 INSERT INTO t1 VALUES ('127.5');
 INSERT INTO t1 VALUES ('-0.1');
-INSERT INTO t1 VALUES ('-0.5');
+INSERT IGNORE INTO t1 VALUES ('-0.5');
 INSERT INTO t1 VALUES ('255.4');
-INSERT INTO t1 VALUES ('255.5');
+INSERT IGNORE INTO t1 VALUES ('255.5');
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -33,13 +33,13 @@ CREATE TABLE t1 (sint16 smallint not null);
 INSERT INTO t1 VALUES ('0.1');
 INSERT INTO t1 VALUES ('0.5');
 INSERT INTO t1 VALUES ('32767.4');
-INSERT INTO t1 VALUES ('32767.5');
+INSERT IGNORE INTO t1 VALUES ('32767.5');
 INSERT INTO t1 VALUES ('-0.1');
 INSERT INTO t1 VALUES ('-0.5');
 INSERT INTO t1 VALUES ('-32767.4');
 INSERT INTO t1 VALUES ('-32767.5');
 INSERT INTO t1 VALUES ('-32768.4');
-INSERT INTO t1 VALUES ('-32768.5');
+INSERT IGNORE INTO t1 VALUES ('-32768.5');
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -50,9 +50,9 @@ INSERT INTO t1 VALUES ('0.5');
 INSERT INTO t1 VALUES ('32767.4');
 INSERT INTO t1 VALUES ('32767.5');
 INSERT INTO t1 VALUES ('-0.1');
-INSERT INTO t1 VALUES ('-0.5');
+INSERT IGNORE INTO t1 VALUES ('-0.5');
 INSERT INTO t1 VALUES ('65535.4');
-INSERT INTO t1 VALUES ('65535.5');
+INSERT IGNORE INTO t1 VALUES ('65535.5');
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -60,13 +60,13 @@ CREATE TABLE t1 (sint24 mediumint not null);
 INSERT INTO t1 VALUES ('0.1');
 INSERT INTO t1 VALUES ('0.5');
 INSERT INTO t1 VALUES ('8388607.4');
-INSERT INTO t1 VALUES ('8388607.5');
+INSERT IGNORE INTO t1 VALUES ('8388607.5');
 INSERT INTO t1 VALUES ('-0.1');
 INSERT INTO t1 VALUES ('-0.5');
 INSERT INTO t1 VALUES ('-8388607.4');
 INSERT INTO t1 VALUES ('-8388607.5');
 INSERT INTO t1 VALUES ('-8388608.4');
-INSERT INTO t1 VALUES ('-8388608.5');
+INSERT IGNORE INTO t1 VALUES ('-8388608.5');
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -76,9 +76,9 @@ INSERT INTO t1 VALUES ('0.5');
 INSERT INTO t1 VALUES ('8388607.4');
 INSERT INTO t1 VALUES ('8388607.5');
 INSERT INTO t1 VALUES ('-0.1');
-INSERT INTO t1 VALUES ('-0.5');
+INSERT IGNORE INTO t1 VALUES ('-0.5');
 INSERT INTO t1 VALUES ('16777215.4');
-INSERT INTO t1 VALUES ('16777215.5');
+INSERT IGNORE INTO t1 VALUES ('16777215.5');
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -86,13 +86,13 @@ CREATE TABLE t1 (sint64 bigint not null);
 INSERT INTO t1 VALUES ('0.1');
 INSERT INTO t1 VALUES ('0.5');
 INSERT INTO t1 VALUES ('9223372036854775807.4');
-INSERT INTO t1 VALUES ('9223372036854775807.5');
+INSERT IGNORE INTO t1 VALUES ('9223372036854775807.5');
 INSERT INTO t1 VALUES ('-0.1');
 INSERT INTO t1 VALUES ('-0.5');
 INSERT INTO t1 VALUES ('-9223372036854775807.4');
 INSERT INTO t1 VALUES ('-9223372036854775807.5');
 INSERT INTO t1 VALUES ('-9223372036854775808.4');
-INSERT INTO t1 VALUES ('-9223372036854775808.5');
+INSERT IGNORE INTO t1 VALUES ('-9223372036854775808.5');
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -102,9 +102,9 @@ INSERT INTO t1 VALUES ('0.5');
 INSERT INTO t1 VALUES ('9223372036854775807.4');
 INSERT INTO t1 VALUES ('9223372036854775807.5');
 INSERT INTO t1 VALUES ('-0.1');
-INSERT INTO t1 VALUES ('-0.5');
+INSERT IGNORE INTO t1 VALUES ('-0.5');
 INSERT INTO t1 VALUES ('18446744073709551615.4');
-INSERT INTO t1 VALUES ('18446744073709551615.5');
+INSERT IGNORE INTO t1 VALUES ('18446744073709551615.5');
 INSERT INTO t1 VALUES ('1844674407370955161.0');
 INSERT INTO t1 VALUES ('1844674407370955161.1');
 INSERT INTO t1 VALUES ('1844674407370955161.2');
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index b207db042e6..a0caf4992bf 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2372,7 +2372,7 @@ DROP TABLE t1;
 #
 
 create table t1(a bigint unsigned, b bigint);
-insert into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff), 
+insert ignore into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff), 
   (0x10000000000000000, 0x10000000000000000), 
   (0x8fffffffffffffff, 0x8fffffffffffffff);
 select hex(a), hex(b) from t1;
@@ -3808,7 +3808,7 @@ DROP TABLE t1;
 --echo #
 
 CREATE TABLE t1(a INT NOT NULL, b YEAR);
-INSERT INTO t1 VALUES ();
+INSERT IGNORE INTO t1 VALUES ();
 CREATE TABLE t2(c INT);
 --echo # Should not err out because of out-of-memory
 SELECT 1 FROM t2 JOIN t1 ON 1=1
@@ -3875,7 +3875,7 @@ SET @@sort_buffer_size= 40000;
 
 CREATE TABLE t1(a CHAR(0) NOT NULL);
 --disable_warnings
-INSERT INTO t1 VALUES (0), (0), (0);
+INSERT IGNORE INTO t1 VALUES (0), (0), (0);
 --enable_warnings
 INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12;
 INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12;
@@ -3888,7 +3888,7 @@ DROP TABLE t1;
 
 CREATE TABLE t1(a CHAR(0) NOT NULL, b CHAR(0) NOT NULL, c int);
 --disable_warnings
-INSERT INTO t1 VALUES (0, 0, 0), (0, 0, 2), (0, 0, 1);
+INSERT IGNORE INTO t1 VALUES (0, 0, 0), (0, 0, 2), (0, 0, 1);
 --enable_warnings
 INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12;
 INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12;
diff --git a/mysql-test/t/signal.test b/mysql-test/t/signal.test
index 31bc7bc9633..cdb5796f1da 100644
--- a/mysql-test/t/signal.test
+++ b/mysql-test/t/signal.test
@@ -1346,6 +1346,7 @@ end $$
 call test_signal() $$
 drop procedure test_signal $$
 
+SET sql_mode= '' $$
 create procedure test_signal()
 begin
   DECLARE aaax VARCHAR(65);
@@ -1400,6 +1401,7 @@ end $$
 
 call test_signal() $$
 drop procedure test_signal $$
+set sql_mode = DEFAULT $$
 
 # Test that HANDLER can catch conditions raised by SIGNAL
 
@@ -1785,7 +1787,7 @@ begin
     select "after RESIGNAL";
   end;
 
-  insert into t_warn set a= 9999999999999999;
+  insert ignore into t_warn set a= 9999999999999999;
 end $$
 
 call test_resignal() $$
@@ -1904,7 +1906,7 @@ begin
     select "after RESIGNAL";
   end;
 
-  insert into t_warn set a= 9999999999999999;
+  insert ignore into t_warn set a= 9999999999999999;
 end $$
 
 call test_resignal() $$
@@ -2171,7 +2173,7 @@ begin
     select "after RESIGNAL";
   end;
 
-  insert into t_warn set a= 9999999999999999;
+  insert ignore into t_warn set a= 9999999999999999;
 end $$
 
 call test_resignal() $$
@@ -2188,7 +2190,7 @@ begin
     select "after RESIGNAL";
   end;
 
-  insert into t_warn set a= 9999999999999999;
+  insert ignore into t_warn set a= 9999999999999999;
 end $$
 
 --error 5555
@@ -2207,7 +2209,7 @@ begin
     select "after RESIGNAL";
   end;
 
-  insert into t_warn set a= 9999999999999999;
+  insert ignore into t_warn set a= 9999999999999999;
 end $$
 
 --error 5555
diff --git a/mysql-test/t/sp-dynamic.test b/mysql-test/t/sp-dynamic.test
index e6f4aae96ac..3a467e26818 100644
--- a/mysql-test/t/sp-dynamic.test
+++ b/mysql-test/t/sp-dynamic.test
@@ -214,6 +214,7 @@ drop procedure p1|
 # statements do not substitute SP variables with their values for binlog, so
 # SP variables must be not accessible in Dynamic SQL.
 #
+set sql_mode= ''|
 create procedure p1()
 begin
   declare a char(10);
@@ -226,6 +227,7 @@ end|
 call p1()|
 --error ER_BAD_FIELD_ERROR
 call p1()|
+set sql_mode= DEFAULT|
 drop procedure p1|
 #
 # J. Use of placeholders in Dynamic SQL.
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index e30fc6e30d4..aa537d3596b 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -2734,7 +2734,7 @@ DROP PROCEDURE p1;
 --echo #
 --echo # Bug#5889: Exit handler for a warning doesn't hide the warning in trigger
 --echo #
-
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1(a INT, b INT);
 INSERT INTO t1 VALUES (1, 2);
 
@@ -2758,6 +2758,7 @@ SELECT * FROM t1;
 
 DROP TRIGGER t1_bu;
 DROP TABLE t1;
+SET sql_mode = DEFAULT;
 
 --echo #
 --echo # Bug#9857: Stored procedures: handler for sqlwarning ignored
@@ -2811,13 +2812,13 @@ CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT,
                 d SMALLINT, e SMALLINT, f SMALLINT);
 
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-  INSERT INTO t2(a, b, c) VALUES(99999, 99999, 99999);
+  INSERT IGNORE INTO t2(a, b, c) VALUES(99999, 99999, 99999);
 
 CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
-  INSERT INTO t2(d, e, f) VALUES(99999, 99999, 99999);
+  INSERT IGNORE INTO t2(d, e, f) VALUES(99999, 99999, 99999);
 
 CREATE PROCEDURE p1()
-  INSERT INTO t1 VALUES(99999, 99999, 99999);
+  INSERT IGNORE INTO t1 VALUES(99999, 99999, 99999);
 
 # What happened before the patch was:
 #  - INSERT INTO t1 added 3 warnings about overflow in 'x', 'y' and 'z' columns;
@@ -2845,7 +2846,7 @@ DROP TABLE t2;
 DROP PROCEDURE p1;
 
 --echo # ----------------------------------------------------------------------
-
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT);
 CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT NOT NULL);
 
@@ -2875,6 +2876,7 @@ SHOW WARNINGS;
 DROP TABLE t1;
 DROP TABLE t2;
 DROP PROCEDURE p1;
+SET sql_mode = DEFAULT;
 
 --echo
 --echo ###################################################################
@@ -3558,7 +3560,7 @@ BEGIN
   # above must be invoked once (for one condition), but all three conditions
   # must be cleared from the Diagnostics Area.
 
-  INSERT INTO t1 VALUES('qqqq', 'ww', 'eee');
+  INSERT IGNORE INTO t1 VALUES('qqqq', 'ww', 'eee');
 
   # The following INSERT will not be executed, because of the EXIT HANDLER.
 
@@ -3585,7 +3587,7 @@ BEGIN
   # invoked once, and all nine SQL-warnings must be cleared from
   # the Diagnostics Area.
 
-  INSERT INTO t2
+  INSERT IGNORE INTO t2
   SELECT
     CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
     CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
@@ -3665,7 +3667,7 @@ BEGIN
     SELECT 'Handler for 1292 (2)' AS Msg;
   END;
 
-  INSERT INTO t2
+  INSERT IGNORE INTO t2
   SELECT
     CAST(999999 AS SIGNED INTEGER),
     CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
@@ -3688,7 +3690,7 @@ BEGIN
     SELECT 'Handler for 1292' Msg;
   END;
 
-  INSERT INTO t2
+  INSERT IGNORE INTO t2
   SELECT
     CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
     CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
@@ -3723,6 +3725,7 @@ DROP PROCEDURE IF EXISTS p2;
 
 delimiter |;
 
+SET sql_mode = '';
 CREATE PROCEDURE p1()
 BEGIN
   DECLARE var1 INTEGER DEFAULT 'string';
@@ -3732,6 +3735,7 @@ END|
 --echo
 CALL p1()|
 --echo
+SET sql_mode = DEFAULT;
 
 CREATE PROCEDURE p2()
 BEGIN
diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test
index c620f415e22..41bfd871c39 100644
--- a/mysql-test/t/sp_notembedded.test
+++ b/mysql-test/t/sp_notembedded.test
@@ -98,6 +98,7 @@ begin
   end if;
   return 1;
 end|
+set statement sql_mode = '' for
 create procedure bug10100p(prm int, inout res int)
 begin
   set res = res * prm;
@@ -105,6 +106,7 @@ begin
     call bug10100p(prm - 1, res);
   end if;
 end|
+set statement sql_mode = '' for
 create procedure bug10100t(prm int)
 begin
   declare res int;
@@ -303,7 +305,7 @@ set session low_priority_updates=default;
 #
 # Bug#44798 MySQL engine crashes when creating stored procedures with execute_priv=N
 #
-INSERT INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv,
+INSERT IGNORE INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv,
 Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv,
 Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv,
 Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv,
diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test
index 93b31499bef..830f051a5f6 100644
--- a/mysql-test/t/strict.test
+++ b/mysql-test/t/strict.test
@@ -1235,6 +1235,7 @@ i int comment
  123456789*123456789*123456789*123456789*
  123456789*123456789*123456789*123456789*');
 set @@sql_mode= @org_mode;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1
 (i int comment
  '123456789*123456789*123456789*123456789*
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 88bba3b5ce6..ffb92222c84 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -678,9 +678,9 @@ CREATE TABLE `t3` (
   PRIMARY KEY  (`numeropost`),
   UNIQUE KEY `maxnumrep` (`maxnumrep`)
 ) ENGINE=MyISAM CHARSET=latin1;
-INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
+INSERT IGNORE INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
 
-INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
+INSERT IGNORE INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
 
 INSERT INTO t3 VALUES (1,1);
 
@@ -3341,7 +3341,7 @@ create table t1(f11 int, f12 int);
 create table t2(f21 int unsigned not null, f22 int, f23 varchar(10));
 insert into t1 values(1,1),(2,2), (3, 3);
 --disable_warnings
-insert into t2
+insert ignore into t2
 select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
 from t0 A, t0 B, t0 C, t0 D;
 --enable_warnings
diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test
index b3c1322184d..bd34546d83f 100644
--- a/mysql-test/t/subselect2.test
+++ b/mysql-test/t/subselect2.test
@@ -28,7 +28,7 @@ DOCID VARCHAR(32)BINARY NOT NULL
 ) ENGINE=InnoDB
 ;
 
-INSERT INTO t1 (DOCID) VALUES ("1"), ("2");
+INSERT IGNORE INTO t1 (DOCID) VALUES ("1"), ("2");
 
 CREATE TABLE t2
 (
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 559b380a7dc..e20291cfa67 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1329,7 +1329,7 @@ DROP TABLE t1,t2,t3,t4,t5;
 --echo #
 
 CREATE TABLE t2 (a int);
-INSERT INTO t2 VALUES ('a'),('a');
+INSERT IGNORE INTO t2 VALUES ('a'),('a');
 
 CREATE TABLE t4 (a varchar(1));
 INSERT INTO t4 VALUES ('m'),('o');
diff --git a/mysql-test/t/system_mysql_db_refs.test b/mysql-test/t/system_mysql_db_refs.test
index c5146e1b043..63f30e7db63 100644
--- a/mysql-test/t/system_mysql_db_refs.test
+++ b/mysql-test/t/system_mysql_db_refs.test
@@ -11,37 +11,37 @@ set @name="This is a very long string, that mustn't find room in a system field
 create table test_db select * from mysql.db;
 delete from test_db;
 --disable_warnings
-insert into test_db (Host,Db,User) values (@name,@name,@name);
+insert ignore into test_db (Host,Db,User) values (@name,@name,@name);
 --enable_warnings
 
 create table test_host select * from mysql.host;
 delete from test_host;
 --disable_warnings
-insert into test_host (Host,Db) values (@name,@name);
+insert ignore into test_host (Host,Db) values (@name,@name);
 --enable_warnings
 
 create table test_user select * from mysql.user;
 delete from test_user;
 --disable_warnings
-insert into test_user (Host,User) values (@name,@name);
+insert ignore into test_user (Host,User) values (@name,@name);
 --enable_warnings
 
 create table test_func select * from mysql.func;
 delete from test_func;
 --disable_warnings
-insert into test_func (name) values (@name);
+insert ignore into test_func (name) values (@name);
 --enable_warnings
 
 create table test_tables_priv select * from mysql.tables_priv;
 delete from test_tables_priv;
 --disable_warnings
-insert into test_tables_priv (Host,Db,User,Table_name) values (@name,@name,@name,@name);
+insert ignore into test_tables_priv (Host,Db,User,Table_name) values (@name,@name,@name,@name);
 --enable_warnings
 
 create table test_columns_priv select * from mysql.columns_priv;
 delete from test_columns_priv;
 --disable_warnings
-insert into test_columns_priv (Host,Db,User,Table_name,Column_name) values (@name,@name,@name,@name,@name);
+insert ignore into test_columns_priv (Host,Db,User,Table_name,Column_name) values (@name,@name,@name,@name,@name);
 --enable_warnings
 
 # 'Host' field must be the same for all the tables:
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 050bd5ea56e..83b6966c676 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -2367,7 +2367,7 @@ DROP TABLE t1, t2;
 CREATE TABLE t1 (id INT NOT NULL);
 CREATE TABLE t2 (id INT NOT NULL);
 INSERT t1 VALUES (1),(2),(3);
-UPDATE t1 SET id=NULL;
+UPDATE IGNORE t1 SET id=NULL;
 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
   INSERT INTO t2 VALUES (3);
 UPDATE t1 SET id=NULL;
@@ -2463,6 +2463,7 @@ DROP TEMPORARY TABLE t2;
 # Bug#36649: Condition area is not properly cleaned up after stored routine invocation
 #
 
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 --disable_warnings
 DROP TRIGGER IF EXISTS trg1;
 DROP TABLE IF EXISTS t1;
@@ -2512,6 +2513,7 @@ BEGIN
 END|
 
 delimiter ;|
+SET sql_mode = DEFAULT;
 
 INSERT INTO t1 VALUES (0);
 SELECT * FROM t1;
diff --git a/mysql-test/t/type_binary.test b/mysql-test/t/type_binary.test
index b583e257aa9..952bc6ef231 100644
--- a/mysql-test/t/type_binary.test
+++ b/mysql-test/t/type_binary.test
@@ -74,7 +74,7 @@ select hex(cast(0x10 as binary(2)));
 # 
 create table t1 (b binary(2), vb varbinary(2));
 insert into t1 values(0x4120, 0x4120);
-insert into t1 values(0x412020, 0x412020);
+insert ignore into t1 values(0x412020, 0x412020);
 drop table t1;
 create table t1 (c char(2), vc varchar(2));
 insert into t1 values(0x4120, 0x4120);
@@ -95,7 +95,7 @@ set @@sql_mode= @old_sql_mode;
 # Bug#14171: Wrong default value for a BINARY field
 #
 create table t1(f1 int, f2 binary(2) not null, f3 char(2) not null);
-insert into t1 set f1=1;
+insert ignore into t1 set f1=1;
 select hex(f2), hex(f3) from t1;
 drop table t1;
 
diff --git a/mysql-test/t/type_bit.test b/mysql-test/t/type_bit.test
index 2ca608e76ff..761f200fe0c 100644
--- a/mysql-test/t/type_bit.test
+++ b/mysql-test/t/type_bit.test
@@ -168,7 +168,7 @@ drop table t1;
 #
 
 create table t1 (a bit(8)) engine=heap;
-insert into t1 values ('1111100000');
+insert ignore into t1 values ('1111100000');
 select a+0 from t1;
 drop table t1;
 
@@ -257,7 +257,7 @@ drop table bug15583;
 #
 
 create table t1(a bit(1), b smallint unsigned);
-insert into t1 (b, a) values ('2', '1');
+insert ignore into t1 (b, a) values ('2', '1');
 select hex(a), b from t1;
 drop table t1;
 
@@ -324,7 +324,7 @@ DROP TABLE t2;
 
 CREATE TABLE t1(a BIT(13), KEY(a));
 --disable_warnings
-INSERT INTO t1(a) VALUES
+INSERT IGNORE INTO t1(a) VALUES
 (65535),(65525),(65535),(65535),(65535),(65535),(65535),(65535),(65535),(65535);
 --enable_warnings
 
diff --git a/mysql-test/t/type_bit_innodb.test b/mysql-test/t/type_bit_innodb.test
index 27eaeda0f99..4c6f9bc1b49 100644
--- a/mysql-test/t/type_bit_innodb.test
+++ b/mysql-test/t/type_bit_innodb.test
@@ -38,7 +38,7 @@ select hex(a) from t1;
 drop table t1;
 
 create table t1 (a bit) engine=innodb;
-insert into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001');
+insert ignore into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001');
 select hex(a) from t1;
 # It is not deterministic which duplicate will be seen first
 --replace_regex /entry '(.*)' for/entry '' for/
@@ -47,7 +47,7 @@ alter table t1 add unique (a);
 drop table t1;
 
 create table t1 (a bit(2)) engine=innodb;
-insert into t1 values (b'00'), (b'01'), (b'10'), (b'100');
+insert ignore into t1 values (b'00'), (b'01'), (b'10'), (b'100');
 select a+0 from t1;
 alter table t1 add key (a);
 explain select a+0 from t1;
diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test
index b818e1b451d..8db6ac6da2a 100644
--- a/mysql-test/t/type_blob.test
+++ b/mysql-test/t/type_blob.test
@@ -17,7 +17,9 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7;
 CREATE TABLE t1 (a blob, b text, c blob(250), d text(70000), e text(70000000));
 show columns from t1;
 # PS doesn't give errors on prepare yet
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t2 (a char(255), b varbinary(70000), c varchar(70000000));
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t4 (c varchar(65530) character set utf8 not null);
 show columns from t2;
 create table t3 (a long, b long byte);
@@ -33,6 +35,7 @@ drop table t1,t2,t3,t4;
 CREATE TABLE t1 (a char(257) default "hello");
 --error 1074
 CREATE TABLE t2 (a char(256));
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t1 (a varchar(70000) default "hello");
 SHOW CREATE TABLE t1;
 CREATE TABLE t2 (a blob default "hello");
diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test
index 63c89f318fc..11924f696db 100644
--- a/mysql-test/t/type_date.test
+++ b/mysql-test/t/type_date.test
@@ -121,7 +121,7 @@ DROP TABLE t1, t2, t3;
 # Test that setting YEAR to invalid string results in default value, not
 # 2000. (Bug #6067)
 CREATE TABLE t1 (y YEAR);
-INSERT INTO t1 VALUES ('abc');
+INSERT IGNORE INTO t1 VALUES ('abc');
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -145,7 +145,7 @@ select year(@d), month(@d), day(@d), cast(@d as date);
 select @d:=1311;
 select year(@d), month(@d), day(@d), cast(@d as date);
 create table t1 (d  date , dt datetime , ts timestamp);
-insert into t1 values (9912101,9912101,9912101);
+insert ignore into t1 values (9912101,9912101,9912101);
 insert into t1 values (11111,11111,11111);
 select * from t1;
 drop table t1;
@@ -282,7 +282,7 @@ DROP TABLE t1;
 # lp:737496 Field_temporal::store_TIME_with_warning() in 5.1-micro
 #
 create table t1 (f1 date, key (f1));
-insert t1 values ('2010-10-10 15:foobar');
+insert ignore t1 values ('2010-10-10 15:foobar');
 drop table t1;
 
 --echo #
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index b16d426ab70..99c9bb656e6 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -24,7 +24,7 @@ select * from t1;
 
 # Test some wrong dates
 truncate table t1;
-insert into t1 values("2003-0303 12:13:14");
+insert ignore into t1 values("2003-0303 12:13:14");
 select * from t1;
 drop table t1;
 
@@ -88,16 +88,16 @@ drop table t1;
 # warnings (for both strings and numbers)
 #
 create table t1 (t datetime);
-insert into t1 values (20030102030460),(20030102036301),(20030102240401),
+insert ignore into t1 values (20030102030460),(20030102036301),(20030102240401),
                       (20030132030401),(20031302030401),(100001202030401);
 select * from t1;
 delete from t1;
-insert into t1 values
+insert ignore into t1 values
   ("2003-01-02 03:04:60"),("2003-01-02 03:63:01"),("2003-01-02 24:04:01"),
   ("2003-01-32 03:04:01"),("2003-13-02 03:04:01"), ("10000-12-02 03:04:00");
 select * from t1;
 delete from t1;
-insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
+insert ignore into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
 select * from t1 order by t;
 drop table t1;
 
diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test
index 834fd0c5327..777bb2f23e5 100644
--- a/mysql-test/t/type_decimal.test
+++ b/mysql-test/t/type_decimal.test
@@ -162,34 +162,34 @@ create table t1 (a decimal(10,2));
 insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0");
 insert into t1 values ("-.1"),("+.1"),(".1");
 insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001");
-insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
-insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
-insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
-insert into t1 values ("1e+4294967296"),("1e-4294967296");
-insert into t1 values ("1e+18446744073709551615"),("1e+18446744073709551616"),("1e-9223372036854775807"),("1e-9223372036854775809");
-insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
+insert ignore into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
+insert ignore into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
+insert ignore into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
+insert ignore into t1 values ("1e+4294967296"),("1e-4294967296");
+insert ignore into t1 values ("1e+18446744073709551615"),("1e+18446744073709551616"),("1e-9223372036854775807"),("1e-9223372036854775809");
+insert ignore into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
 select * from t1;
 drop  table t1;
 
 create table t1 (a decimal(10,2) unsigned);
 insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0");
-insert into t1 values ("-.1"),("+.1"),(".1");
+insert ignore into t1 values ("-.1"),("+.1"),(".1");
 insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001");
-insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
-insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
-insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
-insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
+insert ignore into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
+insert ignore into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
+insert ignore into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
+insert ignore into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
 select * from t1;
 drop  table t1;
 
 create table t1 (a decimal(10,2) zerofill);
 insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0");
-insert into t1 values ("-.1"),("+.1"),(".1");
+insert ignore into t1 values ("-.1"),("+.1"),(".1");
 insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001");
-insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
-insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
-insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
-insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
+insert ignore into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
+insert ignore into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
+insert ignore into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
+insert ignore into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
 select * from t1;
 drop  table t1;
 
@@ -199,9 +199,9 @@ create table t1 (a decimal(10,2));
 insert into t1 values (0.0),("-0.0"),(+0.0),(01.0),(+01.0),(-01.0);
 insert into t1 values (-.1),(+.1),(.1);
 insert into t1 values (00000000000001),(+0000000000001),(-0000000000001);
-insert into t1 values (+111111111.11),(111111111.11),(-11111111.11);
-insert into t1 values (-111111111.11),(+1111111111.11),(1111111111.11);
-insert into t1 values (1e+100),(1e-100),(-1e+100);
+insert ignore into t1 values (+111111111.11),(111111111.11),(-11111111.11);
+insert ignore into t1 values (-111111111.11),(+1111111111.11),(1111111111.11);
+insert ignore into t1 values (1e+100),(1e-100),(-1e+100);
 insert into t1 values (123.4e0),(123.4e+2),(123.4e-2),(123e1),(123e+0);
 insert into t1 values (MID("987",1,2)),("987 "),("987.6e+2 ");
 select * from t1;
@@ -212,28 +212,28 @@ drop  table t1;
 #
 
 create table t1 (a decimal);
-insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+12345678901'),(99999999999999);
+insert ignore into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+12345678901'),(99999999999999);
 select * from t1;
 drop  table t1;
 create table t1 (a decimal unsigned);
-insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
+insert ignore into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
 select * from t1;
 drop  table t1;
 create table t1 (a decimal zerofill);
-insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
+insert ignore into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
 select * from t1;
 drop  table t1;
 create table t1 (a decimal unsigned zerofill);
-insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
+insert ignore into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
 select * from t1;
 drop  table t1;
 
 #  Exponent overflow bug
 create table t1(a decimal(10,0));
-insert into t1 values ("1e4294967295");
+insert ignore into t1 values ("1e4294967295");
 select * from t1;
 delete from t1;
-insert into t1 values("1e4294967297");
+insert ignore into t1 values("1e4294967297");
 select * from t1;
 drop table t1;
 
diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test
index 63e9e9e7b09..105da427219 100644
--- a/mysql-test/t/type_enum.test
+++ b/mysql-test/t/type_enum.test
@@ -30,9 +30,9 @@ drop table t1;
 #
 
 create table t1 (a enum ('0','1'));
-insert into t1 set a='foobar';
+insert ignore into t1 set a='foobar';
 select * from t1;
-update t1 set a = replace(a,'x','y');
+update ignore t1 set a = replace(a,'x','y');
 select * from t1;
 drop table t1;
 
@@ -165,9 +165,10 @@ CREATE TABLE t1 (
   id INT AUTO_INCREMENT PRIMARY KEY,
   c1 ENUM('a', '', 'b')
 );
-INSERT INTO t1 (c1) VALUES (0), ('a'), (''), ('b');
+INSERT IGNORE INTO t1 (c1) VALUES (0), ('a'), (''), ('b');
 SELECT id, c1 + 0, c1 FROM t1;
 
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 CHANGE c1 c1 ENUM('a', '') NOT NULL;
 SELECT id, c1 + 0, c1 FROM t1;
 
@@ -191,7 +192,7 @@ drop table t1;
 CREATE TABLE t1 (c1 ENUM('a', '', 'b'));
 INSERT INTO t1 (c1) VALUES ('b');
 INSERT INTO t1 (c1) VALUES ('');
-INSERT INTO t1 (c1) VALUES (0);
+INSERT IGNORE INTO t1 (c1) VALUES (0);
 INSERT INTO t1 (c1) VALUES ('');
 
 SELECT c1 + 0, COUNT(c1) FROM t1 GROUP BY c1;
@@ -398,12 +399,13 @@ DROP TABLE t1;
 CREATE TABLE t1 (a ENUM('9e200','9e100'));
 CREATE TABLE t2 (a DOUBLE);
 INSERT INTO t2 VALUES ('9e100');
-INSERT INTO t1 SELECT * FROM t2;
+INSERT IGNORE INTO t1 SELECT * FROM t2;
 SELECT * FROM t1;
 DROP TABLE t1,t2;
 
 CREATE TABLE t1 (a DOUBLE);
 INSERT INTO t1 VALUES (9e100);
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY a ENUM('9e200','9e100');
 SELECT * FROM t1;
 DROP TABLE t1;
@@ -411,7 +413,7 @@ DROP TABLE t1;
 CREATE TABLE t1 (a ENUM('200','100'));
 CREATE TABLE t2 (a DOUBLE);
 INSERT INTO t2 VALUES ('100');
-INSERT INTO t1 SELECT * FROM t2;
+INSERT IGNORE INTO t1 SELECT * FROM t2;
 SELECT * FROM t1;
 DROP TABLE t1,t2;
 
@@ -419,12 +421,13 @@ DROP TABLE t1,t2;
 CREATE TABLE t1 (a ENUM('200','100'));
 CREATE TABLE t2 (a INT);
 INSERT INTO t2 VALUES ('100');
-INSERT INTO t1 SELECT * FROM t2;
+INSERT IGNORE INTO t1 SELECT * FROM t2;
 SELECT * FROM t1;
 DROP TABLE t1,t2;
 
 CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES ('200');
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY a ENUM('200','100');
 SELECT *FROM t1;
 DROP TABLE t1;
@@ -432,7 +435,7 @@ DROP TABLE t1;
 CREATE TABLE t1 (a ENUM('200','100'));
 CREATE TABLE t2 (a INT);
 INSERT INTO t2 VALUES ('100');
-INSERT INTO t1 SELECT * FROM t2;
+INSERT IGNORE INTO t1 SELECT * FROM t2;
 SELECT * FROM t1;
 DROP TABLE t1,t2;
 
@@ -441,12 +444,13 @@ DROP TABLE t1,t2;
 CREATE TABLE t1 (a ENUM('2001','2002'));
 CREATE TABLE t2 (a YEAR);
 INSERT INTO t2 VALUES ('2001');
-INSERT INTO t1 SELECT * FROM t2;
+INSERT IGNORE INTO t1 SELECT * FROM t2;
 SELECT * FROM t1;
 DROP TABLE t1,t2;
 
 CREATE TABLE t1 (a YEAR);
 INSERT INTO t1 VALUES ('2001');
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY a ENUM('2001','2002');
 SELECT * FROM t1;
 DROP TABLE t1;
diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test
index f54eacf0595..4e8cee75d63 100644
--- a/mysql-test/t/type_float.test
+++ b/mysql-test/t/type_float.test
@@ -83,8 +83,8 @@ create table t1 (a float(200,100), b double(200,100));
 # float in a char(1) field
 #
 create table t1 (c20 char);
-insert into t1 values (5000.0);
-insert into t1 values (0.5e4);
+insert ignore into t1 values (5000.0);
+insert ignore into t1 values (0.5e4);
 drop table t1;
 
 # Errors
@@ -98,18 +98,18 @@ drop table if exists t1;
 # Don't allow 'double unsigned' to be set to a negative value (Bug #7700)
 create table t1 (d1 double, d2 double unsigned);
 insert into t1 set d1 = -1.0;
-update t1 set d2 = d1;
+update ignore t1 set d2 = d1;
 select * from t1;
 drop table t1;
 
 # Ensure that maximum values as the result of number of decimals
 # being specified in table schema are enforced (Bug #7361)
 create table t1 (f float(4,3));
-insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
+insert ignore into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
 select * from t1;
 drop table if exists t1;
 create table t1 (f double(4,3));
-insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
+insert ignore into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
 select * from t1;
 drop table if exists t1;
 
@@ -241,14 +241,14 @@ DROP TABLE t1;
 create table t1 (f1 double(200, 0));
 insert into t1 values (1e199), (-1e199);
 insert into t1 values (1e200), (-1e200);
-insert into t1 values (2e200), (-2e200);
+insert ignore into t1 values (2e200), (-2e200);
 select f1 + 0e0 from t1;
 drop table t1;
 
 create table t1 (f1 float(30, 0));
 insert into t1 values (1e29), (-1e29);
 insert into t1 values (1e30), (-1e30);
-insert into t1 values (2e30), (-2e30);
+insert ignore into t1 values (2e30), (-2e30);
 select f1 + 0e0 from t1;
 drop table t1;
 
@@ -339,8 +339,8 @@ select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo;
 
 CREATE TABLE t1 (f FLOAT);
 INSERT INTO t1 VALUES ('1.');
-INSERT INTO t1 VALUES ('2.0.');
-INSERT INTO t1 VALUES ('.');
+INSERT IGNORE INTO t1 VALUES ('2.0.');
+INSERT IGNORE INTO t1 VALUES ('.');
 SELECT * FROM t1 ORDER BY f;
 DROP TABLE t1;
 
@@ -467,8 +467,8 @@ DROP TABLE t1;
 --echo # MDEV-4102 Limitation on DOUBLE or REAL length is ignored with INSERT .. SELECT
 --echo #
 CREATE TABLE t1 (d1 DOUBLE(5,2), d2 DOUBLE(10,2));
-INSERT INTO t1 VALUES (10000000.55, 10000000.55);
-INSERT INTO t1 SELECT d2, d2 FROM t1;
+INSERT IGNORE INTO t1 VALUES (10000000.55, 10000000.55);
+INSERT IGNORE INTO t1 SELECT d2, d2 FROM t1;
 SELECT * FROM t1;
 DROP TABLE t1;
 
@@ -481,14 +481,14 @@ CREATE TABLE t1 (
   d10_10 DOUBLE PRECISION (10,10),
   d53_10 DOUBLE(53,10)
 );
-INSERT INTO t1 (f,d10_10,d53_10) VALUES (
+INSERT IGNORE INTO t1 (f,d10_10,d53_10) VALUES (
   -9999999999999999999999999999999999999999999.9999999999,
   -9999999999999999999999999999999999999999999.9999999999,
   -9999999999999999999999999999999999999999999.9999999999
 );
 --vertical_results
 SELECT * FROM t1;
-INSERT INTO t1 (f,d10_10,d53_10) SELECT d53_10, d53_10, d53_10 FROM t1;
+INSERT IGNORE INTO t1 (f,d10_10,d53_10) SELECT d53_10, d53_10, d53_10 FROM t1;
 SELECT * FROM t1;
 ALTER TABLE t1 ADD COLUMN i INT;
 SELECT * FROM t1;
@@ -498,14 +498,14 @@ DROP TABLE t1;
 CREATE TABLE t1 (d10_10 DOUBLE (10,10));
 CREATE TABLE t2 (d53_10 DOUBLE (53,10));
 INSERT INTO t2 VALUES (-9999999999999999999999999999999999999999999.9999999999);
-INSERT INTO t1 (d10_10) SELECT d53_10 FROM t2;
+INSERT IGNORE INTO t1 (d10_10) SELECT d53_10 FROM t2;
 SELECT * FROM t1;
 DROP TABLE t1,t2;
 
 CREATE TABLE t1 (d2_2 FLOAT (2,2));
 CREATE TABLE t2 (d4_2 FLOAT (4,2));
 INSERT INTO t2 VALUES (99.99);
-INSERT INTO t1 (d2_2) SELECT d4_2 FROM t2;
+INSERT IGNORE INTO t1 (d2_2) SELECT d4_2 FROM t2;
 SELECT * FROM t1;
 DROP TABLE t1,t2;
 
diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test
index 152c3ade487..eb0fac57150 100644
--- a/mysql-test/t/type_newdecimal.test
+++ b/mysql-test/t/type_newdecimal.test
@@ -50,13 +50,13 @@ drop table t1;
 #
 
 create table t1 (a decimal(4,2));
-insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
-insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
+insert ignore into t1 value (10000), (1.1e10), ("11111"), (100000.1);
+insert ignore into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
 select a from t1;
 drop table t1;
 create table t1 (a decimal(4,2) unsigned);
-insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
-insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
+insert ignore into t1 value (10000), (1.1e10), ("11111"), (100000.1);
+insert ignore into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
 select a from t1;
 drop table t1;
 
@@ -65,21 +65,21 @@ drop table t1;
 # saving in field with overflow from decimal
 #
 create table t1 (a bigint);
-insert into t1 values (18446744073709551615.0);
-insert into t1 values (9223372036854775808.0);
-insert into t1 values (-18446744073709551615.0);
+insert ignore into t1 values (18446744073709551615.0);
+insert ignore into t1 values (9223372036854775808.0);
+insert ignore into t1 values (-18446744073709551615.0);
 select * from t1;
 drop table t1;
 create table t1 (a bigint unsigned);
 insert into t1 values (18446744073709551615.0);
 insert into t1 values (9223372036854775808.0);
-insert into t1 values (9999999999999999999999999.000);
-insert into t1 values (-1.0);
+insert ignore into t1 values (9999999999999999999999999.000);
+insert ignore into t1 values (-1.0);
 select * from t1;
 drop table t1;
 create table t1 (a tinyint);
-insert into t1 values (18446744073709551615.0);
-insert into t1 values (9223372036854775808.0);
+insert ignore into t1 values (18446744073709551615.0);
+insert ignore into t1 values (9223372036854775808.0);
 select * from t1;
 drop table t1;
 
diff --git a/mysql-test/t/type_num.test b/mysql-test/t/type_num.test
index 64242e34160..87f5cc609d9 100644
--- a/mysql-test/t/type_num.test
+++ b/mysql-test/t/type_num.test
@@ -433,17 +433,17 @@ CREATE TABLE t1 (
 );
 
 INSERT INTO t1 VALUES ('1 ','1 ','1 ','1 ','1 ','1 ','1 ');
-INSERT INTO t1 VALUES ('','','','','','','');
-INSERT INTO t1 VALUES ('x','x','x','x','x','x','x');
-INSERT INTO t1 VALUES (' x',' x',' x',' x',' x',' x',' x');
-INSERT INTO t1 VALUES ('.','.','.','.','.','.','.');
-INSERT INTO t1 VALUES ('-','-','-','-','-','-','-');
-INSERT INTO t1 VALUES ('+','+','+','+','+','+','+');
-INSERT INTO t1 VALUES ('1x','1x','1x','1x','1x','1x','1x');
-INSERT INTO t1 VALUES ('1e','1e','1e','1e','1e','1e','1e');
-INSERT INTO t1 VALUES ('1e-','1e-','1e-','1e-','1e-','1e-','1e-');
-INSERT INTO t1 VALUES ('1E+','1E+','1E+','1E+','1E+','1E+','1E+');
-INSERT INTO t1 VALUES ('1e1000','1e1000','1e1000','1e1000','1e1000','1e1000','1e1000');
+INSERT IGNORE INTO t1 VALUES ('','','','','','','');
+INSERT IGNORE INTO t1 VALUES ('x','x','x','x','x','x','x');
+INSERT IGNORE INTO t1 VALUES (' x',' x',' x',' x',' x',' x',' x');
+INSERT IGNORE INTO t1 VALUES ('.','.','.','.','.','.','.');
+INSERT IGNORE INTO t1 VALUES ('-','-','-','-','-','-','-');
+INSERT IGNORE INTO t1 VALUES ('+','+','+','+','+','+','+');
+INSERT IGNORE INTO t1 VALUES ('1x','1x','1x','1x','1x','1x','1x');
+INSERT IGNORE INTO t1 VALUES ('1e','1e','1e','1e','1e','1e','1e');
+INSERT IGNORE INTO t1 VALUES ('1e-','1e-','1e-','1e-','1e-','1e-','1e-');
+INSERT IGNORE INTO t1 VALUES ('1E+','1E+','1E+','1E+','1E+','1E+','1E+');
+INSERT IGNORE INTO t1 VALUES ('1e1000','1e1000','1e1000','1e1000','1e1000','1e1000','1e1000');
 
 DELETE FROM t1;
 INSERT INTO t1 VALUES (0,0,0,0,0,0,0);
diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test
index 44b0835fcae..7bf29321d06 100644
--- a/mysql-test/t/type_ranges.test
+++ b/mysql-test/t/type_ranges.test
@@ -58,10 +58,10 @@ DROP INDEX test ON t1;
 
 insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one');
 insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,NULL,2,2,'two','two,one');
-insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3);
-insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1);
-insert into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"one,two,tree");
-insert into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,0,4294967295,4294967295,4294967295,'4294967295',0,0);
+insert ignore into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3);
+insert ignore into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1);
+insert ignore into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"one,two,tree");
+insert ignore into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,0,4294967295,4294967295,4294967295,'4294967295',0,0);
 insert into t1 (tiny) values (1);
 
 select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000),date_field,time_field,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1;
@@ -103,7 +103,7 @@ CREATE TABLE t2 (
   PRIMARY KEY (auto)
 );
 
-INSERT INTO t2 (string,mediumblob_col,new_field) SELECT string,mediumblob_col,new_field from t1 where auto > 10;
+INSERT IGNORE INTO t2 (string,mediumblob_col,new_field) SELECT string,mediumblob_col,new_field from t1 where auto > 10;
 
 select * from t2;
 
diff --git a/mysql-test/t/type_set.test b/mysql-test/t/type_set.test
index 16e4f42301d..7a79cd12f70 100644
--- a/mysql-test/t/type_set.test
+++ b/mysql-test/t/type_set.test
@@ -49,6 +49,7 @@ set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17',
 '18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33',
 '34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49',
 '50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','128'));
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1(f1
 set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17',
 '18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33',
diff --git a/mysql-test/t/type_temporal_innodb.test b/mysql-test/t/type_temporal_innodb.test
index ac5daca6508..1ab68961eaa 100644
--- a/mysql-test/t/type_temporal_innodb.test
+++ b/mysql-test/t/type_temporal_innodb.test
@@ -12,7 +12,7 @@
 SELECT TIME'00:00:00'='';
 
 CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=InnoDB;
-INSERT INTO t1 VALUES ('','00:00:00',0);
+INSERT IGNORE INTO t1 VALUES ('','00:00:00',0);
 SELECT * FROM t1 WHERE b='';
 SELECT * FROM t1 IGNORE KEY (b) WHERE b='';
 SELECT * FROM t1 WHERE a=b;
@@ -29,7 +29,7 @@ DROP TABLE t1;
 SELECT DATE'0000-00-00'='';
 
 CREATE TABLE t1 (a ENUM('a'), b DATE, c INT, KEY(b)) ENGINE=InnoDB;
-INSERT INTO t1 VALUES ('','0000-00-00',0);
+INSERT IGNORE INTO t1 VALUES ('','0000-00-00',0);
 SELECT * FROM t1 WHERE b='';
 SELECT * FROM t1 IGNORE KEY (b) WHERE b='';
 SELECT * FROM t1 WHERE a=b;
@@ -46,7 +46,7 @@ DROP TABLE t1;
 SELECT TIMESTAMP'0000-00-00 00:00:00'='';
 
 CREATE TABLE t1 (a ENUM('a'), b DATETIME, c INT, KEY(b)) ENGINE=InnoDB;
-INSERT INTO t1 VALUES ('','0000-00-00 00:00:00',0);
+INSERT IGNORE INTO t1 VALUES ('','0000-00-00 00:00:00',0);
 SELECT * FROM t1 WHERE b='';
 SELECT * FROM t1 IGNORE KEY (b) WHERE b='';
 SELECT * FROM t1 WHERE a=b;
diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test
index c4b93d6dbb2..fb96597e296 100644
--- a/mysql-test/t/type_time.test
+++ b/mysql-test/t/type_time.test
@@ -11,7 +11,7 @@ insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(12345
 insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32");
 select * from t1;
 # Test wrong values
-insert into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a");
+insert ignore into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a");
 select * from t1;
 drop table t1;
 
@@ -219,7 +219,7 @@ CREATE TABLE t1 (a TIME(6));
 --error ER_TRUNCATED_WRONG_VALUE
 INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); 
 SET sql_mode=DEFAULT;
-INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); 
+INSERT IGNORE INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); 
 SELECT * FROM t1;
 DROP TABLE t1;
 SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED);
@@ -740,6 +740,7 @@ CREATE TABLE t1 (a YEAR, b TIME, c YEAR);
 CREATE TABLE t2 (a YEAR);
 INSERT INTO t2 VALUES (0),(1999),(2000),(2030),(2050),(2070);
 INSERT INTO t1 (a,b,c) SELECT a,a,a FROM t2;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY c TIME;
 SELECT * FROM t1;
 DROP TABLE t1,t2;
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index 460769f8961..b90e122aa79 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -78,7 +78,7 @@ truncate table t1;
 insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000");
 select ix+0 from t1;
 truncate table t1;
-insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
+insert ignore into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
 select ix+0 from t1;
 drop table t1;
 
diff --git a/mysql-test/t/type_uint.test b/mysql-test/t/type_uint.test
index 84fca993d09..ae48b30997a 100644
--- a/mysql-test/t/type_uint.test
+++ b/mysql-test/t/type_uint.test
@@ -9,8 +9,8 @@ SET SQL_WARNINGS=1;
 
 create table t1 (this int unsigned);
 insert into t1 values (1);
-insert into t1 values (-1);
-insert into t1 values ('5000000000');
+insert ignore into t1 values (-1);
+insert ignore into t1 values ('5000000000');
 select * from t1;
 drop table t1;
 
diff --git a/mysql-test/t/type_year.test b/mysql-test/t/type_year.test
index 22f164a757c..117906fd889 100644
--- a/mysql-test/t/type_year.test
+++ b/mysql-test/t/type_year.test
@@ -17,7 +17,7 @@ drop table t1;
 #
 
 create table t1 (y year);
-insert into t1 values (now());
+insert ignore into t1 values (now());
 select if(y = now(), 1, 0) from t1;
 drop table t1;
 
@@ -138,7 +138,7 @@ DROP TABLE t2, t4;
 --echo #
 
 CREATE TABLE t1 (y YEAR NOT NULL, s VARCHAR(4));
-INSERT INTO t1 (s) VALUES ('bad');
+INSERT IGNORE INTO t1 (s) VALUES ('bad');
 INSERT INTO t1 (y, s) VALUES (0, 0), (2000, 2000), (2001, 2001);
 
 SELECT * FROM t1 ta, t1 tb WHERE ta.y = tb.y;
@@ -219,32 +219,34 @@ DROP TABLE t1;
 --echo # MDEV-9392 Copying from DECIMAL to YEAR is not consistent about warnings
 --echo #
 CREATE TABLE t1 (a YEAR);
-INSERT INTO t1 VALUES (-0.1);
+INSERT IGNORE INTO t1 VALUES (-0.1);
 DROP TABLE t1;
 
 CREATE TABLE t1 (a YEAR);
 CREATE TABLE t2 (a DECIMAL(10,1));
 INSERT INTO t2 VALUES (-0.1);
-INSERT INTO t1 SELECT * FROM t2;
+INSERT IGNORE INTO t1 SELECT * FROM t2;
 DROP TABLE t1,t2;
 
 CREATE TABLE t1 (a DECIMAL(10,1));
 INSERT INTO t1 VALUES (-0.1);
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY a YEAR;
 DROP TABLE t1;
 
 CREATE TABLE t1 (a YEAR);
-INSERT INTO t1 VALUES (-0.1e0);
+INSERT IGNORE INTO t1 VALUES (-0.1e0);
 DROP TABLE t1;
 
 CREATE TABLE t1 (a YEAR);
 CREATE TABLE t2 (a DOUBLE);
 INSERT INTO t2 VALUES (-0.1);
-INSERT INTO t1 SELECT * FROM t2;
+INSERT IGNORE INTO t1 SELECT * FROM t2;
 DROP TABLE t1,t2;
 
 CREATE TABLE t1 (a DOUBLE);
 INSERT INTO t1 VALUES (-0.1);
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY a YEAR;
 DROP TABLE t1;
 
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index af509eb7b85..fbe307be5ba 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -1222,6 +1222,7 @@ drop table t1;
 #
 # changing value by trigger and CHECK OPTION
 #
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 create table t1 (s1 tinyint);
 create trigger t1_bi before insert on t1 for each row set new.s1 = 500;
 create view v1 as select * from t1 where s1 <> 127 with check option;
@@ -1232,6 +1233,7 @@ select * from t1;
 drop trigger t1_bi;
 drop view v1;
 drop table t1;
+SET sql_mode = default;
 
 #
 # CASCADED should be used for all underlaying VIEWs
@@ -2678,7 +2680,7 @@ DROP TABLE t1,t2;
 CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL);
 CREATE VIEW v1 AS SELECT a, b FROM t1;
 
-INSERT INTO v1 (b) VALUES (2);
+INSERT IGNORE INTO v1 (b) VALUES (2);
 
 SET SQL_MODE = STRICT_ALL_TABLES;
 --error ER_NO_DEFAULT_FOR_VIEW_FIELD
@@ -3571,7 +3573,7 @@ DROP TABLE t1;
 --echo #
 create table t1(f1 int, f2 int not null);
 create view v1 as select f1 from t1;
-insert into v1 values(1);
+insert ignore into v1 values(1);
 set @old_mode=@@sql_mode;
 set @@sql_mode=traditional;
 --error ER_NO_DEFAULT_FOR_VIEW_FIELD
diff --git a/mysql-test/t/warnings.test b/mysql-test/t/warnings.test
index 29714438835..573f3c538c7 100644
--- a/mysql-test/t/warnings.test
+++ b/mysql-test/t/warnings.test
@@ -21,11 +21,11 @@ create table t (i);
 show count(*) errors;
 show errors;
 insert into t1 values (1);
-insert into t1 values ("hej");
-insert into t1 values ("hej"),("då");
+insert ignore into t1 values ("hej");
+insert ignore into t1 values ("hej"),("då");
 set SQL_WARNINGS=1;
-insert into t1 values ("hej");
-insert into t1 values ("hej"),("då");
+insert ignore into t1 values ("hej");
+insert ignore into t1 values ("hej"),("då");
 drop table t1;
 set SQL_WARNINGS=0;
 
@@ -62,17 +62,18 @@ drop table t1;
 #
 
 create table t1(a tinyint NOT NULL, b tinyint unsigned, c char(5));
-insert into t1 values(NULL,100,'mysql'),(10,-1,'mysql ab'),(500,256,'open source'),(20,NULL,'test');
+insert ignore into t1 values(NULL,100,'mysql'),(10,-1,'mysql ab'),(500,256,'open source'),(20,NULL,'test');
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 modify c char(4);
 alter table t1 add d char(2);
-update t1 set a=NULL where a=10;
-update t1 set c='mysql ab' where c='test';
-update t1 set d=c;
+update ignore t1 set a=NULL where a=10;
+update ignore t1 set c='mysql ab' where c='test';
+update ignore t1 set d=c;
 create table t2(a tinyint NOT NULL, b char(3));
-insert into t2 select b,c from t1;
-insert into t2(b) values('mysqlab');
+insert ignore into t2 select b,c from t1;
+insert ignore into t2(b) values('mysqlab');
 set sql_warnings=1;
-insert into t2(b) values('mysqlab');
+insert ignore into t2(b) values('mysqlab');
 set sql_warnings=0;
 drop table t1, t2;
 
@@ -93,7 +94,7 @@ commit;
 --enable_query_log
 alter table t1 add b char;
 set max_error_count=10;
-update t1 set b=a;
+update ignore t1 set b=a;
 --disable_ps_protocol
 select @@warning_count;
 --enable_ps_protocol
@@ -101,7 +102,7 @@ select @@warning_count;
 # Bug#9072
 set max_error_count=0;
 show variables like 'max_error_count';
-update t1 set b='hi';
+update ignore t1 set b='hi';
 --disable_ps_protocol
 select @@warning_count;
 --enable_ps_protocol
@@ -119,7 +120,7 @@ drop table t1;
 create table t1 (a int);
 insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
 # should generate 10 warnings
-update t1 set a='abc';
+update ignore t1 set a='abc';
 show warnings limit 2, 1;
 show warnings limit 0, 10;
 show warnings limit 9, 1;
@@ -152,6 +153,7 @@ INSERT INTO t3 VALUES ( 'a`', 'a`', '1000-01-1' );
 DROP PROCEDURE IF EXISTS sp1;
 DROP PROCEDURE IF EXISTS sp2;
 DROP PROCEDURE IF EXISTS sp3;
+SET sql_mode = '';
 delimiter //;
 CREATE PROCEDURE sp1()
 BEGIN
@@ -172,8 +174,10 @@ delimiter ;//
 CALL sp1();
 CALL sp2();
 CALL sp3();
+SET sql_mode = DEFAULT;
 
 DROP PROCEDURE IF EXISTS sp1;
+SET sql_mode = '';
 delimiter //;
 CREATE PROCEDURE sp1()
 BEGIN
@@ -182,6 +186,7 @@ SELECT f1 into x from t2 limit 1;
 END//
 delimiter ;//
 CALL sp1();
+SET sql_mode = DEFAULT;
 DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t3;
diff --git a/mysql-test/t/xtradb_mrr.test b/mysql-test/t/xtradb_mrr.test
index b56cbb0459f..3e84668955a 100644
--- a/mysql-test/t/xtradb_mrr.test
+++ b/mysql-test/t/xtradb_mrr.test
@@ -403,7 +403,7 @@ INSERT INTO t1 VALUES
 ('IB','IBM- Inc.'),
 ('GO','Google Inc.');
 
-INSERT INTO t2 VALUES
+INSERT IGNORE INTO t2 VALUES
 ('AB','Sweden'),
 ('JA','USA'),
 ('MS','United States of America'),