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