MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
partition-dml-1-1-modes.inc
1 let $DATA_PATH=;
2 let $INDEX_PATH=;
3 --source include/have_partition.inc
4 
5 --echo # Additional tests for WL#5217 by QA, testplan 1.1
6 
7 let $TABLENAME= t1;
8 --source suite/parts/inc/partition-dml_cr_tab.inc
9 --source suite/parts/inc/partition-dml_ins_tab.inc
10 
11 --enable_connect_log
12 
13 GRANT ALL PRIVILEGES ON test.* TO test_user_1 IDENTIFIED BY 'testpw';
14 GRANT ALL PRIVILEGES ON test.* TO test_user_2 IDENTIFIED BY 'testpw';
15 
16 --echo # Run in empty mode
17 set @@global.sql_mode= '';
18 --echo #######################################################
19 --echo # UPDATE without partition selection
20 
21 connect (session1, localhost, test_user_1,'testpw',test);
22 SELECT * FROM t1 PARTITION (`p0-29`);
23 --echo send
24 send
25 UPDATE t1 SET b='table-upd-1' WHERE a BETWEEN 0 AND 9;
26 
27 connect (session2, localhost, test_user_2,'testpw',test);
28 let $wait_condition= SELECT count(*)= 4 FROM t1 WHERE b ='table-upd-1';
29 --source include/wait_condition.inc
30 UPDATE t1 SET b='table-upd-2' WHERE a BETWEEN 0 AND 9;
31 SELECT * FROM t1 PARTITION (`p0-29`);
32 INSERT INTO t1 VALUES (11, 'table-ins');
33 INSERT INTO t1 VALUES (12, 'table-ins');
34 SELECT * FROM t1 PARTITION (`p0-29`);
35 DELETE FROM t1 WHERE b= 'table-ins';
36 SELECT * FROM t1 PARTITION (`p0-29`);
37 
38 connection session1;
39 --echo reap
40 reap;
41 SELECT * FROM t1 PARTITION (`p0-29`);
42 
43 connection session2;
44 SELECT * FROM t1 PARTITION (`p0-29`);
45 
46 --echo #######################################################
47 --echo # UPDATE with partition selection
48 #connect (session1, localhost, test_user_1,'testpw',test);
49 connection session1;
50 SELECT * FROM t1 PARTITION (`p0-29`);
51 --echo send
52 send
53 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
54 
55 #connect (session2, localhost, test_user_2,'testpw',test);
56 connection session2;
57 let $wait_condition= SELECT count(*)= 4 FROM t1 WHERE b ='p0-29-upd-1';
58 --source include/wait_condition.inc
59 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
60 SELECT * FROM t1 PARTITION (`p0-29`);
61 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (11, '(p0-29-)ins');
62 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (12, '(p0-29-)ins');
63 SELECT * FROM t1 PARTITION (`p0-29`);
64 DELETE FROM t1 PARTITION (`p0-29`,subp5) WHERE b= '(p0-29-)ins';
65 SELECT * FROM t1 PARTITION (`p0-29`);
66 
67 connection session1;
68 --echo reap
69 reap;
70 SELECT * FROM t1 PARTITION (`p0-29`);
71 
72 connection session2;
73 SELECT * FROM t1 PARTITION (`p0-29`);
74 
75 disconnect session1;
76 disconnect session2;
77 
78 connection default;
79 DROP TABLE t1;
80 --source suite/parts/inc/partition-dml_cr_tab.inc
81 --source suite/parts/inc/partition-dml_ins_tab.inc
82 --echo # Run in ANSI mode
83 set @@global.sql_mode= ansi;
84 --echo #######################################################
85 --echo # UPDATE without partition selection
86 
87 connect (session1, localhost, test_user_1,'testpw',test);
88 SELECT * FROM t1 PARTITION (`p0-29`);
89 --echo send
90 send
91 UPDATE t1 SET b='table-upd-1' WHERE a BETWEEN 0 AND 9;
92 
93 connect (session2, localhost, test_user_2,'testpw',test);
94 let $wait_condition= SELECT count(*)= 4 FROM t1 WHERE b ='table-upd-1';
95 --source include/wait_condition.inc
96 UPDATE t1 SET b='table-upd-2' WHERE a BETWEEN 0 AND 9;
97 SELECT * FROM t1 PARTITION (`p0-29`);
98 INSERT INTO t1 VALUES (11, 'table-ins');
99 INSERT INTO t1 VALUES (12, 'table-ins');
100 SELECT * FROM t1 PARTITION (`p0-29`);
101 DELETE FROM t1 WHERE b= 'table-ins';
102 SELECT * FROM t1 PARTITION (`p0-29`);
103 
104 connection session1;
105 --echo reap
106 reap;
107 SELECT * FROM t1 PARTITION (`p0-29`);
108 
109 connection session2;
110 SELECT * FROM t1 PARTITION (`p0-29`);
111 
112 --echo #######################################################
113 --echo # UPDATE with partition selection
114 #connect (session1, localhost, test_user_1,'testpw',test);
115 connection session1;
116 SELECT * FROM t1 PARTITION (`p0-29`);
117 --echo send
118 send
119 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
120 
121 #connect (session2, localhost, test_user_2,'testpw',test);
122 connection session2;
123 let $wait_condition= SELECT count(*)= 4 FROM t1 WHERE b ='p0-29-upd-1';
124 --source include/wait_condition.inc
125 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
126 SELECT * FROM t1 PARTITION (`p0-29`);
127 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (11, '(p0-29-)ins');
128 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (12, '(p0-29-)ins');
129 SELECT * FROM t1 PARTITION (`p0-29`);
130 DELETE FROM t1 PARTITION (`p0-29`,subp5) WHERE b= '(p0-29-)ins';
131 SELECT * FROM t1 PARTITION (`p0-29`);
132 
133 connection session1;
134 --echo reap
135 reap;
136 SELECT * FROM t1 PARTITION (`p0-29`);
137 
138 connection session2;
139 SELECT * FROM t1 PARTITION (`p0-29`);
140 
141 disconnect session1;
142 disconnect session2;
143 
144 connection default;
145 DROP TABLE t1;
146 --source suite/parts/inc/partition-dml_cr_tab.inc
147 --source suite/parts/inc/partition-dml_ins_tab.inc
148 --echo # Run in traditional mode
149 set @@global.sql_mode= traditional;
150 --echo #######################################################
151 --echo # UPDATE without partition selection
152 
153 connect (session1, localhost, test_user_1,'testpw',test);
154 SELECT * FROM t1 PARTITION (`p0-29`);
155 --echo send
156 send
157 UPDATE t1 SET b='table-upd-1' WHERE a BETWEEN 0 AND 9;
158 
159 connect (session2, localhost, test_user_2,'testpw',test);
160 let $wait_condition= SELECT count(*)= 4 FROM t1 WHERE b ='table-upd-1';
161 --source include/wait_condition.inc
162 UPDATE t1 SET b='table-upd-2' WHERE a BETWEEN 0 AND 9;
163 SELECT * FROM t1 PARTITION (`p0-29`);
164 INSERT INTO t1 VALUES (11, 'table-ins');
165 INSERT INTO t1 VALUES (12, 'table-ins');
166 SELECT * FROM t1 PARTITION (`p0-29`);
167 DELETE FROM t1 WHERE b= 'table-ins';
168 SELECT * FROM t1 PARTITION (`p0-29`);
169 
170 connection session1;
171 --echo reap
172 reap;
173 SELECT * FROM t1 PARTITION (`p0-29`);
174 
175 connection session2;
176 SELECT * FROM t1 PARTITION (`p0-29`);
177 
178 --echo #######################################################
179 --echo # UPDATE with partition selection
180 #connect (session1, localhost, test_user_1,'testpw',test);
181 connection session1;
182 SELECT * FROM t1 PARTITION (`p0-29`);
183 --echo send
184 send
185 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
186 
187 #connect (session2, localhost, test_user_2,'testpw',test);
188 connection session2;
189 let $wait_condition= SELECT count(*)= 4 FROM t1 WHERE b ='p0-29-upd-1';
190 --source include/wait_condition.inc
191 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9;
192 SELECT * FROM t1 PARTITION (`p0-29`);
193 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (11, '(p0-29-)ins');
194 INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (12, '(p0-29-)ins');
195 SELECT * FROM t1 PARTITION (`p0-29`);
196 DELETE FROM t1 PARTITION (`p0-29`,subp5) WHERE b= '(p0-29-)ins';
197 SELECT * FROM t1 PARTITION (`p0-29`);
198 
199 connection session1;
200 --echo reap
201 reap;
202 SELECT * FROM t1 PARTITION (`p0-29`);
203 
204 connection session2;
205 SELECT * FROM t1 PARTITION (`p0-29`);
206 
207 disconnect session1;
208 disconnect session2;
209 connection default;
210 DROP TABLE t1;
211 DROP USER test_user_1;
212 DROP USER test_user_2;
213