Edit File: concurrent_insert_func.test
############## 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 # # # # Modified: HHunger 2009-02-23 Inserted a wait condition right after the # # "INSERT ..record_6" to wait for the end of # # the insert. # # mleich This test needs some inporovements # # # # 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= "Waiting for table level lock" 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= "Waiting for table level lock" 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'); let $wait_condition= SELECT COUNT(*) = 5 FROM t1; --source include/wait_condition.inc --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;
Back to File Manager