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