MDEV-6066: Merge new defaults from 5.6 and 5.7 (defaults changed, QC can be stopped with no-zero size)

author: Oleksandr Byelkin <sanja@mariadb.com> 2015-08-11 18:45:38 +0200
committer: Sergei Golubchik <serg@mariadb.org> 2015-09-04 10:33:56 +0200
commit: e3982cead235e9becb1abdbf0e73876c8a6e6b28 (patch)
tree: e02c8ac4c2a978aa12f1f9fdf19ec5303f7e9341 /mysql-test/r
parent: 21daa7b9298d31ab1c6ddd1159dba29acea8d868 (diff)
download: mariadb-git-e3982cead235e9becb1abdbf0e73876c8a6e6b28.tar.gz
134 files changed, 499 insertions, 279 deletions
diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result
index 0f747068ebb..f77db650866 100644
--- a/mysql-test/r/analyze_format_json.result
+++ b/mysql-test/r/analyze_format_json.result
@@ -143,7 +143,7 @@ ANALYZE
         "attached_condition": "(tbl2.b < 60)"
       },
       "buffer_type": "flat",
-      "buffer_size": "128Kb",
+      "buffer_size": "256Kb",
       "join_type": "BNL",
       "r_filtered": 100
     }
@@ -181,7 +181,7 @@ ANALYZE
         "attached_condition": "(tbl2.b < 60)"
       },
       "buffer_type": "flat",
-      "buffer_size": "128Kb",
+      "buffer_size": "256Kb",
       "join_type": "BNL",
       "attached_condition": "(tbl1.c > tbl2.c)",
       "r_filtered": 15.833
diff --git a/mysql-test/r/analyze_stmt_orderby.result b/mysql-test/r/analyze_stmt_orderby.result
index 86259a78b11..be1f01a2a52 100644
--- a/mysql-test/r/analyze_stmt_orderby.result
+++ b/mysql-test/r/analyze_stmt_orderby.result
@@ -422,7 +422,7 @@ ANALYZE
             "r_filtered": 100
           },
           "buffer_type": "flat",
-          "buffer_size": "128Kb",
+          "buffer_size": "256Kb",
           "join_type": "BNL",
           "attached_condition": "(t3.a = t0.a)",
           "r_filtered": 10
@@ -491,7 +491,7 @@ ANALYZE
                 "r_filtered": 100
               },
               "buffer_type": "flat",
-              "buffer_size": "128Kb",
+              "buffer_size": "256Kb",
               "join_type": "BNL",
               "attached_condition": "(t5.a = t6.a)",
               "r_filtered": 21.429
@@ -528,7 +528,7 @@ EXPLAIN
             "filtered": 100
           },
           "buffer_type": "flat",
-          "buffer_size": "128Kb",
+          "buffer_size": "256Kb",
           "join_type": "BNL",
           "attached_condition": "(t5.a = t6.a)"
         }
diff --git a/mysql-test/r/analyze_stmt_privileges.result b/mysql-test/r/analyze_stmt_privileges.result
index d382b0b3bb6..d54b5b37863 100644
--- a/mysql-test/r/analyze_stmt_privileges.result
+++ b/mysql-test/r/analyze_stmt_privileges.result
@@ -7,6 +7,7 @@ use db;
 create table t1 (i int, c varchar(8));
 insert into t1 values (1,'foo'),(2,'bar'),(3,'baz'),(4,'qux');
 create view v1 as select * from t1 where i > 1;
+CREATE USER u1@localhost;
 grant ALL on db.v1 to u1@localhost;
 connect  con1,localhost,u1,,;
 select * from db.t1;
diff --git a/mysql-test/r/analyze_stmt_privileges2.result b/mysql-test/r/analyze_stmt_privileges2.result
index 3dded4bc7cc..892791dd8f8 100644
--- a/mysql-test/r/analyze_stmt_privileges2.result
+++ b/mysql-test/r/analyze_stmt_privileges2.result
@@ -1,3 +1,5 @@
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 CREATE DATABASE privtest_db;
 CREATE TABLE privtest_db.t1 (a INT, b VARCHAR(3)) ENGINE=MyISAM;
 CREATE TABLE privtest_db.t2 (a INT, b VARCHAR(3)) ENGINE=MyISAM;
@@ -5232,3 +5234,4 @@ connection default;
 DROP USER 'privtest'@localhost;
 USE test;
 DROP DATABASE privtest_db;
+set GLOBAL sql_mode=default;
diff --git a/mysql-test/r/blackhole_plugin.result b/mysql-test/r/blackhole_plugin.result
index dd1b95ab0d9..2884441056a 100644
--- a/mysql-test/r/blackhole_plugin.result
+++ b/mysql-test/r/blackhole_plugin.result
@@ -1,7 +1,9 @@
+set sql_mode="";
 CREATE TABLE t1(a int) ENGINE=BLACKHOLE;
 Warnings:
 Warning	1286	Unknown storage engine 'BLACKHOLE'
 Warning	1266	Using storage engine MyISAM for table 't1'
+set sql_mode=default;
 DROP TABLE t1;
 INSTALL PLUGIN blackhole SONAME 'ha_blackhole.so';
 INSTALL PLUGIN BLACKHOLE SONAME 'ha_blackhole.so';
diff --git a/mysql-test/r/cache_innodb.result b/mysql-test/r/cache_innodb.result
index 85eef0b3418..971750e3da2 100644
--- a/mysql-test/r/cache_innodb.result
+++ b/mysql-test/r/cache_innodb.result
@@ -1,3 +1,5 @@
+SET global query_cache_type=ON;
+SET local query_cache_type=ON;
 SET SESSION STORAGE_ENGINE = InnoDB;
 drop table if exists t1,t2,t3;
 set @save_query_cache_size = @@global.query_cache_size;
@@ -221,6 +223,7 @@ Variable_name	Value
 Qcache_hits	1
 set @@global.query_cache_size = @save_query_cache_size;
 drop table t2;
+SET global query_cache_type=default;
 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 BEGIN;
 INSERT INTO t1 VALUES(1);
diff --git a/mysql-test/r/change_user.result b/mysql-test/r/change_user.result
index 18c53a5e22b..1de97ed02e6 100644
--- a/mysql-test/r/change_user.result
+++ b/mysql-test/r/change_user.result
@@ -1,6 +1,10 @@
+set global secure_auth=0;
+create user test_nopw;
 grant select on test.* to test_nopw;
-grant select on test.* to test_oldpw identified by password "09301740536db389";
-grant select on test.* to test_newpw identified by "newpw";
+create user test_oldpw identified by password "09301740536db389";
+grant select on test.* to test_oldpw;
+create user test_newpw identified by "newpw";
+grant select on test.* to test_newpw;
 select concat('<', user(), '>'), concat('<', current_user(), '>'), database();
 concat('<', user(), '>')	concat('<', current_user(), '>')	database()
 <root@localhost>	<root@localhost>	test
@@ -85,3 +89,4 @@ IS_USED_LOCK('bug31418')
 NULL
 FLUSH STATUS;
 Value of com_select did not change
+set global secure_auth=default;
diff --git a/mysql-test/r/compound.result b/mysql-test/r/compound.result
index dc1f33147a0..92d3226ec00 100644
--- a/mysql-test/r/compound.result
+++ b/mysql-test/r/compound.result
@@ -155,7 +155,7 @@ drop function fn|
 drop table t1|
 begin not atomic select @@sql_mode; end|
 @@sql_mode
-
+NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 create table t1 (a int)|
 select a from t1 having a > 1|
 a
diff --git a/mysql-test/r/concurrent_innodb_safelog.result b/mysql-test/r/concurrent_innodb_safelog.result
index 24a84afb9ce..03d20f40be3 100644
--- a/mysql-test/r/concurrent_innodb_safelog.result
+++ b/mysql-test/r/concurrent_innodb_safelog.result
@@ -1,4 +1,5 @@
 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SET SQL_MODE="";
 SELECT @@global.tx_isolation;
 @@global.tx_isolation
 REPEATABLE-READ
@@ -790,3 +791,4 @@ eta	tipo	c
 ** connection default
 drop table t1;
 drop user mysqltest@localhost;
+SET SQL_MODE=default;
diff --git a/mysql-test/r/concurrent_innodb_unsafelog.result b/mysql-test/r/concurrent_innodb_unsafelog.result
index 35fc2d89cfe..4a140d9e610 100644
--- a/mysql-test/r/concurrent_innodb_unsafelog.result
+++ b/mysql-test/r/concurrent_innodb_unsafelog.result
@@ -1,4 +1,5 @@
 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SET SQL_MODE="";
 SELECT @@global.tx_isolation;
 @@global.tx_isolation
 REPEATABLE-READ
@@ -786,3 +787,4 @@ eta	tipo	c
 ** connection default
 drop table t1;
 drop user mysqltest@localhost;
+SET SQL_MODE=default;
diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result
index 778219f6ed9..315aea0ef46 100644
--- a/mysql-test/r/connect.result
+++ b/mysql-test/r/connect.result
@@ -1,3 +1,4 @@
+SET global secure_auth=0;
 drop table if exists t1,t2;
 show tables;
 Tables_in_mysql
@@ -143,6 +144,7 @@ drop table t1;
 # -- Bug#33507: Event scheduler creates more threads than max_connections
 # -- which results in user lockout.
 
+CREATE USER mysqltest_u1@localhost;
 GRANT USAGE ON *.* TO mysqltest_u1@localhost;
 
 SET GLOBAL max_connections = 3;
@@ -313,3 +315,4 @@ test
 test
 test
 drop procedure p1;
+SET global secure_auth=default;
diff --git a/mysql-test/r/create_drop_function.result b/mysql-test/r/create_drop_function.result
index f358c24461a..e9db636ab87 100644
--- a/mysql-test/r/create_drop_function.result
+++ b/mysql-test/r/create_drop_function.result
@@ -4,7 +4,7 @@ RETURNS CHAR(100)
 RETURN CONCAT('Hello, ', str, '!');
 SELECT * FROM mysql.proc WHERE name like 'f1';
 db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment	character_set_client	collation_connection	db_collation	body_utf8
-test	f1	FUNCTION	f1	SQL	CONTAINS_SQL	NO	DEFINER	str char(20)	char(100) CHARSET latin1	RETURN CONCAT('Hello, ', str, '!')	root@localhost	2014-09-30 08:00:00	2014-09-30 08:00:00			latin1	latin1_swedish_ci	latin1_swedish_ci	RETURN CONCAT('Hello, ', str, '!')
+test	f1	FUNCTION	f1	SQL	CONTAINS_SQL	NO	DEFINER	str char(20)	char(100) CHARSET latin1	RETURN CONCAT('Hello, ', str, '!')	root@localhost	2014-09-30 08:00:00	2014-09-30 08:00:00	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION		latin1	latin1_swedish_ci	latin1_swedish_ci	RETURN CONCAT('Hello, ', str, '!')
 SELECT f1('world');
 f1('world')
 Hello, world!
diff --git a/mysql-test/r/create_or_replace_permission.result b/mysql-test/r/create_or_replace_permission.result
index d9c5a6f1d64..f662bf1bfd8 100644
--- a/mysql-test/r/create_or_replace_permission.result
+++ b/mysql-test/r/create_or_replace_permission.result
@@ -1,3 +1,5 @@
+set local sql_mode="";
+set global sql_mode="";
 #
 # Tests for checking permission denied on CREATE OR REPLACE if DROP
 # access is revoked
@@ -48,3 +50,4 @@ Warnings:
 Note	1008	Can't drop database 'db2'; database doesn't exist
 DROP DATABASE db1;
 DROP USER mysqltest_1@localhost;
+set global sql_mode=default;
diff --git a/mysql-test/r/ctype_latin2_ch.result b/mysql-test/r/ctype_latin2_ch.result
index 6e73affeef6..da5c8076fca 100644
--- a/mysql-test/r/ctype_latin2_ch.result
+++ b/mysql-test/r/ctype_latin2_ch.result
@@ -333,6 +333,7 @@ Y
 z
 Z
 DROP TABLE t1;
+set sql_mode="";
 CREATE TABLE t1 ENGINE=INNODB AS SELECT repeat('a', 5) AS s1 LIMIT 0;
 INSERT INTO t1 VALUES ('x'),('y'),('z'),('X'),('Y'),('Z');
 SELECT * FROM t1 GROUP BY s1;
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index ad08359bd9c..4da9e95a806 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -1184,7 +1184,9 @@ hex(a)
 005B
 803D
 drop table t1;
+set sql_mode="";
 create table t1(f1 varchar(5) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL) engine=InnoDB;
+set sql_mode=default;
 insert into t1 values('a');
 create index t1f1 on t1(f1);
 select f1 from t1 where f1 like 'a%';
diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result
index 4deabb7513f..61541ec7678 100644
--- a/mysql-test/r/ctype_ujis.result
+++ b/mysql-test/r/ctype_ujis.result
@@ -126,6 +126,7 @@ Field	Type	Null	Key	Default	Extra
 a	char(1)	NO			
 b	enum('��','��')	YES		NULL	
 DROP TABLE t1;
+set sql_mode="";
 CREATE TABLE t1
 (
 a INTEGER NOT NULL,
@@ -133,6 +134,7 @@ b VARCHAR(50) NOT NULL DEFAULT '',
 PRIMARY KEY  (a),
 KEY b (b(10))
 ) ENGINE=InnoDB CHARACTER SET 'ujis' COLLATE 'ujis_japanese_ci';
+set sql_mode=default;
 INSERT INTO t1 (a, b) VALUES (0, 'aaabbbcccddd');
 INSERT INTO t1 (a, b) VALUES (1, 'eeefffggghhh');
 INSERT INTO t1 (a, b) VALUES (2, 'iiijjjkkkl');
diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result
index 288aefaf7ce..bfcd65044d4 100644
--- a/mysql-test/r/ctype_utf16.result
+++ b/mysql-test/r/ctype_utf16.result
@@ -1549,7 +1549,7 @@ SELECT space(date_add(101, INTERVAL CHAR('1' USING utf16) hour_second));
 space(date_add(101, INTERVAL CHAR('1' USING utf16) hour_second))
 NULL
 Warnings:
-Warning	1301	Result of space() was larger than max_allowed_packet (1048576) - truncated
+Warning	1301	Result of space() was larger than max_allowed_packet (4194304) - truncated
 #
 # Bug#11750518 41090: ORDER BY TRUNCATES GROUP_CONCAT RESULT
 #
diff --git a/mysql-test/r/ddl_i18n_koi8r.result b/mysql-test/r/ddl_i18n_koi8r.result
index a57b1369d24..d81b6af0aeb 100644
--- a/mysql-test/r/ddl_i18n_koi8r.result
+++ b/mysql-test/r/ddl_i18n_koi8r.result
@@ -1,3 +1,4 @@
+set sql_mode="";
 set names koi8r;
 
 -------------------------------------------------------------------
diff --git a/mysql-test/r/ddl_i18n_utf8.result b/mysql-test/r/ddl_i18n_utf8.result
index 5409e58e774..5921a7f538d 100644
--- a/mysql-test/r/ddl_i18n_utf8.result
+++ b/mysql-test/r/ddl_i18n_utf8.result
@@ -1,3 +1,4 @@
+set sql_mode="";
 set names utf8;
 
 -------------------------------------------------------------------
diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result
index 2ec961a4ce9..685b534b808 100644
--- a/mysql-test/r/default.result
+++ b/mysql-test/r/default.result
@@ -1,5 +1,6 @@
 drop table if exists t1,t2,t3,t4,t5,t6;
 drop database if exists mysqltest;
+set sql_mode="";
 CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ',
 b varchar(1) binary NOT NULL DEFAULT ' ',
 c varchar(4) binary NOT NULL DEFAULT '0000',
@@ -29,6 +30,7 @@ z varchar(20) binary NOT NULL DEFAULT ' ',
 a1 varchar(30) binary NOT NULL DEFAULT ' ',
 b1 tinyblob NULL)
 ENGINE=InnoDB DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin;
+set sql_mode=default;
 INSERT into t1 (b) values ('1');
 SHOW WARNINGS;
 Level	Code	Message
diff --git a/mysql-test/r/delete_returning_grant.result b/mysql-test/r/delete_returning_grant.result
index c15d3627a39..bdd171940ba 100644
--- a/mysql-test/r/delete_returning_grant.result
+++ b/mysql-test/r/delete_returning_grant.result
@@ -1,3 +1,5 @@
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 CREATE TABLE t1 (a int(11), b varchar(32));
 INSERT INTO t1 VALUES (7,'ggggggg'),(1,'a'),(3,'ccc'),(4,'dddd'),(1,'A'),
 (2,'BB'),(4,'DDDD'),(5,'EEEEE'),(7,'GGGGGGG'),(2,'bb');
@@ -67,3 +69,4 @@ DROP DATABASE mysqltest;
 DROP USER mysqltest_1@localhost;
 DROP VIEW v1;
 DROP TABLE t1;
