1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
source include/not_valgrind.inc;
source include/have_memcached_plugin.inc;
source include/not_windows.inc;
# Save the initial number of concurrent sessions
--source include/count_sessions.inc
--disable_query_log
CALL mtr.add_suppression("daemon-memcached-w-batch-size': unsigned");
CALL mtr.add_suppression("Could not obtain server's UPN to be used as target service name");
CALL mtr.add_suppression("Warning: MySQL is trying to drop");
--enable_query_log
--enable_connect_log
SET @old_innodb_api_trx_level = @@global.innodb_api_trx_level;
# Create the memcached tables
--disable_query_log
source include/memcache_config.inc;
--enable_query_log
INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
"innodb_only", "innodb_only", "innodb_only");
INSERT INTO config_options VALUES("separator", "|");
# describe table for memcache
INSERT INTO containers VALUES ("desc_t1", "test", "t1",
"c1", "c2", "c3", "c4", "c5", "PRIMARY");
--connect (c_diag,localhost, root,,)
--connection default
--let $innodb_api_trx_level = 0
while($innodb_api_trx_level < 4)
{
--echo #
--echo # Testing the scenario with innodb_api_trx_level = $innodb_api_trx_level
--echo #
--eval SET @@global.innodb_api_trx_level = $innodb_api_trx_level
USE test;
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 (c1 VARCHAR(32),
c2 VARCHAR(1024),
c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
ENGINE = INNODB;
INSERT INTO t1 VALUES ('D', 'Darmstadt', 0, 0, 0);
INSERT INTO t1 VALUES ('B', 'Berlin', 0, 0, 0);
INSERT INTO t1 VALUES ('C', 'Cottbus', 0, 0 ,0);
INSERT INTO t1 VALUES ('H', 'Hamburg', 0, 0, 0);
# Tables must exist before plugin can be started!
--let $memcached_address=127.0.0.1:11256
--source ../include/load_daemon_memcached_expecting_success.inc
SET autocommit=0;
LOCK TABLES t1 WRITE;
perl;
use DBI;
use Cache::Memcached;
use Time::HiRes qw(time);
use POSIX qw(strftime);
$| = 1;
my $memd = new Cache::Memcached {
'servers' => [ "127.0.0.1:11256" ],
'connect_timeout' => 20,
'select_timeout' => 5
};
# It currently depends on innodb_api_trx_level if the get will fail.
# It only fails in the SERIALIZABLE (3) level.
$key="D";
print "Get $key...\n";
$val = $memd->get($key);
print "Get has returned $val\n";
# sets should always fail as they always acquire InnoDB table lock
$val="new value";
print "Set $key to $val\n";
my $set_res=$memd->set($key,$val);
print "Set returned $set_res\n";
print "Get $key...\n";
$val = $memd->get($key);
print "Get has returned $val\n";
$memd->disconnect_all;
EOF
# We still haven't commited nor relased the MDL, so the set operation from
# memcached has to wait. Current implementation of memcached does not care
# much about MDL, but it honours InnoDB table locks, so it will wait until
# COMMIT.
--connection c_diag
# There should be X GRANTED to 'default' visible here
# and an IX generated by set operation WAITING behind it.
# In case of SERIALIZABLE (3) there should also be 2 WAITING IS locks of gets
--sorted_result
SELECT lock_mode,lock_status FROM performance_schema.data_locks
WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
# There should be a metadata lock granted to 'default'
--sorted_result
SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
--connection default
SELECT c2 FROM t1 WHERE c1='D';
COMMIT;
SET autocommit=1;
# We have commited, so InnoDB's table locks are released, and the set
# operation from memcached can proceed even though we still hold MDL lock.
# The set is reported to client as timed out (select_timeout=5), but its
# transaction's thread actually should eventually finish the operation.
# We wait here until c2 becomes "new value".
--let wait_condition=SELECT COUNT(*) FROM t1 WHERE c1='D' AND c2='new value'
--source include/wait_condition.inc
SELECT c2 FROM t1 WHERE c1='D';
--connection c_diag
# There should be no InnoDB table locks any more
--sorted_result
SELECT lock_mode,lock_status FROM performance_schema.data_locks
WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
# There should still be a metadata lock
--sorted_result
SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
--connection default
UNLOCK TABLES;
--connection c_diag
# There should be no locks
--sorted_result
SELECT lock_mode,lock_status FROM performance_schema.data_locks
WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
# There should be no metadata locks
--sorted_result
SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
SELECT c2 FROM t1 WHERE c1='D';
--connection default
# Stop plugin before innodb_memcached configuration
UNINSTALL PLUGIN daemon_memcached;
DROP TABLE t1;
--inc $innodb_api_trx_level
}
--disconnect c_diag
DROP DATABASE innodb_memcache;
SET @@global.innodb_api_trx_level = @old_innodb_api_trx_level;
# Wait till all disconnects are completed.
--source include/wait_until_count_sessions.inc
|
