MDEV-7635: Update tests to adapt to the new default sql_mode (part 4)bb-10.2-mdev7635.part1

35 files changed, 193 insertions, 50 deletions

diff --git a/mysql-test/r/order_by_zerolength-4285.result b/mysql-test/r/order_by_zerolength-4285.result
index f60ce7d90c7..52204d14191 100644
--- a/mysql-test/r/order_by_zerolength-4285.result
+++ b/mysql-test/r/order_by_zerolength-4285.result
@@ -3,14 +3,14 @@ insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 select * from t1 order by now(), cast(pk as char(0));
 pk
 1
-2
-3
-4
-5
-6
-7
-8
 9
+8
+7
+6
+5
+4
+3
+2
 10
 Warnings:
 Warning	1292	Truncated incorrect CHAR(0) value: '1'
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 9b7d84dfdc4..6fbe2d62fd7 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -1879,6 +1879,7 @@ DROP PROCEDURE p1;
 #
 # Bug#5889: Exit handler for a warning doesn't hide the warning in trigger
 #
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1(a INT, b INT);
 INSERT INTO t1 VALUES (1, 2);
 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
@@ -1895,6 +1896,7 @@ a	b
 10	20
 DROP TRIGGER t1_bu;
 DROP TABLE t1;
+SET sql_mode = DEFAULT;
 #
 # Bug#9857: Stored procedures: handler for sqlwarning ignored
 #
@@ -1940,11 +1942,11 @@ CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT);
 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);
 
 CALL p1();
 Warnings:
@@ -1962,6 +1964,7 @@ DROP TABLE t1;
 DROP TABLE t2;
 DROP PROCEDURE p1;
 # ----------------------------------------------------------------------
+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);
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
@@ -1991,6 +1994,7 @@ Error	1048	Column 'c' cannot be null
 DROP TABLE t1;
 DROP TABLE t2;
 DROP PROCEDURE p1;
+SET sql_mode = DEFAULT;
 
 ###################################################################
 # Tests for the following bugs:
@@ -2614,7 +2618,7 @@ SELECT 'Warning caught' AS msg;
 # The INSERT below raises 3 SQL-conditions (warnings). The EXIT HANDLER
 # 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.
 INSERT INTO t1 VALUES('zzz', 'xx', 'yyyy');
 END|
@@ -2638,7 +2642,7 @@ SELECT 'Warning 1292 caught' AS msg;
 # and 3 SQL-warnings with code 1264. The CONTINUE HANDLER above must be
 # 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),
@@ -2708,7 +2712,7 @@ SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1234;
 SHOW WARNINGS;
 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),
@@ -2735,7 +2739,7 @@ BEGIN
 SHOW WARNINGS;
 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),
@@ -2771,6 +2775,7 @@ DROP TABLE t2;
 
 DROP PROCEDURE IF EXISTS p1;
 DROP PROCEDURE IF EXISTS p2;
+SET sql_mode = '';
 CREATE PROCEDURE p1()
 BEGIN
 DECLARE var1 INTEGER DEFAULT 'string';
@@ -2781,6 +2786,7 @@ CALL p1()|
 Warnings:
 Warning	1366	Incorrect integer value: 'string' for column 'var1' at row 1
 
+SET sql_mode = DEFAULT;
 CREATE PROCEDURE p2()
 BEGIN
 DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H2';
diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result
index 73cc4f154c7..c052fdb2ce1 100644
--- a/mysql-test/r/sp_notembedded.result
+++ b/mysql-test/r/sp_notembedded.result
@@ -56,6 +56,7 @@ return prm * bug10100f(prm - 1);
 end if;
 return 1;
 end|
+set sql_mode = ''|
 create procedure bug10100p(prm int, inout res int)
 begin
 set res = res * prm;
@@ -70,6 +71,7 @@ set res = 1;
 call bug10100p(prm, res);
 select res;
 end|
+set sql_mode = default|
 create table t3 (a int)|
 insert into t3 values (0)|
 create view v1 as select a from t3;
@@ -210,7 +212,7 @@ disconnect rl_contender;
 drop procedure p1;
 drop table t1;
 set session low_priority_updates=default;
-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/suite/funcs_1/r/innodb_trig_1011ext.result b/mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result
index 8e9a654d4ed..f4a6d75fbc0 100644
--- a/mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result
+++ b/mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result
@@ -163,6 +163,7 @@ Testcase 3.5.10.6: (implemented in trig_frkey.test)
 
 Testcase 3.5.10.extra:
 ----------------------
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 create table t1_sp (var136 tinyint, var151 decimal) engine = <engine_to_be_used>;
 create trigger trg before insert on t1_sp
 for each row set @counter=@counter+1;
@@ -199,6 +200,7 @@ count(*)
 drop procedure trig_sp;
 drop trigger trg;
 drop table t1_sp;
+set sql_mode = default;
 
 Testcase 3.5.11.1 (implemented in trig_perf.test)
 -------------------------------------------------
@@ -290,7 +292,7 @@ drop table t4;
 
 Testcase y.y.y.4: Recursive trigger/SP references
 -------------------------------------------------