+set GLOBAL sql_mode=default;
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index fa0a69a487d..639942f0da9 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2262,10 +2262,7 @@ CREATE TABLE IF NOT EXISTS `galleries` (
 `year` int(11) DEFAULT NULL, 
 PRIMARY KEY (`id`), 
 UNIQUE KEY `name` (`name`) 
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 'galleries'
+) DEFAULT CHARSET=utf8;
 CREATE TABLE IF NOT EXISTS `pictures` ( 
 `id` int(11) NOT NULL AUTO_INCREMENT, 
 `name` varchar(100) NOT NULL, 
@@ -2278,10 +2275,7 @@ CREATE TABLE IF NOT EXISTS `pictures` (
 `type` int(11) NOT NULL, 
 PRIMARY KEY (`id`), 
 KEY `gallery_id` (`gallery_id`) 
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 'pictures'
+) DEFAULT CHARSET=utf8 ;
 ALTER TABLE `pictures` 
 ADD CONSTRAINT `pictures_ibfk_1` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`);
 INSERT INTO `galleries` (`id`, `name`, `year`) VALUES 
diff --git a/mysql-test/r/enforce_storage_engine.result b/mysql-test/r/enforce_storage_engine.result
index cef03cb6b87..4a0c3317765 100644
--- a/mysql-test/r/enforce_storage_engine.result
+++ b/mysql-test/r/enforce_storage_engine.result
@@ -1,3 +1,5 @@
+set local sql_mode="";
+set global sql_mode="";
 drop table if exists t1;
 SET SESSION enforce_storage_engine=MyISAM;
 select @@session.enforce_storage_engine;
@@ -117,5 +119,6 @@ t1	CREATE TABLE `t1` (
   PRIMARY KEY (`c1`)
 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
 DROP TABLE t1;
+set global sql_mode=default;
 SET SESSION enforce_storage_engine=NULL;
 SET GLOBAL enforce_storage_engine=NULL;
diff --git a/mysql-test/r/enforce_storage_engine_opt.result b/mysql-test/r/enforce_storage_engine_opt.result
index a28461ef091..1b03aa281d9 100644
--- a/mysql-test/r/enforce_storage_engine_opt.result
+++ b/mysql-test/r/enforce_storage_engine_opt.result
@@ -1,3 +1,4 @@
+SET SQL_MODE="";
 create table t1 (c1 int primary key auto_increment, c2 varchar(10)) engine=memory;
 Warnings:
 Note	1266	Using storage engine MyISAM for table 't1'
diff --git a/mysql-test/r/events_1.result b/mysql-test/r/events_1.result
index 5a64cf14ef5..78115497fc6 100644
--- a/mysql-test/r/events_1.result
+++ b/mysql-test/r/events_1.result
@@ -1,3 +1,4 @@
+set sql_mode="";
 call mtr.add_suppression("Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted");
 drop database if exists events_test;
 drop database if exists db_x;
diff --git a/mysql-test/r/events_2.result b/mysql-test/r/events_2.result
index 3b7f3566cab..9a1f9d954e8 100644
--- a/mysql-test/r/events_2.result
+++ b/mysql-test/r/events_2.result
@@ -1,3 +1,4 @@
+set sql_mode="";
 drop database if exists events_test;
 create database events_test;
 use events_test;
diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result
index 7ce3c740cd4..3de6a6e48fc 100644
--- a/mysql-test/r/events_bugs.result
+++ b/mysql-test/r/events_bugs.result
@@ -1,3 +1,4 @@
+SET SQL_MODE="";
 drop database if exists events_test;
 drop database if exists mysqltest_db1;
 drop database if exists mysqltest_db2;
diff --git a/mysql-test/r/events_trans.result b/mysql-test/r/events_trans.result
index 084587079ba..873f0441089 100644
--- a/mysql-test/r/events_trans.result
+++ b/mysql-test/r/events_trans.result
@@ -1,3 +1,4 @@
+set sql_mode="";
 drop database if exists events_test;
 drop database if exists mysqltest_no_such_database;
 create database events_test;
diff --git a/mysql-test/r/events_trans_notembedded.result b/mysql-test/r/events_trans_notembedded.result
index 1e3dfffe232..fb68bc9ceab 100644
--- a/mysql-test/r/events_trans_notembedded.result
+++ b/mysql-test/r/events_trans_notembedded.result
@@ -2,6 +2,7 @@ drop database if exists events_test;
 drop database if exists mysqltest_db2;
 create database events_test;
 use events_test;
+create user mysqltest_user1@localhost;
 grant create, insert, select, delete on mysqltest_db2.*
 to mysqltest_user1@localhost;
 create database mysqltest_db2;
diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result
index f81a62ef23b..0507cee62b4 100644
--- a/mysql-test/r/explain_json.result
+++ b/mysql-test/r/explain_json.result
@@ -371,7 +371,7 @@ EXPLAIN
         "attached_condition": "(tbl2.b < 5)"
       },
       "buffer_type": "flat",
-      "buffer_size": "128Kb",
+      "buffer_size": "256Kb",
       "join_type": "BNL",
       "attached_condition": "(tbl2.a = tbl1.a)"
     }
@@ -638,7 +638,7 @@ EXPLAIN
         "filtered": 100
       },
       "buffer_type": "flat",
-      "buffer_size": "128Kb",
+      "buffer_size": "256Kb",
       "join_type": "BNL"
     }
   }
@@ -672,7 +672,7 @@ EXPLAIN
         "first_match": "t2"
       },
       "buffer_type": "flat",
-      "buffer_size": "128Kb",
+      "buffer_size": "256Kb",
       "join_type": "BNL",
       "attached_condition": "((t1.b = t2.b) and (t1.a = t2.a))"
     }
@@ -709,7 +709,7 @@ EXPLAIN
           "filtered": 100
         },
         "buffer_type": "flat",
-        "buffer_size": "128Kb",
+        "buffer_size": "256Kb",
         "join_type": "BNL",
         "attached_condition": "((t1.b = t2.b) and (t1.a = t2.a))"
       }
@@ -822,7 +822,7 @@ EXPLAIN
               "filtered": 100
             },
             "buffer_type": "flat",
-            "buffer_size": "128Kb",
+            "buffer_size": "256Kb",
             "join_type": "BNL",
             "attached_condition": "((t2.b <> outer_t1.a) and trigcond(((<cache>(outer_t1.a) = t1.a) or isnull(t1.a))))"
           }
@@ -873,7 +873,7 @@ EXPLAIN
               "filtered": 100
             },
             "buffer_type": "flat",
-            "buffer_size": "128Kb",
+            "buffer_size": "256Kb",
             "join_type": "BNL",
             "attached_condition": "(tbl2.b = tbl1.b)"
           }
diff --git a/mysql-test/r/features.result b/mysql-test/r/features.result
index 04e0df98ec7..52650d118b3 100644
--- a/mysql-test/r/features.result
+++ b/mysql-test/r/features.result
@@ -1,4 +1,5 @@
 drop table if exists t1;
+set sql_mode="";
 flush status;
 show status like "feature%";
 Variable_name	Value
diff --git a/mysql-test/r/fix_priv_tables.result b/mysql-test/r/fix_priv_tables.result
index e54330a5520..dea1ccdbd18 100644
--- a/mysql-test/r/fix_priv_tables.result
+++ b/mysql-test/r/fix_priv_tables.result
@@ -1,4 +1,5 @@
 drop table if exists t1,t1aa,t2aa;
+set sql_mode="";
 DROP DATABASE IF EXISTS testdb;
 CREATE DATABASE testdb;
 CREATE TABLE testdb.t1 (
diff --git a/mysql-test/r/flush-innodb-notembedded.result b/mysql-test/r/flush-innodb-notembedded.result
index 817d57d9d79..911abfe6daf 100644
--- a/mysql-test/r/flush-innodb-notembedded.result
+++ b/mysql-test/r/flush-innodb-notembedded.result
@@ -1,3 +1,4 @@
+set sql_mode="";
 # Test 7: Check privileges required.
 #
 CREATE DATABASE db1;
diff --git a/mysql-test/r/flush_read_lock.result b/mysql-test/r/flush_read_lock.result
index c2e8531d01f..260edf8e51d 100644
--- a/mysql-test/r/flush_read_lock.result
+++ b/mysql-test/r/flush_read_lock.result
@@ -1,3 +1,5 @@
+set global sql_mode="";
+set local sql_mode="";
 # FTWRL takes two global metadata locks -- a global shared
 # metadata lock and the commit blocker lock.
 # The first lock prevents DDL from taking place.
@@ -1667,3 +1669,4 @@ drop database `#mysql50#mysqltest-2`;
 drop database mysqltest1;
 drop temporary tables t1_temp, t2_temp;
 drop tables t1_base, t2_base, t3_trans;
+set global sql_mode=default;
diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result
index 9fde006b377..4763d878cc6 100644
--- a/mysql-test/r/func_compress.result
+++ b/mysql-test/r/func_compress.result
@@ -1,3 +1,4 @@
+set global max_allowed_packet=1048576;
 select @test_compress_string:='string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ';
 @test_compress_string:='string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa '
 string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 
@@ -152,6 +153,7 @@ UNCOMPRESS(CAST(0 AS BINARY(5)))
 NULL
 Warnings:
 Warning	1259	ZLIB: Input data corrupted
+set global max_allowed_packet=default;
 #
 # End of 5.5 tests
 #
diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result
index 62814b45675..73e486972ab 100644
--- a/mysql-test/r/func_math.result
+++ b/mysql-test/r/func_math.result
@@ -214,7 +214,9 @@ CAST(RAND(2) * 1000 AS UNSIGNED)	CAST(RAND(?) * 1000 AS UNSIGNED)
 122	122
 645	645
 DROP TABLE t1;
+set sql_mode="";
 create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8;
+set sql_mode=default;
 insert into t1 values ('http://www.foo.com/', now());
 select a from t1 where a='http://www.foo.com/' order by abs(timediff(ts, 0));
 a
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index 62138819b0a..a9548aa70e9 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -334,10 +334,7 @@ CREATE TABLE t1 (a char(2) not null );
 INSERT INTO t1 VALUES (4),(7),(1);
 set @optimizer_switch_save= @@optimizer_switch;
 set optimizer_switch='materialization=off';
-CREATE TABLE tv (e char(2) not null ) engine=mysql;
-Warnings:
-Warning	1286	Unknown storage engine 'mysql'
-Warning	1266	Using storage engine MyISAM for table 'tv'
+CREATE TABLE tv (e char(2) not null );
 INSERT INTO tv VALUES (1);
 CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM tv;
 CREATE ALGORITHM=MERGE VIEW vm AS SELECT * FROM tv;
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index aa3de4fc915..3ebb5f2d3eb 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1,4 +1,5 @@
 drop table if exists t1,t2;
+set global max_allowed_packet=1048576;
 set names latin1;
 select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo';
 hello	'hello'	""hello""	'h'e'l'l'o'	hel"lo	hel'lo
@@ -4530,6 +4531,7 @@ SET NAMES latin2;
 EXECUTE stmt;
 COLLATION(space(2))
 latin2_general_ci
+set global max_allowed_packet=default;
 #
 # End of 5.6 tests
 #
diff --git a/mysql-test/r/func_weight_string.result b/mysql-test/r/func_weight_string.result
index 91ca9f68076..f28e5451af5 100644
--- a/mysql-test/r/func_weight_string.result
+++ b/mysql-test/r/func_weight_string.result
@@ -1,4 +1,5 @@
 drop table if exists t1;
+set global max_allowed_packet=1048576;
 set names latin1;
 select hex(weight_string(0x010203));
 hex(weight_string(0x010203))
@@ -90,3 +91,4 @@ NULL
 Warnings:
 Warning	1301	Result of cast_as_binary() was larger than max_allowed_packet (1048576) - truncated
 Warning	1301	Result of weight_string() was larger than max_allowed_packet (1048576) - truncated
+set global max_allowed_packet=default;
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index 2d451de1302..0d8f30d125f 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -809,7 +809,7 @@ drop procedure if exists fn3;
 create function fn3 () returns point deterministic return GeomFromText("point(1 1)");
 show create function fn3;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-fn3		CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS point
+fn3	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS point
     DETERMINISTIC
 return GeomFromText("point(1 1)")	latin1	latin1_swedish_ci	latin1_swedish_ci
 select astext(fn3());
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index 499251ec3fc..3020c7deba7 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -1,3 +1,5 @@
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
 SET GLOBAL log_bin_trust_function_creators = 1;
 drop table if exists t1;
@@ -2580,3 +2582,4 @@ Grants for mysqltest_u1@%
 GRANT USAGE ON *.* TO 'mysqltest_u1'@'%'
 drop database mysqltest_db1;
 drop user mysqltest_u1;
+set GLOBAL sql_mode=default;
diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result
index a1cee5741e4..bd14f21010b 100644
--- a/mysql-test/r/grant2.result
+++ b/mysql-test/r/grant2.result
@@ -1,3 +1,5 @@
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 SET NAMES binary;
 drop database if exists mysqltest;
 drop database if exists mysqltest_1;
@@ -845,3 +847,4 @@ DROP USER mysqltest_u2@localhost;
 DROP USER mysqltest_u3@localhost;
 DROP USER mysqltest_u4@localhost;
 DROP USER mysqltest_u5@localhost;
+set GLOBAL sql_mode=default;
diff --git a/mysql-test/r/grant3.result b/mysql-test/r/grant3.result
index fd51a83d4b2..d1722fc8ea9 100644
--- a/mysql-test/r/grant3.result
+++ b/mysql-test/r/grant3.result
@@ -1,3 +1,5 @@
+set global sql_mode="";
+set local sql_mode="";
 SET NAMES binary;
 drop table if exists t1;
 delete from mysql.user where user like 'mysqltest\_%';
@@ -177,4 +179,5 @@ SELECT b FROM temp.t1;
 ERROR 42000: SELECT command denied to user 'user2'@'localhost' for column 'b' in table 't1'
 DROP USER 'user2'@'%';
 DROP DATABASE temp;
+set global sql_mode=default;
 End of 5.0 tests
diff --git a/mysql-test/r/grant4.result b/mysql-test/r/grant4.result
index d96802d105c..5a0032cd338 100644
--- a/mysql-test/r/grant4.result
+++ b/mysql-test/r/grant4.result
@@ -1,4 +1,6 @@
 drop database if exists mysqltest_db1;
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 create database mysqltest_db1;
 use mysqltest_db1;
 create table t_column_priv_only (a int, b int);
@@ -212,3 +214,4 @@ create user foo3 identified via mysql_old_password using '00';
 ERROR HY000: Password hash should be a 16-digit hexadecimal number
 create user foo4 identified via mysql_old_password using '11111111111111111111111111111111111111111';
 ERROR HY000: Password hash should be a 16-digit hexadecimal number
+set GLOBAL sql_mode=default;
diff --git a/mysql-test/r/grant_4332.result b/mysql-test/r/grant_4332.result
index 5a475bed6d8..93c0a24d164 100644
--- a/mysql-test/r/grant_4332.result
+++ b/mysql-test/r/grant_4332.result
@@ -1,3 +1,5 @@
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 alter table mysql.user modify User char(16) binary not null default '';
 alter table mysql.db modify User char(16) binary not null default '';
 alter table mysql.tables_priv modify User char(16) binary not null default '';
@@ -30,3 +32,4 @@ Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is
 def					user()	253	141	14	N	1	31	8
 user()
 root@localhost
+set GLOBAL sql_mode=default;
diff --git a/mysql-test/r/grant_cache_no_prot.result b/mysql-test/r/grant_cache_no_prot.result
index 777eb10cda6..34abd96f026 100644
--- a/mysql-test/r/grant_cache_no_prot.result
+++ b/mysql-test/r/grant_cache_no_prot.result
@@ -1,5 +1,9 @@
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
 drop database if exists mysqltest;
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
 set GLOBAL query_cache_size=1355776;
 reset query cache;
 flush status;
@@ -219,3 +223,7 @@ flush privileges;
 drop table test.t1,mysqltest.t1,mysqltest.t2;
 drop database mysqltest;
 set GLOBAL query_cache_size=default;
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
+set GLOBAL sql_mode=default;
+set GLOBAL query_cache_type=default;
diff --git a/mysql-test/r/grant_cache_ps_prot.result b/mysql-test/r/grant_cache_ps_prot.result
index b504dc4be35..5fe57c7c35d 100644
--- a/mysql-test/r/grant_cache_ps_prot.result
+++ b/mysql-test/r/grant_cache_ps_prot.result
@@ -1,5 +1,9 @@
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
 drop database if exists mysqltest;
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
 set GLOBAL query_cache_size=1355776;
 reset query cache;
 flush status;
@@ -219,3 +223,7 @@ flush privileges;
 drop table test.t1,mysqltest.t1,mysqltest.t2;
 drop database mysqltest;
 set GLOBAL query_cache_size=default;
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
+set GLOBAL sql_mode=default;
+set GLOBAL query_cache_type=default;
diff --git a/mysql-test/r/grant_explain_non_select.result b/mysql-test/r/grant_explain_non_select.result
index 8bce20f7a95..1dbb5b1e3c0 100644
--- a/mysql-test/r/grant_explain_non_select.result
+++ b/mysql-test/r/grant_explain_non_select.result
@@ -1,3 +1,5 @@
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 CREATE DATABASE privtest_db;
 CREATE TABLE privtest_db.t1 (a INT);
 CREATE TABLE privtest_db.t2 (a INT);
@@ -176,3 +178,5 @@ DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a;
 DROP USER 'privtest'@localhost;
 USE test;
 DROP DATABASE privtest_db;
+set GLOBAL sql_mode=default;
+set LOCAL sql_mode=default;
diff --git a/mysql-test/r/grant_lowercase.result b/mysql-test/r/grant_lowercase.result
index e2d50724cec..b449453a47a 100644
--- a/mysql-test/r/grant_lowercase.result
+++ b/mysql-test/r/grant_lowercase.result
@@ -1,3 +1,4 @@
+set sql_mode="";
 grant file on *.* to user1@localhost with grant option;
 grant select on `a%`.* to user1@localhost with grant option;
 grant file on aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa.* to 'user'@'%' identified by 'secret';
diff --git a/mysql-test/r/help.result b/mysql-test/r/help.result
index 16719cc8193..3a266567175 100644
--- a/mysql-test/r/help.result
+++ b/mysql-test/r/help.result
@@ -148,10 +148,12 @@ help 'impossible_category_1';
 source_category_name	name	is_it_category
 impossible_category_1	impossible_function_1	N
 impossible_category_1	impossible_function_2	N
+set sql_mode="";
 alter table mysql.help_relation engine=innodb;
 alter table mysql.help_keyword engine=innodb;
 alter table mysql.help_topic engine=innodb;
 alter table mysql.help_category engine=innodb;
+set sql_mode=default;
 help 'function_of_my_dream';
 name	is_it_category
 help '%possible_f%';
diff --git a/mysql-test/r/host_cache_size_functionality.result b/mysql-test/r/host_cache_size_functionality.result
index e7f9e09fd2f..069cf61bcad 100644
--- a/mysql-test/r/host_cache_size_functionality.result
+++ b/mysql-test/r/host_cache_size_functionality.result
@@ -6,7 +6,7 @@ echo '##'
 ####################################################################
 SELECT COUNT(@@GLOBAL.Host_Cache_Size)
 1 Expected
-set @Default_host_cache_size=128;
+set @Default_host_cache_size=279;
 select @@global.Host_Cache_Size=@Default_host_cache_size;
 @@global.Host_Cache_Size=@Default_host_cache_size
 1
diff --git a/mysql-test/r/implicit_commit.result b/mysql-test/r/implicit_commit.result
index d568d05e7b7..07536ab0719 100644
--- a/mysql-test/r/implicit_commit.result
+++ b/mysql-test/r/implicit_commit.result
@@ -1,3 +1,4 @@
+SET SQL_MODE="";
 SET GLOBAL EVENT_SCHEDULER = OFF;
 SET BINLOG_FORMAT = STATEMENT;
 CREATE DATABASE db1;
diff --git a/mysql-test/r/index_intersect.result b/mysql-test/r/index_intersect.result
index 1337c3fac2d..7a0633d4dc8 100644
--- a/mysql-test/r/index_intersect.result
+++ b/mysql-test/r/index_intersect.result
@@ -1013,10 +1013,7 @@ f4 varchar(32),
 f5 int,
 PRIMARY KEY (f1),
 KEY (f4)
-) ENGINE=InnoDB;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 't1'
+);
 INSERT INTO t1 VALUES 
 (5,'H',1), (9,'g',0), (527,'i',0), (528,'y',1), (529,'S',6),
 (530,'m',7), (531,'b',2), (532,'N',1), (533,'V',NULL), (534,'l',1),
