~drizzle-trunk/drizzle/development

# This test requires that --log-output includes 'table', and the general

# log is on

# PS causes different statistics

--disable_ps_protocol

connect (con1,localhost,root,,);

connect (con2,localhost,root,,);

flush status;

# Logging to the general query log table (--log-output=table --log) increments

# Table_locks_immediate with each query, so here Immediate becomes 1

show status like 'Table_lock%';

# ++Immediate = 2

select * from information_schema.session_status where variable_name like 'Table_lock%';

connection con1;

# ++Immediate = 3

SET SQL_LOG_BIN=0;

set @old_general_log = @@global.general_log;                                      

set global general_log = 'OFF';

--disable_warnings

# ++Immediate = 4

drop table if exists t1;

--enable_warnings

# ++Immediate = 5

create table t1(n int) engine=myisam;

# Immediate + 2 = 7

insert into t1 values(1);

connection con2;

# Immediate + 2 = 9

lock tables t1 read;

# ++Immediate = 10

unlock tables;

# Immediate + 2 = 12

lock tables t1 read;

connection con1;

# ++Immediate = 13

let $ID= `select connection_id()`;

# ++Immediate = 14 (Not +2, because this increments Table_locks_waited)

--send

update t1 set n = 3;

connection con2;

# wait for the other query to start executing

let $wait_condition= select 1 from INFORMATION_SCHEMA.PROCESSLIST where ID = $ID and STATE = "Table lock";

# Immediate = 14 + $wait_condition_reps ($wait_timeout is 0, so no extra select

# is done inside wait_condition.inc)

--source include/wait_condition.inc

# ++Immediate = 15 + $wait_condition_reps

unlock tables;

connection con1;

reap;

# ++Immediate = 16 + $wait_condition_reps

show status like 'Table_locks_waited';

drop table t1;

set global general_log = @old_general_log;

disconnect con2;

disconnect con1;

connection default;

# End of 4.1 tests

#

# last_query_cost

#

select 1;

show status like 'last_query_cost';

create table t1 (a int);

insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

select * from t1 where a=6;

show status like 'last_query_cost';

# Ensure value dosn't change by second status call

show status like 'last_query_cost';

select 1;

show status like 'last_query_cost';

drop table t1;

#

# Test for Bug #15933 max_used_connections is wrong after FLUSH STATUS

# if connections are cached

#

#

# The first suggested fix from the bug report was chosen

# (see http://bugs.mysql.com/bug.php?id=15933):

#

#   a) On flushing the status, set max_used_connections to

#   threads_connected, not to 0.

#

#   b) Check if it is necessary to increment max_used_connections when

#   taking a thread from the cache as well as when creating new threads

#

# Wait for at most $disconnect_timeout seconds for disconnects to finish.

let $disconnect_timeout = 10;

# Wait for any previous disconnects to finish.

FLUSH STATUS;

--disable_query_log

--disable_result_log

eval SET @wait_left = $disconnect_timeout;

let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;

eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;

let $wait_more = `SELECT @max_used_connections != 1 && @wait_left > 0`;

while ($wait_more)

{

  sleep 1;

  FLUSH STATUS;

  SET @wait_left = @wait_left - 1;

  let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;

  eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;

  let $wait_more = `SELECT @max_used_connections != 1 && @wait_left > 0`;

}

--enable_query_log

--enable_result_log

# Prerequisite.

SHOW STATUS LIKE 'max_used_connections';

SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';

# Save original setting.

SET @save_thread_cache_size=@@thread_cache_size;

SET GLOBAL thread_cache_size=3;

connect (con1,localhost,root,,);

connect (con2,localhost,root,,);

connection con1;

disconnect con2;

# Check that max_used_connections still reflects maximum value.

SHOW STATUS LIKE 'max_used_connections';

SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';

# Check that after flush max_used_connections equals to current number

# of connections.  First wait for previous disconnect to finish.

