diff options
| author | Oleksandr Byelkin <sanja@mariadb.com> | 2015-08-11 18:45:38 +0200 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2015-09-04 10:33:56 +0200 |
| commit | e3982cead235e9becb1abdbf0e73876c8a6e6b28 (patch) | |
| tree | e02c8ac4c2a978aa12f1f9fdf19ec5303f7e9341 /mysql-test/t | |
| parent | 21daa7b9298d31ab1c6ddd1159dba29acea8d868 (diff) | |
| download | mariadb-git-e3982cead235e9becb1abdbf0e73876c8a6e6b28.tar.gz | |
MDEV-6066: Merge new defaults from 5.6 and 5.7 (defaults changed, QC can be stopped with no-zero size)
Diffstat (limited to 'mysql-test/t')
100 files changed, 344 insertions, 86 deletions
diff --git a/mysql-test/t/analyze_stmt_privileges.test b/mysql-test/t/analyze_stmt_privileges.test index 9dd8f274fce..b565f17c0f7 100644 --- a/mysql-test/t/analyze_stmt_privileges.test +++ b/mysql-test/t/analyze_stmt_privileges.test @@ -11,6 +11,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,,) diff --git a/mysql-test/t/analyze_stmt_privileges2.test b/mysql-test/t/analyze_stmt_privileges2.test index 6fcdb7d4399..9a0299be535 100644 --- a/mysql-test/t/analyze_stmt_privileges2.test +++ b/mysql-test/t/analyze_stmt_privileges2.test @@ -24,6 +24,8 @@ --source include/count_sessions.inc --enable_connect_log +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; CREATE DATABASE privtest_db; @@ -5395,6 +5397,7 @@ DROP USER 'privtest'@localhost; USE test; DROP DATABASE privtest_db; +set GLOBAL sql_mode=default; --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/blackhole_plugin.test b/mysql-test/t/blackhole_plugin.test index 0cc7ae0817c..487fc5656f2 100644 --- a/mysql-test/t/blackhole_plugin.test +++ b/mysql-test/t/blackhole_plugin.test @@ -1,8 +1,9 @@ if (!$HA_BLACKHOLE_SO) { --skip Need blackhole plugin } - +set sql_mode=""; CREATE TABLE t1(a int) ENGINE=BLACKHOLE; +set sql_mode=default; DROP TABLE t1; --replace_regex /\.dll/.so/ eval INSTALL PLUGIN blackhole SONAME '$HA_BLACKHOLE_SO'; diff --git a/mysql-test/t/bootstrap.test b/mysql-test/t/bootstrap.test index 97376eb7412..840b9a12cee 100644 --- a/mysql-test/t/bootstrap.test +++ b/mysql-test/t/bootstrap.test @@ -78,6 +78,7 @@ EOF # Check that installed plugins are *not* automatically loaded in --bootstrap # --write_file $MYSQLTEST_VARDIR/tmp/bootstrap_plugins.sql +SET SQL_MODE=""; use test; create table t1(a int) engine=example; EOF diff --git a/mysql-test/t/change_user.test b/mysql-test/t/change_user.test index 659354e9c48..c918224520d 100644 --- a/mysql-test/t/change_user.test +++ b/mysql-test/t/change_user.test @@ -1,10 +1,14 @@ +# This test is checking that old password authentication works +set global secure_auth=0; # # functional change user tests # - +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(); @@ -136,3 +140,5 @@ if ($after != $before){ die The value of com_select changed during change_user; } echo Value of com_select did not change; + +set global secure_auth=default; diff --git a/mysql-test/t/connect.test b/mysql-test/t/connect.test index c4a6fb73b10..20989e70ef9 100644 --- a/mysql-test/t/connect.test +++ b/mysql-test/t/connect.test @@ -12,6 +12,7 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +SET global secure_auth=0; --disable_warnings drop table if exists t1,t2; @@ -134,6 +135,7 @@ drop table t1; --echo # -- which results in user lockout. --echo +CREATE USER mysqltest_u1@localhost; GRANT USAGE ON *.* TO mysqltest_u1@localhost; # NOTE: if the test case fails sporadically due to spurious connections, @@ -452,3 +454,6 @@ delimiter ;| call p1(2); drop procedure p1; + + +SET global secure_auth=default; diff --git a/mysql-test/t/create_or_replace_permission.test b/mysql-test/t/create_or_replace_permission.test index f155e97198c..0e833a125bd 100644 --- a/mysql-test/t/create_or_replace_permission.test +++ b/mysql-test/t/create_or_replace_permission.test @@ -2,6 +2,8 @@ # Grant tests not performed with embedded server -- source include/not_embedded.inc +set local sql_mode=""; +set global sql_mode=""; --echo # --echo # Tests for checking permission denied on CREATE OR REPLACE if DROP --echo # access is revoked @@ -64,3 +66,6 @@ REVOKE ALL ON db1.* FROM mysqltest_1@localhost; DROP DATABASE IF EXISTS db2; DROP DATABASE db1; DROP USER mysqltest_1@localhost; + + +set global sql_mode=default; diff --git a/mysql-test/t/ctype_latin2_ch.test b/mysql-test/t/ctype_latin2_ch.test index c8ea54e6091..06ccd2c9782 100644 --- a/mysql-test/t/ctype_latin2_ch.test +++ b/mysql-test/t/ctype_latin2_ch.test @@ -144,6 +144,7 @@ SELECT * FROM t1 GROUP BY s1; SELECT * FROM t1 ORDER BY s1; DROP TABLE t1; +set sql_mode=""; --disable_warnings CREATE TABLE t1 ENGINE=INNODB AS SELECT repeat('a', 5) AS s1 LIMIT 0; --enable_warnings @@ -183,6 +184,7 @@ DROP TABLE t1; SET NAMES latin2; + # # Bug#37854 Test fails/aborts for collate latin2_czech_cs used with SET and ENUM datatypes # diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index 6e5b0850eca..5dd0a5f426c 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -440,9 +440,11 @@ drop table t1; # # Bug #14583 Bug on query using a LIKE on indexed field with ucs2_bin collation # +set sql_mode=""; --disable_warnings create table t1(f1 varchar(5) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL) engine=InnoDB; --enable_warnings +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/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test index 6fc928c7e60..3f444580b13 100644 --- a/mysql-test/t/ctype_ujis.test +++ b/mysql-test/t/ctype_ujis.test @@ -87,6 +87,7 @@ DROP TABLE t1; # # Bug #6345 Unexpected behaviour with partial indices # +set sql_mode=""; --disable_warnings CREATE TABLE t1 ( @@ -96,6 +97,7 @@ CREATE TABLE t1 KEY b (b(10)) ) ENGINE=InnoDB CHARACTER SET 'ujis' COLLATE 'ujis_japanese_ci'; --enable_warnings +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/t/ddl_i18n_koi8r.test b/mysql-test/t/ddl_i18n_koi8r.test index 40f8d07c55d..0a62a8ba0f8 100644 --- a/mysql-test/t/ddl_i18n_koi8r.test +++ b/mysql-test/t/ddl_i18n_koi8r.test @@ -24,6 +24,8 @@ # - restore object; # +set sql_mode=""; + ########################################################################### # # NOTE: this file contains text in UTF8 and KOI8-R encodings. diff --git a/mysql-test/t/ddl_i18n_utf8.test b/mysql-test/t/ddl_i18n_utf8.test index 35109e62100..023047b952e 100644 --- a/mysql-test/t/ddl_i18n_utf8.test +++ b/mysql-test/t/ddl_i18n_utf8.test @@ -30,6 +30,8 @@ # ########################################################################### +set sql_mode=""; + # Test requires server to accept client connections (for mysqldump portions) --source include/not_embedded.inc --source include/have_utf8.inc diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test index 6904d9bb242..17f4383ae22 100644 --- a/mysql-test/t/default.test +++ b/mysql-test/t/default.test @@ -9,6 +9,7 @@ drop database if exists mysqltest; # Bug 10838 # Insert causes warnings for no default values and corrupts tables # +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', @@ -39,6 +40,7 @@ CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ', b1 tinyblob NULL) ENGINE=InnoDB DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin; --enable_warnings +set sql_mode=default; INSERT into t1 (b) values ('1'); SHOW WARNINGS; diff --git a/mysql-test/t/delete_returning_grant.test b/mysql-test/t/delete_returning_grant.test index f67a766b936..67613603a72 100644 --- a/mysql-test/t/delete_returning_grant.test +++ b/mysql-test/t/delete_returning_grant.test @@ -3,6 +3,9 @@ # --source include/not_embedded.inc +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'); @@ -76,3 +79,4 @@ DROP USER mysqltest_1@localhost; DROP VIEW v1; DROP TABLE t1; +set GLOBAL sql_mode=default; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 67899837bb2..3d9ab4e4477 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1600,7 +1600,7 @@ CREATE TABLE IF NOT EXISTS `galleries` ( `year` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `pictures` ( `id` int(11) NOT NULL AUTO_INCREMENT, @@ -1614,7 +1614,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 ; +) DEFAULT CHARSET=utf8 ; ALTER TABLE `pictures` ADD CONSTRAINT `pictures_ibfk_1` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`); diff --git a/mysql-test/t/enforce_storage_engine.test b/mysql-test/t/enforce_storage_engine.test index 8256fcd59fa..63f600d1879 100644 --- a/mysql-test/t/enforce_storage_engine.test +++ b/mysql-test/t/enforce_storage_engine.test @@ -1,5 +1,7 @@ -- source include/not_embedded.inc +set local sql_mode=""; +set global sql_mode=""; --disable_warnings drop table if exists t1; --enable_warnings @@ -82,5 +84,6 @@ DROP TABLE t1; connection default; disconnect con1; +set global sql_mode=default; SET SESSION enforce_storage_engine=NULL; SET GLOBAL enforce_storage_engine=NULL; diff --git a/mysql-test/t/enforce_storage_engine_opt.test b/mysql-test/t/enforce_storage_engine_opt.test index 82cc4117cdb..7ca7f94d3dc 100644 --- a/mysql-test/t/enforce_storage_engine_opt.test +++ b/mysql-test/t/enforce_storage_engine_opt.test @@ -1,3 +1,4 @@ +SET SQL_MODE=""; create table t1 (c1 int primary key auto_increment, c2 varchar(10)) engine=memory; show create table t1; set session sql_mode='no_engine_substitution'; diff --git a/mysql-test/t/events_1.test b/mysql-test/t/events_1.test index 89e8f9ca7dc..7cd488bc376 100644 --- a/mysql-test/t/events_1.test +++ b/mysql-test/t/events_1.test @@ -4,6 +4,8 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc +set sql_mode=""; + call mtr.add_suppression("Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted"); --disable_warnings diff --git a/mysql-test/t/events_2.test b/mysql-test/t/events_2.test index 3d609654b21..ec69a2a5591 100644 --- a/mysql-test/t/events_2.test +++ b/mysql-test/t/events_2.test @@ -3,6 +3,8 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc +set sql_mode=""; + --disable_warnings drop database if exists events_test; --enable_warnings diff --git a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test index 1e93917b08e..c0157a7727e 100644 --- a/mysql-test/t/events_bugs.test +++ b/mysql-test/t/events_bugs.test @@ -8,6 +8,7 @@ # Bug#41925 Warning 1366 Incorrect string value: ... for column processlist.info # # Please set $ +SET SQL_MODE=""; let $fixed_bug41925= 0; # # Dear maintainer of this test. Please do NOT remove the next big comment. @@ -1319,5 +1320,4 @@ DROP DATABASE events_test; SET GLOBAL event_scheduler= 'ON'; --source include/running_event_scheduler.inc SET @@global.concurrent_insert= @concurrent_insert; - # THIS MUST BE THE LAST LINE in this file. diff --git a/mysql-test/t/events_trans.test b/mysql-test/t/events_trans.test index 6d829379fea..eea24f19577 100644 --- a/mysql-test/t/events_trans.test +++ b/mysql-test/t/events_trans.test @@ -4,6 +4,8 @@ -- source include/have_innodb.inc -- source include/not_embedded.inc +set sql_mode=""; + --disable_warnings drop database if exists events_test; drop database if exists mysqltest_no_such_database; diff --git a/mysql-test/t/events_trans_notembedded.test b/mysql-test/t/events_trans_notembedded.test index 0353d183386..3cf823bf713 100644 --- a/mysql-test/t/events_trans_notembedded.test +++ b/mysql-test/t/events_trans_notembedded.test @@ -13,6 +13,7 @@ use events_test; # # Privilege checks # +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/t/features.test b/mysql-test/t/features.test index f2ac5a5bba6..225ab40b361 100644 --- a/mysql-test/t/features.test +++ b/mysql-test/t/features.test @@ -6,6 +6,8 @@ drop table if exists t1; --enable_warnings +set sql_mode=""; + flush status; show status like "feature%"; diff --git a/mysql-test/t/fix_priv_tables.test b/mysql-test/t/fix_priv_tables.test index f68c8b518c8..293d3915a05 100644 --- a/mysql-test/t/fix_priv_tables.test +++ b/mysql-test/t/fix_priv_tables.test @@ -22,6 +22,8 @@ if (!$MYSQL_FIX_PRIVILEGE_TABLES) drop table if exists t1,t1aa,t2aa; --enable_warnings +set sql_mode=""; + # # Bug #20589 Missing some table level privileges after upgrade # diff --git a/mysql-test/t/flush-innodb-notembedded.test b/mysql-test/t/flush-innodb-notembedded.test index d08a0647ff5..15bfeb53475 100644 --- a/mysql-test/t/flush-innodb-notembedded.test +++ b/mysql-test/t/flush-innodb-notembedded.test @@ -1,6 +1,8 @@ --source include/have_innodb.inc --source include/not_embedded.inc +set sql_mode=""; + --echo # Test 7: Check privileges required. --echo # diff --git a/mysql-test/t/flush_read_lock.test b/mysql-test/t/flush_read_lock.test index e8ec07392b5..7ba80ea38ac 100644 --- a/mysql-test/t/flush_read_lock.test +++ b/mysql-test/t/flush_read_lock.test @@ -13,6 +13,8 @@ # Save the initial number of concurrent sessions. --source include/count_sessions.inc +set global sql_mode=""; +set local sql_mode=""; --echo # FTWRL takes two global metadata locks -- a global shared --echo # metadata lock and the commit blocker lock. --echo # The first lock prevents DDL from taking place. @@ -2161,6 +2163,7 @@ disconnect con1; disconnect con2; disconnect con3; +set global sql_mode=default; # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test index fc3d2697426..2a06769b2ef 100644 --- a/mysql-test/t/func_compress.test +++ b/mysql-test/t/func_compress.test @@ -5,6 +5,10 @@ # Note that this test gives error in the gzip library when running under # valgrind, but these warnings can be ignored +set global max_allowed_packet=1048576; +connect (conn1,localhost,root,,); +connection conn1; + select @test_compress_string:='string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa '; select length(@test_compress_string); @@ -142,6 +146,9 @@ DROP TABLE t1; # SELECT UNCOMPRESS(CAST(0 AS BINARY(5))); +disconnect conn1; +connection default; +set global max_allowed_packet=default; --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 2998742dbcc..b48f4599074 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -136,9 +136,11 @@ DROP TABLE t1; # # InnoDB is required to reproduce the fault, but it is okay if we default to # MyISAM when testing. +set sql_mode=""; --disable_warnings create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8; --enable_warnings +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)); drop table t1; diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index ef6472a3848..364266d7f6f 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -379,7 +379,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; +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/t/func_str.test b/mysql-test/t/func_str.test index 95b742d3545..726397bc2a7 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -6,6 +6,10 @@ drop table if exists t1,t2; --enable_warnings +set global max_allowed_packet=1048576; +connect (conn1,localhost,root,,); +connection conn1; + let $mysqld_datadir= `select @@datadir`; set names latin1; @@ -1562,7 +1566,7 @@ round( ) ); ---connection default +--connection conn1 SET @@global.max_allowed_packet:= @tmp_max; --disconnect newconn # @@ -1739,6 +1743,9 @@ EXECUTE stmt; SET NAMES latin2; EXECUTE stmt; +disconnect conn1; +connection default; +set global max_allowed_packet=default; --echo # --echo # End of 5.6 tests diff --git a/mysql-test/t/func_weight_string.test b/mysql-test/t/func_weight_string.test index 6c34ce1e460..cf6524e9753 100644 --- a/mysql-test/t/func_weight_string.test +++ b/mysql-test/t/func_weight_string.test @@ -2,6 +2,11 @@ drop table if exists t1; --enable_warnings + +set global max_allowed_packet=1048576; +connect (conn1,localhost,root,,); +connection conn1; + set names latin1; # @@ -111,3 +116,6 @@ drop table t1; SELECT HEX(WEIGHT_STRING('ab' AS CHAR(1000000000000000000))); SELECT HEX(WEIGHT_STRING('ab' AS BINARY(1000000000000000000))); +disconnect conn1; +connection default; +set global max_allowed_packet=default; diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index b1580383f22..1d828cd8693 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -6,6 +6,8 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +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; @@ -2208,9 +2210,6 @@ disconnect con1; DROP USER untrusted@localhost; DROP DATABASE secret; -# Wait till we reached the initial number of concurrent sessions ---source include/wait_until_count_sessions.inc - --echo # --echo # BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES --echo # DIFFERENTLY'. @@ -2233,3 +2232,7 @@ grant select on mysqltest_db1.t1 to mysqltest_u1; show grants for mysqltest_u1; drop database mysqltest_db1; drop user mysqltest_u1; + +set GLOBAL sql_mode=default; +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index 8590dccd1d1..a40fd2afb18 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -5,6 +5,8 @@ --source include/count_sessions.inc +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; SET NAMES binary; # @@ -1029,5 +1031,6 @@ DROP USER mysqltest_u4@localhost; DROP USER mysqltest_u5@localhost; +set GLOBAL sql_mode=default; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/grant3.test b/mysql-test/t/grant3.test index d24b2de17eb..27f565916f7 100644 --- a/mysql-test/t/grant3.test +++ b/mysql-test/t/grant3.test @@ -4,6 +4,8 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +set global sql_mode=""; +set local sql_mode=""; # Test of GRANT commands @@ -204,6 +206,7 @@ connection default; DROP USER 'user2'@'%'; DROP DATABASE temp; +set global sql_mode=default; --echo End of 5.0 tests # Wait till we reached the initial number of concurrent sessions diff --git a/mysql-test/t/grant4.test b/mysql-test/t/grant4.test index 36c44fe997a..a3578c9b85a 100644 --- a/mysql-test/t/grant4.test +++ b/mysql-test/t/grant4.test @@ -4,6 +4,8 @@ --disable_warnings drop database if exists mysqltest_db1; --enable_warnings +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); @@ -232,3 +234,6 @@ create user foo2 identified via mysql_native_password using '2222222222222222'; create user foo3 identified via mysql_old_password using '00'; --error ER_PASSWD_LENGTH create user foo4 identified via mysql_old_password using '11111111111111111111111111111111111111111'; + + +set GLOBAL sql_mode=default; diff --git a/mysql-test/t/grant_4332.test b/mysql-test/t/grant_4332.test index d3c3b2d5652..41e0b822f98 100644 --- a/mysql-test/t/grant_4332.test +++ b/mysql-test/t/grant_4332.test @@ -6,6 +6,8 @@ # --source include/not_embedded.inc +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 ''; @@ -40,3 +42,4 @@ flush privileges; select user(); --disable_metadata +set GLOBAL sql_mode=default; diff --git a/mysql-test/t/grant_explain_non_select.test b/mysql-test/t/grant_explain_non_select.test index d59a8f3d8ce..701586be325 100644 --- a/mysql-test/t/grant_explain_non_select.test +++ b/mysql-test/t/grant_explain_non_select.test @@ -8,6 +8,9 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; + CREATE DATABASE privtest_db; CREATE TABLE privtest_db.t1 (a INT); @@ -254,5 +257,7 @@ DROP USER 'privtest'@localhost; USE test; DROP DATABASE privtest_db; +set GLOBAL sql_mode=default; +set LOCAL sql_mode=default; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/grant_lowercase.test b/mysql-test/t/grant_lowercase.test index 4b27b4c674d..e31a72b890e 100644 --- a/mysql-test/t/grant_lowercase.test +++ b/mysql-test/t/grant_lowercase.test @@ -4,7 +4,7 @@ # # http://seclists.org/fulldisclosure/2012/Dec/4 # - +set sql_mode=""; # in acl_get(), check_grant_db(), mysql_grant() grant file on *.* to user1@localhost with grant option; grant select on `a%`.* to user1@localhost with grant option; @@ -28,4 +28,3 @@ delete from mysql.db where db like '=>%'; alter table mysql.host modify Db char(64) default ""; alter table mysql.db modify Db char(64) default "", modify User char(80) default ""; flush privileges; - diff --git a/mysql-test/t/help.test b/mysql-test/t/help.test index 71821e46771..43e61136e05 100644 --- a/mysql-test/t/help.test +++ b/mysql-test/t/help.test @@ -63,12 +63,14 @@ help 'impossible_function_1'; help 'impossible_category_1'; ############## +set sql_mode=""; --disable_warnings 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; --enable_warnings +set sql_mode=default; ############## help 'function_of_my_dream'; diff --git a/mysql-test/t/host_cache_size_functionality.test b/mysql-test/t/host_cache_size_functionality.test index 1696c2fcb97..5a7376cc5eb 100644 --- a/mysql-test/t/host_cache_size_functionality.test +++ b/mysql-test/t/host_cache_size_functionality.test @@ -35,7 +35,7 @@ SELECT COUNT(@@GLOBAL.Host_Cache_Size); --echo 1 Expected #set @Default_host_cache_size=(select if(if(@@global.max_connections<500,128+@@global.max_connections,128+@@global.max_connections+floor((@@global.max_connections-500)/20))>2000,2000,if(@@global.max_connections<500,128+@@global.max_connections,128+@@global.max_connections+floor((@@global.max_connections-500)/20)))); -set @Default_host_cache_size=128; +set @Default_host_cache_size=279; select @@global.Host_Cache_Size=@Default_host_cache_size; --echo 1 Expected diff --git a/mysql-test/t/implicit_commit.test b/mysql-test/t/implicit_commit.test index 59f8dc3a44c..bcc43865395 100644 --- a/mysql-test/t/implicit_commit.test +++ b/mysql-test/t/implicit_commit.test @@ -2,6 +2,8 @@ source include/have_innodb.inc; source include/not_embedded.inc; source include/have_profiling.inc; +SET SQL_MODE=""; + SET GLOBAL EVENT_SCHEDULER = OFF; SET BINLOG_FORMAT = STATEMENT; diff --git a/mysql-test/t/index_intersect.test b/mysql-test/t/index_intersect.test index 001c698029e..19918c03479 100644 --- a/mysql-test/t/index_intersect.test +++ b/mysql-test/t/index_intersect.test @@ -428,7 +428,7 @@ CREATE TABLE t1 ( 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), diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index cd98bf3aa5c..f1e78441852 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -14,6 +14,8 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +set global sql_mode=""; +set local sql_mode=""; # Test for information_schema.schemata & # show databases @@ -1907,3 +1909,4 @@ disconnect con1; # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc +set global sql_mode=default; diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index 5b730db47cd..70ad9b5b920 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -7,6 +7,9 @@ -- source include/testdb_only.inc +set local sql_mode=""; +set global sql_mode=""; + --disable_warnings drop table if exists t1,t2; drop view if exists v1,v2; @@ -250,3 +253,5 @@ connection user1; disconnect user1; --source include/wait_until_disconnected.inc connection default; + +set global sql_mode=default; diff --git a/mysql-test/t/information_schema_routines.test b/mysql-test/t/information_schema_routines.test index c578176a85d..190410c2b82 100644 --- a/mysql-test/t/information_schema_routines.test +++ b/mysql-test/t/information_schema_routines.test @@ -54,6 +54,8 @@ # the routine; otherwise NULL), # DEFINER (shows the user who created the routine). ################################################################################ +set sql_mode=""; +set sql_mode=""; -- echo # ========== routines.1 ========== USE INFORMATION_SCHEMA; --replace_result ENGINE=MyISAM "" ENGINE=MARIA "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" "" diff --git a/mysql-test/t/insert_notembedded.test b/mysql-test/t/insert_notembedded.test index 4e5fe6f6755..713eaf5db40 100644 --- a/mysql-test/t/insert_notembedded.test +++ b/mysql-test/t/insert_notembedded.test @@ -1,5 +1,8 @@ -- source include/not_embedded.inc +set local sql_mode=""; +set global sql_mode=""; + --disable_warnings drop table if exists t1; --enable_warnings @@ -188,3 +191,6 @@ disconnect select; 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/t/kill.test b/mysql-test/t/kill.test index b762900f1ec..aaf931a2e7f 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -10,6 +10,8 @@ -- source include/not_embedded.inc -- source include/have_debug_sync.inc -- source include/not_threadpool.inc +set local sql_mode=""; +set global sql_mode=""; --disable_warnings SET DEBUG_SYNC = 'RESET'; @@ -675,3 +677,5 @@ DROP USER u1@localhost; SET DEBUG_SYNC = 'RESET'; DROP FUNCTION MY_KILL; + +set global sql_mode=default; diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 753b6bd6379..efd0fe5d630 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -324,9 +324,11 @@ DROP DATABASE mysqltest_1; # connection locker; # Disable warnings to allow test to run also without InnoDB +set sql_mode=""; --disable_warnings create table t1 (f1 int(12) unsigned not null auto_increment, primary key(f1)) engine=innodb; --enable_warnings +set sql_mode=default; lock tables t1 write; connection writer; send diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test index 28f6e6dd829..a4158ba6a24 100644 --- a/mysql-test/t/log_tables.test +++ b/mysql-test/t/log_tables.test @@ -6,6 +6,7 @@ # check that CSV engine was compiled in --source include/have_csv.inc +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/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test index 1be0351f9bc..cb5cd711c7f 100644 --- a/mysql-test/t/lowercase_fs_off.test +++ b/mysql-test/t/lowercase_fs_off.test @@ -5,6 +5,8 @@ -- source include/have_case_sensitive_file_system.inc -- source include/not_embedded.inc +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; connect (master,localhost,root,,); connection master; create database d1; @@ -102,3 +104,5 @@ 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/t/lowercase_table_grant.test b/mysql-test/t/lowercase_table_grant.test index 7449231fca5..2f98f570e29 100644 --- a/mysql-test/t/lowercase_table_grant.test +++ b/mysql-test/t/lowercase_table_grant.test @@ -3,6 +3,7 @@ # Test of grants when lower_case_table_names is on use mysql; +set sql_mode=""; # mixed-case database name for testing create database MYSQLtest; diff --git a/mysql-test/t/lowercase_table_qcache.test b/mysql-test/t/lowercase_table_qcache.test index e63ad3b2c16..ab05755bec1 100644 --- a/mysql-test/t/lowercase_table_qcache.test +++ b/mysql-test/t/lowercase_table_qcache.test @@ -3,6 +3,8 @@ # Test of query cache with --lower-case-table-names # set GLOBAL query_cache_size=1355776; +set GLOBAL query_cache_type=ON; +set LOCAL query_cache_type=ON; --disable_warnings drop database if exists MySQLtesT; @@ -26,6 +28,7 @@ select * from MySQL.db; enable_result_log; show status like "Qcache_queries_in_cache"; -set GLOBAL query_cache_size=0; +set GLOBAL query_cache_size=default; +set GLOBAL query_cache_type=default; # End of 4.1 tests diff --git a/mysql-test/t/max_statement_time.test b/mysql-test/t/max_statement_time.test index a25a97e1db3..0882daff139 100644 --- a/mysql-test/t/max_statement_time.test +++ b/mysql-test/t/max_statement_time.test @@ -79,6 +79,7 @@ DROP TABLE t1; --echo # MAX_STATEMENT_TIME account resource --echo +set statement sql_mode="" for GRANT USAGE ON *.* TO user1@localhost WITH MAX_STATEMENT_TIME 1.005; --echo # con1 diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 2697e3b9a5f..5bfe22697dd 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -1895,13 +1895,14 @@ DROP TABLE t1; --disable_warnings drop table if exists t_parent; --enable_warnings -set @save_table_definition_cache=@@global.table_definition_cache; +set @save_table_open_cache=@@global.table_open_cache; --echo # ---echo # Set @@global.table_definition_cache to minimum +--echo # Set @@global.table_open_cache to minimum --echo # -set @@global.table_definition_cache=400; +set @@global.table_open_cache=400; +select @@table_open_cache; set @a=null; -let $1 = 400; +let $1 = `select @@table_open_cache;`; --echo # --echo # Create 400 merge children --echo # @@ -1935,7 +1936,7 @@ deallocate prepare stmt; --echo # --echo # Cleanup --echo # -let $1 = 400; +let $1 = `select @@table_open_cache;`; --disable_query_log while ($1) { @@ -1944,7 +1945,7 @@ while ($1) } --enable_query_log drop table t_parent; -set @@global.table_definition_cache=@save_table_definition_cache; +set @@global.table_open_cache=@save_table_open_cache; # # WL#4144 - Lock MERGE engine children diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index c013938416e..8c620ed2ce4 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -378,6 +378,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; connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index c328ab19ffd..f1813dd0ca4 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -4,6 +4,8 @@ # # Testing the MySQL command line client(mysql) # +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; --disable_warnings drop table if exists t1,t2,t3; @@ -632,5 +634,6 @@ EOF --exec $MYSQL --default-character-set=utf8 < $MYSQLTEST_VARDIR/tmp/mdev-6572.sql 2>&1 --remove_file $MYSQLTEST_VARDIR/tmp/mdev-6572.sql +set GLOBAL sql_mode=default; --echo --echo End of tests diff --git a/mysql-test/t/mysql_comments.test b/mysql-test/t/mysql_comments.test index 1f997aeb1ab..7b00f17e259 100644 --- a/mysql-test/t/mysql_comments.test +++ b/mysql-test/t/mysql_comments.test @@ -10,6 +10,8 @@ # # ######################################################################## +set global sql_mode=""; + # # Bug #11230: Keeping comments when storing stored procedures # @@ -34,4 +36,6 @@ drop trigger if exists t1_bi; --echo "Pass 2 : --enable-comments" --exec $MYSQL --enable-comments test 2>&1 < "./t/mysql_comments.sql" + +set global sql_mode=default; --echo End of 5.0 tests diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test index a24ba0de2d9..552bb12ec74 100644 --- a/mysql-test/t/mysql_upgrade.test +++ b/mysql-test/t/mysql_upgrade.test @@ -2,6 +2,8 @@ -- source include/have_working_dns.inc -- source include/have_innodb.inc +set sql_mode=""; + # # Basic test that we can run mysql_upgrde and that it finds the # expected binaries it uses. @@ -158,4 +160,5 @@ SELECT grantor FROM mysql.tables_priv WHERE db = 'mysql' AND table_name = 'user' 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; --echo End of tests diff --git a/mysql-test/t/mysqlbinlog-innodb.test b/mysql-test/t/mysqlbinlog-innodb.test index 49702e8db38..c8bf8095705 100644 --- a/mysql-test/t/mysqlbinlog-innodb.test +++ b/mysql-test/t/mysqlbinlog-innodb.test @@ -1,7 +1,6 @@ -- source include/have_binlog_format_statement.inc -- source include/have_log_bin.inc -- source include/have_innodb.inc - # # MBug#702303: Spurious `use` statements in output from mysqlbinlog --rewrite-db="a->b" # diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test index e36f106a5be..8c81bdc9bbb 100644 --- a/mysql-test/t/openssl_1.test +++ b/mysql-test/t/openssl_1.test @@ -6,6 +6,8 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +set local sql_mode=""; +set global sql_mode=""; --disable_warnings drop table if exists t1; @@ -263,6 +265,7 @@ disconnect con1; connection default; DROP USER bug42158@localhost; +set global sql_mode=default; --echo End of 5.1 tests # Wait till we reached the initial number of concurrent sessions diff --git a/mysql-test/t/openssl_6975.test b/mysql-test/t/openssl_6975.test index bc6397c5c28..88fe17d0621 100644 --- a/mysql-test/t/openssl_6975.test +++ b/mysql-test/t/openssl_6975.test @@ -7,7 +7,9 @@ source include/have_ssl_communication.inc; # this is OpenSSL test. +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"; let $mysql=$MYSQL --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem -e "SHOW STATUS LIKE 'ssl_Cipher'" 2>&1; diff --git a/mysql-test/t/partition_grant.test b/mysql-test/t/partition_grant.test index cf944e507fd..a5df218bb10 100644 --- a/mysql-test/t/partition_grant.test +++ b/mysql-test/t/partition_grant.test @@ -21,6 +21,7 @@ insert into t1 values (1),(2); # cleanup possible grants for mysqltest_1 left by previous tests # and ensure consistent results of SHOW GRANTS command below. --disable_warnings +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost; revoke all privileges on *.* from mysqltest_1@localhost; --enable_warnings diff --git a/mysql-test/t/partition_hash.test b/mysql-test/t/partition_hash.test index 5b9aa5707b9..c8f473a0477 100644 --- a/mysql-test/t/partition_hash.test +++ b/mysql-test/t/partition_hash.test @@ -135,12 +135,14 @@ drop table t1; # # Disable warnings to allow this test case to run without # the Blackhole storage engine. +set sql_mode=""; --disable_warnings CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1); --enable_warnings --error 0,ER_BINLOG_LOGGING_IMPOSSIBLE INSERT INTO t1 VALUES (0); DROP TABLE t1; +set sql_mode=default; # # BUG 18423 Hash partitioning can lose rows in some queries diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index 2d90764da0d..b982ab8d2e8 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -478,6 +478,7 @@ drop table t1; # # Bug 20397: Partitions: Crash when using non-existing engine # +SET SQL_MODE=""; create table t1 (a int) engine = x partition by key (a); @@ -492,6 +493,7 @@ partition by list (a) alter table t1 engine = x; show create table t1; drop table t1; +SET SQL_MODE=default; # BUG#26117: index_merge sort-union over partitioned table crashes diff --git a/mysql-test/t/plugin.test b/mysql-test/t/plugin.test index ec7117bb832..68c4d5afd64 100644 --- a/mysql-test/t/plugin.test +++ b/mysql-test/t/plugin.test @@ -1,8 +1,8 @@ --source include/not_windows_embedded.inc --source include/have_example_plugin.inc +--error ER_UNKNOWN_STORAGE_ENGINE CREATE TABLE t1(a int) ENGINE=EXAMPLE; -DROP TABLE t1; INSTALL PLUGIN example SONAME 'ha_example'; --replace_regex /\.dll/.so/ diff --git a/mysql-test/t/plugin_auth.test b/mysql-test/t/plugin_auth.test index 5e620654e0a..c5d96e0d612 100644 --- a/mysql-test/t/plugin_auth.test +++ b/mysql-test/t/plugin_auth.test @@ -3,6 +3,9 @@ --source include/mysql_upgrade_preparation.inc --source include/have_innodb.inc +SET GLOBAL SQL_MODE=""; +SET LOCAL SQL_MODE=""; + query_vertical SELECT PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_DESCRIPTION FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='test_plugin_server'; @@ -60,7 +63,7 @@ disconnect plug_con_rightp; --echo ## 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; --echo ## test utf-8 user name @@ -580,4 +583,5 @@ disconnect bug12818542_con2; DROP USER bug12818542@localhost; DROP USER bug12818542_dest@localhost; +SET GLOBAL SQL_MODE=default; --echo End of 5.5 tests diff --git a/mysql-test/t/plugin_auth_qa.test b/mysql-test/t/plugin_auth_qa.test index 3a3921510e0..fce72a66702 100644 --- a/mysql-test/t/plugin_auth_qa.test +++ b/mysql-test/t/plugin_auth_qa.test @@ -3,6 +3,8 @@ --source include/have_plugin_auth.inc --source include/not_embedded.inc +set sql_mode=""; + CREATE DATABASE test_user_db; --sorted_result @@ -332,5 +334,6 @@ DROP USER grant_user,plug_dest; set @@sql_mode= @save_sql_mode; # DROP DATABASE test_user_db; + --exit diff --git a/mysql-test/t/plugin_not_embedded.test b/mysql-test/t/plugin_not_embedded.test index 3e3bd6e86ca..3b4129b6b6b 100644 --- a/mysql-test/t/plugin_not_embedded.test +++ b/mysql-test/t/plugin_not_embedded.test @@ -5,6 +5,7 @@ --echo # Bug#51770: UNINSTALL PLUGIN requires no privileges --echo # +CREATE USER bug51770@localhost; GRANT INSERT ON mysql.plugin TO bug51770@localhost; connect(con1,localhost,bug51770,,); --replace_regex /\.dll/.so/ @@ -29,4 +30,3 @@ let $path = `select CONCAT_WS('/', '..', '$HA_EXAMPLE_SO')`; --replace_regex /\.dll/.so/ --error ER_UDF_NO_PATHS eval INSTALL PLUGIN example SONAME '$path'; - diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 84c9874260a..96dd88f5eb6 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1,5 +1,6 @@ -- source include/not_embedded.inc -- source include/have_log_bin.inc + call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); # # SQL Syntax for Prepared Statements test diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 387dcd205bd..a97b0b1c815 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -4,7 +4,16 @@ # # Tests with query cache # +SET GLOBAL query_cache_type= Off; +SET LOCAL query_cache_type= Off; +SET LOCAL query_cache_type= 0; +--error ER_QUERY_CACHE_IS_GLOBALY_DISABLED +SET LOCAL query_cache_type= on; +--error ER_QUERY_CACHE_IS_GLOBALY_DISABLED +SET LOCAL query_cache_type= 1; set GLOBAL query_cache_size=1355776; +SET GLOBAL query_cache_type= ON; +SET LOCAL query_cache_type= ON; # Reset query cache variables. @@ -1204,9 +1213,8 @@ set global query_cache_size=102400; set global query_cache_size=0; set global query_cache_type=0; show status like 'Qcache_free_blocks'; - ---error ER_QUERY_CACHE_DISABLED set global query_cache_size=102400; +show status like 'Qcache_free_blocks'; set global query_cache_type=on; set session query_cache_type=on; @@ -1260,6 +1268,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; # # Bug#33756 - query cache with concurrent_insert=0 appears broken diff --git a/mysql-test/t/query_cache_debug.test b/mysql-test/t/query_cache_debug.test index 854af85f3fb..5eba778cf9e 100644 --- a/mysql-test/t/query_cache_debug.test +++ b/mysql-test/t/query_cache_debug.test @@ -3,6 +3,7 @@ --source include/have_debug_sync.inc --source include/long_test.inc +set global query_cache_type= ON; # # Bug #30887 Server crashes on SET GLOBAL query_cache_size=0 # diff --git a/mysql-test/t/query_cache_merge.test b/mysql-test/t/query_cache_merge.test index 189de8e4092..3046c4ecbfe 100644 --- a/mysql-test/t/query_cache_merge.test +++ b/mysql-test/t/query_cache_merge.test @@ -1,5 +1,8 @@ # Test query cache with many tables +SET GLOBAL query_cache_type=ON; +SET LOCAL query_cache_type=ON; + --source include/have_query_cache.inc let $LIMIT=`SHOW VARIABLES LIKE 'open_files_limit'`; let $MIN_LIMIT=600; @@ -102,7 +105,8 @@ while ($c) dec $c; } -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; --echo End of 5.1 tests diff --git a/mysql-test/t/query_cache_notembedded-master.opt b/mysql-test/t/query_cache_notembedded-master.opt new file mode 100644 index 00000000000..871d6965726 --- /dev/null +++ b/mysql-test/t/query_cache_notembedded-master.opt @@ -0,0 +1 @@ +--query-cache-size=1355776 diff --git a/mysql-test/t/query_cache_notembedded.test b/mysql-test/t/query_cache_notembedded.test index edb6db43516..dff6c2a139b 100644 --- a/mysql-test/t/query_cache_notembedded.test +++ b/mysql-test/t/query_cache_notembedded.test @@ -8,7 +8,12 @@ # # Tests with query cache # -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=""; # Reset query cache variables. @@ -270,7 +275,8 @@ DROP DATABASE bug30269; disconnect 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; @@ -324,3 +330,7 @@ delimiter ;| # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc +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/t/query_cache_with_views.test b/mysql-test/t/query_cache_with_views.test index 97e37c53aa3..9b03d0e99d0 100644 --- a/mysql-test/t/query_cache_with_views.test +++ b/mysql-test/t/query_cache_with_views.test @@ -6,7 +6,8 @@ drop table if exists t1,t2,v1,v2,v3; drop view if exists t1,t2,v1,v2,v3; --enable_warnings - +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); @@ -145,3 +146,4 @@ SET AUTOCOMMIT=DEFAULT; # Reset default environment. set GLOBAL query_cache_size=default; +set GLOBAL query_cache_type=default; diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test index d0f397697d5..3e5d4fc8ce1 100644 --- a/mysql-test/t/read_only.test +++ b/mysql-test/t/read_only.test @@ -3,7 +3,6 @@ # should work with embedded server after mysqltest is fixed --source include/not_embedded.inc - set @start_read_only= @@global.read_only; # Save the initial number of concurrent sessions @@ -16,6 +15,7 @@ DROP TABLE IF EXISTS t1,t2,t3; # READ_ONLY does nothing to SUPER users # so we use a non-SUPER one: +create user test@localhost; grant CREATE, SELECT, DROP on *.* to test@localhost; --echo connect (con1,localhost,test,,test); @@ -278,6 +278,7 @@ 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`@`%`; @@ -364,4 +365,3 @@ DROP TABLE t1; # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc - diff --git a/mysql-test/t/read_only_innodb.test b/mysql-test/t/read_only_innodb.test index de237fecbb6..f404ba17579 100644 --- a/mysql-test/t/read_only_innodb.test +++ b/mysql-test/t/read_only_innodb.test @@ -13,6 +13,7 @@ DROP TABLE IF EXISTS table_11733 ; # READ_ONLY does nothing to SUPER users # so we use a non-SUPER one: +create user test@localhost; grant CREATE, SELECT, DROP on *.* to test@localhost; connect (con1,localhost,test,,test); @@ -47,6 +48,7 @@ disconnect con1; # Bug #35732: read-only blocks SELECT statements in InnoDB # # Test 1: read only mode +create user test@localhost; GRANT CREATE, SELECT, DROP ON *.* TO test@localhost; connect(con1, localhost, test, , test); @@ -251,3 +253,4 @@ connection default; SET GLOBAL READ_ONLY = OFF; DROP USER bug33669@localhost; DROP DATABASE db1; + diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index c46ff69295f..92e38604a30 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -848,6 +848,7 @@ set optimizer_use_condition_selectivity=1; explain extended 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; +--sorted_result 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; @@ -855,6 +856,7 @@ set optimizer_use_condition_selectivity=3; explain extended 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; +--sorted_result 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; @@ -946,11 +948,11 @@ drop table t0,t1,t2; --echo # Bug mdev-7316: a conjunct in WHERE with selectivity == 0 --echo # -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); diff --git a/mysql-test/t/servers.test b/mysql-test/t/servers.test index 37817937ab5..645206c376e 100644 --- a/mysql-test/t/servers.test +++ b/mysql-test/t/servers.test @@ -1,4 +1,5 @@ # Generic tests for servers (do not require FEDERATED) +set sql_mode=""; --echo # --echo # MDEV-4594 - CREATE SERVER crashes embedded @@ -21,5 +22,3 @@ DROP SERVER s1; CREATE SERVER s1 FOREIGN DATA WRAPPER mysql OPTIONS(SOCKET 'bar'); SELECT * FROM mysql.servers; DROP SERVER s1; - - diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 8a3d9478946..d7bfb3a973e 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -322,8 +322,11 @@ create database mysqltest; show create database mysqltest; 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; connect (con1,localhost,mysqltest_1,,mysqltest); @@ -990,6 +993,7 @@ create database mysqltest; show create database mysqltest; 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; connect (con4,localhost,mysqltest_4,,mysqltest); connection con4; diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test index 83c05d93007..6615ff66737 100644 --- a/mysql-test/t/show_explain.test +++ b/mysql-test/t/show_explain.test @@ -753,6 +753,7 @@ drop table t1,t3,t4; --echo # --echo # ---------- SHOW EXPLAIN and permissions ----------------- --echo # +create user test2@localhost; grant ALL on test.* to test2@localhost; # Give the user SUPER privilege so it can set debug_dbug variable. @@ -1201,4 +1202,3 @@ drop table t0,t1,t2; connection default; disconnect con1; set debug_sync='RESET'; - diff --git a/mysql-test/t/single_delete_update.test b/mysql-test/t/single_delete_update.test index e3ee13f891c..ec939641ea3 100644 --- a/mysql-test/t/single_delete_update.test +++ b/mysql-test/t/single_delete_update.test @@ -452,30 +452,3 @@ SELECT * FROM t2 WHERE c = 10 ORDER BY a DESC, b DESC; DROP TABLE t1, t2; - ---echo # ---echo # Bug #53742: UPDATEs have no effect after applying patch for bug 36569 ---echo # - ---disable_warnings -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; ---enable_warnings - -INSERT INTO t1 VALUES (), (), (), (); - -SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; -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; -SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; - -DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; -SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; - -DROP TABLE t1; - diff --git a/mysql-test/t/single_delete_update_innodb.test b/mysql-test/t/single_delete_update_innodb.test new file mode 100644 index 00000000000..7e7817d4dd6 --- /dev/null +++ b/mysql-test/t/single_delete_update_innodb.test @@ -0,0 +1,30 @@ +# +# Single table specific update/delete tests (mysql_update and mysql_delete) +# +--source include/have_innodb.inc + + +--echo # +--echo # Bug #53742: UPDATEs have no effect after applying patch for bug 36569 +--echo # + +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; +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; +SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; + +DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; + +DROP TABLE t1; + diff --git a/mysql-test/t/skip_name_resolve.test b/mysql-test/t/skip_name_resolve.test index a14f3af5cd6..b0c5118f970 100644 --- a/mysql-test/t/skip_name_resolve.test +++ b/mysql-test/t/skip_name_resolve.test @@ -5,6 +5,7 @@ --source include/count_sessions.inc # Bug#8471 IP address with mask fail when skip-name-resolve is on +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'; REVOKE ALL ON test.* FROM mysqltest_1@'127.0.0.1/255.255.255.255'; diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index 0646e727232..a5df4859d6b 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -7,13 +7,13 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc - connect (con1root,localhost,root,,); connection con1root; use test; # Create user user1 with no particular access rights +create user user1@localhost; grant usage on *.* to user1@localhost; flush privileges; @@ -57,10 +57,13 @@ call stamp(1); select * from t1; select db(); +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; connect (con2user1,localhost,user1,,); connect (con3anon,localhost,anon,,); @@ -175,6 +178,7 @@ 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; @@ -263,8 +267,11 @@ flush privileges; # # Test the new security acls # +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 ); @@ -340,11 +347,13 @@ connection root; --disable_warnings drop function if exists bug_9503; +drop user if exists user1@localhost; --enable_warnings delimiter //; 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// @@ -420,6 +429,7 @@ begin end// delimiter ;// +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost; connect (n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK); @@ -1047,4 +1057,3 @@ grant execute on procedure sp1 to current_user() identified by 'barfoo'; show grants; drop procedure sp1; set password=''; - diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 3a4ddee1de2..9e6b919ea19 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -14,7 +14,6 @@ # Tests that require --with-geometry go into sp_gis.test # Tests that require multibyte character sets, which are not always available, # go into separate files (e.g. sp-ucs2.test) - use test; # Test tables diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test index 2a24ab3d005..d7aaaa4340f 100644 --- a/mysql-test/t/sp_notembedded.test +++ b/mysql-test/t/sp_notembedded.test @@ -219,6 +219,7 @@ delimiter ;| drop procedure if exists bug15298_1; drop procedure if exists bug15298_2; --enable_warnings +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; @@ -486,6 +487,5 @@ drop user foo1@localhost; # set @@global.concurrent_insert= @old_concurrent_insert; - # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index 39ac086071b..a9759b99269 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -5,6 +5,8 @@ -- source include/have_log_bin.inc -- source include/have_innodb.inc +set SQL_MODE=""; + disable_query_log; call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); enable_query_log; diff --git a/mysql-test/t/stack-crash.test b/mysql-test/t/stack-crash.test index d5bdccc132d..e4feb95a332 100644 --- a/mysql-test/t/stack-crash.test +++ b/mysql-test/t/stack-crash.test @@ -32,7 +32,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; +) 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` ( @@ -48,7 +48,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; +) 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(*) diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 9395323d32b..32b903c0265 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -281,6 +281,7 @@ create database db37908; --enable_warnings 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; delimiter |; create procedure proc37908() begin select 1; end | diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index c989cb22558..c2b93c919dd 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2524,7 +2524,7 @@ CREATE TABLE t1 ( PRIMARY KEY (pk), KEY col_int_key (col_int_key), KEY col_varchar_key (col_varchar_key, col_int_key) -) ENGINE=InnoDB; +); INSERT INTO t1 VALUES (10,8,'x','x'), diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index 24f48206013..52857f9169f 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -616,7 +616,7 @@ SET optimizer_switch=@save_optimizer_switch; --echo # (just a testcase) CREATE TABLE t1 ( PostID int(10) unsigned NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) DEFAULT CHARSET=utf8; INSERT INTO t1 (PostID) VALUES (1), (2); @@ -625,7 +625,7 @@ CREATE TABLE t2 ( EntityID int(10) unsigned NOT NULL, UserID int(10) unsigned NOT NULL, UNIQUE KEY EntityID (EntityID,UserID) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) DEFAULT CHARSET=utf8; INSERT INTO t2 (EntityID, UserID) VALUES (1, 30), (2, 30); diff --git a/mysql-test/t/timezone_grant.test b/mysql-test/t/timezone_grant.test index 8013f2b04ce..a6dd6be695e 100644 --- a/mysql-test/t/timezone_grant.test +++ b/mysql-test/t/timezone_grant.test @@ -24,6 +24,7 @@ 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; connect (tzuser, localhost, mysqltest_1,,); connection tzuser; @@ -103,6 +104,7 @@ drop table t1, t2; # Let us rely on that previous tests done proper cleanups 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; connect (tzuser3, localhost, mysqltest_1,,); create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1; diff --git a/mysql-test/t/trigger-compat.test b/mysql-test/t/trigger-compat.test index 8b360d16b98..c627d1a6040 100644 --- a/mysql-test/t/trigger-compat.test +++ b/mysql-test/t/trigger-compat.test @@ -19,7 +19,6 @@ # # Prepare environment. # - DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; diff --git a/mysql-test/t/trigger_notembedded.test b/mysql-test/t/trigger_notembedded.test index b4ce183e114..f0c565be41f 100644 --- a/mysql-test/t/trigger_notembedded.test +++ b/mysql-test/t/trigger_notembedded.test @@ -28,7 +28,6 @@ # # Prepare environment. # - DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; @@ -1019,6 +1018,7 @@ 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; --connect(con1,localhost,mysqltest_u1,,mysqltest_db1) diff --git a/mysql-test/t/user_limits.test b/mysql-test/t/user_limits.test index 7c249f61686..ebb4fd4fb88 100644 --- a/mysql-test/t/user_limits.test +++ b/mysql-test/t/user_limits.test @@ -26,6 +26,7 @@ flush privileges; --disable_ps_protocol # Test of MAX_QUERIES_PER_HOUR limit +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 2; # This ensures that counters are reset and makes test scheduling independent flush user_resources; @@ -46,6 +47,7 @@ disconnect mqph; disconnect mqph2; # Test of MAX_UPDATES_PER_HOUR limit +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 2; flush user_resources; connect (muph, localhost, mysqltest_1,,); @@ -70,6 +72,7 @@ disconnect muph; disconnect muph2; # Test of MAX_CONNECTIONS_PER_HOUR limit +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost with max_connections_per_hour 2; flush user_resources; connect (mcph1, localhost, mysqltest_1,,); @@ -97,6 +100,7 @@ drop user mysqltest_1@localhost; # Test of MAX_USER_CONNECTIONS limit # We need this to reset internal mqh_used variable flush privileges; +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost with max_user_connections 2; flush user_resources; connect (muc1, localhost, mysqltest_1,,); @@ -150,6 +154,7 @@ set session max_user_connections= 2; set global max_user_connections= 2; select @@session.max_user_connections, @@global.max_user_connections; # Let us check that global limit works +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost; flush user_resources; connect (muca1, localhost, mysqltest_1,,); diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 4c7c74f8032..8f29528ac25 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -1467,9 +1467,11 @@ drop function if exists t1_max; drop function if exists t1_min; --enable_warnings +set sql_mode=""; --disable_warnings create table t1 (a int) engine=innodb; --enable_warnings +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/t/view.test b/mysql-test/t/view.test index 8c2a8ac8dc2..26faae545d8 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2083,18 +2083,16 @@ drop table t1; # Bug#12941 # ---disable_warnings 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; ---enable_warnings +); 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; @@ -2806,11 +2804,12 @@ DROP VIEW IF EXISTS v1; # Bug#21261 Wrong access rights was required for an insert to a view # +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; CREATE DATABASE bug21261DB; USE bug21261DB; connect (root,localhost,root,,bug21261DB); connection root; - CREATE TABLE t1 (x INT); CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost'; @@ -2840,6 +2839,8 @@ USE test; disconnect root; disconnect user21261; +set GLOBAL sql_mode=default; +set LOCAL sql_mode=default; # # Bug#15950 NOW() optimized away in VIEWs diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 8907c726ae3..6d0cd8e5c28 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -11,6 +11,7 @@ drop view if exists v1,v2,v3; # simple test of grants +create user test@localhost; grant create view on test.* to test@localhost; show grants for test@localhost; revoke create view on test.* from test@localhost; @@ -29,6 +30,7 @@ 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; @@ -90,7 +92,7 @@ select d from mysqltest.v1; connection root; 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; # TEMPORARY TABLE algorithm @@ -100,6 +102,7 @@ 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; connection user1; @@ -110,7 +113,7 @@ select d from mysqltest.v1; connection root; 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; # @@ -129,6 +132,7 @@ 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; # v5: SHOW VIEW, but no SELECT 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; @@ -225,7 +229,7 @@ show create view mysqltest.v4; connection root; 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; # @@ -244,6 +248,7 @@ 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; @@ -274,6 +279,7 @@ update v3 set a=a+c; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -291,6 +297,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+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; @@ -310,6 +317,7 @@ delete from v2 where c < 4; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -327,6 +335,7 @@ 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; @@ -346,6 +355,7 @@ insert into v2 select x,y from t2; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -359,6 +369,7 @@ 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; @@ -407,6 +418,7 @@ create view v4 as select b+1 from mysqltest.t2; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; drop view v1,v2,v4; @@ -418,6 +430,7 @@ connection root; create database mysqltest; --enable_warnings create table mysqltest.t1 (a int); +create user mysqltest_1@localhost; grant all privileges on mysqltest.* to mysqltest_1@localhost; connection user1; @@ -427,6 +440,7 @@ use test; connection root; revoke all privileges on mysqltest.* from mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -439,6 +453,7 @@ 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; @@ -455,6 +470,7 @@ select * from v1; grant select on mysqltest.t1 to mysqltest_1@localhost; select * from v1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop view v1; drop database mysqltest; @@ -482,6 +498,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; @@ -506,6 +523,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; # @@ -529,6 +547,7 @@ delimiter //; create function f2 () returns int begin declare v int; select s1 from t2 into v; return v; end// delimiter ;// +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; @@ -558,6 +577,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; # @@ -572,6 +592,7 @@ 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; @@ -601,6 +622,7 @@ select * from v5; drop table t1; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; @@ -617,6 +639,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; @@ -640,6 +663,7 @@ 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; # @@ -657,6 +681,7 @@ delete from mysql.user where user=''; flush privileges; # Create the test user +create user 'test14256'@'%'; grant all on test.* to 'test14256'@'%'; connect (test14256,localhost,test14256,,test); @@ -702,6 +727,7 @@ use mysqltest; CREATE TABLE t1 (i INT); CREATE VIEW v1 AS SELECT * FROM t1; SHOW CREATE VIEW v1; +create user mysqltest_1@localhost; GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; connection user1; @@ -1162,6 +1188,7 @@ 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; @@ -1237,6 +1264,7 @@ 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; diff --git a/mysql-test/t/warnings_engine_disabled.test b/mysql-test/t/warnings_engine_disabled.test index 11b22135128..46c686aa6d6 100644 --- a/mysql-test/t/warnings_engine_disabled.test +++ b/mysql-test/t/warnings_engine_disabled.test @@ -12,9 +12,11 @@ if (!`SELECT count(*) FROM information_schema.engines WHERE # Test for handler type, will select MyISAM and print a warning # about that - since InnoDB is disabled # +set sql_mode=""; create table t1 (id int) engine=InnoDB; alter table t1 engine=InnoDB; drop table t1; +set sql_mode=default; # # Bug#29263 disabled storage engines omitted in SHOW ENGINES |