diff --git a/mysql-test/r/index_intersect_innodb.result b/mysql-test/r/index_intersect_innodb.result
index 33f2247e5d1..9c3a501111d 100644
--- a/mysql-test/r/index_intersect_innodb.result
+++ b/mysql-test/r/index_intersect_innodb.result
@@ -1014,7 +1014,7 @@ f4 varchar(32),
 f5 int,
 PRIMARY KEY (f1),
 KEY (f4)
-) ENGINE=InnoDB;
+);
 INSERT INTO t1 VALUES 
 (5,'H',1), (9,'g',0), (527,'i',0), (528,'y',1), (529,'S',6),
 (530,'m',7), (531,'b',2), (532,'N',1), (533,'V',NULL), (534,'l',1),
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 8525cbf9b12..fa5280df2b4 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1,3 +1,5 @@
+set global sql_mode="";
+set local sql_mode="";
 DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
 DROP VIEW IF EXISTS v1;
 show variables where variable_name like "skip_show_database";
@@ -2066,3 +2068,4 @@ Variable_name	Value
 Opened_tables	3
 drop database mysqltest;
 drop database db1;
+set global sql_mode=default;
diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result
index 81a17e7ed3b..c09e04f786a 100644
--- a/mysql-test/r/information_schema_db.result
+++ b/mysql-test/r/information_schema_db.result
@@ -1,3 +1,5 @@
+set local sql_mode="";
+set global sql_mode="";
 drop table if exists t1,t2;
 drop view if exists v1,v2;
 drop function if exists f1;
@@ -216,3 +218,4 @@ show create view testdb_1.v1;
 ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v1'
 drop user mysqltest_1@localhost;
 drop database testdb_1;
+set global sql_mode=default;
diff --git a/mysql-test/r/information_schema_routines.result b/mysql-test/r/information_schema_routines.result
index b8f4fb9b1ef..65e352e3af3 100644
--- a/mysql-test/r/information_schema_routines.result
+++ b/mysql-test/r/information_schema_routines.result
@@ -1,3 +1,5 @@
+set sql_mode="";
+set sql_mode="";
 # ========== routines.1 ==========
 USE INFORMATION_SCHEMA;
 SHOW CREATE TABLE INFORMATION_SCHEMA.ROUTINES;
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index cff78e76664..bc1c24276ed 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -416,11 +416,11 @@ DROP TABLE t1;
 # Bug#59259 "Incorrect rows returned for a correlated subquery
 #            when ICP is on"
 #
-CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
 INSERT INTO t1 VALUES (11,0);
 INSERT INTO t1 VALUES (12,5);
 INSERT INTO t1 VALUES (15,0);
-CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
 INSERT INTO t2 VALUES (11,1);
 INSERT INTO t2 VALUES (12,2);
 INSERT INTO t2 VALUES (15,4);
diff --git a/mysql-test/r/insert_notembedded.result b/mysql-test/r/insert_notembedded.result
index ba3e2221922..41e31508fe0 100644
--- a/mysql-test/r/insert_notembedded.result
+++ b/mysql-test/r/insert_notembedded.result
@@ -1,3 +1,5 @@
+set local sql_mode="";
+set global sql_mode="";
 drop table if exists t1;
 create table t1 (n int);
 create view  v1 as select * from t1;
@@ -125,3 +127,5 @@ a	b
 unlock tables;
 drop table t1;
 set low_priority_updates=default;
+set local sql_mode=default;
+set global sql_mode=default;
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 68ea89949b2..90d305fb384 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -39,7 +39,7 @@ COUNT(*)
 984
 show variables like 'join_buffer_size';
 Variable_name	Value
-join_buffer_size	131072
+join_buffer_size	262144
 set join_cache_level=1;
 show variables like 'join_cache_level';
 Variable_name	Value
@@ -774,7 +774,7 @@ set join_cache_level=default;
 set join_buffer_size=default;
 show variables like 'join_buffer_size';
 Variable_name	Value
-join_buffer_size	131072
+join_buffer_size	262144
 show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	2
@@ -808,7 +808,7 @@ INDEX (Percentage)
 );
 show variables like 'join_buffer_size';
 Variable_name	Value
-join_buffer_size	131072
+join_buffer_size	262144
 set join_cache_level=3;
 show variables like 'join_cache_level';
 Variable_name	Value
@@ -1008,7 +1008,7 @@ Russian Federation	NULL
 Vietnam	NULL
 show variables like 'join_buffer_size';
 Variable_name	Value
-join_buffer_size	131072
+join_buffer_size	262144
 set join_cache_level=4;
 show variables like 'join_cache_level';
 Variable_name	Value
@@ -1267,7 +1267,7 @@ Czech Republic	10278100	NULL	NULL
 DROP INDEX City_Name ON City;
 show variables like 'join_buffer_size';
 Variable_name	Value
-join_buffer_size	131072
+join_buffer_size	262144
 set join_cache_level=5;
 show variables like 'join_cache_level';
 Variable_name	Value
@@ -2688,7 +2688,7 @@ set join_cache_level=default;
 set join_buffer_size=default;
 show variables like 'join_buffer_size';
 Variable_name	Value
-join_buffer_size	131072
+join_buffer_size	262144
 show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	2
@@ -5918,7 +5918,7 @@ select @@join_buffer_space_limit;
 8192
 select @@join_buffer_size;
 @@join_buffer_size
-131072
+262144
 explain select count(*) from t1, t1 t2 where t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	11	
diff --git a/mysql-test/r/kill.result b/mysql-test/r/kill.result
index 091fdfbcc3c..d40c4ee758c 100644
--- a/mysql-test/r/kill.result
+++ b/mysql-test/r/kill.result
@@ -1,3 +1,5 @@
+set local sql_mode="";
+set global sql_mode="";
 SET DEBUG_SYNC = 'RESET';
 DROP TABLE IF EXISTS t1, t2, t3;
 DROP FUNCTION IF EXISTS MY_KILL;
@@ -322,3 +324,4 @@ SLEEP(1000)
 DROP USER u1@localhost;
 SET DEBUG_SYNC = 'RESET';
 DROP FUNCTION MY_KILL;
+set global sql_mode=default;
diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result
index f9e4a88fe95..12960a4f5f6 100644
--- a/mysql-test/r/lock_multi.result
+++ b/mysql-test/r/lock_multi.result
@@ -115,7 +115,9 @@ ERROR HY000: Can't execute the query because you have a conflicting read lock
 UNLOCK TABLES;
 DROP DATABASE mysqltest_1;
 ERROR HY000: Can't drop database 'mysqltest_1'; database doesn't exist
+set sql_mode="";
 create table t1 (f1 int(12) unsigned not null auto_increment, primary key(f1)) engine=innodb;
+set sql_mode=default;
 lock tables t1 write;
 alter table t1 auto_increment=0;
 alter table t1 auto_increment=0;
diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result
index dd2beb87974..ff5055e1ae4 100644
--- a/mysql-test/r/log_tables.result
+++ b/mysql-test/r/log_tables.result
@@ -1,3 +1,4 @@
+SET SQL_MODE="";
 SET @old_general_log_state = @@global.general_log;
 SET @old_log_output=       @@global.log_output;
 SET @old_slow_query_log=   @@global.slow_query_log;
diff --git a/mysql-test/r/lowercase_fs_off.result b/mysql-test/r/lowercase_fs_off.result
index 9b819cf3843..4c4cdb76615 100644
--- a/mysql-test/r/lowercase_fs_off.result
+++ b/mysql-test/r/lowercase_fs_off.result
@@ -1,3 +1,5 @@
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 create database d1;
 grant all on d1.* to 'sample'@'localhost' identified by 'password';
 flush privileges;
@@ -63,3 +65,4 @@ CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1;
 RENAME TABLE t1 TO T1;
 ALTER TABLE T1 RENAME t1;
 DROP TABLE t1;
+set GLOBAL sql_mode=default;
diff --git a/mysql-test/r/lowercase_table_grant.result b/mysql-test/r/lowercase_table_grant.result
index afb54f8c472..009965c0c8e 100644
--- a/mysql-test/r/lowercase_table_grant.result
+++ b/mysql-test/r/lowercase_table_grant.result
@@ -1,4 +1,5 @@
 use mysql;
+set sql_mode="";
 create database MYSQLtest;
 grant all on MySQLtest.* to mysqltest_1@localhost;
 show grants for mysqltest_1@localhost;
diff --git a/mysql-test/r/lowercase_table_qcache.result b/mysql-test/r/lowercase_table_qcache.result
index f8d34e0f592..9d7e1007e4c 100644
--- a/mysql-test/r/lowercase_table_qcache.result
+++ b/mysql-test/r/lowercase_table_qcache.result
@@ -1,4 +1,6 @@
 set GLOBAL query_cache_size=1355776;
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
 drop database if exists MySQLtesT;
 create database MySQLtesT;
 create table MySQLtesT.t1 (a int);
@@ -21,4 +23,5 @@ select * from MySQL.db;
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
 Qcache_queries_in_cache	0
-set GLOBAL query_cache_size=0;
+set GLOBAL query_cache_size=default;
+set GLOBAL query_cache_type=default;
diff --git a/mysql-test/r/max_statement_time.result b/mysql-test/r/max_statement_time.result
index 1ad2de7f7e3..44ee03b813a 100644
--- a/mysql-test/r/max_statement_time.result
+++ b/mysql-test/r/max_statement_time.result
@@ -52,6 +52,7 @@ DROP TABLE t1;
 
 # MAX_STATEMENT_TIME account resource
 
+set statement sql_mode="" for
 GRANT USAGE ON *.* TO user1@localhost WITH MAX_STATEMENT_TIME 1.005;
 # con1
 SELECT @@max_statement_time;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 89aaf48219e..804313af701 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -2270,7 +2270,7 @@ CREATE TABLE t3(a int) ENGINE = MERGE UNION(t1, t2);
 CREATE TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo();
 SHOW CREATE TRIGGER tr1;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-tr1		CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo()	latin1	latin1_swedish_ci	latin1_swedish_ci
+tr1	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo()	latin1	latin1_swedish_ci	latin1_swedish_ci
 DROP TRIGGER tr1;
 DROP TABLE t1, t2, t3;
 #
@@ -2545,11 +2545,14 @@ DROP TABLE t1;
 # Test merge table with too many merge children.
 #
 drop table if exists t_parent;
-set @save_table_definition_cache=@@global.table_definition_cache;
+set @save_table_open_cache=@@global.table_open_cache;
 #
-# Set @@global.table_definition_cache to minimum
+# Set @@global.table_open_cache to minimum
 #
-set @@global.table_definition_cache=400;
+set @@global.table_open_cache=400;
+select @@table_open_cache;
+@@table_open_cache
+400
 set @a=null;
 #
 # Create 400 merge children
@@ -2573,7 +2576,7 @@ deallocate prepare stmt;
 # Cleanup
 #
 drop table t_parent;
-set @@global.table_definition_cache=@save_table_definition_cache;
+set @@global.table_open_cache=@save_table_open_cache;
 DROP DATABASE IF EXISTS mysql_test1;
 CREATE DATABASE mysql_test1;
 CREATE TABLE t1 ... DATA DIRECTORY=... INDEX DIRECTORY=...
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 688184f2f83..b0265119eef 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -422,6 +422,7 @@ create database mysqltest;
 create table mysqltest.t1 (a int, b int, primary key (a));
 create table mysqltest.t2 (a int, b int, primary key (a));
 create table mysqltest.t3 (a int, b int, primary key (a));
+create user mysqltest_1@localhost;
 grant select on mysqltest.* to mysqltest_1@localhost;
 grant update on mysqltest.t1 to mysqltest_1@localhost;
 update t1, t2 set t1.b=1 where t1.a=t2.a;
diff --git a/mysql-test/r/myisam_explain_non_select_all.result b/mysql-test/r/myisam_explain_non_select_all.result
index 688c1ccfec1..fc0f54286a1 100644
--- a/mysql-test/r/myisam_explain_non_select_all.result
+++ b/mysql-test/r/myisam_explain_non_select_all.result
@@ -2785,10 +2785,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 DROP TABLE t1,t2;
 #74
-CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 't1'
+CREATE TABLE t1(a INT PRIMARY KEY);
 INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
 # used key is modified & Using temporary
 #
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 2792d75abb2..e726a50d89b 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -414,17 +414,11 @@ DROP TABLE t1;
 # Bug#59259 "Incorrect rows returned for a correlated subquery
 #            when ICP is on"
 #
-CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 't1'
+CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
 INSERT INTO t1 VALUES (11,0);
 INSERT INTO t1 VALUES (12,5);
 INSERT INTO t1 VALUES (15,0);
-CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 't2'
+CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
 INSERT INTO t2 VALUES (11,1);
 INSERT INTO t2 VALUES (12,2);
 INSERT INTO t2 VALUES (15,4);
diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result
index 82d69557f0d..ca19d2eb98b 100644
--- a/mysql-test/r/mysql.result
+++ b/mysql-test/r/mysql.result
@@ -1,3 +1,5 @@
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 drop table if exists t1,t2,t3;
 create table t1(a int);
 insert into t1 values(1);
@@ -531,5 +533,6 @@ a
 ERROR 1300 (HY000): Invalid utf8 character string: 'test\xF0\x9F\x98\x81 '
 ERROR 1300 (HY000): Invalid binary character string: 'test\xF0\x9F\x98\x81 '
 ERROR 1300 (HY000) at line 2: Invalid utf8 character string: 'test\xF0\x9F\x98\x81'
+set GLOBAL sql_mode=default;
 
 End of tests
diff --git a/mysql-test/r/mysql_comments.result b/mysql-test/r/mysql_comments.result
index 7f1c0b50c5e..4865c7a7af0 100644
--- a/mysql-test/r/mysql_comments.result
+++ b/mysql-test/r/mysql_comments.result
@@ -1,3 +1,4 @@
+set global sql_mode="";
 drop table if exists t1;
 drop function if exists foofct;
 drop procedure if exists empty;
@@ -59,4 +60,5 @@ Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connectio
 t1_bi		CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1\nfor each row\nbegin\n# comment 1a\n-- comment 1b\n/*\n   comment 1c\n*/\n  -- declare some variables here\n  declare b int;\n  declare c float;\n\n  -- do more stuff here\n  -- commented nicely and so on\n\n  -- famous last words ...\n  set NEW.data := 12;\nend	latin1	latin1_swedish_ci	latin1_swedish_ci
 id	data
 trig	12
+set global sql_mode=default;
 End of 5.0 tests
diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result
index 737f8b63783..c34707383b0 100644
--- a/mysql-test/r/mysql_upgrade.result
+++ b/mysql-test/r/mysql_upgrade.result
@@ -1,3 +1,4 @@
+set sql_mode="";
 Run mysql_upgrade once
 Phase 1/6: Checking and upgrading mysql database
 Processing databases
@@ -457,4 +458,5 @@ even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@loca
 even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost
 DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost;
 DROP PROCEDURE test.pr;