-set @sql_mode='traditional';
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 create table t1_sp (
 count integer,
 var136 tinyint,
@@ -347,6 +349,7 @@ count(*)
 drop procedure trig_sp;
 drop trigger trg;
 drop table t1_sp;
+set sql_mode = default;
 
 Testcase y.y.y.5: Rollback of nested trigger references
 -------------------------------------------------------
diff --git a/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc b/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc
index dd8b2a87cd8..88b04a0eefd 100644
--- a/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc
+++ b/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc
@@ -144,7 +144,7 @@ let $message= Testcase 3.5.10.6: (implemented in trig_frkey.test);
 #           on the subject table from a stored procedure is indeed activated correctly
 let $message= Testcase 3.5.10.extra:;
 --source include/show_msg.inc
-
+        set sql_mode = 'NO_ENGINE_SUBSTITUTION';
         --replace_result $engine_type <engine_to_be_used>
 	eval create table t1_sp (var136 tinyint, var151 decimal) engine = $engine_type;
 
@@ -184,6 +184,7 @@ let $message= Testcase 3.5.10.extra:;
 	drop trigger trg;
 	drop table t1_sp;
 	--enable_warnings
+        set sql_mode = default;
 
 ##################################
 ########## Section 3.5.11 ########
@@ -321,8 +322,7 @@ let $message= Testcase y.y.y.3: Circular trigger reference;
 # Testcase: create recursive trigger/storedprocedures conditions
 let $message= Testcase y.y.y.4: Recursive trigger/SP references;
 --source include/show_msg.inc
-
-set @sql_mode='traditional';
+        set sql_mode = 'NO_ENGINE_SUBSTITUTION';
         --replace_result $engine_type <engine_to_be_used>
 	eval create table t1_sp (
 		count integer,
@@ -375,6 +375,7 @@ set @sql_mode='traditional';
 	drop trigger trg;
 	drop table t1_sp;
 	--enable_warnings
+        set sql_mode = default;
 
 
 # Testcase: y.y.y.5:
diff --git a/mysql-test/suite/gcol/inc/gcol_ins_upd.inc b/mysql-test/suite/gcol/inc/gcol_ins_upd.inc
index 4b3431eea2e..7fde9c2e852 100644
--- a/mysql-test/suite/gcol/inc/gcol_ins_upd.inc
+++ b/mysql-test/suite/gcol/inc/gcol_ins_upd.inc
@@ -47,7 +47,7 @@ delete from t1;
 select * from t1;
 
 --echo # INSERT INTO tbl_name VALUES... a non-NULL value is specified against gcols
-insert into t1 values (1,2,3);
+insert ignore into t1 values (1,2,3);
 select * from t1;
 delete from t1;
 select * from t1;
@@ -73,7 +73,7 @@ select * from t1;
 
 --echo # INSERT INTO tbl_name (<normal+gcols>) VALUES... a non-NULL value is specified
 --echo # against gcols
-insert into t1 (a,b) values (1,3), (2,4);
+insert ignore into t1 (a,b) values (1,3), (2,4);
 select * from t1;
 delete from t1;
 select * from t1;
@@ -131,7 +131,7 @@ select * from t1;
 --echo # UPDATE tbl_name SET gcol=expr WHERE non-gcol=expr
 insert into t1 (a) values (1), (2);
 select * from t1 order by a;
-update t1 set c=3 where a=2;
+update ignore t1 set c=3 where a=2;
 select * from t1 order by a;
 delete from t1;
 select * from t1;
@@ -147,7 +147,7 @@ select * from t1;
 --echo # UPDATE tbl_name SET gcol=expr WHERE gcol=expr
 insert into t1 (a) values (1), (2);
 select * from t1 order by a;
-update t1 set c=3 where b=-2;
+update ignore t1 set c=3 where b=-2;
 select * from t1 order by a;
 delete from t1;
 select * from t1;
@@ -493,7 +493,7 @@ b TIMESTAMP(4) GENERATED ALWAYS AS ('') VIRTUAL,
 KEY (a(183),b)
 );
 
-INSERT INTO t VALUES(), (), ();
+INSERT IGNORE INTO t VALUES(), (), ();
 
 DELETE IGNORE FROM t;
 
@@ -512,7 +512,7 @@ CREATE TABLE t (
   UNIQUE(b),
   UNIQUE(b1)
 );
-INSERT INTO t VALUES();
+INSERT IGNORE INTO t VALUES();
 SELECT b from t;
 SELECT b1 from t;
 SELECT * from t;
diff --git a/mysql-test/suite/gcol/inc/gcol_keys.inc b/mysql-test/suite/gcol/inc/gcol_keys.inc
index 7f888ef54a5..44824215c40 100644
--- a/mysql-test/suite/gcol/inc/gcol_keys.inc
+++ b/mysql-test/suite/gcol/inc/gcol_keys.inc
@@ -676,7 +676,9 @@ INSERT INTO t(a) VALUES (1);
 # Before index was created, this query returned the expected one match.
 SELECT * FROM t WHERE c = '0';
 # Adding an index sometimes crashed, other times populated it with garbage ...
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t ADD UNIQUE INDEX (c(1));
+SET sql_mode = DEFAULT;
 # ... so that this query found no match in the index.
 SELECT * FROM t WHERE c = '0';
 DROP TABLE t;
@@ -689,9 +691,16 @@ CREATE TABLE t (a INT, b INT, h VARCHAR(10));
 INSERT INTO t VALUES (12, 3, "ss");
 INSERT INTO t VALUES (13, 4, "ss");
 INSERT INTO t VALUES (14, 0, "ss");
+--error ER_DIVISION_BY_ZERO
 ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a/b) VIRTUAL;
-#--error ER_DIVISION_BY_ZERO
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
+ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a/b) VIRTUAL;
+SET sql_mode = DEFAULT;
+--error ER_DIVISION_BY_ZERO
+CREATE INDEX idx ON t(c);
+SET sql_mode = '';
 CREATE INDEX idx ON t(c);
+set sql_mode = DEFAULT;
 CALL mtr.add_suppression("\\[Warning\\] InnoDB: Compute virtual column values failed");
 DROP TABLE t;
 }
