Debian Codegraph @

mysql-test/suite/sys_vars/t/identity_func.test

raw
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
25 drop table if exists t1;
26 drop table if exists t2;
27 --enable_warnings
28
29 #########################
30 # Creating new table #
31 #########################
32
33 --echo ## Creating new table t1 ##
34 CREATE TABLE t1
35 (
36 id INT NOT NULL auto_increment,
37 PRIMARY KEY (id),
38 name VARCHAR(30)
39 ) ENGINE = INNODB;
40
41 --echo ## Creating another new table t2 ##
42 CREATE TABLE t2
43 (
44 id INT NOT NULL auto_increment,
45 PRIMARY KEY (id),
46 name VARCHAR(30)
47 ) ENGINE = INNODB;
48
49 # MDEV-515 takes X-lock on the table for the first insert
50 # So concurrent insert won't happen on the table
51 INSERT INTO t1 VALUES(100, "MDEV-515");
52 INSERT INTO t2 VALUES(100, "MDEV-515");
53
54 --echo '#--------------------FN_DYNVARS_035_01-------------------------#'
55 ###############################################
56 # Verifying initial value of identity. #
57 ###############################################
58
59 --echo ## It should be zero ##
60 SELECT @@identity = 0;
61
62 connect (test_con1, localhost, root,,);
63 connection test_con1;
64 SET @@autocommit = 0;
65
66 --echo ## Inserting rows in table t1 ##
67 INSERT into t1(name) values('Record_1');
68 INSERT into t1(name) values('Record_2');
69 INSERT into t1(name) values('Record_3');
70
71 --echo ## Verifying total values in t1 ##
72 SELECT @@identity from t1;
73
74
75 --echo ## Now inserting some data in table t2 ##
76 INSERT into t2(name) values('Record_1');
77
78 --echo ## Verifying total values in t2 ##
79 SELECT @@identity from t2;
80
81
82 --echo '#--------------------FN_DYNVARS_035_02-------------------------#'
83 ##########################################################
84 # Verifying value of identity with new connection #
85 ##########################################################
86
87 connect (test_con2, localhost, root,,);
88 connection test_con2;
89 SELECT * from t1;
90
91 --echo ## Verifying total values in t1 ##
92 SELECT @@identity from t1;
93
94 --echo ## Verifying total values in t2 ##
95 SELECT @@identity from t2;
96
97 --echo ## Inserting some more records in table t1 ##
98 INSERT into t1(name) values('Record_1_1');
99 INSERT into t1(name) values('Record_1_2');
100
101 --echo ## Verifying total values in t1 ##
102 SELECT @@identity from t1;
103
104 --echo ## Inserting row in table t2 ##
105 INSERT into t2(name) values('Record_1_3');
106
107 --echo ## Verifying total values in t2 ##
108 SELECT @@identity from t2;
109
110
111 --echo '#--------------------FN_DYNVARS_035_03-------------------------#'
112 ###################################################################
113 # Verifying identity value by using commit in connectio # 01 #
114 ###################################################################
115
116 connection test_con1;
117
118 --echo ## Commiting rows added in test_con1 ##
119 COMMIT;
120
121 --echo ## Verifying records in both tables ##
122 SELECT * from t1;
123 SELECT * from t2;
124
125 --echo ## Verifying total values in t1 after commiting data ##
126 SELECT @@identity from t1;
127
128 --echo ## Verifying total values in t2 after commiting data ##
129 SELECT @@identity from t2;
130
131 INSERT into t1(name) values('Record_4');
132
133 --echo ## Now verifying value of variable after inserting 1 row in this connection ##
134 SELECT @@identity from t1;
135
136 --echo ## Dropping tables t1 & t2 ##
137 drop table t1, t2;
138
139 disconnect test_con1;
140 disconnect test_con2;