+set sql_mode=default;
 End of tests
diff --git a/mysql-test/r/mysqlbinlog-innodb.result b/mysql-test/r/mysqlbinlog-innodb.result
index f0358928c0e..2704be9e3ed 100644
--- a/mysql-test/r/mysqlbinlog-innodb.result
+++ b/mysql-test/r/mysqlbinlog-innodb.result
@@ -30,7 +30,7 @@ use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
@@ -61,7 +61,7 @@ use `foo`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result
index b29fe0b9ec7..94aaee0c574 100644
--- a/mysql-test/r/mysqlbinlog.result
+++ b/mysql-test/r/mysqlbinlog.result
@@ -23,7 +23,7 @@ use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
@@ -103,7 +103,7 @@ use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
@@ -139,7 +139,7 @@ BEGIN
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
@@ -190,7 +190,7 @@ use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
@@ -217,7 +217,7 @@ use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
@@ -297,7 +297,7 @@ use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
@@ -333,7 +333,7 @@ BEGIN
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
@@ -384,7 +384,7 @@ use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
@@ -493,7 +493,7 @@ use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
@@ -545,7 +545,7 @@ use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index 20f8eac7f8f..0c92b7785f1 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -21,9 +21,10 @@ The following options may be given as the first argument:
  --automatic-sp-privileges 
  Creating and dropping stored procedures alters ACLs
  (Defaults to on; use --skip-automatic-sp-privileges to disable.)
- --back-log=#        The number of outstanding connection requests MySQL can
+ --back-log=#        The number of outstanding connection requests MariaDB can
  have. This comes into play when the main MySQL thread
  gets very many connection requests in a very short time
+ (Automatically configured unless set explicitly)
  -b, --basedir=name  Path to installation directory. All paths are usually
  resolved relative to this
  --big-tables        Allow big result sets by saving all temporary sets on
@@ -265,6 +266,7 @@ The following options may be given as the first argument:
  height-balanced, DOUBLE_PREC_HB - double precision
  height-balanced.
  --host-cache-size=# How many host names should be cached to avoid resolving.
+ (Automatically configured unless set explicitly)
  --ignore-builtin-innodb 
  Disable initialization of builtin InnoDB plugin
  --ignore-db-dirs=name 
@@ -895,6 +897,7 @@ The following options may be given as the first argument:
  write privileges to the mysql.user table.
  --secure-auth       Disallow authentication for accounts that have old
  (pre-4.1) passwords
+ (Defaults to on; use --skip-secure-auth to disable.)
  --secure-file-priv=name 
  Limit LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE() to
  files within specified directory
@@ -1043,13 +1046,12 @@ The following options may be given as the first argument:
  (Defaults to on; use --skip-sync-frm to disable.)
  --sync-master-info=# 
  Synchronously flush master info to disk after every #th
- event. Use 0 (default) to disable synchronous flushing
+ event. Use 0 to disable synchronous flushing
  --sync-relay-log=#  Synchronously flush relay log to disk after every #th
- event. Use 0 (default) to disable synchronous flushing
+ event. Use 0 to disable synchronous flushing
  --sync-relay-log-info=# 
  Synchronously flush relay log info to disk after every
- #th transaction. Use 0 (default) to disable synchronous
- flushing
+ #th transaction. Use 0 to disable synchronous flushing
  --sysdate-is-now    Non-default option to alias SYSDATE() to NOW() to make it
  safe-replicable. Since 5.0, SYSDATE() returns a `dynamic'
  value different for different invocations, even within
@@ -1131,7 +1133,7 @@ auto-increment-increment 1
 auto-increment-offset 1
 autocommit TRUE
 automatic-sp-privileges TRUE
-back-log 150
+back-log 80
 big-tables FALSE
 bind-address (No default value)
 binlog-annotate-row-events FALSE
@@ -1199,7 +1201,7 @@ gtid-strict-mode FALSE
 help TRUE
 histogram-size 0
 histogram-type SINGLE_PREC_HB
-host-cache-size 128
+host-cache-size 279
 ignore-builtin-innodb FALSE
 ignore-db-dirs 
 init-connect 
@@ -1207,7 +1209,7 @@ init-file (No default value)
 init-rpl-role MASTER
 init-slave 
 interactive-timeout 28800
-join-buffer-size 131072
+join-buffer-size 262144
 join-buffer-space-limit 2097152
 join-cache-level 2
 keep-files-on-create FALSE
@@ -1245,7 +1247,7 @@ lower-case-table-names 1
 master-info-file master.info
 master-retry-count 86400
 master-verify-checksum FALSE
-max-allowed-packet 1048576
+max-allowed-packet 4194304
 max-binlog-cache-size 18446744073709547520
 max-binlog-size 1073741824
 max-binlog-stmt-cache-size 18446744073709547520
@@ -1257,7 +1259,7 @@ max-error-count 64
 max-heap-table-size 16777216
 max-join-size 18446744073709551615
 max-length-for-sort-data 1024
-max-long-data-size 1048576
+max-long-data-size 4194304
 max-prepared-stmt-count 16382
 max-relay-log-size 1073741824
 max-seeks-for-key 18446744073709551615
@@ -1352,9 +1354,9 @@ protocol-version 10
 query-alloc-block-size 16384
 query-cache-limit 1048576
 query-cache-min-res-unit 4096
-query-cache-size 0
+query-cache-size 1048576
 query-cache-strip-comments FALSE
-query-cache-type ON
+query-cache-type OFF
 query-cache-wlock-invalidate FALSE
 query-prealloc-size 24576
 range-alloc-block-size 4096
@@ -1376,7 +1378,7 @@ report-port 0
 report-user (No default value)
 rowid-merge-buff-size 8388608
 safe-user-create FALSE
-secure-auth FALSE
+secure-auth TRUE
 secure-file-priv (No default value)
 server-id 0
 show-slave-auth-info FALSE
@@ -1402,20 +1404,20 @@ slave-type-conversions
 slow-launch-time 2
 slow-query-log FALSE
 sort-buffer-size 2097152
-sql-mode 
+sql-mode NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 stack-trace TRUE
 stored-program-cache 256
 strict-password-validation TRUE
 symbolic-links FALSE
 sync-binlog 0
 sync-frm FALSE
-sync-master-info 0
-sync-relay-log 0
-sync-relay-log-info 0
+sync-master-info 10000
+sync-relay-log 10000
+sync-relay-log-info 10000
 sysdate-is-now FALSE
-table-cache 400
+table-cache 2000
 table-definition-cache 400
-table-open-cache 400
+table-open-cache 2000
 tc-heuristic-recover COMMIT
 thread-cache-size 0
 thread-pool-idle-timeout 60
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 7ae8e80cd00..e99cbe08fa6 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -2468,10 +2468,10 @@ if new.a > 10 then
 set new.a := 10;
 set new.a := 11;
 end if;
-end	BEFORE	0000-00-00 00:00:00		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
+end	BEFORE	0000-00-00 00:00:00	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
 trg2	UPDATE	t1	begin
 if old.a % 2 = 0 then set new.b := 12; end if;
-end	BEFORE	0000-00-00 00:00:00		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
+end	BEFORE	0000-00-00 00:00:00	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
 trg3	UPDATE	t1	begin
 if new.a = -1 then
 set @fired:= "Yes";
@@ -2515,7 +2515,7 @@ UNLOCK TABLES;
 /*!50003 SET character_set_results = latin1 */ ;
 /*!50003 SET collation_connection  = latin1_swedish_ci */ ;
 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
-/*!50003 SET sql_mode              = '' */ ;
+/*!50003 SET sql_mode              = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
 DELIMITER ;;
 /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger trg1 before insert on t1 for each row
 begin
@@ -2536,7 +2536,7 @@ DELIMITER ;
 /*!50003 SET character_set_results = latin1 */ ;
 /*!50003 SET collation_connection  = latin1_swedish_ci */ ;
 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
-/*!50003 SET sql_mode              = '' */ ;
+/*!50003 SET sql_mode              = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
 DELIMITER ;;
 /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger trg2 before update on t1 for each row begin
 if old.a % 2 = 0 then set new.b := 12; end if;
@@ -2671,10 +2671,10 @@ if new.a > 10 then
 set new.a := 10;
 set new.a := 11;
 end if;
-end	BEFORE	#		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
+end	BEFORE	#	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
 trg2	UPDATE	t1	begin
 if old.a % 2 = 0 then set new.b := 12; end if;
-end	BEFORE	#		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
+end	BEFORE	#	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
 trg3	UPDATE	t1	begin
 if new.a = -1 then
 set @fired:= "Yes";
diff --git a/mysql-test/r/openssl_1.result b/mysql-test/r/openssl_1.result
index c5dd1500110..1d5c6d179df 100644
--- a/mysql-test/r/openssl_1.result
+++ b/mysql-test/r/openssl_1.result
@@ -1,3 +1,5 @@
+set local sql_mode="";
+set global sql_mode="";
 drop table if exists t1;
 create table t1(f1 int);
 insert into t1 values (5);
@@ -206,4 +208,5 @@ SHOW STATUS LIKE 'Ssl_cipher';
 Variable_name	Value
 Ssl_cipher	DHE-RSA-AES256-SHA
 DROP USER bug42158@localhost;
+set global sql_mode=default;
 End of 5.1 tests
diff --git a/mysql-test/r/openssl_6975,tlsv10.result b/mysql-test/r/openssl_6975,tlsv10.result
index 52d5978749e..6285faa0143 100644
--- a/mysql-test/r/openssl_6975,tlsv10.result
+++ b/mysql-test/r/openssl_6975,tlsv10.result
@@ -1,4 +1,6 @@
+create user ssl_sslv3@localhost;
 grant select on test.* to ssl_sslv3@localhost require cipher "RC4-SHA";
+create user ssl_tls12@localhost;
 grant select on test.* to ssl_tls12@localhost require cipher "AES128-SHA256";
 TLS1.2 ciphers: user is ok with any cipher
 ERROR 2026 (HY000): SSL connection error: error:14077410:SSL routines:SSL23_GET_SERVER_HELLO:sslv3 alert handshake failure
diff --git a/mysql-test/r/openssl_6975,tlsv12.result b/mysql-test/r/openssl_6975,tlsv12.result
index 033220427be..31d2658c829 100644
--- a/mysql-test/r/openssl_6975,tlsv12.result
+++ b/mysql-test/r/openssl_6975,tlsv12.result
@@ -1,4 +1,6 @@
+create user ssl_sslv3@localhost;
 grant select on test.* to ssl_sslv3@localhost require cipher "RC4-SHA";
+create user ssl_tls12@localhost;
 grant select on test.* to ssl_tls12@localhost require cipher "AES128-SHA256";
 TLS1.2 ciphers: user is ok with any cipher
 Variable_name	Value
diff --git a/mysql-test/r/partition_cache.result b/mysql-test/r/partition_cache.result
index cd579d00952..39ba4841337 100644
--- a/mysql-test/r/partition_cache.result
+++ b/mysql-test/r/partition_cache.result
@@ -1,3 +1,5 @@
+SET global query_cache_type=ON;
+SET local query_cache_type=ON;
 SET SESSION STORAGE_ENGINE = InnoDB;
 drop table if exists t1,t2,t3;
 set @save_query_cache_size = @@global.query_cache_size;
@@ -203,3 +205,4 @@ Variable_name	Value
 Qcache_hits	1
 set @@global.query_cache_size = @save_query_cache_size;
 drop table t2;
+SET global query_cache_type=default;
diff --git a/mysql-test/r/partition_cache_innodb.result b/mysql-test/r/partition_cache_innodb.result
index 0d0abbb096c..271b23eed92 100644
--- a/mysql-test/r/partition_cache_innodb.result
+++ b/mysql-test/r/partition_cache_innodb.result
@@ -1,3 +1,5 @@
+SET global query_cache_type=ON;
+SET local query_cache_type=ON;
 SET SESSION STORAGE_ENGINE = innodb;
 drop table if exists t1;
 set @save_query_cache_size = @@global.query_cache_size;
@@ -149,3 +151,4 @@ Variable_name	Value
 Qcache_hits	1
 drop table t1;
 set @@global.query_cache_size = @save_query_cache_size;
+SET global query_cache_type=default;
diff --git a/mysql-test/r/partition_cache_myisam.result b/mysql-test/r/partition_cache_myisam.result
index 0b617c03590..b7d3dc53599 100644
--- a/mysql-test/r/partition_cache_myisam.result
+++ b/mysql-test/r/partition_cache_myisam.result
@@ -1,3 +1,5 @@
+SET global query_cache_type=ON;
+SET local query_cache_type=ON;
 SET SESSION STORAGE_ENGINE = myisam;
 drop table if exists t1;
 set @save_query_cache_size = @@global.query_cache_size;
@@ -151,3 +153,4 @@ Variable_name	Value
 Qcache_hits	2
 drop table t1;
 set @@global.query_cache_size = @save_query_cache_size;
+SET global query_cache_type=default;
diff --git a/mysql-test/r/partition_grant.result b/mysql-test/r/partition_grant.result
index c334a473a2a..f036b0bcc28 100644
--- a/mysql-test/r/partition_grant.result
+++ b/mysql-test/r/partition_grant.result
@@ -3,6 +3,7 @@ create schema mysqltest_1;
 use mysqltest_1;
 create table t1 (a int) partition by list (a) (partition p1 values in (1), partition p2 values in (2), partition p3 values in (3));
 insert into t1 values (1),(2);
+create user mysqltest_1@localhost;
 grant usage on *.* to mysqltest_1@localhost;
 revoke all privileges on *.* from mysqltest_1@localhost;
 grant select,alter on mysqltest_1.* to mysqltest_1@localhost;
diff --git a/mysql-test/r/partition_hash.result b/mysql-test/r/partition_hash.result
index 90126bdd4fd..9a63db3f974 100644
--- a/mysql-test/r/partition_hash.result
+++ b/mysql-test/r/partition_hash.result
@@ -166,9 +166,11 @@ select * from t1;
 f1	f2
 -1	#######
 drop table t1;
+set sql_mode="";
 CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1);
 INSERT INTO t1 VALUES (0);
 DROP TABLE t1;
+set sql_mode=default;
 create table t1 (c1 int DEFAULT NULL,
 c2 varchar (30) DEFAULT NULL,
 c3 date DEFAULT NULL)
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index 798e3b9a6bf..8a72d5fe3b1 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -429,6 +429,7 @@ where a between '2006-01-01' and '2007-06-01';
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p1	ALL	NULL	NULL	NULL	NULL	2	Using where
 drop table t1;
+SET SQL_MODE="";
 create table t1 (a int)
 engine = x
 partition by key (a);
@@ -457,6 +458,7 @@ t1	CREATE TABLE `t1` (
 /*!50100 PARTITION BY LIST (a)
 (PARTITION p0 VALUES IN (0) ENGINE = InnoDB) */
 drop table t1;
+SET SQL_MODE=default;
 create table t1
 (
 id int unsigned auto_increment,
diff --git a/mysql-test/r/plugin.result b/mysql-test/r/plugin.result
index b92881ae7bc..c23c4f2d8a2 100644
--- a/mysql-test/r/plugin.result
+++ b/mysql-test/r/plugin.result
@@ -1,8 +1,5 @@
 CREATE TABLE t1(a int) ENGINE=EXAMPLE;
-Warnings:
-Warning	1286	Unknown storage engine 'EXAMPLE'
-Warning	1266	Using storage engine MyISAM for table 't1'
-DROP TABLE t1;
+ERROR 42000: Unknown storage engine 'EXAMPLE'
 INSTALL PLUGIN example SONAME 'ha_example';
 INSTALL PLUGIN EXAMPLE SONAME 'ha_example';
 ERROR HY000: Plugin 'EXAMPLE' already installed
diff --git a/mysql-test/r/plugin_auth.result b/mysql-test/r/plugin_auth.result
index 0d3948dd313..626722f9dd1 100644
--- a/mysql-test/r/plugin_auth.result
+++ b/mysql-test/r/plugin_auth.result
@@ -1,3 +1,5 @@
+SET GLOBAL SQL_MODE="";
+SET LOCAL SQL_MODE="";
 SELECT PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_DESCRIPTION
 FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='test_plugin_server';
 PLUGIN_STATUS	ACTIVE
@@ -44,7 +46,7 @@ plug@localhost	plug_dest@%
 ## test no_auto_create_user sql mode with plugin users
 SET @@sql_mode=no_auto_create_user;
 GRANT INSERT ON TEST.* TO grant_user IDENTIFIED WITH 'test_plugin_server';
-SET @@sql_mode=default;
+SET @@sql_mode="";
 DROP USER grant_user;
 ## test utf-8 user name
 CREATE USER `Ÿ` IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest';
@@ -426,4 +428,5 @@ USER()	CURRENT_USER()
 bug12818542@localhost	bug12818542_dest@localhost
 DROP USER bug12818542@localhost;
 DROP USER bug12818542_dest@localhost;
+SET GLOBAL SQL_MODE=default;
 End of 5.5 tests
diff --git a/mysql-test/r/plugin_auth_qa.result b/mysql-test/r/plugin_auth_qa.result
index b86a01f6b52..4f274c45971 100644
--- a/mysql-test/r/plugin_auth_qa.result
+++ b/mysql-test/r/plugin_auth_qa.result
@@ -1,3 +1,4 @@
+set sql_mode="";
 CREATE DATABASE test_user_db;
 SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
 user	plugin	authentication_string
diff --git a/mysql-test/r/plugin_not_embedded.result b/mysql-test/r/plugin_not_embedded.result
index 27553366660..8106abc497f 100644
--- a/mysql-test/r/plugin_not_embedded.result
+++ b/mysql-test/r/plugin_not_embedded.result
@@ -1,6 +1,7 @@
 #
 # Bug#51770: UNINSTALL PLUGIN requires no privileges
 #
+CREATE USER bug51770@localhost;
 GRANT INSERT ON mysql.plugin TO bug51770@localhost;
 INSTALL PLUGIN example SONAME 'ha_example.so';
 UNINSTALL PLUGIN example;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 706018d0129..1bdfaa7cc70 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -2390,15 +2390,15 @@ create procedure a() select 42;
 create procedure proc_1(a char(2)) show create procedure a;
 call proc_1("bb");
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
 select 42	latin1	latin1_swedish_ci	latin1_swedish_ci
 call proc_1("bb");
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
 select 42	latin1	latin1_swedish_ci	latin1_swedish_ci
 call proc_1("bb");
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
 select 42	latin1	latin1_swedish_ci	latin1_swedish_ci
 drop procedure proc_1;
 create function func_1() returns int begin show create procedure a; return 1; end|
@@ -2410,15 +2410,15 @@ ERROR 42000: FUNCTION test.func_1 does not exist
 prepare abc from "show create procedure a";
 execute abc;
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
 select 42	latin1	latin1_swedish_ci	latin1_swedish_ci
 execute abc;
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
 select 42	latin1	latin1_swedish_ci	latin1_swedish_ci
 execute abc;
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
 select 42	latin1	latin1_swedish_ci	latin1_swedish_ci
 deallocate prepare abc;
 drop procedure a;
@@ -2427,15 +2427,15 @@ create function a() returns int return 42+13;
 create procedure proc_1(a char(2)) show create function a;
 call proc_1("bb");
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
 return 42+13	latin1	latin1_swedish_ci	latin1_swedish_ci
 call proc_1("bb");
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
 return 42+13	latin1	latin1_swedish_ci	latin1_swedish_ci
 call proc_1("bb");
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
 return 42+13	latin1	latin1_swedish_ci	latin1_swedish_ci
 drop procedure proc_1;
 create function func_1() returns int begin show create function a; return 1; end|
@@ -2447,15 +2447,15 @@ ERROR 42000: FUNCTION test.func_1 does not exist
 prepare abc from "show create function a";
 execute abc;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
 return 42+13	latin1	latin1_swedish_ci	latin1_swedish_ci
 execute abc;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
 return 42+13	latin1	latin1_swedish_ci	latin1_swedish_ci
 execute abc;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-a		CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
+a	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
 return 42+13	latin1	latin1_swedish_ci	latin1_swedish_ci
 deallocate prepare abc;
 drop function a;
diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
index 3ce52cb1595..2ed38dc5c11 100644
--- a/mysql-test/r/ps_1general.result
+++ b/mysql-test/r/ps_1general.result
@@ -308,7 +308,7 @@ Threads_running	#
 prepare stmt4 from ' show variables like ''sql_mode'' ';
 execute stmt4;
 Variable_name	Value
-sql_mode	
+sql_mode	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 prepare stmt4 from ' show engine myisam logs ';
 execute stmt4;
 Type	Name	Status
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 118c6785136..d34c96cd568 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -1,4 +1,13 @@
+SET GLOBAL query_cache_type= Off;
+SET LOCAL query_cache_type= Off;
+SET LOCAL query_cache_type= 0;
+SET LOCAL query_cache_type= on;
+ERROR HY000: Query cache is globally disabled and you can't enable it only for this session
+SET LOCAL query_cache_type= 1;
+ERROR HY000: Query cache is globally disabled and you can't enable it only for this session
 set GLOBAL query_cache_size=1355776;
+SET GLOBAL query_cache_type= ON;
+SET LOCAL query_cache_type= ON;
 flush query cache;
 flush query cache;
 reset query cache;
@@ -1599,7 +1608,9 @@ show status like 'Qcache_free_blocks';
 Variable_name	Value
 Qcache_free_blocks	0
 set global query_cache_size=102400;
-ERROR HY000: Query cache is disabled; set query_cache_type to ON or DEMAND to enable it
+show status like 'Qcache_free_blocks';
+Variable_name	Value
+Qcache_free_blocks	1
 set global query_cache_type=on;
 set session query_cache_type=on;
 Restore default values.
@@ -1660,6 +1671,8 @@ set GLOBAL query_cache_limit=default;
 set GLOBAL query_cache_min_res_unit=default;
 set GLOBAL query_cache_size=default;
 set local query_cache_type=default;
+set GLOBAL query_cache_type=on;
+set local query_cache_type=on;
 FLUSH STATUS;
 SET GLOBAL query_cache_size=10*1024*1024;
 SET @save_concurrent_insert= @@concurrent_insert;
diff --git a/mysql-test/r/query_cache_debug.result b/mysql-test/r/query_cache_debug.result
index 50a3a02fe4d..01c642b325b 100644
--- a/mysql-test/r/query_cache_debug.result
+++ b/mysql-test/r/query_cache_debug.result
@@ -1,3 +1,4 @@
+set global query_cache_type= ON;
 flush status;
 set query_cache_type=DEMAND;
 set global query_cache_size= 1024*768;
diff --git a/mysql-test/r/query_cache_merge.result b/mysql-test/r/query_cache_merge.result
index c324df65f77..e31ab05b396 100644
--- a/mysql-test/r/query_cache_merge.result
+++ b/mysql-test/r/query_cache_merge.result
@@ -1,3 +1,5 @@
+SET GLOBAL query_cache_type=ON;
+SET LOCAL query_cache_type=ON;
 SET @@global.query_cache_size=1355776;
 flush status;
 select count(*) from t00;
@@ -1690,6 +1692,7 @@ DROP TABLE t4;
 DROP TABLE t3;
 DROP TABLE t2;
 DROP TABLE t1;
-SET @@global.query_cache_size = 0;
+SET @@global.query_cache_size = default;
 SET @@global.table_definition_cache = @save_table_definition_cache;
+SET GLOBAL query_cache_type=default;
 End of 5.1 tests
diff --git a/mysql-test/r/query_cache_notembedded.result b/mysql-test/r/query_cache_notembedded.result
index f2021f9d2e7..400ac2b2718 100644
--- a/mysql-test/r/query_cache_notembedded.result
+++ b/mysql-test/r/query_cache_notembedded.result
@@ -1,4 +1,8 @@
-set GLOBAL query_cache_size=1355776;
+set @sql_mode_save=@@global.sql_mode;
+set @query_cache_type_save=@@global.query_cache_type;
+set @query_cache_size_save=@@global.query_cache_size;
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 flush query cache;
 flush query cache;
 reset query cache;
@@ -379,7 +383,8 @@ Qcache_queries_in_cache	1
 USE test;
 DROP DATABASE bug30269;
 DROP USER 'bug30269'@'localhost';
-set GLOBAL query_cache_type=default;
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
 set GLOBAL query_cache_limit=default;
 set GLOBAL query_cache_min_res_unit=default;
 set GLOBAL query_cache_size=default;
@@ -435,3 +440,6 @@ flush query cache|
 delete from t1|
 drop procedure bug3583|
 drop table t1|
+SET GLOBAL query_cache_size=@query_cache_size_save;
+SET GLOBAL query_cache_type=@query_cache_type_save;
+set GLOBAL sql_mode=@sql_mode_save;
diff --git a/mysql-test/r/query_cache_ps_no_prot.result b/mysql-test/r/query_cache_ps_no_prot.result
index 02e660450d1..5076003a7fb 100644
--- a/mysql-test/r/query_cache_ps_no_prot.result
+++ b/mysql-test/r/query_cache_ps_no_prot.result
@@ -1,3 +1,5 @@
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
 ---- establish connection con1 (root) ----
 ---- switch to connection default ----
 set @initial_query_cache_size = @@global.query_cache_size;
@@ -572,3 +574,4 @@ deallocate prepare stmt;
 drop table t1;
 set @@global.query_cache_size=@initial_query_cache_size;
 flush status;
+set GLOBAL query_cache_type=default;
diff --git a/mysql-test/r/query_cache_ps_ps_prot.result b/mysql-test/r/query_cache_ps_ps_prot.result
index 2115ecc62ff..4105bc40f94 100644
--- a/mysql-test/r/query_cache_ps_ps_prot.result
+++ b/mysql-test/r/query_cache_ps_ps_prot.result
@@ -1,3 +1,5 @@
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
 ---- establish connection con1 (root) ----
 ---- switch to connection default ----
 set @initial_query_cache_size = @@global.query_cache_size;
@@ -572,3 +574,4 @@ deallocate prepare stmt;
 drop table t1;
 set @@global.query_cache_size=@initial_query_cache_size;
 flush status;
+set GLOBAL query_cache_type=default;
diff --git a/mysql-test/r/query_cache_with_views.result b/mysql-test/r/query_cache_with_views.result
index 7f0417a60c7..763113dbac3 100644
--- a/mysql-test/r/query_cache_with_views.result
+++ b/mysql-test/r/query_cache_with_views.result
@@ -1,5 +1,7 @@
 drop table if exists t1,t2,v1,v2,v3;
 drop view if exists t1,t2,v1,v2,v3;
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
 set GLOBAL query_cache_size=1355776;
 flush status;
 create table t1 (a int, b int);
@@ -207,3 +209,4 @@ DROP TABLE t2;
 DROP VIEW t1_view;
 SET AUTOCOMMIT=DEFAULT;
 set GLOBAL query_cache_size=default;
+set GLOBAL query_cache_type=default;
diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result
index ae92d39a916..964c259ebf8 100644
--- a/mysql-test/r/read_only.result
+++ b/mysql-test/r/read_only.result
@@ -1,5 +1,6 @@
 set @start_read_only= @@global.read_only;
 DROP TABLE IF EXISTS t1,t2,t3;
+create user test@localhost;
 grant CREATE, SELECT, DROP on *.* to test@localhost;
 connect (con1,localhost,test,,test);
 connection default;
@@ -139,6 +140,7 @@ delete from mysql.db where User like 'mysqltest_%';
 delete from mysql.tables_priv where User like 'mysqltest_%';
 delete from mysql.columns_priv where User like 'mysqltest_%';
 flush privileges;
+create user `mysqltest_u1`@`%`;
 grant all on mysqltest_db2.* to `mysqltest_u1`@`%`;
 create database mysqltest_db1;
 grant all on mysqltest_db1.* to `mysqltest_u1`@`%`;
diff --git a/mysql-test/r/read_only_innodb.result b/mysql-test/r/read_only_innodb.result
index 1e041395d3c..a276e6cbc19 100644
--- a/mysql-test/r/read_only_innodb.result
+++ b/mysql-test/r/read_only_innodb.result
@@ -1,4 +1,5 @@
 DROP TABLE IF EXISTS table_11733 ;
+create user test@localhost;
 grant CREATE, SELECT, DROP on *.* to test@localhost;
 set global read_only=0;
 create table table_11733 (a int) engine=InnoDb;
@@ -16,6 +17,7 @@ ERROR HY000: The MariaDB server is running with the --read-only option so it can
 set global read_only=0;
 drop table table_11733 ;
 drop user test@localhost;
+create user test@localhost;
 GRANT CREATE, SELECT, DROP ON *.* TO test@localhost;
 CREATE TABLE t1(a INT) ENGINE=INNODB;
 INSERT INTO t1 VALUES (0), (1);
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 620bdc6bd50..4238359c201 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1298,10 +1298,10 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` A
 select * from t1, t2, t1 as t3
 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
 a	b	c	d	a	b
