Debian Codegraph @

mysql-test/suite/sys_vars/r/auto_increment_increment_func.result

raw
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 CREATE TABLE t1
7 (
8 id INT NOT NULL auto_increment,
9 PRIMARY KEY (id),
10 name VARCHAR(30)
11 );
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');
18 SELECT * from t1;
19 id name
20 1 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');
25 SELECT * from t1;
26 id name
27 1 Record_1
28 6 Record_2
29 ## Test behavior of variable after assigning some larger value to it ##
30 SELECT @@auto_increment_increment;
31 @@auto_increment_increment
32 5
33 SET @@auto_increment_increment = 100;
34 INSERT into t1(name) values('Record_5');
35 SELECT * from t1;
36 id name
37 1 Record_1
38 6 Record_2
39 101 Record_5
40 '#--------------------FN_DYNVARS_001_03-------------------------#'
41 CONNECT test_con1,localhost,root,,;
42 connection test_con1;
43 ## Value of session & global vairable here should be 10 ##
44 SELECT @@global.auto_increment_increment = 10;
45 @@global.auto_increment_increment = 10
46 1
47 SELECT @@session.auto_increment_increment = 10;
48 @@session.auto_increment_increment = 10
49 1
50 ## Setting global value of variable and inserting data in table ##
51 SET @@global.auto_increment_increment = 20;
52 SELECT @@global.auto_increment_increment;
53 @@global.auto_increment_increment
54 20
55 INSERT into t1(name) values('Record_6');
56 SELECT * from t1;
57 id name
58 1 Record_1
59 6 Record_2
60 101 Record_5
61 111 Record_6
62 ## Setting session value of variable and inserting data in table ##
63 SET @@session.auto_increment_increment = 2;
64 SELECT @@session.auto_increment_increment;
65 @@session.auto_increment_increment
66 2
67 INSERT into t1(name) values('Record_8');
68 INSERT into t1(name) values('Record_9');
69 SELECT * from t1;
70 id name
71 1 Record_1
72 6 Record_2
73 101 Record_5
74 111 Record_6
75 113 Record_8
76 115 Record_9
77 '#--------------------FN_DYNVARS_001_04-------------------------#'
78 CONNECT test_con2,localhost,root,,;
79 connection test_con2;
80 ## Verifying initial values of variable in global & session scope ##
81 ## global & session initial value should be 20 ##
82 SELECT @@global.auto_increment_increment = 20;
83 @@global.auto_increment_increment = 20
84 1
85 SELECT @@session.auto_increment_increment = 20;
86 @@session.auto_increment_increment = 20
87 1
88 ## Setting value of session variable to 5 and verifying its behavior ##
89 SET @@session.auto_increment_increment = 5;
90 INSERT into t1(name) values('Record_10');
91 SELECT * from t1;
92 id name
93 1 Record_1
94 6 Record_2
95 101 Record_5
96 111 Record_6
97 113 Record_8
98 115 Record_9
99 116 Record_10
100 SET @@session.auto_increment_increment = 1;
101 SELECT @@auto_increment_increment;
102 @@auto_increment_increment
103 1
104 SELECT @@global.auto_increment_increment;
105 @@global.auto_increment_increment
106 20
107 '#--------------------FN_DYNVARS_001_05-------------------------#'
108 connection 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
113 1
114 ## session value should be 2 ##
115 SELECT @@session.auto_increment_increment = 2;
116 @@session.auto_increment_increment = 2
117 1
118 INSERT into t1(name) values('Record_11');
119 INSERT into t1(name) values('Record_12');
120 SELECT * from t1;
121 id name
122 1 Record_1
123 6 Record_2
124 101 Record_5
125 111 Record_6
126 113 Record_8
127 115 Record_9
128 116 Record_10
129 117 Record_11
130 119 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');
136 SELECT * from t1;
137 id name
138 1 Record_1
139 6 Record_2
140 101 Record_5
141 111 Record_6
142 113 Record_8
143 115 Record_9
144 116 Record_10
145 117 Record_11
146 119 Record_12
147 121 Record_13
148 123 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');
153 SELECT * from t1;
154 id name
155 1 Record_1
156 6 Record_2
157 101 Record_5
158 111 Record_6
159 113 Record_8
160 115 Record_9
161 116 Record_10
162 117 Record_11
163 119 Record_12
164 121 Record_13
165 123 Record_14
166 125 Record_15
167 127 Record_16
168 '#--------------------FN_DYNVARS_001_07-------------------------#'
169 ## Verifying behavior of variable with negative value ##
170 SET @@auto_increment_increment = -10;
171 Warnings:
172 Warning 1292 Truncated incorrect auto_increment_increment value: '-10'
173 INSERT into t1(name) values('Record_17');
174 INSERT into t1(name) values('Record_18');
175 SELECT * from t1;
176 id name
177 1 Record_1
178 6 Record_2
179 101 Record_5
180 111 Record_6
181 113 Record_8
182 115 Record_9
183 116 Record_10
184 117 Record_11
185 119 Record_12
186 121 Record_13
187 123 Record_14
188 125 Record_15
189 127 Record_16
190 128 Record_17
191 129 Record_18
192 disconnect test_con2;
193 ## Dropping table t1 ##
194 DROP table if exists t1;
195 disconnect test_con1;
196 connection default;
197 SET @@global.auto_increment_increment = @global_auto_increment_increment;
198 SET @@session.auto_increment_increment = @session_auto_increment_increment;
199 SET @@global.auto_increment_offset = @global_auto_increment_offset;
200 SET @@session.auto_increment_offset = @session_auto_increment_offset;