diff --git a/mysql-test/suite/gcol/inc/gcol_select.inc b/mysql-test/suite/gcol/inc/gcol_select.inc
index efaffd5168d..632a0cca922 100644
--- a/mysql-test/suite/gcol/inc/gcol_select.inc
+++ b/mysql-test/suite/gcol/inc/gcol_select.inc
@@ -852,15 +852,19 @@ DROP TABLE t2, t3;
 --disable_abort_on_error
 CREATE TABLE t1(a INT);
 INSERT INTO t1 VALUES(2147483647);
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL;
 ALTER TABLE t1 DROP COLUMN b;
 ALTER TABLE t1 ADD COLUMN c SMALLINT AS (a) VIRTUAL;
 ALTER TABLE t1 DROP COLUMN c;
 ALTER TABLE t1 ADD COLUMN d SMALLINT AS (a) VIRTUAL;
 ALTER TABLE t1 DROP COLUMN d;
+SET sql_mode = DEFAULT;
 ALTER TABLE t1 ADD COLUMN c INT AS(a) VIRTUAL;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 CHANGE c c SMALLINT AS(a) VIRTUAL;
 ALTER TABLE t1 MODIFY c TINYINT AS(a) VIRTUAL;
+SET sql_mode = DEFAULT;
 SELECT * FROM t1;
 DROP TABLE t1;
 CREATE TABLE t1(a INT);
@@ -869,8 +873,10 @@ ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL;
 ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, ALGORITHM=COPY;
 ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
 ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED;
 ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUSIVE;
+SET sql_mode = DEFAULT;
 --enable_abort_on_error
 DROP TABLE t1;
 
diff --git a/mysql-test/suite/gcol/inc/gcol_supported_sql_funcs_main.inc b/mysql-test/suite/gcol/inc/gcol_supported_sql_funcs_main.inc
index 88268ddd6c4..957940b8c99 100644
--- a/mysql-test/suite/gcol/inc/gcol_supported_sql_funcs_main.inc
+++ b/mysql-test/suite/gcol/inc/gcol_supported_sql_funcs_main.inc
@@ -937,7 +937,9 @@ let $cols = a datetime, b datetime generated always as (last_day(a)) virtual;
 let $values1 = '2003-02-05',default;
 let $values2 = '2003-02-32',default;
 let $rows = 2;
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 --source suite/gcol/inc/gcol_supported_sql_funcs.inc
+set sql_mode = DEFAULT;
 
 --echo # MAKEDATE()
 let $cols = a int, b datetime generated always as (makedate(a,1)) virtual;
@@ -1046,7 +1048,9 @@ let $rows = 1;
 let $cols = a datetime, b datetime, c time generated always as (timediff(a,b)) virtual;
 let $values1 = '2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',default;
 let $rows = 1;
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 --source suite/gcol/inc/gcol_supported_sql_funcs.inc
+set sql_mode = DEFAULT;
 
 --echo # TIMESTAMP()
 let $cols = a datetime, b timestamp generated always as (timestamp(a)) virtual;
diff --git a/mysql-test/suite/gcol/r/gcol_column_def_options_myisam.result b/mysql-test/suite/gcol/r/gcol_column_def_options_myisam.result
index f6d0830ee3d..15522f94c1e 100644
--- a/mysql-test/suite/gcol/r/gcol_column_def_options_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_column_def_options_myisam.result
@@ -354,15 +354,19 @@ ERROR 42000: Can't DROP INDEX `PRIMARY`; check that it exists
 DROP TABLE t1;
 # Bug#20949226:i CAN ASSIGN NON-DEFAULT() VALUE TO GENERATED COLUMN
 #
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5 AS c2;
 Warnings:
 Warning	1906	The value specified for generated column 'c2' in table 't1' ignored
+SET sql_mode = DEFAULT;
 CREATE TABLE t2 (a int);
 INSERT INTO t2 values(1);
 DROP TABLE t1;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, a AS c2 from t2;
 Warnings:
 Warning	1906	The value specified for generated column 'c2' in table 't1' ignored
+SET sql_mode = DEFAULT;
 DROP TABLE t1;
 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5;
 SELECT * FROM t1;
diff --git a/mysql-test/suite/gcol/r/gcol_ins_upd_innodb.result b/mysql-test/suite/gcol/r/gcol_ins_upd_innodb.result
index 192016ba8df..3c2e4d3dd0a 100644
--- a/mysql-test/suite/gcol/r/gcol_ins_upd_innodb.result
+++ b/mysql-test/suite/gcol/r/gcol_ins_upd_innodb.result
@@ -23,7 +23,7 @@ delete from t1;
 select * from t1;
 a	b	c
 # INSERT INTO tbl_name VALUES... a non-NULL value is specified against gcols
-insert into t1 values (1,2,3);
+insert ignore into t1 values (1,2,3);
 Warnings:
 Warning	1906	The value specified for generated column 'b' in table 't1' ignored
 Warning	1906	The value specified for generated column 'c' in table 't1' ignored
@@ -63,7 +63,7 @@ select * from t1;
 a	b	c
 # INSERT INTO tbl_name (<normal+gcols>) VALUES... a non-NULL value is specified
 # against gcols
-insert into t1 (a,b) values (1,3), (2,4);
+insert ignore into t1 (a,b) values (1,3), (2,4);
 Warnings:
 Warning	1906	The value specified for generated column 'b' in table 't1' ignored
 Warning	1906	The value specified for generated column 'b' in table 't1' ignored
@@ -151,7 +151,7 @@ select * from t1 order by a;
 a	b	c
 1	-1	-1
 2	-2	-2
-update t1 set c=3 where a=2;
+update ignore t1 set c=3 where a=2;
 Warnings:
 Warning	1906	The value specified for generated column 'c' in table 't1' ignored
 select * from t1 order by a;
@@ -181,7 +181,7 @@ select * from t1 order by a;
 a	b	c
 1	-1	-1
 2	-2	-2
-update t1 set c=3 where b=-2;
+update ignore t1 set c=3 where b=-2;
 Warnings:
 Warning	1906	The value specified for generated column 'c' in table 't1' ignored
 select * from t1 order by a;