-1495	89366	89366	28296	28296	3
-961	24512	24512	85239	85239	4
 1063	89366	89366	28296	28296	3
+1495	89366	89366	28296	28296	3
 221	56120	56120	28296	28296	3
+961	24512	24512	85239	85239	4
 set optimizer_use_condition_selectivity=3;
 explain extended
 select * from t1, t2, t1 as t3
@@ -1315,10 +1315,10 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` A
 select * from t1, t2, t1 as t3
 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
 a	b	c	d	a	b
-961	24512	24512	85239	85239	4
-1495	89366	89366	28296	28296	3
 1063	89366	89366	28296	28296	3
+1495	89366	89366	28296	28296	3
 221	56120	56120	28296	28296	3
+961	24512	24512	85239	85239	4
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1,t2;
 set histogram_type=@save_histogram_type;
@@ -1412,16 +1412,10 @@ drop table t0,t1,t2;
 #
 # Bug mdev-7316: a conjunct in WHERE with selectivity == 0
 #
-CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b)) ENGINE=INNODB;
-Warnings:
-Warning	1286	Unknown storage engine 'INNODB'
-Warning	1266	Using storage engine MyISAM for table 't1'
+CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b));
 INSERT INTO t1 VALUES
 ('USAChinese',10), ('USAEnglish',20), ('USAFrench',30);
-CREATE TABLE t2 (i int) ENGINE=INNODB;
-Warnings:
-Warning	1286	Unknown storage engine 'INNODB'
-Warning	1266	Using storage engine MyISAM for table 't2'
+CREATE TABLE t2 (i int);
 INSERT INTO t2 VALUES
 (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4);
 ANALYZE TABLE t1, t2;
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 0acbb465ba8..daf28073cf1 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1308,10 +1308,10 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` A
 select * from t1, t2, t1 as t3
 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
 a	b	c	d	a	b
-1495	89366	89366	28296	28296	3
-961	24512	24512	85239	85239	4
 1063	89366	89366	28296	28296	3
+1495	89366	89366	28296	28296	3
 221	56120	56120	28296	28296	3
+961	24512	24512	85239	85239	4
 set optimizer_use_condition_selectivity=3;
 explain extended
 select * from t1, t2, t1 as t3
