Debian Codegraph @

mysql-test/suite/memcached/t/memc250_container.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
25 # describe table for memcache
26 INSERT INTO containers VALUES ("desc_t1", "test", "t1",
27 "c1", "c2", "c3", "c4", "c5", "PRIMARY");
28
29 USE test;
30
31 --disable_warnings
32 DROP TABLE IF EXISTS t1;
33 --enable_warnings
34 CREATE TABLE t1 (c1 VARCHAR(32),
35 c2 VARCHAR(1024),
36 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
37 ENGINE = INNODB;
38
39 INSERT INTO t1 VALUES ('D', 'Darmstadt', 0, 0, 0);
40 INSERT INTO t1 VALUES ('B', 'Berlin', 0, 0, 0);
41 INSERT INTO t1 VALUES ('C', 'Cottbus', 0, 0 ,0);
42 INSERT INTO t1 VALUES ('H', 'Hamburg', 0, 0, 0);
43
44 # Tables must exist before plugin can be started!
45 --let $memcached_address=127.0.0.1:11250
46 --source ../include/load_daemon_memcached_expecting_success.inc
47
48 --sorted_result
49 SELECT c1,c2 FROM t1;
50
51 perl;
52 use DBI;
53 use Cache::Memcached;
54 my $memd = new Cache::Memcached {
55 'servers' => [ "127.0.0.1:11250" ],
56 'connect_timeout' => 20,
57 'select_timeout' => 20
58 };
59 print "# Add E|Essen:\n";
60 if ($memd->add("E","Essen")) {
61 print "Ok.\n";
62 }
63 else {
64 print "Error: E|Essen is still existing.\n";
65 }
66 $memd->disconnect_all;
67 EOF
68
69 perl;
70 use DBI;
71 use Cache::Memcached;
72 my $memd = new Cache::Memcached {
73 'servers' => [ "127.0.0.1:11250" ],
74 'connect_timeout' => 20,
75 'select_timeout' => 20
76 };
77 print "# Add E|Essen:\n";
78 if ($memd->add("E","Essen")) {
79 print "Ok.\n";
80 }
81 else {
82 print "Error: E|Essen is still existing.\n";
83 }
84 $memd->disconnect_all;
85 EOF
86
87 --sorted_result
88 SELECT c1,c2 FROM t1;
89
90 perl;
91 use DBI;
92 use Cache::Memcached;
93 my $memd = new Cache::Memcached {
94 'servers' => [ "127.0.0.1:11250" ],
95 'connect_timeout' => 20,
96 'select_timeout' => 20
97 };
98 print "# Here are the memcached results with E:\n";
99 $val = $memd->get("E");
100 if ($val) { print "$val\n"; }
101 $memd->disconnect_all;
102 EOF
103
104 --sorted_result
105 SELECT c1,c2 FROM t1;
106
107 perl;
108 use DBI;
109 use Cache::Memcached;
110 my $memd = new Cache::Memcached {
111 'servers' => [ "127.0.0.1:11250" ],
112 'connect_timeout' => 20,
113 'select_timeout' => 20
114 };
115 print "# Here are the memcached results with E,H:\n";
116 $val = $memd->get("E");
117 if ($val) { print "$val\n"; }
118 $val = $memd->get("H");
119 if ($val) { print "$val\n"; }
120 $memd->disconnect_all;
121 EOF
122
123 #----------------------------------------------------------------
124 # Switch to a different table
125 #----------------------------------------------------------------
126
127 # Stop plugin before innodb_memcached configuration
128 UNINSTALL PLUGIN daemon_memcached;
129
130 use innodb_memcache;
131
132 DELETE FROM containers;
133
134 # describe table for memcache
135 INSERT INTO containers VALUES ("desc_t2", "test", "t2",
136 "c1", "c2", "c3", "c4", "c5", "PRIMARY");
137
138 USE test;
139
140 --disable_warnings
141 DROP TABLE IF EXISTS t1;
142 --enable_warnings
143 CREATE TABLE t2 (c1 VARCHAR(32),
144 c2 VARCHAR(1024),
145 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
146 ENGINE = INNODB;
147
148 INSERT INTO t2 VALUES ('D', 't2_Darmstadt', 0, 0, 0);
149 INSERT INTO t2 VALUES ('B', 't2_Berlin', 0, 0, 0);
150 INSERT INTO t2 VALUES ('C', 't2_Cottbus', 0, 0 ,0);
151 INSERT INTO t2 VALUES ('H', 't2_Hamburg', 0, 0, 0);
152
153 # Tables must exist before plugin can be started!
154 --let $memcached_address=127.0.0.1:11250
155 --source ../include/load_daemon_memcached_expecting_success.inc
156
157 --sorted_result
158 SELECT c1,c2 FROM t2;
159
160 perl;
161 use DBI;
162 use Cache::Memcached;
163 my $memd = new Cache::Memcached {
164 'servers' => [ "127.0.0.1:11250" ],
165 'connect_timeout' => 20,
166 'select_timeout' => 20
167 };
168 print "# Add E|Essen:\n";
169 if ($memd->add("E","Essen")) {
170 print "Ok.\n";
171 }
172 else {
173 print "Error: E|Essen is still existing.\n";
174 }
175
176 $memd->disconnect_all;
177 EOF
178
179 perl;
180 use DBI;
181 use Cache::Memcached;
182 my $memd = new Cache::Memcached {
183 'servers' => [ "127.0.0.1:11250" ],
184 'connect_timeout' => 20,
185 'select_timeout' => 20
186 };
187 print "# Add E|Essen:\n";
188 if ($memd->add("E","Essen")) {
189 print "Ok.\n";
190 }
191 else {
192 print "Error: E|Essen is still existing.\n";
193 }
194 $memd->disconnect_all;
195 EOF
196
197 --sorted_result
198 SELECT c1,c2 FROM t2;
199
200 perl;
201 use DBI;
202 use Cache::Memcached;
203 my $memd = new Cache::Memcached {
204 'servers' => [ "127.0.0.1:11250" ],
205 'connect_timeout' => 20,
206 'select_timeout' => 20
207 };
208 print "# Here are the memcached results with E:\n";
209 $val = $memd->get("E");
210 if ($val) { print "$val\n"; }
211 $memd->disconnect_all;
212 EOF
213
214 --sorted_result
215 SELECT c1,c2 FROM t2;
216
217 perl;
218 use DBI;
219 use Cache::Memcached;
220 my $memd = new Cache::Memcached {
221 'servers' => [ "127.0.0.1:11250" ],
222 'connect_timeout' => 20,
223 'select_timeout' => 20
224 };
225 print "# Here are the memcached results with E,H:\n";
226 $val = $memd->get("E");
227 if ($val) { print "$val\n"; }
228 $val = $memd->get("H");
229 if ($val) { print "$val\n"; }
230 $memd->disconnect_all;
231 EOF
232
233 #----------------------------------------------------------------
234 # Schema name as NULL
235 #----------------------------------------------------------------
236
237 # Stop plugin before innodb_memcached configuration
238 UNINSTALL PLUGIN daemon_memcached;
239
240 use innodb_memcache;
241
242 DELETE FROM containers;
243
244 --error 1048
245 INSERT INTO containers VALUES ("desc_t2", NULL, "t2",
246 "c1", "c2", "c3", "c4", "c5", "PRIMARY");
247
248 --error 1048
249 INSERT INTO containers VALUES ("desc_t2", "test", "t2",
250 NULL, "c2", "c3", "c4", "c5", "PRIMARY");
251
252 #----------------------------------------------------------------
253 # Non-existing Schema
254 #----------------------------------------------------------------
255 # Stop plugin before innodb_memcached configuration
256
257 DELETE FROM containers;
258
259 # describe table for memcache
260 INSERT INTO containers VALUES ("desc_t2", "junk", "t2",
261 "c1", "c2", "c3", "c4", "c5", "PRIMARY");
262
263 # even though the plugin can be installed, the initialization will fail
264 --let $memcached_address=127.0.0.1:11250
265 --source ../include/load_daemon_memcached_expecting_error.inc
266
267 perl;
268 use DBI;
269 use Cache::Memcached;
270 print "# This will fail, since memcached not initialized\n";
271 my $memd = new Cache::Memcached {
272 'servers' => [ "127.0.0.1:11250" ],
273 'connect_timeout' => 20,
274 'select_timeout' => 20
275 };
276 $val = $memd->get("E");
277 if ($val) { print "$val\n"; }
278 $val = $memd->get("H");
279 if ($val) { print "$val\n"; }
280 $memd->disconnect_all;
281 EOF
282
283 #----------------------------------------------------------------
284 # Test different column types
285 #----------------------------------------------------------------
286 # Stop plugin before innodb_memcached configuration
287 UNINSTALL PLUGIN daemon_memcached;
288
289 DELETE FROM containers;
290
291 # describe table for memcache
292 INSERT INTO containers VALUES ("desc_t2", "test", "t2",
293 "c1", "c2", "c3", "c4", "c5", "PRIMARY");
294
295 USE test;
296
297 --disable_warnings
298 DROP TABLE IF EXISTS t2;
299 --enable_warnings
300 CREATE TABLE t2 (c1 int,
301 c2 VARCHAR(1024),
302 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
303 ENGINE = INNODB;
304
305 INSERT INTO t2 VALUES (3, 't2_Hamburg', 0, 0, 0);
306
307 # Tables must exist before plugin can be started!
308 --let $memcached_address=127.0.0.1:11250
309 --source ../include/load_daemon_memcached_expecting_success.inc
310
311 perl;
312 use DBI;
313 use Cache::Memcached;
314 print "# This will succeed, since memcached supports int as key\n";
315 my $memd = new Cache::Memcached {
316 'servers' => [ "127.0.0.1:11250" ],
317 'connect_timeout' => 20,
318 'select_timeout' => 20
319 };
320 $val = $memd->get("3");
321 if ($val) { print "$val\n"; }
322 $memd->disconnect_all;
323 EOF
324
325 #----------------------------------------------------------------
326 # Test different column types
327 #----------------------------------------------------------------
328
329 use test;
330
331 --disable_warnings
332 DROP TABLE IF EXISTS t2;
333 --enable_warnings
334 CREATE TABLE t2 (c1 CHAR(10),
335 c2 VARCHAR(1024),
336 c3 INT, c4 INT, c5 INT, primary key(c1));
337
338 INSERT INTO t2 VALUES ('H', 't2_Hamburg', 0, 0, 0);
339
340 UNINSTALL PLUGIN daemon_memcached;
341
342 --let $memcached_address=127.0.0.1:11250
343 --source ../include/load_daemon_memcached_expecting_success.inc
344
345 perl;
346 use DBI;
347 use Cache::Memcached;
348 print "# This will fail, since memcached not initialized\n";
349 my $memd = new Cache::Memcached {
350 'servers' => [ "127.0.0.1:11250" ],
351 'connect_timeout' => 20,
352 'select_timeout' => 20
353 };
354 $val = $memd->get("H");
355 if ($val) { print "$val\n"; }
356 $memd->disconnect_all;
357 EOF
358
359 #----------------------------------------------------------------
360 # Test CAS
361 #----------------------------------------------------------------
362
363 # Stop plugin before innodb_memcached configuration
364 UNINSTALL PLUGIN daemon_memcached;
365
366 use innodb_memcache;
367
368 DELETE FROM containers;
369
370 # describe table for memcache
371 INSERT INTO containers VALUES ("desc_t2", "test", "t2",
372 "c1", "c2", "c3", "c4", "c5", "PRIMARY");
373
374 USE test;
375
376 --disable_warnings
377 DROP TABLE IF EXISTS t2;
378 --enable_warnings
379 CREATE TABLE t2 (c1 VARCHAR(32),
380 c2 VARCHAR(1024),
381 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
382 ENGINE = INNODB;
383
384 # Tables must exist before plugin can be started!
385 --let $memcached_address=127.0.0.1:11250
386 --source ../include/load_daemon_memcached_expecting_success.inc
387
388 --sorted_result
389 SELECT c1,c2 FROM t2;
390
391 perl;
392 use DBI;
393 use Cache::Memcached;
394 my $memd = new Cache::Memcached {
395 'servers' => [ "127.0.0.1:11250" ],
396 'connect_timeout' => 20,
397 'select_timeout' => 20
398 };
399 print "# Add E|Essen:\n";
400 if ($memd->add("E","Essen")) {
401 print "Ok.\n";
402 }
403 else {
404 print "Error: E|Essen is still existing.\n";
405 }
406 $memd->disconnect_all;
407 EOF
408
409 # This test could fail when run with --repeat>1 because the monotonic
410 # counter used for CAS is not reset between runs (it is a static
411 # variable inside mci_get_cas() function).
412 # To overcome this we take note of baseline cas value first
413 # and then subtract it from subsequent queries.
414 SELECT MAX(c4) INTO @baseline_cas_value FROM t2;
415 # Notice the cas value is updated
416 --sorted_result
417 SELECT c1, c2, c4-@baseline_cas_value as relative_cas FROM t2;
418
419 perl;
420 use DBI;
421 use Cache::Memcached;
422 my $memd = new Cache::Memcached {
423 'servers' => [ "127.0.0.1:11250" ],
424 'connect_timeout' => 20,
425 'select_timeout' => 20
426 };
427 print "# Add G|Gossen:\n";
428 if ($memd->add("G","Gossen")) {
429 print "Ok.\n";
430 }
431 else {
432 print "Error: G|Gossen is still existing.\n";
433 }
434 if ($memd->add("H","Hamburg")) {
435 print "Ok.\n";
436 }
437 else {
438 print "Error: H|Hamburg is still existing.\n";
439 }
440 $memd->disconnect_all;
441 EOF
442
443 --sorted_result
444 SELECT c1, c2, c4-@baseline_cas_value as relative_cas FROM t2;
445
446
447 #----------------------------------------------------------------
448 # Test expiration
449 #----------------------------------------------------------------
450
451 --sorted_result
452 SELECT c1,c2,c3,c4-@baseline_cas_value as relative_cas,c5 FROM test.t2;
453
454 perl;
455 use DBI;
456 use Cache::Memcached;
457 my $memd = new Cache::Memcached {
458 'servers' => [ "127.0.0.1:11250" ],
459 'connect_timeout' => 20,
460 'select_timeout' => 20
461 };
462 print "# Add B|Berlin expires in 1 seconds:\n";
463 if ($memd->add("B","Berlin",1)) {
464 print "Ok.\n";
465 }
466 else {
467 print "Error: B|Berlin is still existing.\n";
468 }
469 $memd->disconnect_all;
470 EOF
471
472 # This sleep is really neaded to make the key in memcache expire
473 select sleep(3);
474
475 perl;
476 use DBI;
477 use Cache::Memcached;
478 print "# Should not find B|Berlin, since it is expired\n";
479 my $memd = new Cache::Memcached {
480 'servers' => [ "127.0.0.1:11250" ],
481 'connect_timeout' => 20,
482 'select_timeout' => 20
483 };
484 $val = $memd->get("B");
485 if ($val) { print "$val\n"; }
486 $memd->disconnect_all;
487 EOF
488
489 perl;
490 use DBI;
491 use Cache::Memcached;
492 my $memd = new Cache::Memcached {
493 'servers' => [ "127.0.0.1:11250" ],
494 'connect_timeout' => 20,
495 'select_timeout' => 20
496 };
497 print "# Add C|Cottbus, expires 100 seconds:\n";
498 if ($memd->add("C","Cottbus", 100)) {
499 print "Ok.\n";
500 }
501 else {
502 print "Error: C|Cottbus is still existing.\n";
503 }
504
505 perl;
506 use DBI;
507 use Cache::Memcached;
508 print "# Should be able to find C|Cottbus, assume it does not take 100s to get here\n";
509 my $memd = new Cache::Memcached {
510 'servers' => [ "127.0.0.1:11250" ],
511 'connect_timeout' => 20,
512 'select_timeout' => 20
513 };
514 $val = $memd->get("C");
515 if ($val) { print "$val\n"; }
516 $memd->disconnect_all;
517 EOF
518
519 # set an expiration to be 60*60*24*30 (seconds in 30 days), it
520 # will be considered as absolute time
521 perl;
522 use DBI;
523 use Cache::Memcached;
524 my $memd = new Cache::Memcached {
525 'servers' => [ "127.0.0.1:11250" ],
526 'connect_timeout' => 20,
527 'select_timeout' => 20
528 };
529 print "# Add J|Jilin\n";
530 if ($memd->add("J","Jilin", 2592001)) {
531 print "Ok.\n";
532 }
533 else {
534 print "Error: J|Jilin is still existing.\n";
535 }
536 $memd->disconnect_all;
537 EOF
538
539 perl;
540 use DBI;
541 use Cache::Memcached;
542 print "# Should not find J|Jilin, it should be expired\n";
543 my $memd = new Cache::Memcached {
544 'servers' => [ "127.0.0.1:11250" ],
545 'connect_timeout' => 20,
546 'select_timeout' => 20
547 };
548 $val = $memd->get("J");
549 if ($val) { print "$val\n"; }
550 $memd->disconnect_all;
551 EOF
552
553 #----------------------------------------------------------------
554 # Alter mapped column type
555 #----------------------------------------------------------------
556
557 # Stop plugin before innodb_memcached configuration
558 UNINSTALL PLUGIN daemon_memcached;
559
560 use innodb_memcache;
561
562 DELETE FROM containers;
563
564 # describe table for memcache
565 INSERT INTO containers VALUES ("desc_t2", "test", "t2",
566 "c1", "c2", "c3", "c4", "c5", "PRIMARY");
567
568 USE test;
569
570 --disable_warnings
571 DROP TABLE IF EXISTS t2;
572 --enable_warnings
573 CREATE TABLE t2 (c1 VARCHAR(32),
574 c2 VARCHAR(1024),
575 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
576 ENGINE = INNODB;
577
578 INSERT INTO t2 VALUES ('D', 't2_Darmstadt', 0, 0, 0);
579 INSERT INTO t2 VALUES ('B', 't2_Berlin', 0, 0, 0);
580 INSERT INTO t2 VALUES ('C', 't2_Cottbus', 0, 0 ,0);
581 INSERT INTO t2 VALUES ('H', 't2_Hamburg', 0, 0, 0);
582
583 # Tables must exist before plugin can be started!
584 --let $memcached_address=127.0.0.1:11250
585 --source ../include/load_daemon_memcached_expecting_success.inc
586
587 --sorted_result
588 SELECT c1,c2 FROM t2;
589
590 perl;
591 use DBI;
592 use Cache::Memcached;
593 my $memd = new Cache::Memcached {
594 'servers' => [ "127.0.0.1:11250" ],
595 'connect_timeout' => 20,
596 'select_timeout' => 20
597 };
598 print "# Add E|Essen:\n";
599 if ($memd->add("E","Essen")) {
600 print "Ok.\n";
601 }
602 else {
603 print "Error: E|Essen is still existing.\n";
604 }
605
606 $memd->disconnect_all;
607 EOF
608
609 connection default;
610 --disable_query_log
611 --disable_warnings
612 delete from test.t2;
613 alter table test.t2 modify c2 int;
614
615 select c1, c2 from test.t2;
616
617 perl;
618 use DBI;
619 use Cache::Memcached;
620 my $memd = new Cache::Memcached {
621 'servers' => [ "127.0.0.1:11250" ],
622 'connect_timeout' => 20,
623 'select_timeout' => 20
624 };
625 print "# Add E|Essen:\n";
626 if ($memd->add("E","Essen")) {
627 print "Ok.\n";
628 }
629 else {
630 print "Error: E|Essen is still existing.\n";
631 }
632 $val = $memd->get("B");
633 if ($val) { print "$val\n"; }
634 $memd->disconnect_all;
635 EOF
636
637 --sorted_result
638 SELECT c1,c2 FROM t2;
639
640 perl;
641 use DBI;
642 use Cache::Memcached;
643 my $memd = new Cache::Memcached {
644 'servers' => [ "127.0.0.1:11250" ],
645 'connect_timeout' => 20,
646 'select_timeout' => 20
647 };
648 print "# Here are the memcached results with E:\n";
649 $val = $memd->get("E");
650 if ($val) { print "# value of E is $val end.\n"; }
651 $memd->disconnect_all;
652 EOF
653
654 --sorted_result
655 SELECT c1,c2 FROM t2;
656
657 perl;
658 use DBI;
659 use Cache::Memcached;
660 my $memd = new Cache::Memcached {
661 'servers' => [ "127.0.0.1:11250" ],
662 'connect_timeout' => 20,
663 'select_timeout' => 20
664 };
665 print "# Here are the memcached results with E,H:\n";
666 $val = $memd->get("E");
667 if ($val) { print "$val\n"; }
668 $val = $memd->get("H");
669 if ($val) { print "$val\n"; }
670 $memd->disconnect_all;
671 EOF
672
673 connection default;
674 UNINSTALL PLUGIN daemon_memcached;
675 --enable_warnings
676 --enable_query_log
677 alter table test.t2 change c2 c_new int;
678
679 show create table test.t2;
680
681 # This install will not initialize the table mapping, due to column
682 # type mismatch
683 --let $memcached_address=127.0.0.1:11250
684 --source ../include/load_daemon_memcached_expecting_error.inc
685
686 #----------------------------------------------------------------
687 DROP TABLE t2;
688
689 UNINSTALL PLUGIN daemon_memcached;
690 DROP DATABASE innodb_memcache;
691
692 SET @@global.transaction_isolation= @transaction_isolation;