1
#################### mysql-test\t\identity_func.test ##########################
3
# Variable Name: identity #
5
# Access Type: Dynamic #
11
# Creation Date: 2008-03-07 #
12
# Author: Salman Rawala #
14
# Description: Test Cases of Dynamic System Variable identity #
15
# that checks the functionality of this variable #
17
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
18
# server-system-variables.html #
20
###############################################################################
22
--source include/have_innodb.inc
25
drop table if exists t1;
26
drop table if exists t2;
29
#########################
30
# Creating new table #
31
#########################
33
--echo ## Creating new table t1 ##
36
id INT NOT NULL auto_increment,
41
--echo ## Creating another new table t2 ##
44
id INT NOT NULL auto_increment,
49
--echo '#--------------------FN_DYNVARS_035_01-------------------------#'
50
###############################################
51
# Verifying initial value of identity. #
52
###############################################
54
--echo ## It should be zero ##
55
SELECT @@identity = 0;
57
--echo ## Creating and connecting with new connection test_con1 ##
58
connect (test_con1, localhost, root,,);
62
--echo ## Inserting rows in table t1 ##
63
INSERT into t1(name) values('Record_1');
64
INSERT into t1(name) values('Record_2');
65
INSERT into t1(name) values('Record_3');
67
--echo ## Verifying total values in t1 ##
68
SELECT @@identity from t1;
71
--echo ## Now inserting some data in table t2 ##
72
INSERT into t2(name) values('Record_1');
74
--echo ## Verifying total values in t2 ##
75
SELECT @@identity from t2;
78
--echo '#--------------------FN_DYNVARS_035_02-------------------------#'
79
##########################################################
80
# Verifying value of identity with new connection #
81
##########################################################
83
--echo ## Creating and connecting with new connection test_con2 ##
84
connect (test_con2, localhost, root,,);
88
--echo ## Verifying total values in t1 ##
89
SELECT @@identity from t1;
91
--echo ## Verifying total values in t2 ##
92
SELECT @@identity from t2;
94
--echo ## Inserting some more records in table t1 ##
95
INSERT into t1(name) values('Record_1_1');
96
INSERT into t1(name) values('Record_1_2');
98
--echo ## Verifying total values in t1 ##
99
SELECT @@identity from t1;
101
--echo ## Inserting row in table t2 ##
102
INSERT into t2(name) values('Record_1_3');
104
--echo ## Verifying total values in t2 ##
105
SELECT @@identity from t2;
108
--echo '#--------------------FN_DYNVARS_035_03-------------------------#'
109
###################################################################
110
# Verifying identity value by using commit in connectio # 01 #
111
###################################################################
113
--echo ## Switching to connection test_con1 ##
114
connection test_con1;
116
--echo ## Commiting rows added in test_con1 ##
119
--echo ## Verifying records in both tables ##
123
--echo ## Verifying total values in t1 after commiting data ##
124
SELECT @@identity from t1;
126
--echo ## Verifying total values in t2 after commiting data ##
127
SELECT @@identity from t2;
129
INSERT into t1(name) values('Record_4');
131
--echo ## Now verifying value of variable after inserting 1 row in this connection ##
132
SELECT @@identity from t1;
134
--echo ## Dropping tables t1 & t2 ##
137
--echo ## Disconnecting both the connections ##
138
disconnect test_con1;
139
disconnect test_con2;