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;
6
## Creating New Table ##
9
id INT NOT NULL auto_increment,
13
'#--------------------FN_DYNVARS_002_01-------------------------#'
14
## Setting initial value of variable to 5 ##
15
SET @@auto_increment_increment = 10;
16
SET @@auto_increment_offset = 5;
17
'#--------------------FN_DYNVARS_002_02-------------------------#'
18
## Inserting records in table and verifying variable's behavior ##
19
INSERT into t1(name) values('Record_1');
23
INSERT into t1(name) values('Record_2');
28
## Test behavior of variable after updating value of variable ##
29
SET @@auto_increment_offset = 24;
30
SELECT @@auto_increment_offset;
31
@@auto_increment_offset
33
INSERT into t1(name) values('Record_3');
39
INSERT into t1(name) values('Record_4');
46
'#--------------------FN_DYNVARS_002_03-------------------------#'
47
## Changing value of global scope before opening new connection ##
48
SET @@global.auto_increment_increment = 15;
49
SET @@global.auto_increment_offset = 36;
50
## New connection test_con1 ##
51
## Value of session & global vairable here should be 10 ##
52
SELECT @@global.auto_increment_offset = 36;
53
@@global.auto_increment_offset = 36
55
SELECT @@session.auto_increment_offset = 36;
56
@@session.auto_increment_offset = 36
58
## Verify global value effect of variable by inserting new rows in table ##
59
INSERT into t1(name) values('Record_5');
60
INSERT into t1(name) values('Record_6');
69
## Setting session value of variable and inserting data in table ##
70
SET @@session.auto_increment_offset = 54;
71
INSERT into t1(name) values('Record_7');
72
INSERT into t1(name) values('Record_8');
83
'#--------------------FN_DYNVARS_002_04-------------------------#'
84
## Setting value of variable less than last insert id ##
85
SET @@session.auto_increment_offset = 5;
86
INSERT into t1(name) values('Record_9');
87
'Bug#35367: Random value of id is increasing on assigning value to';
88
'variable that is less than current offset';
89
INSERT into t1(name) values('Record_10');
90
INSERT into t1(name) values('Record_11');
91
INSERT into t1(name) values('Record_12');
106
'#--------------------FN_DYNVARS_002_05-------------------------#'
107
## Assigning value to variable greater than auto_increment_incrent value ##
108
SET @@auto_increment_offset = 140;
109
SET @@auto_increment_increment = 10;
110
INSERT into t1(name) values('Record_13');
111
INSERT into t1(name) values('Record_14');
128
'Bug#35369: Some invalid value of id is increasing on assigning value to';
129
'variable that is greater than auto_increment_increment';
130
'#--------------------FN_DYNVARS_002_06-------------------------#'
131
## Changing datatype of column id with primary key to SmallInt ##
132
ALTER table t1 modify id SMALLINT NOT NULL auto_increment;
133
INSERT into t1(name) values('Record_15');
134
INSERT into t1(name) values('Record_16');
153
## Changing datatype of column id with primary key to BigInt ##
154
ALTER table t1 modify id BIGINT NOT NULL auto_increment;
155
INSERT into t1(name) values('Record_17');
156
INSERT into t1(name) values('Record_18');
177
'#--------------------FN_DYNVARS_002_07-------------------------#'
178
## Assigning -ve value to variable ##
179
SET @@auto_increment_offset = -10;
181
Warning 1292 Truncated incorrect auto-increment-offset value: '0'
182
SELECT @@auto_increment_offset = -10;
183
@@auto_increment_offset = -10
185
INSERT into t1(name) values('Record_17');
186
INSERT into t1(name) values('Record_18');
209
'Bug#35370: Some invalid value of id is increasing on assigning negative';
210
' value in variable';
211
## Assigning value that is out of range of variable ##
212
SET @@auto_increment_offset = 65536;
214
Warning 1292 Truncated incorrect auto_increment_offset value: '65536'
215
SELECT @@auto_increment_offset;
216
@@auto_increment_offset
218
INSERT into t1(name) values('Record_17');
219
INSERT into t1(name) values('Record_18');
220
INSERT into t1(name) values('Record_19');
221
INSERT into t1(name) values('Record_20');
248
## No effect of auto_increment_offset since value of this variable is greater ##
249
## than auto_increment_increment ##
251
DROP table if exists t1;
252
## Disconnecting connection ##
253
## switching to default connection ##
254
SET @@global.auto_increment_increment = @global_auto_increment_increment;
255
SET @@session.auto_increment_increment = @session_auto_increment_increment;
256
SET @@global.auto_increment_offset = @global_auto_increment_offset;
257
SET @@session.auto_increment_offset = @session_auto_increment_offset;