@@ -1325,10 +1325,10 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` A
 select * from t1, t2, t1 as t3
 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
 a	b	c	d	a	b
-961	24512	24512	85239	85239	4
-1495	89366	89366	28296	28296	3
 1063	89366	89366	28296	28296	3
+1495	89366	89366	28296	28296	3
 221	56120	56120	28296	28296	3
+961	24512	24512	85239	85239	4
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1,t2;
 set histogram_type=@save_histogram_type;
@@ -1422,10 +1422,10 @@ drop table t0,t1,t2;
 #
 # Bug mdev-7316: a conjunct in WHERE with selectivity == 0
 #
-CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b)) ENGINE=INNODB;
+CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b));
 INSERT INTO t1 VALUES
 ('USAChinese',10), ('USAEnglish',20), ('USAFrench',30);
-CREATE TABLE t2 (i int) ENGINE=INNODB;
+CREATE TABLE t2 (i int);
 INSERT INTO t2 VALUES
 (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4);
 ANALYZE TABLE t1, t2;
diff --git a/mysql-test/r/servers.result b/mysql-test/r/servers.result
index ab5e444b134..585f0f62af0 100644
--- a/mysql-test/r/servers.result
+++ b/mysql-test/r/servers.result
@@ -1,3 +1,4 @@
+set sql_mode="";
 #
 # MDEV-4594 - CREATE SERVER crashes embedded
 #
diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result
index 08072fccba5..2b557de8e05 100644
--- a/mysql-test/r/set_statement.result
+++ b/mysql-test/r/set_statement.result
@@ -642,7 +642,7 @@ SELECT @@myisam_sort_buffer_size,
 '# Pre-STATEMENT variable value
 SELECT @@sql_mode;
 @@sql_mode
-
+NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 ''
 ''
 SET STATEMENT sql_mode='ansi' FOR PREPARE stmt FROM 'SELECT "t1".* FROM t1';
@@ -659,7 +659,7 @@ deallocate prepare stmt;
 '# Post-STATEMENT
 SELECT @@sql_mode;
 @@sql_mode
-
+NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 check the same behaviour in normal set
 SET sql_mode='ansi';
 PREPARE stmt FROM 'SELECT "t1".* FROM t1';
@@ -675,7 +675,7 @@ ALTER TABLE t1 drop COLUMN v3;
 deallocate prepare stmt;
 SELECT @@sql_mode;
 @@sql_mode
-
+NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 SET sql_mode='ansi';
 SELECT @@sql_mode;
 @@sql_mode
@@ -704,7 +704,7 @@ ALTER TABLE t1 drop COLUMN v3;
 drop procedure p6;
 SELECT @@sql_mode;
 @@sql_mode
-
+NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 # SET and the statement parsed as one unit before the SET takes effect
 SET STATEMENT sql_mode='ansi' FOR
 CREATE PROCEDURE p6() BEGIN
@@ -715,7 +715,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 END' at line 4
 SELECT @@sql_mode;
 @@sql_mode
-
+NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 SET sql_mode='ansi';
 SELECT @@sql_mode;
 @@sql_mode
@@ -732,7 +732,7 @@ v1	v2
 SET sql_mode=default;
 SELECT @@sql_mode;
 @@sql_mode
-
+NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 # SET and the statement parsed as one unit before the SET takes effect
 SET STATEMENT sql_mode='ansi' FOR
 BEGIN NOT ATOMIC
diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
index 1d2ab12dbb8..c281650ecf5 100644
--- a/mysql-test/r/show_check.result
+++ b/mysql-test/r/show_check.result
@@ -518,8 +518,11 @@ Database	Create Database
 mysqltest	CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */
 create table mysqltest.t1(a int);
 insert into mysqltest.t1 values(1);
+create user mysqltest_1@localhost;
 grant select on `mysqltest`.* to mysqltest_1@localhost;
+create user mysqltest_2@localhost;
 grant usage  on `mysqltest`.* to mysqltest_2@localhost;
+create user mysqltest_3@localhost;
 grant drop   on `mysqltest`.* to mysqltest_3@localhost;
 select * from t1;
 a
@@ -997,13 +1000,13 @@ def	information_schema	TRIGGERS	TRIGGERS	EVENT_OBJECT_TABLE	Table	253	192	2	N	1
 def	information_schema	TRIGGERS	TRIGGERS	ACTION_STATEMENT	Statement	252	589815	10	N	17	0	33
 def	information_schema	TRIGGERS	TRIGGERS	ACTION_TIMING	Timing	253	18	6	N	1	0	33
 def	information_schema	TRIGGERS	TRIGGERS	CREATED	Created	12	19	0	Y	128	0	63
-def	information_schema	TRIGGERS	TRIGGERS	SQL_MODE	sql_mode	253	24576	0	N	1	0	33
+def	information_schema	TRIGGERS	TRIGGERS	SQL_MODE	sql_mode	253	24576	42	N	1	0	33
 def	information_schema	TRIGGERS	TRIGGERS	DEFINER	Definer	253	567	14	N	1	0	33
 def	information_schema	TRIGGERS	TRIGGERS	CHARACTER_SET_CLIENT	character_set_client	253	96	6	N	1	0	33
 def	information_schema	TRIGGERS	TRIGGERS	COLLATION_CONNECTION	collation_connection	253	96	6	N	1	0	33
 def	information_schema	TRIGGERS	TRIGGERS	DATABASE_COLLATION	Database Collation	253	96	17	N	1	0	33
 Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
-t1_bi	INSERT	t1	SET @a = 1	BEFORE	NULL		root@localhost	binary	binary	latin1_swedish_ci
+t1_bi	INSERT	t1	SET @a = 1	BEFORE	NULL	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	root@localhost	binary	binary	latin1_swedish_ci
 ----------------------------------------------------------------
 SELECT
 TRIGGER_CATALOG,
@@ -1041,10 +1044,10 @@ def	information_schema	TRIGGERS	TRIGGERS	ACTION_REFERENCE_OLD_TABLE	ACTION_REFER
 def	information_schema	TRIGGERS	TRIGGERS	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_NEW_TABLE	253	192	0	Y	0	0	33
 def	information_schema	TRIGGERS	TRIGGERS	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_OLD_ROW	253	9	3	N	1	0	33
 def	information_schema	TRIGGERS	TRIGGERS	ACTION_REFERENCE_NEW_ROW	ACTION_REFERENCE_NEW_ROW	253	9	3	N	1	0	33
-def	information_schema	TRIGGERS	TRIGGERS	SQL_MODE	SQL_MODE	253	24576	0	N	1	0	33
+def	information_schema	TRIGGERS	TRIGGERS	SQL_MODE	SQL_MODE	253	24576	42	N	1	0	33
 def	information_schema	TRIGGERS	TRIGGERS	DEFINER	DEFINER	253	567	14	N	1	0	33
 TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	SQL_MODE	DEFINER
-def	test	t1_bi	INSERT	def	test	t1	NULL	SET @a = 1	ROW	BEFORE	NULL	NULL	OLD	NEW		root@localhost
+def	test	t1_bi	INSERT	def	test	t1	NULL	SET @a = 1	ROW	BEFORE	NULL	NULL	OLD	NEW	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	root@localhost
 ----------------------------------------------------------------
 SHOW CREATE VIEW v1;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
@@ -1076,13 +1079,13 @@ def	test	v1	select 1 AS `1`	NONE	NO	root@localhost	DEFINER	binary	binary	UNDEFIN
 SHOW CREATE PROCEDURE p1;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					Procedure	253	192	2	N	1	31	33
-def					sql_mode	253	0	0	N	1	31	33
+def					sql_mode	253	126	42	N	1	31	33
 def					Create Procedure	253	3072	59	Y	0	31	33
 def					character_set_client	253	96	6	N	1	31	33
 def					collation_connection	253	96	6	N	1	31	33
 def					Database Collation	253	96	17	N	1	31	33
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+p1	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
 SELECT 1	binary	binary	latin1_swedish_ci
 ----------------------------------------------------------------
 SELECT
@@ -1122,22 +1125,22 @@ def	information_schema	ROUTINES	ROUTINES	IS_DETERMINISTIC	IS_DETERMINISTIC	253	9
 def	information_schema	ROUTINES	ROUTINES	SQL_DATA_ACCESS	SQL_DATA_ACCESS	253	192	12	N	1	0	33
 def	information_schema	ROUTINES	ROUTINES	SQL_PATH	SQL_PATH	253	192	0	Y	0	0	33
 def	information_schema	ROUTINES	ROUTINES	SECURITY_TYPE	SECURITY_TYPE	253	21	7	N	1	0	33
-def	information_schema	ROUTINES	ROUTINES	SQL_MODE	SQL_MODE	253	24576	0	N	1	0	33
+def	information_schema	ROUTINES	ROUTINES	SQL_MODE	SQL_MODE	253	24576	42	N	1	0	33
 def	information_schema	ROUTINES	ROUTINES	ROUTINE_COMMENT	ROUTINE_COMMENT	252	589815	0	N	17	0	33
 def	information_schema	ROUTINES	ROUTINES	DEFINER	DEFINER	253	567	14	N	1	0	33
 SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	SQL_MODE	ROUTINE_COMMENT	DEFINER
-p1	def	test	p1	PROCEDURE	NULL	SQL	SELECT 1	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER			root@localhost
+p1	def	test	p1	PROCEDURE	NULL	SQL	SELECT 1	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION		root@localhost
 ----------------------------------------------------------------
 SHOW CREATE FUNCTION f1;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					Function	253	192	2	N	1	31	33
-def					sql_mode	253	0	0	N	1	31	33
+def					sql_mode	253	126	42	N	1	31	33
 def					Create Function	253	3072	74	Y	0	31	33
 def					character_set_client	253	96	6	N	1	31	33
 def					collation_connection	253	96	6	N	1	31	33
 def					Database Collation	253	96	17	N	1	31	33
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
+f1	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
 RETURN 1	binary	binary	latin1_swedish_ci
 ----------------------------------------------------------------
 SELECT
@@ -1177,11 +1180,11 @@ def	information_schema	ROUTINES	ROUTINES	IS_DETERMINISTIC	IS_DETERMINISTIC	253	9
 def	information_schema	ROUTINES	ROUTINES	SQL_DATA_ACCESS	SQL_DATA_ACCESS	253	192	12	N	1	0	33
 def	information_schema	ROUTINES	ROUTINES	SQL_PATH	SQL_PATH	253	192	0	Y	0	0	33
 def	information_schema	ROUTINES	ROUTINES	SECURITY_TYPE	SECURITY_TYPE	253	21	7	N	1	0	33
-def	information_schema	ROUTINES	ROUTINES	SQL_MODE	SQL_MODE	253	24576	0	N	1	0	33
+def	information_schema	ROUTINES	ROUTINES	SQL_MODE	SQL_MODE	253	24576	42	N	1	0	33
 def	information_schema	ROUTINES	ROUTINES	ROUTINE_COMMENT	ROUTINE_COMMENT	252	589815	0	N	17	0	33
 def	information_schema	ROUTINES	ROUTINES	DEFINER	DEFINER	253	567	14	N	1	0	33
 SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	SQL_MODE	ROUTINE_COMMENT	DEFINER
-f1	def	test	f1	FUNCTION	int(11)	SQL	RETURN 1	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER			root@localhost
+f1	def	test	f1	FUNCTION	int(11)	SQL	RETURN 1	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION		root@localhost
 ----------------------------------------------------------------
 DROP DATABASE mysqltest1;
 DROP TABLE t1;
@@ -1279,6 +1282,7 @@ Database	Create Database
 mysqltest	CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */
 create table mysqltest.t1(a int);
 insert into mysqltest.t1 values(1);
+create user mysqltest_4@localhost;
 grant select on `mysqltest`.`t1` to mysqltest_4@localhost;
 show create database mysqltest;
 Database	Create Database
@@ -1346,69 +1350,69 @@ CREATE TABLE t1(c1 INT);
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1;
 SHOW CREATE TRIGGER t1_bi;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 CREATE PROCEDURE p1() SHOW CREATE TRIGGER t1_bi;
 CALL p1();
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 CALL p1();
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 CALL p1();
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 CALL p1();
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 CALL p1();
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 CALL p1();
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 CALL p1();
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 CALL p1();
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 CALL p1();
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 CALL p1();
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 PREPARE stmt1 FROM 'SHOW CREATE TRIGGER t1_bi';
 EXECUTE stmt1;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 EXECUTE stmt1;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 EXECUTE stmt1;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 EXECUTE stmt1;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 EXECUTE stmt1;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 EXECUTE stmt1;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 EXECUTE stmt1;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 EXECUTE stmt1;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 EXECUTE stmt1;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 EXECUTE stmt1;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 DROP TABLE t1;
 DROP PROCEDURE p1;
 DEALLOCATE PREPARE stmt1;
@@ -1432,20 +1436,20 @@ View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'тест' AS `test`	koi8r	koi8r_general_ci
 SHOW CREATE PROCEDURE p1;
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+p1	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
 SELECT 'тест' AS test	koi8r	koi8r_general_ci	latin1_swedish_ci
 SHOW CREATE FUNCTION f1;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS char(10) CHARSET latin1
+f1	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS char(10) CHARSET latin1
 RETURN 'тест'	koi8r	koi8r_general_ci	latin1_swedish_ci
 SHOW CREATE TRIGGER t1_bi;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1
 FOR EACH ROW
 SET NEW.c1 = 'тест'	koi8r	koi8r_general_ci	latin1_swedish_ci
 SHOW CREATE EVENT ev1;
 Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database Collation
-ev1		SYSTEM	CREATE DEFINER=`root`@`localhost` EVENT `ev1` ON SCHEDULE AT '2030-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 'тест' AS test	koi8r	koi8r_general_ci	latin1_swedish_ci
+ev1	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	SYSTEM	CREATE DEFINER=`root`@`localhost` EVENT `ev1` ON SCHEDULE AT '2030-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 'тест' AS test	koi8r	koi8r_general_ci	latin1_swedish_ci
 DROP VIEW v1;
 DROP PROCEDURE p1;
 DROP FUNCTION f1;
@@ -1508,7 +1512,7 @@ LOCK TABLE t1 WRITE;
 # Connection default
 SHOW CREATE TRIGGER t1_bi;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1	utf8	utf8_general_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1	utf8	utf8_general_ci	latin1_swedish_ci
 # Connection con1
 UNLOCK TABLES;
 # Test 2: ALTER TABLE with SHOW CREATE TRIGGER in transaction
@@ -1516,7 +1520,7 @@ UNLOCK TABLES;
 START TRANSACTION;
 SHOW CREATE TRIGGER t1_bi;
 Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
-t1_bi		CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1	utf8	utf8_general_ci	latin1_swedish_ci
+t1_bi	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1	utf8	utf8_general_ci	latin1_swedish_ci
 # Connection con1
 ALTER TABLE t1 CHARACTER SET = utf8;
 # Connection default
diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 8aefb552d57..0dc6f2b28ac 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -782,6 +782,7 @@ drop table t1,t3,t4;
 #
 # ----------  SHOW EXPLAIN and permissions -----------------
 #
+create user test2@localhost;
 grant ALL on test.* to test2@localhost;
 grant super on *.* to test2@localhost;
 #
diff --git a/mysql-test/r/single_delete_update.result b/mysql-test/r/single_delete_update.result
index 9332effeb56..cdf97e20238 100644
--- a/mysql-test/r/single_delete_update.result
+++ b/mysql-test/r/single_delete_update.result
@@ -1166,35 +1166,3 @@ a	b	c
 25	25	10
 25	25	10
 DROP TABLE t1, t2;
-#
-# Bug #53742: UPDATEs have no effect after applying patch for bug 36569
-#
-CREATE TABLE t1 (
-pk INT NOT NULL AUTO_INCREMENT,
-c1_idx CHAR(1) DEFAULT 'y',
-c2 INT,
-PRIMARY KEY (pk),
-INDEX c1_idx (c1_idx)
-) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (), (), (), ();
-SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
-pk	c1_idx	c2
-4	y	NULL
-3	y	NULL
-UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
-SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
-pk	c1_idx	c2
-4	y	0
-3	y	0
-SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC;
-pk	c1_idx	c2
-4	y	0
-3	y	0
-2	y	NULL
-1	y	NULL
-DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
-SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC;
-pk	c1_idx	c2
-2	y	NULL
-1	y	NULL
-DROP TABLE t1;
diff --git a/mysql-test/r/single_delete_update_innodb.result b/mysql-test/r/single_delete_update_innodb.result
new file mode 100644
index 00000000000..aca40974054
--- /dev/null
+++ b/mysql-test/r/single_delete_update_innodb.result
@@ -0,0 +1,32 @@
+#
+# Bug #53742: UPDATEs have no effect after applying patch for bug 36569
+#
+CREATE TABLE t1 (
+pk INT NOT NULL AUTO_INCREMENT,
+c1_idx CHAR(1) DEFAULT 'y',
+c2 INT,
+PRIMARY KEY (pk),
+INDEX c1_idx (c1_idx)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (), (), (), ();
+SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
+pk	c1_idx	c2
+4	y	NULL
+3	y	NULL
+UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
+SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
+pk	c1_idx	c2
+4	y	0
+3	y	0
+SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC;
+pk	c1_idx	c2
+4	y	0
+3	y	0
+2	y	NULL
+1	y	NULL
+DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
+SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC;
+pk	c1_idx	c2
+2	y	NULL
+1	y	NULL
+DROP TABLE t1;
diff --git a/mysql-test/r/skip_grants.result b/mysql-test/r/skip_grants.result
index db3b6abdc8b..9852d6d12c1 100644
--- a/mysql-test/r/skip_grants.result
+++ b/mysql-test/r/skip_grants.result
@@ -39,11 +39,11 @@ View	Create View	character_set_client	collation_connection
 v3	CREATE ALGORITHM=UNDEFINED DEFINER=`a`@`%` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`c` AS `c` from `t1`	latin1	latin1_swedish_ci
 SHOW CREATE PROCEDURE p3;
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-p3		CREATE DEFINER=`a`@`%` PROCEDURE `p3`()
+p3	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`a`@`%` PROCEDURE `p3`()
 SELECT 3	latin1	latin1_swedish_ci	latin1_swedish_ci
 SHOW CREATE FUNCTION f3;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f3		CREATE DEFINER=`a`@`%` FUNCTION `f3`() RETURNS int(11)
+f3	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`a`@`%` FUNCTION `f3`() RETURNS int(11)
 RETURN 3	latin1	latin1_swedish_ci	latin1_swedish_ci
 DROP TRIGGER t1_bi;
 DROP TRIGGER ti_ai;
diff --git a/mysql-test/r/skip_name_resolve.result b/mysql-test/r/skip_name_resolve.result
index 1d92d52110d..b2230ccbd91 100644
--- a/mysql-test/r/skip_name_resolve.result
+++ b/mysql-test/r/skip_name_resolve.result
@@ -1,3 +1,4 @@
+CREATE USER mysqltest_1@'127.0.0.1/255.255.255.255';
 GRANT ALL ON test.* TO mysqltest_1@'127.0.0.1/255.255.255.255';
 SHOW GRANTS FOR mysqltest_1@'127.0.0.1/255.255.255.255';
 Grants for mysqltest_1@127.0.0.1/255.255.255.255
diff --git a/mysql-test/r/sp-lock.result b/mysql-test/r/sp-lock.result
index 860312dca3e..49ff0c7ba58 100644
--- a/mysql-test/r/sp-lock.result
+++ b/mysql-test/r/sp-lock.result
@@ -725,7 +725,7 @@ release_lock("test")
 get_lock("test", 10)
 1
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+p1	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
 BEGIN
 SELECT get_lock("test", 10);
 SHOW CREATE PROCEDURE p1;
diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result
index 7e89867963a..276da41a0fe 100644
--- a/mysql-test/r/sp-security.result
+++ b/mysql-test/r/sp-security.result
@@ -1,4 +1,5 @@
 use test;
+create user user1@localhost;
 grant usage on *.* to user1@localhost;
 flush privileges;
 drop table if exists t1;
@@ -31,10 +32,13 @@ root@localhost	1
 select db();
 db()
 test
+create user user1@'%';
 grant execute on procedure db1_secret.stamp to user1@'%';
 grant execute on function db1_secret.db to user1@'%';
+set sql_mode='';
 grant execute on procedure db1_secret.stamp to ''@'%';
 grant execute on function db1_secret.db to ''@'%';
+set sql_mode=default;
 call db1_secret.stamp(2);
 select db1_secret.db();
 db1_secret.db()
@@ -103,6 +107,7 @@ create table t2 (s1 int);
 insert into t2 values (0);
 grant usage on db2.* to user1@localhost;
 grant select on db2.* to user1@localhost;
+create user user2@localhost;
 grant usage on db2.* to user2@localhost;
 grant select,insert,update,delete,create routine on db2.* to user2@localhost;
 grant create routine on db2.* to user1@localhost;
@@ -156,8 +161,11 @@ delete from mysql.procs_priv where user='user1' or user='user2';
 delete from mysql.procs_priv where user='' and host='%';
 delete from mysql.db where user='user2';
 flush privileges;
+create user usera@localhost;
 grant usage on *.* to usera@localhost;
+create user userb@localhost;
 grant usage on *.* to userb@localhost;
+create user userc@localhost;
 grant usage on *.* to userc@localhost;
 create database sptest;
 create table t1 ( u varchar(64), i int );
@@ -220,9 +228,11 @@ delete from mysql.tables_priv where user='usera';
 flush privileges;
 drop table t1;
 drop function if exists bug_9503;
+drop user if exists user1@localhost;
 create database mysqltest//
 use mysqltest//
 create table t1 (s1 int)//
+create user user1@localhost//
 grant select on t1 to user1@localhost//
 create function bug_9503 () returns int sql security invoker begin declare v int;
 select min(s1) into v from t1; return v; end//
@@ -266,6 +276,7 @@ create procedure mysqltest_1.p1()
 begin
 select 1 from dual;
 end//
+create user mysqltest_1@localhost;
 grant usage on *.* to mysqltest_1@localhost;
 call mysqltest_1.p1();
 ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1'
@@ -366,19 +377,19 @@ Note	1449	The user specified as a definer ('a @ b @ c'@'localhost') does not exi
 USE mysqltest;
 SHOW CREATE PROCEDURE wl2897_p1;
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-wl2897_p1		CREATE DEFINER=`mysqltest_2`@`localhost` PROCEDURE `wl2897_p1`()
+wl2897_p1	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`mysqltest_2`@`localhost` PROCEDURE `wl2897_p1`()
 SELECT 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 SHOW CREATE PROCEDURE wl2897_p3;
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-wl2897_p3		CREATE DEFINER=`a @ b @ c`@`localhost` PROCEDURE `wl2897_p3`()
+wl2897_p3	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`a @ b @ c`@`localhost` PROCEDURE `wl2897_p3`()
 SELECT 3	latin1	latin1_swedish_ci	latin1_swedish_ci
 SHOW CREATE FUNCTION wl2897_f1;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-wl2897_f1		CREATE DEFINER=`mysqltest_2`@`localhost` FUNCTION `wl2897_f1`() RETURNS int(11)
+wl2897_f1	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`mysqltest_2`@`localhost` FUNCTION `wl2897_f1`() RETURNS int(11)
 RETURN 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 SHOW CREATE FUNCTION wl2897_f3;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-wl2897_f3		CREATE DEFINER=`a @ b @ c`@`localhost` FUNCTION `wl2897_f3`() RETURNS int(11)
+wl2897_f3	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`a @ b @ c`@`localhost` FUNCTION `wl2897_f3`() RETURNS int(11)
 RETURN 3	latin1	latin1_swedish_ci	latin1_swedish_ci
 DROP USER mysqltest_1@localhost;
 DROP USER mysqltest_2@localhost;
@@ -444,7 +455,7 @@ SELECT a;
 END //
 SHOW CREATE PROCEDURE test.sp19857;
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-sp19857		CREATE DEFINER=`user19857`@`localhost` PROCEDURE `sp19857`()
+sp19857	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`user19857`@`localhost` PROCEDURE `sp19857`()
     DETERMINISTIC
 BEGIN
 DECLARE a INT;
@@ -612,7 +623,7 @@ GRANT SELECT ON mysql.proc TO user2@localhost;
 # This should work
 SHOW CREATE PROCEDURE db1.p1;
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+p1	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
 SELECT 1	latin1	latin1_swedish_ci	latin1_swedish_ci
 # Connection default
 DROP USER user2@localhost;
diff --git a/mysql-test/r/sp-ucs2.result b/mysql-test/r/sp-ucs2.result
index c7a3ed6e46a..3c444ad97de 100644
--- a/mysql-test/r/sp-ucs2.result
+++ b/mysql-test/r/sp-ucs2.result
@@ -40,7 +40,7 @@ BEGIN
 RETURN '';
 END|
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f		CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET ucs2 COLLATE ucs2_bin
+f	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET ucs2 COLLATE ucs2_bin
 BEGIN
 RETURN '';
 END	latin1	latin1_swedish_ci	latin1_swedish_ci
@@ -55,7 +55,7 @@ BEGIN
 RETURN '';
 END|
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f		CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET ucs2 COLLATE ucs2_bin
+f	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET ucs2 COLLATE ucs2_bin
 BEGIN
 RETURN '';
 END	latin1	latin1_swedish_ci	latin1_swedish_ci
@@ -67,7 +67,7 @@ BEGIN
 RETURN '';
 END|
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f		CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET latin1 COLLATE latin1_bin
+f	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET latin1 COLLATE latin1_bin
 BEGIN
 RETURN '';
 END	latin1	latin1_swedish_ci	latin1_swedish_ci
@@ -83,7 +83,7 @@ SET f2= f1;
 SET f2= concat(collation(f1), ' ', collation(f2));
 END|
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f		CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET latin1 COLLATE latin1_bin
+f	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET latin1 COLLATE latin1_bin
 BEGIN
 RETURN '';
 END	latin1	latin1_swedish_ci	latin1_swedish_ci
@@ -127,7 +127,7 @@ RETURNS ENUM( 'w' ) CHARACTER SET ucs2
 RETURN 0;
 SHOW CREATE FUNCTION bug48766;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-bug48766		CREATE DEFINER=`root`@`localhost` FUNCTION `bug48766`() RETURNS enum('w') CHARSET ucs2
+bug48766	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `bug48766`() RETURNS enum('w') CHARSET ucs2
 RETURN 0	utf8	utf8_general_ci	latin1_swedish_ci
 SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_NAME='bug48766';
@@ -139,7 +139,7 @@ RETURNS ENUM('а','б','в','г') CHARACTER SET ucs2
 RETURN 0;
 SHOW CREATE FUNCTION bug48766;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-bug48766		CREATE DEFINER=`root`@`localhost` FUNCTION `bug48766`() RETURNS enum('а','б','в','г') CHARSET ucs2
+bug48766	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `bug48766`() RETURNS enum('а','б','в','г') CHARSET ucs2
 RETURN 0	utf8	utf8_general_ci	latin1_swedish_ci
 SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_NAME='bug48766';
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 27a21e190fd..fe81c96e811 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -791,7 +791,7 @@ comment 'Characteristics procedure test'
   insert into t1 values ("chistics", 1)|
 show create procedure chistics|
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-chistics		CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
+chistics	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
     MODIFIES SQL DATA
     COMMENT 'Characteristics procedure test'
 insert into t1 values ("chistics", 1)	latin1	latin1_swedish_ci	latin1_swedish_ci
@@ -803,7 +803,7 @@ delete from t1|
 alter procedure chistics sql security invoker|
 show create procedure chistics|
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-chistics		CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
+chistics	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
     MODIFIES SQL DATA
     SQL SECURITY INVOKER
     COMMENT 'Characteristics procedure test'
@@ -818,7 +818,7 @@ comment 'Characteristics procedure test'
   return 42|
 show create function chistics|
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-chistics		CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
+chistics	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
     DETERMINISTIC
     SQL SECURITY INVOKER
     COMMENT 'Characteristics procedure test'
@@ -831,7 +831,7 @@ no sql
 comment 'Characteristics function test'|
 show create function chistics|
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-chistics		CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
+chistics	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
     NO SQL
     DETERMINISTIC
     SQL SECURITY INVOKER
diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result
index 3cc4ff4238b..40a06d312a0 100644
--- a/mysql-test/r/sp_notembedded.result
+++ b/mysql-test/r/sp_notembedded.result
@@ -150,6 +150,7 @@ drop view v1|
 drop table t3|
 drop procedure if exists bug15298_1;
 drop procedure if exists bug15298_2;
+create user 'mysqltest_1'@'localhost';
 grant all privileges on test.* to 'mysqltest_1'@'localhost';
 create procedure 15298_1 () sql security definer show grants for current_user;
 create procedure 15298_2 () sql security definer show grants;
@@ -203,6 +204,8 @@ Warnings:
 Warning	1364	Field 'authentication_string' doesn't have a default value
 FLUSH PRIVILEGES;
 CREATE PROCEDURE p1(i INT) BEGIN END;
+Warnings:
+Warning	1404	Failed to grant EXECUTE and ALTER ROUTINE privileges
 DROP PROCEDURE p1;
 DELETE FROM mysql.user WHERE User='mysqltest_1';
 FLUSH PRIVILEGES;
diff --git a/mysql-test/r/sp_trans.result b/mysql-test/r/sp_trans.result
index 5526fc19aae..c76615d644f 100644
--- a/mysql-test/r/sp_trans.result
+++ b/mysql-test/r/sp_trans.result
@@ -1,3 +1,4 @@
+set SQL_MODE="";
 drop table if exists t1, t2, t3;
 drop procedure if exists bug8850|
 create table t1 (a int) engine=innodb|
diff --git a/mysql-test/r/stack-crash.result b/mysql-test/r/stack-crash.result
index be5b6464b9a..62870db79f3 100644
--- a/mysql-test/r/stack-crash.result
+++ b/mysql-test/r/stack-crash.result
@@ -23,10 +23,7 @@ CREATE TABLE t1 (
 `sspo_lu_uid` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`sspo_id`),
 KEY `post_uid` (`sspo_uid`,`sspo_cr_date`)
-) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 't1'
+) AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
 INSERT INTO t1 VALUES (1,2,'P','test1','',0,'','',NULL,'','','','','A','2013-09-30 00:19:32',2,'2013-09-30 00:19:32',2),(2,2,'P','bbb','',0,'','',NULL,'','','','','A','2013-10-02 15:06:35',2,'2013-10-02 15:06:35',2);
 CREATE TABLE `t2` (
 `spoo_id` int(11) NOT NULL AUTO_INCREMENT,
@@ -41,10 +38,7 @@ CREATE TABLE `t2` (
 `spoo_lu_uid` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`spoo_id`),
 KEY `object_option_main_idx` (`spoo_user_type_id`,`spoo_uid`,`spoo_option_id`,`spoo_value`(255))
-) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 't2'
+) AUTO_INCREMENT=107 DEFAULT CHARSET=utf8;
 INSERT INTO `t2` VALUES (19,1,2,6,'Dortmund','A','2013-09-26 01:36:51',2,'2013-09-26 01:36:51',2),(20,1,2,8,'49','A','2013-09-26 01:36:51',2,'2013-09-26 01:36:51',2);
 SELECT Count(*)
 FROM   t1 AS tbl
diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result
index 140db7c5721..55afd159053 100644
--- a/mysql-test/r/status.result
+++ b/mysql-test/r/status.result
@@ -204,6 +204,7 @@ Com_drop_function	1
 create database db37908;
 create table db37908.t1(f1 int);
 insert into db37908.t1 values(1);
+create user mysqltest_1@localhost;
 grant usage,execute on test.* to mysqltest_1@localhost;
 create procedure proc37908() begin select 1; end |
 create function func37908() returns int sql security invoker
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 46c799c4f6e..a937cc960f3 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2821,10 +2821,7 @@ col_varchar_nokey varchar(1) DEFAULT NULL,
 PRIMARY KEY (pk),
 KEY col_int_key (col_int_key),
 KEY col_varchar_key (col_varchar_key, col_int_key)
-) ENGINE=InnoDB;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 't1'
+);
 INSERT INTO t1 VALUES
 (10,8,'x','x'),
 (11,7,'d','d'),
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index ba582d7e008..73dd69681d2 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2835,10 +2835,7 @@ col_varchar_nokey varchar(1) DEFAULT NULL,
 PRIMARY KEY (pk),
 KEY col_int_key (col_int_key),
 KEY col_varchar_key (col_varchar_key, col_int_key)
-) ENGINE=InnoDB;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 't1'
+);
 INSERT INTO t1 VALUES
 (10,8,'x','x'),
 (11,7,'d','d'),
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index c633261bcd3..3e0cc480aec 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -682,20 +682,14 @@ SET optimizer_switch=@save_optimizer_switch;
 #  (just a testcase)
 CREATE TABLE t1 (
 PostID int(10) unsigned NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 't1'
+) DEFAULT CHARSET=utf8;
 INSERT INTO t1 (PostID) VALUES (1), (2);
 CREATE TABLE t2 (
 VoteID int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
 EntityID int(10) unsigned NOT NULL,
 UserID int(10) unsigned NOT NULL,
 UNIQUE KEY EntityID (EntityID,UserID)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-Warnings:
-Warning	1286	Unknown storage engine 'InnoDB'
-Warning	1266	Using storage engine MyISAM for table 't2'
+) DEFAULT CHARSET=utf8;
 INSERT INTO t2 (EntityID, UserID) VALUES (1,  30), (2, 30);
 SELECT t1.*, T.Voted as Voted
 FROM 
diff --git a/mysql-test/r/timezone_grant.result b/mysql-test/r/timezone_grant.result
index 49918038da5..d33d92dbc9e 100644
--- a/mysql-test/r/timezone_grant.result
+++ b/mysql-test/r/timezone_grant.result
@@ -7,6 +7,7 @@ delete from mysql.columns_priv where user like 'mysqltest\_%';
 flush privileges;
 create table t1 (a int, b datetime);
 create table t2 (c int, d datetime);
+create user mysqltest_1@localhost;
 grant all privileges on test.* to mysqltest_1@localhost;
 show grants for current_user();
 Grants for mysqltest_1@localhost
@@ -63,6 +64,7 @@ flush privileges;
 drop table t1, t2;
 create table t1 (a int, b datetime);
 insert into t1 values (1, 20010101000000), (2, 20020101000000);
+create user mysqltest_1@localhost;
 grant all privileges on test.* to mysqltest_1@localhost;
 create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1;
 select * from v1;
diff --git a/mysql-test/r/trigger-compat.result b/mysql-test/r/trigger-compat.result
index 8caba961624..7659667fb37 100644
--- a/mysql-test/r/trigger-compat.result
+++ b/mysql-test/r/trigger-compat.result
@@ -29,8 +29,8 @@ wl2818_trg2	mysqltest_dfn@localhost
 
 SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
 TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED	SQL_MODE	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
-def	mysqltest_db1	wl2818_trg1	INSERT	def	mysqltest_db1	t1	0	NULL	INSERT INTO t2 VALUES(CURRENT_USER())	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL			latin1	latin1_swedish_ci	latin1_swedish_ci
-def	mysqltest_db1	wl2818_trg2	INSERT	def	mysqltest_db1	t1	0	NULL	INSERT INTO t2 VALUES(CURRENT_USER())	ROW	AFTER	NULL	NULL	OLD	NEW	NULL		mysqltest_dfn@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
+def	mysqltest_db1	wl2818_trg1	INSERT	def	mysqltest_db1	t1	0	NULL	INSERT INTO t2 VALUES(CURRENT_USER())	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION		latin1	latin1_swedish_ci	latin1_swedish_ci
+def	mysqltest_db1	wl2818_trg2	INSERT	def	mysqltest_db1	t1	0	NULL	INSERT INTO t2 VALUES(CURRENT_USER())	ROW	AFTER	NULL	NULL	OLD	NEW	NULL	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	mysqltest_dfn@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
 DROP TRIGGER wl2818_trg1;
 Warnings:
 Warning	1454	No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
diff --git a/mysql-test/r/trigger_notembedded.result b/mysql-test/r/trigger_notembedded.result
index ef86e4c496c..795f722b0d4 100644
--- a/mysql-test/r/trigger_notembedded.result
+++ b/mysql-test/r/trigger_notembedded.result
@@ -138,8 +138,8 @@ INSERT INTO t1 VALUES(6);
 ERROR HY000: The user specified as a definer ('mysqltest_nonexs'@'localhost') does not exist
 SHOW TRIGGERS;
 Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
-trg1	INSERT	t1	SET @new_sum = 0	BEFORE	NULL		mysqltest_inv@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
-trg2	INSERT	t1	SET @new_sum = 0	AFTER	NULL		mysqltest_nonexs@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
+trg1	INSERT	t1	SET @new_sum = 0	BEFORE	NULL	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	mysqltest_inv@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
+trg2	INSERT	t1	SET @new_sum = 0	AFTER	NULL	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	mysqltest_nonexs@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
 DROP TRIGGER trg1;
 DROP TRIGGER trg2;
 CREATE TRIGGER trg1 BEFORE INSERT ON t1
@@ -168,11 +168,11 @@ trg5	@abcdef@@@hostname
 
 SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
 TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED	SQL_MODE	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
-def	mysqltest_db1	trg1	INSERT	def	mysqltest_db1	t1	0	NULL	SET @a = 1	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL			latin1	latin1_swedish_ci	latin1_swedish_ci
-def	mysqltest_db1	trg2	INSERT	def	mysqltest_db1	t1	0	NULL	SET @a = 2	ROW	AFTER	NULL	NULL	OLD	NEW	NULL		@	latin1	latin1_swedish_ci	latin1_swedish_ci
-def	mysqltest_db1	trg3	UPDATE	def	mysqltest_db1	t1	0	NULL	SET @a = 3	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL		@abc@def@@%	latin1	latin1_swedish_ci	latin1_swedish_ci
-def	mysqltest_db1	trg4	UPDATE	def	mysqltest_db1	t1	0	NULL	SET @a = 4	ROW	AFTER	NULL	NULL	OLD	NEW	NULL		@hostname	latin1	latin1_swedish_ci	latin1_swedish_ci
-def	mysqltest_db1	trg5	DELETE	def	mysqltest_db1	t1	0	NULL	SET @a = 5	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL		@abcdef@@@hostname	latin1	latin1_swedish_ci	latin1_swedish_ci
+def	mysqltest_db1	trg1	INSERT	def	mysqltest_db1	t1	0	NULL	SET @a = 1	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION		latin1	latin1_swedish_ci	latin1_swedish_ci
+def	mysqltest_db1	trg2	INSERT	def	mysqltest_db1	t1	0	NULL	SET @a = 2	ROW	AFTER	NULL	NULL	OLD	NEW	NULL	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	@	latin1	latin1_swedish_ci	latin1_swedish_ci
+def	mysqltest_db1	trg3	UPDATE	def	mysqltest_db1	t1	0	NULL	SET @a = 3	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	@abc@def@@%	latin1	latin1_swedish_ci	latin1_swedish_ci
+def	mysqltest_db1	trg4	UPDATE	def	mysqltest_db1	t1	0	NULL	SET @a = 4	ROW	AFTER	NULL	NULL	OLD	NEW	NULL	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	@hostname	latin1	latin1_swedish_ci	latin1_swedish_ci
+def	mysqltest_db1	trg5	DELETE	def	mysqltest_db1	t1	0	NULL	SET @a = 5	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	@abcdef@@@hostname	latin1	latin1_swedish_ci	latin1_swedish_ci
 
 ---> connection: default
 DROP USER mysqltest_dfn@localhost;
@@ -502,6 +502,7 @@ DROP DATABASE db1;
 DROP DATABASE IF EXISTS mysqltest_db1;
 CREATE DATABASE mysqltest_db1;
 USE mysqltest_db1;
+CREATE USER mysqltest_u1@localhost;
 GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost;
 CREATE TABLE t1 (
 a1 int,
diff --git a/mysql-test/r/truncate.result b/mysql-test/r/truncate.result
index 773075f9dae..9b4cb4a36d9 100644
--- a/mysql-test/r/truncate.result
+++ b/mysql-test/r/truncate.result
@@ -142,7 +142,7 @@ TRUNCATE p1;
 ERROR 42S02: Table 'test.p1' doesn't exist
 SHOW CREATE PROCEDURE p1;
 Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
-p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+p1	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
 SET @a = 5	latin1	latin1_swedish_ci	latin1_swedish_ci
 DROP PROCEDURE p1;
 #
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index ef1749eda52..d3498c27f13 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1281,7 +1281,7 @@ t3	CREATE TABLE `t3` (
 drop tables t1,t2,t3;
 SELECT @tmp_max:= @@global.max_allowed_packet;
 @tmp_max:= @@global.max_allowed_packet
-1048576
+4194304
 SET @@global.max_allowed_packet=25000000;
 Warnings:
 Warning	1292	Truncated incorrect max_allowed_packet value: '25000000'
diff --git a/mysql-test/r/user_limits.result b/mysql-test/r/user_limits.result
index 688704f528a..5ab98d1fc30 100644
--- a/mysql-test/r/user_limits.result
+++ b/mysql-test/r/user_limits.result
@@ -6,6 +6,7 @@ delete from mysql.db where user like 'mysqltest\_%';
 delete from mysql.tables_priv where user like 'mysqltest\_%';
 delete from mysql.columns_priv where user like 'mysqltest\_%';
 flush privileges;
+create user mysqltest_1@localhost;
 grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 2;
 flush user_resources;
 select * from t1;
@@ -17,6 +18,7 @@ ERROR 42000: User 'mysqltest_1' has exceeded the 'max_queries_per_hour' resource
 select * from t1;
 ERROR 42000: User 'mysqltest_1' has exceeded the 'max_queries_per_hour' resource (current value: 2)
 drop user mysqltest_1@localhost;
+create user mysqltest_1@localhost;
 grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 2;
 flush user_resources;
 select * from t1;
@@ -36,6 +38,7 @@ ERROR 42000: User 'mysqltest_1' has exceeded the 'max_updates_per_hour' resource
 select * from t1;
 i
 drop user mysqltest_1@localhost;
+create user mysqltest_1@localhost;
 grant usage on *.* to mysqltest_1@localhost with max_connections_per_hour 2;
 flush user_resources;
 select * from t1;
@@ -50,6 +53,7 @@ connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
 ERROR 42000: User 'mysqltest_1' has exceeded the 'max_connections_per_hour' resource (current value: 2)
 drop user mysqltest_1@localhost;
 flush privileges;
+create user mysqltest_1@localhost;
 grant usage on *.* to mysqltest_1@localhost with max_user_connections 2;
 flush user_resources;
 select * from t1;
@@ -86,6 +90,7 @@ set global max_user_connections= 2;
 select @@session.max_user_connections, @@global.max_user_connections;
 @@session.max_user_connections	@@global.max_user_connections
 2	2
+create user mysqltest_1@localhost;
 grant usage on *.* to mysqltest_1@localhost;
 flush user_resources;
 select @@session.max_user_connections, @@global.max_user_connections;
diff --git a/mysql-test/r/user_var-binlog.result b/mysql-test/r/user_var-binlog.result
index e6e9ddf9545..2e1eb15cad4 100644
--- a/mysql-test/r/user_var-binlog.result
+++ b/mysql-test/r/user_var-binlog.result
@@ -30,7 +30,7 @@ use `test`/*!*/;
 SET TIMESTAMP=10000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
-SET @@session.sql_mode=0/*!*/;
+SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index 5ed6c5abd47..fef3e4a3e9e 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -1133,12 +1133,12 @@ ERROR HY000: Variable 'ft_stopword_file' is a read only variable
 #
 SHOW VARIABLES like 'back_log';
 Variable_name	Value
-back_log	150
+back_log	80
 SELECT @@session.back_log;
 ERROR HY000: Variable 'back_log' is a GLOBAL variable
 SELECT @@global.back_log;
 @@global.back_log
-150
+80
 SET @@session.back_log= 7;
 ERROR HY000: Variable 'back_log' is a read only variable
 SET @@global.back_log= 7;
@@ -1528,7 +1528,7 @@ SET @@global.key_buffer_size=@kbs;
 SET @@global.key_cache_block_size=@kcbs;
 select @@max_long_data_size;
 @@max_long_data_size
-1048576
+4194304
 #
 # Bug#11766424  59527:
 # Assert in DECIMAL_BIN_SIZE: 
@@ -1743,7 +1743,9 @@ SET @@sql_quote_show_create = @sql_quote_show_create_saved;
 drop table if exists t1;
 drop function if exists t1_max;
 drop function if exists t1_min;
+set sql_mode="";
 create table t1 (a int) engine=innodb;
+set sql_mode=default;
 insert into t1(a) values (0), (1);
 create function t1_max() returns int return (select max(a) from t1);
 create function t1_min() returns int return (select min(a) from t1);
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 4a39eb9d57a..df2b48564ad 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2237,12 +2237,12 @@ drop table t1;
 create table t1 (
 r_object_id char(16) NOT NULL,
 group_name varchar(32) NOT NULL
-) engine = InnoDB;
+);
 create table t2 (
 r_object_id char(16) NOT NULL,
 i_position int(11) NOT NULL,
 users_names varchar(32) default NULL
-) Engine = InnoDB;
+);
 create view v1 as select r_object_id, group_name from t1;
 create view v2 as select r_object_id, i_position, users_names from t2;
 create unique index r_object_id on t1(r_object_id);
@@ -2902,6 +2902,8 @@ Tables_in_test
 t1
 DROP TABLE t1;
 DROP VIEW IF EXISTS v1;
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
 CREATE DATABASE bug21261DB;
 USE bug21261DB;
 CREATE TABLE t1 (x INT);
@@ -2924,6 +2926,8 @@ DROP VIEW v1;
 DROP TABLE t1;
 DROP DATABASE bug21261DB;
 USE test;
+set GLOBAL sql_mode=default;
+set LOCAL sql_mode=default;
 create table t1 (f1 datetime);
 create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute;
 show create view v1;
diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result
index 550ca12d19e..525f9fbb5e1 100644
--- a/mysql-test/r/view_grant.result
+++ b/mysql-test/r/view_grant.result
@@ -1,5 +1,6 @@
 drop database if exists mysqltest;
 drop view if exists v1,v2,v3;
+create user test@localhost;
 grant create view on test.* to test@localhost;
 show grants for test@localhost;
 Grants for test@localhost
@@ -13,6 +14,7 @@ drop user test@localhost;
 create database mysqltest;
 create table mysqltest.t1 (a int, b int);
 create table mysqltest.t2 (a int, b int);
+create user mysqltest_1@localhost;
 grant select on mysqltest.t1 to mysqltest_1@localhost;
 grant create view,select on test.* to mysqltest_1@localhost;
 create definer=root@localhost view v1 as select * from mysqltest.t1;
@@ -46,18 +48,19 @@ c
 select d from mysqltest.v1;
 ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1'
 revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 create database mysqltest;
 create table mysqltest.t1 (a int, b int);
 create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
+create user mysqltest_1@localhost;
 grant select (c) on mysqltest.v1 to mysqltest_1@localhost;
 select c from mysqltest.v1;
 c
 select d from mysqltest.v1;
 ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1'
 revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 create database mysqltest;
 create table mysqltest.t1 (a int, b int);
@@ -67,6 +70,7 @@ create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqlt
 create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2;
 create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2;
 create view mysqltest.v5 (c,d) as select a+1,b+1 from mysqltest.t1;
+create user mysqltest_1@localhost;
 grant select on mysqltest.v1 to mysqltest_1@localhost;
 grant select on mysqltest.v2 to mysqltest_1@localhost;
 grant select on mysqltest.v3 to mysqltest_1@localhost;
@@ -166,7 +170,7 @@ show create view mysqltest.v4;
 View	Create View	character_set_client	collation_connection
 v4	CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v4` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2`	latin1	latin1_swedish_ci
 revoke all privileges on mysqltest.* from mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 create database mysqltest;
 create table mysqltest.t1 (a int, b int, primary key(a));
@@ -176,6 +180,7 @@ insert into mysqltest.t2 values (3), (4), (5), (6);
 create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
 create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;
 create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1;
+create user mysqltest_1@localhost;
 grant update (a) on mysqltest.v2 to mysqltest_1@localhost;
 grant update on mysqltest.v1 to mysqltest_1@localhost;
 grant select on mysqltest.* to mysqltest_1@localhost;
@@ -222,6 +227,7 @@ update v3 set a=a+c;
 ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3'
 use test;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 create database mysqltest;
 create table mysqltest.t1 (a int, b int, primary key(a));
@@ -230,6 +236,7 @@ create table mysqltest.t2 (x int);
 insert into mysqltest.t2 values (3), (4), (5), (6);
 create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
 create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1;
+create user mysqltest_1@localhost;
 grant delete on mysqltest.v1 to mysqltest_1@localhost;
 grant select on mysqltest.* to mysqltest_1@localhost;
 use mysqltest;
@@ -250,6 +257,7 @@ delete from v2 where c < 4;
 ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2'
 use test;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 create database mysqltest;
 create table mysqltest.t1 (a int, b int, primary key(a));
@@ -258,6 +266,7 @@ create table mysqltest.t2 (x int, y int);
 insert into mysqltest.t2 values (3,4);
 create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1;
 create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;
+create user mysqltest_1@localhost;
 grant insert on mysqltest.v1 to mysqltest_1@localhost;
 grant select on mysqltest.* to mysqltest_1@localhost;
 use mysqltest;
@@ -280,10 +289,12 @@ insert into v2 select x,y from t2;
 ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2'
 use test;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 create database mysqltest;
 create table mysqltest.t1 (a int, b int);
 create table mysqltest.t2 (a int, b int);
+create user mysqltest_1@localhost;
 grant update on mysqltest.t1 to mysqltest_1@localhost;
 grant update(b) on mysqltest.t2 to mysqltest_1@localhost;
 grant create view,update on test.* to mysqltest_1@localhost;
@@ -308,18 +319,22 @@ ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column
 grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost;
 create view v4 as select b+1 from mysqltest.t2;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 drop view v1,v2,v4;
 create database mysqltest;
 create table mysqltest.t1 (a int);
+create user mysqltest_1@localhost;
 grant all privileges on mysqltest.* to mysqltest_1@localhost;
 use mysqltest;
 create view v1 as select * from t1;
 use test;
 revoke all privileges on mysqltest.* from mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 create database mysqltest;
 create table mysqltest.t1 (a int, b int);
+create user mysqltest_1@localhost;
 grant select on mysqltest.t1 to mysqltest_1@localhost;
 grant create view,select on test.* to mysqltest_1@localhost;
 create view v1 as select * from mysqltest.t1;
@@ -333,6 +348,7 @@ grant select on mysqltest.t1 to mysqltest_1@localhost;
 select * from v1;
 a	b
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
 drop view v1;
 drop database mysqltest;
 create database mysqltest;
@@ -348,6 +364,7 @@ create algorithm=MERGE view v2 as select f2() from t1;
 create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1;
 create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1;
 create SQL SECURITY INVOKER view v5 as select * from v4;
+create user mysqltest_1@localhost;
 grant select on v1 to mysqltest_1@localhost;
 grant select on v2 to mysqltest_1@localhost;
 grant select on v3 to mysqltest_1@localhost;
@@ -372,6 +389,7 @@ drop function f2;
 drop table t1, t2;
 use test;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 create database mysqltest;
 use mysqltest;
@@ -381,6 +399,7 @@ create table t2 (s1 int);
 drop function if exists f2;
 create function f2 () returns int begin declare v int; select s1 from t2
 into v; return v; end//
+create user mysqltest_1@localhost;
 grant select on t1 to mysqltest_1@localhost;
 grant execute on function f2 to mysqltest_1@localhost;
 grant create view on mysqltest.* to mysqltest_1@localhost;
@@ -409,12 +428,14 @@ drop function f2;
 drop table t1, t2;
 use test;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 create database mysqltest;
 use mysqltest;
 create table t1 (a int);
 create table v1 (a int);
 insert into t1 values (1);
+create user mysqltest_1@localhost;
 grant select on t1 to mysqltest_1@localhost;
 grant select on v1 to mysqltest_1@localhost;
 grant create view on mysqltest.* to mysqltest_1@localhost;
@@ -442,6 +463,7 @@ ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or fun
 drop table t1;
 use test;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 create database mysqltest;
 use mysqltest;
@@ -452,6 +474,7 @@ create algorithm=MERGE view v2 as select *, a as b from t1;
 create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1;
 create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1;
 create SQL SECURITY INVOKER view v5 as select * from v4;
+create user mysqltest_1@localhost;
 grant select on v1 to mysqltest_1@localhost;
 grant select on v2 to mysqltest_1@localhost;
 grant select on v3 to mysqltest_1@localhost;
@@ -475,12 +498,14 @@ drop view v1, v2, v3, v4, v5;
 drop table t1;
 use test;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
 drop database mysqltest;
 drop view if exists v1;
 drop table if exists t1;
 create table t1 as select * from mysql.user where user='';
 delete from mysql.user where user='';
 flush privileges;
+create user 'test14256'@'%';
 grant all on test.* to 'test14256'@'%';
 use test;
 create view v1 as select 42;
@@ -511,6 +536,7 @@ CREATE VIEW  v1 AS SELECT * FROM t1;
 SHOW CREATE VIEW v1;
 View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1`	latin1	latin1_swedish_ci
