1
SET @global_auto_increment_increment = @@global.auto_increment_increment;
2
SET @session_auto_increment_increment = @@session.auto_increment_increment;
3
SET @global_auto_increment_offset = @@global.auto_increment_offset;
4
SET @session_auto_increment_offset = @@session.auto_increment_offset;
5
drop table if exists t1;
8
id INT NOT NULL auto_increment,
12
'#--------------------FN_DYNVARS_001_01-------------------------#'
13
## Setting initial value of auto_increment_increment to 5 ##
14
SET @@auto_increment_increment = 5;
15
'#--------------------FN_DYNVARS_001_02-------------------------#'
16
## Inserting first record in table to check behavior of the variable ##
17
INSERT into t1(name) values('Record_1');
21
## Changing value of variable to 10 ##
22
SET @@global.auto_increment_increment = 10;
23
## Inserting record and verifying value of column id ##
24
INSERT into t1(name) values('Record_2');
29
## Test behavior of variable after assigning some larger value to it ##
30
SELECT @@auto_increment_increment;
31
@@auto_increment_increment
33
SET @@auto_increment_increment = 100;
34
INSERT into t1(name) values('Record_5');
40
'#--------------------FN_DYNVARS_001_03-------------------------#'
41
## Creating new connection test_con1 ##
42
## Value of session & global vairable here should be 10 ##
43
SELECT @@global.auto_increment_increment = 10;
44
@@global.auto_increment_increment = 10
46
SELECT @@session.auto_increment_increment = 10;
47
@@session.auto_increment_increment = 10
49
## Setting global value of variable and inserting data in table ##
50
SET @@global.auto_increment_increment = 20;
51
SELECT @@global.auto_increment_increment;
52
@@global.auto_increment_increment
54
INSERT into t1(name) values('Record_6');
61
## Setting session value of variable and inserting data in table ##
62
SET @@session.auto_increment_increment = 2;
63
SELECT @@session.auto_increment_increment;
64
@@session.auto_increment_increment
66
INSERT into t1(name) values('Record_8');
67
INSERT into t1(name) values('Record_9');
76
'#--------------------FN_DYNVARS_001_04-------------------------#'
77
## Creating another new connection test_con2 ##
78
## Verifying initial values of variable in global & session scope ##
79
## global & session initial value should be 20 ##
80
SELECT @@global.auto_increment_increment = 20;
81
@@global.auto_increment_increment = 20
83
SELECT @@session.auto_increment_increment = 20;
84
@@session.auto_increment_increment = 20
86
## Setting value of session variable to 5 and verifying its behavior ##
87
SET @@session.auto_increment_increment = 5;
88
INSERT into t1(name) values('Record_10');
98
'Bug#35362: Here Record_10 id should be 120 instead of 115 because we'
99
'have set the value of variable to 5'
100
SET @@session.auto_increment_increment = 1;
101
SELECT @@auto_increment_increment;
102
@@auto_increment_increment
104
SELECT @@global.auto_increment_increment;
105
@@global.auto_increment_increment
107
'#--------------------FN_DYNVARS_001_05-------------------------#'
108
## Switching to test_con1 ##
109
## Verifying values of global & session value of variable ##
110
## global value should be 20 ##
111
SELECT @@global.auto_increment_increment = 20;
112
@@global.auto_increment_increment = 20
114
## session value should be 2 ##
115
SELECT @@session.auto_increment_increment = 2;
116
@@session.auto_increment_increment = 2
118
INSERT into t1(name) values('Record_11');
119
INSERT into t1(name) values('Record_12');
131
'#--------------------FN_DYNVARS_001_06-------------------------#'
132
## Changing column's datatype to SmallInt and verifying variable's behavior ##
133
ALTER table t1 MODIFY id SMALLINT NOT NULL auto_increment;
134
INSERT into t1(name) values('Record_13');
135
INSERT into t1(name) values('Record_14');
149
## Changing column's datatype to BigInt and verifying variable's behavior ##
150
ALTER table t1 MODIFY id BIGINT NOT NULL auto_increment;
151
INSERT into t1(name) values('Record_15');
152
INSERT into t1(name) values('Record_16');
168
'#--------------------FN_DYNVARS_001_07-------------------------#'
169
## Verifying behavior of variable with negative value ##
170
SET @@auto_increment_increment = -10;
172
Warning 1292 Truncated incorrect auto-increment-increment value: '0'
173
INSERT into t1(name) values('Record_17');
174
INSERT into t1(name) values('Record_18');
192
'Bug#35364: Variable is incrementing some random values on assigning -ve value'
193
## Disconnecting test_con2 ##
194
## Dropping table t1 ##
195
DROP table if exists t1;
196
## Disconnecting test_con1 ##
197
## switching to default connection ##
198
SET @@global.auto_increment_increment = @global_auto_increment_increment;
199
SET @@session.auto_increment_increment = @session_auto_increment_increment;
200
SET @@global.auto_increment_offset = @global_auto_increment_offset;
201
SET @@session.auto_increment_offset = @session_auto_increment_offset;