Debian Codegraph @

mysql-test/suite/memcached/t/memc277_default_table.test

raw
1 source include/not_valgrind.inc;
2 source include/have_memcached_plugin.inc;
3 source include/not_windows.inc;
4
5 --disable_query_log
6 CALL mtr.add_suppression("daemon-memcached-w-batch-size': unsigned");
7 CALL mtr.add_suppression("Could not obtain server's UPN to be used as target service name");
8 CALL mtr.add_suppression("Warning: MySQL is trying to drop");
9 --enable_query_log
10
11 --enable_connect_log
12 SET @transaction_isolation= @@global.transaction_isolation;
13 SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
14
15 # Create the memcached tables
16 --disable_query_log
17 source include/memcache_config.inc;
18 --enable_query_log
19
20 INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
21 "innodb_only", "innodb_only", "innodb_only");
22
23 INSERT INTO config_options VALUES("separator", "|");
24 INSERT INTO config_options VALUES("table_map_delimiter", ".");
25
26 # describe table for memcache
27 INSERT INTO containers VALUES ("desct1", "test", "t1",
28 "c1", "c2,c21", "c3", "c4", "c5", "PRIMARY");
29 INSERT INTO containers VALUES ("desct2", "test", "t2",
30 "c1", "c2", "c3", "c4", "c5", "PRIMARY");
31 INSERT INTO containers VALUES ("default", "test", "t3",
32 "c1", "c2,c21", "c3", "c4", "c5", "PRIMARY");
33 INSERT INTO containers VALUES ("desct4", "test", "t4",
34 "c1", "c2,c21,c22", "c3", "c4", "c5", "PRIMARY");
35 INSERT INTO containers VALUES ("desct5", "test", "t5",
36 "c1", "c2,c21,c22,c23", "c3", "c4", "c5", "PRIMARY");
37
38 USE test;
39
40 --disable_warnings
41 DROP TABLE IF EXISTS t1,t2;
42 --enable_warnings
43 CREATE TABLE t1 (c1 VARCHAR(32),
44 c2 VARCHAR(1024),
45 c21 VARCHAR(1024),
46 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
47 ENGINE = INNODB;
48
49 CREATE TABLE t2 (c1 VARCHAR(32),
50 c2 VARCHAR(1024),
51 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
52 ENGINE = INNODB;
53
54 CREATE TABLE t3 (c1 VARCHAR(32),
55 c2 VARCHAR(1024),
56 c21 VARCHAR(1024),
57 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
58 ENGINE = INNODB;
59
60 CREATE TABLE t4 (c1 VARCHAR(32),
61 c2 VARCHAR(1024),
62 c21 VARCHAR(1024),
63 c22 VARCHAR(1024),
64 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
65 ENGINE = INNODB;
66
67 CREATE TABLE t5 (c1 VARCHAR(32),
68 c2 VARCHAR(1024),
69 c21 VARCHAR(1024),
70 c22 VARCHAR(1024),
71 c23 VARCHAR(1024),
72 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
73 ENGINE = INNODB;
74
75 INSERT INTO t1 VALUES ('D', 'Darmstadt', 'City', 0, 0, 0);
76 INSERT INTO t1 VALUES ('B', 'Berlin', 'Mitte', 0, 0, 0);
77 INSERT INTO t1 VALUES ('C', 'Cottbus', 'West', 0, 0 ,0);
78 INSERT INTO t1 VALUES ('H', 'Hamburg', 'Norderstedt', 0, 0, 0);
79
80 INSERT INTO t2 VALUES ('D', 'Dortmund', 0, 0, 0);
81 INSERT INTO t2 VALUES ('B', 'Bremen', 0, 0, 0);
82 INSERT INTO t2 VALUES ('C', 'Celle', 0, 0 ,0);
83 INSERT INTO t2 VALUES ('H', 'Hannover', 0, 0, 0);
84
85 INSERT INTO t3 VALUES ('D', 'Detmold', 'City', 0, 0, 0);
86 INSERT INTO t3 VALUES ('B', 'Bonn', 'Mitte', 0, 0, 0);
87 INSERT INTO t3 VALUES ('A', 'Aachen', 'West', 0, 0 ,0);
88 INSERT INTO t3 VALUES ('H', 'Hameln', 'Nord', 0, 0, 0);
89
90 INSERT INTO t4 VALUES ('Z', 'Zossen', 'City', '12345', 0, 0, 0);
91 INSERT INTO t4 VALUES ('B', 'Brandenburg', 'Mitte', '23456', 0, 0, 0);
92 INSERT INTO t4 VALUES ('M', 'Muenchen', 'West', '34567', 0, 0 ,0);
93 INSERT INTO t4 VALUES ('H', 'Husum', 'Nord', '45678', 0, 0, 0);
94
95 INSERT INTO t5 VALUES ('D', 'Duesseldorf', 'City', '98765', 'Hafen', 0, 0, 0);
96 INSERT INTO t5 VALUES ('O', 'Oldenburg', 'Mitte', '87654', 'Deich', 0, 0, 0);
97 INSERT INTO t5 VALUES ('K', 'Koeln', 'West', '76543', 'Rhein', 0, 0 ,0);
98 INSERT INTO t5 VALUES ('S', 'Stuttgart', 'Nord', '65432', 'Fernsehturm', 0, 0, 0);
99
100 # Tables must exist before plugin can be started!
101 --let $memcached_address=127.0.0.1:11277
102 --source ../include/load_daemon_memcached_expecting_success.inc
103
104 --sorted_result
105 SELECT c1,c2,c21 FROM t1;
106 --sorted_result
107 SELECT c1,c2 FROM t2;
108 --sorted_result
109 SELECT c1,c2,c21 FROM t3;
110 --sorted_result
111 SELECT c1,c2,c21,c22 FROM t4;
112 --sorted_result
113 SELECT c1,c2,c21,c22,c23 FROM t5;
114
115 perl;
116 use DBI;
117 use Cache::Memcached;
118 my $memd = new Cache::Memcached {
119 'servers' => [ "127.0.0.1:11277" ],
120 'connect_timeout' => 20,
121 'select_timeout' => 20
122 };
123 print "Here are the memcached results with D,B,A,H of default:\n";
124 $val = $memd->get("D");
125 if ($val) { print "$val\n"; }
126 $val = $memd->get("B");
127 if ($val) { print "$val\n"; }
128 $val = $memd->get("A");
129 if ($val) { print "$val\n"; }
130 $val = $memd->get("H");
131 if ($val) { print "$val\n"; }
132 $memd->disconnect_all;
133 EOF
134
135 perl;
136 use DBI;
137 use Cache::Memcached;
138 my $memd = new Cache::Memcached {
139 'servers' => [ "127.0.0.1:11277" ],
140 'connect_timeout' => 20,
141 'select_timeout' => 20
142 };
143 print "Here are the memcached results with D,B,H,C of t1:\n";
144 $val = $memd->get('@@desct1.D');
145 if ($val) { print "$val\n"; }
146 $val = $memd->get("\@\@desct1.B");
147 if ($val) { print "$val\n"; }
148 $val = $memd->get('@@desct1.'.'H');
149 if ($val) { print "$val\n"; }
150 $val = $memd->get('@@desct1.C');
151 if ($val) { print "$val\n"; }
152 print "Here are the memcached results with D,B,H,C of t2:\n";
153 $val = $memd->get("\@\@desct2");
154 $val = $memd->get("D");
155 if ($val) { print "$val\n"; }
156 $val = $memd->get('@@desct2.B');
157 if ($val) { print "$val\n"; }
158 $val = $memd->get('H');
159 if ($val) { print "$val\n"; }
160 $val = $memd->get('C');
161 if ($val) { print "$val\n"; }
162 print "Here are the memcached results with D,B,H,A of t3:\n";
163 $val = $memd->get("\@\@desct3.D");
164 if ($val) { print "$val\n"; }
165 $val = $memd->get('@@desct3.B');
166 if ($val) { print "$val\n"; }
167 $val = $memd->get('@@desct3.H');
168 if ($val) { print "$val\n"; }
169 $val = $memd->get('@@desct3.A');
170 if ($val) { print "$val\n"; }
171 print "Here are the memcached results with D,O,K,S of t5:\n";
172 $val = $memd->get("\@\@desct5.D");
173 if ($val) { print "$val\n"; }
174 $val = $memd->get('@@desct5.O');
175 if ($val) { print "$val\n"; }
176 $val = $memd->get('@@desct5.K');
177 if ($val) { print "$val\n"; }
178 $val = $memd->get('@@desct5.S');
179 if ($val) { print "$val\n"; }
180 print "Here are the memcached results with Z,B,H,M of t4:\n";
181 $val = $memd->get("\@\@desct4.Z");
182 if ($val) { print "$val\n"; }
183 $val = $memd->get('@@desct4.B');
184 if ($val) { print "$val\n"; }
185 $val = $memd->get('@@desct4.H');
186 if ($val) { print "$val\n"; }
187 $val = $memd->get('@@desct4.M');
188 if ($val) { print "$val\n"; }
189 print "Here are the memcached results with D,B,H,C of t2:\n";
190 $memd->get("\@\@desct2");
191 $val = $memd->get("D");
192 if ($val) { print "$val\n"; }
193 $val = $memd->get('B');
194 if ($val) { print "$val\n"; }
195 $val = $memd->get('H');
196 if ($val) { print "$val\n"; }
197 $val = $memd->get('C');
198 if ($val) { print "$val\n"; }
199 $memd->disconnect_all;
200 EOF
201
202 --sorted_result
203 SELECT c1,c2,c21 FROM t1;
204 --sorted_result
205 SELECT c1,c2 FROM t2;
206 --sorted_result
207 SELECT c1,c2,c21 FROM t3;
208 --sorted_result
209 SELECT c1,c2,c21,c22 FROM t4;
210 --sorted_result
211 SELECT c1,c2,c21,c22,c23 FROM t5;
212
213 perl;
214 use DBI;
215 use Cache::Memcached;
216 my $memd = new Cache::Memcached {
217 'servers' => [ "127.0.0.1:11277" ],
218 'connect_timeout' => 20,
219 'select_timeout' => 20
220 };
221 print "Here are the memcached results after set in default:\n";
222 $val = $memd->set("E","Essen|Ost");
223 $val = $memd->get("E");
224 if ($val) { print "$val\n"; }
225 print "Here are the memcached results after set in t2:\n";
226 $val = $memd->set("\@\@desct2.E","Essen");
227 $val = $memd->get("\@\@desct2.E");
228 if ($val) { print "$val\n"; }
229 print "Here are the memcached results after set in t5:\n";
230 $val = $memd->get("\@\@desct5");
231 $val = $memd->set("E","Essen|Mitte|111111|Mahlzeit");
232 $val = $memd->get("E");
233 if ($val) { print "$val\n"; }
234 print "Here are the memcached results after set in t3:\n";
235 $val = $memd->set('@@desct3.E',"Essen|Ost");
236 $memd->get('@@desct3');
237 $val = $memd->get("E");
238 if ($val) { print "$val\n"; }
239 print "Here are the memcached results after set in t4:\n";
240 $val = $memd->set('@@desct4.E',"Essen|Mitte|111111");
241 $val = $memd->get('@@desct4.E');
242 if ($val) { print "$val\n"; }
243 print "Here are the memcached results after set in t5:\n";
244 $val = $memd->get("\@\@desct5");
245 $val = $memd->set("\@\@desct5.E","Essen|Aussen|111111|Mahlzeit");
246 $val = $memd->get("\@\@desct5.E");
247 if ($val) { print "$val\n"; }
248 $memd->disconnect_all;
249 EOF
250
251 --sorted_result
252 SELECT c1,c2,c21 FROM t1;
253 --sorted_result
254 SELECT c1,c2 FROM t2;
255 --sorted_result
256 SELECT c1,c2,c21 FROM t3;
257 --sorted_result
258 SELECT c1,c2,c21,c22 FROM t4;
259 --sorted_result
260 SELECT c1,c2,c21,c22,c23 FROM t5;
261
262 UNINSTALL PLUGIN daemon_memcached;
263 UPDATE innodb_memcache.containers SET name='desct3' WHERE name='default';
264 UPDATE innodb_memcache.containers SET name='default' WHERE name='desct4';
265 --let $memcached_address=127.0.0.1:11277
266 --source ../include/load_daemon_memcached_expecting_success.inc
267
268 --sorted_result
269 SELECT c1,c2,c21 FROM t1;
270 --sorted_result
271 SELECT c1,c2 FROM t2;
272 --sorted_result
273 SELECT c1,c2,c21 FROM t3;
274 --sorted_result
275 SELECT c1,c2,c21,c22 FROM t4;
276 --sorted_result
277 SELECT c1,c2,c21,c22,c23 FROM t5;
278
279 perl;
280 use DBI;
281 use Cache::Memcached;
282 my $memd = new Cache::Memcached {
283 'servers' => [ "127.0.0.1:11277" ],
284 'connect_timeout' => 20,
285 'select_timeout' => 20
286 };
287 print "Here are the memcached results after set in default:\n";
288 $val = $memd->set('E',"Essen|Ausserhalb|222222");
289 $val = $memd->get('E');
290 if ($val) { print "$val\n"; }
291 print "Here are the memcached results after set in t5:\n";
292 $val = $memd->get("\@\@desct5");
293 $val = $memd->set("E","Essen|Bahnhof|222222|Gut");
294 $val = $memd->get("E");
295 if ($val) { print "$val\n"; }
296 print "Here are the memcached results after set in t3:\n";
297 $val = $memd->set('@@desct3.E',"Essen|West");
298 $memd->get('@@desct3');
299 $val = $memd->get("E");
300 if ($val) { print "$val\n"; }
301 $memd->disconnect_all;
302 EOF
303
304 --sorted_result
305 SELECT c1,c2,c21 FROM t1;
306 --sorted_result
307 SELECT c1,c2,c21 FROM t3;
308 --sorted_result
309 SELECT c1,c2,c21,c22 FROM t4;
310 --sorted_result
311 SELECT c1,c2,c21,c22,c23 FROM t5;
312
313 UNINSTALL PLUGIN daemon_memcached;
314 # Have after UPDATE 2 defaults, t3 and t4.
315 --error ER_DUP_ENTRY
316 UPDATE innodb_memcache.containers SET name='default' WHERE name='desct3';
317 # Decription of t4 will be deleted and t1 will become default.
318 DELETE FROM innodb_memcache.containers WHERE name='default';
319 --let $memcached_address=127.0.0.1:11277
320 --source ../include/load_daemon_memcached_expecting_success.inc
321
322 --sorted_result
323 SELECT c1,c2,c21 FROM t1;
324 --sorted_result
325 SELECT c1,c2 FROM t2;
326 --sorted_result
327 SELECT c1,c2,c21 FROM t3;
328 --sorted_result
329 SELECT c1,c2,c21,c22 FROM t4;
330 --sorted_result
331 SELECT c1,c2,c21,c22,c23 FROM t5;
332
333 perl;
334 use DBI;
335 use Cache::Memcached;
336 my $memd = new Cache::Memcached {
337 'servers' => [ "127.0.0.1:11277" ],
338 'connect_timeout' => 20,
339 'select_timeout' => 20
340 };
341 print "Here are the memcached results after set in default:\n";
342 $val = $memd->set('E',"Essen|Nord");
343 $val = $memd->get('E');
344 if ($val) { print "$val\n"; }
345 print "Here are the memcached results after set in t4:\n";
346 $val = $memd->set('@@desct4.E',"Essen|West");
347 $memd->get('@@desct4');
348 $val = $memd->get("E");
349 if ($val) { print "$val\n"; }
350 $memd->disconnect_all;
351 EOF
352
353 --sorted_result
354 SELECT c1,c2,c21 FROM t1;
355 --sorted_result
356 SELECT c1,c2,c21 FROM t3;
357 --sorted_result
358 SELECT c1,c2,c21,c22 FROM t4;
359
360 UNINSTALL PLUGIN daemon_memcached;
361 #Delete first row in container, so 2nd must become default.
362 DELETE FROM innodb_memcache.containers WHERE name='desct1';
363 --let $memcached_address=127.0.0.1:11277
364 --source ../include/load_daemon_memcached_expecting_success.inc
365
366 --sorted_result
367 SELECT c1,c2,c21 FROM t1;
368 --sorted_result
369 SELECT c1,c2 FROM t2;
370 --sorted_result
371 SELECT c1,c2,c21 FROM t3;
372 --sorted_result
373 SELECT c1,c2,c21,c22 FROM t4;
374 --sorted_result
375 SELECT c1,c2,c21,c22,c23 FROM t5;
376
377 perl;
378 use DBI;
379 use Cache::Memcached;
380 my $memd = new Cache::Memcached {
381 'servers' => [ "127.0.0.1:11277" ],
382 'connect_timeout' => 20,
383 'select_timeout' => 20
384 };
385 print "Here are the memcached results after set in default:\n";
386 $val = $memd->set('E',"Emden");
387 $val = $memd->get('E');
388 if ($val) { print "$val\n"; }
389 print "Here are the memcached results after set in t1:\n";
390 $val = $memd->set('@@desct1.E',"Emden|Mole");
391 $memd->get('@@desct1');
392 $val = $memd->get("E");
393 if ($val) { print "$val\n"; }
394 $memd->disconnect_all;
395 EOF
396
397 --sorted_result
398 SELECT c1,c2,c21 FROM t1;
399 --sorted_result
400 SELECT c1,c2 FROM t2;
401
402 DROP TABLE t1,t2,t3,t4,t5;
403
404 UNINSTALL PLUGIN daemon_memcached;
405 DROP DATABASE innodb_memcache;
406
407 SET @@global.transaction_isolation= @transaction_isolation;