diff options
Diffstat (limited to 'mysql-test/suite/galera')
13 files changed, 138 insertions, 2918 deletions
diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index feac559ff55..c8ea8420e8b 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -10,33 +10,13 @@ # ############################################################################## -GCF-939 : MDEV-21520 galera.GCF-939 -MW-329 : MDEV-19962 Galera test failure on MW-329 galera_as_slave_ctas : MDEV-28378 timeout -galera_as_slave_replication_bundle : MDEV-15785 OPTION_GTID_BEGIN is set in Gtid_log_event::do_apply_event() -galera_bf_abort_group_commit : MDEV-18282 Galera test failure on galera.galera_bf_abort_group_commit -galera_bf_kill_debug : MDEV-24485 wsrep::client_state::do_acquire_ownership(): Assertion `state_ == s_idle || mode_ != m_local' failed -galera_bf_lock_wait : MDEV-21597 wsrep::transaction::start_transaction(): Assertion `active() == false' failed -galera_encrypt_tmp_files : Get error failed to enable encryption of temporary files -galera_gcache_recover_manytrx : MDEV-18834 Galera test failure -galera_parallel_simple : MDEV-20318 galera.galera_parallel_simple fails +galera_bf_abort_at_after_statement : Timeout in wait_condition.inc for SELECT COUNT(*) = 1 FROM t1 where id = 1 and val = 3 galera_pc_recovery : MDEV-25199 cluster fails to start up -galera_shutdown_nonprim : MDEV-21493 galera.galera_shutdown_nonprim -galera_sst_mysqldump : MDEV-26501 : galera.galera_sst_mysqldump MTR failed: galera SST with mysqldump failed -galera_unicode_identifiers : MDEV-26500 : galera.galera_unicode_identifiers MTR failed: InnoDB: innodb_fatal_semaphore_wait_threshold was exceeded for dict_sys.mutex -galera_var_dirty_reads : MDEV-25615 Galera test failure on galera_var_dirty_reads -galera_var_ignore_apply_errors : MDEV-26770 galera_var_ignore_apply_errors fails Server did not transition to READY state +galera_sst_encrypted : MDEV-29876 Galera test failure on galera_sst_encrypted +MW-284 : MDEV-29861 Galera test case hangs +galera_binlog_checksum : MDEV-29861 Galera test case hangs +galera_var_notify_ssl_ipv6 : MDEV-29861 Galera test case hangs galera_var_node_address : MDEV-20485 Galera test failure -galera_var_notify_cmd : MDEV-21905 Galera test galera_var_notify_cmd causes hang -galera_var_notify_ssl_ipv6 : hangs after the merge of MDEV-27682 -galera_var_retry_autocommit: MDEV-18181 Galera test failure on galera.galera_var_retry_autocommit -galera_wsrep_provider_unset_set: wsrep_provider is read-only for security reasons -partition : MDEV-19958 Galera test failure on galera.partition -pxc-421: wsrep_provider is read-only for security reasons -query_cache: MDEV-15805 Test failure on galera.query_cache -versioning_trx_id: MDEV-18590: galera.versioning_trx_id: Test failure: mysqltest: Result content mismatch -galera_bf_abort_at_after_statement : Unstable -galera_bf_abort_shutdown : MDEV-29773 Assertion failure on sql/wsrep_mysqld.cc:2893 in wsrep_bf_abort_shutdown -galera.MW-284 : MDEV-29861: Galera test case hangs -galera.galera_binlog_checksum : MDEV-29861: Galera test case hangs -galera_var_notify_ssl_ipv6 : MDEV-29861: Galera test case hangs +MDEV-26575 : MDEV-29878 Galera test failure on MDEV-26575 +galera_bf_abort_shutdown : MDEV-29918 Assertion failure on galera_bf_abort_shutdown diff --git a/mysql-test/suite/galera/r/galera_bf_abort_shutdown.result b/mysql-test/suite/galera/r/galera_bf_abort_shutdown.result index 5707b68e190..5233ea6c63c 100644 --- a/mysql-test/suite/galera/r/galera_bf_abort_shutdown.result +++ b/mysql-test/suite/galera/r/galera_bf_abort_shutdown.result @@ -10,4 +10,6 @@ SET DEBUG_SYNC = 'wsrep_before_certification WAIT_FOR continue'; INSERT INTO t1 VALUES (1); connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2; connection node_1; +connection node_2a; +connection node_1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_gcache_recover_manytrx.result b/mysql-test/suite/galera/r/galera_gcache_recover_manytrx.result index faa85126f03..63b16165970 100644 --- a/mysql-test/suite/galera/r/galera_gcache_recover_manytrx.result +++ b/mysql-test/suite/galera/r/galera_gcache_recover_manytrx.result @@ -94,6 +94,7 @@ CALL insert_1m ();; connection node_1_insert_10m; CALL insert_10m ();; connection node_2; +call mtr.add_suppression("Error in Log_event::read_log_event():.*"); SET SESSION wsrep_sync_wait = 0; Killing server ... connection node_1; @@ -130,9 +131,11 @@ DROP PROCEDURE update_simple; DROP PROCEDURE insert_1k; DROP PROCEDURE insert_1m; connection node_1; +call mtr.add_suppression("Error in Log_event::read_log_event():.*"); CALL mtr.add_suppression("conflict state 7 after post commit"); CALL mtr.add_suppression("Skipped GCache ring buffer recovery"); include/assert_grep.inc [async IST sender starting to serve] connection node_2; +call mtr.add_suppression("Error in Log_event::read_log_event():.*"); CALL mtr.add_suppression("Skipped GCache ring buffer recovery"); include/assert_grep.inc [Recovering GCache ring buffer: found gapless sequence] diff --git a/mysql-test/suite/galera/r/galera_wsrep_new_cluster.result b/mysql-test/suite/galera/r/galera_wsrep_new_cluster.result index 244623d52b7..87f61e2be62 100644 --- a/mysql-test/suite/galera/r/galera_wsrep_new_cluster.result +++ b/mysql-test/suite/galera/r/galera_wsrep_new_cluster.result @@ -1,6 +1,22 @@ connection node_2; connection node_1; connection node_1; +connection node_2; +connection node_2; +Shutting down server ... +connection node_1; +connection node_2; +Cleaning grastate.dat file ... +Starting server ... +connection node_1; +connection node_2; +connection node_2; +Shutting down server ... +connection node_1; +connection node_2; +Cleaning grastate.dat file ... +Starting server ... +connection node_1; SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; VARIABLE_VALUE Primary @@ -38,3 +54,7 @@ VARIABLE_VALUE SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_state_comment'; VARIABLE_VALUE Synced +Shutting down server ... +Cleaning var directory ... +Starting server ... +connection node_1; diff --git a/mysql-test/suite/galera/r/partition.result b/mysql-test/suite/galera/r/partition.result index 2e16d06519c..9d649fcec69 100644 --- a/mysql-test/suite/galera/r/partition.result +++ b/mysql-test/suite/galera/r/partition.result @@ -57,7 +57,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `i` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) -) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 +) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`i`) (PARTITION `p1` VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION `p2` VALUES LESS THAN (20) ENGINE = InnoDB, @@ -67,7 +67,7 @@ Table Create Table p1 CREATE TABLE `p1` ( `i` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) -) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 +) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM test.t1; i 19 @@ -94,7 +94,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `i` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`i`) (PARTITION `p1` VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION `pMax` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) @@ -106,7 +106,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `i` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (`i`) (PARTITION `p1` VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION `pMax` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) diff --git a/mysql-test/suite/galera/r/query_cache.result b/mysql-test/suite/galera/r/query_cache.result deleted file mode 100644 index 5dabd38a982..00000000000 --- a/mysql-test/suite/galera/r/query_cache.result +++ /dev/null @@ -1,1758 +0,0 @@ -connection node_2; -connection node_1; - -# Execute FLUSH/RESET commands. -# On node-1 -connection node_1; -SET @query_cache_size_saved=@@GLOBAL.query_cache_size; -SET @query_cache_type_saved=@@GLOBAL.query_cache_type; -set GLOBAL query_cache_size=1355776; -flush query cache; -reset query cache; -flush status; -# On node-2 -connection node_2; -SET @query_cache_size_saved=@@GLOBAL.query_cache_size; -SET @query_cache_type_saved=@@GLOBAL.query_cache_type; -set GLOBAL query_cache_size=1355776; -flush query cache; -reset query cache; -flush status; -# On node-1 -connection node_1; -create table t1 (a int not null) engine=innodb; -insert into t1 values (1),(2),(3); -select * from t1; -a -1 -2 -3 -select * from t1; -a -1 -2 -3 -select sql_no_cache * from t1; -a -1 -2 -3 -select length(now()) from t1; -length(now()) -19 -19 -19 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 -# On node-2 -connection node_2; -select * from t1; -a -1 -2 -3 -select * from t1; -a -1 -2 -3 -select sql_no_cache * from t1; -a -1 -2 -3 -select length(now()) from t1; -length(now()) -19 -19 -19 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 -# On node-1 -connection node_1; -delete from t1 where a=1; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 -# On node-2 -connection node_2; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 -# On node-1 -connection node_1; -select * from t1; -a -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 -# On node-2 -connection node_2; -select * from t1; -a -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 -# On node-1 -connection node_1; -update t1 set a=1 where a=3; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 -# On node-2 -connection node_2; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 -# On node-1 -connection node_1; -select * from t1; -a -2 -1 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 -# On node-2 -connection node_2; -select * from t1; -a -2 -1 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 -# On node-1 -connection node_1; -drop table t1; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 -# On node-2 -connection node_2; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 1 - -# On node-1 -connection node_1; -create table t1 (a int not null) ENGINE=MyISAM; -insert into t1 values (1),(2),(3); -create table t2 (a int not null) ENGINE=MyISAM; -insert into t2 values (4),(5),(6); -create table t3 (a int not null) engine=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; -select * from t3; -a -1 -2 -3 -4 -5 -6 -select * from t3; -a -1 -2 -3 -4 -5 -6 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 2 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 2 -insert into t2 values (7); -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 2 -select * from t1; -a -1 -2 -3 -select * from t1; -a -1 -2 -3 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -insert into t3 values (8); -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -select * from t3; -a -1 -2 -3 -8 -4 -5 -6 -7 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -update t2 set a=9 where a=7; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -select * from t1; -a -1 -2 -3 -8 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -update t3 set a=10 where a=1; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -select * from t3; -a -10 -2 -3 -8 -4 -5 -6 -9 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -delete from t2 where a=9; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -select * from t1; -a -10 -2 -3 -8 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -delete from t3 where a=10; -select * from t3; -a -2 -3 -8 -4 -5 -6 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -# On node-2 -connection node_2; -select * from t3; -a -select * from t3; -a -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 4 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 2 -drop table t1, t2, t3; -# On node-1 -connection node_1; -set query_cache_type=demand; -create table t1 (a int not null) engine=innodb; -insert into t1 values (1),(2),(3); -select * from t1; -a -1 -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -select sql_cache * from t1 union select * from t1; -a -1 -2 -3 -set query_cache_type=2; -select sql_cache * from t1 union select * from t1; -a -1 -2 -3 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 4 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -set query_cache_type=on; -# On node-2 -connection node_2; -set query_cache_type=demand; -select * from t1; -a -1 -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 2 -select sql_cache * from t1 union select * from t1; -a -1 -2 -3 -set query_cache_type=2; -select sql_cache * from t1 union select * from t1; -a -1 -2 -3 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -set query_cache_type=on; -# On node-1 -connection node_1; -select sql_no_cache * from t1; -a -1 -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 4 -# On node-2 -connection node_2; -select sql_no_cache * from t1; -a -1 -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -drop table t1; -# On node-1 -connection node_1; -create table t1 (a text not null) engine=innodb; -select CONNECTION_ID() from t1; -CONNECTION_ID() -select FOUND_ROWS(); -FOUND_ROWS() -0 -select NOW() from t1; -NOW() -select CURDATE() from t1; -CURDATE() -select CURTIME() from t1; -CURTIME() -select DATABASE() from t1; -DATABASE() -select ENCRYPT("test") from t1; -ENCRYPT("test") -select LAST_INSERT_ID() from t1; -LAST_INSERT_ID() -select RAND() from t1; -RAND() -select UNIX_TIMESTAMP() from t1; -UNIX_TIMESTAMP() -select USER() from t1; -USER() -select CURRENT_USER() from t1; -CURRENT_USER() -select benchmark(1,1) from t1; -benchmark(1,1) -explain extended select benchmark(1,1) from t1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 -Warnings: -Note 1003 select benchmark(1,1) AS `benchmark(1,1)` from `test`.`t1` -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 4 -# On node-2 -connection node_2; -select CONNECTION_ID() from t1; -CONNECTION_ID() -select FOUND_ROWS(); -FOUND_ROWS() -0 -select NOW() from t1; -NOW() -select CURDATE() from t1; -CURDATE() -select CURTIME() from t1; -CURTIME() -select DATABASE() from t1; -DATABASE() -select ENCRYPT("test") from t1; -ENCRYPT("test") -select LAST_INSERT_ID() from t1; -LAST_INSERT_ID() -select RAND() from t1; -RAND() -select UNIX_TIMESTAMP() from t1; -UNIX_TIMESTAMP() -select USER() from t1; -USER() -select CURRENT_USER() from t1; -CURRENT_USER() -select benchmark(1,1) from t1; -benchmark(1,1) -explain extended select benchmark(1,1) from t1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 -Warnings: -Note 1003 select benchmark(1,1) AS `benchmark(1,1)` from `test`.`t1` -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -drop table t1; -# On node-1 -connection node_1; -create database mysqltest; -create table mysqltest.t1 (i int not null auto_increment, a int, primary key -(i)) engine=innodb; -insert into mysqltest.t1 values (1, 1); -select * from mysqltest.t1 where i is null; -i a -create table t1(a int) engine=innodb; -select * from t1; -a -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 4 -select * from mysqltest.t1; -i a -1 1 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 3 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 4 -# On node-2 -connection node_2; -select * from t1; -a -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -select * from mysqltest.t1; -i a -1 1 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -drop database mysqltest; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -drop table t1; -# On node-1 -connection node_1; -create table t1 (a char(1) not null collate koi8r_general_ci) engine=innodb; -insert into t1 values(_koi8r 0xc3); -set CHARACTER SET koi8r; -select * from t1; -a -à -set CHARACTER SET cp1251_koi8; -select * from t1; -a -ö -set CHARACTER SET DEFAULT; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 4 -# On node-2 -connection node_2; -set CHARACTER SET koi8r; -select * from t1; -a -à -set CHARACTER SET cp1251_koi8; -select * from t1; -a -ö -set CHARACTER SET DEFAULT; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -drop table t1; -# On node-1 -connection node_1; -create database if not exists mysqltest; -create table mysqltest.t1 (i int not null) engine=innodb; -create table t1 (i int not null) engine=innodb; -insert into mysqltest.t1 (i) values (1); -insert into t1 (i) values (2); -select * from t1; -i -2 -use mysqltest; -select * from t1; -i -1 -select * from t1; -i -1 -use test; -select * from t1; -i -2 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 6 -# On node-2 -connection node_2; -select * from t1; -i -2 -use mysqltest; -select * from t1; -i -1 -select * from t1; -i -1 -use test; -select * from t1; -i -2 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 5 -drop database mysqltest; -drop table t1; -# On node-1 -connection node_1; -create table t1 (i int not null) engine=innodb; -insert into t1 (i) values (1),(2),(3),(4); -select SQL_CALC_FOUND_ROWS * from t1 limit 2; -i -1 -2 -select FOUND_ROWS(); -FOUND_ROWS() -4 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 6 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -select * from t1 where i=1; -i -1 -select FOUND_ROWS(); -FOUND_ROWS() -1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 6 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -select SQL_CALC_FOUND_ROWS * from t1 limit 2; -i -1 -2 -select FOUND_ROWS(); -FOUND_ROWS() -4 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 7 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -select * from t1 where i=1; -i -1 -select FOUND_ROWS(); -FOUND_ROWS() -1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 8 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -# On node-2 -connection node_2; -select SQL_CALC_FOUND_ROWS * from t1 limit 2; -i -1 -2 -select FOUND_ROWS(); -FOUND_ROWS() -4 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 5 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -select * from t1 where i=1; -i -1 -select FOUND_ROWS(); -FOUND_ROWS() -1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 5 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -select SQL_CALC_FOUND_ROWS * from t1 limit 2; -i -1 -2 -select FOUND_ROWS(); -FOUND_ROWS() -4 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 6 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -select * from t1 where i=1; -i -1 -select FOUND_ROWS(); -FOUND_ROWS() -1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 7 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -drop table t1; -# On node-2 -connection node_2; -flush query cache; -reset query cache; -# On node-1 -connection node_1; -flush query cache; -reset query cache; -create table t1 (a int not null) ENGINE=MYISAM; -insert into t1 values (1),(2),(3); -select * from t1; -a -1 -2 -3 -select * from t1; -a -1 -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 9 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 21 -insert delayed into t1 values (4); -select a from t1; -a -1 -2 -3 -4 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 9 -# On node-2 -connection node_2; -select * from t1; -a -select * from t1; -a -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 8 -insert delayed into t1 values (4); -select a from t1; -a -4 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 8 -drop table t1; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 8 -# On node-1 -connection node_1; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 9 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 22 -# On node-2 -connection node_2; -show global variables like "query_cache_min_res_unit"; -Variable_name Value -query_cache_min_res_unit 4096 -set GLOBAL query_cache_min_res_unit=1001; -Warnings: -Warning 1292 Truncated incorrect query_cache_min_res_unit value: '1001' -show global variables like "query_cache_min_res_unit"; -Variable_name Value -query_cache_min_res_unit 1000 -# On node-1 -connection node_1; -show global variables like "query_cache_min_res_unit"; -Variable_name Value -query_cache_min_res_unit 4096 -set GLOBAL query_cache_min_res_unit=1001; -Warnings: -Warning 1292 Truncated incorrect query_cache_min_res_unit value: '1001' -show global variables like "query_cache_min_res_unit"; -Variable_name Value -query_cache_min_res_unit 1000 -create table t1 (a int not null) engine=innodb; -insert into t1 values (1),(2),(3); -create table t2 (a int not null) engine=innodb; -insert into t2 values (1),(2),(3); -select * from t1; -a -1 -2 -3 -select * from t1; -a -1 -2 -3 -select * from t2; -a -1 -2 -3 -select * from t2; -a -1 -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 24 -# On node-2 -connection node_2; -select * from t1; -a -1 -2 -3 -select * from t1; -a -1 -2 -3 -select * from t2; -a -1 -2 -3 -select * from t2; -a -1 -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 10 -drop table t1; -select a from t2; -a -1 -2 -3 -select a from t2; -a -1 -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 18 -set GLOBAL query_cache_min_res_unit=default; -show global variables like "query_cache_min_res_unit"; -Variable_name Value -query_cache_min_res_unit 4096 -# On node-1 -connection node_1; -select a from t2; -a -1 -2 -3 -select a from t2; -a -1 -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 25 -drop table t2; -set GLOBAL query_cache_min_res_unit=default; -show global variables like "query_cache_min_res_unit"; -Variable_name Value -query_cache_min_res_unit 4096 -# On node-1 -connection node_1; -create table t1 (a int not null) engine=innodb; -insert into t1 values (1); -select "aaa" from t1; -aaa -aaa -select "AAA" from t1; -AAA -AAA -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 27 -# On node-2 -connection node_2; -select "aaa" from t1; -aaa -aaa -select "AAA" from t1; -AAA -AAA -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 20 -drop table t1; -# On node-1 -connection node_1; -create table t1 (a int) engine=innodb; -set GLOBAL query_cache_size=1000; -Warnings: -Warning 1292 Truncated incorrect query_cache_size value: '1000' -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 0 -select * from t1; -a -set GLOBAL query_cache_size=1024; -Warnings: -Warning 1282 Query cache failed to set size 1024; new query cache size is 0 -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 0 -select * from t1; -a -set GLOBAL query_cache_size=10240; -Warnings: -Warning 1282 Query cache failed to set size 10240; new query cache size is 0 -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 0 -select * from t1; -a -set GLOBAL query_cache_size=20480; -Warnings: -Warning 1282 Query cache failed to set size 20480; new query cache size is 0 -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 0 -select * from t1; -a -set GLOBAL query_cache_size=40960; -Warnings: -Warning 1282 Query cache failed to set size 40960; new query cache size is 0 -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 0 -select * from t1; -a -set GLOBAL query_cache_size=51200; -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 51200 -select * from t1; -a -set GLOBAL query_cache_size=61440; -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 61440 -select * from t1; -a -set GLOBAL query_cache_size=81920; -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 81920 -select * from t1; -a -set GLOBAL query_cache_size=102400; -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 102400 -select * from t1; -a -# On node-2 -connection node_2; -set GLOBAL query_cache_size=1000; -Warnings: -Warning 1292 Truncated incorrect query_cache_size value: '1000' -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 0 -select * from t1; -a -set GLOBAL query_cache_size=1024; -Warnings: -Warning 1282 Query cache failed to set size 1024; new query cache size is 0 -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 0 -select * from t1; -a -set GLOBAL query_cache_size=10240; -Warnings: -Warning 1282 Query cache failed to set size 10240; new query cache size is 0 -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 0 -select * from t1; -a -set GLOBAL query_cache_size=20480; -Warnings: -Warning 1282 Query cache failed to set size 20480; new query cache size is 0 -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 0 -select * from t1; -a -set GLOBAL query_cache_size=40960; -Warnings: -Warning 1282 Query cache failed to set size 40960; new query cache size is 0 -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 0 -select * from t1; -a -set GLOBAL query_cache_size=51200; -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 51200 -select * from t1; -a -set GLOBAL query_cache_size=61440; -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 61440 -select * from t1; -a -set GLOBAL query_cache_size=81920; -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 81920 -select * from t1; -a -set GLOBAL query_cache_size=102400; -show global variables like "query_cache_size"; -Variable_name Value -query_cache_size 102400 -select * from t1; -a -drop table t1; -# On node-1 -connection node_1; -set GLOBAL query_cache_size=1048576; -create table t1 (i int not null) engine=innodb; -create table t2 (i int not null) engine=innodb; -select * from t1; -i -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -create temporary table t3 (i int not null); -select * from t2; -i -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -select * from t3; -i -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -update t1 set i=(select distinct 1 from (select * from t2) a); -drop table t3; -# On node-2 -connection node_2; -set GLOBAL query_cache_size=1048576; -select * from t1; -i -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -select * from t2; -i -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -drop table t1, t2; -# On node-1 -connection node_1; -use mysql; -select * from db; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -use test; -select * from mysql.db; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -# On node-1 -connection node_1; -create table t1(id int auto_increment primary key) engine=innodb; -insert into t1 values (1), (2), (3); -select * from t1; -id -1 -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -# On node-2 -connection node_2; -select * from t1; -id -1 -2 -3 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -# On node-1 -connection node_1; -alter table t1 rename to t2; -select * from t1; -ERROR 42S02: Table 'test.t1' doesn't exist -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -# On node-2 -connection node_2; -select * from t1; -ERROR 42S02: Table 'test.t1' doesn't exist -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -drop table t2; -# On node-1 -connection node_1; -create table t1 (word char(20) not null) engine=innodb; -select * from t1; -word -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -load data infile 'MYSQLTEST_VARDIR/std_data/words.dat' into table t1; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -# On node-2 -connection node_2; -select count(*) from t1; -count(*) -70 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -load data infile 'MYSQLTEST_VARDIR/std_data/words.dat' into table t1; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -select count(*) from t1; -count(*) -140 -drop table t1; -# -# INTO OUTFILE/DUMPFILE test -# -# On node-1 -connection node_1; -create table t1 (a int) engine=innodb; -insert into t1 values (1),(2),(3); -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -select * from t1 into outfile "query_cache.out.file"; -Warnings: -Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead -select * from t1 into outfile "query_cache.out.file"; -ERROR HY000: File 'query_cache.out.file' already exists -select * from t1 limit 1 into dumpfile "query_cache.dump.file"; -Warnings: -Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 36 -drop table t1; -# -# Test of SQL_SELECT_LIMIT -# -# On node-1 -connection node_1; -create table t1 (a int) engine=innodb; -insert into t1 values (1),(2); -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -select * from t1; -a -1 -2 -SET SQL_SELECT_LIMIT=1; -select * from t1; -a -1 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 38 -SET SQL_SELECT_LIMIT=DEFAULT; -# On node-2 -connection node_2; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -select * from t1; -a -1 -2 -SET SQL_SELECT_LIMIT=1; -select * from t1; -a -1 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -SET SQL_SELECT_LIMIT=DEFAULT; -drop table t1; -# -# WRITE LOCK & QC -# -# On node-1 -connection node_1; -create table t1 (a int not null) engine=innodb; -create table t2 (a int not null) engine=innodb; -set query_cache_wlock_invalidate=1; -create view v1 as select * from t1; -select * from t1; -a -select * from t2; -a -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 40 -lock table t1 write, t2 read; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -unlock table; -select * from t1; -a -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -lock table v1 write; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 41 -unlock table; -drop view v1; -set query_cache_wlock_invalidate=default; -# On node-2 -connection node_2; -set query_cache_wlock_invalidate=1; -create view v1 as select * from t1; -select * from t1; -a -select * from t2; -a -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -lock table t1 write, t2 read; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -unlock table; -select * from t1; -a -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -lock table v1 write; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 35 -unlock table; -drop view v1; -set query_cache_wlock_invalidate=default; -drop table t1,t2; -# -# Hiding real table stored in query cache by temporary table -# -# On node-1 -connection node_1; -create table t1 (id int primary key) engine=innodb; -insert into t1 values (1),(2),(3); -select * from t1; -id -1 -2 -3 -create temporary table t1 (a int not null auto_increment primary key); -select * from t1; -a -drop table t1; -drop table t1; -# On node-1 -connection node_1; -SET NAMES koi8r; -CREATE TABLE t1 (a char(1) character set koi8r) engine=innodb; -INSERT INTO t1 VALUES (_koi8r 0xc3),(_koi8r 0xc3); -SELECT a,'Â','â'='Â' FROM t1; -a  'â'='Â' -à  0 -à  0 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 43 -set collation_connection=koi8r_bin; -SELECT a,'Â','â'='Â' FROM t1; -a  'â'='Â' -à  0 -à  0 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 44 -set character_set_client=cp1251; -SELECT a,'Â','â'='Â' FROM t1; -a ç? 'ç?'='ç?' -à ç? 1 -à ç? 1 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 3 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 45 -set character_set_results=cp1251; -SELECT a,'Â','â'='Â' FROM t1; -a Ã? 'â'='Â' -ö Ã? 1 -ö Ã? 1 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 4 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 46 -SET NAMES default; -# On node-2 -connection node_2; -# -# Run select -# -SELECT a,'Â','â'='Â' FROM t1; -a  'â'='Â' -?  0 -?  0 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -set collation_connection=koi8r_bin; -SELECT a,'Â','â'='Â' FROM t1; -a ?? 'â'='Â' -? ?? 1 -? ?? 1 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 2 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 37 -set character_set_client=cp1251; -SELECT a,'Â','â'='Â' FROM t1; -a ?? '??'='?‚' -? ?? 1 -? ?? 1 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 3 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 38 -set character_set_results=cp1251; -SELECT a,'Â','â'='Â' FROM t1; -a Ã? 'â'='Â' -ö Ã? 1 -ö Ã? 1 -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 4 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 39 -drop table t1; -# -# Comments before command -# -# On node-1 -connection node_1; -create table t1 (a int) engine=innodb; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -/**/ select * from t1; -a -/**/ select * from t1; -a -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 13 -# On node-2 -connection node_2; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 11 -/**/ select * from t1; -a -/**/ select * from t1; -a -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 12 -drop table t1; -# -# Information schema & query cache test -# -# On node-1 -connection node_1; -set session query_cache_type = 2; -create table t1(a int) engine=innodb; -select table_name from information_schema.tables -where table_schema="test"; -table_name -t1 -drop table t1; -select table_name from information_schema.tables -where table_schema="test"; -table_name -set session query_cache_type = 1; -set global query_cache_size=1024*1024; -flush query cache; -create table t1 ( a int ) engine=myisam; -insert into t1 values (1); -select a from t1; -a -1 -select a from t1; -a -1 -show status like 'qcache_queries_in_cache'; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 14 -repair table t1; -Table Op Msg_type Msg_text -test.t1 repair status OK -show status like 'qcache_queries_in_cache'; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 14 -# On node-2 -connection node_2; -select a from t1; -a -select a from t1; -a -show status like 'qcache_queries_in_cache'; -Variable_name Value -Qcache_queries_in_cache 1 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 13 -repair table t1; -Table Op Msg_type Msg_text -test.t1 repair status OK -show status like 'qcache_queries_in_cache'; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 13 -drop table t1; -# Restore original settings. -# On node-1 -connection node_1; -SET GLOBAL query_cache_size=@query_cache_size_saved; -SET GLOBAL query_cache_type=@query_cache_type_saved; - -# On node-2 -connection node_2; -SET GLOBAL query_cache_size=@query_cache_size_saved; -SET GLOBAL query_cache_type=@query_cache_type_saved; -# End of test diff --git a/mysql-test/suite/galera/t/galera_bf_abort_shutdown.test b/mysql-test/suite/galera/t/galera_bf_abort_shutdown.test index 42f85ecf7c2..6eb1cd9f4b2 100644 --- a/mysql-test/suite/galera/t/galera_bf_abort_shutdown.test +++ b/mysql-test/suite/galera/t/galera_bf_abort_shutdown.test @@ -23,12 +23,22 @@ SET DEBUG_SYNC = 'wsrep_before_certification WAIT_FOR continue'; --send INSERT INTO t1 VALUES (1) --connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2 ---source include/restart_mysqld.inc +--source include/shutdown_mysqld.inc + +# On node_1, verify that the node has left the cluster. +--connection node_1 +--let $wait_condition = SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +--source include/wait_condition.inc + +--connection node_2a +--source include/start_mysqld.inc + +--connection node_1 +--let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +--source include/wait_condition.inc # Restore original auto_increment_offset values. --let $node_2=node_2a --source include/auto_increment_offset_restore.inc ---connection node_1 - DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test b/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test index 08165f30f7d..d92288b7881 100644 --- a/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test +++ b/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test @@ -126,6 +126,7 @@ DELIMITER ;| --send CALL insert_10m (); --connection node_2 +call mtr.add_suppression("Error in Log_event::read_log_event():.*"); SET SESSION wsrep_sync_wait = 0; # Make sure that node_2 is not killed while TOIs are applied. @@ -199,6 +200,7 @@ DROP PROCEDURE insert_1k; DROP PROCEDURE insert_1m; --connection node_1 +call mtr.add_suppression("Error in Log_event::read_log_event():.*"); CALL mtr.add_suppression("conflict state 7 after post commit"); # Warning happens when the cluster is started for the first time @@ -213,6 +215,7 @@ CALL mtr.add_suppression("Skipped GCache ring buffer recovery"); --source include/assert_grep.inc --connection node_2 +call mtr.add_suppression("Error in Log_event::read_log_event():.*"); CALL mtr.add_suppression("Skipped GCache ring buffer recovery"); # Confirm that gcache recovery took place diff --git a/mysql-test/suite/galera/t/galera_var_notify_ssl_ipv6.cnf b/mysql-test/suite/galera/t/galera_var_notify_ssl_ipv6.cnf index ce121d20e03..db6f36605b7 100644 --- a/mysql-test/suite/galera/t/galera_var_notify_ssl_ipv6.cnf +++ b/mysql-test/suite/galera/t/galera_var_notify_ssl_ipv6.cnf @@ -7,14 +7,14 @@ ssl-ca=@ENV.MYSQL_TEST_DIR/std_data/cacert.pem bind-address=:: [mysqld.1] -wsrep_provider_options='base_port=@mysqld.1.#galera_port;gmcast.listen_addr=tcp://[::]:@mysqld.1.#galera_port;ist.recv_addr=[::1]:@mysqld.1.#ist_port;gcache.size=1;pc.ignore_sb=true' +wsrep_provider_options='base_host=[::1];base_port=@mysqld.1.#galera_port;gmcast.listen_addr=tcp://[::]:@mysqld.1.#galera_port;ist.recv_addr=[::1]:@mysqld.1.#ist_port;repl.causal_read_timeout=PT90S;evs.suspect_timeout=PT10S;evs.inactive_timeout=PT30S;evs.install_timeout=PT15S;gcache.size=10M' wsrep_node_incoming_address='[::1]:@mysqld.1.port' -wsrep_node_address=::1 +wsrep_node_address=[::1]:@mysqld.1.#galera_port wsrep_sst_receive_address='[::1]:@mysqld.1.#sst_port' [mysqld.2] wsrep_cluster_address='gcomm://[::1]:@mysqld.1.#galera_port' -wsrep_provider_options='base_port=@mysqld.2.#galera_port;gmcast.listen_addr=tcp://[::]:@mysqld.2.#galera_port;ist.recv_addr=[::1]:@mysqld.2.#ist_port;gcache.size=1;pc.ignore_sb=true' -wsrep_node_address=::1 +wsrep_provider_options='base_host=[::1];base_port=@mysqld.2.#galera_port;gmcast.listen_addr=tcp://[::]:@mysqld.2.#galera_port;ist.recv_addr=[::1]:@mysqld.2.#ist_port;repl.causal_read_timeout=PT90S;evs.suspect_timeout=PT10S;evs.inactive_timeout=PT30S;evs.install_timeout=PT15S;gcache.size=10M' wsrep_node_incoming_address='[::1]:@mysqld.2.port' +wsrep_node_address=[::1]:@mysqld.2.#galera_port wsrep_sst_receive_address='[::1]:@mysqld.2.#sst_port' diff --git a/mysql-test/suite/galera/t/galera_wsrep_new_cluster-master.opt b/mysql-test/suite/galera/t/galera_wsrep_new_cluster-master.opt deleted file mode 100644 index c31150c46af..00000000000 --- a/mysql-test/suite/galera/t/galera_wsrep_new_cluster-master.opt +++ /dev/null @@ -1 +0,0 @@ ---wsrep-new-cluster diff --git a/mysql-test/suite/galera/t/galera_wsrep_new_cluster.test b/mysql-test/suite/galera/t/galera_wsrep_new_cluster.test index 84b6c032fc6..3fff51fa26d 100644 --- a/mysql-test/suite/galera/t/galera_wsrep_new_cluster.test +++ b/mysql-test/suite/galera/t/galera_wsrep_new_cluster.test @@ -6,6 +6,62 @@ --source include/galera_cluster.inc +# Save original auto_increment_offset values. +--let $node_1=node_1 +--let $node_2=node_2 +--source include/auto_increment_offset_save.inc + +--connection node_2 +--echo Shutting down server ... +--source include/shutdown_mysqld.inc + +--connection node_1 + +--let $wait_condition = SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' +--source include/wait_condition.inc + +--connection node_2 + +# +# Delete grastate.dat with safe_to_bootstrap: 0 +# +--echo Cleaning grastate.dat file ... +--remove_file $MYSQLTEST_VARDIR/mysqld.2/data/grastate.dat + +--echo Starting server ... +--let $restart_noprint=2 +--let $start_mysqld_params="--wsrep-new-cluster" +--source include/start_mysqld.inc +--source include/wait_until_ready.inc + +# Save original auto_increment_offset values. +--let $node_1=node_1 +--let $node_2=node_2 +--source include/auto_increment_offset_save.inc + +--connection node_2 +--echo Shutting down server ... +--source include/shutdown_mysqld.inc + +--connection node_1 + +--let $wait_condition = SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' +--source include/wait_condition.inc + +--connection node_2 + +# +# Delete grastate.dat with safe_to_bootstrap: 0 +# +--echo Cleaning grastate.dat file ... +--remove_file $MYSQLTEST_VARDIR/mysqld.2/data/grastate.dat + +--echo Starting server ... +--let $restart_noprint=2 +--let $start_mysqld_params="--wsrep-new-cluster" +--source include/start_mysqld.inc +--source include/wait_until_ready.inc + --connection node_1 --let $wait_condition = SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; @@ -37,3 +93,29 @@ SELECT (VARIABLE_VALUE = 0 OR VARIABLE_VALUE = 1 ) FROM INFORMATION_SCHEMA.GLOBA SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_ready'; SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_state'; SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_state_comment'; + +--echo Shutting down server ... +--source include/shutdown_mysqld.inc + +# +# Force SST +# +--echo Cleaning var directory ... +--remove_file $MYSQLTEST_VARDIR/mysqld.2/data/grastate.dat +--remove_files_wildcard $MYSQLTEST_VARDIR/mysqld.2/data/mtr +--remove_files_wildcard $MYSQLTEST_VARDIR/mysqld.2/data/performance_schema +--remove_files_wildcard $MYSQLTEST_VARDIR/mysqld.2/data/test +--remove_files_wildcard $MYSQLTEST_VARDIR/mysqld.2/data/mysql +--remove_files_wildcard $MYSQLTEST_VARDIR/mysqld.2/data + +--echo Starting server ... +--let $start_mysqld_params= +--source include/start_mysqld.inc +--source include/wait_until_ready.inc + +--connection node_1 + +--let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' +--source include/wait_condition.inc + +--source include/auto_increment_offset_restore.inc diff --git a/mysql-test/suite/galera/t/query_cache.cnf b/mysql-test/suite/galera/t/query_cache.cnf deleted file mode 100644 index 4a82e9fa037..00000000000 --- a/mysql-test/suite/galera/t/query_cache.cnf +++ /dev/null @@ -1,9 +0,0 @@ -!include ../galera_2nodes.cnf - -[mysqld.1] -query_cache_type=1 - -[mysqld.2] -query_cache_type=1 - - diff --git a/mysql-test/suite/galera/t/query_cache.test b/mysql-test/suite/galera/t/query_cache.test deleted file mode 100644 index aeec67fb576..00000000000 --- a/mysql-test/suite/galera/t/query_cache.test +++ /dev/null @@ -1,1112 +0,0 @@ ---source include/galera_cluster.inc ---source include/have_innodb.inc ---source include/have_query_cache.inc - ---echo ---echo # Execute FLUSH/RESET commands. ---echo # On node-1 ---connection node_1 -SET @query_cache_size_saved=@@GLOBAL.query_cache_size; -SET @query_cache_type_saved=@@GLOBAL.query_cache_type; -set GLOBAL query_cache_size=1355776; -flush query cache; # This crashed in some versions -reset query cache; -flush status; - ---echo # On node-2 ---connection node_2 -SET @query_cache_size_saved=@@GLOBAL.query_cache_size; -SET @query_cache_type_saved=@@GLOBAL.query_cache_type; -set GLOBAL query_cache_size=1355776; -flush query cache; # This crashed in some versions -reset query cache; -flush status; - -# -# INSERT/UPDATE/DELETE/DROP/SELECT -# - ---echo # On node-1 ---connection node_1 -create table t1 (a int not null) engine=innodb; -insert into t1 values (1),(2),(3); -select * from t1; -select * from t1; -select sql_no_cache * from t1; -select length(now()) from t1; - -show status like "Qcache_queries_in_cache"; -show status like "Qcache_inserts"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 - -select * from t1; -select * from t1; -select sql_no_cache * from t1; -select length(now()) from t1; - -show status like "Qcache_queries_in_cache"; -show status like "Qcache_inserts"; -show status like "Qcache_hits"; - -# DELETE should invalidate cache on both the nodes. ---echo # On node-1 ---connection node_1 -delete from t1 where a=1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - -# Add a SELECT to the cache. ---echo # On node-1 ---connection node_1 -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - -# UPDATE should invalidate cache on both the nodes. ---echo # On node-1 ---connection node_1 -update t1 set a=1 where a=3; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - -# Add a SELECT to the cache. ---echo # On node-1 ---connection node_1 -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - -# DROP should invalidate cache on both the nodes. ---echo # On node-1 ---connection node_1 -drop table t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# MERGE TABLES with INSERT/UPDATE and DELETE -# ---echo ---echo # On node-1 ---connection node_1 -create table t1 (a int not null) ENGINE=MyISAM; -insert into t1 values (1),(2),(3); -create table t2 (a int not null) ENGINE=MyISAM; -insert into t2 values (4),(5),(6); -create table t3 (a int not null) engine=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; -# insert -select * from t3; -select * from t3; -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -insert into t2 values (7); -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select * from t1; -select * from t1; -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -insert into t3 values (8); -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -# update -select * from t3; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -update t2 set a=9 where a=7; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -update t3 set a=10 where a=1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -#delete -select * from t3; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -delete from t2 where a=9; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -delete from t3 where a=10; -select * from t3; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -# MERGE table, expect no records. -select * from t3; -select * from t3; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_inserts"; -show status like "Qcache_hits"; - -drop table t1, t2, t3; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# SELECT SQL_CACHE ... -# ---echo # On node-1 ---connection node_1 -set query_cache_type=demand; -create table t1 (a int not null) engine=innodb; -insert into t1 values (1),(2),(3); -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select sql_cache * from t1 union select * from t1; -set query_cache_type=2; -select sql_cache * from t1 union select * from t1; -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; -set query_cache_type=on; - ---echo # On node-2 ---connection node_2 -set query_cache_type=demand; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select sql_cache * from t1 union select * from t1; -set query_cache_type=2; -select sql_cache * from t1 union select * from t1; -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; -set query_cache_type=on; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# SELECT SQL_NO_CACHE -# ---echo # On node-1 ---connection node_1 -select sql_no_cache * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; ---echo # On node-2 ---connection node_2 -select sql_no_cache * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# Check that queries that uses NOW(), LAST_INSERT_ID()... are not cached. -# ---echo # On node-1 ---connection node_1 -create table t1 (a text not null) engine=innodb; -select CONNECTION_ID() from t1; -select FOUND_ROWS(); -select NOW() from t1; -select CURDATE() from t1; -select CURTIME() from t1; -select DATABASE() from t1; -select ENCRYPT("test") from t1; -select LAST_INSERT_ID() from t1; -select RAND() from t1; -select UNIX_TIMESTAMP() from t1; -select USER() from t1; -select CURRENT_USER() from t1; -select benchmark(1,1) from t1; -explain extended select benchmark(1,1) from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; ---echo # On node-2 ---connection node_2 -select CONNECTION_ID() from t1; -select FOUND_ROWS(); -select NOW() from t1; -select CURDATE() from t1; -select CURTIME() from t1; -select DATABASE() from t1; -select ENCRYPT("test") from t1; -select LAST_INSERT_ID() from t1; -select RAND() from t1; -select UNIX_TIMESTAMP() from t1; -select USER() from t1; -select CURRENT_USER() from t1; -select benchmark(1,1) from t1; -explain extended select benchmark(1,1) from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# Non-cachable ODBC work around (and prepare cache for drop database) -# ---echo # On node-1 ---connection node_1 -create database mysqltest; -create table mysqltest.t1 (i int not null auto_increment, a int, primary key - (i)) engine=innodb; -insert into mysqltest.t1 values (1, 1); -select * from mysqltest.t1 where i is null; -create table t1(a int) engine=innodb; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select * from mysqltest.t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select * from mysqltest.t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -# -# drop db -# -drop database mysqltest; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# Charset conversion (cp1251_koi8 always present) -# Note: Queries using different default character sets are cached separately. -# ---echo # On node-1 ---connection node_1 -create table t1 (a char(1) not null collate koi8r_general_ci) engine=innodb; -insert into t1 values(_koi8r 0xc3); -set CHARACTER SET koi8r; -select * from t1; -set CHARACTER SET cp1251_koi8; -select * from t1; -set CHARACTER SET DEFAULT; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -set CHARACTER SET koi8r; -select * from t1; -set CHARACTER SET cp1251_koi8; -select * from t1; -set CHARACTER SET DEFAULT; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# Same tables in different dbs -# ---echo # On node-1 ---connection node_1 -create database if not exists mysqltest; -create table mysqltest.t1 (i int not null) engine=innodb; -create table t1 (i int not null) engine=innodb; -insert into mysqltest.t1 (i) values (1); -insert into t1 (i) values (2); - -select * from t1; -use mysqltest; -select * from t1; -select * from t1; -use test; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -select * from t1; -use mysqltest; -select * from t1; -select * from t1; -use test; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -drop database mysqltest; -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# FOUND_ROWS() -# ---echo # On node-1 ---connection node_1 -create table t1 (i int not null) engine=innodb; -insert into t1 (i) values (1),(2),(3),(4); - -select SQL_CALC_FOUND_ROWS * from t1 limit 2; -select FOUND_ROWS(); -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; - -select * from t1 where i=1; -select FOUND_ROWS(); -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; - -select SQL_CALC_FOUND_ROWS * from t1 limit 2; -select FOUND_ROWS(); -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; - -select * from t1 where i=1; -select FOUND_ROWS(); -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; - ---echo # On node-2 ---connection node_2 -select SQL_CALC_FOUND_ROWS * from t1 limit 2; -select FOUND_ROWS(); -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; - -select * from t1 where i=1; -select FOUND_ROWS(); -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; - -select SQL_CALC_FOUND_ROWS * from t1 limit 2; -select FOUND_ROWS(); -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; - -select * from t1 where i=1; -select FOUND_ROWS(); -show status like "Qcache_hits"; -show status like "Qcache_queries_in_cache"; - -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# Test insert delayed (MYISAM) -# - ---echo # On node-2 ---connection node_2 -flush query cache; -reset query cache; - ---echo # On node-1 ---connection node_1 -flush query cache; -reset query cache; - -create table t1 (a int not null) ENGINE=MYISAM; -insert into t1 values (1),(2),(3); -select * from t1; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -insert delayed into t1 values (4); ---sleep 5 # Wait for insert delayed to be executed. -select a from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -select * from t1; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -insert delayed into t1 values (4); ---sleep 5 # Wait for insert delayed to be executed. -select a from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - -drop table t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-1 ---connection node_1 -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; - -# -# Test of min result data unit size changing -# ---echo # On node-2 ---connection node_2 -show global variables like "query_cache_min_res_unit"; -set GLOBAL query_cache_min_res_unit=1001; -show global variables like "query_cache_min_res_unit"; - ---echo # On node-1 ---connection node_1 -show global variables like "query_cache_min_res_unit"; -set GLOBAL query_cache_min_res_unit=1001; -show global variables like "query_cache_min_res_unit"; -create table t1 (a int not null) engine=innodb; -insert into t1 values (1),(2),(3); -create table t2 (a int not null) engine=innodb; -insert into t2 values (1),(2),(3); -select * from t1; -select * from t1; -select * from t2; -select * from t2; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; - ---echo # On node-2 ---connection node_2 -select * from t1; -select * from t1; -select * from t2; -select * from t2; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -drop table t1; -select a from t2; -select a from t2; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -set GLOBAL query_cache_min_res_unit=default; -show global variables like "query_cache_min_res_unit"; - ---echo # On node-1 ---connection node_1 -select a from t2; -select a from t2; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -drop table t2; -set GLOBAL query_cache_min_res_unit=default; -show global variables like "query_cache_min_res_unit"; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# Case sensitive test -# ---echo # On node-1 ---connection node_1 -create table t1 (a int not null) engine=innodb; -insert into t1 values (1); -select "aaa" from t1; -select "AAA" from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; - ---echo # On node-2 ---connection node_2 -select "aaa" from t1; -select "AAA" from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# Test of query cache resizing -# ---echo # On node-1 ---connection node_1 -create table t1 (a int) engine=innodb; -set GLOBAL query_cache_size=1000; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=1024; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=10240; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=20480; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=40960; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=51200; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=61440; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=81920; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=102400; -show global variables like "query_cache_size"; -select * from t1; - ---echo # On node-2 ---connection node_2 -set GLOBAL query_cache_size=1000; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=1024; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=10240; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=20480; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=40960; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=51200; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=61440; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=81920; -show global variables like "query_cache_size"; -select * from t1; -set GLOBAL query_cache_size=102400; -show global variables like "query_cache_size"; -select * from t1; - -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# Temporary tables (ignored by Galera) -# ---echo # On node-1 ---connection node_1 -set GLOBAL query_cache_size=1048576; -create table t1 (i int not null) engine=innodb; -create table t2 (i int not null) engine=innodb; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -create temporary table t3 (i int not null); -select * from t2; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select * from t3; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -update t1 set i=(select distinct 1 from (select * from t2) a); -drop table t3; - ---echo # On node-2 ---connection node_2 -set GLOBAL query_cache_size=1048576; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select * from t2; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - -drop table t1, t2; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# System databse test (no need to perform it on node_2) -# Note: Queries on system tables are not cached. -# ---echo # On node-1 ---connection node_1 -use mysql; -disable_result_log; -select * from db; -enable_result_log; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -use test; -disable_result_log; -select * from mysql.db; -enable_result_log; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - -# -# Simple rename test -# ---echo # On node-1 ---connection node_1 -create table t1(id int auto_increment primary key) engine=innodb; -insert into t1 values (1), (2), (3); -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-1 ---connection node_1 -alter table t1 rename to t2; ---error ER_NO_SUCH_TABLE -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 ---error ER_NO_SUCH_TABLE -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -drop table t2; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# Load data invalidation test -# ---echo # On node-1 ---connection node_1 -create table t1 (word char(20) not null) engine=innodb; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; ---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR -eval load data infile '$MYSQLTEST_VARDIR/std_data/words.dat' into table t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; ---let $wait_condition = SELECT COUNT(*) = 70 FROM t1; ---source include/wait_condition.inc - ---echo # On node-2 ---connection node_2 ---let $wait_condition = SELECT COUNT(*) = 70 FROM t1; ---source include/wait_condition.inc -select count(*) from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; ---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR -eval load data infile '$MYSQLTEST_VARDIR/std_data/words.dat' into table t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select count(*) from t1; - -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - ---echo # ---echo # INTO OUTFILE/DUMPFILE test ---echo # ---echo # On node-1 ---connection node_1 -create table t1 (a int) engine=innodb; -insert into t1 values (1),(2),(3); -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select * from t1 into outfile "query_cache.out.file"; ---error ER_FILE_EXISTS_ERROR -select * from t1 into outfile "query_cache.out.file"; -select * from t1 limit 1 into dumpfile "query_cache.dump.file"; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -drop table t1; -let $datadir=`select @@datadir`; ---remove_file $datadir/test/query_cache.dump.file ---remove_file $datadir/test/query_cache.out.file - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - ---echo # ---echo # Test of SQL_SELECT_LIMIT ---echo # ---echo # On node-1 ---connection node_1 -create table t1 (a int) engine=innodb; -insert into t1 values (1),(2); -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select * from t1; -SET SQL_SELECT_LIMIT=1; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -SET SQL_SELECT_LIMIT=DEFAULT; - ---echo # On node-2 ---connection node_2 -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -select * from t1; -SET SQL_SELECT_LIMIT=1; -select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -SET SQL_SELECT_LIMIT=DEFAULT; - -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - ---echo # ---echo # WRITE LOCK & QC ---echo # ---echo # On node-1 ---connection node_1 -create table t1 (a int not null) engine=innodb; -create table t2 (a int not null) engine=innodb; - -set query_cache_wlock_invalidate=1; -create view v1 as select * from t1; -select * from t1; -select * from t2; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -lock table t1 write, t2 read; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -unlock table; -select * from t1; -# Implicit locking of t1 does not invalidate QC -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -lock table v1 write; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -unlock table; -drop view v1; -set query_cache_wlock_invalidate=default; - ---echo # On node-2 ---connection node_2 -set query_cache_wlock_invalidate=1; -create view v1 as select * from t1; -select * from t1; -select * from t2; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -lock table t1 write, t2 read; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -unlock table; -select * from t1; -# Implicit locking of t1 does not invalidate QC -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -lock table v1 write; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -unlock table; -drop view v1; -set query_cache_wlock_invalidate=default; - -drop table t1,t2; - ---echo # ---echo # Hiding real table stored in query cache by temporary table ---echo # ---echo # On node-1 ---connection node_1 -create table t1 (id int primary key) engine=innodb; -insert into t1 values (1),(2),(3); -select * from t1; -create temporary table t1 (a int not null auto_increment primary key); -select * from t1; -drop table t1; -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - -# -# Test character set related variables: -# character_set_result -# character_set_client -# charactet_set_connection/collation_connection -# If at least one of the above variables has changed, -# the cached query can't be reused. In the below test -# absolutely the same query is used several times, -# SELECT should fetch different results for every instance. -# No hits should be produced. -# New cache entry should appear for every SELECT. -# - ---echo # On node-1 ---connection node_1 -SET NAMES koi8r; -CREATE TABLE t1 (a char(1) character set koi8r) engine=innodb; -INSERT INTO t1 VALUES (_koi8r 0xc3),(_koi8r 0xc3); -# -# Run select -# -SELECT a,'Â','â'='Â' FROM t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -# -# Change collation_connection and run the same query again -# -set collation_connection=koi8r_bin; -SELECT a,'Â','â'='Â' FROM t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -# -# Now change character_set_client and run the same query again -# -set character_set_client=cp1251; -SELECT a,'Â','â'='Â' FROM t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -# -# And finally change character_set_results and run the same query again -# -set character_set_results=cp1251; -SELECT a,'Â','â'='Â' FROM t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -SET NAMES default; - ---echo # On node-2 ---connection node_2 - ---echo # ---echo # Run select ---echo # - -SELECT a,'Â','â'='Â' FROM t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -# -# Change collation_connection and run the same query again -# -set collation_connection=koi8r_bin; -SELECT a,'Â','â'='Â' FROM t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -# -# Now change character_set_client and run the same query again -# -set character_set_client=cp1251; -SELECT a,'Â','â'='Â' FROM t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; -# -# And finally change character_set_results and run the same query again -# -set character_set_results=cp1251; -SELECT a,'Â','â'='Â' FROM t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -show status like "Qcache_inserts"; - -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - ---echo # ---echo # Comments before command ---echo # - ---echo # On node-1 ---connection node_1 -create table t1 (a int) engine=innodb; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -/**/ select * from t1; -/**/ select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -/**/ select * from t1; -/**/ select * from t1; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; - -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - ---echo # ---echo # Information schema & query cache test ---echo # - ---echo # On node-1 ---connection node_1 -set session query_cache_type = 2; -create table t1(a int) engine=innodb; -select table_name from information_schema.tables -where table_schema="test"; -drop table t1; -select table_name from information_schema.tables -where table_schema="test"; -# Bug #8480: REPAIR TABLE needs to flush the table from the query cache -set session query_cache_type = 1; -set global query_cache_size=1024*1024; -flush query cache; -create table t1 ( a int ) engine=myisam; # myisam for repair tables -insert into t1 values (1); -select a from t1; -select a from t1; -show status like 'qcache_queries_in_cache'; -show status like "Qcache_hits"; -repair table t1; -show status like 'qcache_queries_in_cache'; -show status like "Qcache_hits"; - ---echo # On node-2 ---connection node_2 -select a from t1; -select a from t1; -show status like 'qcache_queries_in_cache'; -show status like "Qcache_hits"; -repair table t1; -show status like 'qcache_queries_in_cache'; -show status like "Qcache_hits"; -drop table t1; - -# -# RESET QUERY CACHE -# ---source include/reset_query_cache.inc - ---echo # Restore original settings. ---echo # On node-1 ---connection node_1 -SET GLOBAL query_cache_size=@query_cache_size_saved; -SET GLOBAL query_cache_type=@query_cache_type_saved; ---echo ---echo # On node-2 ---connection node_2 -SET GLOBAL query_cache_size=@query_cache_size_saved; -SET GLOBAL query_cache_type=@query_cache_type_saved; - ---echo # End of test |