blob: e6ea7c4304bcfaae53c2f64d57cb27de215f895a (
plain)
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
|
############## 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#option_mysqld_concurrent_insert #
# #
################################################################################
--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
####################################################################
--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;
#INSERT into t1(name) values('Record_7');
#SELECT * from t1;
#connection default;
#unlock tables;
#SELECT * from t1;
#INSERT into t1(name) values('Record_6');
# On inserting rows in hole while the value of concurrent_insert is 1
# MySQL server hangs.
--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;
--echo ## Switching to default connection ##
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;
|
