1
############## mysql-test\t\auto_increment_increment_func.test ################
3
# Variable Name: auto_increment_increment #
4
# Scope: GLOBAL & SESSION #
5
# Access Type: Dynamic #
11
# Creation Date: 2008-03-07 #
12
# Author: Salman Rawala #
14
# Description: Test Cases of Dynamic System Variable "auto_increment_increment"#
15
# that checks functionality of this variable #
17
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
18
# server-system-variables.html#option_mysqld_auto-increment-increment #
20
################################################################################
23
SET @global_auto_increment_increment = @@global.auto_increment_increment;
24
SET @session_auto_increment_increment = @@session.auto_increment_increment;
25
SET @global_auto_increment_offset = @@global.auto_increment_offset;
26
SET @session_auto_increment_offset = @@session.auto_increment_offset;
30
drop table if exists t1;
33
#########################
34
# Creating new table #
35
#########################
38
id INT NOT NULL auto_increment,
43
--echo '#--------------------FN_DYNVARS_001_01-------------------------#'
44
##########################################################
45
# Setting initial value of auto_increment_increment #
46
##########################################################
48
--echo ## Setting initial value of auto_increment_increment to 5 ##
49
SET @@auto_increment_increment = 5;
52
--echo '#--------------------FN_DYNVARS_001_02-------------------------#'
53
###########################################################################
54
# Inserting first value in table to check auto_increment_increment initial
56
###########################################################################
58
--echo ## Inserting first record in table to check behavior of the variable ##
59
INSERT into t1(name) values('Record_1');
62
--echo ## Changing value of variable to 10 ##
63
SET @@global.auto_increment_increment = 10;
65
--echo ## Inserting record and verifying value of column id ##
66
INSERT into t1(name) values('Record_2');
70
--echo ## Test behavior of variable after assigning some larger value to it ##
71
SELECT @@auto_increment_increment;
72
SET @@auto_increment_increment = 100;
73
INSERT into t1(name) values('Record_5');
77
--echo '#--------------------FN_DYNVARS_001_03-------------------------#'
78
##########################################################
79
# Test behavior of variable on new connection # 01 #
80
##########################################################
82
--echo ## Creating new connection test_con1 ##
83
CONNECT (test_con1,localhost,root,,);
86
--echo ## Value of session & global vairable here should be 10 ##
87
SELECT @@global.auto_increment_increment = 10;
88
SELECT @@session.auto_increment_increment = 10;
90
--echo ## Setting global value of variable and inserting data in table ##
91
SET @@global.auto_increment_increment = 20;
92
SELECT @@global.auto_increment_increment;
93
INSERT into t1(name) values('Record_6');
96
--echo ## Setting session value of variable and inserting data in table ##
97
SET @@session.auto_increment_increment = 2;
98
SELECT @@session.auto_increment_increment;
99
INSERT into t1(name) values('Record_8');
100
INSERT into t1(name) values('Record_9');
104
--echo '#--------------------FN_DYNVARS_001_04-------------------------#'
105
##########################################################
106
# Test behavior of variable on new connection # 02 #
107
##########################################################
109
--echo ## Creating another new connection test_con2 ##
110
CONNECT (test_con2,localhost,root,,);
111
connection test_con2;
113
--echo ## Verifying initial values of variable in global & session scope ##
114
--echo ## global & session initial value should be 20 ##
115
SELECT @@global.auto_increment_increment = 20;
116
SELECT @@session.auto_increment_increment = 20;
118
--echo ## Setting value of session variable to 5 and verifying its behavior ##
119
SET @@session.auto_increment_increment = 5;
120
INSERT into t1(name) values('Record_10');
122
--echo 'Bug#35362: Here Record_10 id should be 120 instead of 115 because we'
123
--echo 'have set the value of variable to 5'
125
SET @@session.auto_increment_increment = 1;
126
SELECT @@auto_increment_increment;
127
SELECT @@global.auto_increment_increment;
130
--echo '#--------------------FN_DYNVARS_001_05-------------------------#'
131
#####################################################################
132
# Verify variable's value of connection # 01 after processing on
134
#####################################################################
136
--echo ## Switching to test_con1 ##
137
connection test_con1;
139
--echo ## Verifying values of global & session value of variable ##
140
--echo ## global value should be 20 ##
141
SELECT @@global.auto_increment_increment = 20;
143
--echo ## session value should be 2 ##
144
SELECT @@session.auto_increment_increment = 2;
146
INSERT into t1(name) values('Record_11');
147
INSERT into t1(name) values('Record_12');
151
--echo '#--------------------FN_DYNVARS_001_06-------------------------#'
152
###############################################################################
153
# Altering table field to different datatypes and checking their behavior #
154
###############################################################################
156
--echo ## Changing column's datatype to SmallInt and verifying variable's behavior ##
157
ALTER table t1 MODIFY id SMALLINT NOT NULL auto_increment;
158
INSERT into t1(name) values('Record_13');
159
INSERT into t1(name) values('Record_14');
162
--echo ## Changing column's datatype to BigInt and verifying variable's behavior ##
163
ALTER table t1 MODIFY id BIGINT NOT NULL auto_increment;
164
INSERT into t1(name) values('Record_15');
165
INSERT into t1(name) values('Record_16');
168
--echo '#--------------------FN_DYNVARS_001_07-------------------------#'
169
###############################################################################
170
# Check behavior of variable after assigning invalid value #
171
###############################################################################
173
--echo ## Verifying behavior of variable with negative value ##
174
SET @@auto_increment_increment = -10;
175
INSERT into t1(name) values('Record_17');
176
INSERT into t1(name) values('Record_18');
179
--echo 'Bug#35364: Variable is incrementing some random values on assigning -ve value'
182
############################################################
183
# Disconnecting all connection & dropping table #
184
############################################################
186
--echo ## Disconnecting test_con2 ##
187
DISCONNECT test_con2;
189
--echo ## Dropping table t1 ##
190
DROP table if exists t1;
192
--echo ## Disconnecting test_con1 ##
193
DISCONNECT test_con1;
195
--echo ## switching to default connection ##
199
SET @@global.auto_increment_increment = @global_auto_increment_increment;
200
SET @@session.auto_increment_increment = @session_auto_increment_increment;
201
SET @@global.auto_increment_offset = @global_auto_increment_offset;
202
SET @@session.auto_increment_offset = @session_auto_increment_offset;