1SET @global_auto_increment_increment = @@global.auto_increment_increment; 2SET @session_auto_increment_increment = @@session.auto_increment_increment; 3SET @global_auto_increment_offset = @@global.auto_increment_offset; 4SET @session_auto_increment_offset = @@session.auto_increment_offset; 5drop table if exists t1; 6## Creating New Table ## 7CREATE TABLE t1 8( 9id INT NOT NULL auto_increment, 10PRIMARY KEY (id), 11name VARCHAR(30) 12); 13'#--------------------FN_DYNVARS_002_01-------------------------#' 14## Setting initial value of variable to 5 ## 15SET @@auto_increment_increment = 10; 16SET @@auto_increment_offset = 5; 17'#--------------------FN_DYNVARS_002_02-------------------------#' 18## Inserting records in table and verifying variable's behavior ## 19INSERT into t1(name) values('Record_1'); 20SELECT * from t1; 21id name 225 Record_1 23INSERT into t1(name) values('Record_2'); 24SELECT * from t1; 25id name 265 Record_1 2715 Record_2 28## Test behavior of variable after updating value of variable ## 29SET @@auto_increment_offset = 24; 30SELECT @@auto_increment_offset; 31@@auto_increment_offset 3224 33INSERT into t1(name) values('Record_3'); 34SELECT * from t1; 35id name 365 Record_1 3715 Record_2 3824 Record_3 39INSERT into t1(name) values('Record_4'); 40SELECT * from t1; 41id name 425 Record_1 4315 Record_2 4424 Record_3 4534 Record_4 46'#--------------------FN_DYNVARS_002_03-------------------------#' 47## Changing value of global scope before opening new connection ## 48SET @@global.auto_increment_increment = 15; 49SET @@global.auto_increment_offset = 36; 50CONNECT test_con1,localhost,root,,; 51connection test_con1; 52## Value of session & global vairable here should be 10 ## 53SELECT @@global.auto_increment_offset = 36; 54@@global.auto_increment_offset = 36 551 56SELECT @@session.auto_increment_offset = 36; 57@@session.auto_increment_offset = 36 581 59## Verify global value effect of variable by inserting new rows in table ## 60INSERT into t1(name) values('Record_5'); 61INSERT into t1(name) values('Record_6'); 62SELECT * from t1; 63id name 645 Record_1 6515 Record_2 6624 Record_3 6734 Record_4 6836 Record_5 6951 Record_6 70## Setting session value of variable and inserting data in table ## 71SET @@session.auto_increment_offset = 54; 72INSERT into t1(name) values('Record_7'); 73INSERT into t1(name) values('Record_8'); 74SELECT * from t1; 75id name 765 Record_1 7715 Record_2 7824 Record_3 7934 Record_4 8036 Record_5 8151 Record_6 8254 Record_7 8369 Record_8 84'#--------------------FN_DYNVARS_002_04-------------------------#' 85## Setting value of variable less than last insert id ## 86SET @@session.auto_increment_offset = 5; 87INSERT into t1(name) values('Record_9'); 88INSERT into t1(name) values('Record_10'); 89INSERT into t1(name) values('Record_11'); 90INSERT into t1(name) values('Record_12'); 91SELECT * from t1; 92id name 935 Record_1 9415 Record_2 9524 Record_3 9634 Record_4 9736 Record_5 9851 Record_6 9954 Record_7 10069 Record_8 10180 Record_9 10295 Record_10 103110 Record_11 104125 Record_12 105'#--------------------FN_DYNVARS_002_05-------------------------#' 106## Assigning value to variable greater than auto_increment_incrent value ## 107SET @@auto_increment_offset = 140; 108SET @@auto_increment_increment = 10; 109INSERT into t1(name) values('Record_13'); 110INSERT into t1(name) values('Record_14'); 111SELECT * from t1; 112id name 1135 Record_1 11415 Record_2 11524 Record_3 11634 Record_4 11736 Record_5 11851 Record_6 11954 Record_7 12069 Record_8 12180 Record_9 12295 Record_10 123110 Record_11 124125 Record_12 125134 Record_13 126140 Record_14 127'#--------------------FN_DYNVARS_002_06-------------------------#' 128## Changing datatype of column id with primary key to SmallInt ## 129ALTER table t1 modify id SMALLINT NOT NULL auto_increment; 130INSERT into t1(name) values('Record_15'); 131INSERT into t1(name) values('Record_16'); 132SELECT * from t1; 133id name 1345 Record_1 13515 Record_2 13624 Record_3 13734 Record_4 13836 Record_5 13951 Record_6 14054 Record_7 14169 Record_8 14280 Record_9 14395 Record_10 144110 Record_11 145125 Record_12 146134 Record_13 147140 Record_14 148150 Record_15 149160 Record_16 150## Changing datatype of column id with primary key to BigInt ## 151ALTER table t1 modify id BIGINT NOT NULL auto_increment; 152INSERT into t1(name) values('Record_17'); 153INSERT into t1(name) values('Record_18'); 154SELECT * from t1; 155id name 1565 Record_1 15715 Record_2 15824 Record_3 15934 Record_4 16036 Record_5 16151 Record_6 16254 Record_7 16369 Record_8 16480 Record_9 16595 Record_10 166110 Record_11 167125 Record_12 168134 Record_13 169140 Record_14 170150 Record_15 171160 Record_16 172170 Record_17 173180 Record_18 174'#--------------------FN_DYNVARS_002_07-------------------------#' 175## Assigning -ve value to variable ## 176SET @@auto_increment_offset = -10; 177Warnings: 178Warning 1292 Truncated incorrect auto_increment_offset value: '-10' 179SELECT @@auto_increment_offset = -10; 180@@auto_increment_offset = -10 1810 182INSERT into t1(name) values('Record_17'); 183INSERT into t1(name) values('Record_18'); 184SELECT * from t1; 185id name 1865 Record_1 18715 Record_2 18824 Record_3 18934 Record_4 19036 Record_5 19151 Record_6 19254 Record_7 19369 Record_8 19480 Record_9 19595 Record_10 196110 Record_11 197125 Record_12 198134 Record_13 199140 Record_14 200150 Record_15 201160 Record_16 202170 Record_17 203180 Record_18 204181 Record_17 205191 Record_18 206## Assigning value that is out of range of variable ## 207SET @@auto_increment_offset = 65536; 208Warnings: 209Warning 1292 Truncated incorrect auto_increment_offset value: '65536' 210SELECT @@auto_increment_offset; 211@@auto_increment_offset 21265535 213INSERT into t1(name) values('Record_17'); 214INSERT into t1(name) values('Record_18'); 215INSERT into t1(name) values('Record_19'); 216INSERT into t1(name) values('Record_20'); 217SELECT * from t1; 218id name 2195 Record_1 22015 Record_2 22124 Record_3 22234 Record_4 22336 Record_5 22451 Record_6 22554 Record_7 22669 Record_8 22780 Record_9 22895 Record_10 229110 Record_11 230125 Record_12 231134 Record_13 232140 Record_14 233150 Record_15 234160 Record_16 235170 Record_17 236180 Record_18 237181 Record_17 238191 Record_18 239199 Record_17 240209 Record_18 241219 Record_19 242229 Record_20 243## No effect of auto_increment_offset since value of this variable is greater ## 244## than auto_increment_increment ## 245## Dropping table ## 246DROP table if exists t1; 247disconnect test_con1; 248connection default; 249SET @@global.auto_increment_increment = @global_auto_increment_increment; 250SET @@session.auto_increment_increment = @session_auto_increment_increment; 251SET @@global.auto_increment_offset = @global_auto_increment_offset; 252SET @@session.auto_increment_offset = @session_auto_increment_offset; 253