diff --git a/mysql-test/suite/gcol/r/gcol_ins_upd_myisam.result b/mysql-test/suite/gcol/r/gcol_ins_upd_myisam.result
index b30eb709c47..4d62a5d7a82 100644
--- a/mysql-test/suite/gcol/r/gcol_ins_upd_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_ins_upd_myisam.result
@@ -23,7 +23,7 @@ delete from t1;
 select * from t1;
 a	b	c
 # INSERT INTO tbl_name VALUES... a non-NULL value is specified against gcols
-insert into t1 values (1,2,3);
+insert ignore into t1 values (1,2,3);
 Warnings:
 Warning	1906	The value specified for generated column 'b' in table 't1' ignored
 Warning	1906	The value specified for generated column 'c' in table 't1' ignored
@@ -63,7 +63,7 @@ select * from t1;
 a	b	c
 # INSERT INTO tbl_name (<normal+gcols>) VALUES... a non-NULL value is specified
 # against gcols
-insert into t1 (a,b) values (1,3), (2,4);
+insert ignore into t1 (a,b) values (1,3), (2,4);
 Warnings:
 Warning	1906	The value specified for generated column 'b' in table 't1' ignored
 Warning	1906	The value specified for generated column 'b' in table 't1' ignored
@@ -151,7 +151,7 @@ select * from t1 order by a;
 a	b	c
 1	-1	-1
 2	-2	-2
-update t1 set c=3 where a=2;
+update ignore t1 set c=3 where a=2;
 Warnings:
 Warning	1906	The value specified for generated column 'c' in table 't1' ignored
 select * from t1 order by a;
@@ -181,7 +181,7 @@ select * from t1 order by a;
 a	b	c
 1	-1	-1
 2	-2	-2
-update t1 set c=3 where b=-2;
+update ignore t1 set c=3 where b=-2;
 Warnings:
 Warning	1906	The value specified for generated column 'c' in table 't1' ignored
 select * from t1 order by a;
@@ -549,7 +549,7 @@ a BLOB GENERATED ALWAYS AS ('') VIRTUAL,
 b TIMESTAMP(4) GENERATED ALWAYS AS ('') VIRTUAL,
 KEY (a(183),b)
 );
-INSERT INTO t VALUES(), (), ();
+INSERT IGNORE INTO t VALUES(), (), ();
 Warnings:
 Warning	1265	Data truncated for column 'b' at row 1
 Warning	1265	Data truncated for column 'b' at row 2
@@ -568,7 +568,7 @@ c1 YEAR GENERATED ALWAYS AS ('aaaa') STORED,
 UNIQUE(b),
 UNIQUE(b1)
 );
-INSERT INTO t VALUES();
+INSERT IGNORE INTO t VALUES();
 SELECT b from t;
 b
 2000
diff --git a/mysql-test/suite/gcol/r/gcol_keys_myisam.result b/mysql-test/suite/gcol/r/gcol_keys_myisam.result
index a331b1d2154..9da6313abce 100644
--- a/mysql-test/suite/gcol/r/gcol_keys_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_keys_myisam.result
@@ -706,9 +706,11 @@ INSERT INTO t(a) VALUES (1);
 SELECT * FROM t WHERE c = '0';
 a	b	c
 1	127	0
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t ADD UNIQUE INDEX (c(1));
 Warnings:
 Warning	1264	Out of range value for column 'b' at row 1
+SET sql_mode = DEFAULT;
 SELECT * FROM t WHERE c = '0';
 a	b	c
 1	127	0
@@ -722,7 +724,15 @@ INSERT INTO t VALUES (12, 3, "ss");
 INSERT INTO t VALUES (13, 4, "ss");
 INSERT INTO t VALUES (14, 0, "ss");
 ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a/b) VIRTUAL;
+ERROR 22012: Division by 0
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
+ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a/b) VIRTUAL;
+SET sql_mode = DEFAULT;
+CREATE INDEX idx ON t(c);
+ERROR 22012: Division by 0
+SET sql_mode = '';
 CREATE INDEX idx ON t(c);
+set sql_mode = DEFAULT;
 CALL mtr.add_suppression("\\[Warning\\] InnoDB: Compute virtual column values failed");
 DROP TABLE t;
 #
diff --git a/mysql-test/suite/gcol/r/gcol_select_innodb.result b/mysql-test/suite/gcol/r/gcol_select_innodb.result
index 24daadb0d5b..61b86f55a96 100644
--- a/mysql-test/suite/gcol/r/gcol_select_innodb.result
+++ b/mysql-test/suite/gcol/r/gcol_select_innodb.result
@@ -512,19 +512,23 @@ DROP TABLE t2, t3;
 #
 CREATE TABLE t1(a INT);
 INSERT INTO t1 VALUES(2147483647);
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL;
 ALTER TABLE t1 DROP COLUMN b;
 ALTER TABLE t1 ADD COLUMN c SMALLINT AS (a) VIRTUAL;
 ALTER TABLE t1 DROP COLUMN c;
 ALTER TABLE t1 ADD COLUMN d SMALLINT AS (a) VIRTUAL;
 ALTER TABLE t1 DROP COLUMN d;
+SET sql_mode = DEFAULT;
 ALTER TABLE t1 ADD COLUMN c INT AS(a) VIRTUAL;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 CHANGE c c SMALLINT AS(a) VIRTUAL;
 Warnings:
 Warning	1264	Out of range value for column 'c' at row 1
 ALTER TABLE t1 MODIFY c TINYINT AS(a) VIRTUAL;
 Warnings:
 Warning	1264	Out of range value for column 'c' at row 1
+SET sql_mode = DEFAULT;
 SELECT * FROM t1;
 a	c
 2147483647	127