FLUSH STATUS;

--disable_query_log

--disable_result_log

eval SET @wait_left = $disconnect_timeout;

let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;

eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;

let $wait_more = `SELECT @max_used_connections != 2 && @wait_left > 0`;

while ($wait_more)

{

  sleep 1;

  FLUSH STATUS;

  SET @wait_left = @wait_left - 1;

  let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;

  eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;

  let $wait_more = `SELECT @max_used_connections != 2 && @wait_left > 0`;

}

--enable_query_log

--enable_result_log

# Check that we don't count disconnected thread any longer.

SHOW STATUS LIKE 'max_used_connections';

SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';

# Check that max_used_connections is updated when cached thread is

# reused...

connect (con2,localhost,root,,);

SHOW STATUS LIKE 'max_used_connections';

SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';

# ...and when new thread is created.

connect (con3,localhost,root,,);

SHOW STATUS LIKE 'max_used_connections';

SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';

# Restore original setting.

connection default;

SET GLOBAL thread_cache_size=@save_thread_cache_size;

disconnect con3;

disconnect con2;

disconnect con1;

#

# Bug #30377: EXPLAIN loses last_query_cost when used with UNION

#

CREATE TABLE t1 ( a INT );

INSERT INTO t1 VALUES (1), (2);

SELECT a FROM t1 LIMIT 1;

SHOW SESSION STATUS LIKE 'Last_query_cost';

EXPLAIN SELECT a FROM t1;

SHOW SESSION STATUS LIKE 'Last_query_cost';

SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a;

SHOW SESSION STATUS LIKE 'Last_query_cost';

EXPLAIN SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a;

SHOW SESSION STATUS LIKE 'Last_query_cost';

SELECT a IN (SELECT a FROM t1) FROM t1 LIMIT 1;

SHOW SESSION STATUS LIKE 'Last_query_cost';

SELECT (SELECT a FROM t1 LIMIT 1) x FROM t1 LIMIT 1;

SHOW SESSION STATUS LIKE 'Last_query_cost';

SELECT * FROM t1 a, t1 b LIMIT 1;

SHOW SESSION STATUS LIKE 'Last_query_cost';

DROP TABLE t1;

# End of 5.0 tests

#

# Ensure that SHOW STATUS only changes global status variables

#

connect (con1,localhost,root,,);

let $rnd_next = `show global status like 'handler_read_rnd_next'`;

let $tmp_table = `show global status like 'Created_tmp_tables'`;

show status like 'com_show_status';

show status like 'hand%write%';

show status like '%tmp%';

show status like 'hand%write%';

show status like '%tmp%';

show status like 'com_show_status';

let $rnd_next2 = `show global status like 'handler_read_rnd_next'`;

let $tmp_table2 = `show global status like 'Created_tmp_tables'`;

--disable_query_log

eval select substring_index('$rnd_next2',0x9,-1)-substring_index('$rnd_next',0x9,-1) as rnd_diff, substring_index('$tmp_table2',0x9,-1)-substring_index('$tmp_table',0x9,-1) as tmp_table_diff;

--enable_query_log

# 

# Bug#30252 Com_create_function is not incremented.

#

show global status like 'Com%function%';

DELIMITER //;

create function f1 (x INTEGER) returns integer

  begin

    declare ret integer;

    set ret = x * 10;

    return ret;

  end //

DELIMITER ;//

drop function f1;

show global status like 'Com%function%';

# End of 5.1 tests

#

# Bug #17954: Threads_connected > Threads_created

#

SELECT VARIABLE_VALUE INTO @tc FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected';

SELECT VARIABLE_NAME FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_created' AND VARIABLE_VALUE < @tc;

SELECT VARIABLE_VALUE INTO @tr FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_running';

FLUSH STATUS;

SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected' AND VARIABLE_VALUE < @tc;

SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_running' AND VARIABLE_VALUE < @tr;