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/suite | |
| 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/suite')
173 files changed, 491 insertions, 249 deletions
diff --git a/mysql-test/suite/archive/archive_plugin.result b/mysql-test/suite/archive/archive_plugin.result index feb825f20ee..5656704989e 100644 --- a/mysql-test/suite/archive/archive_plugin.result +++ b/mysql-test/suite/archive/archive_plugin.result @@ -1,8 +1,5 @@ CREATE TABLE t1(a int) ENGINE=ARCHIVE; -Warnings: -Warning 1286 Unknown storage engine 'ARCHIVE' -Warning 1266 Using storage engine MyISAM for table 't1' -DROP TABLE t1; +ERROR 42000: Unknown storage engine 'ARCHIVE' INSTALL PLUGIN archive SONAME 'ha_archive.so'; INSTALL PLUGIN ARCHIVE SONAME 'ha_archive.so'; ERROR HY000: Plugin 'ARCHIVE' already installed diff --git a/mysql-test/suite/archive/archive_plugin.test b/mysql-test/suite/archive/archive_plugin.test index 8ea40114cca..69bf9a5f4ce 100644 --- a/mysql-test/suite/archive/archive_plugin.test +++ b/mysql-test/suite/archive/archive_plugin.test @@ -2,8 +2,8 @@ if (!$HA_ARCHIVE_SO) { --skip Need archive plugin } +--error ER_UNKNOWN_STORAGE_ENGINE CREATE TABLE t1(a int) ENGINE=ARCHIVE; -DROP TABLE t1; --replace_regex /\.dll/.so/ eval INSTALL PLUGIN archive SONAME '$HA_ARCHIVE_SO'; --replace_regex /\.dll/.so/ diff --git a/mysql-test/suite/archive/discover_5438.result b/mysql-test/suite/archive/discover_5438.result index b5ab56aa364..0cd44c40a4e 100644 --- a/mysql-test/suite/archive/discover_5438.result +++ b/mysql-test/suite/archive/discover_5438.result @@ -3,6 +3,7 @@ create view t1 as select "I am a view" as a; ERROR 42S01: Table 't1' already exists drop table t1; create table t1 (a int) engine=archive; +create user foo@bar; grant select on test.t1 to foo@bar; drop user foo@bar; drop table t1; diff --git a/mysql-test/suite/archive/discover_5438.test b/mysql-test/suite/archive/discover_5438.test index c05955baaf3..892b9d49592 100644 --- a/mysql-test/suite/archive/discover_5438.test +++ b/mysql-test/suite/archive/discover_5438.test @@ -12,6 +12,7 @@ create view t1 as select "I am a view" as a; drop table t1; create table t1 (a int) engine=archive; +create user foo@bar; grant select on test.t1 to foo@bar; drop user foo@bar; drop table t1; diff --git a/mysql-test/suite/binlog/r/binlog_grant.result b/mysql-test/suite/binlog/r/binlog_grant.result index 09a3d709929..618e00af72a 100644 --- a/mysql-test/suite/binlog/r/binlog_grant.result +++ b/mysql-test/suite/binlog/r/binlog_grant.result @@ -22,6 +22,7 @@ ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) fo **** Clean up **** set global binlog_format = @saved_binlog_format; drop user mysqltest_1@localhost; +CREATE USER 'mysqltest_1'@'localhost'; GRANT REPLICATION CLIENT ON *.* TO 'mysqltest_1'@'localhost'; SHOW MASTER LOGS; SHOW BINARY LOGS; diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog2.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog2.result index 7581a9a7cad..2719091f4dd 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog2.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog2.result @@ -1,3 +1,4 @@ +set sql_mode=""; drop table if exists t1; reset master; set @a=UNIX_TIMESTAMP("2020-01-21 15:32:22"); diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result index 0372d2749a8..da48f4b2641 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result @@ -363,7 +363,7 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; 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/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result index 21b3d4f63be..4d92d3714c9 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result @@ -1,3 +1,4 @@ +set sql_mode=""; SET NAMES 'utf8'; # # Preparatory cleanup. diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result index 5dea7c79bdb..11fa220e896 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result @@ -1,3 +1,4 @@ +set sql_mode=""; SET NAMES 'utf8'; # # Preparatory cleanup. diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_trans.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_trans.result index ad1a6ede9b7..92e8438094f 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_trans.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_trans.result @@ -147,7 +147,7 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; 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/suite/binlog/r/binlog_row_ctype_ucs.result b/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result index c446c0f4861..6b56c006518 100644 --- a/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result +++ b/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result @@ -20,7 +20,7 @@ BEGIN 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/suite/binlog/r/binlog_row_mysqlbinlog_options.result b/mysql-test/suite/binlog/r/binlog_row_mysqlbinlog_options.result index f2722924b6a..ad5d0500d19 100644 --- a/mysql-test/suite/binlog/r/binlog_row_mysqlbinlog_options.result +++ b/mysql-test/suite/binlog/r/binlog_row_mysqlbinlog_options.result @@ -51,7 +51,7 @@ use `new_test1`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; 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/*!*/; @@ -263,7 +263,7 @@ use `new_test1`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=#/*!*/; 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/suite/binlog/r/binlog_stm_ctype_ucs.result b/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result index 55db2de4b52..825b9bf05a0 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result +++ b/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result @@ -22,7 +22,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/suite/binlog/t/binlog_grant.test b/mysql-test/suite/binlog/t/binlog_grant.test index 0c9d9a45ec9..8a76b11e707 100644 --- a/mysql-test/suite/binlog/t/binlog_grant.test +++ b/mysql-test/suite/binlog/t/binlog_grant.test @@ -58,6 +58,7 @@ drop user mysqltest_1@localhost; # Testing if REPLICATION CLIENT privilege is enough to execute # SHOW MASTER LOGS and SHOW BINARY. +CREATE USER 'mysqltest_1'@'localhost'; GRANT REPLICATION CLIENT ON *.* TO 'mysqltest_1'@'localhost'; --connect(rpl,localhost,mysqltest_1,,) diff --git a/mysql-test/suite/binlog/t/binlog_mysqlbinlog2.test b/mysql-test/suite/binlog/t/binlog_mysqlbinlog2.test index 367314d9643..76d6abf4d18 100644 --- a/mysql-test/suite/binlog/t/binlog_mysqlbinlog2.test +++ b/mysql-test/suite/binlog/t/binlog_mysqlbinlog2.test @@ -5,6 +5,7 @@ -- source include/have_binlog_format_mixed_or_statement.inc -- source include/binlog_start_pos.inc +set sql_mode=""; --disable_warnings drop table if exists t1; --enable_warnings diff --git a/mysql-test/suite/binlog/t/binlog_row_annotate.test b/mysql-test/suite/binlog/t/binlog_row_annotate.test index a0f72c9222c..e831b29df00 100644 --- a/mysql-test/suite/binlog/t/binlog_row_annotate.test +++ b/mysql-test/suite/binlog/t/binlog_row_annotate.test @@ -24,6 +24,8 @@ --disable_query_log +set sql_mode=""; + # Fix timestamp to avoid varying results SET timestamp=1000000000; diff --git a/mysql-test/suite/federated/federatedx.result b/mysql-test/suite/federated/federatedx.result index 522b286c545..5356fcad424 100644 --- a/mysql-test/suite/federated/federatedx.result +++ b/mysql-test/suite/federated/federatedx.result @@ -1810,6 +1810,7 @@ length(a) 5000 drop table t1; drop table t1; +set sql_mode=""; DROP TABLE IF EXISTS federated.test; CREATE TABLE federated.test ( `i` int(11) NOT NULL, @@ -1818,6 +1819,7 @@ CREATE TABLE federated.test ( PRIMARY KEY (`i`,`j`), UNIQUE KEY `i` (`i`,`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; +set sql_mode=default; DROP TABLE IF EXISTS federated.test1; DROP TABLE IF EXISTS federated.test2; create table federated.test1 ( diff --git a/mysql-test/suite/federated/federatedx.test b/mysql-test/suite/federated/federatedx.test index e79970f8dcd..87ef49e66e8 100644 --- a/mysql-test/suite/federated/federatedx.test +++ b/mysql-test/suite/federated/federatedx.test @@ -1633,6 +1633,7 @@ drop table t1; # connection slave; +set sql_mode=""; --disable_warnings DROP TABLE IF EXISTS federated.test; CREATE TABLE federated.test ( @@ -1643,6 +1644,7 @@ CREATE TABLE federated.test ( UNIQUE KEY `i` (`i`,`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; --enable_warnings +set sql_mode=default; connection master; --disable_warnings diff --git a/mysql-test/suite/funcs_1/datadict/is_routines.inc b/mysql-test/suite/funcs_1/datadict/is_routines.inc index 5ef88e2dffc..ecbb1b8c0da 100644 --- a/mysql-test/suite/funcs_1/datadict/is_routines.inc +++ b/mysql-test/suite/funcs_1/datadict/is_routines.inc @@ -18,6 +18,7 @@ # 2008-06-11 mleich Move t/is_routines.test to this file and # create variants for embedded/non embedded server. # +set sql_mode=""; let $engine_type = MEMORY; let $other_engine_type = MyISAM; diff --git a/mysql-test/suite/funcs_1/datadict/is_triggers.inc b/mysql-test/suite/funcs_1/datadict/is_triggers.inc index 5597bc816fc..6c618e533b2 100644 --- a/mysql-test/suite/funcs_1/datadict/is_triggers.inc +++ b/mysql-test/suite/funcs_1/datadict/is_triggers.inc @@ -19,6 +19,7 @@ # 2008-06-11 mleich Move t/is_triggers.test to this file and # create variants for embedded/non embedded server. # +set global sql_mode=""; let $engine_type = MEMORY; let $other_engine_type = MyISAM; @@ -257,3 +258,4 @@ ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers; # Cleanup DROP DATABASE db_datadict; +set global sql_mode=default; diff --git a/mysql-test/suite/funcs_1/datadict/processlist_priv.inc b/mysql-test/suite/funcs_1/datadict/processlist_priv.inc index cc96f89f01a..e40ed39d49a 100644 --- a/mysql-test/suite/funcs_1/datadict/processlist_priv.inc +++ b/mysql-test/suite/funcs_1/datadict/processlist_priv.inc @@ -226,6 +226,7 @@ SELECT * FROM information_schema.processlist; --echo connection default (user=root) --echo #################################################################################### connection default; +create user ''@'localhost'; GRANT PROCESS ON *.* TO ''@'localhost'; --real_sleep 0.3 diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result index fa8fb6c1eb2..1131477869a 100644 --- a/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result +++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result @@ -1,3 +1,5 @@ +set sql_mode=""; +set GLOBAL sql_mode=""; --source suite/funcs_1/storedproc/load_sp_tb.inc -------------------------------------------------------------------------------- @@ -388,6 +390,7 @@ DROP USER 'user_2'@'localhost'; -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS db_storedproc; DROP DATABASE IF EXISTS db_storedproc_1; +set GLOBAL sql_mode=default; . +++ END OF SCRIPT +++ -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result index 0dfc1f51b76..de63339ee95 100644 --- a/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result +++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result @@ -1,3 +1,4 @@ +set sql_mode=""; --source suite/funcs_1/storedproc/load_sp_tb.inc -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/innodb_trig_03.result b/mysql-test/suite/funcs_1/r/innodb_trig_03.result index db92ea56444..5edef4cf3af 100644 --- a/mysql-test/suite/funcs_1/r/innodb_trig_03.result +++ b/mysql-test/suite/funcs_1/r/innodb_trig_03.result @@ -1,3 +1,4 @@ +set sql_mode=""; USE test; drop table if exists tb3; create table tb3 ( diff --git a/mysql-test/suite/funcs_1/r/innodb_trig_03e.result b/mysql-test/suite/funcs_1/r/innodb_trig_03e.result index cf93657885c..6d8bba4714b 100644 --- a/mysql-test/suite/funcs_1/r/innodb_trig_03e.result +++ b/mysql-test/suite/funcs_1/r/innodb_trig_03e.result @@ -449,8 +449,8 @@ create trigger trg1_4 before UPDATE on t1 for each row set new.f1 = 'trig 1_4-yes'; show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE NULL test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci -trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE NULL test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci select current_user; current_user test_noprivs@localhost diff --git a/mysql-test/suite/funcs_1/r/innodb_trig_0407.result b/mysql-test/suite/funcs_1/r/innodb_trig_0407.result index 7f01975db16..c51347d5fa8 100644 --- a/mysql-test/suite/funcs_1/r/innodb_trig_0407.result +++ b/mysql-test/suite/funcs_1/r/innodb_trig_0407.result @@ -1,3 +1,5 @@ +set sql_mode=""; +set GLOBAL sql_mode=""; USE test; drop table if exists tb3; create table tb3 ( @@ -468,3 +470,4 @@ drop user test_general@localhost; drop user test_general; drop user test_super@localhost; DROP TABLE test.tb3; +set GLOBAL sql_mode=default; diff --git a/mysql-test/suite/funcs_1/r/innodb_trig_08.result b/mysql-test/suite/funcs_1/r/innodb_trig_08.result index f9454edce4b..20354088a0f 100644 --- a/mysql-test/suite/funcs_1/r/innodb_trig_08.result +++ b/mysql-test/suite/funcs_1/r/innodb_trig_08.result @@ -1,3 +1,5 @@ +set sql_mode=""; +set GLOBAL sql_mode=""; USE test; drop table if exists tb3; create table tb3 ( @@ -521,3 +523,4 @@ drop user test_general@localhost; drop user test_general; drop user test_super@localhost; DROP TABLE test.tb3; +set GLOBAL sql_mode=default; diff --git a/mysql-test/suite/funcs_1/r/is_routines.result b/mysql-test/suite/funcs_1/r/is_routines.result index a062e2d7b8f..525b5c92a68 100644 --- a/mysql-test/suite/funcs_1/r/is_routines.result +++ b/mysql-test/suite/funcs_1/r/is_routines.result @@ -1,3 +1,4 @@ +set sql_mode=""; SHOW TABLES FROM information_schema LIKE 'ROUTINES'; Tables_in_information_schema (ROUTINES) ROUTINES diff --git a/mysql-test/suite/funcs_1/r/is_triggers.result b/mysql-test/suite/funcs_1/r/is_triggers.result index 53bb54aa0f2..d6b5f3a42bf 100644 --- a/mysql-test/suite/funcs_1/r/is_triggers.result +++ b/mysql-test/suite/funcs_1/r/is_triggers.result @@ -1,3 +1,4 @@ +set global sql_mode=""; SHOW TABLES FROM information_schema LIKE 'TRIGGERS'; Tables_in_information_schema (TRIGGERS) TRIGGERS @@ -228,3 +229,4 @@ ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_ ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP DATABASE db_datadict; +set global sql_mode=default; diff --git a/mysql-test/suite/funcs_1/r/memory_storedproc_06.result b/mysql-test/suite/funcs_1/r/memory_storedproc_06.result index f08165b21f8..c38481bac50 100644 --- a/mysql-test/suite/funcs_1/r/memory_storedproc_06.result +++ b/mysql-test/suite/funcs_1/r/memory_storedproc_06.result @@ -1,4 +1,5 @@ SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; +set GLOBAL sql_mode=""; --source suite/funcs_1/storedproc/load_sp_tb.inc -------------------------------------------------------------------------------- @@ -389,6 +390,7 @@ DROP USER 'user_2'@'localhost'; -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS db_storedproc; DROP DATABASE IF EXISTS db_storedproc_1; +set GLOBAL sql_mode=default; . +++ END OF SCRIPT +++ -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/memory_trig_03e.result b/mysql-test/suite/funcs_1/r/memory_trig_03e.result index 5f05f7c0af5..cfa839b382c 100644 --- a/mysql-test/suite/funcs_1/r/memory_trig_03e.result +++ b/mysql-test/suite/funcs_1/r/memory_trig_03e.result @@ -450,8 +450,8 @@ create trigger trg1_4 before UPDATE on t1 for each row set new.f1 = 'trig 1_4-yes'; show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE NULL test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci -trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE NULL test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci select current_user; current_user test_noprivs@localhost diff --git a/mysql-test/suite/funcs_1/r/memory_trig_0407.result b/mysql-test/suite/funcs_1/r/memory_trig_0407.result index 2146c74b72e..7f3277f55e1 100644 --- a/mysql-test/suite/funcs_1/r/memory_trig_0407.result +++ b/mysql-test/suite/funcs_1/r/memory_trig_0407.result @@ -1,4 +1,5 @@ SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; +set GLOBAL sql_mode=""; USE test; drop table if exists tb3; create table tb3 ( @@ -469,3 +470,4 @@ drop user test_general@localhost; drop user test_general; drop user test_super@localhost; DROP TABLE test.tb3; +set GLOBAL sql_mode=default; diff --git a/mysql-test/suite/funcs_1/r/memory_trig_08.result b/mysql-test/suite/funcs_1/r/memory_trig_08.result index d19507e5f4d..4632cfe269c 100644 --- a/mysql-test/suite/funcs_1/r/memory_trig_08.result +++ b/mysql-test/suite/funcs_1/r/memory_trig_08.result @@ -1,4 +1,5 @@ SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; +set GLOBAL sql_mode=""; USE test; drop table if exists tb3; create table tb3 ( @@ -522,3 +523,4 @@ drop user test_general@localhost; drop user test_general; drop user test_super@localhost; DROP TABLE test.tb3; +set GLOBAL sql_mode=default; diff --git a/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result b/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result index f08165b21f8..c38481bac50 100644 --- a/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result +++ b/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result @@ -1,4 +1,5 @@ SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; +set GLOBAL sql_mode=""; --source suite/funcs_1/storedproc/load_sp_tb.inc -------------------------------------------------------------------------------- @@ -389,6 +390,7 @@ DROP USER 'user_2'@'localhost'; -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS db_storedproc; DROP DATABASE IF EXISTS db_storedproc_1; +set GLOBAL sql_mode=default; . +++ END OF SCRIPT +++ -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/myisam_trig_03e.result b/mysql-test/suite/funcs_1/r/myisam_trig_03e.result index c34525a52f8..fd796dd4550 100644 --- a/mysql-test/suite/funcs_1/r/myisam_trig_03e.result +++ b/mysql-test/suite/funcs_1/r/myisam_trig_03e.result @@ -450,8 +450,8 @@ create trigger trg1_4 before UPDATE on t1 for each row set new.f1 = 'trig 1_4-yes'; show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE NULL test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci -trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE NULL test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci select current_user; current_user test_noprivs@localhost diff --git a/mysql-test/suite/funcs_1/r/myisam_trig_0407.result b/mysql-test/suite/funcs_1/r/myisam_trig_0407.result index 2146c74b72e..7f3277f55e1 100644 --- a/mysql-test/suite/funcs_1/r/myisam_trig_0407.result +++ b/mysql-test/suite/funcs_1/r/myisam_trig_0407.result @@ -1,4 +1,5 @@ SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; +set GLOBAL sql_mode=""; USE test; drop table if exists tb3; create table tb3 ( @@ -469,3 +470,4 @@ drop user test_general@localhost; drop user test_general; drop user test_super@localhost; DROP TABLE test.tb3; +set GLOBAL sql_mode=default; diff --git a/mysql-test/suite/funcs_1/r/myisam_trig_08.result b/mysql-test/suite/funcs_1/r/myisam_trig_08.result index d19507e5f4d..4632cfe269c 100644 --- a/mysql-test/suite/funcs_1/r/myisam_trig_08.result +++ b/mysql-test/suite/funcs_1/r/myisam_trig_08.result @@ -1,4 +1,5 @@ SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; +set GLOBAL sql_mode=""; USE test; drop table if exists tb3; create table tb3 ( @@ -522,3 +523,4 @@ drop user test_general@localhost; drop user test_general; drop user test_super@localhost; DROP TABLE test.tb3; +set GLOBAL sql_mode=default; diff --git a/mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result b/mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result index 079aca6b25c..7733cc4c43b 100644 --- a/mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result +++ b/mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result @@ -206,6 +206,7 @@ ID root HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY R 5 Grant PROCESS privilege to anonymous user. connection default (user=root) #################################################################################### +create user ''@'localhost'; GRANT PROCESS ON *.* TO ''@'localhost'; #################################################################################### 5.1 Establish connection (anonymous1,localhost,'',,information_schema) diff --git a/mysql-test/suite/funcs_1/r/processlist_priv_ps.result b/mysql-test/suite/funcs_1/r/processlist_priv_ps.result index 19631cfb820..04c309c3f54 100644 --- a/mysql-test/suite/funcs_1/r/processlist_priv_ps.result +++ b/mysql-test/suite/funcs_1/r/processlist_priv_ps.result @@ -206,6 +206,7 @@ ID root HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY R 5 Grant PROCESS privilege to anonymous user. connection default (user=root) #################################################################################### +create user ''@'localhost'; GRANT PROCESS ON *.* TO ''@'localhost'; #################################################################################### 5.1 Establish connection (anonymous1,localhost,'',,information_schema) diff --git a/mysql-test/suite/funcs_1/r/storedproc.result b/mysql-test/suite/funcs_1/r/storedproc.result index cf3bc923b19..37609c770a5 100644 --- a/mysql-test/suite/funcs_1/r/storedproc.result +++ b/mysql-test/suite/funcs_1/r/storedproc.result @@ -1,3 +1,4 @@ +set sql_mode=""; --source suite/funcs_1/storedproc/load_sp_tb.inc -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc index 3e5ca055ae1..c4f8496fd06 100644 --- a/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc +++ b/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc @@ -4,6 +4,8 @@ # privileges. --source include/not_embedded.inc +set GLOBAL sql_mode=""; + --source suite/funcs_1/storedproc/load_sp_tb.inc # ============================================================================== @@ -451,6 +453,7 @@ DROP USER 'user_2'@'localhost'; # USE the same .inc to cleanup before and after the test --source suite/funcs_1/storedproc/cleanup_sp_tb.inc +set GLOBAL sql_mode=default; # ============================================================================== --echo --echo . +++ END OF SCRIPT +++ diff --git a/mysql-test/suite/funcs_1/t/innodb_storedproc_06.test b/mysql-test/suite/funcs_1/t/innodb_storedproc_06.test index ce061da2299..7bfc3153a61 100644 --- a/mysql-test/suite/funcs_1/t/innodb_storedproc_06.test +++ b/mysql-test/suite/funcs_1/t/innodb_storedproc_06.test @@ -4,6 +4,7 @@ --source include/have_innodb.inc # 2. Set $engine_type +set sql_mode=""; let $engine_type= innodb; --source suite/funcs_1/storedproc/storedproc_06.inc diff --git a/mysql-test/suite/funcs_1/t/innodb_storedproc_08.test b/mysql-test/suite/funcs_1/t/innodb_storedproc_08.test index c8c289c5f49..39c983ad2a0 100644 --- a/mysql-test/suite/funcs_1/t/innodb_storedproc_08.test +++ b/mysql-test/suite/funcs_1/t/innodb_storedproc_08.test @@ -4,6 +4,7 @@ --source include/have_innodb.inc # 2. Set $engine_type +set sql_mode=""; let $engine_type= innodb; --source suite/funcs_1/storedproc/storedproc_08.inc diff --git a/mysql-test/suite/funcs_1/t/innodb_trig_03.test b/mysql-test/suite/funcs_1/t/innodb_trig_03.test index 2a611c97fbf..f2a8fa3c1d9 100644 --- a/mysql-test/suite/funcs_1/t/innodb_trig_03.test +++ b/mysql-test/suite/funcs_1/t/innodb_trig_03.test @@ -5,6 +5,7 @@ # 1. Check if InnoDB is available --source include/have_innodb.inc # 2. Set $engine_type +set sql_mode=""; let $engine_type= innodb; --source suite/funcs_1/triggers/triggers_03.inc diff --git a/mysql-test/suite/funcs_1/t/innodb_trig_0407.test b/mysql-test/suite/funcs_1/t/innodb_trig_0407.test index d8081770f4b..dddb000cf18 100644 --- a/mysql-test/suite/funcs_1/t/innodb_trig_0407.test +++ b/mysql-test/suite/funcs_1/t/innodb_trig_0407.test @@ -5,6 +5,7 @@ # 1. Check if InnoDB is available --source include/have_innodb.inc # 2. Set $engine_type +set sql_mode=""; let $engine_type= innodb; --source suite/funcs_1/triggers/triggers_0407.inc diff --git a/mysql-test/suite/funcs_1/t/innodb_trig_08.test b/mysql-test/suite/funcs_1/t/innodb_trig_08.test index ba48b977ba2..c870c1a7895 100644 --- a/mysql-test/suite/funcs_1/t/innodb_trig_08.test +++ b/mysql-test/suite/funcs_1/t/innodb_trig_08.test @@ -5,6 +5,7 @@ # 1. Check if InnoDB is available --source include/have_innodb.inc # 2. Set $engine_type +set sql_mode=""; let $engine_type= innodb; --source suite/funcs_1/triggers/triggers_08.inc diff --git a/mysql-test/suite/funcs_1/t/storedproc.test b/mysql-test/suite/funcs_1/t/storedproc.test index ff417957c85..ca9dfcbbe2d 100644 --- a/mysql-test/suite/funcs_1/t/storedproc.test +++ b/mysql-test/suite/funcs_1/t/storedproc.test @@ -9,6 +9,7 @@ # - restore global sort_buffer_size after some subtest # ############################################################################ +set sql_mode=""; # Bug#37746 - Arithmetic range ("int") is smaller than expected # This code is in place to ensure this test is only skipped diff --git a/mysql-test/suite/funcs_1/triggers/triggers_0407.inc b/mysql-test/suite/funcs_1/triggers/triggers_0407.inc index 2bc9dd66478..907260a00f0 100644 --- a/mysql-test/suite/funcs_1/triggers/triggers_0407.inc +++ b/mysql-test/suite/funcs_1/triggers/triggers_0407.inc @@ -3,6 +3,7 @@ # Trigger Tests # (test case numbering refer to requirement document TP v1.1) #====================================================================== +set GLOBAL sql_mode=""; USE test; --source suite/funcs_1/include/tb3.inc @@ -622,3 +623,6 @@ let $message= Testcase 3.5.7.17 (see Testcase 3.5.1.1); drop user test_super@localhost; DROP TABLE test.tb3; + + +set GLOBAL sql_mode=default; diff --git a/mysql-test/suite/funcs_1/triggers/triggers_08.inc b/mysql-test/suite/funcs_1/triggers/triggers_08.inc index 996a5aa69ae..0aeb46896a6 100644 --- a/mysql-test/suite/funcs_1/triggers/triggers_08.inc +++ b/mysql-test/suite/funcs_1/triggers/triggers_08.inc @@ -4,6 +4,7 @@ # (test case numbering refer to requirement document TP v1.1) #====================================================================== # WL#4084: enable disabled parts, 2007-11-15 hhunger +set GLOBAL sql_mode=""; USE test; --source suite/funcs_1/include/tb3.inc @@ -557,3 +558,4 @@ let $message= Testcase 3.5.8.7; drop user test_super@localhost; DROP TABLE test.tb3; +set GLOBAL sql_mode=default; diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 7a8c0dd44dd..42962b3a226 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -416,17 +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; -Warnings: -Warning 1286 Unknown storage engine 'InnoDB' -Warning 1266 Using storage engine Aria 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 Aria 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/suite/parts/inc/partition_auto_increment.inc b/mysql-test/suite/parts/inc/partition_auto_increment.inc index 45406bd145a..e3a8d681275 100644 --- a/mysql-test/suite/parts/inc/partition_auto_increment.inc +++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc @@ -3,6 +3,9 @@ # auto_increment test # used variables: $engine # +--disable_query_log +set sql_mode=""; +--enable_query_log -- disable_warnings DROP TABLE IF EXISTS t1; diff --git a/mysql-test/suite/perfschema/include/connection_setup.inc b/mysql-test/suite/perfschema/include/connection_setup.inc index a661d43d063..93b8e959ee3 100644 --- a/mysql-test/suite/perfschema/include/connection_setup.inc +++ b/mysql-test/suite/perfschema/include/connection_setup.inc @@ -53,10 +53,15 @@ --disable_query_log +create user user1@localhost; grant ALL on *.* to user1@localhost; +create user user2@localhost; grant ALL on *.* to user2@localhost; +create user user3@localhost; grant ALL on *.* to user3@localhost; +create user user4@localhost; grant ALL on *.* to user4@localhost; +create user user5@localhost; grant ALL on *.* to user5@localhost; flush privileges; diff --git a/mysql-test/suite/perfschema/include/event_aggregate_setup.inc b/mysql-test/suite/perfschema/include/event_aggregate_setup.inc index ec35e60d463..7dfefd25235 100644 --- a/mysql-test/suite/perfschema/include/event_aggregate_setup.inc +++ b/mysql-test/suite/perfschema/include/event_aggregate_setup.inc @@ -66,10 +66,13 @@ --source ../include/wait_for_pfs_thread_count.inc --disable_query_log - +create user user1@localhost; grant ALL on *.* to user1@localhost; +create user user2@localhost; grant ALL on *.* to user2@localhost; +create user user3@localhost; grant ALL on *.* to user3@localhost; +create user user4@localhost; grant ALL on *.* to user4@localhost; flush privileges; diff --git a/mysql-test/suite/perfschema/include/stage_setup.inc b/mysql-test/suite/perfschema/include/stage_setup.inc index 639b1df8c02..85ade046939 100644 --- a/mysql-test/suite/perfschema/include/stage_setup.inc +++ b/mysql-test/suite/perfschema/include/stage_setup.inc @@ -28,9 +28,13 @@ --disable_query_log +create user user1@localhost; grant ALL on *.* to user1@localhost; +create user user2@localhost; grant ALL on *.* to user2@localhost; +create user user3@localhost; grant ALL on *.* to user3@localhost; +create user user4@localhost; grant ALL on *.* to user4@localhost; flush privileges; diff --git a/mysql-test/suite/perfschema/include/table_aggregate_setup.inc b/mysql-test/suite/perfschema/include/table_aggregate_setup.inc index 522cdb9346d..79aa5482838 100644 --- a/mysql-test/suite/perfschema/include/table_aggregate_setup.inc +++ b/mysql-test/suite/perfschema/include/table_aggregate_setup.inc @@ -71,9 +71,13 @@ --source ../include/wait_for_pfs_thread_count.inc --disable_query_log +create user user1@localhost; grant ALL on *.* to user1@localhost; +create user user2@localhost; grant ALL on *.* to user2@localhost; +create user user3@localhost; grant ALL on *.* to user3@localhost; +create user user4@localhost; grant ALL on *.* to user4@localhost; flush privileges; diff --git a/mysql-test/suite/perfschema/r/column_privilege.result b/mysql-test/suite/perfschema/r/column_privilege.result index e4ad9200272..ea901484d7e 100644 --- a/mysql-test/suite/perfschema/r/column_privilege.result +++ b/mysql-test/suite/perfschema/r/column_privilege.result @@ -2,6 +2,7 @@ show grants; Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +create user 'pfs_user_5'@localhost; grant usage on *.* to 'pfs_user_5'@localhost with GRANT OPTION; grant SELECT(thread_id, event_id) on performance_schema.events_waits_current to 'pfs_user_5'@localhost; diff --git a/mysql-test/suite/perfschema/r/global_read_lock.result b/mysql-test/suite/perfschema/r/global_read_lock.result index f7ff4382261..dab22c79100 100644 --- a/mysql-test/suite/perfschema/r/global_read_lock.result +++ b/mysql-test/suite/perfschema/r/global_read_lock.result @@ -1,5 +1,6 @@ use performance_schema; update performance_schema.setup_instruments set enabled='YES'; +create user pfsuser@localhost; grant SELECT, UPDATE, LOCK TABLES on performance_schema.* to pfsuser@localhost; flush privileges; connect (con1, localhost, pfsuser, , test); diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_again_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_again_allow.result index ffe3dc2e0eb..d146f5324d0 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_again_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_again_allow.result @@ -23,6 +23,7 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'192.0.2.4'; grant select on test.* to 'root'@'192.0.2.4'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_bad_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_bad_allow.result index 7c658e17c55..6c1bce7c9a0 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_bad_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_bad_allow.result @@ -23,6 +23,7 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'192.0.2.4'; grant select on test.* to 'root'@'192.0.2.4'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_good_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_good_allow.result index 5657887e439..d04a565508f 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_good_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_good_allow.result @@ -23,6 +23,7 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'santa.claus.ipv4.example.com'; grant select on test.* to 'root'@'santa.claus.ipv4.example.com'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_noname_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_noname_allow.result index 115af4792c3..ea5e91307c8 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_noname_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv4_addrinfo_noname_allow.result @@ -23,7 +23,9 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'santa.claus.ipv4.example.com'; grant select on test.* to 'root'@'santa.claus.ipv4.example.com'; +create user 'root'@'192.0.2.4'; grant select on test.* to 'root'@'192.0.2.4'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv4_blocked.result b/mysql-test/suite/perfschema/r/hostcache_ipv4_blocked.result index 6f5affadd2f..4eae65c4618 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv4_blocked.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv4_blocked.result @@ -24,6 +24,7 @@ select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host select @@global.max_connect_errors into @saved_max_connect_errors; set global max_connect_errors = 3; +create user 'root'@'santa.claus.ipv4.example.com'; grant select on test.* to 'root'@'santa.claus.ipv4.example.com'; create user 'quota'@'santa.claus.ipv4.example.com'; grant select on test.* to 'quota'@'santa.claus.ipv4.example.com'; diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_again_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_again_allow.result index 6531f7e69f0..1c893320340 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_again_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_again_allow.result @@ -23,7 +23,9 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'192.0.2.4'; grant select on test.* to 'root'@'192.0.2.4'; +create user 'root'@'santa.claus.ipv4.example.com'; grant select on test.* to 'root'@'santa.claus.ipv4.example.com'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_noname_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_noname_allow.result index 315f90ce3e5..68855bbc0ce 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_noname_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv4_nameinfo_noname_allow.result @@ -23,6 +23,7 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'192.0.2.4'; grant select on test.* to 'root'@'192.0.2.4'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_again_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_again_allow.result index 59a12495352..36d7692f824 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_again_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_again_allow.result @@ -23,6 +23,7 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'2001:db8::6:6'; grant select on test.* to 'root'@'2001:db8::6:6'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_bad_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_bad_allow.result index 904139875c6..37470bdc0bb 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_bad_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_bad_allow.result @@ -23,6 +23,7 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'2001:db8::6:6'; grant select on test.* to 'root'@'2001:db8::6:6'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_good_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_good_allow.result index f5b2f43ab91..410f116f4d0 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_good_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_good_allow.result @@ -23,6 +23,7 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'santa.claus.ipv6.example.com'; grant select on test.* to 'root'@'santa.claus.ipv6.example.com'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_noname_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_noname_allow.result index 9cc8a675fcc..6ccb3b66f22 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_noname_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv6_addrinfo_noname_allow.result @@ -23,7 +23,9 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'santa.claus.ipv6.example.com'; grant select on test.* to 'root'@'santa.claus.ipv6.example.com'; +create user 'root'@'2001:db8::6:6'; grant select on test.* to 'root'@'2001:db8::6:6'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv6_blocked.result b/mysql-test/suite/perfschema/r/hostcache_ipv6_blocked.result index 5d1846f3354..a0c2d8bdfa6 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv6_blocked.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv6_blocked.result @@ -24,6 +24,7 @@ select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host select @@global.max_connect_errors into @saved_max_connect_errors; set global max_connect_errors = 3; +create user 'root'@'santa.claus.ipv6.example.com'; grant select on test.* to 'root'@'santa.claus.ipv6.example.com'; create user 'quota'@'santa.claus.ipv6.example.com'; grant select on test.* to 'quota'@'santa.claus.ipv6.example.com'; diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_again_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_again_allow.result index a2107324c65..a2b9a32c764 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_again_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_again_allow.result @@ -23,7 +23,9 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'2001:db8::6:6'; grant select on test.* to 'root'@'2001:db8::6:6'; +create user 'root'@'santa.claus.ipv6.example.com'; grant select on test.* to 'root'@'santa.claus.ipv6.example.com'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_noname_allow.result b/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_noname_allow.result index 809644cbe08..99b47be724f 100644 --- a/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_noname_allow.result +++ b/mysql-test/suite/perfschema/r/hostcache_ipv6_nameinfo_noname_allow.result @@ -23,6 +23,7 @@ select `User`, `Host` from mysql.`user` where `user` like '2001:%'; User Host select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; User Host +create user 'root'@'2001:db8::6:6'; grant select on test.* to 'root'@'2001:db8::6:6'; select "Con1 is alive"; Con1 is alive diff --git a/mysql-test/suite/perfschema/r/privilege.result b/mysql-test/suite/perfschema/r/privilege.result index 49d72fc499f..f46c094b5ac 100644 --- a/mysql-test/suite/perfschema/r/privilege.result +++ b/mysql-test/suite/perfschema/r/privilege.result @@ -2,6 +2,9 @@ show grants; Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +create user 'pfs_user_1'@localhost; +create user 'pfs_user_2'@localhost; +create user 'pfs_user_3'@localhost; grant ALL on *.* to 'pfs_user_1'@localhost with GRANT OPTION; grant ALL on performance_schema.* to 'pfs_user_2'@localhost with GRANT OPTION; diff --git a/mysql-test/suite/perfschema/r/read_only.result b/mysql-test/suite/perfschema/r/read_only.result index 19108326f1b..b2502808821 100644 --- a/mysql-test/suite/perfschema/r/read_only.result +++ b/mysql-test/suite/perfschema/r/read_only.result @@ -1,5 +1,6 @@ use performance_schema; set @start_read_only= @@global.read_only; +create user pfsuser@localhost; grant SELECT, UPDATE on performance_schema.* to pfsuser@localhost; flush privileges; connect (con1, localhost, pfsuser, , test); diff --git a/mysql-test/suite/perfschema/r/setup_actors.result b/mysql-test/suite/perfschema/r/setup_actors.result index 038fb623ab6..187b558e827 100644 --- a/mysql-test/suite/perfschema/r/setup_actors.result +++ b/mysql-test/suite/perfschema/r/setup_actors.result @@ -17,10 +17,15 @@ hostb % % hosta user1 % % user2 % localhost user3 % +create user user1@localhost; grant ALL on *.* to user1@localhost; +create user user2@localhost; grant ALL on *.* to user2@localhost; +create user user3@localhost; grant ALL on *.* to user3@localhost; +create user user4@localhost; grant ALL on *.* to user4@localhost; +create user user5@localhost; grant select on test.* to user5@localhost; flush privileges; # Switch to (con1, localhost, user1, , ) diff --git a/mysql-test/suite/perfschema/r/socket_summary_by_instance_func.result b/mysql-test/suite/perfschema/r/socket_summary_by_instance_func.result index d725009d8fd..21b99360e64 100644 --- a/mysql-test/suite/perfschema/r/socket_summary_by_instance_func.result +++ b/mysql-test/suite/perfschema/r/socket_summary_by_instance_func.result @@ -33,11 +33,13 @@ ERROR 28000: Access denied for user 'boot0123456789'@'localhost' (using password # 2.5 Connect should pass, host = localhost # length of user name = 10 character # length of default db = 9 character +CREATE USER 'root012345'@'localhost'; GRANT ALL PRIVILEGES ON *.* TO 'root012345'@'localhost'; DROP USER 'root012345'@'localhost'; # 2.6 Connect should pass, host = localhost # length of user name = 14 character # length of default db = 9 character +CREATE USER 'root0123456789'@'localhost'; GRANT ALL PRIVILEGES ON *.* to 'root0123456789'@'localhost'; DROP USER 'root0123456789'@'localhost'; # 2.7 Connect should pass, host = my_localhost diff --git a/mysql-test/suite/perfschema/r/stage_mdl_global.result b/mysql-test/suite/perfschema/r/stage_mdl_global.result index 1a6f51a4acc..de5df8f189a 100644 --- a/mysql-test/suite/perfschema/r/stage_mdl_global.result +++ b/mysql-test/suite/perfschema/r/stage_mdl_global.result @@ -6,6 +6,8 @@ user1 statement/sql/flush flush tables with read lock username event_name nesting_event_type username event_name nesting_event_type user1 stage/sql/init STATEMENT +user1 stage/sql/Waiting for query cache lock STATEMENT +user1 stage/sql/init STATEMENT user1 stage/sql/query end STATEMENT user1 stage/sql/closing tables STATEMENT user1 stage/sql/freeing items STATEMENT diff --git a/mysql-test/suite/perfschema/t/column_privilege.test b/mysql-test/suite/perfschema/t/column_privilege.test index 6fabdfb4229..004492c6145 100644 --- a/mysql-test/suite/perfschema/t/column_privilege.test +++ b/mysql-test/suite/perfschema/t/column_privilege.test @@ -7,6 +7,7 @@ show grants; +create user 'pfs_user_5'@localhost; grant usage on *.* to 'pfs_user_5'@localhost with GRANT OPTION; # Test per column privileges on performance_schema diff --git a/mysql-test/suite/perfschema/t/global_read_lock.test b/mysql-test/suite/perfschema/t/global_read_lock.test index 1ab6005f7e2..0883ed1ed42 100644 --- a/mysql-test/suite/perfschema/t/global_read_lock.test +++ b/mysql-test/suite/perfschema/t/global_read_lock.test @@ -11,6 +11,7 @@ use performance_schema; # Ensure that instrumentation is turned on when we create new connection. update performance_schema.setup_instruments set enabled='YES'; +create user pfsuser@localhost; grant SELECT, UPDATE, LOCK TABLES on performance_schema.* to pfsuser@localhost; flush privileges; diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_again_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_again_allow.test index a96ccf86315..df82a5c047d 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_again_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_again_allow.test @@ -18,6 +18,7 @@ call mtr.add_suppression("santa.claus.ipv4.example.com"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'192.0.2.4'; grant select on test.* to 'root'@'192.0.2.4'; connect (con1,"127.0.0.1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_bad_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_bad_allow.test index 4bf041b1ada..08cbe8d15af 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_bad_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_bad_allow.test @@ -20,6 +20,7 @@ call mtr.add_suppression("santa.claus.ipv4.example.com"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'192.0.2.4'; grant select on test.* to 'root'@'192.0.2.4'; connect (con1,"127.0.0.1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_good_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_good_allow.test index 9931fa5ffba..808a1b927d7 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_good_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_good_allow.test @@ -18,6 +18,7 @@ call mtr.add_suppression("192.0.2.4"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'santa.claus.ipv4.example.com'; grant select on test.* to 'root'@'santa.claus.ipv4.example.com'; connect (con1,"127.0.0.1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_noname_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_noname_allow.test index 283d3a5841d..6f763d8c666 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_noname_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv4_addrinfo_noname_allow.test @@ -18,7 +18,9 @@ call mtr.add_suppression("santa.claus.ipv4.example.com"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'santa.claus.ipv4.example.com'; grant select on test.* to 'root'@'santa.claus.ipv4.example.com'; +create user 'root'@'192.0.2.4'; grant select on test.* to 'root'@'192.0.2.4'; connect (con1,"127.0.0.1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv4_blocked.test b/mysql-test/suite/perfschema/t/hostcache_ipv4_blocked.test index 7c89aa3d44e..b18dcd5f494 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv4_blocked.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv4_blocked.test @@ -20,6 +20,7 @@ select @@global.max_connect_errors into @saved_max_connect_errors; set global max_connect_errors = 3; +create user 'root'@'santa.claus.ipv4.example.com'; grant select on test.* to 'root'@'santa.claus.ipv4.example.com'; create user 'quota'@'santa.claus.ipv4.example.com'; diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv4_nameinfo_again_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv4_nameinfo_again_allow.test index bce65b61cfc..ab72e0f4340 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv4_nameinfo_again_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv4_nameinfo_again_allow.test @@ -18,7 +18,9 @@ call mtr.add_suppression("192.0.2.4"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'192.0.2.4'; grant select on test.* to 'root'@'192.0.2.4'; +create user 'root'@'santa.claus.ipv4.example.com'; grant select on test.* to 'root'@'santa.claus.ipv4.example.com'; connect (con1,"127.0.0.1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv4_nameinfo_noname_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv4_nameinfo_noname_allow.test index 52b696831af..06776de6b27 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv4_nameinfo_noname_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv4_nameinfo_noname_allow.test @@ -18,6 +18,7 @@ call mtr.add_suppression("192.0.2.4"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'192.0.2.4'; grant select on test.* to 'root'@'192.0.2.4'; connect (con1,"127.0.0.1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_again_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_again_allow.test index c1af8516b79..f276e759f75 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_again_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_again_allow.test @@ -19,6 +19,7 @@ call mtr.add_suppression("santa.claus.ipv6.example.com"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'2001:db8::6:6'; grant select on test.* to 'root'@'2001:db8::6:6'; connect (con1,"::1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_bad_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_bad_allow.test index ae1de4f032e..9f951273e98 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_bad_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_bad_allow.test @@ -21,6 +21,7 @@ call mtr.add_suppression("santa.claus.ipv6.example.com"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'2001:db8::6:6'; grant select on test.* to 'root'@'2001:db8::6:6'; connect (con1,"::1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_good_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_good_allow.test index 9dbd682681e..81427e86c5e 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_good_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_good_allow.test @@ -19,6 +19,7 @@ call mtr.add_suppression("192.0.2.4"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'santa.claus.ipv6.example.com'; grant select on test.* to 'root'@'santa.claus.ipv6.example.com'; connect (con1,"::1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_noname_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_noname_allow.test index b675b5089a8..7ce12639b7f 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_noname_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv6_addrinfo_noname_allow.test @@ -19,7 +19,9 @@ call mtr.add_suppression("santa.claus.ipv6.example.com"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'santa.claus.ipv6.example.com'; grant select on test.* to 'root'@'santa.claus.ipv6.example.com'; +create user 'root'@'2001:db8::6:6'; grant select on test.* to 'root'@'2001:db8::6:6'; connect (con1,"::1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv6_blocked.test b/mysql-test/suite/perfschema/t/hostcache_ipv6_blocked.test index cd78087dad1..0a04ec306cf 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv6_blocked.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv6_blocked.test @@ -21,6 +21,7 @@ select @@global.max_connect_errors into @saved_max_connect_errors; set global max_connect_errors = 3; +create user 'root'@'santa.claus.ipv6.example.com'; grant select on test.* to 'root'@'santa.claus.ipv6.example.com'; create user 'quota'@'santa.claus.ipv6.example.com'; diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv6_nameinfo_again_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv6_nameinfo_again_allow.test index 983a6e80ff7..7e49ed62b93 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv6_nameinfo_again_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv6_nameinfo_again_allow.test @@ -19,7 +19,9 @@ call mtr.add_suppression("2001:db8::6:6"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'2001:db8::6:6'; grant select on test.* to 'root'@'2001:db8::6:6'; +create user 'root'@'santa.claus.ipv6.example.com'; grant select on test.* to 'root'@'santa.claus.ipv6.example.com'; connect (con1,"::1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/hostcache_ipv6_nameinfo_noname_allow.test b/mysql-test/suite/perfschema/t/hostcache_ipv6_nameinfo_noname_allow.test index 3a30030413f..62876e859cb 100644 --- a/mysql-test/suite/perfschema/t/hostcache_ipv6_nameinfo_noname_allow.test +++ b/mysql-test/suite/perfschema/t/hostcache_ipv6_nameinfo_noname_allow.test @@ -19,6 +19,7 @@ call mtr.add_suppression("2001:db8::6:6"); --source ../include/wait_for_pfs_thread_count.inc --source ../include/hostcache_set_state.inc +create user 'root'@'2001:db8::6:6'; grant select on test.* to 'root'@'2001:db8::6:6'; connect (con1,"::1",root,,test,$MASTER_MYPORT,); diff --git a/mysql-test/suite/perfschema/t/nesting.test b/mysql-test/suite/perfschema/t/nesting.test index 29819770645..7f1c0b570ad 100644 --- a/mysql-test/suite/perfschema/t/nesting.test +++ b/mysql-test/suite/perfschema/t/nesting.test @@ -10,6 +10,7 @@ --disable_query_log +create user user1@localhost; grant ALL on *.* to user1@localhost; flush privileges; diff --git a/mysql-test/suite/perfschema/t/privilege.test b/mysql-test/suite/perfschema/t/privilege.test index 1864a34bcfe..f5bd5cc4036 100644 --- a/mysql-test/suite/perfschema/t/privilege.test +++ b/mysql-test/suite/perfschema/t/privilege.test @@ -5,6 +5,9 @@ show grants; +create user 'pfs_user_1'@localhost; +create user 'pfs_user_2'@localhost; +create user 'pfs_user_3'@localhost; grant ALL on *.* to 'pfs_user_1'@localhost with GRANT OPTION; # Test denied privileges on performance_schema.* @@ -328,4 +331,3 @@ UPDATE performance_schema.setup_timers SET timer_name = 'MICROSECOND' where name UPDATE performance_schema.setup_timers SET timer_name = 'CYCLE' where name="wait"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="stage"; UPDATE performance_schema.setup_timers SET timer_name = 'NANOSECOND' where name="statement"; - diff --git a/mysql-test/suite/perfschema/t/read_only.test b/mysql-test/suite/perfschema/t/read_only.test index 3050cee9e72..7b0284a7a48 100644 --- a/mysql-test/suite/perfschema/t/read_only.test +++ b/mysql-test/suite/perfschema/t/read_only.test @@ -11,6 +11,7 @@ use performance_schema; set @start_read_only= @@global.read_only; +create user pfsuser@localhost; grant SELECT, UPDATE on performance_schema.* to pfsuser@localhost; flush privileges; diff --git a/mysql-test/suite/perfschema/t/setup_actors.test b/mysql-test/suite/perfschema/t/setup_actors.test index e975fa96782..39d60b0ab03 100644 --- a/mysql-test/suite/perfschema/t/setup_actors.test +++ b/mysql-test/suite/perfschema/t/setup_actors.test @@ -33,10 +33,15 @@ values ('hostb', '%', '%'); select * from performance_schema.setup_actors order by USER, HOST, ROLE; +create user user1@localhost; grant ALL on *.* to user1@localhost; +create user user2@localhost; grant ALL on *.* to user2@localhost; +create user user3@localhost; grant ALL on *.* to user3@localhost; +create user user4@localhost; grant ALL on *.* to user4@localhost; +create user user5@localhost; grant select on test.* to user5@localhost; flush privileges; diff --git a/mysql-test/suite/perfschema/t/socket_summary_by_instance_func.test b/mysql-test/suite/perfschema/t/socket_summary_by_instance_func.test index d06edb0d78f..bf04e62e1b5 100644 --- a/mysql-test/suite/perfschema/t/socket_summary_by_instance_func.test +++ b/mysql-test/suite/perfschema/t/socket_summary_by_instance_func.test @@ -493,6 +493,7 @@ let $connect_user= root; --echo # length of user name = 10 character --echo # length of default db = 9 character #======================================================================== +CREATE USER 'root012345'@'localhost'; GRANT ALL PRIVILEGES ON *.* TO 'root012345'@'localhost'; let $connect_host= localhost; let $connect_db= mysqltest; @@ -504,6 +505,7 @@ DROP USER 'root012345'@'localhost'; --echo # length of user name = 14 character --echo # length of default db = 9 character #======================================================================== +CREATE USER 'root0123456789'@'localhost'; GRANT ALL PRIVILEGES ON *.* to 'root0123456789'@'localhost'; let $connect_host= localhost; let $connect_db= mysqltest; diff --git a/mysql-test/suite/plugins/r/audit_null.result b/mysql-test/suite/plugins/r/audit_null.result index 584d10e3a3b..75eb5a9f682 100644 --- a/mysql-test/suite/plugins/r/audit_null.result +++ b/mysql-test/suite/plugins/r/audit_null.result @@ -1,5 +1,6 @@ set @old_global_general_log=@@global.general_log; set global general_log=OFF; +create user testuser@localhost; grant select on *.* to testuser@localhost; install plugin audit_null soname 'adt_null'; select 1; diff --git a/mysql-test/suite/plugins/r/auth_v0100.result b/mysql-test/suite/plugins/r/auth_v0100.result index 53affc94947..6b5f5c917f3 100644 --- a/mysql-test/suite/plugins/r/auth_v0100.result +++ b/mysql-test/suite/plugins/r/auth_v0100.result @@ -5,6 +5,7 @@ auth_0x0100 1.0 mysql_native_password 2.0 mysql_old_password 2.0 create user tt identified via auth_0x0100; +create user zzzzzzzzzzzzzzzz; grant select on test.* to zzzzzzzzzzzzzzzz; connect(localhost,tt,,test,MASTER_MYPORT,MASTER_MYSOCK); ERROR 28000: Access denied for user 'tt'@'localhost' (using password: YES) diff --git a/mysql-test/suite/plugins/r/qc_info.result b/mysql-test/suite/plugins/r/qc_info.result index 850e6db53f4..0924b5bd12d 100644 --- a/mysql-test/suite/plugins/r/qc_info.result +++ b/mysql-test/suite/plugins/r/qc_info.result @@ -1,3 +1,5 @@ +set global query_cache_type=ON; +set local query_cache_type=ON; set global query_cache_size=1355776; create table t1 (a int not null); insert into t1 values (1),(2),(3); @@ -16,6 +18,7 @@ set global query_cache_size = 0; select * from information_schema.query_cache_info; STATEMENT_SCHEMA STATEMENT_TEXT RESULT_BLOCKS_COUNT RESULT_BLOCKS_SIZE RESULT_BLOCKS_SIZE_USED set global query_cache_size= default; +set global query_cache_type=default; show query_cache_info; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'query_cache_info' at line 1 flush query_cache_info; diff --git a/mysql-test/suite/plugins/r/qc_info_priv.result b/mysql-test/suite/plugins/r/qc_info_priv.result index c723eca1ec0..f7b538f46e4 100644 --- a/mysql-test/suite/plugins/r/qc_info_priv.result +++ b/mysql-test/suite/plugins/r/qc_info_priv.result @@ -1,3 +1,5 @@ +set global query_cache_type=ON; +set local query_cache_type=ON; set global query_cache_size=1355776; create table t1 (a int not null); insert into t1 values (1),(2),(3); @@ -21,3 +23,4 @@ count(*) drop user mysqltest; drop table t1; set global query_cache_size= default; +set global query_cache_type=default; diff --git a/mysql-test/suite/plugins/t/audit_null.test b/mysql-test/suite/plugins/t/audit_null.test index 3e32154ec3d..199e4ba154e 100644 --- a/mysql-test/suite/plugins/t/audit_null.test +++ b/mysql-test/suite/plugins/t/audit_null.test @@ -8,6 +8,7 @@ if (!$ADT_NULL_SO) { set @old_global_general_log=@@global.general_log; set global general_log=OFF; +create user testuser@localhost; grant select on *.* to testuser@localhost; --disable_ps_protocol diff --git a/mysql-test/suite/plugins/t/auth_v0100.test b/mysql-test/suite/plugins/t/auth_v0100.test index 3cf93ba67f2..51f8d8941ef 100644 --- a/mysql-test/suite/plugins/t/auth_v0100.test +++ b/mysql-test/suite/plugins/t/auth_v0100.test @@ -9,6 +9,7 @@ install soname 'auth_0x0100'; select plugin_name, plugin_type_version from information_schema.plugins where plugin_type='authentication' order by plugin_name; create user tt identified via auth_0x0100; +create user zzzzzzzzzzzzzzzz; grant select on test.* to zzzzzzzzzzzzzzzz; --replace_result $MASTER_MYSOCK MASTER_MYSOCK $MASTER_MYPORT MASTER_MYPORT diff --git a/mysql-test/suite/plugins/t/qc_info.test b/mysql-test/suite/plugins/t/qc_info.test index fcb523eca2c..ef759ce8eb4 100644 --- a/mysql-test/suite/plugins/t/qc_info.test +++ b/mysql-test/suite/plugins/t/qc_info.test @@ -8,6 +8,7 @@ set global query_cache_size = 0; select * from information_schema.query_cache_info; set global query_cache_size= default; +set global query_cache_type=default; --error ER_PARSE_ERROR show query_cache_info; diff --git a/mysql-test/suite/plugins/t/qc_info_init.inc b/mysql-test/suite/plugins/t/qc_info_init.inc index 9422bd7f141..1de99cc9029 100644 --- a/mysql-test/suite/plugins/t/qc_info_init.inc +++ b/mysql-test/suite/plugins/t/qc_info_init.inc @@ -3,6 +3,8 @@ if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'qu --skip QUERY_CACHE_INFO plugin is not active } +set global query_cache_type=ON; +set local query_cache_type=ON; set global query_cache_size=1355776; create table t1 (a int not null); diff --git a/mysql-test/suite/plugins/t/qc_info_priv.test b/mysql-test/suite/plugins/t/qc_info_priv.test index 31c394107c1..adcf882be9f 100644 --- a/mysql-test/suite/plugins/t/qc_info_priv.test +++ b/mysql-test/suite/plugins/t/qc_info_priv.test @@ -12,4 +12,4 @@ drop user mysqltest; drop table t1; set global query_cache_size= default; - +set global query_cache_type=default; diff --git a/mysql-test/suite/roles/admin.result b/mysql-test/suite/roles/admin.result index 44bf010b8ea..838f2ea165d 100644 --- a/mysql-test/suite/roles/admin.result +++ b/mysql-test/suite/roles/admin.result @@ -1,3 +1,4 @@ +create user foo@localhost; grant create user on *.* to foo@localhost; create role role1; create role role2 with admin current_user; diff --git a/mysql-test/suite/roles/admin.test b/mysql-test/suite/roles/admin.test index 03f3449200b..242518eb13d 100644 --- a/mysql-test/suite/roles/admin.test +++ b/mysql-test/suite/roles/admin.test @@ -1,5 +1,6 @@ source include/not_embedded.inc; +create user foo@localhost; grant create user on *.* to foo@localhost; ######################################## @@ -99,4 +100,3 @@ grant role4 to current_user; ######################################## drop role role1, role2, role3, role4, role5, role6; drop user foo@localhost; - diff --git a/mysql-test/suite/roles/create_and_drop_current.result b/mysql-test/suite/roles/create_and_drop_current.result index 382e5d10248..7e847677364 100644 --- a/mysql-test/suite/roles/create_and_drop_current.result +++ b/mysql-test/suite/roles/create_and_drop_current.result @@ -1,3 +1,4 @@ +create user foo@localhost; grant create user on *.* to foo@localhost; create user current_user; ERROR HY000: Operation CREATE USER failed for CURRENT_USER diff --git a/mysql-test/suite/roles/create_and_drop_current.test b/mysql-test/suite/roles/create_and_drop_current.test index 5250ef78f43..7ca8161a30c 100644 --- a/mysql-test/suite/roles/create_and_drop_current.test +++ b/mysql-test/suite/roles/create_and_drop_current.test @@ -12,6 +12,7 @@ --source include/not_embedded.inc +create user foo@localhost; grant create user on *.* to foo@localhost; --change_user foo diff --git a/mysql-test/suite/roles/definer.result b/mysql-test/suite/roles/definer.result index a2d84244c38..7ced6255629 100644 --- a/mysql-test/suite/roles/definer.result +++ b/mysql-test/suite/roles/definer.result @@ -9,6 +9,7 @@ grant role1 to current_user; create role role2; grant insert,select on mysqltest1.t1 to role2; grant event,execute,trigger on mysqltest1.* to role2; +create user foo@localhost; grant create view on mysqltest1.* to foo@localhost; create role role4; grant select on mysqltest1.t1 to role4; @@ -78,6 +79,7 @@ Warnings: Note 1449 The user specified as a definer ('role4'@'') does not exist select * from test.v5; ERROR HY000: The user specified as a definer ('role4'@'') does not exist +create user role4; grant select on mysqltest1.t1 to role4; show create view test.v5; View Create View character_set_client collation_connection @@ -104,7 +106,7 @@ create definer=current_role trigger tr1 before insert on t2 for each row insert t1 values (111, 222, 333); show create trigger tr1; Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -tr1 CREATE DEFINER=`role1` trigger tr1 before insert on t2 for each row +tr1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` trigger tr1 before insert on t2 for each row insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci set role none; insert t2 values (11,22,33); @@ -121,7 +123,7 @@ create definer=role2 trigger tr2 before delete on t2 for each row insert t1 values (111, 222, 333); show create trigger tr2; Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -tr2 CREATE DEFINER=`role2` trigger tr2 before delete on t2 for each row +tr2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role2` trigger tr2 before delete on t2 for each row insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci delete from t2 where a=1; select * from t1; @@ -139,7 +141,7 @@ Warnings: Note 1449 The user specified as a definer ('role3'@'%') does not exist show create trigger tr3; Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -tr3 CREATE DEFINER=`role3`@`%` trigger tr3 before update on t2 for each row +tr3 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role3`@`%` trigger tr3 before update on t2 for each row insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci update t2 set b=2 where a=2; ERROR HY000: The user specified as a definer ('role3'@'%') does not exist @@ -153,7 +155,7 @@ a b c flush tables; show create trigger tr2; Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -tr2 CREATE DEFINER=`role2`@`` trigger tr2 before delete on t2 for each row +tr2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role2`@`` trigger tr2 before delete on t2 for each row insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci delete from t2 where a=2; ERROR HY000: The user specified as a definer ('role2'@'%') does not exist @@ -170,7 +172,7 @@ set role role1; create definer=current_role procedure pr1() insert t1 values (111, 222, 333); show create procedure pr1; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pr1 CREATE DEFINER=`role1` PROCEDURE `pr1`() +pr1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` PROCEDURE `pr1`() insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci set role none; call pr1(); @@ -182,7 +184,7 @@ a b c create definer=role2 procedure pr2() insert t1 values (111, 222, 333); show create procedure pr2; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pr2 CREATE DEFINER=`role2` PROCEDURE `pr2`() +pr2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role2` PROCEDURE `pr2`() insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci call pr2(); select * from t1; @@ -196,7 +198,7 @@ Warnings: Note 1449 The user specified as a definer ('role3'@'%') does not exist show create procedure pr3; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pr3 CREATE DEFINER=`role3`@`%` PROCEDURE `pr3`() +pr3 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role3`@`%` PROCEDURE `pr3`() insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci call pr3(); ERROR HY000: The user specified as a definer ('role3'@'%') does not exist @@ -213,7 +215,7 @@ set role role1; create definer=current_role function fn1() returns int return (select sum(a+b) from t1); show create function fn1; Function sql_mode Create Function character_set_client collation_connection Database Collation -fn1 CREATE DEFINER=`role1` FUNCTION `fn1`() RETURNS int(11) +fn1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` FUNCTION `fn1`() RETURNS int(11) return (select sum(a+b) from t1) latin1 latin1_swedish_ci latin1_swedish_ci set role none; select fn1(); @@ -225,7 +227,7 @@ a b c create definer=role2 function fn2() returns int return (select sum(a+b) from t1); show create function fn2; Function sql_mode Create Function character_set_client collation_connection Database Collation -fn2 CREATE DEFINER=`role2` FUNCTION `fn2`() RETURNS int(11) +fn2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role2` FUNCTION `fn2`() RETURNS int(11) return (select sum(a+b) from t1) latin1 latin1_swedish_ci latin1_swedish_ci select fn2(); fn2() @@ -235,7 +237,7 @@ Warnings: Note 1449 The user specified as a definer ('role3'@'%') does not exist show create function fn3; Function sql_mode Create Function character_set_client collation_connection Database Collation -fn3 CREATE DEFINER=`role3`@`%` FUNCTION `fn3`() RETURNS int(11) +fn3 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role3`@`%` FUNCTION `fn3`() RETURNS int(11) return (select sum(a+b) from t1) latin1 latin1_swedish_ci latin1_swedish_ci select fn3(); ERROR HY000: The user specified as a definer ('role3'@'%') does not exist @@ -248,7 +250,7 @@ create definer=current_role event e1 on schedule every 1 second starts '2000-01- insert t1 values (111, 2, 0); show create event e1; Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation -e1 SYSTEM CREATE DEFINER=`role1` EVENT `e1` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 2, 0) latin1 latin1_swedish_ci latin1_swedish_ci +e1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SYSTEM CREATE DEFINER=`role1` EVENT `e1` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 2, 0) latin1 latin1_swedish_ci latin1_swedish_ci set role none; create definer=role3 event e3 on schedule every 1 second starts '2000-01-01' do insert t1 values (111, 3, 0); @@ -256,12 +258,12 @@ Warnings: Note 1449 The user specified as a definer ('role3'@'%') does not exist show create event e3; Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation -e3 SYSTEM CREATE DEFINER=`role3`@`%` EVENT `e3` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 3, 0) latin1 latin1_swedish_ci latin1_swedish_ci +e3 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SYSTEM CREATE DEFINER=`role3`@`%` EVENT `e3` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 3, 0) latin1 latin1_swedish_ci latin1_swedish_ci create definer=role2 event e2 on schedule every 1 second starts '2000-01-01' do insert t1 values (111, 4, 0); show create event e2; Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation -e2 SYSTEM CREATE DEFINER=`role2` EVENT `e2` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 4, 0) latin1 latin1_swedish_ci latin1_swedish_ci +e2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SYSTEM CREATE DEFINER=`role2` EVENT `e2` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 4, 0) latin1 latin1_swedish_ci latin1_swedish_ci set global event_scheduler=off; select distinct * from t1; a b c @@ -337,7 +339,7 @@ INSERT INTO `t2` VALUES (2,20,200); /*!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=`role1`*/ /*!50003 trigger tr1 before insert on t2 for each row insert t1 values (111, 222, 333) */;; @@ -353,7 +355,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=`role3`@`%`*/ /*!50003 trigger tr3 before update on t2 for each row insert t1 values (111, 222, 333) */;; @@ -369,7 +371,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=`role2`@``*/ /*!50003 trigger tr2 before delete on t2 for each row insert t1 values (111, 222, 333) */;; @@ -387,7 +389,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' */ ;; /*!50003 SET @saved_time_zone = @@time_zone */ ;; /*!50003 SET time_zone = 'SYSTEM' */ ;; /*!50106 CREATE*/ /*!50117 DEFINER=`role1`*/ /*!50106 EVENT `e1` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 2, 0) */ ;; @@ -404,7 +406,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' */ ;; /*!50003 SET @saved_time_zone = @@time_zone */ ;; /*!50003 SET time_zone = 'SYSTEM' */ ;; /*!50106 CREATE*/ /*!50117 DEFINER=`role2`*/ /*!50106 EVENT `e2` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 4, 0) */ ;; @@ -421,7 +423,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' */ ;; /*!50003 SET @saved_time_zone = @@time_zone */ ;; /*!50003 SET time_zone = 'SYSTEM' */ ;; /*!50106 CREATE*/ /*!50117 DEFINER=`role3`@`%`*/ /*!50106 EVENT `e3` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 3, 0) */ ;; @@ -439,7 +441,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 ;; CREATE DEFINER=`role1` FUNCTION `fn1`() RETURNS int(11) return (select sum(a+b) from t1) ;; @@ -455,7 +457,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 ;; CREATE DEFINER=`role2` FUNCTION `fn2`() RETURNS int(11) return (select sum(a+b) from t1) ;; @@ -471,7 +473,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 ;; CREATE DEFINER=`role3`@`%` FUNCTION `fn3`() RETURNS int(11) return (select sum(a+b) from t1) ;; @@ -487,7 +489,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 ;; CREATE DEFINER=`role1` PROCEDURE `pr1`() insert t1 values (111, 222, 333) ;; @@ -503,7 +505,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 ;; CREATE DEFINER=`role2`@`%` PROCEDURE `pr2`() insert t1 values (111, 222, 333) ;; @@ -519,7 +521,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 ;; CREATE DEFINER=`role3`@`%` PROCEDURE `pr3`() insert t1 values (111, 222, 333) ;; diff --git a/mysql-test/suite/roles/definer.test b/mysql-test/suite/roles/definer.test index 995c36a8511..3de4a6922c2 100644 --- a/mysql-test/suite/roles/definer.test +++ b/mysql-test/suite/roles/definer.test @@ -26,6 +26,7 @@ grant insert,select on mysqltest1.t1 to role2; grant event,execute,trigger on mysqltest1.* to role2; # create a non-priv user and a priv role granted to him +create user foo@localhost; grant create view on mysqltest1.* to foo@localhost; create role role4; grant select on mysqltest1.t1 to role4; @@ -99,6 +100,7 @@ show create view test.v5; --error ER_NO_SUCH_USER select * from test.v5; +create user role4; grant select on mysqltest1.t1 to role4; show create view test.v5; --error ER_NO_SUCH_USER diff --git a/mysql-test/suite/roles/drop_current_user-5176.result b/mysql-test/suite/roles/drop_current_user-5176.result index 42fb4ba36b7..ea9f4fa4f92 100644 --- a/mysql-test/suite/roles/drop_current_user-5176.result +++ b/mysql-test/suite/roles/drop_current_user-5176.result @@ -1,3 +1,4 @@ +create user foo@localhost; grant create user on *.* to foo@localhost; drop user foo@localhost; select * from information_schema.applicable_roles; diff --git a/mysql-test/suite/roles/drop_current_user-5176.test b/mysql-test/suite/roles/drop_current_user-5176.test index e022d3e8a86..27051345e8b 100644 --- a/mysql-test/suite/roles/drop_current_user-5176.test +++ b/mysql-test/suite/roles/drop_current_user-5176.test @@ -3,6 +3,7 @@ # --source include/not_embedded.inc +create user foo@localhost; grant create user on *.* to foo@localhost; --connect (foo,localhost,foo,,) drop user foo@localhost; diff --git a/mysql-test/suite/roles/grant-5771.result b/mysql-test/suite/roles/grant-5771.result index 4921641f3a5..5d79820e7a0 100644 --- a/mysql-test/suite/roles/grant-5771.result +++ b/mysql-test/suite/roles/grant-5771.result @@ -1,5 +1,6 @@ create database mysqltest1; create database mysqltest2; +create user foo@localhost; create role r1, r2; grant all on mysqltest1.* to r1; grant all on mysqltest2.* to r2; diff --git a/mysql-test/suite/roles/grant-5771.test b/mysql-test/suite/roles/grant-5771.test index 84c625a6067..3c8f5d2f7c0 100644 --- a/mysql-test/suite/roles/grant-5771.test +++ b/mysql-test/suite/roles/grant-5771.test @@ -6,6 +6,7 @@ create database mysqltest1; create database mysqltest2; +create user foo@localhost; create role r1, r2; grant all on mysqltest1.* to r1; grant all on mysqltest2.* to r2; diff --git a/mysql-test/suite/roles/grant_role_auto_create_user.result b/mysql-test/suite/roles/grant_role_auto_create_user.result index 81b25e5f527..674480758fb 100644 --- a/mysql-test/suite/roles/grant_role_auto_create_user.result +++ b/mysql-test/suite/roles/grant_role_auto_create_user.result @@ -1,8 +1,11 @@ create database db; create role auto_create; +create user auto_create; grant all on db.* to auto_create; +create user foo@localhost; grant auto_create to foo@localhost; -grant auto_create to bar@localhost identified by 'baz'; +create user bar@localhost identified by 'baz'; +grant auto_create to bar@localhost; set role 'auto_create'; use db; create table t1 (i int); diff --git a/mysql-test/suite/roles/grant_role_auto_create_user.test b/mysql-test/suite/roles/grant_role_auto_create_user.test index d2489a4ea74..0259d0a8ff6 100644 --- a/mysql-test/suite/roles/grant_role_auto_create_user.test +++ b/mysql-test/suite/roles/grant_role_auto_create_user.test @@ -5,10 +5,12 @@ create database db; create role auto_create; +create user auto_create; grant all on db.* to auto_create; - +create user foo@localhost; grant auto_create to foo@localhost; -grant auto_create to bar@localhost identified by 'baz'; +create user bar@localhost identified by 'baz'; +grant auto_create to bar@localhost; # Test if the users have been created and the role has been granted to them --connect (con1,localhost,foo,,) diff --git a/mysql-test/suite/roles/password.result b/mysql-test/suite/roles/password.result index 1992431e1a3..2d54db2ca64 100644 --- a/mysql-test/suite/roles/password.result +++ b/mysql-test/suite/roles/password.result @@ -1,3 +1,4 @@ +set sql_mode=''; create role r1; grant select on *.* to r1 identified by 'foobar'; drop user r1; diff --git a/mysql-test/suite/roles/password.test b/mysql-test/suite/roles/password.test index fed0f2f4a19..e5fff01ddbc 100644 --- a/mysql-test/suite/roles/password.test +++ b/mysql-test/suite/roles/password.test @@ -10,6 +10,7 @@ # max_queries_per_hour | max_updates_per_hour | max_connections_per_hour | max_user_connections #set password for ... = ... +set sql_mode=''; create role r1; # IDENTIFIED does not apply to roles, using it forces username context diff --git a/mysql-test/suite/roles/rpl_definer.result b/mysql-test/suite/roles/rpl_definer.result index eadb6315d9b..c9e4f66a267 100644 --- a/mysql-test/suite/roles/rpl_definer.result +++ b/mysql-test/suite/roles/rpl_definer.result @@ -21,19 +21,19 @@ create definer=current_role procedure pcr() select current_role; create definer=role1 procedure pr() select "role1"; show create procedure pcu; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pcu CREATE DEFINER=`root`@`localhost` PROCEDURE `pcu`() +pcu NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `pcu`() select current_user latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pu; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pu CREATE DEFINER=`root`@`localhost` PROCEDURE `pu`() +pu NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `pu`() select "root@localhost" latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pcr; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pcr CREATE DEFINER=`role1` PROCEDURE `pcr`() +pcr NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` PROCEDURE `pcr`() select current_role latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pr; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pr CREATE DEFINER=`role1` PROCEDURE `pr`() +pr NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` PROCEDURE `pr`() select "role1" latin1 latin1_swedish_ci latin1_swedish_ci [connection slave] set role role1; @@ -49,19 +49,19 @@ GRANT role2 TO 'role1' GRANT role2 TO 'root'@'localhost' WITH ADMIN OPTION show create procedure pcu; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pcu CREATE DEFINER=`root`@`localhost` PROCEDURE `pcu`() +pcu NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `pcu`() select current_user latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pu; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pu CREATE DEFINER=`root`@`localhost` PROCEDURE `pu`() +pu NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `pu`() select "root@localhost" latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pcr; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pcr CREATE DEFINER=`role1` PROCEDURE `pcr`() +pcr NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` PROCEDURE `pcr`() select current_role latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pr; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pr CREATE DEFINER=`role1` PROCEDURE `pr`() +pr NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` PROCEDURE `pr`() select "role1" latin1 latin1_swedish_ci latin1_swedish_ci drop procedure pcu; drop procedure pu; diff --git a/mysql-test/suite/rpl/r/rpl_binlog_grant.result b/mysql-test/suite/rpl/r/rpl_binlog_grant.result index ccc7116a949..0c780122e38 100644 --- a/mysql-test/suite/rpl/r/rpl_binlog_grant.result +++ b/mysql-test/suite/rpl/r/rpl_binlog_grant.result @@ -6,6 +6,7 @@ create table t (s1 int) engine=innodb; set @@autocommit=0; start transaction; insert into t values (1); +create user x@y; grant select on t to x@y; rollback; show grants for x@y; diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_function.result b/mysql-test/suite/rpl/r/rpl_create_drop_function.result index 66b1f485aa7..fc10d1f7c25 100644 --- a/mysql-test/suite/rpl/r/rpl_create_drop_function.result +++ b/mysql-test/suite/rpl/r/rpl_create_drop_function.result @@ -7,7 +7,7 @@ CONCAT('Hello, ', str, '!'); ERROR 42000: FUNCTION hello already exists SHOW CREATE FUNCTION hello; Function sql_mode Create Function character_set_client collation_connection Database Collation -hello CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(str CHAR(20)) RETURNS char(50) CHARSET latin1 +hello NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(str CHAR(20)) RETURNS char(50) CHARSET latin1 DETERMINISTIC RETURN CONCAT('Hello, ', str, '!') latin1 latin1_swedish_ci latin1_swedish_ci diff --git a/mysql-test/suite/rpl/r/rpl_current_user.result b/mysql-test/suite/rpl/r/rpl_current_user.result index a5e340b8850..3391af5de9c 100644 --- a/mysql-test/suite/rpl/r/rpl_current_user.result +++ b/mysql-test/suite/rpl/r/rpl_current_user.result @@ -8,6 +8,7 @@ CREATE VIEW test.v_procs_priv AS SELECT * FROM mysql.procs_priv WHERE User LIKE CREATE VIEW test.v_event AS SELECT definer FROM mysql.event WHERE name = 'e1'; CREATE PROCEDURE p1() SELECT 1; # bug48321_1-01234 has the max length(16) of user. +CREATE USER 'bug48321_1-01234'@'localhost'; GRANT ALL PRIVILEGES ON *.* TO 'bug48321_1-01234'@'localhost' WITH GRANT OPTION; # Test the max lengths of user and host names diff --git a/mysql-test/suite/rpl/r/rpl_ddl.result b/mysql-test/suite/rpl/r/rpl_ddl.result index 0173bf1e28d..df246cc547f 100644 --- a/mysql-test/suite/rpl/r/rpl_ddl.result +++ b/mysql-test/suite/rpl/r/rpl_ddl.result @@ -1,5 +1,7 @@ include/master-slave.inc [connection master] +set global sql_mode=''; +set local sql_mode=''; -------- switch to master ------- SET AUTOCOMMIT = 1; @@ -1620,4 +1622,5 @@ use test; DROP TEMPORARY TABLE mysqltest1.t22; DROP DATABASE mysqltest1; DROP DATABASE mysqltest3; +set global sql_mode=default; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_do_grant.result b/mysql-test/suite/rpl/r/rpl_do_grant.result index 4cbc2d7d9e9..a9f1d92cb42 100644 --- a/mysql-test/suite/rpl/r/rpl_do_grant.result +++ b/mysql-test/suite/rpl/r/rpl_do_grant.result @@ -6,6 +6,7 @@ flush privileges; delete from mysql.user where user=_binary'rpl_ignore_grant'; delete from mysql.db where user=_binary'rpl_ignore_grant'; flush privileges; +create user rpl_do_grant@localhost; grant select on *.* to rpl_do_grant@localhost; grant drop on test.* to rpl_do_grant@localhost; show grants for rpl_do_grant@localhost; @@ -113,7 +114,7 @@ GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `bug42217_db`.`upgrade_del_func` TO 'cr USE bug42217_db; SHOW CREATE FUNCTION upgrade_del_func; Function sql_mode Create Function character_set_client collation_connection Database Collation -upgrade_del_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET latin1 +upgrade_del_func NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET latin1 BEGIN RETURN "INSIDE upgrade_del_func()"; END latin1 latin1_swedish_ci latin1_swedish_ci @@ -124,7 +125,7 @@ INSIDE upgrade_del_func() USE bug42217_db; SHOW CREATE FUNCTION upgrade_del_func; Function sql_mode Create Function character_set_client collation_connection Database Collation -upgrade_del_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET latin1 +upgrade_del_func NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET latin1 BEGIN RETURN "INSIDE upgrade_del_func()"; END latin1 latin1_swedish_ci latin1_swedish_ci @@ -149,7 +150,7 @@ upgrade_alter_func() INSIDE upgrade_alter_func() SHOW CREATE FUNCTION upgrade_alter_func; Function sql_mode Create Function character_set_client collation_connection Database Collation -upgrade_alter_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_alter_func`() RETURNS char(30) CHARSET latin1 +upgrade_alter_func NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_alter_func`() RETURNS char(30) CHARSET latin1 BEGIN RETURN "INSIDE upgrade_alter_func()"; END latin1 latin1_swedish_ci latin1_swedish_ci diff --git a/mysql-test/suite/rpl/r/rpl_hrtime.result b/mysql-test/suite/rpl/r/rpl_hrtime.result index 5a6787d37ac..3b29d339739 100644 --- a/mysql-test/suite/rpl/r/rpl_hrtime.result +++ b/mysql-test/suite/rpl/r/rpl_hrtime.result @@ -35,7 +35,7 @@ use `test`/*!*/; SET TIMESTAMP=1293832861/*!*/; 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/suite/rpl/r/rpl_ignore_grant.result b/mysql-test/suite/rpl/r/rpl_ignore_grant.result index ae6d6ddfc8d..40baaa60b01 100644 --- a/mysql-test/suite/rpl/r/rpl_ignore_grant.result +++ b/mysql-test/suite/rpl/r/rpl_ignore_grant.result @@ -1,8 +1,12 @@ include/master-slave.inc [connection master] +set global sql_mode=""; +set local sql_mode=""; delete from mysql.user where user=_binary'rpl_ignore_grant'; delete from mysql.db where user=_binary'rpl_ignore_grant'; flush privileges; +set global sql_mode=""; +set local sql_mode=""; delete from mysql.user where user=_binary'rpl_ignore_grant'; delete from mysql.db where user=_binary'rpl_ignore_grant'; flush privileges; @@ -31,4 +35,6 @@ flush privileges; delete from mysql.user where user=_binary'rpl_ignore_grant'; delete from mysql.db where user=_binary'rpl_ignore_grant'; flush privileges; +set global sql_mode=default; +set global sql_mode=default; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_ignore_table.result b/mysql-test/suite/rpl/r/rpl_ignore_table.result index 8e7166fa278..aa5f6d3e6db 100644 --- a/mysql-test/suite/rpl/r/rpl_ignore_table.result +++ b/mysql-test/suite/rpl/r/rpl_ignore_table.result @@ -17,6 +17,7 @@ DROP TABLE t4; **** Adding GRANTS on master **** create table test.t1(a int); create table test.t4(a int); +set sql_mode=""; GRANT SELECT ON test.t1 TO mysqltest1@localhost; GRANT INSERT ON test.t4 TO mysqltest2@localhost; GRANT select, update, insert, references on t1 @@ -65,6 +66,7 @@ flush privileges; show grants for mysqltest5@somehost; Grants for mysqltest5@somehost GRANT USAGE ON *.* TO 'mysqltest5'@'somehost' +set sql_mode=""; **** Checking grants on slave **** show grants for mysqltest2@localhost; Grants for mysqltest2@localhost diff --git a/mysql-test/suite/rpl/r/rpl_mdev382.result b/mysql-test/suite/rpl/r/rpl_mdev382.result index bc245a073cd..2e3faaba7b6 100644 --- a/mysql-test/suite/rpl/r/rpl_mdev382.result +++ b/mysql-test/suite/rpl/r/rpl_mdev382.result @@ -362,7 +362,7 @@ use `ts``et`/*!*/; 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/suite/rpl/r/rpl_replicate_do.result b/mysql-test/suite/rpl/r/rpl_replicate_do.result index 27fd92db31c..c1e3ee60e10 100644 --- a/mysql-test/suite/rpl/r/rpl_replicate_do.result +++ b/mysql-test/suite/rpl/r/rpl_replicate_do.result @@ -34,15 +34,15 @@ t1 t2 show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -trg1 INSERT t1 set new.b=2 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -trg2 INSERT t2 set new.b=2 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1 INSERT t1 set new.b=2 BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg2 INSERT t2 set new.b=2 BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci *** slave *** show tables; Tables_in_test t1 show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -trg1 INSERT t1 set new.b=2 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1 INSERT t1 set new.b=2 BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci *** master *** drop trigger trg1; drop trigger trg2; diff --git a/mysql-test/suite/rpl/r/rpl_row_USER.result b/mysql-test/suite/rpl/r/rpl_row_USER.result index 5298395da20..47b94074ecf 100644 --- a/mysql-test/suite/rpl/r/rpl_row_USER.result +++ b/mysql-test/suite/rpl/r/rpl_row_USER.result @@ -1,5 +1,7 @@ include/master-slave.inc [connection master] +set global sql_mode=""; +set local sql_mode=""; DROP DATABASE IF EXISTS mysqltest1; CREATE DATABASE mysqltest1; CREATE USER tester IDENTIFIED BY 'test'; @@ -33,3 +35,5 @@ REVOKE ALL ON mysqltest1.* FROM ''@'localhost%'; DROP USER tester@'%'; DROP USER ''@'localhost%'; include/rpl_end.inc +set global sql_mode=default; +set local sql_mode=default; diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result b/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result index 5e7c3c10c1e..71747e825b9 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result @@ -103,7 +103,7 @@ a 7 8 9 -SET GLOBAL QUERY_CACHE_SIZE=0; +SET GLOBAL QUERY_CACHE_SIZE=default; DROP TABLE t1; ================ Test for BUG#22550 ================ include/rpl_reset.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_sp012.result b/mysql-test/suite/rpl/r/rpl_row_sp012.result index 57d92386638..e084d43f90a 100644 --- a/mysql-test/suite/rpl/r/rpl_row_sp012.result +++ b/mysql-test/suite/rpl/r/rpl_row_sp012.result @@ -3,6 +3,7 @@ include/master-slave.inc DROP PROCEDURE IF EXISTS test.p1; DROP PROCEDURE IF EXISTS test.p2; DROP PROCEDURE IF EXISTS test.p3; +create user user1@localhost; grant usage on *.* to user1@localhost; flush privileges; SELECT CURRENT_USER(); @@ -17,6 +18,7 @@ CREATE PROCEDURE test.p3 () SQL SECURITY INVOKER CALL test.p1(); GRANT EXECUTE ON PROCEDURE p1 TO user1@localhost; GRANT EXECUTE ON PROCEDURE p2 TO user1@localhost; GRANT EXECUTE ON PROCEDURE p3 TO user1@localhost; +set sql_mode=default; <******** Master user1 p3 & p2 calls *******> ---------------------------------------------- diff --git a/mysql-test/suite/rpl/r/rpl_set_statement.result b/mysql-test/suite/rpl/r/rpl_set_statement.result index 78d34bd0853..c9df74b0d3b 100644 --- a/mysql-test/suite/rpl/r/rpl_set_statement.result +++ b/mysql-test/suite/rpl/r/rpl_set_statement.result @@ -1,5 +1,7 @@ include/master-slave.inc [connection master] +set sql_mode=""; +set sql_mode=""; DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; call mtr.add_suppression("Unsafe statement written to the binary log*"); diff --git a/mysql-test/suite/rpl/r/rpl_sp.result b/mysql-test/suite/rpl/r/rpl_sp.result index 8ca72672073..ea9c8906217 100644 --- a/mysql-test/suite/rpl/r/rpl_sp.result +++ b/mysql-test/suite/rpl/r/rpl_sp.result @@ -1,5 +1,7 @@ include/master-slave.inc [connection master] +set local sql_mode=''; +set global sql_mode=''; drop database if exists mysqltest1; create database mysqltest1; use mysqltest1; @@ -1311,3 +1313,4 @@ drop table t1; drop procedure p1; # End of 5.5 tests. include/rpl_end.inc +set global sql_mode=default; diff --git a/mysql-test/suite/rpl/r/rpl_ssl.result b/mysql-test/suite/rpl/r/rpl_ssl.result index 35aaabc8c56..f02fa4e7b9f 100644 --- a/mysql-test/suite/rpl/r/rpl_ssl.result +++ b/mysql-test/suite/rpl/r/rpl_ssl.result @@ -1,5 +1,6 @@ include/master-slave.inc [connection master] +create user replssl@localhost; grant replication slave on *.* to replssl@localhost require ssl; create table t1 (t int auto_increment, KEY(t)); stop slave; diff --git a/mysql-test/suite/rpl/r/rpl_ssl1.result b/mysql-test/suite/rpl/r/rpl_ssl1.result index a7a56d7c5ee..c9fe21a7929 100644 --- a/mysql-test/suite/rpl/r/rpl_ssl1.result +++ b/mysql-test/suite/rpl/r/rpl_ssl1.result @@ -1,5 +1,6 @@ include/master-slave.inc [connection master] +create user replssl@localhost; grant replication slave on *.* to replssl@localhost require ssl; create table t1 (t int); stop slave; diff --git a/mysql-test/suite/rpl/t/rpl_binlog_grant.test b/mysql-test/suite/rpl/t/rpl_binlog_grant.test index 26ebb29ae8d..f8513021134 100644 --- a/mysql-test/suite/rpl/t/rpl_binlog_grant.test +++ b/mysql-test/suite/rpl/t/rpl_binlog_grant.test @@ -16,6 +16,7 @@ create table t (s1 int) engine=innodb; set @@autocommit=0; start transaction; insert into t values (1); +create user x@y; grant select on t to x@y; let $wait_binlog_event= grant select; source include/wait_for_binlog_event.inc; diff --git a/mysql-test/suite/rpl/t/rpl_current_user.test b/mysql-test/suite/rpl/t/rpl_current_user.test index c9ee6fcd576..1f5a82486e3 100644 --- a/mysql-test/suite/rpl/t/rpl_current_user.test +++ b/mysql-test/suite/rpl/t/rpl_current_user.test @@ -42,6 +42,7 @@ CREATE VIEW test.v_procs_priv AS SELECT * FROM mysql.procs_priv WHERE User LIKE CREATE VIEW test.v_event AS SELECT definer FROM mysql.event WHERE name = 'e1'; CREATE PROCEDURE p1() SELECT 1; --echo # bug48321_1-01234 has the max length(16) of user. +CREATE USER 'bug48321_1-01234'@'localhost'; GRANT ALL PRIVILEGES ON *.* TO 'bug48321_1-01234'@'localhost' WITH GRANT OPTION; --echo @@ -235,4 +236,3 @@ DROP PROCEDURE my_revoke; DROP FUNCTION my_user; DROP EVENT e1; --source include/rpl_end.inc - diff --git a/mysql-test/suite/rpl/t/rpl_do_grant.test b/mysql-test/suite/rpl/t/rpl_do_grant.test index 4d7d1bacc15..83b9a84744f 100644 --- a/mysql-test/suite/rpl/t/rpl_do_grant.test +++ b/mysql-test/suite/rpl/t/rpl_do_grant.test @@ -17,6 +17,7 @@ flush privileges; # test replication of GRANT connection master; +create user rpl_do_grant@localhost; grant select on *.* to rpl_do_grant@localhost; grant drop on test.* to rpl_do_grant@localhost; sync_slave_with_master; diff --git a/mysql-test/suite/rpl/t/rpl_ignore_grant.test b/mysql-test/suite/rpl/t/rpl_ignore_grant.test index 34074ba8ca9..ee049a515d7 100644 --- a/mysql-test/suite/rpl/t/rpl_ignore_grant.test +++ b/mysql-test/suite/rpl/t/rpl_ignore_grant.test @@ -6,11 +6,15 @@ source include/master-slave.inc; # do not be influenced by other tests. connection master; +set global sql_mode=""; +set local sql_mode=""; delete from mysql.user where user=_binary'rpl_ignore_grant'; delete from mysql.db where user=_binary'rpl_ignore_grant'; flush privileges; save_master_pos; connection slave; +set global sql_mode=""; +set local sql_mode=""; sync_with_master; # as these DELETE were not replicated, we need to do them manually on the # slave. @@ -56,5 +60,8 @@ delete from mysql.user where user=_binary'rpl_ignore_grant'; delete from mysql.db where user=_binary'rpl_ignore_grant'; flush privileges; +set global sql_mode=default; +connection master; +set global sql_mode=default; # End of 4.1 tests --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_ignore_table.test b/mysql-test/suite/rpl/t/rpl_ignore_table.test index b0019b70dc8..f9b01c83917 100644 --- a/mysql-test/suite/rpl/t/rpl_ignore_table.test +++ b/mysql-test/suite/rpl/t/rpl_ignore_table.test @@ -49,6 +49,7 @@ connection master; create table test.t1(a int); create table test.t4(a int); +set sql_mode=""; # Simple user that should not replicate GRANT SELECT ON test.t1 TO mysqltest1@localhost; @@ -87,7 +88,7 @@ show grants for mysqltest6@localhost; flush privileges; show grants for mysqltest5@somehost; - +set sql_mode=""; sync_slave_with_master; --echo **** Checking grants on slave **** diff --git a/mysql-test/suite/rpl/t/rpl_row_USER.test b/mysql-test/suite/rpl/t/rpl_row_USER.test index 7e05e4e0041..31826812efa 100644 --- a/mysql-test/suite/rpl/t/rpl_row_USER.test +++ b/mysql-test/suite/rpl/t/rpl_row_USER.test @@ -11,6 +11,8 @@ # Includes -- source include/have_binlog_format_row.inc -- source include/master-slave.inc +set global sql_mode=""; +set local sql_mode=""; # Begin clean up test section connection master; @@ -58,3 +60,6 @@ sync_slave_with_master; # End of 5.0 test case --source include/rpl_end.inc + +set global sql_mode=default; +set local sql_mode=default; diff --git a/mysql-test/suite/rpl/t/rpl_row_basic_11bugs.test b/mysql-test/suite/rpl/t/rpl_row_basic_11bugs.test index f4b371b737f..82929d57db5 100644 --- a/mysql-test/suite/rpl/t/rpl_row_basic_11bugs.test +++ b/mysql-test/suite/rpl/t/rpl_row_basic_11bugs.test @@ -91,7 +91,7 @@ INSERT INTO t1 VALUES (7),(8),(9); sync_slave_with_master; SELECT * FROM t1; -SET GLOBAL QUERY_CACHE_SIZE=0; +SET GLOBAL QUERY_CACHE_SIZE=default; --connection master DROP TABLE t1; diff --git a/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test b/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test index ed0f31b75be..a44cc18c0ed 100644 --- a/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test +++ b/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test @@ -21,6 +21,7 @@ --disable_query_log select "---Setup Section --" as ""; +set sql_mode=""; --enable_query_log set timestamp=1000000000; diff --git a/mysql-test/suite/rpl/t/rpl_row_sp012.test b/mysql-test/suite/rpl/t/rpl_row_sp012.test index 23bc92b51cf..80d312f1878 100644 --- a/mysql-test/suite/rpl/t/rpl_row_sp012.test +++ b/mysql-test/suite/rpl/t/rpl_row_sp012.test @@ -23,6 +23,7 @@ DROP PROCEDURE IF EXISTS test.p3; # Begin test section 1 # Create user user1 with no particular access rights +create user user1@localhost; grant usage on *.* to user1@localhost; flush privileges; @@ -34,6 +35,7 @@ CREATE PROCEDURE test.p3 () SQL SECURITY INVOKER CALL test.p1(); GRANT EXECUTE ON PROCEDURE p1 TO user1@localhost; GRANT EXECUTE ON PROCEDURE p2 TO user1@localhost; GRANT EXECUTE ON PROCEDURE p3 TO user1@localhost; +set sql_mode=default; # Need to wait for the rights to be applied at the slave sync_slave_with_master; diff --git a/mysql-test/suite/rpl/t/rpl_set_statement.test b/mysql-test/suite/rpl/t/rpl_set_statement.test index 8a3087bec6d..5bc54e44e34 100644 --- a/mysql-test/suite/rpl/t/rpl_set_statement.test +++ b/mysql-test/suite/rpl/t/rpl_set_statement.test @@ -1,5 +1,9 @@ --source include/master-slave.inc --source include/have_binlog_format_statement.inc +set sql_mode=""; +connection slave; +set sql_mode=""; +connection master; --disable_warnings DROP TABLE IF EXISTS t1; diff --git a/mysql-test/suite/rpl/t/rpl_sp.test b/mysql-test/suite/rpl/t/rpl_sp.test index c978a145a92..124d98cd29b 100644 --- a/mysql-test/suite/rpl/t/rpl_sp.test +++ b/mysql-test/suite/rpl/t/rpl_sp.test @@ -6,6 +6,8 @@ source include/have_binlog_format_mixed.inc; source include/master-slave.inc; +set local sql_mode=''; +set global sql_mode=''; # we need a db != test, where we don't have automatic grants --disable_warnings @@ -739,3 +741,4 @@ drop procedure p1; # Cleanup sync_slave_with_master; --source include/rpl_end.inc +set global sql_mode=default; diff --git a/mysql-test/suite/rpl/t/rpl_ssl.test b/mysql-test/suite/rpl/t/rpl_ssl.test index 43289428d45..9e42764715b 100644 --- a/mysql-test/suite/rpl/t/rpl_ssl.test +++ b/mysql-test/suite/rpl/t/rpl_ssl.test @@ -3,6 +3,7 @@ source include/master-slave.inc; # create a user for replication that requires ssl encryption connection master; +create user replssl@localhost; grant replication slave on *.* to replssl@localhost require ssl; create table t1 (t int auto_increment, KEY(t)); diff --git a/mysql-test/suite/rpl/t/rpl_ssl1.test b/mysql-test/suite/rpl/t/rpl_ssl1.test index f5f63a00033..d994dd21c1d 100644 --- a/mysql-test/suite/rpl/t/rpl_ssl1.test +++ b/mysql-test/suite/rpl/t/rpl_ssl1.test @@ -7,6 +7,7 @@ source include/master-slave.inc; # creating replication user for whom ssl auth is required # preparing playground connection master; +create user replssl@localhost; grant replication slave on *.* to replssl@localhost require ssl; create table t1 (t int); diff --git a/mysql-test/suite/sys_vars/r/back_log_basic.result b/mysql-test/suite/sys_vars/r/back_log_basic.result index d68e72a8a4a..0940ebeef8c 100644 --- a/mysql-test/suite/sys_vars/r/back_log_basic.result +++ b/mysql-test/suite/sys_vars/r/back_log_basic.result @@ -1,20 +1,20 @@ select @@global.back_log; @@global.back_log -150 +80 select @@session.back_log; ERROR HY000: Variable 'back_log' is a GLOBAL variable show global variables like 'back_log'; Variable_name Value -back_log 150 +back_log 80 show session variables like 'back_log'; Variable_name Value -back_log 150 +back_log 80 select * from information_schema.global_variables where variable_name='back_log'; VARIABLE_NAME VARIABLE_VALUE -BACK_LOG 150 +BACK_LOG 80 select * from information_schema.session_variables where variable_name='back_log'; VARIABLE_NAME VARIABLE_VALUE -BACK_LOG 150 +BACK_LOG 80 set global back_log=1; ERROR HY000: Variable 'back_log' is a read only variable set session back_log=1; diff --git a/mysql-test/suite/sys_vars/r/join_buffer_size_basic.result b/mysql-test/suite/sys_vars/r/join_buffer_size_basic.result index 2aa8b7dd80a..48131c96fce 100644 --- a/mysql-test/suite/sys_vars/r/join_buffer_size_basic.result +++ b/mysql-test/suite/sys_vars/r/join_buffer_size_basic.result @@ -1,20 +1,20 @@ SET @start_global_value = @@global.join_buffer_size; SELECT @start_global_value; @start_global_value -131072 +262144 SET @start_session_value = @@session.join_buffer_size; SELECT @start_session_value; @start_session_value -131072 +262144 '#--------------------FN_DYNVARS_053_01-------------------------#' SET @@global.join_buffer_size = DEFAULT; SELECT @@global.join_buffer_size; @@global.join_buffer_size -131072 +262144 SET @@session.join_buffer_size = DEFAULT; SELECT @@session.join_buffer_size; @@session.join_buffer_size -131072 +262144 '#--------------------FN_DYNVARS_053_03-------------------------#' SET @@global.join_buffer_size = 8200; Warnings: @@ -172,8 +172,8 @@ ERROR 42S22: Unknown column 'join_buffer_size' in 'field list' SET @@global.join_buffer_size = @start_global_value; SELECT @@global.join_buffer_size; @@global.join_buffer_size -131072 +262144 SET @@session.join_buffer_size = @start_session_value; SELECT @@session.join_buffer_size; @@session.join_buffer_size -131072 +262144 diff --git a/mysql-test/suite/sys_vars/r/max_allowed_packet_basic.result b/mysql-test/suite/sys_vars/r/max_allowed_packet_basic.result index ffa43415bd3..79708c480a8 100644 --- a/mysql-test/suite/sys_vars/r/max_allowed_packet_basic.result +++ b/mysql-test/suite/sys_vars/r/max_allowed_packet_basic.result @@ -1,7 +1,7 @@ SET @start_global_value = @@global.max_allowed_packet; SELECT @start_global_value; @start_global_value -1048576 +4194304 SET @@global.max_allowed_packet = DEFAULT; '#--------------------FN_DYNVARS_070_01-------------------------#' SET @@global.max_allowed_packet = 1000; @@ -11,19 +11,19 @@ Warning 1708 The value of 'max_allowed_packet' should be no less than the value SET @@global.max_allowed_packet = DEFAULT; SELECT @@global.max_allowed_packet; @@global.max_allowed_packet -1048576 +4194304 SET @@session.max_allowed_packet = 20000; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SET @@session.max_allowed_packet = DEFAULT; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 '#--------------------FN_DYNVARS_070_02-------------------------#' SET @@global.max_allowed_packet = DEFAULT; SELECT @@global.max_allowed_packet = 1048576; @@global.max_allowed_packet = 1048576 -1 +0 '#--------------------FN_DYNVARS_070_03-------------------------#' SET @@global.max_allowed_packet = 1024; Warnings: @@ -53,27 +53,27 @@ SET @@session.max_allowed_packet = 1024; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 SET @@session.max_allowed_packet = 1025; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 SET @@session.max_allowed_packet = 65535; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 SET @@session.max_allowed_packet = 1073741824; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 SET @@session.max_allowed_packet = 1073741823; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 '#------------------FN_DYNVARS_070_05-----------------------#' SET @@global.max_allowed_packet = 0; Warnings: @@ -116,29 +116,29 @@ SET @@session.max_allowed_packet = 0; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 SET @@session.max_allowed_packet = 1023; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 SET @@session.max_allowed_packet = -2; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 SET @@session.max_allowed_packet = 65530.34; ERROR 42000: Incorrect argument type to variable 'max_allowed_packet' SET @@session.max_allowed_packet = 10737418241; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 SET @@session.max_allowed_packet = test; ERROR 42000: Incorrect argument type to variable 'max_allowed_packet' SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 '#------------------FN_DYNVARS_070_06-----------------------#' SELECT @@global.max_allowed_packet = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES @@ -187,7 +187,7 @@ SET max_allowed_packet = 1024; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@max_allowed_packet; @@max_allowed_packet -1048576 +4194304 SELECT local.max_allowed_packet; ERROR 42S02: Unknown table 'local' in field list SELECT session.max_allowed_packet; @@ -197,7 +197,7 @@ ERROR 42S22: Unknown column 'max_allowed_packet' in 'field list' SET @@global.max_allowed_packet = @start_global_value; SELECT @@global.max_allowed_packet; @@global.max_allowed_packet -1048576 +4194304 SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 diff --git a/mysql-test/suite/sys_vars/r/max_allowed_packet_func.result b/mysql-test/suite/sys_vars/r/max_allowed_packet_func.result index 1e20776973c..1a848cbae4b 100644 --- a/mysql-test/suite/sys_vars/r/max_allowed_packet_func.result +++ b/mysql-test/suite/sys_vars/r/max_allowed_packet_func.result @@ -14,7 +14,7 @@ SET @@session.max_allowed_packet = 1024; ERROR HY000: SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value SELECT @@session.max_allowed_packet; @@session.max_allowed_packet -1048576 +4194304 SET @@session.net_buffer_length = 1024; ERROR HY000: SESSION variable 'net_buffer_length' is read-only. Use SET GLOBAL to assign the value SELECT @@session.net_buffer_length; diff --git a/mysql-test/suite/sys_vars/r/max_user_connections_func.result b/mysql-test/suite/sys_vars/r/max_user_connections_func.result index 04999a0a43f..4a71bf1b3f9 100644 --- a/mysql-test/suite/sys_vars/r/max_user_connections_func.result +++ b/mysql-test/suite/sys_vars/r/max_user_connections_func.result @@ -2,6 +2,7 @@ SET @default_max_user_connections = @@global.max_user_connections; Set Global max_user_connections=2; +CREATE USER test@localhost; GRANT USAGE on *.* TO test@localhost; '#--------------------FN_DYNVARS_114_01-------------------------#' ** Connecting conn1 using username 'test' ** diff --git a/mysql-test/suite/sys_vars/r/query_cache_limit_func.result b/mysql-test/suite/sys_vars/r/query_cache_limit_func.result index bbbb273428a..8dde5c3603b 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_limit_func.result +++ b/mysql-test/suite/sys_vars/r/query_cache_limit_func.result @@ -3,6 +3,8 @@ SET @global_query_cache_limit = @@global.query_cache_limit; SET @global_query_cache_size = @@global.query_cache_size; SET @global_query_cache_type = @@global.query_cache_type; +SET GLOBAL query_cache_type= ON; +SET LOCAL query_cache_type= ON; ** warnings ** DROP TABLE IF EXISTS t; ** creating table ** diff --git a/mysql-test/suite/sys_vars/r/query_cache_size_basic.result b/mysql-test/suite/sys_vars/r/query_cache_size_basic.result index 319948deefa..ee959a45fa0 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_size_basic.result +++ b/mysql-test/suite/sys_vars/r/query_cache_size_basic.result @@ -6,7 +6,7 @@ Warning 1292 Truncated incorrect query_cache_size value: '99' SET @@global.query_cache_size = DEFAULT; SELECT @@global.query_cache_size; @@global.query_cache_size -0 +1048576 '#---------------------FN_DYNVARS_133_02-------------------------#' SET @@global.query_cache_size = @start_value; SELECT @@global.query_cache_size = @start_value; diff --git a/mysql-test/suite/sys_vars/r/query_cache_type_basic.result b/mysql-test/suite/sys_vars/r/query_cache_type_basic.result index bb49d671840..dd59f503527 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_type_basic.result +++ b/mysql-test/suite/sys_vars/r/query_cache_type_basic.result @@ -1,22 +1,22 @@ SET @global_start_value = @@global.query_cache_type; SELECT @global_start_value; @global_start_value -ON +OFF SET @session_start_value = @@session.query_cache_type; SELECT @session_start_value; @session_start_value -ON +OFF '#--------------------FN_DYNVARS_134_01------------------------#' SET @@global.query_cache_type = 0; SET @@global.query_cache_type = DEFAULT; SELECT @@global.query_cache_type; @@global.query_cache_type -ON +OFF SET @@session.query_cache_type = 0; SET @@session.query_cache_type = DEFAULT; SELECT @@session.query_cache_type; @@session.query_cache_type -ON +OFF '#---------------------FN_DYNVARS_134_02-------------------------#' SET @@global.query_cache_type = NULL; ERROR 42000: Variable 'query_cache_type' can't be set to the value of 'NULL' @@ -115,8 +115,8 @@ OFF SET @@global.query_cache_type = @global_start_value; SELECT @@global.query_cache_type; @@global.query_cache_type -ON +OFF SET @@session.query_cache_type = @session_start_value; SELECT @@session.query_cache_type; @@session.query_cache_type -ON +OFF diff --git a/mysql-test/suite/sys_vars/r/query_cache_type_func.result b/mysql-test/suite/sys_vars/r/query_cache_type_func.result index af121244572..c016f05e119 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_type_func.result +++ b/mysql-test/suite/sys_vars/r/query_cache_type_func.result @@ -18,9 +18,12 @@ SET @@GLOBAL.query_cache_size = 65536; SET @@query_cache_type = DEFAULT; SELECT @@query_cache_type; @@query_cache_type -ON -ON Expected +OFF +OFF Expected +SET @@query_cache_type = ON; +ERROR HY000: Query cache is globally disabled and you can't enable it only for this session '#--------------------FN_DYNVARS_135_02-------------------------#' +SET GLOBAL query_cache_type= ON; SET @@query_cache_type = NULL; ERROR 42000: Variable 'query_cache_type' can't be set to the value of 'NULL' Expected error: Wrong value for variable diff --git a/mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result b/mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result index c4342f3bca8..5868bd597eb 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result +++ b/mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result @@ -7,6 +7,9 @@ SET @start_global_value = @@GLOBAL.query_cache_wlock_invalidate; SET @start_session_value = @@SESSION.query_cache_wlock_invalidate; SET @old_cache_size = @@GLOBAL.query_cache_size; SET @old_cache_type = @@GLOBAL.query_cache_type; +SET GLOBAL query_cache_type= ON; +SET LOCAL query_cache_type= ON; +SET LOCAL query_cache_type= ON; CREATE TABLE t1(id int, value varchar(10)); INSERT INTO t1 VALUES(1, 'val1'); INSERT INTO t1 VALUES(2, 'val2'); diff --git a/mysql-test/suite/sys_vars/r/secure_auth_basic.result b/mysql-test/suite/sys_vars/r/secure_auth_basic.result index 9f12a1760a4..35f283de278 100644 --- a/mysql-test/suite/sys_vars/r/secure_auth_basic.result +++ b/mysql-test/suite/sys_vars/r/secure_auth_basic.result @@ -1,19 +1,19 @@ SET @global_start_value = @@global.secure_auth; SELECT @global_start_value; @global_start_value -0 +1 '#--------------------FN_DYNVARS_143_01------------------------#' SET @@global.secure_auth = 1; SET @@global.secure_auth = DEFAULT; SELECT @@global.secure_auth; @@global.secure_auth -0 +1 '#---------------------FN_DYNVARS_143_02-------------------------#' SET secure_auth = 1; ERROR HY000: Variable 'secure_auth' is a GLOBAL variable and should be set with SET GLOBAL SELECT @@secure_auth; @@secure_auth -0 +1 SELECT global.secure_auth; ERROR 42S02: Unknown table 'global' in field list SET global secure_auth = 1; @@ -102,4 +102,4 @@ SELECT @@global.secure_auth; SET @@global.secure_auth = @global_start_value; SELECT @@global.secure_auth; @@global.secure_auth -0 +1 diff --git a/mysql-test/suite/sys_vars/r/sql_mode_basic.result b/mysql-test/suite/sys_vars/r/sql_mode_basic.result index c806fadc4ed..b3c76b12b64 100644 --- a/mysql-test/suite/sys_vars/r/sql_mode_basic.result +++ b/mysql-test/suite/sys_vars/r/sql_mode_basic.result @@ -1,22 +1,22 @@ SET @global_start_value = @@global.sql_mode; SELECT @global_start_value; @global_start_value - +NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SET @session_start_value = @@session.sql_mode; SELECT @session_start_value; @session_start_value - +NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION '#--------------------FN_DYNVARS_152_01------------------------#' SET @@global.sql_mode = ANSI; SET @@global.sql_mode = DEFAULT; SELECT @@global.sql_mode; @@global.sql_mode - +NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SET @@session.sql_mode = ANSI; SET @@session.sql_mode = DEFAULT; SELECT @@session.sql_mode; @@session.sql_mode - +NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION '#---------------------FN_DYNVARS_152_02-------------------------#' SET @@global.sql_mode = NULL; ERROR 42000: Variable 'sql_mode' can't be set to the value of 'NULL' @@ -420,8 +420,8 @@ ANSI_QUOTES SET @@global.sql_mode = @global_start_value; SELECT @@global.sql_mode; @@global.sql_mode - +NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SET @@session.sql_mode = @session_start_value; SELECT @@session.sql_mode; @@session.sql_mode - +NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION diff --git a/mysql-test/suite/sys_vars/r/sync_master_info_basic.result b/mysql-test/suite/sys_vars/r/sync_master_info_basic.result index 940282f629a..ea8b7d7a483 100644 --- a/mysql-test/suite/sys_vars/r/sync_master_info_basic.result +++ b/mysql-test/suite/sys_vars/r/sync_master_info_basic.result @@ -1,24 +1,24 @@ SET @start_global_value = @@global.sync_master_info; SELECT @start_global_value; @start_global_value -0 +10000 select @@global.sync_master_info; @@global.sync_master_info -0 +10000 select @@session.sync_master_info; ERROR HY000: Variable 'sync_master_info' is a GLOBAL variable show global variables like 'sync_master_info'; Variable_name Value -sync_master_info 0 +sync_master_info 10000 show session variables like 'sync_master_info'; Variable_name Value -sync_master_info 0 +sync_master_info 10000 select * from information_schema.global_variables where variable_name='sync_master_info'; VARIABLE_NAME VARIABLE_VALUE -SYNC_MASTER_INFO 0 +SYNC_MASTER_INFO 10000 select * from information_schema.session_variables where variable_name='sync_master_info'; VARIABLE_NAME VARIABLE_VALUE -SYNC_MASTER_INFO 0 +SYNC_MASTER_INFO 10000 set global sync_master_info=1; select @@global.sync_master_info; @@global.sync_master_info @@ -45,4 +45,4 @@ select @@global.sync_master_info; SET @@global.sync_master_info = @start_global_value; SELECT @@global.sync_master_info; @@global.sync_master_info -0 +10000 diff --git a/mysql-test/suite/sys_vars/r/sync_relay_log_basic.result b/mysql-test/suite/sys_vars/r/sync_relay_log_basic.result index a98fcbfe44c..5196f8ff2dc 100644 --- a/mysql-test/suite/sys_vars/r/sync_relay_log_basic.result +++ b/mysql-test/suite/sys_vars/r/sync_relay_log_basic.result @@ -1,24 +1,24 @@ SET @start_global_value = @@global.sync_relay_log; SELECT @start_global_value; @start_global_value -0 +10000 select @@global.sync_relay_log; @@global.sync_relay_log -0 +10000 select @@session.sync_relay_log; ERROR HY000: Variable 'sync_relay_log' is a GLOBAL variable show global variables like 'sync_relay_log'; Variable_name Value -sync_relay_log 0 +sync_relay_log 10000 show session variables like 'sync_relay_log'; Variable_name Value -sync_relay_log 0 +sync_relay_log 10000 select * from information_schema.global_variables where variable_name='sync_relay_log'; VARIABLE_NAME VARIABLE_VALUE -SYNC_RELAY_LOG 0 +SYNC_RELAY_LOG 10000 select * from information_schema.session_variables where variable_name='sync_relay_log'; VARIABLE_NAME VARIABLE_VALUE -SYNC_RELAY_LOG 0 +SYNC_RELAY_LOG 10000 set global sync_relay_log=1; select @@global.sync_relay_log; @@global.sync_relay_log @@ -45,4 +45,4 @@ select @@global.sync_relay_log; SET @@global.sync_relay_log = @start_global_value; SELECT @@global.sync_relay_log; @@global.sync_relay_log -0 +10000 diff --git a/mysql-test/suite/sys_vars/r/sync_relay_log_info_basic.result b/mysql-test/suite/sys_vars/r/sync_relay_log_info_basic.result index 4900bf166b0..aa84affb7bb 100644 --- a/mysql-test/suite/sys_vars/r/sync_relay_log_info_basic.result +++ b/mysql-test/suite/sys_vars/r/sync_relay_log_info_basic.result @@ -1,24 +1,24 @@ SET @start_global_value = @@global.sync_relay_log_info; SELECT @start_global_value; @start_global_value -0 +10000 select @@global.sync_relay_log_info; @@global.sync_relay_log_info -0 +10000 select @@session.sync_relay_log_info; ERROR HY000: Variable 'sync_relay_log_info' is a GLOBAL variable show global variables like 'sync_relay_log_info'; Variable_name Value -sync_relay_log_info 0 +sync_relay_log_info 10000 show session variables like 'sync_relay_log_info'; Variable_name Value -sync_relay_log_info 0 +sync_relay_log_info 10000 select * from information_schema.global_variables where variable_name='sync_relay_log_info'; VARIABLE_NAME VARIABLE_VALUE -SYNC_RELAY_LOG_INFO 0 +SYNC_RELAY_LOG_INFO 10000 select * from information_schema.session_variables where variable_name='sync_relay_log_info'; VARIABLE_NAME VARIABLE_VALUE -SYNC_RELAY_LOG_INFO 0 +SYNC_RELAY_LOG_INFO 10000 set global sync_relay_log_info=1; select @@global.sync_relay_log_info; @@global.sync_relay_log_info @@ -45,4 +45,4 @@ select @@global.sync_relay_log_info; SET @@global.sync_relay_log_info = @start_global_value; SELECT @@global.sync_relay_log_info; @@global.sync_relay_log_info -0 +10000 diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index f7470d93842..1f914394c26 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -81,13 +81,13 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME BACK_LOG SESSION_VALUE NULL -GLOBAL_VALUE 150 -GLOBAL_VALUE_ORIGIN COMPILE-TIME +GLOBAL_VALUE 80 +GLOBAL_VALUE_ORIGIN AUTO DEFAULT_VALUE 150 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time -NUMERIC_MIN_VALUE 1 +VARIABLE_COMMENT 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 +NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 65535 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL @@ -1159,8 +1159,8 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME HOST_CACHE_SIZE SESSION_VALUE NULL -GLOBAL_VALUE 128 -GLOBAL_VALUE_ORIGIN COMPILE-TIME +GLOBAL_VALUE 279 +GLOBAL_VALUE_ORIGIN AUTO DEFAULT_VALUE 128 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED @@ -1298,10 +1298,10 @@ ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME JOIN_BUFFER_SIZE -SESSION_VALUE 131072 -GLOBAL_VALUE 131072 +SESSION_VALUE 262144 +GLOBAL_VALUE 262144 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 131072 +DEFAULT_VALUE 262144 VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The size of the buffer that is used for joins @@ -1361,9 +1361,9 @@ DEFAULT_VALUE 134217728 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The size of the buffer used for index blocks for MyISAM tables. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_BLOCK_SIZE 4096 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1375,9 +1375,9 @@ DEFAULT_VALUE 300 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT This characterizes the number of hits a hot block has to be untouched until it is considered aged enough to be downgraded to a warm block. This specifies the percentage ratio of that number of hits to the total number of blocks in key cache -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 100 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 100 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1389,9 +1389,9 @@ DEFAULT_VALUE 1024 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The default size of key cache blocks -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 512 +NUMERIC_MAX_VALUE 16384 +NUMERIC_BLOCK_SIZE 512 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1403,9 +1403,9 @@ DEFAULT_VALUE 100 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The minimum percentage of warm blocks in key cache -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 1 +NUMERIC_MAX_VALUE 100 +NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1417,9 +1417,9 @@ DEFAULT_VALUE 512 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Number of hash buckets for open and changed files. If you have a lot of MyISAM files open you should increase this for faster flush of changes. A good value is probably 1/10 of number of possible open MyISAM files. -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 128 +NUMERIC_MAX_VALUE 16384 +NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1431,9 +1431,9 @@ DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The number of segments in a key cache -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 64 +NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1732,10 +1732,10 @@ ENUM_VALUE_LIST OFF,ON READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME MAX_ALLOWED_PACKET -SESSION_VALUE 1048576 -GLOBAL_VALUE 1048576 +SESSION_VALUE 4194304 +GLOBAL_VALUE 4194304 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 1048576 +DEFAULT_VALUE 4194304 VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Max packet length to send to or receive from the server @@ -1915,7 +1915,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME MAX_LONG_DATA_SIZE SESSION_VALUE NULL -GLOBAL_VALUE 1048576 +GLOBAL_VALUE 4194304 GLOBAL_VALUE_ORIGIN AUTO DEFAULT_VALUE 1048576 VARIABLE_SCOPE GLOBAL @@ -3091,9 +3091,9 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_SIZE SESSION_VALUE NULL -GLOBAL_VALUE 0 +GLOBAL_VALUE 1048576 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 0 +DEFAULT_VALUE 1048576 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The memory allocated to store results from old queries @@ -3118,10 +3118,10 @@ ENUM_VALUE_LIST OFF,ON READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME QUERY_CACHE_TYPE -SESSION_VALUE ON -GLOBAL_VALUE ON +SESSION_VALUE OFF +GLOBAL_VALUE OFF GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE ON +DEFAULT_VALUE OFF VARIABLE_SCOPE SESSION VARIABLE_TYPE ENUM VARIABLE_COMMENT OFF = Don't cache or retrieve results. ON = Cache all results except SELECT SQL_NO_CACHE ... queries. DEMAND = Cache only SELECT SQL_CACHE ... queries @@ -3231,9 +3231,9 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SECURE_AUTH SESSION_VALUE NULL -GLOBAL_VALUE OFF +GLOBAL_VALUE ON GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE OFF +DEFAULT_VALUE ON VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BOOLEAN VARIABLE_COMMENT Disallow authentication for accounts that have old (pre-4.1) passwords @@ -3511,9 +3511,9 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME SQL_MODE SESSION_VALUE ANSI_QUOTES -GLOBAL_VALUE +GLOBAL_VALUE NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE +DEFAULT_VALUE NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION VARIABLE_SCOPE SESSION VARIABLE_TYPE SET VARIABLE_COMMENT Sets the sql mode @@ -3763,12 +3763,12 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME SYNC_MASTER_INFO SESSION_VALUE NULL -GLOBAL_VALUE 0 +GLOBAL_VALUE 10000 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 0 +DEFAULT_VALUE 10000 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE INT UNSIGNED -VARIABLE_COMMENT Synchronously flush master info to disk after every #th event. Use 0 (default) to disable synchronous flushing +VARIABLE_COMMENT Synchronously flush master info to disk after every #th event. Use 0 to disable synchronous flushing NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 NUMERIC_BLOCK_SIZE 1 @@ -3791,9 +3791,9 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TABLE_OPEN_CACHE SESSION_VALUE NULL -GLOBAL_VALUE 400 +GLOBAL_VALUE 2000 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 400 +DEFAULT_VALUE 2000 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The number of cached open tables diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index d98ca0129eb..8fa72a36026 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -81,13 +81,13 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME BACK_LOG SESSION_VALUE NULL -GLOBAL_VALUE 150 -GLOBAL_VALUE_ORIGIN COMPILE-TIME +GLOBAL_VALUE 80 +GLOBAL_VALUE_ORIGIN AUTO DEFAULT_VALUE 150 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time -NUMERIC_MIN_VALUE 1 +VARIABLE_COMMENT 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 +NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 65535 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL @@ -1271,8 +1271,8 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME HOST_CACHE_SIZE SESSION_VALUE NULL -GLOBAL_VALUE 128 -GLOBAL_VALUE_ORIGIN COMPILE-TIME +GLOBAL_VALUE 279 +GLOBAL_VALUE_ORIGIN AUTO DEFAULT_VALUE 128 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED @@ -1410,10 +1410,10 @@ ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME JOIN_BUFFER_SIZE -SESSION_VALUE 131072 -GLOBAL_VALUE 131072 +SESSION_VALUE 262144 +GLOBAL_VALUE 262144 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 131072 +DEFAULT_VALUE 262144 VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The size of the buffer that is used for joins @@ -1473,9 +1473,9 @@ DEFAULT_VALUE 134217728 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The size of the buffer used for index blocks for MyISAM tables. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_BLOCK_SIZE 4096 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1487,9 +1487,9 @@ DEFAULT_VALUE 300 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT This characterizes the number of hits a hot block has to be untouched until it is considered aged enough to be downgraded to a warm block. This specifies the percentage ratio of that number of hits to the total number of blocks in key cache -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 100 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 100 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1501,9 +1501,9 @@ DEFAULT_VALUE 1024 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The default size of key cache blocks -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 512 +NUMERIC_MAX_VALUE 16384 +NUMERIC_BLOCK_SIZE 512 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1515,9 +1515,9 @@ DEFAULT_VALUE 100 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The minimum percentage of warm blocks in key cache -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 1 +NUMERIC_MAX_VALUE 100 +NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1529,9 +1529,9 @@ DEFAULT_VALUE 512 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Number of hash buckets for open and changed files. If you have a lot of MyISAM files open you should increase this for faster flush of changes. A good value is probably 1/10 of number of possible open MyISAM files. -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 128 +NUMERIC_MAX_VALUE 16384 +NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1543,9 +1543,9 @@ DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The number of segments in a key cache -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 64 +NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -1914,10 +1914,10 @@ ENUM_VALUE_LIST OFF,ON READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME MAX_ALLOWED_PACKET -SESSION_VALUE 1048576 -GLOBAL_VALUE 1048576 +SESSION_VALUE 4194304 +GLOBAL_VALUE 4194304 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 1048576 +DEFAULT_VALUE 4194304 VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Max packet length to send to or receive from the server @@ -2097,7 +2097,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME MAX_LONG_DATA_SIZE SESSION_VALUE NULL -GLOBAL_VALUE 1048576 +GLOBAL_VALUE 4194304 GLOBAL_VALUE_ORIGIN AUTO DEFAULT_VALUE 1048576 VARIABLE_SCOPE GLOBAL @@ -3287,9 +3287,9 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_SIZE SESSION_VALUE NULL -GLOBAL_VALUE 0 +GLOBAL_VALUE 1048576 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 0 +DEFAULT_VALUE 1048576 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The memory allocated to store results from old queries @@ -3314,10 +3314,10 @@ ENUM_VALUE_LIST OFF,ON READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME QUERY_CACHE_TYPE -SESSION_VALUE ON -GLOBAL_VALUE ON +SESSION_VALUE OFF +GLOBAL_VALUE OFF GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE ON +DEFAULT_VALUE OFF VARIABLE_SCOPE SESSION VARIABLE_TYPE ENUM VARIABLE_COMMENT OFF = Don't cache or retrieve results. ON = Cache all results except SELECT SQL_NO_CACHE ... queries. DEMAND = Cache only SELECT SQL_CACHE ... queries @@ -3693,9 +3693,9 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SECURE_AUTH SESSION_VALUE NULL -GLOBAL_VALUE OFF +GLOBAL_VALUE ON GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE OFF +DEFAULT_VALUE ON VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BOOLEAN VARIABLE_COMMENT Disallow authentication for accounts that have old (pre-4.1) passwords @@ -4169,9 +4169,9 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME SQL_MODE SESSION_VALUE ANSI_QUOTES -GLOBAL_VALUE +GLOBAL_VALUE NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE +DEFAULT_VALUE NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION VARIABLE_SCOPE SESSION VARIABLE_TYPE SET VARIABLE_COMMENT Sets the sql mode @@ -4435,12 +4435,12 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME SYNC_MASTER_INFO SESSION_VALUE NULL -GLOBAL_VALUE 0 +GLOBAL_VALUE 10000 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 0 +DEFAULT_VALUE 10000 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE INT UNSIGNED -VARIABLE_COMMENT Synchronously flush master info to disk after every #th event. Use 0 (default) to disable synchronous flushing +VARIABLE_COMMENT Synchronously flush master info to disk after every #th event. Use 0 to disable synchronous flushing NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 NUMERIC_BLOCK_SIZE 1 @@ -4449,12 +4449,12 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SYNC_RELAY_LOG SESSION_VALUE NULL -GLOBAL_VALUE 0 +GLOBAL_VALUE 10000 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 0 +DEFAULT_VALUE 10000 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE INT UNSIGNED -VARIABLE_COMMENT Synchronously flush relay log to disk after every #th event. Use 0 (default) to disable synchronous flushing +VARIABLE_COMMENT Synchronously flush relay log to disk after every #th event. Use 0 to disable synchronous flushing NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 NUMERIC_BLOCK_SIZE 1 @@ -4463,12 +4463,12 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SYNC_RELAY_LOG_INFO SESSION_VALUE NULL -GLOBAL_VALUE 0 +GLOBAL_VALUE 10000 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 0 +DEFAULT_VALUE 10000 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE INT UNSIGNED -VARIABLE_COMMENT Synchronously flush relay log info to disk after every #th transaction. Use 0 (default) to disable synchronous flushing +VARIABLE_COMMENT Synchronously flush relay log info to disk after every #th transaction. Use 0 to disable synchronous flushing NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 NUMERIC_BLOCK_SIZE 1 @@ -4478,7 +4478,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TABLE_DEFINITION_CACHE SESSION_VALUE NULL GLOBAL_VALUE 400 -GLOBAL_VALUE_ORIGIN COMPILE-TIME +GLOBAL_VALUE_ORIGIN AUTO DEFAULT_VALUE 400 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED @@ -4491,9 +4491,9 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TABLE_OPEN_CACHE SESSION_VALUE NULL -GLOBAL_VALUE 400 +GLOBAL_VALUE 2000 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 400 +DEFAULT_VALUE 2000 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The number of cached open tables diff --git a/mysql-test/suite/sys_vars/r/table_open_cache_basic.result b/mysql-test/suite/sys_vars/r/table_open_cache_basic.result index 2381dfe4900..d940ed69901 100644 --- a/mysql-test/suite/sys_vars/r/table_open_cache_basic.result +++ b/mysql-test/suite/sys_vars/r/table_open_cache_basic.result @@ -1,18 +1,18 @@ SET @start_value = @@global.table_open_cache ; SELECT @start_value; @start_value -400 +2000 '#--------------------FN_DYNVARS_001_01------------------------#' SET @@global.table_open_cache = 99; SET @@global.table_open_cache = DeFAULT; SELECT @@global.table_open_cache; @@global.table_open_cache -400 +2000 '#---------------------FN_DYNVARS_001_02-------------------------#' SET @@global.table_open_cache = Default; SELECT @@global.table_open_cache = 400; @@global.table_open_cache = 400 -1 +0 '#--------------------FN_DYNVARS_001_03------------------------#' SET @@global.table_open_cache = 8; SELECT @@global.table_open_cache ; @@ -104,4 +104,4 @@ ERROR 42S22: Unknown column 'table_open_cache' in 'field list' SET @@global.table_open_cache = @start_value; SELECT @@global.table_open_cache ; @@global.table_open_cache -400 +2000 diff --git a/mysql-test/suite/sys_vars/t/max_user_connections_func.test b/mysql-test/suite/sys_vars/t/max_user_connections_func.test index 74707c9b296..2af396b614f 100644 --- a/mysql-test/suite/sys_vars/t/max_user_connections_func.test +++ b/mysql-test/suite/sys_vars/t/max_user_connections_func.test @@ -39,6 +39,7 @@ SET @default_max_user_connections = @@global.max_user_connections; Set Global max_user_connections=2; +CREATE USER test@localhost; GRANT USAGE on *.* TO test@localhost; --echo '#--------------------FN_DYNVARS_114_01-------------------------#' diff --git a/mysql-test/suite/sys_vars/t/query_cache_limit_func.test b/mysql-test/suite/sys_vars/t/query_cache_limit_func.test index e90a0e6adda..c115524d49d 100644 --- a/mysql-test/suite/sys_vars/t/query_cache_limit_func.test +++ b/mysql-test/suite/sys_vars/t/query_cache_limit_func.test @@ -31,6 +31,9 @@ SET @global_query_cache_limit = @@global.query_cache_limit; SET @global_query_cache_size = @@global.query_cache_size; SET @global_query_cache_type = @@global.query_cache_type; +SET GLOBAL query_cache_type= ON; +SET LOCAL query_cache_type= ON; + --echo ** warnings ** --disable_warnings DROP TABLE IF EXISTS t; diff --git a/mysql-test/suite/sys_vars/t/query_cache_type_func.test b/mysql-test/suite/sys_vars/t/query_cache_type_func.test index 4679d1ec339..e015e374aeb 100644 --- a/mysql-test/suite/sys_vars/t/query_cache_type_func.test +++ b/mysql-test/suite/sys_vars/t/query_cache_type_func.test @@ -71,12 +71,15 @@ SET @@GLOBAL.query_cache_size = 65536; SET @@query_cache_type = DEFAULT; SELECT @@query_cache_type; ---echo ON Expected +--echo OFF Expected +--error ER_QUERY_CACHE_IS_GLOBALY_DISABLED +SET @@query_cache_type = ON; --echo '#--------------------FN_DYNVARS_135_02-------------------------#' # # Testing valid possible values # +SET GLOBAL query_cache_type= ON; --error ER_WRONG_VALUE_FOR_VAR SET @@query_cache_type = NULL; diff --git a/mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test b/mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test index e126a67ff30..5c7533eb50a 100644 --- a/mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test +++ b/mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test @@ -49,6 +49,11 @@ SET @start_session_value = @@SESSION.query_cache_wlock_invalidate; SET @old_cache_size = @@GLOBAL.query_cache_size; SET @old_cache_type = @@GLOBAL.query_cache_type; +SET GLOBAL query_cache_type= ON; +SET LOCAL query_cache_type= ON; +connection con1; +SET LOCAL query_cache_type= ON; +connection con0; # # Creating test table |