@@ -537,6 +541,7 @@ ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED;
 Warnings:
 Warning	1264	Out of range value for column 'f' at row 1
@@ -544,6 +549,7 @@ ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUS
 Warnings:
 Warning	1264	Out of range value for column 'f' at row 1
 Warning	1264	Out of range value for column 'g' at row 1
+SET sql_mode = DEFAULT;
 DROP TABLE t1;
 #
 # Bug#21980430 GCOLS: CRASHING
diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result
index d5e4bdabb38..374bc6915b3 100644
--- a/mysql-test/suite/gcol/r/gcol_select_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result
@@ -1121,6 +1121,7 @@ DROP TABLE t2, t3;
 #
 CREATE TABLE t1(a INT);
 INSERT INTO t1 VALUES(2147483647);
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL;
 Warnings:
 Warning	1264	Out of range value for column 'b' at row 1
@@ -1133,13 +1134,16 @@ ALTER TABLE t1 ADD COLUMN d SMALLINT AS (a) VIRTUAL;
 Warnings:
 Warning	1264	Out of range value for column 'd' at row 1
 ALTER TABLE t1 DROP COLUMN d;
+SET sql_mode = DEFAULT;
 ALTER TABLE t1 ADD COLUMN c INT AS(a) VIRTUAL;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 CHANGE c c SMALLINT AS(a) VIRTUAL;
 Warnings:
 Warning	1264	Out of range value for column 'c' at row 1
 ALTER TABLE t1 MODIFY c TINYINT AS(a) VIRTUAL;
 Warnings:
 Warning	1264	Out of range value for column 'c' at row 1
+SET sql_mode = DEFAULT;
 SELECT * FROM t1;
 a	c
 2147483647	127
@@ -1152,6 +1156,7 @@ ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED;
 Warnings:
 Warning	1264	Out of range value for column 'f' at row 1
@@ -1159,6 +1164,7 @@ ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUS
 Warnings:
 Warning	1264	Out of range value for column 'f' at row 1
 Warning	1264	Out of range value for column 'g' at row 1
+SET sql_mode = DEFAULT;
 DROP TABLE t1;
 #
 # Bug#21980430 GCOLS: CRASHING
diff --git a/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_innodb.result b/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_innodb.result
index 25e07e450d9..a6032c74432 100644
--- a/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_innodb.result
+++ b/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_innodb.result
@@ -273,10 +273,14 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 insert into t1 values (2,default);
 insert into t1 values (-2,default);
+Warnings:
+Warning	1365	Division by 0
 select * from t1;
 a	b
 -2	NULL
 2	0.693147
+Warning	1365	Division by 0
+Warnings:
 drop table t1;
 set sql_warnings = 0;
 # LOG()
