1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
############## 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,,);
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 ##
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;
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');
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
connection test_con1;
# to complete the send above^
reap;
SELECT * FROM t1;
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;
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;
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;
disconnect test_con1;
SET @@global.concurrent_insert= @start_value;
|
