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 --source include/have_innodb.inc
23
24 --disable_warnings
25 drop table if exists t1;
26 --enable_warnings
27
28 #########################
29 # Creating new table #
30 #########################
31
32 --echo ## Creating new table ##
33 CREATE TABLE t1
34 (
35 id INT NOT NULL auto_increment,
36 PRIMARY KEY (id),
37 name VARCHAR(30)
38 ) ENGINE = INNODB;
39
40 --echo '#--------------------FN_DYNVARS_059_01-------------------------#'
41 #######################################################################
42 # Setting initial value of last_insert_id and verifying its behavior #
43 #######################################################################
44
45 --echo ## Verifying initial value of ##
46 SELECT @@session.last_insert_id;
47
48 --echo ## Inserting records in table t1 ##
49 INSERT into t1(name) values('Record_1');
50 INSERT into t1(name) values('Record_2');
51 SELECT * from t1;
52
53 --echo ## Verifying value of variable after inserting some rows ##
54 SELECT @@session.last_insert_id = 2;
55
56
57 --echo '#--------------------FN_DYNVARS_059_02-------------------------#'
58 #################################################################################
59 # Verifying value of last_insert_id with new connection without committing rows #
60 #################################################################################
61
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 connect (test_con2, localhost, root,,);
85 connection test_con2;
86
87 --echo ## Inserting values through new connection ##
88 INSERT into t1(name) values('Record_6');
89 INSERT into t1(name) values('Record_7');
90
91 SELECT * from t1;
92
93 --echo ## Verifying value of variable in second connection ##
94 SELECT @@last_insert_id;
95
96
97
98 --echo '#--------------------FN_DYNVARS_059_04-------------------------#'
99 ############################################################################
100 # Now verifying value of last_insert_id after committing rows in
101 # connection # 1
102 ############################################################################
103
104 connection test_con1;
105
106 --echo ## Verifying all records in table & value of variable ##
107 SELECT * from t1;
108 SELECT @@session.last_insert_id;
109
110 --echo ## Commiting records in table ##
111 COMMIT;
112 SELECT @@session.last_insert_id;
113
114
115 --echo ## Switching to test_con2 & verifying value of variable in it ##
116 connection test_con2;
117 SELECT @@session.last_insert_id;
118
119
120
121 --echo '#--------------------FN_DYNVARS_059_05-------------------------#'
122 #####################################################################
123 # Verify value after explicitly setting value of variable #
124 #####################################################################
125
126 --echo ## Setting value of variable ##
127 SET @@session.last_insert_id = 100;
128 SELECT @@session.last_insert_id;
129
130 --echo ## Inserting new record and verifying variable's effect on it ##
131 INSERT into t1(name) values('Record_8');
132 SELECT @@session.last_insert_id;
133
134 --echo ## Dropping table t1 ##
135 drop table t1;
136
137 disconnect test_con1;
138 disconnect test_con2;