@@ -292,11 +296,15 @@ t1	CREATE TABLE `t1` (
 insert into t1 values (2,65536,default);
 insert into t1 values (10,100,default);
 insert into t1 values (1,100,default);
+Warnings:
+Warning	1365	Division by 0
 select * from t1;
 a	b	c
 1	100	NULL
 10	100	2
 2	65536	16
+Warning	1365	Division by 0
+Warnings:
 drop table t1;
 set sql_warnings = 0;
 set sql_warnings = 1;
@@ -309,10 +317,14 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 insert into t1 values (2,default);
 insert into t1 values (-2,default);
+Warnings:
+Warning	1365	Division by 0
 select * from t1;
 a	b
 -2	NULL
 2	0.693147
+Warning	1365	Division by 0
+Warnings:
 drop table t1;
 set sql_warnings = 0;
 # LOG2()
@@ -326,10 +338,14 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 insert into t1 values (65536,default);
 insert into t1 values (-100,default);
+Warnings:
+Warning	1365	Division by 0
 select * from t1;
 a	b
 -100	NULL
 65536	16
+Warning	1365	Division by 0
+Warnings:
 drop table t1;
 set sql_warnings = 0;
 # LOG10()
@@ -344,11 +360,15 @@ t1	CREATE TABLE `t1` (
 insert into t1 values (2,default);
 insert into t1 values (100,default);
 insert into t1 values (-100,default);
+Warnings:
+Warning	1365	Division by 0
 select * from t1;
 a	b
 -100	NULL
 100	2
 2	0.30103
+Warning	1365	Division by 0
+Warnings:
 drop table t1;
 set sql_warnings = 0;
 # -
@@ -2254,6 +2274,7 @@ a	b
 drop table t1;
 set sql_warnings = 0;
 # LAST_DAY()
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 set sql_warnings = 1;
 create table t1 (a datetime, b datetime generated always as (last_day(a)) virtual);
 show create table t1;
@@ -2272,6 +2293,7 @@ a	b
 2003-02-05 00:00:00	2003-02-28 00:00:00
 drop table t1;
 set sql_warnings = 0;
+set sql_mode = DEFAULT;
 # MAKEDATE()
 set sql_warnings = 1;
 create table t1 (a int, b datetime generated always as (makedate(a,1)) virtual);
@@ -2531,6 +2553,7 @@ a	b
 drop table t1;
 set sql_warnings = 0;
 # TIMEDIFF()
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 set sql_warnings = 1;
 create table t1 (a datetime, b datetime, c time generated always as (timediff(a,b)) virtual);
 show create table t1;
@@ -2546,6 +2569,7 @@ a	b	c
 2008-12-31 23:59:59	2008-12-30 01:01:01	46:58:58
 drop table t1;
 set sql_warnings = 0;
+set sql_mode = DEFAULT;
 # TIMESTAMP()
 set sql_warnings = 1;
 create table t1 (a datetime, b timestamp generated always as (timestamp(a)) virtual);
diff --git a/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_myisam.result b/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_myisam.result
index 03be01c3229..72f6d385353 100644
--- a/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_myisam.result
@@ -273,10 +273,14 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 insert into t1 values (2,default);
 insert into t1 values (-2,default);
+Warnings:
+Warning	1365	Division by 0
 select * from t1;
 a	b
 -2	NULL
 2	0.693147
+Warning	1365	Division by 0
+Warnings:
 drop table t1;
 set sql_warnings = 0;
 # LOG()
@@ -292,11 +296,15 @@ t1	CREATE TABLE `t1` (
 insert into t1 values (2,65536,default);
 insert into t1 values (10,100,default);
 insert into t1 values (1,100,default);
+Warnings:
+Warning	1365	Division by 0
 select * from t1;
 a	b	c
 1	100	NULL
 10	100	2
 2	65536	16
+Warning	1365	Division by 0
+Warnings:
 drop table t1;
 set sql_warnings = 0;
 set sql_warnings = 1;
@@ -309,10 +317,14 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 insert into t1 values (2,default);
 insert into t1 values (-2,default);
+Warnings:
+Warning	1365	Division by 0
 select * from t1;
 a	b
 -2	NULL
 2	0.693147
+Warning	1365	Division by 0
+Warnings:
 drop table t1;
 set sql_warnings = 0;
 # LOG2()
@@ -326,10 +338,14 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 insert into t1 values (65536,default);
 insert into t1 values (-100,default);
+Warnings:
+Warning	1365	Division by 0
 select * from t1;
 a	b
 -100	NULL
 65536	16
+Warning	1365	Division by 0
+Warnings:
 drop table t1;
 set sql_warnings = 0;
 # LOG10()
@@ -344,11 +360,15 @@ t1	CREATE TABLE `t1` (
 insert into t1 values (2,default);
 insert into t1 values (100,default);
 insert into t1 values (-100,default);
+Warnings:
+Warning	1365	Division by 0
 select * from t1;
 a	b
 -100	NULL
 100	2
 2	0.30103
+Warning	1365	Division by 0
+Warnings:
 drop table t1;
 set sql_warnings = 0;
 # -
@@ -2254,6 +2274,7 @@ a	b
 drop table t1;
 set sql_warnings = 0;
 # LAST_DAY()
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 set sql_warnings = 1;
 create table t1 (a datetime, b datetime generated always as (last_day(a)) virtual);
 show create table t1;
@@ -2272,6 +2293,7 @@ a	b
 2003-02-05 00:00:00	2003-02-28 00:00:00
 drop table t1;
 set sql_warnings = 0;
+set sql_mode = DEFAULT;
 # MAKEDATE()
 set sql_warnings = 1;
 create table t1 (a int, b datetime generated always as (makedate(a,1)) virtual);
@@ -2531,6 +2553,7 @@ a	b
 drop table t1;
 set sql_warnings = 0;
 # TIMEDIFF()
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 set sql_warnings = 1;
 create table t1 (a datetime, b datetime, c time generated always as (timediff(a,b)) virtual);
 show create table t1;
@@ -2546,6 +2569,7 @@ a	b	c
 2008-12-31 23:59:59	2008-12-30 01:01:01	46:58:58
 drop table t1;
 set sql_warnings = 0;
+set sql_mode = DEFAULT;
 # TIMESTAMP()
 set sql_warnings = 1;
 create table t1 (a datetime, b timestamp generated always as (timestamp(a)) virtual);
diff --git a/mysql-test/suite/gcol/r/innodb_virtual_fk.result b/mysql-test/suite/gcol/r/innodb_virtual_fk.result
index 38a13edd8fa..3ba7b8dc260 100644
--- a/mysql-test/suite/gcol/r/innodb_virtual_fk.result
+++ b/mysql-test/suite/gcol/r/innodb_virtual_fk.result
@@ -346,6 +346,10 @@ ON UPDATE CASCADE);
 INSERT INTO t1 VALUES(1), (2);
 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
 UPDATE t1 SET fld1= 0 WHERE fld1= 2;
+ERROR 22012: Division by 0
+UPDATE IGNORE t1 SET fld1= 0 WHERE fld1= 2;
+Warnings:
+Warning	1365	Division by 0
 SELECT fld2 FROM t2;
 fld2
 NULL
diff --git a/mysql-test/suite/gcol/t/innodb_virtual_fk.test b/mysql-test/suite/gcol/t/innodb_virtual_fk.test
index bd8f3664839..37a2b871a00 100644
--- a/mysql-test/suite/gcol/t/innodb_virtual_fk.test
+++ b/mysql-test/suite/gcol/t/innodb_virtual_fk.test
@@ -290,8 +290,9 @@ CREATE TABLE t2(fld1 INT NOT NULL,
 		ON UPDATE CASCADE);
 INSERT INTO t1 VALUES(1), (2);
 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
-#--error ER_DIVISION_BY_ZERO
+--error ER_DIVISION_BY_ZERO
 UPDATE t1 SET fld1= 0 WHERE fld1= 2;
+UPDATE IGNORE t1 SET fld1= 0 WHERE fld1= 2;
 SELECT fld2 FROM t2;
 DROP TABLE t2, t1;
 
diff --git a/mysql-test/suite/innodb_fts/r/create.result b/mysql-test/suite/innodb_fts/r/create.result
index c537aa81efd..615f0368599 100644
--- a/mysql-test/suite/innodb_fts/r/create.result
+++ b/mysql-test/suite/innodb_fts/r/create.result
@@ -16,10 +16,10 @@ INSERT INTO t SET t=REPEAT(_utf8 0xefbc91,84);
 INSERT INTO t SET t=REPEAT('after',17);
 INSERT INTO t SET t=REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15);
 # The data below is not 3-byte UTF-8, but 4-byte chars.
-INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84);
+INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84);
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9F\x96\x95\xF0\x9F...' for column 't' at row 1
-INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85);
+INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85);
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9F\x96\x96\xF0\x9F...' for column 't' at row 1
 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST
