diff options
Diffstat (limited to 'mysql-test/suite/sys_vars/t/concurrent_insert_func.test')
| -rw-r--r-- | mysql-test/suite/sys_vars/t/concurrent_insert_func.test | 162 |
1 files changed, 162 insertions, 0 deletions
diff --git a/mysql-test/suite/sys_vars/t/concurrent_insert_func.test b/mysql-test/suite/sys_vars/t/concurrent_insert_func.test new file mode 100644 index 00000000000..cdeb76d9cac --- /dev/null +++ b/mysql-test/suite/sys_vars/t/concurrent_insert_func.test @@ -0,0 +1,162 @@ +############## mysql-test\t\concurrent_insert_basic.test ####################### +# # +# Variable Name: concurrent_insert # +# Scope: GLOBAL # +# Access Type: Dynamic # +# Data Type: Boolean & Numeric # +# Default Value: 1 # +# Valid Values: 0,1 & 2 # +# # +# # +# Creation Date: 2008-03-07 # +# Author: Salman Rawala # +# # +# Description: Test Cases of Dynamic System Variable "concurrent_insert" # +# that checks functionality of this variable # +# # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # +# # +################################################################################ + +--source include/not_embedded.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +######################### +# Creating new table # +######################### + +--echo ## Creating new table ## +CREATE TABLE t1 +( +name VARCHAR(30) +); + +--echo '#--------------------FN_DYNVARS_018_01-------------------------#' +#################################################################### +# Setting initial value of concurrent_insert to 1 +# concurrent_insert = 1 means Enables concurrent insert +# for MyISAM tables that don't have holes +#################################################################### + +SET @start_value= @@global.concurrent_insert; + +--echo ## Setting initial value of variable to 1 ## +SET @@global.concurrent_insert = 1; +INSERT INTO t1(name) VALUES('Record_1'); +INSERT INTO t1(name) VALUES('Record_2'); +INSERT INTO t1(name) VALUES('Record_3'); + +--echo ## locking table ## +LOCK TABLE t1 READ LOCAL; + +--echo ## Creating new connection to insert some rows in table ## +connect (test_con1,localhost,root,,); +--echo connection test_con1; +connection test_con1; + +--echo ## New records should come at the end of all rows ## +INSERT INTO t1(name) VALUES('Record_4'); +SELECT * FROM t1; + + +--echo ## unlocking tables ## +--echo connection default; +connection default; +UNLOCK TABLES; + +--echo ## deleting record to create hole in table ## +DELETE FROM t1 WHERE name ='Record_2'; + + +--echo '#--------------------FN_DYNVARS_018_02-------------------------#' +#################################################################### +# Setting initial value of concurrent_insert to 1 +# concurrent_insert = 1 and trying to insert some values +# in MyISAM tables that have holes +#################################################################### +# lock table and connect with connection1 +LOCK TABLE t1 READ LOCAL; +--echo connection test_con1; +connection test_con1; + +# setting value of concurrent_insert to 1 +SET @@global.concurrent_insert=1; + +--echo ## send INSERT which should be blocked until unlock of the table ## +send +INSERT INTO t1(name) VALUES('Record_7'); + +--echo connection default; +connection default; +# wait until INSERT will be locked (low performance) +let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE state= "Locked" AND info LIKE "INSERT INTO t1%"; +--source include/wait_condition.inc + +--echo ## show processlist info and state ## +SELECT state,info FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE state= "Locked" AND info LIKE "INSERT INTO t1%"; +--echo ## table contents befor UNLOCK ## +SELECT * FROM t1; +UNLOCK TABLES; +--echo ## table contens after UNLOCK ## +SELECT * FROM t1; +INSERT INTO t1(name) VALUES('Record_6'); + +--echo connection test_con1; +connection test_con1; +# to complete the send above^ +reap; +SELECT * FROM t1; +--echo connection default; +connection default; + +--echo '#--------------------FN_DYNVARS_018_03-------------------------#' +################################################################################ +# Setting value of concurrent_insert to 2 to verify values after inserting +# it into table with holes +# concurrent_insert = 2 means Enables concurrent insert +# for MyISAM tables that have holes but inserts values at the end of all rows +################################################################################ + +--echo ## lock table and connect with connection1 ## +LOCK TABLE t1 READ LOCAL; +--echo connection test_con1; +connection test_con1; +--echo ## setting value of concurrent_insert to 2 ## +SET @@global.concurrent_insert=2; + +--echo ## Inserting record in table, record should go at the end of the table ## +INSERT INTO t1(name) VALUES('Record_5'); +SELECT * FROM t1; +SELECT @@concurrent_insert; + +--echo connection default; +connection default; + +--echo ## Unlocking table ## +UNLOCK TABLES; + +SELECT * FROM t1; + +--echo ## Inserting new row, this should go in the hole ## +INSERT INTO t1(name) VALUES('Record_6'); +SELECT * FROM t1; + +--echo ## connection test_con1 ## + +DELETE FROM t1 WHERE name ='Record_3'; +SELECT * FROM t1; + +--echo ## Dropping table ## +DROP TABLE t1; + +--echo ## Disconnecting connection ## +disconnect test_con1; + +SET @@global.concurrent_insert= @start_value; + |