Debian Codegraph @

mysql-test/suite/sys_vars/t/concurrent_insert_func.test

raw
1 ############## mysql-test\t\concurrent_insert_basic.test #######################
2 # #
3 # Variable Name: concurrent_insert #
4 # Scope: GLOBAL #
5 # Access Type: Dynamic #
6 # Data Type: Boolean & Numeric #
7 # Default Value: 1 #
8 # Valid Values: 0,1 & 2 #
9 # #
10 # #
11 # Creation Date: 2008-03-07 #
12 # Author: Salman Rawala #
13 # #
14 # Modified: HHunger 2009-02-23 Inserted a wait condition right after the #
15 # "INSERT ..record_6" to wait for the end of #
16 # the insert. #
17 # mleich This test needs some inporovements #
18 # #
19 # Description: Test Cases of Dynamic System Variable "concurrent_insert" #
20 # that checks functionality of this variable #
21 # #
22 # Reference: #
23 # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
24 # #
25 ################################################################################
26 --source include/force_myisam_default.inc
27
28 # Skip the test if MyISAM is not available
29 --source include/have_myisam.inc
30
31 --disable_warnings
32 DROP TABLE IF EXISTS t1;
33 --enable_warnings
34
35 #########################
36 # Creating new table #
37 #########################
38
39 --echo ## Creating new table ##
40 CREATE TABLE t1
41 (
42 name VARCHAR(30)
43 );
44
45 --echo '#--------------------FN_DYNVARS_018_01-------------------------#'
46 ####################################################################
47 # Setting initial value of concurrent_insert to 1
48 # concurrent_insert = 1 means Enables concurrent insert
49 # for MyISAM tables that don't have holes
50 ####################################################################
51
52 SET @start_value= @@global.concurrent_insert;
53
54 --echo ## Setting initial value of variable to 1 ##
55 SET @@global.concurrent_insert = 1;
56 INSERT INTO t1(name) VALUES('Record_1');
57 INSERT INTO t1(name) VALUES('Record_2');
58 INSERT INTO t1(name) VALUES('Record_3');
59
60 --echo ## locking table ##
61 LOCK TABLE t1 READ LOCAL;
62
63 --echo ## Creating new connection to insert some rows in table ##
64 connect (test_con1,localhost,root,,);
65 --echo connection test_con1;
66 connection test_con1;
67
68 --echo ## New records should come at the end of all rows ##
69 INSERT INTO t1(name) VALUES('Record_4');
70 SELECT * FROM t1;
71
72 --echo ## unlocking tables ##
73 --echo connection default;
74 connection default;
75 UNLOCK TABLES;
76
77 --echo ## deleting record to create hole in table ##
78 DELETE FROM t1 WHERE name ='Record_2';
79
80
81 --echo '#--------------------FN_DYNVARS_018_02-------------------------#'
82 ####################################################################
83 # Setting initial value of concurrent_insert to 1
84 # concurrent_insert = 1 and trying to insert some values
85 # in MyISAM tables that have holes
86 ####################################################################
87 # lock table and connect with connection1
88 LOCK TABLE t1 READ LOCAL;
89 --echo connection test_con1;
90 connection test_con1;
91
92 # setting value of concurrent_insert to 1
93 SET @@global.concurrent_insert=1;
94
95 --echo ## send INSERT which should be blocked until unlock of the table ##
96 send
97 INSERT INTO t1(name) VALUES('Record_7');
98
99 --echo connection default;
100 connection default;
101 # wait until INSERT will be locked (low performance)
102 let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
103 WHERE state= "Waiting for table level lock" AND
104 info LIKE "INSERT INTO t1%";
105 --source include/wait_condition.inc
106
107 --echo ## show processlist info and state ##
108 SELECT state,info FROM INFORMATION_SCHEMA.PROCESSLIST
109 WHERE state= "Waiting for table level lock" AND info LIKE "INSERT INTO t1%";
110 --echo ## table contents befor UNLOCK ##
111 SELECT * FROM t1;
112 UNLOCK TABLES;
113 --echo ## table contens after UNLOCK ##
114 SELECT * FROM t1;
115 INSERT INTO t1(name) VALUES('Record_6');
116 let $wait_condition= SELECT COUNT(*) = 5 FROM t1;
117 --source include/wait_condition.inc
118
119 --echo connection test_con1;
120 connection test_con1;
121 # to complete the send above^
122 reap;
123 SELECT * FROM t1;
124 --echo connection default;
125 connection default;
126
127 --echo '#--------------------FN_DYNVARS_018_03-------------------------#'
128 ################################################################################
129 # Setting value of concurrent_insert to 2 to verify values after inserting
130 # it into table with holes
131 # concurrent_insert = 2 means Enables concurrent insert
132 # for MyISAM tables that have holes but inserts values at the end of all rows
133 ################################################################################
134
135 --echo ## lock table and connect with connection1 ##
136 LOCK TABLE t1 READ LOCAL;
137 --echo connection test_con1;
138 connection test_con1;
139 --echo ## setting value of concurrent_insert to 2 ##
140 SET @@global.concurrent_insert=2;
141
142 --echo ## Inserting record in table, record should go at the end of the table ##
143 INSERT INTO t1(name) VALUES('Record_5');
144 SELECT * FROM t1;
145 SELECT @@concurrent_insert;
146
147 --echo connection default;
148 connection default;
149
150 --echo ## Unlocking table ##
151 UNLOCK TABLES;
152
153 SELECT * FROM t1;
154
155 --echo ## Inserting new row, this should go in the hole ##
156 INSERT INTO t1(name) VALUES('Record_6');
157 SELECT * FROM t1;
158
159 --echo ## connection test_con1 ##
160
161 DELETE FROM t1 WHERE name ='Record_3';
162 SELECT * FROM t1;
163
164 --echo ## Dropping table ##
165 DROP TABLE t1;
166
167 --echo ## Disconnecting connection ##
168 disconnect test_con1;
169
170 SET @@global.concurrent_insert= @start_value;
171