diff --git a/mysql-test/suite/innodb_fts/t/create.test b/mysql-test/suite/innodb_fts/t/create.test
index f0329602ed1..fe0273af750 100644
--- a/mysql-test/suite/innodb_fts/t/create.test
+++ b/mysql-test/suite/innodb_fts/t/create.test
@@ -26,8 +26,8 @@ INSERT INTO t SET t=REPEAT(_utf8 0xefbc91,84);
 INSERT INTO t SET t=REPEAT('after',17); # too long, will not be indexed
 INSERT INTO t SET t=REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15);
 --echo # The data below is not 3-byte UTF-8, but 4-byte chars.
-INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84);
-INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85);
+INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84);
+INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85);
 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST
 (REPEAT(CONCAT(REPEAT(_utf8mb3 0xE0B987, 4), REPEAT(_utf8mb3 0xE0B989, 5)), 5));
 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('BeforeTheIndexCreation');
diff --git a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result
index 78d5f79e311..f4c6dc4bc65 100644
--- a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result
+++ b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result
@@ -255,7 +255,9 @@ c1	ST_Astext(c2)	ST_Astext(c4)
 INSERT INTO tab SELECT * FROM tab1;
 ALTER TABLE tab DROP PRIMARY KEY;
 ALTER TABLE tab DROP INDEX idx2;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2;
+SET sql_mode = DEFAULT;
 INSERT INTO temp_tab SELECT * FROM tab;
 CREATE SPATIAL INDEX idx2 ON temp_tab(c2);
 CREATE SPATIAL INDEX idx3 ON temp_tab(c3);
diff --git a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test
index efd6cb6c867..993ebbe9914 100644
--- a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test
+++ b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test
@@ -303,8 +303,9 @@ ALTER TABLE tab DROP PRIMARY KEY;
 ALTER TABLE tab DROP INDEX idx2;
 
 # Check spatial index on temp tables
-
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2;
+SET sql_mode = DEFAULT;
 
 INSERT INTO temp_tab SELECT * FROM tab;
 
diff --git a/mysql-test/suite/innodb_zip/r/index_large_prefix.result b/mysql-test/suite/innodb_zip/r/index_large_prefix.result
index 88fd0438f45..4e57b27dec8 100644
--- a/mysql-test/suite/innodb_zip/r/index_large_prefix.result
+++ b/mysql-test/suite/innodb_zip/r/index_large_prefix.result
@@ -102,6 +102,7 @@ Got one of the listed errors
 show warnings;
 Level	Code	Message
 Error	1071	Specified key was too long; max key length is 767 bytes
+Error	1118	Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
 set global innodb_large_prefix=1;
 Warnings:
 Warning	131	Using innodb_large_prefix is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
@@ -110,6 +111,7 @@ Got one of the listed errors
 show warnings;
 Level	Code	Message
 Error	1071	Specified key was too long; max key length is 3072 bytes
+Error	1118	Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
 create index idx3 on worklog5743_1(a2(436));
 ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
 show warnings;
diff --git a/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc b/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc
index f807405d18d..5acea045d0f 100644
--- a/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc
+++ b/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc
@@ -152,7 +152,7 @@ DROP TABLE t1,t2;
 --echo #
 --echo # Examine the number of times triggers are recalculated for updates
 --echo #
-
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 (
   a INTEGER UNSIGNED NULL DEFAULT NULL,
   b CHAR(10) NULL DEFAULT NULL,
@@ -216,10 +216,12 @@ drop trigger t1_ins;
 drop trigger t1_update;
 drop table t1;
 
+SET sql_mode = DEFAULT;
+
 --echo #
 --echo # Same test, but with virtual keys
 --echo #
-
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 (
   a INTEGER UNSIGNED NULL DEFAULT NULL,
   b CHAR(10) NULL DEFAULT NULL,
@@ -281,3 +283,4 @@ select * from t1;
 drop trigger t1_ins;
 drop trigger t1_update;
 drop table t1;
+SET sql_mode = DEFAULT;
diff --git a/mysql-test/suite/vcol/r/update.result b/mysql-test/suite/vcol/r/update.result
index 1dd80ae92b4..0069955f504 100644
--- a/mysql-test/suite/vcol/r/update.result
+++ b/mysql-test/suite/vcol/r/update.result
@@ -56,6 +56,7 @@ a	b	c
 drop table t, t2;
 create table t1 (a int, b int, c int, d int, e int);
 insert t1 values (1,2,3,4,5), (1,2,3,4,5);
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 create table t (a int primary key,
 b int, c blob as (b), index (c(57)),
 d blob, e blob as (d), index (e(57)))
@@ -65,6 +66,7 @@ Warning	1906	The value specified for generated column 'c' in table 't' ignored
 Warning	1906	The value specified for generated column 'e' in table 't' ignored
 Warning	1906	The value specified for generated column 'c' in table 't' ignored
 Warning	1906	The value specified for generated column 'e' in table 't' ignored
+set sql_mode = DEFAULT;
 check table t;
 Table	Op	Msg_type	Msg_text
 test.t	check	status	OK
diff --git a/mysql-test/suite/vcol/r/vcol_keys_myisam.result b/mysql-test/suite/vcol/r/vcol_keys_myisam.result
index 078d6cfff1c..24409d88e83 100644
--- a/mysql-test/suite/vcol/r/vcol_keys_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_keys_myisam.result
@@ -360,6 +360,8 @@ vcol_enum SET('','a','b','c','d','e','f','foo','bar') AS (col_enum) VIRTUAL,
 UNIQUE(pk),
 PRIMARY KEY(pk)
 ) ENGINE=MyISAM;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 ADD INDEX(col_enum,vcol_int);
 ALTER TABLE t1 ADD INDEX(col_year);
+SET sql_mode = DEFAULT;
 DROP TABLE t1;
diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result
index 294b8b1aa28..6f35fab7495 100644
--- a/mysql-test/suite/vcol/r/vcol_misc.result
+++ b/mysql-test/suite/vcol/r/vcol_misc.result
@@ -351,7 +351,9 @@ t1	CREATE TABLE `t1` (
   `b` timestamp GENERATED ALWAYS AS (cast(`a` as datetime)) VIRTUAL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a)),c TIMESTAMP);
