Debian Codegraph @

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

raw
1 ################# mysql-test\t\last_insert_id_func.test #######################
2 # #
3 # Variable Name: last_insert_id #
4 # Scope: GLOBAL #
5 # Access Type: Dynamic #
6 # Data Type: numeric #
7 # Default Value: - #
8 # Range: - #
9 # #
10 # #
11 # Creation Date: 2008-03-07 #
12 # Author: Salman #
13 # #
14 # Description: Test Cases of Dynamic System Variable key_buffer_size #
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
23 --disable_warnings
24 drop table if exists t1;
25 --enable_warnings
26
27 #########################
28 # Creating new table #
29 #########################
30
31 --echo ## Creating new table ##
32 CREATE TABLE t1
33 (
34 id INT NOT NULL auto_increment,
35 PRIMARY KEY (id),
36 name VARCHAR(30)
37 ) ENGINE = INNODB;
38
39 --echo '#--------------------FN_DYNVARS_059_01-------------------------#'
40 #######################################################################
41 # Setting initial value of last_insert_id and verifying its behavior #
42 #######################################################################
43
44 --echo ## Verifying initial value of ##
45 SELECT @@session.last_insert_id;
46
47 --echo ## Inserting records in table t1 ##
48 INSERT into t1(name) values('Record_1');
49 INSERT into t1(name) values('Record_2');
50 SELECT * from t1;
51
52 --echo ## Verifying value of variable after inserting some rows ##
53 SELECT @@session.last_insert_id = 2;
54
55
56 --echo '#--------------------FN_DYNVARS_059_02-------------------------#'
57 #################################################################################
58 # Verifying value of last_insert_id with new connection without committing rows #
59 #################################################################################
60
61 --echo ## Creating & connecting to new connection test_con1 ##
62 connect (test_con1, localhost, root,,);
63 connection test_con1;
64
65 SET @@autocommit = 0;
66
67 --echo ## Verifying initial value of variable in new connection ##
68 SELECT @@session.last_insert_id;
69
70 --echo ## Inserting rows in table t1 ##
71 START TRANSACTION;
72 INSERT into t1(name) values('Record_3');
73 INSERT into t1(name) values('Record_4');
74 INSERT into t1(name) values('Record_5');
75
76 --echo ## Verifying value of variable without committing rows ##
77 SELECT @@session.last_insert_id;
78
79 --echo '#--------------------FN_DYNVARS_059_03-------------------------#'
80 #########################################################################
81 # Now verifying value of last_insert_id with second new connection #
82 #########################################################################
83
84 --echo ## Creating & connecting to new connection test_con2 ##
85 connect (test_con2, localhost, root,,);
86 connection test_con2;
87
88 --echo ## Inserting values through new connection ##
89 INSERT into t1(name) values('Record_6');
90 INSERT into t1(name) values('Record_7');
91
92 SELECT * from t1;
93
94 --echo ## Verifying value of variable in second connection ##
95 SELECT @@last_insert_id;
96
97
98
99 --echo '#--------------------FN_DYNVARS_059_04-------------------------#'
100 ############################################################################
101 # Now verifying value of last_insert_id after committing rows in
102 # connection # 1
103 ############################################################################
104
105 --echo ## Switching to test_con1 ##
106 connection test_con1;
107
108 --echo ## Verifying all records in table & value of variable ##
109 SELECT * from t1;
110 SELECT @@session.last_insert_id;
111
112 --echo ## Commiting records in table ##
113 COMMIT;
114 SELECT @@session.last_insert_id;
115
116
117 --echo ## Switching to test_con2 & verifying value of variable in it ##
118 connection test_con2;
119 SELECT @@session.last_insert_id;
120
121
122
123 --echo '#--------------------FN_DYNVARS_059_05-------------------------#'
124 #####################################################################
125 # Verify value after explicitly setting value of variable #
126 #####################################################################
127
128 --echo ## Setting value of variable ##
129 SET @@session.last_insert_id = 100;
130 SELECT @@session.last_insert_id;
131
132 --echo ## Inserting new record and verifying variable's effect on it ##
133 INSERT into t1(name) values('Record_8');
134 SELECT @@session.last_insert_id;
135
136 --echo ## Dropping table t1 ##
137 drop table t1;
138
139 --echo ## Disconnecting both the connections ##
140 disconnect test_con1;
141 disconnect test_con2;
142
143
144
145
146