+create user mysqltest_1@localhost;
 GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost;
 use mysqltest;
 LOCK TABLES v1 READ;
@@ -853,6 +879,7 @@ INSERT INTO mysqltest1.t1 VALUES (11), (12), (13), (14);
 INSERT INTO mysqltest1.t2 VALUES (21), (22), (23), (24);
 INSERT INTO mysqltest1.t3 VALUES (31), (32), (33), (34);
 INSERT INTO mysqltest1.t4 VALUES (41), (42), (43), (44);
+CREATE USER mysqltest_u1@localhost;
 GRANT SELECT ON mysqltest1.t1 TO mysqltest_u1@localhost;
 GRANT INSERT ON mysqltest1.t2 TO mysqltest_u1@localhost;
 GRANT SELECT, UPDATE ON mysqltest1.t3 TO mysqltest_u1@localhost;
@@ -933,6 +960,7 @@ CREATE DATABASE db1;
 USE db1;
 CREATE TABLE t1(f1 INT, f2 INT);
 CREATE VIEW v1 AS SELECT f1, f2 FROM t1;
+CREATE USER foo;
 GRANT SELECT (f1) ON t1 TO foo;
 GRANT SELECT (f1) ON v1 TO foo;
 USE db1;
diff --git a/mysql-test/r/warnings_engine_disabled.result b/mysql-test/r/warnings_engine_disabled.result
index 9fcd3b934c0..d9e490ba18f 100644
--- a/mysql-test/r/warnings_engine_disabled.result
+++ b/mysql-test/r/warnings_engine_disabled.result
@@ -1,3 +1,4 @@
+set sql_mode="";
 create table t1 (id int) engine=InnoDB;
 Warnings:
 Warning	1286	Unknown storage engine 'InnoDB'
@@ -6,6 +7,7 @@ alter table t1 engine=InnoDB;
 Warnings:
 Warning	1286	Unknown storage engine 'InnoDB'
 drop table t1;
+set sql_mode=default;
 SELECT ENGINE, SUPPORT FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='InnoDB';
 ENGINE	SUPPORT
 InnoDB	NO
author	Oleksandr Byelkin <sanja@mariadb.com>	2015-08-11 18:45:38 +0200
committer	Sergei Golubchik <serg@mariadb.org>	2015-09-04 10:33:56 +0200
commit	e3982cead235e9becb1abdbf0e73876c8a6e6b28 (patch)
tree	e02c8ac4c2a978aa12f1f9fdf19ec5303f7e9341 /mysql-test/r
parent	21daa7b9298d31ab1c6ddd1159dba29acea8d868 (diff)
download	mariadb-git-e3982cead235e9becb1abdbf0e73876c8a6e6b28.tar.gz