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 57connect (test_con1, localhost, root,,); 58connection test_con1; 59SET @@autocommit = 0; 60 61--echo ## Inserting rows in table t1 ## 62INSERT into t1(name) values('Record_1'); 63INSERT into t1(name) values('Record_2'); 64INSERT into t1(name) values('Record_3'); 65 66--echo ## Verifying total values in t1 ## 67SELECT @@identity from t1; 68 69 70--echo ## Now inserting some data in table t2 ## 71INSERT into t2(name) values('Record_1'); 72 73--echo ## Verifying total values in t2 ## 74SELECT @@identity from t2; 75 76 77--echo '#--------------------FN_DYNVARS_035_02-------------------------#' 78########################################################## 79# Verifying value of identity with new connection # 80########################################################## 81 82connect (test_con2, localhost, root,,); 83connection test_con2; 84SELECT * from t1; 85 86--echo ## Verifying total values in t1 ## 87SELECT @@identity from t1; 88 89--echo ## Verifying total values in t2 ## 90SELECT @@identity from t2; 91 92--echo ## Inserting some more records in table t1 ## 93INSERT into t1(name) values('Record_1_1'); 94INSERT into t1(name) values('Record_1_2'); 95 96--echo ## Verifying total values in t1 ## 97SELECT @@identity from t1; 98 99--echo ## Inserting row in table t2 ## 100INSERT into t2(name) values('Record_1_3'); 101 102--echo ## Verifying total values in t2 ## 103SELECT @@identity from t2; 104 105 106--echo '#--------------------FN_DYNVARS_035_03-------------------------#' 107################################################################### 108# Verifying identity value by using commit in connectio # 01 # 109################################################################### 110 111connection test_con1; 112 113--echo ## Commiting rows added in test_con1 ## 114COMMIT; 115 116--echo ## Verifying records in both tables ## 117SELECT * from t1; 118SELECT * from t2; 119 120--echo ## Verifying total values in t1 after commiting data ## 121SELECT @@identity from t1; 122 123--echo ## Verifying total values in t2 after commiting data ## 124SELECT @@identity from t2; 125 126INSERT into t1(name) values('Record_4'); 127 128--echo ## Now verifying value of variable after inserting 1 row in this connection ## 129SELECT @@identity from t1; 130 131--echo ## Dropping tables t1 & t2 ## 132drop table t1, t2; 133 134disconnect test_con1; 135disconnect test_con2; 136