1#################### mysql-test\t\identity_func.test ########################## 2# # 3# Variable Name: identity # 4# Scope: SESSION # 5# Access Type: Dynamic # 6# Data Type: numeric # 7# Default Value: - # 8# Range: - # 9# # 10# # 11# Creation Date: 2008-03-07 # 12# Author: Salman Rawala # 13# # 14# Description: Test Cases of Dynamic System Variable identity # 15# that checks the functionality of this variable # 16# # 17# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # 18# server-system-variables.html # 19# # 20############################################################################### 21 22--source include/have_innodb.inc 23 24--disable_warnings 25drop table if exists t1; 26drop table if exists t2; 27--enable_warnings 28 29######################### 30# Creating new table # 31######################### 32 33--echo ## Creating new table t1 ## 34CREATE TABLE t1 35( 36id INT NOT NULL auto_increment, 37PRIMARY KEY (id), 38name VARCHAR(30) 39) ENGINE = INNODB; 40 41--echo ## Creating another new table t2 ## 42CREATE TABLE t2 43( 44id INT NOT NULL auto_increment, 45PRIMARY KEY (id), 46name VARCHAR(30) 47) ENGINE = INNODB; 48 49--echo '#--------------------FN_DYNVARS_035_01-------------------------#' 50############################################### 51# Verifying initial value of identity. # 52############################################### 53 54--echo ## It should be zero ## 55SELECT @@identity = 0; 56 57--echo ## Creating and connecting with new connection test_con1 ## 58connect (test_con1, localhost, root,,); 59connection test_con1; 60SET @@autocommit = 0; 61 62--echo ## Inserting rows in table t1 ## 63INSERT into t1(name) values('Record_1'); 64INSERT into t1(name) values('Record_2'); 65INSERT into t1(name) values('Record_3'); 66 67--echo ## Verifying total values in t1 ## 68SELECT @@identity from t1; 69 70 71--echo ## Now inserting some data in table t2 ## 72INSERT into t2(name) values('Record_1'); 73 74--echo ## Verifying total values in t2 ## 75SELECT @@identity from t2; 76 77 78--echo '#--------------------FN_DYNVARS_035_02-------------------------#' 79########################################################## 80# Verifying value of identity with new connection # 81########################################################## 82 83--echo ## Creating and connecting with new connection test_con2 ## 84connect (test_con2, localhost, root,,); 85connection test_con2; 86SELECT * from t1; 87 88--echo ## Verifying total values in t1 ## 89SELECT @@identity from t1; 90 91--echo ## Verifying total values in t2 ## 92SELECT @@identity from t2; 93 94--echo ## Inserting some more records in table t1 ## 95INSERT into t1(name) values('Record_1_1'); 96INSERT into t1(name) values('Record_1_2'); 97 98--echo ## Verifying total values in t1 ## 99SELECT @@identity from t1; 100 101--echo ## Inserting row in table t2 ## 102INSERT into t2(name) values('Record_1_3'); 103 104--echo ## Verifying total values in t2 ## 105SELECT @@identity from t2; 106 107 108--echo '#--------------------FN_DYNVARS_035_03-------------------------#' 109################################################################### 110# Verifying identity value by using commit in connectio # 01 # 111################################################################### 112 113--echo ## Switching to connection test_con1 ## 114connection test_con1; 115 116--echo ## Commiting rows added in test_con1 ## 117COMMIT; 118 119--echo ## Verifying records in both tables ## 120SELECT * from t1; 121SELECT * from t2; 122 123--echo ## Verifying total values in t1 after commiting data ## 124SELECT @@identity from t1; 125 126--echo ## Verifying total values in t2 after commiting data ## 127SELECT @@identity from t2; 128 129INSERT into t1(name) values('Record_4'); 130 131--echo ## Now verifying value of variable after inserting 1 row in this connection ## 132SELECT @@identity from t1; 133 134--echo ## Dropping tables t1 & t2 ## 135drop table t1, t2; 136 137--echo ## Disconnecting both the connections ## 138disconnect test_con1; 139disconnect test_con2; 140 141 142 143 144 145