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 ## 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
45 1
46 SELECT @@session.auto_increment_increment = 10;
47 @@session.auto_increment_increment = 10
48 1
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
53 20
54 INSERT into t1(name) values('Record_6');
55 SELECT * from t1;
56 id name
57 1 Record_1
58 6 Record_2
59 101 Record_5
60 111 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
65 2
66 INSERT into t1(name) values('Record_8');
67 INSERT into t1(name) values('Record_9');
68 SELECT * from t1;
69 id name
70 1 Record_1
71 6 Record_2
72 101 Record_5
73 111 Record_6
74 113 Record_8
75 115 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
82 1
83 SELECT @@session.auto_increment_increment = 20;
84 @@session.auto_increment_increment = 20
85 1
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');
89 SELECT * from t1;
90 id name
91 1 Record_1
92 6 Record_2
93 101 Record_5
94 111 Record_6
95 113 Record_8
96 115 Record_9
97 116 Record_10
98 SET @@session.auto_increment_increment = 1;
99 SELECT @@auto_increment_increment;
100 @@auto_increment_increment
101 1
102 SELECT @@global.auto_increment_increment;
103 @@global.auto_increment_increment
104 20
105 '#--------------------FN_DYNVARS_001_05-------------------------#'
106 ## Switching to test_con1 ##
107 ## Verifying values of global & session value of variable ##
108 ## global value should be 20 ##
109 SELECT @@global.auto_increment_increment = 20;
110 @@global.auto_increment_increment = 20
111 1
112 ## session value should be 2 ##
113 SELECT @@session.auto_increment_increment = 2;
114 @@session.auto_increment_increment = 2
115 1
116 INSERT into t1(name) values('Record_11');
117 INSERT into t1(name) values('Record_12');
118 SELECT * from t1;
119 id name
120 1 Record_1
121 6 Record_2
122 101 Record_5
123 111 Record_6
124 113 Record_8
125 115 Record_9
126 116 Record_10
127 117 Record_11
128 119 Record_12
129 '#--------------------FN_DYNVARS_001_06-------------------------#'
130 ## Changing column's datatype to SmallInt and verifying variable's behavior ##
131 ALTER table t1 MODIFY id SMALLINT NOT NULL auto_increment;
132 INSERT into t1(name) values('Record_13');
133 INSERT into t1(name) values('Record_14');
134 SELECT * from t1;
135 id name
136 1 Record_1
137 6 Record_2
138 101 Record_5
139 111 Record_6
140 113 Record_8
141 115 Record_9
142 116 Record_10
143 117 Record_11
144 119 Record_12
145 121 Record_13
146 123 Record_14
147 ## Changing column's datatype to BigInt and verifying variable's behavior ##
148 ALTER table t1 MODIFY id BIGINT NOT NULL auto_increment;
149 INSERT into t1(name) values('Record_15');
150 INSERT into t1(name) values('Record_16');
151 SELECT * from t1;
152 id name
153 1 Record_1
154 6 Record_2
155 101 Record_5
156 111 Record_6
157 113 Record_8
158 115 Record_9
159 116 Record_10
160 117 Record_11
161 119 Record_12
162 121 Record_13
163 123 Record_14
164 125 Record_15
165 127 Record_16
166 '#--------------------FN_DYNVARS_001_07-------------------------#'
167 ## Verifying behavior of variable with negative value ##
168 SET @@auto_increment_increment = -10;
169 Warnings:
170 Warning 1292 Truncated incorrect auto_increment_increment value: '-10'
171 INSERT into t1(name) values('Record_17');
172 INSERT into t1(name) values('Record_18');
173 SELECT * from t1;
174 id name
175 1 Record_1
176 6 Record_2
177 101 Record_5
178 111 Record_6
179 113 Record_8
180 115 Record_9
181 116 Record_10
182 117 Record_11
183 119 Record_12
184 121 Record_13
185 123 Record_14
186 125 Record_15
187 127 Record_16
188 128 Record_17
189 129 Record_18
190 ## Disconnecting test_con2 ##
191 ## Dropping table t1 ##
192 DROP table if exists t1;
193 ## Disconnecting test_con1 ##
194 ## switching to default connection ##
195 SET @@global.auto_increment_increment = @global_auto_increment_increment;
196 SET @@session.auto_increment_increment = @session_auto_increment_increment;
197 SET @@global.auto_increment_offset = @global_auto_increment_offset;
198 SET @@session.auto_increment_offset = @session_auto_increment_offset;