Edit File: auto_increment_offset_func.result
SET @global_auto_increment_increment = @@global.auto_increment_increment; SET @session_auto_increment_increment = @@session.auto_increment_increment; SET @global_auto_increment_offset = @@global.auto_increment_offset; SET @session_auto_increment_offset = @@session.auto_increment_offset; drop table if exists t1; ## Creating New Table ## CREATE TABLE t1 ( id INT NOT NULL auto_increment, PRIMARY KEY (id), name VARCHAR(30) ); '#--------------------FN_DYNVARS_002_01-------------------------#' ## Setting initial value of variable to 5 ## SET @@auto_increment_increment = 10; SET @@auto_increment_offset = 5; '#--------------------FN_DYNVARS_002_02-------------------------#' ## Inserting records in table and verifying variable's behavior ## INSERT into t1(name) values('Record_1'); SELECT * from t1; id name 5 Record_1 INSERT into t1(name) values('Record_2'); SELECT * from t1; id name 5 Record_1 15 Record_2 ## Test behavior of variable after updating value of variable ## SET @@auto_increment_offset = 24; SELECT @@auto_increment_offset; @@auto_increment_offset 24 INSERT into t1(name) values('Record_3'); SELECT * from t1; id name 5 Record_1 15 Record_2 24 Record_3 INSERT into t1(name) values('Record_4'); SELECT * from t1; id name 5 Record_1 15 Record_2 24 Record_3 34 Record_4 '#--------------------FN_DYNVARS_002_03-------------------------#' ## Changing value of global scope before opening new connection ## SET @@global.auto_increment_increment = 15; SET @@global.auto_increment_offset = 36; ## New connection test_con1 ## ## Value of session & global vairable here should be 10 ## SELECT @@global.auto_increment_offset = 36; @@global.auto_increment_offset = 36 1 SELECT @@session.auto_increment_offset = 36; @@session.auto_increment_offset = 36 1 ## Verify global value effect of variable by inserting new rows in table ## INSERT into t1(name) values('Record_5'); INSERT into t1(name) values('Record_6'); SELECT * from t1; id name 5 Record_1 15 Record_2 24 Record_3 34 Record_4 36 Record_5 51 Record_6 ## Setting session value of variable and inserting data in table ## SET @@session.auto_increment_offset = 54; INSERT into t1(name) values('Record_7'); INSERT into t1(name) values('Record_8'); SELECT * from t1; id name 5 Record_1 15 Record_2 24 Record_3 34 Record_4 36 Record_5 51 Record_6 54 Record_7 69 Record_8 '#--------------------FN_DYNVARS_002_04-------------------------#' ## Setting value of variable less than last insert id ## SET @@session.auto_increment_offset = 5; INSERT into t1(name) values('Record_9'); INSERT into t1(name) values('Record_10'); INSERT into t1(name) values('Record_11'); INSERT into t1(name) values('Record_12'); SELECT * from t1; id name 5 Record_1 15 Record_2 24 Record_3 34 Record_4 36 Record_5 51 Record_6 54 Record_7 69 Record_8 80 Record_9 95 Record_10 110 Record_11 125 Record_12 '#--------------------FN_DYNVARS_002_05-------------------------#' ## Assigning value to variable greater than auto_increment_incrent value ## SET @@auto_increment_offset = 140; SET @@auto_increment_increment = 10; INSERT into t1(name) values('Record_13'); INSERT into t1(name) values('Record_14'); SELECT * from t1; id name 5 Record_1 15 Record_2 24 Record_3 34 Record_4 36 Record_5 51 Record_6 54 Record_7 69 Record_8 80 Record_9 95 Record_10 110 Record_11 125 Record_12 134 Record_13 140 Record_14 '#--------------------FN_DYNVARS_002_06-------------------------#' ## Changing datatype of column id with primary key to SmallInt ## ALTER table t1 modify id SMALLINT NOT NULL auto_increment; INSERT into t1(name) values('Record_15'); INSERT into t1(name) values('Record_16'); SELECT * from t1; id name 5 Record_1 15 Record_2 24 Record_3 34 Record_4 36 Record_5 51 Record_6 54 Record_7 69 Record_8 80 Record_9 95 Record_10 110 Record_11 125 Record_12 134 Record_13 140 Record_14 150 Record_15 160 Record_16 ## Changing datatype of column id with primary key to BigInt ## ALTER table t1 modify id BIGINT NOT NULL auto_increment; INSERT into t1(name) values('Record_17'); INSERT into t1(name) values('Record_18'); SELECT * from t1; id name 5 Record_1 15 Record_2 24 Record_3 34 Record_4 36 Record_5 51 Record_6 54 Record_7 69 Record_8 80 Record_9 95 Record_10 110 Record_11 125 Record_12 134 Record_13 140 Record_14 150 Record_15 160 Record_16 170 Record_17 180 Record_18 '#--------------------FN_DYNVARS_002_07-------------------------#' ## Assigning -ve value to variable ## SET @@auto_increment_offset = -10; Warnings: Warning 1292 Truncated incorrect auto_increment_offset value: '-10' SELECT @@auto_increment_offset = -10; @@auto_increment_offset = -10 0 INSERT into t1(name) values('Record_17'); INSERT into t1(name) values('Record_18'); SELECT * from t1; id name 5 Record_1 15 Record_2 24 Record_3 34 Record_4 36 Record_5 51 Record_6 54 Record_7 69 Record_8 80 Record_9 95 Record_10 110 Record_11 125 Record_12 134 Record_13 140 Record_14 150 Record_15 160 Record_16 170 Record_17 180 Record_18 181 Record_17 191 Record_18 ## Assigning value that is out of range of variable ## SET @@auto_increment_offset = 65536; Warnings: Warning 1292 Truncated incorrect auto_increment_offset value: '65536' SELECT @@auto_increment_offset; @@auto_increment_offset 65535 INSERT into t1(name) values('Record_17'); INSERT into t1(name) values('Record_18'); INSERT into t1(name) values('Record_19'); INSERT into t1(name) values('Record_20'); SELECT * from t1; id name 5 Record_1 15 Record_2 24 Record_3 34 Record_4 36 Record_5 51 Record_6 54 Record_7 69 Record_8 80 Record_9 95 Record_10 110 Record_11 125 Record_12 134 Record_13 140 Record_14 150 Record_15 160 Record_16 170 Record_17 180 Record_18 181 Record_17 191 Record_18 199 Record_17 209 Record_18 219 Record_19 229 Record_20 ## No effect of auto_increment_offset since value of this variable is greater ## ## than auto_increment_increment ## ## Dropping table ## DROP table if exists t1; ## Disconnecting connection ## ## switching to default connection ## SET @@global.auto_increment_increment = @global_auto_increment_increment; SET @@session.auto_increment_increment = @session_auto_increment_increment; SET @@global.auto_increment_offset = @global_auto_increment_offset; SET @@session.auto_increment_offset = @session_auto_increment_offset;