summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorunknown <knielsen@knielsen-hq.org>2010-01-11 14:15:28 +0100
committerunknown <knielsen@knielsen-hq.org>2010-01-11 14:15:28 +0100
commit0855019e3ae0c8d1d20c49a8be317050c093b4dc (patch)
treec60b27ef063564b256a70addfd51e6f1ca877fac
parent95c4b56a0a23e3dafee3da29a99dae12a8aa40c1 (diff)
downloadmariadb-git-0855019e3ae0c8d1d20c49a8be317050c093b4dc.tar.gz
Fix multiple test suite failures in Buildbot due to races in the test cases or missing server features not properly checked
mysql-test/r/func_misc.result: Move test that requires query cache from main.func_misc to main.query_cache. mysql-test/r/mysqltest.result: Fix test failure due to race. This test case creates > 300 connections in a tight loop, and depending on thread scheduling and load, even though each connection is immediately disconnected before connecting the next one, the server max connections may still be exceeded due to server not being able to free old connections as fast as new ones are made. mysql-test/r/query_cache.result: Move test that requires query cache from main.func_misc to main.query_cache. Move test that requires query cache from main.variables to main.query_cache. mysql-test/r/query_cache_notembedded.result: Move test that requires query cache from main.sp_notembedded to main.query_cache_notembedded. mysql-test/r/sp_notembedded.result: Move test that requires query cache from main.sp_notembedded to main.query_cache_notembedded. mysql-test/r/udf.result: Move test in main.udf that requires query cache to separate file. mysql-test/r/udf_query_cache.result: Move test in main.udf that requires query cache to separate file. mysql-test/r/variables.result: Move test that requires query cache from main.variables to main.query_cache. mysql-test/suite/funcs_1/datadict/processlist_val.inc: Fix race where result file may show state "cleaning up" in the small window between setting COMMAND to 'Sleep' and clearing STATE. mysql-test/suite/rpl/r/rpl_temporary.result: Fix race with suppression of warning message by fixing the test to not generate the warning message in the first place. Problem was a race between creating an anonymous account and resetting the slave. If the slave reset happens before replicating the account, the subsequest deletion of the account will fail to replicate correctly due to missing row. mysql-test/suite/rpl/t/rpl_temporary.test: Fix race with suppression of warning message by fixing the test to not generate the warning message in the first place. Problem was a race between creating an anonymous account and resetting the slave. If the slave reset happens before replicating the account, the subsequest deletion of the account will fail to replicate correctly due to missing row. mysql-test/t/func_misc.test: Move test that requires query cache from main.func_misc to main.query_cache. Move test that requires query cache from main.variables to main.query_cache. mysql-test/t/mysqltest.test: Fix test failure due to race. This test case creates > 300 connections in a tight loop, and depending on thread scheduling and load, even though each connection is immediately disconnected before connecting the next one, the server max connections may still be exceeded due to server not being able to free old connections as fast as new ones are made. mysql-test/t/query_cache.test: Move test that requires query cache to main.query_cache. mysql-test/t/query_cache_notembedded.test: Move test that requires query cache from main.sp_notembedded to main.query_cache_notembedded. mysql-test/t/sp_notembedded.test: Move test that requires query cache from main.sp_notembedded to main.query_cache_notembedded. mysql-test/t/udf.test: Move test in main.udf that requires query cache to separate file. mysql-test/t/udf_query_cache-master.opt: Move test in main.udf that requires query cache to separate file. mysql-test/t/udf_query_cache.test: Move test in main.udf that requires query cache to separate file. mysql-test/t/variables.test: Move test that requires query cache from main.variables to main.query_cache. tests/mysql_client_test.c: In tests that require query cache, skip the test if query cache not available. Do this dynamically rather than using HAVE_QUERY_CACHE, as there is no guarantee that the server we run against was compiled with same preprocessor #define as the mysql_client_test program (and since it is trivial to check dynamically).
-rw-r--r--mysql-test/r/func_misc.result89
-rw-r--r--mysql-test/r/mysqltest.result1
-rw-r--r--mysql-test/r/query_cache.result98
-rw-r--r--mysql-test/r/query_cache_notembedded.result52
-rw-r--r--mysql-test/r/sp_notembedded.result52
-rw-r--r--mysql-test/r/udf.result23
-rw-r--r--mysql-test/r/udf_query_cache.result24
-rw-r--r--mysql-test/r/variables.result10
-rw-r--r--mysql-test/suite/funcs_1/datadict/processlist_val.inc2
-rw-r--r--mysql-test/suite/rpl/r/rpl_temporary.result5
-rw-r--r--mysql-test/suite/rpl/t/rpl_temporary.test12
-rw-r--r--mysql-test/t/func_misc.test192
-rw-r--r--mysql-test/t/mysqltest.test12
-rw-r--r--mysql-test/t/query_cache.test205
-rw-r--r--mysql-test/t/query_cache_notembedded.test47
-rw-r--r--mysql-test/t/sp_notembedded.test46
-rw-r--r--mysql-test/t/udf.test23
-rw-r--r--mysql-test/t/udf_query_cache-master.opt1
-rw-r--r--mysql-test/t/udf_query_cache.test35
-rw-r--r--mysql-test/t/variables.test10
-rw-r--r--tests/mysql_client_test.c46
21 files changed, 527 insertions, 458 deletions
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index d939d72f7ef..828d4bafc53 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -104,95 +104,6 @@ t1 CREATE TABLE `t1` (
`length(uuid())` int(10) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
-#------------------------------------------------------------------------
-# Tests for Bug#6760 and Bug#12689
-SET @row_count = 4;
-SET @sleep_time_per_result_row = 1;
-SET @max_acceptable_delay = 2;
-SET @@global.query_cache_size = 1024 * 64;
-DROP TEMPORARY TABLE IF EXISTS t_history;
-DROP TABLE IF EXISTS t1;
-CREATE TEMPORARY TABLE t_history (attempt SMALLINT,
-start_ts DATETIME, end_ts DATETIME,
-start_cached INTEGER, end_cached INTEGER);
-CREATE TABLE t1 (f1 BIGINT);
-INSERT INTO t_history
-SET attempt = 4 - 4 + 1, start_ts = NOW(),
-start_cached = 0;
-SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
-f1 SLEEP(@sleep_time_per_result_row)
-1 0
-1 0
-1 0
-1 0
-UPDATE t_history SET end_ts = NOW()
-WHERE attempt = 4 - 4 + 1;
-UPDATE t_history SET end_cached = 0
-WHERE attempt = 4 - 4 + 1;
-INSERT INTO t_history
-SET attempt = 4 - 3 + 1, start_ts = NOW(),
-start_cached = 0;
-SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
-f1 SLEEP(@sleep_time_per_result_row)
-1 0
-1 0
-1 0
-1 0
-UPDATE t_history SET end_ts = NOW()
-WHERE attempt = 4 - 3 + 1;
-UPDATE t_history SET end_cached = 0
-WHERE attempt = 4 - 3 + 1;
-INSERT INTO t_history
-SET attempt = 4 - 2 + 1, start_ts = NOW(),
-start_cached = 0;
-SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
-f1 SLEEP(@sleep_time_per_result_row)
-1 0
-1 0
-1 0
-1 0
-UPDATE t_history SET end_ts = NOW()
-WHERE attempt = 4 - 2 + 1;
-UPDATE t_history SET end_cached = 0
-WHERE attempt = 4 - 2 + 1;
-INSERT INTO t_history
-SET attempt = 4 - 1 + 1, start_ts = NOW(),
-start_cached = 0;
-SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
-f1 SLEEP(@sleep_time_per_result_row)
-1 0
-1 0
-1 0
-1 0
-UPDATE t_history SET end_ts = NOW()
-WHERE attempt = 4 - 1 + 1;
-UPDATE t_history SET end_cached = 0
-WHERE attempt = 4 - 1 + 1;
-# Test 1: Does the query with SLEEP need a reasonable time?
-SELECT COUNT(*) >= 4 - 1 INTO @aux1 FROM t_history
-WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
-BETWEEN 0 AND @max_acceptable_delay;
-SELECT @aux1 AS "Expect 1";
-Expect 1
-1
-# Test 2: Does the query with SLEEP need a reasonable time even in case
-# of the non first execution?
-SELECT COUNT(*) >= 4 - 1 - 1 INTO @aux2 FROM t_history
-WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
-BETWEEN 0 AND @max_acceptable_delay
-AND attempt > 1;
-SELECT @aux2 AS "Expect 1";
-Expect 1
-1
-# Test 3: The query with SLEEP must be not cached.
-SELECT COUNT(*) = 4 INTO @aux3 FROM t_history
-WHERE end_cached = start_cached;
-SELECT @aux3 AS "Expect 1";
-Expect 1
-1
-DROP TABLE t1;
-DROP TEMPORARY TABLE t_history;
-SET @@global.query_cache_size = default;
create table t1 select INET_ATON('255.255.0.1') as `a`;
show create table t1;
Table Create Table
diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result
index 2e3a9489593..7d903a17d2e 100644
--- a/mysql-test/r/mysqltest.result
+++ b/mysql-test/r/mysqltest.result
@@ -1,3 +1,4 @@
+SET GLOBAL max_connections = 1000;
select 0 as "before_use_test" ;
before_use_test
0
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index a410f7b2dcf..2380ecc5969 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -1302,6 +1302,15 @@ drop procedure f3;
drop procedure f4;
drop table t1;
set GLOBAL query_cache_size=0;
+set GLOBAL query_cache_size=100000;
+set SESSION query_cache_size=10000;
+ERROR HY000: Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
+set global query_cache_limit=100;
+set global query_cache_size=100;
+set global query_cache_type=demand;
+set GLOBAL query_cache_type=default;
+set GLOBAL query_cache_limit=default;
+set GLOBAL query_cache_size=default;
End of 4.1 tests
SET GLOBAL query_cache_size=102400;
create table t1(a int);
@@ -1707,6 +1716,95 @@ Variable_name Value
Qcache_hits 2
DROP TABLE t1;
SET GLOBAL query_cache_size= default;
+#------------------------------------------------------------------------
+# Tests for Bug#6760 and Bug#12689
+SET @row_count = 4;
+SET @sleep_time_per_result_row = 1;
+SET @max_acceptable_delay = 2;
+SET @@global.query_cache_size = 1024 * 64;
+DROP TEMPORARY TABLE IF EXISTS t_history;
+DROP TABLE IF EXISTS t1;
+CREATE TEMPORARY TABLE t_history (attempt SMALLINT,
+start_ts DATETIME, end_ts DATETIME,
+start_cached INTEGER, end_cached INTEGER);
+CREATE TABLE t1 (f1 BIGINT);
+INSERT INTO t_history
+SET attempt = 4 - 4 + 1, start_ts = NOW(),
+start_cached = 0;
+SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
+f1 SLEEP(@sleep_time_per_result_row)
+1 0
+1 0
+1 0
+1 0
+UPDATE t_history SET end_ts = NOW()
+WHERE attempt = 4 - 4 + 1;
+UPDATE t_history SET end_cached = 0
+WHERE attempt = 4 - 4 + 1;
+INSERT INTO t_history
+SET attempt = 4 - 3 + 1, start_ts = NOW(),
+start_cached = 0;
+SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
+f1 SLEEP(@sleep_time_per_result_row)
+1 0
+1 0
+1 0
+1 0
+UPDATE t_history SET end_ts = NOW()
+WHERE attempt = 4 - 3 + 1;
+UPDATE t_history SET end_cached = 0
+WHERE attempt = 4 - 3 + 1;
+INSERT INTO t_history
+SET attempt = 4 - 2 + 1, start_ts = NOW(),
+start_cached = 0;
+SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
+f1 SLEEP(@sleep_time_per_result_row)
+1 0
+1 0
+1 0
+1 0
+UPDATE t_history SET end_ts = NOW()
+WHERE attempt = 4 - 2 + 1;
+UPDATE t_history SET end_cached = 0
+WHERE attempt = 4 - 2 + 1;
+INSERT INTO t_history
+SET attempt = 4 - 1 + 1, start_ts = NOW(),
+start_cached = 0;
+SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
+f1 SLEEP(@sleep_time_per_result_row)
+1 0
+1 0
+1 0
+1 0
+UPDATE t_history SET end_ts = NOW()
+WHERE attempt = 4 - 1 + 1;
+UPDATE t_history SET end_cached = 0
+WHERE attempt = 4 - 1 + 1;
+# Test 1: Does the query with SLEEP need a reasonable time?
+SELECT COUNT(*) >= 4 - 1 INTO @aux1 FROM t_history
+WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
+BETWEEN 0 AND @max_acceptable_delay;
+SELECT @aux1 AS "Expect 1";
+Expect 1
+1
+# Test 2: Does the query with SLEEP need a reasonable time even in case
+# of the non first execution?
+SELECT COUNT(*) >= 4 - 1 - 1 INTO @aux2 FROM t_history
+WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
+BETWEEN 0 AND @max_acceptable_delay
+AND attempt > 1;
+SELECT @aux2 AS "Expect 1";
+Expect 1
+1
+# Test 3: The query with SLEEP must be not cached.
+SELECT COUNT(*) = 4 INTO @aux3 FROM t_history
+WHERE end_cached = start_cached;
+SELECT @aux3 AS "Expect 1";
+Expect 1
+1
+DROP TABLE t1;
+DROP TEMPORARY TABLE t_history;
+SET @@global.query_cache_size = default;
End of 5.0 tests
SET GLOBAL query_cache_size=1024*1024*512;
CREATE TABLE t1 (a ENUM('rainbow'));
diff --git a/mysql-test/r/query_cache_notembedded.result b/mysql-test/r/query_cache_notembedded.result
index d9bf7a6d814..2dfa7d7aa38 100644
--- a/mysql-test/r/query_cache_notembedded.result
+++ b/mysql-test/r/query_cache_notembedded.result
@@ -382,3 +382,55 @@ set GLOBAL query_cache_type=default;
set GLOBAL query_cache_limit=default;
set GLOBAL query_cache_min_res_unit=default;
set GLOBAL query_cache_size=default;
+drop table if exists t1|
+create table t1 (
+id char(16) not null default '',
+data int not null
+)|
+drop procedure if exists bug3583|
+drop procedure if exists bug3583|
+create procedure bug3583()
+begin
+declare c int;
+select * from t1;
+select count(*) into c from t1;
+select c;
+end|
+insert into t1 values ("x", 3), ("y", 5)|
+set @x = @@query_cache_size|
+set global query_cache_size = 10*1024*1024|
+flush status|
+flush query cache|
+show status like 'Qcache_hits'|
+Variable_name Value
+Qcache_hits 0
+call bug3583()|
+id data
+x 3
+y 5
+c
+2
+show status like 'Qcache_hits'|
+Variable_name Value
+Qcache_hits 0
+call bug3583()|
+id data
+x 3
+y 5
+c
+2
+call bug3583()|
+id data
+x 3
+y 5
+c
+2
+show status like 'Qcache_hits'|
+Variable_name Value
+Qcache_hits 2
+set global query_cache_size = @x|
+flush status|
+flush query cache|
+delete from t1|
+drop procedure bug3583|
+drop table t1|
diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result
index 831616f491b..af73d637590 100644
--- a/mysql-test/r/sp_notembedded.result
+++ b/mysql-test/r/sp_notembedded.result
@@ -25,58 +25,6 @@ call bug4902_2()|
show warnings|
Level Code Message
drop procedure bug4902_2|
-drop table if exists t1|
-create table t1 (
-id char(16) not null default '',
-data int not null
-)|
-drop procedure if exists bug3583|
-drop procedure if exists bug3583|
-create procedure bug3583()
-begin
-declare c int;
-select * from t1;
-select count(*) into c from t1;
-select c;
-end|
-insert into t1 values ("x", 3), ("y", 5)|
-set @x = @@query_cache_size|
-set global query_cache_size = 10*1024*1024|
-flush status|
-flush query cache|
-show status like 'Qcache_hits'|
-Variable_name Value
-Qcache_hits 0
-call bug3583()|
-id data
-x 3
-y 5
-c
-2
-show status like 'Qcache_hits'|
-Variable_name Value
-Qcache_hits 0
-call bug3583()|
-id data
-x 3
-y 5
-c
-2
-call bug3583()|
-id data
-x 3
-y 5
-c
-2
-show status like 'Qcache_hits'|
-Variable_name Value
-Qcache_hits 2
-set global query_cache_size = @x|
-flush status|
-flush query cache|
-delete from t1|
-drop procedure bug3583|
-drop table t1|
drop procedure if exists bug6807|
create procedure bug6807()
begin
diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index 601b364fbbe..393a2f54b05 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -311,29 +311,6 @@ drop function f3;
drop function metaphon;
drop function myfunc_double;
drop function myfunc_int;
-CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
-create table t1 (a char);
-set GLOBAL query_cache_size=1355776;
-reset query cache;
-select metaphon('MySQL') from t1;
-metaphon('MySQL')
-show status like "Qcache_hits";
-Variable_name Value
-Qcache_hits 0
-show status like "Qcache_queries_in_cache";
-Variable_name Value
-Qcache_queries_in_cache 0
-select metaphon('MySQL') from t1;
-metaphon('MySQL')
-show status like "Qcache_hits";
-Variable_name Value
-Qcache_hits 0
-show status like "Qcache_queries_in_cache";
-Variable_name Value
-Qcache_queries_in_cache 0
-drop table t1;
-drop function metaphon;
-set GLOBAL query_cache_size=default;
DROP DATABASE IF EXISTS mysqltest;
CREATE DATABASE mysqltest;
USE mysqltest;
diff --git a/mysql-test/r/udf_query_cache.result b/mysql-test/r/udf_query_cache.result
new file mode 100644
index 00000000000..01dac554142
--- /dev/null
+++ b/mysql-test/r/udf_query_cache.result
@@ -0,0 +1,24 @@
+drop table if exists t1;
+CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
+create table t1 (a char);
+set GLOBAL query_cache_size=1355776;
+reset query cache;
+select metaphon('MySQL') from t1;
+metaphon('MySQL')
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 0
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 0
+select metaphon('MySQL') from t1;
+metaphon('MySQL')
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 0
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 0
+drop table t1;
+drop function metaphon;
+set GLOBAL query_cache_size=default;
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index 4582ccddd81..49c19e9b455 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -19,8 +19,6 @@ set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size;
set @my_net_buffer_length =@@global.net_buffer_length;
set @my_net_write_timeout =@@global.net_write_timeout;
set @my_net_read_timeout =@@global.net_read_timeout;
-set @my_query_cache_limit =@@global.query_cache_limit;
-set @my_query_cache_type =@@global.query_cache_type;
set @my_rpl_recovery_rank =@@global.rpl_recovery_rank;
set @my_server_id =@@global.server_id;
set @my_slow_launch_time =@@global.slow_launch_time;
@@ -215,7 +213,6 @@ storage_engine MRG_MYISAM
select * from information_schema.global_variables where variable_name like 'storage_engine';
VARIABLE_NAME VARIABLE_VALUE
STORAGE_ENGINE MRG_MYISAM
-set GLOBAL query_cache_size=100000;
set GLOBAL myisam_max_sort_file_size=2000000;
show global variables like 'myisam_max_sort_file_size';
Variable_name Value
@@ -423,8 +420,6 @@ ERROR 42000: Variable 'big_tables' can't be set to the value of '2'
show local variables like 'storage_engine';
Variable_name Value
storage_engine MEMORY
-set SESSION query_cache_size=10000;
-ERROR HY000: Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
set GLOBAL storage_engine=DEFAULT;
ERROR 42000: Variable 'storage_engine' doesn't have a default value
set character_set_client=UNKNOWN_CHARACTER_SET;
@@ -529,9 +524,6 @@ Warnings:
Warning 1292 Truncated incorrect net_buffer_length value: '100'
set net_read_timeout=100;
set net_write_timeout=100;
-set global query_cache_limit=100;
-set global query_cache_size=100;
-set global query_cache_type=demand;
set read_buffer_size=100;
Warnings:
Warning 1292 Truncated incorrect read_buffer_size value: '100'
@@ -1047,8 +1039,6 @@ set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size;
set global net_buffer_length =@my_net_buffer_length;
set global net_write_timeout =@my_net_write_timeout;
set global net_read_timeout =@my_net_read_timeout;
-set global query_cache_limit =@my_query_cache_limit;
-set global query_cache_type =@my_query_cache_type;
set global rpl_recovery_rank =@my_rpl_recovery_rank;
set global server_id =@my_server_id;
set global slow_launch_time =@my_slow_launch_time;
diff --git a/mysql-test/suite/funcs_1/datadict/processlist_val.inc b/mysql-test/suite/funcs_1/datadict/processlist_val.inc
index 9936b7c580b..a2ab02c963f 100644
--- a/mysql-test/suite/funcs_1/datadict/processlist_val.inc
+++ b/mysql-test/suite/funcs_1/datadict/processlist_val.inc
@@ -238,7 +238,7 @@ echo
# Poll till all connections of 'test_user' are in a state with COMMAND = 'Sleep'
;
let $wait_condition= SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST
- WHERE USER = 'test_user' AND COMMAND = 'Sleep';
+ WHERE USER = 'test_user' AND COMMAND = 'Sleep' AND STATE = '';
--source include/wait_condition.inc
echo
# ----- switch to connection con2 (user = test_user) -----
diff --git a/mysql-test/suite/rpl/r/rpl_temporary.result b/mysql-test/suite/rpl/r/rpl_temporary.result
index 631eb0677b0..79c5abbd478 100644
--- a/mysql-test/suite/rpl/r/rpl_temporary.result
+++ b/mysql-test/suite/rpl/r/rpl_temporary.result
@@ -4,7 +4,8 @@ reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
-call mtr.add_suppression("Slave: Can\'t find record in \'user\' Error_code: 1032");
+SET sql_log_bin = 0;
+SET sql_log_bin = 1;
reset master;
DROP TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 (a char(1));
@@ -127,6 +128,8 @@ select * from t1;
a
1
drop table t1;
+SET sql_log_bin = 0;
+SET sql_log_bin = 1;
-- Bug#43748
-- make a user on the slave that can list but not kill system threads.
FLUSH PRIVILEGES;
diff --git a/mysql-test/suite/rpl/t/rpl_temporary.test b/mysql-test/suite/rpl/t/rpl_temporary.test
index a59e4f2fd21..56d8ab97c3f 100644
--- a/mysql-test/suite/rpl/t/rpl_temporary.test
+++ b/mysql-test/suite/rpl/t/rpl_temporary.test
@@ -1,7 +1,10 @@
+-- source include/master-slave.inc
+
# Test need anonymous user when connection are made as "zedjzlcsjhd"
+# But we only need it on the master, not the slave.
+SET sql_log_bin = 0;
source include/add_anonymous_users.inc;
-
--- source include/master-slave.inc
+SET sql_log_bin = 1;
# Clean up old slave's binlogs.
# The slave is started with --log-slave-updates
@@ -17,9 +20,6 @@ source include/add_anonymous_users.inc;
save_master_pos;
connection slave;
-# Add suppression for expected warning(s) in slaves error log
-call mtr.add_suppression("Slave: Can\'t find record in \'user\' Error_code: 1032");
-
sync_with_master;
reset master;
@@ -291,7 +291,9 @@ drop table t1;
--remove_file $MYSQLTEST_VARDIR/tmp/bug14157.sql
# Delete the anonymous users
+SET sql_log_bin = 0;
source include/delete_anonymous_users.inc;
+SET sql_log_bin = 1;
diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test
index 98a5ad8d80c..afce01cac5c 100644
--- a/mysql-test/t/func_misc.test
+++ b/mysql-test/t/func_misc.test
@@ -103,198 +103,6 @@ show create table t1;
drop table t1;
#
-# Bug#6760: Add SLEEP() function (feature request)
-#
-# Logics of original test:
-# Reveal that a query with SLEEP does not need less time than estimated.
-#
-# Bug#12689: SLEEP() gets incorrectly cached/optimized-away
-#
-# Description from bug report (slightly modified)
-#
-# Bug 1 (happened all time):
-# SELECT * FROM t1 WHERE SLEEP(1) will only result in a sleep of 1
-# second, regardless of the number of rows in t1.
-# Bug 2 (happened all time):
-# Such a query will also get cached by the query cache, but should not.
-#
-# Notes (mleich, 2008-05)
-# =======================
-#
-# Experiments around
-# Bug#36345 Test 'func_misc' fails on RHAS3 x86_64
-# showed that the tests for both bugs could produce in case of parallel
-# artificial system time (like via ntpd)
-# - decreases false alarm
-# - increases false success
-#
-# We try here to circumvent these issues by reimplementation of the tests
-# and sophisticated scripting, although the cause of the problems is a massive
-# error within the setup of the testing environment.
-# Tests relying on or checking derivates of the system time must never meet
-# parallel manipulations of system time.
-#
-# Results of experiments with/without manipulation of system time,
-# information_schema.processlist content, high load on testing box
-# ----------------------------------------------------------------
-# Definition: Predicted_cumulative_sleep_time =
-# #_of_result_rows * sleep_time_per_result_row
-#
-# 1. Total (real sleep time) ~= predicted_cumulative_sleep_time !!
-# 2. The state of a session within the PROCESSLIST changes to 'User sleep'
-# if the sessions runs a statement containing the sleep function and the
-# processing of the statement is just within the phase where the sleep
-# is done. (*)
-# 3. NOW() and processlist.time behave "synchronous" to system time and
-# show also the "jumps" caused by system time manipulations. (*)
-# 4. processlist.time is unsigned, the "next" value below 0 is ~ 4G (*)
-# 5. Current processlist.time ~= current real sleep time if the system time
-# was not manipulated. (*)
-# 6. High system load can cause delays of <= 2 seconds.
-# 7. Thanks to Davi for excellent hints and ideas.
-#
-# (*)
-# - information_schema.processlist is not available before MySQL 5.1.
-# - Observation of processlist content requires a
-# - "worker" session sending the query with "send" and pulling results
-# with "reap"
-# - session observing the processlist parallel to the worker session
-# "send" and "reap" do not work in case of an embedded server.
-# Conclusion: Tests based on processlist have too many restrictions.
-#
-# Solutions for subtests based on TIMEDIFF of values filled via NOW()
-# -------------------------------------------------------------------
-# Run the following sequence three times
-# 1. SELECT <start_time>
-# 2. Query with SLEEP
-# 3. SELECT <end_time>
-# If TIMEDIFF(<end_time>,<start_time>) is at least two times within a
-# reasonable range assume that we did not met errors we were looking for.
-#
-# It is extreme unlikely that we have two system time changes within the
-# < 30 seconds runtime. Even if the unlikely happens, there are so
-# frequent runs of this test on this or another testing box which will
-# catch the problem.
-#
-
---echo #------------------------------------------------------------------------
---echo # Tests for Bug#6760 and Bug#12689
-# Number of rows within the intended result set.
-SET @row_count = 4;
-# Parameter within SLEEP function
-SET @sleep_time_per_result_row = 1;
-# Maximum acceptable delay caused by high load on testing box
-SET @max_acceptable_delay = 2;
-# TIMEDIFF = time for query with sleep (mostly the time caused by SLEEP)
-# + time for delays caused by high load on testing box
-# Ensure that at least a reasonable fraction of TIMEDIFF belongs to the SLEEP
-# by appropriate setting of variables.
-# Ensure that any "judging" has a base of minimum three attempts.
-# (Test 2 uses all attempts except the first one.)
-if (!` SELECT (@sleep_time_per_result_row * @row_count - @max_acceptable_delay >
- @sleep_time_per_result_row) AND (@row_count - 1 >= 3)`)
-{
- --echo # Have to abort because of error in plausibility check
- --echo ######################################################
- --vertical_results
- SELECT @sleep_time_per_result_row * @row_count - @max_acceptable_delay >
- @sleep_time_per_result_row AS must_be_1,
- @row_count - 1 >= 3 AS must_be_also_1,
- @sleep_time_per_result_row, @row_count, @max_acceptable_delay;
- exit;
-}
-SET @@global.query_cache_size = 1024 * 64;
---disable_warnings
-DROP TEMPORARY TABLE IF EXISTS t_history;
-DROP TABLE IF EXISTS t1;
---enable_warnings
-CREATE TEMPORARY TABLE t_history (attempt SMALLINT,
-start_ts DATETIME, end_ts DATETIME,
-start_cached INTEGER, end_cached INTEGER);
-CREATE TABLE t1 (f1 BIGINT);
-let $num = `SELECT @row_count`;
---disable_query_log
-begin;
-while ($num)
-{
- INSERT INTO t1 VALUES (1);
- dec $num;
-}
-commit;
---enable_query_log
-
-let $loops = 4;
-let $num = $loops;
-while ($num)
-{
- let $Qcache_queries_in_cache =
- query_get_value(SHOW STATUS LIKE 'Qcache_queries_in_cache', Value, 1);
- eval
- INSERT INTO t_history
- SET attempt = $loops - $num + 1, start_ts = NOW(),
- start_cached = $Qcache_queries_in_cache;
- SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
- #
- # Do not determine Qcache_queries_in_cache before updating end_ts. The SHOW
- # might cost too much time on an overloaded box.
- eval
- UPDATE t_history SET end_ts = NOW()
- WHERE attempt = $loops - $num + 1;
- let $Qcache_queries_in_cache =
- query_get_value(SHOW STATUS LIKE 'Qcache_queries_in_cache', Value, 1);
- eval
- UPDATE t_history SET end_cached = $Qcache_queries_in_cache
- WHERE attempt = $loops - $num + 1;
- # DEBUG eval SELECT * FROM t_history WHERE attempt = $loops - $num + 1;
- dec $num;
-}
-
-# 1. The majority of queries with SLEEP must need a reasonable time
-# -> SLEEP has an impact on runtime
-# = Replacement for original Bug#6760 test
-# -> total runtime is clear more needed than for one result row needed
-# = Replacement for one of the original Bug#12689 tests
---echo # Test 1: Does the query with SLEEP need a reasonable time?
-eval SELECT COUNT(*) >= $loops - 1 INTO @aux1 FROM t_history
-WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
- BETWEEN 0 AND @max_acceptable_delay;
-SELECT @aux1 AS "Expect 1";
-#
-# 2. The majority of queries (the first one must be ignored) with SLEEP must
-# need a reasonable time
-# -> If we assume that the result of a cached query will be sent back
-# immediate, without any sleep, than the query with SLEEP cannot be cached
-# (current and intended behaviour for queries with SLEEP).
-# -> It could be also not excluded that the query was cached but the server
-# honoured somehow the SLEEP. Such a behaviour would be also acceptable.
-# = Replacement for one of the original Bug#12689 tests
---echo # Test 2: Does the query with SLEEP need a reasonable time even in case
---echo # of the non first execution?
-eval SELECT COUNT(*) >= $loops - 1 - 1 INTO @aux2 FROM t_history
-WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
- BETWEEN 0 AND @max_acceptable_delay
- AND attempt > 1;
-SELECT @aux2 AS "Expect 1";
-#
-# 3. The query with SLEEP should be not cached.
-# -> SHOW STATUS Qcache_queries_in_cache must be not incremented after
-# the execution of the query with SLEEP
---echo # Test 3: The query with SLEEP must be not cached.
-eval SELECT COUNT(*) = $loops INTO @aux3 FROM t_history
-WHERE end_cached = start_cached;
-SELECT @aux3 AS "Expect 1";
-#
-# Dump the content of t_history if one of the tests failed.
-if (`SELECT @aux1 + @aux2 + @aux3 <> 3`)
-{
- --echo # Some tests failed, dumping the content of t_history
- SELECT * FROM t_history;
-}
-DROP TABLE t1;
-DROP TEMPORARY TABLE t_history;
-SET @@global.query_cache_size = default;
-
-#
# Bug #21466: INET_ATON() returns signed, not unsigned
#
diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test
index bcf33aa8c27..c86ebda3a24 100644
--- a/mysql-test/t/mysqltest.test
+++ b/mysql-test/t/mysqltest.test
@@ -9,6 +9,14 @@
# Save the initial number of concurrent sessions
--source include/count_sessions.inc
+# Some tests below connect/disconnect rapidly in a loop. This causes a race
+# where mysqld may not have time to register the previous disconnects before
+# new connects, and eventually we run out of connections. So we need to
+# increase the maximum.
+let $saved_max_connections = `SELECT @@global.max_connections`;
+SET GLOBAL max_connections = 1000;
+
+
# ============================================================================
#
# Test of mysqltest itself
@@ -2319,3 +2327,7 @@ disconnect $y;
connection default;
# Wait till we reached the initial number of concurrent sessions
--source include/wait_until_count_sessions.inc
+
+--disable_query_log
+--eval SET GLOBAL max_connections = $saved_max_connections
+--enable_query_log
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 3d9045d3aa6..de8467b509d 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -882,6 +882,19 @@ drop procedure f4;
drop table t1;
set GLOBAL query_cache_size=0;
+# Tests moved from main.variables due to needing query cache in server.
+set GLOBAL query_cache_size=100000;
+--error ER_GLOBAL_VARIABLE
+set SESSION query_cache_size=10000;
+set global query_cache_limit=100;
+set global query_cache_size=100;
+set global query_cache_type=demand;
+
+set GLOBAL query_cache_type=default;
+set GLOBAL query_cache_limit=default;
+set GLOBAL query_cache_size=default;
+
+
--echo End of 4.1 tests
#
@@ -1288,6 +1301,198 @@ SHOW STATUS LIKE "Qcache_hits";
DROP TABLE t1;
SET GLOBAL query_cache_size= default;
+#
+# Bug#6760: Add SLEEP() function (feature request)
+#
+# Logics of original test:
+# Reveal that a query with SLEEP does not need less time than estimated.
+#
+# Bug#12689: SLEEP() gets incorrectly cached/optimized-away
+#
+# Description from bug report (slightly modified)
+#
+# Bug 1 (happened all time):
+# SELECT * FROM t1 WHERE SLEEP(1) will only result in a sleep of 1
+# second, regardless of the number of rows in t1.
+# Bug 2 (happened all time):
+# Such a query will also get cached by the query cache, but should not.
+#
+# Notes (mleich, 2008-05)
+# =======================
+#
+# Experiments around
+# Bug#36345 Test 'func_misc' fails on RHAS3 x86_64
+# showed that the tests for both bugs could produce in case of parallel
+# artificial system time (like via ntpd)
+# - decreases false alarm
+# - increases false success
+#
+# We try here to circumvent these issues by reimplementation of the tests
+# and sophisticated scripting, although the cause of the problems is a massive
+# error within the setup of the testing environment.
+# Tests relying on or checking derivates of the system time must never meet
+# parallel manipulations of system time.
+#
+# Results of experiments with/without manipulation of system time,
+# information_schema.processlist content, high load on testing box
+# ----------------------------------------------------------------
+# Definition: Predicted_cumulative_sleep_time =
+# #_of_result_rows * sleep_time_per_result_row
+#
+# 1. Total (real sleep time) ~= predicted_cumulative_sleep_time !!
+# 2. The state of a session within the PROCESSLIST changes to 'User sleep'
+# if the sessions runs a statement containing the sleep function and the
+# processing of the statement is just within the phase where the sleep
+# is done. (*)
+# 3. NOW() and processlist.time behave "synchronous" to system time and
+# show also the "jumps" caused by system time manipulations. (*)
+# 4. processlist.time is unsigned, the "next" value below 0 is ~ 4G (*)
+# 5. Current processlist.time ~= current real sleep time if the system time
+# was not manipulated. (*)
+# 6. High system load can cause delays of <= 2 seconds.
+# 7. Thanks to Davi for excellent hints and ideas.
+#
+# (*)
+# - information_schema.processlist is not available before MySQL 5.1.
+# - Observation of processlist content requires a
+# - "worker" session sending the query with "send" and pulling results
+# with "reap"
+# - session observing the processlist parallel to the worker session
+# "send" and "reap" do not work in case of an embedded server.
+# Conclusion: Tests based on processlist have too many restrictions.
+#
+# Solutions for subtests based on TIMEDIFF of values filled via NOW()
+# -------------------------------------------------------------------
+# Run the following sequence three times
+# 1. SELECT <start_time>
+# 2. Query with SLEEP
+# 3. SELECT <end_time>
+# If TIMEDIFF(<end_time>,<start_time>) is at least two times within a
+# reasonable range assume that we did not met errors we were looking for.
+#
+# It is extreme unlikely that we have two system time changes within the
+# < 30 seconds runtime. Even if the unlikely happens, there are so
+# frequent runs of this test on this or another testing box which will
+# catch the problem.
+#
+
+--echo #------------------------------------------------------------------------
+--echo # Tests for Bug#6760 and Bug#12689
+# Number of rows within the intended result set.
+SET @row_count = 4;
+# Parameter within SLEEP function
+SET @sleep_time_per_result_row = 1;
+# Maximum acceptable delay caused by high load on testing box
+SET @max_acceptable_delay = 2;
+# TIMEDIFF = time for query with sleep (mostly the time caused by SLEEP)
+# + time for delays caused by high load on testing box
+# Ensure that at least a reasonable fraction of TIMEDIFF belongs to the SLEEP
+# by appropriate setting of variables.
+# Ensure that any "judging" has a base of minimum three attempts.
+# (Test 2 uses all attempts except the first one.)
+if (!` SELECT (@sleep_time_per_result_row * @row_count - @max_acceptable_delay >
+ @sleep_time_per_result_row) AND (@row_count - 1 >= 3)`)
+{
+ --echo # Have to abort because of error in plausibility check
+ --echo ######################################################
+ --vertical_results
+ SELECT @sleep_time_per_result_row * @row_count - @max_acceptable_delay >
+ @sleep_time_per_result_row AS must_be_1,
+ @row_count - 1 >= 3 AS must_be_also_1,
+ @sleep_time_per_result_row, @row_count, @max_acceptable_delay;
+ exit;
+}
+SET @@global.query_cache_size = 1024 * 64;
+--disable_warnings
+DROP TEMPORARY TABLE IF EXISTS t_history;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+CREATE TEMPORARY TABLE t_history (attempt SMALLINT,
+start_ts DATETIME, end_ts DATETIME,
+start_cached INTEGER, end_cached INTEGER);
+CREATE TABLE t1 (f1 BIGINT);
+let $num = `SELECT @row_count`;
+--disable_query_log
+begin;
+while ($num)
+{
+ INSERT INTO t1 VALUES (1);
+ dec $num;
+}
+commit;
+--enable_query_log
+
+let $loops = 4;
+let $num = $loops;
+while ($num)
+{
+ let $Qcache_queries_in_cache =
+ query_get_value(SHOW STATUS LIKE 'Qcache_queries_in_cache', Value, 1);
+ eval
+ INSERT INTO t_history
+ SET attempt = $loops - $num + 1, start_ts = NOW(),
+ start_cached = $Qcache_queries_in_cache;
+ SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
+ #
+ # Do not determine Qcache_queries_in_cache before updating end_ts. The SHOW
+ # might cost too much time on an overloaded box.
+ eval
+ UPDATE t_history SET end_ts = NOW()
+ WHERE attempt = $loops - $num + 1;
+ let $Qcache_queries_in_cache =
+ query_get_value(SHOW STATUS LIKE 'Qcache_queries_in_cache', Value, 1);
+ eval
+ UPDATE t_history SET end_cached = $Qcache_queries_in_cache
+ WHERE attempt = $loops - $num + 1;
+ # DEBUG eval SELECT * FROM t_history WHERE attempt = $loops - $num + 1;
+ dec $num;
+}
+
+# 1. The majority of queries with SLEEP must need a reasonable time
+# -> SLEEP has an impact on runtime
+# = Replacement for original Bug#6760 test
+# -> total runtime is clear more needed than for one result row needed
+# = Replacement for one of the original Bug#12689 tests
+--echo # Test 1: Does the query with SLEEP need a reasonable time?
+eval SELECT COUNT(*) >= $loops - 1 INTO @aux1 FROM t_history
+WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
+ BETWEEN 0 AND @max_acceptable_delay;
+SELECT @aux1 AS "Expect 1";
+#
+# 2. The majority of queries (the first one must be ignored) with SLEEP must
+# need a reasonable time
+# -> If we assume that the result of a cached query will be sent back
+# immediate, without any sleep, than the query with SLEEP cannot be cached
+# (current and intended behaviour for queries with SLEEP).
+# -> It could be also not excluded that the query was cached but the server
+# honoured somehow the SLEEP. Such a behaviour would be also acceptable.
+# = Replacement for one of the original Bug#12689 tests
+--echo # Test 2: Does the query with SLEEP need a reasonable time even in case
+--echo # of the non first execution?
+eval SELECT COUNT(*) >= $loops - 1 - 1 INTO @aux2 FROM t_history
+WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
+ BETWEEN 0 AND @max_acceptable_delay
+ AND attempt > 1;
+SELECT @aux2 AS "Expect 1";
+#
+# 3. The query with SLEEP should be not cached.
+# -> SHOW STATUS Qcache_queries_in_cache must be not incremented after
+# the execution of the query with SLEEP
+--echo # Test 3: The query with SLEEP must be not cached.
+eval SELECT COUNT(*) = $loops INTO @aux3 FROM t_history
+WHERE end_cached = start_cached;
+SELECT @aux3 AS "Expect 1";
+#
+# Dump the content of t_history if one of the tests failed.
+if (`SELECT @aux1 + @aux2 + @aux3 <> 3`)
+{
+ --echo # Some tests failed, dumping the content of t_history
+ SELECT * FROM t_history;
+}
+DROP TABLE t1;
+DROP TEMPORARY TABLE t_history;
+SET @@global.query_cache_size = default;
+
--echo End of 5.0 tests
#
diff --git a/mysql-test/t/query_cache_notembedded.test b/mysql-test/t/query_cache_notembedded.test
index 095d47f5bdf..d807215348c 100644
--- a/mysql-test/t/query_cache_notembedded.test
+++ b/mysql-test/t/query_cache_notembedded.test
@@ -274,5 +274,52 @@ set GLOBAL query_cache_limit=default;
set GLOBAL query_cache_min_res_unit=default;
set GLOBAL query_cache_size=default;
+#
+# Bug#3583 query cache doesn't work for stored procedures
+#
+delimiter |;
+--disable_warnings
+drop table if exists t1|
+--enable_warnings
+create table t1 (
+ id char(16) not null default '',
+ data int not null
+)|
+--disable_warnings
+drop procedure if exists bug3583|
+--enable_warnings
+--disable_warnings
+drop procedure if exists bug3583|
+--enable_warnings
+create procedure bug3583()
+begin
+ declare c int;
+
+ select * from t1;
+ select count(*) into c from t1;
+ select c;
+end|
+
+insert into t1 values ("x", 3), ("y", 5)|
+set @x = @@query_cache_size|
+set global query_cache_size = 10*1024*1024|
+
+flush status|
+flush query cache|
+show status like 'Qcache_hits'|
+call bug3583()|
+show status like 'Qcache_hits'|
+call bug3583()|
+call bug3583()|
+show status like 'Qcache_hits'|
+
+set global query_cache_size = @x|
+flush status|
+flush query cache|
+delete from t1|
+drop procedure bug3583|
+drop table t1|
+delimiter ;|
+
# Wait till we reached the initial number of concurrent sessions
--source include/wait_until_count_sessions.inc
diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test
index f593e184ad2..f8803e28e0a 100644
--- a/mysql-test/t/sp_notembedded.test
+++ b/mysql-test/t/sp_notembedded.test
@@ -56,52 +56,6 @@ show warnings|
drop procedure bug4902_2|
#
-# Bug#3583 query cache doesn't work for stored procedures
-#
---disable_warnings
-drop table if exists t1|
---enable_warnings
-create table t1 (
- id char(16) not null default '',
- data int not null
-)|
---disable_warnings
-drop procedure if exists bug3583|
---enable_warnings
---disable_warnings
-drop procedure if exists bug3583|
---enable_warnings
-create procedure bug3583()
-begin
- declare c int;
-
- select * from t1;
- select count(*) into c from t1;
- select c;
-end|
-
-insert into t1 values ("x", 3), ("y", 5)|
-set @x = @@query_cache_size|
-set global query_cache_size = 10*1024*1024|
-
-flush status|
-flush query cache|
-show status like 'Qcache_hits'|
-call bug3583()|
-show status like 'Qcache_hits'|
-call bug3583()|
-call bug3583()|
-show status like 'Qcache_hits'|
-
-set global query_cache_size = @x|
-flush status|
-flush query cache|
-delete from t1|
-drop procedure bug3583|
-drop table t1|
-
-
-#
# Bug#6807 Stored procedure crash if CREATE PROCEDURE ... KILL QUERY
#
--disable_warnings
diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test
index 7bf252040e5..4364b0c820e 100644
--- a/mysql-test/t/udf.test
+++ b/mysql-test/t/udf.test
@@ -342,29 +342,6 @@ drop function myfunc_double;
drop function myfunc_int;
#
-# Bug #28921: Queries containing UDF functions are cached
-#
-
---replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
-eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB";
-create table t1 (a char);
-
-set GLOBAL query_cache_size=1355776;
-reset query cache;
-
-select metaphon('MySQL') from t1;
-show status like "Qcache_hits";
-show status like "Qcache_queries_in_cache";
-
-select metaphon('MySQL') from t1;
-show status like "Qcache_hits";
-show status like "Qcache_queries_in_cache";
-
-drop table t1;
-drop function metaphon;
-set GLOBAL query_cache_size=default;
-
-#
# Bug#28318 CREATE FUNCTION (UDF) requires a schema
#
diff --git a/mysql-test/t/udf_query_cache-master.opt b/mysql-test/t/udf_query_cache-master.opt
new file mode 100644
index 00000000000..7d8786c156a
--- /dev/null
+++ b/mysql-test/t/udf_query_cache-master.opt
@@ -0,0 +1 @@
+$UDF_EXAMPLE_LIB_OPT
diff --git a/mysql-test/t/udf_query_cache.test b/mysql-test/t/udf_query_cache.test
new file mode 100644
index 00000000000..cb64a2f1665
--- /dev/null
+++ b/mysql-test/t/udf_query_cache.test
@@ -0,0 +1,35 @@
+--source include/have_udf.inc
+--source include/have_query_cache.inc
+#
+# To run this tests the "sql/udf_example.c" need to be compiled into
+# udf_example.so and LD_LIBRARY_PATH should be setup to point out where
+# the library are.
+#
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+#
+# Bug #28921: Queries containing UDF functions are cached
+#
+
+--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
+eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB";
+create table t1 (a char);
+
+set GLOBAL query_cache_size=1355776;
+reset query cache;
+
+select metaphon('MySQL') from t1;
+show status like "Qcache_hits";
+show status like "Qcache_queries_in_cache";
+
+select metaphon('MySQL') from t1;
+show status like "Qcache_hits";
+show status like "Qcache_queries_in_cache";
+
+drop table t1;
+drop function metaphon;
+set GLOBAL query_cache_size=default;
+
diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test
index c8ee633dab3..593ea3de7c1 100644
--- a/mysql-test/t/variables.test
+++ b/mysql-test/t/variables.test
@@ -28,8 +28,6 @@ set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size;
set @my_net_buffer_length =@@global.net_buffer_length;
set @my_net_write_timeout =@@global.net_write_timeout;
set @my_net_read_timeout =@@global.net_read_timeout;
-set @my_query_cache_limit =@@global.query_cache_limit;
-set @my_query_cache_type =@@global.query_cache_type;
set @my_rpl_recovery_rank =@@global.rpl_recovery_rank;
set @my_server_id =@@global.server_id;
set @my_slow_launch_time =@@global.slow_launch_time;
@@ -138,7 +136,6 @@ show local variables like 'storage_engine';
select * from information_schema.session_variables where variable_name like 'storage_engine';
show global variables like 'storage_engine';
select * from information_schema.global_variables where variable_name like 'storage_engine';
-set GLOBAL query_cache_size=100000;
set GLOBAL myisam_max_sort_file_size=2000000;
show global variables like 'myisam_max_sort_file_size';
@@ -255,8 +252,6 @@ set storage_engine=UNKNOWN_TABLE_TYPE;
--error ER_WRONG_VALUE_FOR_VAR
set storage_engine=MERGE, big_tables=2;
show local variables like 'storage_engine';
---error ER_GLOBAL_VARIABLE
-set SESSION query_cache_size=10000;
--error ER_NO_DEFAULT
set GLOBAL storage_engine=DEFAULT;
--error ER_UNKNOWN_CHARACTER_SET
@@ -334,9 +329,6 @@ set myisam_sort_buffer_size=100;
set global net_buffer_length=100;
set net_read_timeout=100;
set net_write_timeout=100;
-set global query_cache_limit=100;
-set global query_cache_size=100;
-set global query_cache_type=demand;
set read_buffer_size=100;
set read_rnd_buffer_size=100;
set global rpl_recovery_rank=100;
@@ -822,8 +814,6 @@ set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size;
set global net_buffer_length =@my_net_buffer_length;
set global net_write_timeout =@my_net_write_timeout;
set global net_read_timeout =@my_net_read_timeout;
-set global query_cache_limit =@my_query_cache_limit;
-set global query_cache_type =@my_query_cache_type;
set global rpl_recovery_rank =@my_rpl_recovery_rank;
set global server_id =@my_server_id;
set global slow_launch_time =@my_slow_launch_time;
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index 0efe779c7bc..7f5bcaff083 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -2465,6 +2465,34 @@ static uint query_cache_hits(MYSQL *conn)
/*
+ Check that query cache is available in server.
+*/
+static my_bool is_query_cache_available()
+{
+ int rc;
+ MYSQL_RES *result;
+ MYSQL_ROW row;
+ int res= -1;
+
+ rc= mysql_query(mysql, "SHOW VARIABLES LIKE 'have_query_cache'");
+ myquery(rc);
+
+ result= mysql_store_result(mysql);
+ DIE_UNLESS(result);
+
+ row= mysql_fetch_row(result);
+ DIE_UNLESS(row != NULL);
+ if (strcmp(row[1], "YES") == 0)
+ res= 1;
+ else if (strcmp(row[1], "NO") == 0)
+ res= 0;
+ mysql_free_result(result);
+
+ DIE_UNLESS(res == 0 || res == 1);
+ return res;
+}
+
+/*
Test that prepared statements make use of the query cache just as normal
statements (BUG#735).
*/
@@ -2508,6 +2536,12 @@ static void test_ps_query_cache()
myheader("test_ps_query_cache");
+ if (! is_query_cache_available())
+ {
+ fprintf(stdout, "Skipping test_ps_query_cache: Query cache not available.\n");
+ return;
+ }
+
rc= mysql_query(mysql, "SET SQL_MODE=''");
myquery(rc);
@@ -17863,8 +17897,6 @@ static void test_bug43560(void)
Bug#36326: nested transaction and select
*/
-#ifdef HAVE_QUERY_CACHE
-
static void test_bug36326()
{
int rc;
@@ -17872,6 +17904,12 @@ static void test_bug36326()
DBUG_ENTER("test_bug36326");
myheader("test_bug36326");
+ if (! is_query_cache_available())
+ {
+ fprintf(stdout, "Skipping test_bug36326: Query cache not available.\n");
+ DBUG_VOID_RETURN;
+ }
+
rc= mysql_autocommit(mysql, TRUE);
myquery(rc);
rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
@@ -17911,8 +17949,6 @@ static void test_bug36326()
DBUG_VOID_RETURN;
}
-#endif
-
/**
Bug#41078: With CURSOR_TYPE_READ_ONLY mysql_stmt_fetch() returns short
string value.
@@ -18373,9 +18409,7 @@ static struct my_tests_st my_tests[]= {
{ "test_bug38486", test_bug38486 },
{ "test_bug40365", test_bug40365 },
{ "test_bug43560", test_bug43560 },
-#ifdef HAVE_QUERY_CACHE
{ "test_bug36326", test_bug36326 },
-#endif
{ "test_bug41078", test_bug41078 },
{ "test_bug44495", test_bug44495 },
{ 0, 0 }