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
27 --source include/not_embedded.inc
28
29 --disable_warnings
30 DROP TABLE IF EXISTS t1;
31 --enable_warnings
32
33 #########################
34 # Creating new table #
35 #########################
36
37 --echo ## Creating new table ##
38 CREATE TABLE t1
39 (
40 name VARCHAR(30)
41 );
42
43 --echo '#--------------------FN_DYNVARS_018_01-------------------------#'
44 ####################################################################
45 # Setting initial value of concurrent_insert to 1
46 # concurrent_insert = 1 means Enables concurrent insert
47 # for MyISAM tables that don't have holes
48 ####################################################################
49
50 SET @start_value= @@global.concurrent_insert;
51
52 --echo ## Setting initial value of variable to 1 ##
53 SET @@global.concurrent_insert = 1;
54 INSERT INTO t1(name) VALUES('Record_1');
55 INSERT INTO t1(name) VALUES('Record_2');
56 INSERT INTO t1(name) VALUES('Record_3');
57
58 --echo ## locking table ##
59 LOCK TABLE t1 READ LOCAL;
60
61 --echo ## Creating new connection to insert some rows in table ##
62 connect (test_con1,localhost,root,,);
63 connection test_con1;
64
65 --echo ## New records should come at the end of all rows ##
66 INSERT INTO t1(name) VALUES('Record_4');
67 SELECT * FROM t1;
68
69 --echo ## unlocking tables ##
70 connection default;
71 UNLOCK TABLES;
72
73 --echo ## deleting record to create hole in table ##
74 DELETE FROM t1 WHERE name ='Record_2';
75
76
77 --echo '#--------------------FN_DYNVARS_018_02-------------------------#'
78 ####################################################################
79 # Setting initial value of concurrent_insert to 1
80 # concurrent_insert = 1 and trying to insert some values
81 # in MyISAM tables that have holes
82 ####################################################################
83 # lock table and connect with connection1
84 LOCK TABLE t1 READ LOCAL;
85 connection test_con1;
86
87 # setting value of concurrent_insert to 1
88 SET @@global.concurrent_insert=1;
89
90 --echo ## send INSERT which should be blocked until unlock of the table ##
91 send
92 INSERT INTO t1(name) VALUES('Record_7');
93
94 connection default;
95 # wait until INSERT will be locked (low performance)
96 let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
97 WHERE state= "Waiting for table level lock" AND
98 info LIKE "INSERT INTO t1%";
99 --source include/wait_condition.inc
100
101 --echo ## show processlist info and state ##
102 SELECT state,info FROM INFORMATION_SCHEMA.PROCESSLIST
103 WHERE state= "Waiting for table level lock" AND info LIKE "INSERT INTO t1%";
104 --echo ## table contents befor UNLOCK ##
105 SELECT * FROM t1;
106 UNLOCK TABLES;
107 --echo ## table contens after UNLOCK ##
108 SELECT * FROM t1;
109 INSERT INTO t1(name) VALUES('Record_6');
110 let $wait_condition= SELECT COUNT(*) = 5 FROM t1;
111 --source include/wait_condition.inc
112
113 connection test_con1;
114 # to complete the send above^
115 reap;
116 SELECT * FROM t1;
117 connection default;
118
119 --echo '#--------------------FN_DYNVARS_018_03-------------------------#'
120 ################################################################################
121 # Setting value of concurrent_insert to 2 to verify values after inserting
122 # it into table with holes
123 # concurrent_insert = 2 means Enables concurrent insert
124 # for MyISAM tables that have holes but inserts values at the end of all rows
125 ################################################################################
126
127 --echo ## lock table and connect with connection1 ##
128 LOCK TABLE t1 READ LOCAL;
129 connection test_con1;
130 --echo ## setting value of concurrent_insert to 2 ##
131 SET @@global.concurrent_insert=2;
132
133 --echo ## Inserting record in table, record should go at the end of the table ##
134 INSERT INTO t1(name) VALUES('Record_5');
135 SELECT * FROM t1;
136 SELECT @@concurrent_insert;
137
138 connection default;
139
140 --echo ## Unlocking table ##
141 UNLOCK TABLES;
142
143 SELECT * FROM t1;
144
145 --echo ## Inserting new row, this should go in the hole ##
146 INSERT INTO t1(name) VALUES('Record_6');
147 SELECT * FROM t1;
148
149 --echo ## connection test_con1 ##
150
151 DELETE FROM t1 WHERE name ='Record_3';
152 SELECT * FROM t1;
153
154 --echo ## Dropping table ##
155 DROP TABLE t1;
156
157 disconnect test_con1;
158
159 SET @@global.concurrent_insert= @start_value;