### mysql-test\suite\sys_vars\t\max_delayed_threads_func.test #################
#                                                                             #
# Variable Name: max_delayed_threads                                          #
# Scope: GLOBAL & SESSION                                                     #
# Access Type: Dynamic                                                        #
# Data Type: Numeric                                                          #
# Default Value: 20                                                           # 
# Range: 0 - 16384                                                            #
#                                                                             #
#                                                                             #
# Creation Date: 2008-03-07                                                   #
# Author:  Salman Rawala                                                      #
#                                                                             #
# Description: Test Cases of Dynamic System Variable auto_increment_increment #
#              that checks functionality of this variable                     #
#                                                                             #
# Reference: http://dev.mysql.com/doc/refman/5.1/en/                          #
#  server-system-variables.html#option_mysqld_auto-increment-increment        #
#                                                                             #
###############################################################################

####################################################################
#      Setting initial value of variable                           #
####################################################################
SET @@global.max_insert_delayed_threads = 2;
SELECT @@global.max_insert_delayed_threads;

--echo ## Creating new connections ##
###########################################################
#      Creating new connection                            #
###########################################################
connect (con0,localhost,root,,);
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
connect (con3,localhost,root,,);
connect (con4,localhost,root,,);
connect (con5,localhost,root,,);
connect (con6,localhost,root,,);
connect (con7,localhost,root,,);
connect (con8,localhost,root,,);
connect (con9,localhost,root,,);
connect (con10,localhost,root,,);
connect (con11,localhost,root,,);
connect (con12,localhost,root,,);
connect (con13,localhost,root,,);
connect (con14,localhost,root,,);
connect (con15,localhost,root,,);
connect (con16,localhost,root,,);
connect (con17,localhost,root,,);
connect (con18,localhost,root,,);
connect (con19,localhost,root,,);
connect (con20,localhost,root,,);
connect (con21,localhost,root,,);

connection default;

####################################################
#      Creating different tables                   #
####################################################

CREATE TABLE t1 (a varchar(100), b timestamp);
CREATE TABLE t2 (a varchar(100), b timestamp);
CREATE TABLE t3 (a varchar(100), b timestamp);
CREATE TABLE t4 (a varchar(100), b timestamp);
CREATE TABLE t5 (a varchar(100), b timestamp);
CREATE TABLE t6 (a varchar(100), b timestamp);
CREATE TABLE status(a varchar(100));


--echo '#--------------------FN_DYNVARS_160_01-------------------------#'
###############################################################################
#  Verifying behavior of variable for threads more than variable's value      #
###############################################################################

--echo ## Verifying current process list ##
show processlist;
show status like '%thread%';

--echo ## Locking table for write ##
connection default;
LOCK TABLES t1 write, t2 write, t3 write, t4 write, t5 write, t6 write;

--echo ## Inserting delayed insert rows with different connections ##

delimiter |;

connection con0|
send
INSERT DELAYED into t1 values('test2d', NOW());
INSERT INTO status VALUES('INSERTED T1');|

connection con4|
send
INSERT DELAYED into t2 values('test2d', NOW());
INSERT INTO status VALUES('INSERTED T2');|

connection con1|
send
INSERT DELAYED into t3 values('test2d', NOW());
INSERT INTO status VALUES('INSERTED T3');|

connection con5|
send
INSERT DELAYED into t4 values('test2d', NOW());
INSERT INTO status VALUES('INSERTED T4');|

connection con2|
send
INSERT DELAYED into t5 values('test2d', NOW());
INSERT INTO status VALUES('INSERTED T5');|

connection con6|
send
INSERT DELAYED into t6 values('test2d', NOW());
INSERT INTO status VALUES('INSERTED T6');|

delimiter ;|


--echo ## Verifying process list before unlocking tables ##
connection default;
show processlist;
show status like '%thread%';
SELECT * from t1;
SELECT * from t2;
SELECT * from t3;
SELECT * from t4;
SELECT * from t5;
SELECT * from t6;

--sleep 2
unlock tables;

--echo ## Verifying process list after unlocking tables ##
connection default;
SELECT * from t1;
SELECT * from t2;
SELECT * from t3;
SELECT * from t4;
SELECT * from t5;
SELECT * from t6;                           
show processlist;
show status like '%thread%';

--echo Bug: Inserts delayed are not behaving as normal Insert as mentioned in 
--echo documentation
SELECT * FROM status;