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
|
#################### 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 #
# #
###############################################################################
--source include/have_innodb.inc
--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;
# MDEV-515 takes X-lock on the table for the first insert
# So concurrent insert won't happen on the table
INSERT INTO t1 VALUES(100, "MDEV-515");
INSERT INTO t2 VALUES(100, "MDEV-515");
--echo '#--------------------FN_DYNVARS_035_01-------------------------#'
###############################################
# Verifying initial value of identity. #
###############################################
--echo ## It should be zero ##
SELECT @@identity = 0;
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 #
##########################################################
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 #
###################################################################
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;
disconnect test_con1;
disconnect test_con2;
|
