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