diff options
Diffstat (limited to 'mysql-test/r')
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 |