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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
|
SET @global_auto_increment_increment = @@global.auto_increment_increment;
SET @session_auto_increment_increment = @@session.auto_increment_increment;
SET @global_auto_increment_offset = @@global.auto_increment_offset;
SET @session_auto_increment_offset = @@session.auto_increment_offset;
drop table if exists t1;
CREATE TABLE t1
(
id INT NOT NULL auto_increment,
PRIMARY KEY (id),
name VARCHAR(30)
);
'#--------------------FN_DYNVARS_001_01-------------------------#'
## Setting initial value of auto_increment_increment to 5 ##
SET @@auto_increment_increment = 5;
'#--------------------FN_DYNVARS_001_02-------------------------#'
## Inserting first record in table to check behavior of the variable ##
INSERT into t1(name) values('Record_1');
SELECT * from t1;
id name
1 Record_1
## Changing value of variable to 10 ##
SET @@global.auto_increment_increment = 10;
## Inserting record and verifying value of column id ##
INSERT into t1(name) values('Record_2');
SELECT * from t1;
id name
1 Record_1
6 Record_2
## Test behavior of variable after assigning some larger value to it ##
SELECT @@auto_increment_increment;
@@auto_increment_increment
5
SET @@auto_increment_increment = 100;
INSERT into t1(name) values('Record_5');
SELECT * from t1;
id name
1 Record_1
6 Record_2
101 Record_5
'#--------------------FN_DYNVARS_001_03-------------------------#'
CONNECT test_con1,localhost,root,,;
connection test_con1;
## Value of session & global vairable here should be 10 ##
SELECT @@global.auto_increment_increment = 10;
@@global.auto_increment_increment = 10
1
SELECT @@session.auto_increment_increment = 10;
@@session.auto_increment_increment = 10
1
## Setting global value of variable and inserting data in table ##
SET @@global.auto_increment_increment = 20;
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
20
INSERT into t1(name) values('Record_6');
SELECT * from t1;
id name
1 Record_1
6 Record_2
101 Record_5
111 Record_6
## Setting session value of variable and inserting data in table ##
SET @@session.auto_increment_increment = 2;
SELECT @@session.auto_increment_increment;
@@session.auto_increment_increment
2
INSERT into t1(name) values('Record_8');
INSERT into t1(name) values('Record_9');
SELECT * from t1;
id name
1 Record_1
6 Record_2
101 Record_5
111 Record_6
113 Record_8
115 Record_9
'#--------------------FN_DYNVARS_001_04-------------------------#'
CONNECT test_con2,localhost,root,,;
connection test_con2;
## Verifying initial values of variable in global & session scope ##
## global & session initial value should be 20 ##
SELECT @@global.auto_increment_increment = 20;
@@global.auto_increment_increment = 20
1
SELECT @@session.auto_increment_increment = 20;
@@session.auto_increment_increment = 20
1
## Setting value of session variable to 5 and verifying its behavior ##
SET @@session.auto_increment_increment = 5;
INSERT into t1(name) values('Record_10');
SELECT * from t1;
id name
1 Record_1
6 Record_2
101 Record_5
111 Record_6
113 Record_8
115 Record_9
116 Record_10
SET @@session.auto_increment_increment = 1;
SELECT @@auto_increment_increment;
@@auto_increment_increment
1
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
20
'#--------------------FN_DYNVARS_001_05-------------------------#'
connection test_con1;
## Verifying values of global & session value of variable ##
## global value should be 20 ##
SELECT @@global.auto_increment_increment = 20;
@@global.auto_increment_increment = 20
1
## session value should be 2 ##
SELECT @@session.auto_increment_increment = 2;
@@session.auto_increment_increment = 2
1
INSERT into t1(name) values('Record_11');
INSERT into t1(name) values('Record_12');
SELECT * from t1;
id name
1 Record_1
6 Record_2
101 Record_5
111 Record_6
113 Record_8
115 Record_9
116 Record_10
117 Record_11
119 Record_12
'#--------------------FN_DYNVARS_001_06-------------------------#'
## Changing column's datatype to SmallInt and verifying variable's behavior ##
ALTER table t1 MODIFY id SMALLINT NOT NULL auto_increment;
INSERT into t1(name) values('Record_13');
INSERT into t1(name) values('Record_14');
SELECT * from t1;
id name
1 Record_1
6 Record_2
101 Record_5
111 Record_6
113 Record_8
115 Record_9
116 Record_10
117 Record_11
119 Record_12
121 Record_13
123 Record_14
## Changing column's datatype to BigInt and verifying variable's behavior ##
ALTER table t1 MODIFY id BIGINT NOT NULL auto_increment;
INSERT into t1(name) values('Record_15');
INSERT into t1(name) values('Record_16');
SELECT * from t1;
id name
1 Record_1
6 Record_2
101 Record_5
111 Record_6
113 Record_8
115 Record_9
116 Record_10
117 Record_11
119 Record_12
121 Record_13
123 Record_14
125 Record_15
127 Record_16
'#--------------------FN_DYNVARS_001_07-------------------------#'
## Verifying behavior of variable with negative value ##
SET @@auto_increment_increment = -10;
Warnings:
Warning 1292 Truncated incorrect auto_increment_increment value: '-10'
INSERT into t1(name) values('Record_17');
INSERT into t1(name) values('Record_18');
SELECT * from t1;
id name
1 Record_1
6 Record_2
101 Record_5
111 Record_6
113 Record_8
115 Record_9
116 Record_10
117 Record_11
119 Record_12
121 Record_13
123 Record_14
125 Record_15
127 Record_16
128 Record_17
129 Record_18
disconnect test_con2;
## Dropping table t1 ##
DROP table if exists t1;
disconnect test_con1;
connection default;
SET @@global.auto_increment_increment = @global_auto_increment_increment;
SET @@session.auto_increment_increment = @session_auto_increment_increment;
SET @@global.auto_increment_offset = @global_auto_increment_offset;
SET @@session.auto_increment_offset = @session_auto_increment_offset;
|