+SET sql_mode = DEFAULT;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
diff --git a/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result b/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result
index 0a82f1006e7..dae34569d1b 100644
--- a/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result
@@ -128,6 +128,7 @@ DROP TABLE t1,t2;
 #
 # Examine the number of times triggers are recalculated for updates
 #
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 (
 a INTEGER UNSIGNED NULL DEFAULT NULL,
 b CHAR(10) NULL DEFAULT NULL,
@@ -215,9 +216,11 @@ a	b	c	blob_a	blob_b	blob_c
 drop trigger t1_ins;
 drop trigger t1_update;
 drop table t1;
+SET sql_mode = DEFAULT;
 #
 # Same test, but with virtual keys
 #
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 (
 a INTEGER UNSIGNED NULL DEFAULT NULL,
 b CHAR(10) NULL DEFAULT NULL,
@@ -304,3 +307,4 @@ a	b	c	blob_a	blob_b	blob_c
 drop trigger t1_ins;
 drop trigger t1_update;
 drop table t1;
+SET sql_mode = DEFAULT;
diff --git a/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result b/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result
index edafd474286..5cb02f3911c 100644
--- a/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result
@@ -128,6 +128,7 @@ DROP TABLE t1,t2;
 #
 # Examine the number of times triggers are recalculated for updates
 #
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 (
 a INTEGER UNSIGNED NULL DEFAULT NULL,
 b CHAR(10) NULL DEFAULT NULL,
@@ -215,9 +216,11 @@ a	b	c	blob_a	blob_b	blob_c
 drop trigger t1_ins;
 drop trigger t1_update;
 drop table t1;
+SET sql_mode = DEFAULT;
 #
 # Same test, but with virtual keys
 #
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 (
 a INTEGER UNSIGNED NULL DEFAULT NULL,
 b CHAR(10) NULL DEFAULT NULL,
@@ -304,3 +307,4 @@ a	b	c	blob_a	blob_b	blob_c
 drop trigger t1_ins;
 drop trigger t1_update;
 drop table t1;
+SET sql_mode = DEFAULT;
diff --git a/mysql-test/suite/vcol/t/update.test b/mysql-test/suite/vcol/t/update.test
index 8326afe214f..5899161dced 100644
--- a/mysql-test/suite/vcol/t/update.test
+++ b/mysql-test/suite/vcol/t/update.test
@@ -72,10 +72,12 @@ drop table t, t2;
 #
 create table t1 (a int, b int, c int, d int, e int);
 insert t1 values (1,2,3,4,5), (1,2,3,4,5);
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 create table t (a int primary key,
   b int, c blob as (b), index (c(57)),
   d blob, e blob as (d), index (e(57)))
   replace select * from t1;
+set sql_mode = DEFAULT;
 check table t; select * from t;
 update t set a=10, b=1, d=1;
 check table t; select * from t;
diff --git a/mysql-test/suite/vcol/t/vcol_keys_myisam.test b/mysql-test/suite/vcol/t/vcol_keys_myisam.test
index 049037216dc..043323e0ce6 100644
--- a/mysql-test/suite/vcol/t/vcol_keys_myisam.test
+++ b/mysql-test/suite/vcol/t/vcol_keys_myisam.test
@@ -243,7 +243,9 @@ INSERT INTO t1 (col_date,col_datetime,col_int,col_varchar,col_timestamp,col_bit,
 ;
 --enable_query_log
 --disable_warnings
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 ADD INDEX(col_enum,vcol_int);
 ALTER TABLE t1 ADD INDEX(col_year);
+SET sql_mode = DEFAULT;
 --enable_warnings
 DROP TABLE t1;
diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test
index 2a69b677119..6543c030df4 100644
--- a/mysql-test/suite/vcol/t/vcol_misc.test
+++ b/mysql-test/suite/vcol/t/vcol_misc.test
@@ -323,7 +323,9 @@ DROP TABLE t1;
 # Make sure that if the first TIMESTAMP column appears to be virtual,
 # then no further promotion is done, so the next TIMESTAMP column "c" does not
 # get the "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" attributes.
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a)),c TIMESTAMP);
+SET sql_mode = DEFAULT;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index 595eb962dc3..aa537d3596b 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -2846,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);
 
@@ -2876,6 +2876,7 @@ SHOW WARNINGS;
 DROP TABLE t1;
 DROP TABLE t2;
 DROP PROCEDURE p1;
+SET sql_mode = DEFAULT;
 
 --echo
 --echo ###################################################################
@@ -3559,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.
 
@@ -3586,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),
@@ -3666,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),
@@ -3689,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),
@@ -3724,6 +3725,7 @@ DROP PROCEDURE IF EXISTS p2;
 
 delimiter |;
 
+SET sql_mode = '';
 CREATE PROCEDURE p1()
 BEGIN
   DECLARE var1 INTEGER DEFAULT 'string';
@@ -3733,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..ccdc670c2bc 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 sql_mode = ''|
 create procedure bug10100p(prm int, inout res int)
 begin
   set res = res * prm;
@@ -112,6 +113,7 @@ begin
   call bug10100p(prm, res);
   select res;
 end|
+set sql_mode = default|
 
 # a procedure which use tables and recursion
 create table t3 (a 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,