diff options
Diffstat (limited to 'mysql-test/suite')
10 files changed, 249 insertions, 134 deletions
diff --git a/mysql-test/suite/encryption/r/innodb_encryption.result b/mysql-test/suite/encryption/r/innodb_encryption.result index 4ede82ebd38..3b1552be4be 100644 --- a/mysql-test/suite/encryption/r/innodb_encryption.result +++ b/mysql-test/suite/encryption/r/innodb_encryption.result @@ -19,7 +19,7 @@ innodb_system # Success! # Now turn off encryption and wait for threads to decrypt everything SET GLOBAL innodb_encrypt_tables = off; -# Wait max 10 min for key encryption threads to encrypt all spaces +# Wait max 10 min for key encryption threads to decrypt all spaces SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; NAME diff --git a/mysql-test/suite/encryption/t/innodb_encryption.test b/mysql-test/suite/encryption/t/innodb_encryption.test index 1c8d200458a..2b0b2b8d7fb 100644 --- a/mysql-test/suite/encryption/t/innodb_encryption.test +++ b/mysql-test/suite/encryption/t/innodb_encryption.test @@ -33,7 +33,7 @@ AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NA --echo # Now turn off encryption and wait for threads to decrypt everything SET GLOBAL innodb_encrypt_tables = off; ---echo # Wait max 10 min for key encryption threads to encrypt all spaces +--echo # Wait max 10 min for key encryption threads to decrypt all spaces --let $wait_timeout= 600 --let $wait_condition=SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; --source include/wait_condition.inc diff --git a/mysql-test/suite/galera/r/galera_schema.result b/mysql-test/suite/galera/r/galera_schema.result new file mode 100644 index 00000000000..24a4099c94d --- /dev/null +++ b/mysql-test/suite/galera/r/galera_schema.result @@ -0,0 +1,114 @@ +connection node_2; +connection node_1; +CREATE TABLE IF NOT EXISTS wsrep_cluster +( +cluster_uuid CHAR(36) PRIMARY KEY, +view_id BIGINT NOT NULL, +view_seqno BIGINT NOT NULL, +protocol_version INT NOT NULL, +capabilities INT NOT NULL +) ENGINE=InnoDB; +CREATE TABLE IF NOT EXISTS wsrep_cluster_members +( +node_uuid CHAR(36) PRIMARY KEY, +cluster_uuid CHAR(36) NOT NULL, +node_name CHAR(32) NOT NULL, +node_incoming_address VARCHAR(256) NOT NULL +) ENGINE=InnoDB; +CREATE TABLE IF NOT EXISTS wsrep_cluster_members_history +( +node_uuid CHAR(36) PRIMARY KEY, +cluster_uuid CHAR(36) NOT NULL, +last_view_id BIGINT NOT NULL, +last_view_seqno BIGINT NOT NULL, +node_name CHAR(32) NOT NULL, +node_incoming_address VARCHAR(256) NOT NULL +) ENGINE=InnoDB; +CREATE TABLE IF NOT EXISTS wsrep_streaming_log +( +node_uuid CHAR(36), +trx_id BIGINT, +seqno BIGINT, +flags INT NOT NULL, +frag LONGBLOB NOT NULL, +PRIMARY KEY (node_uuid, trx_id, seqno) +) ENGINE=InnoDB; +DELETE FROM wsrep_cluster; +DELETE FROM wsrep_cluster_members; +ALTER TABLE wsrep_cluster STATS_PERSISTENT=0; +ALTER TABLE wsrep_cluster_members STATS_PERSISTENT=0; +ALTER TABLE wsrep_cluster_members_history STATS_PERSISTENT=0; +ALTER TABLE wsrep_streaming_log STATS_PERSISTENT=0; +SHOW CREATE TABLE wsrep_cluster; +Table Create Table +wsrep_cluster CREATE TABLE `wsrep_cluster` ( + `cluster_uuid` char(36) NOT NULL, + `view_id` bigint(20) NOT NULL, + `view_seqno` bigint(20) NOT NULL, + `protocol_version` int(11) NOT NULL, + `capabilities` int(11) NOT NULL, + PRIMARY KEY (`cluster_uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE wsrep_cluster_members; +Table Create Table +wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( + `node_uuid` char(36) NOT NULL, + `cluster_uuid` char(36) NOT NULL, + `node_name` char(32) NOT NULL, + `node_incoming_address` varchar(256) NOT NULL, + PRIMARY KEY (`node_uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE wsrep_cluster_members_history; +Table Create Table +wsrep_cluster_members_history CREATE TABLE `wsrep_cluster_members_history` ( + `node_uuid` char(36) NOT NULL, + `cluster_uuid` char(36) NOT NULL, + `last_view_id` bigint(20) NOT NULL, + `last_view_seqno` bigint(20) NOT NULL, + `node_name` char(32) NOT NULL, + `node_incoming_address` varchar(256) NOT NULL, + PRIMARY KEY (`node_uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE wsrep_streaming_log; +Table Create Table +wsrep_streaming_log CREATE TABLE `wsrep_streaming_log` ( + `node_uuid` char(36) NOT NULL, + `trx_id` bigint(20) NOT NULL, + `seqno` bigint(20) NOT NULL, + `flags` int(11) NOT NULL, + `frag` longblob NOT NULL, + PRIMARY KEY (`node_uuid`,`trx_id`,`seqno`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE mysql.wsrep_cluster; +Table Create Table +wsrep_cluster CREATE TABLE `wsrep_cluster` ( + `cluster_uuid` char(36) NOT NULL, + `view_id` bigint(20) NOT NULL, + `view_seqno` bigint(20) NOT NULL, + `protocol_version` int(11) NOT NULL, + `capabilities` int(11) NOT NULL, + PRIMARY KEY (`cluster_uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE mysql.wsrep_cluster_members; +Table Create Table +wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( + `node_uuid` char(36) NOT NULL, + `cluster_uuid` char(36) NOT NULL, + `node_name` char(32) NOT NULL, + `node_incoming_address` varchar(256) NOT NULL, + PRIMARY KEY (`node_uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +SHOW CREATE TABLE mysql.wsrep_streaming_log; +Table Create Table +wsrep_streaming_log CREATE TABLE `wsrep_streaming_log` ( + `node_uuid` char(36) NOT NULL, + `trx_id` bigint(20) NOT NULL, + `seqno` bigint(20) NOT NULL, + `flags` int(11) NOT NULL, + `frag` longblob NOT NULL, + PRIMARY KEY (`node_uuid`,`trx_id`,`seqno`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 +DROP TABLE wsrep_cluster; +DROP TABLE wsrep_cluster_members; +DROP TABLE wsrep_cluster_members_history; +DROP TABLE wsrep_streaming_log; diff --git a/mysql-test/suite/galera/t/galera_schema.test b/mysql-test/suite/galera/t/galera_schema.test new file mode 100644 index 00000000000..a3ee814c393 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_schema.test @@ -0,0 +1,61 @@ +--source include/galera_cluster.inc + +CREATE TABLE IF NOT EXISTS wsrep_cluster +( + cluster_uuid CHAR(36) PRIMARY KEY, + view_id BIGINT NOT NULL, + view_seqno BIGINT NOT NULL, + protocol_version INT NOT NULL, + capabilities INT NOT NULL +) ENGINE=InnoDB; + +CREATE TABLE IF NOT EXISTS wsrep_cluster_members +( + node_uuid CHAR(36) PRIMARY KEY, + cluster_uuid CHAR(36) NOT NULL, + node_name CHAR(32) NOT NULL, + node_incoming_address VARCHAR(256) NOT NULL +) ENGINE=InnoDB; + +CREATE TABLE IF NOT EXISTS wsrep_cluster_members_history +( + node_uuid CHAR(36) PRIMARY KEY, + cluster_uuid CHAR(36) NOT NULL, + last_view_id BIGINT NOT NULL, + last_view_seqno BIGINT NOT NULL, + node_name CHAR(32) NOT NULL, + node_incoming_address VARCHAR(256) NOT NULL +) ENGINE=InnoDB; + +CREATE TABLE IF NOT EXISTS wsrep_streaming_log +( + node_uuid CHAR(36), + trx_id BIGINT, + seqno BIGINT, + flags INT NOT NULL, + frag LONGBLOB NOT NULL, + PRIMARY KEY (node_uuid, trx_id, seqno) +) ENGINE=InnoDB; + +DELETE FROM wsrep_cluster; +DELETE FROM wsrep_cluster_members; + +ALTER TABLE wsrep_cluster STATS_PERSISTENT=0; +ALTER TABLE wsrep_cluster_members STATS_PERSISTENT=0; +ALTER TABLE wsrep_cluster_members_history STATS_PERSISTENT=0; +ALTER TABLE wsrep_streaming_log STATS_PERSISTENT=0; + +SHOW CREATE TABLE wsrep_cluster; +SHOW CREATE TABLE wsrep_cluster_members; +SHOW CREATE TABLE wsrep_cluster_members_history; +SHOW CREATE TABLE wsrep_streaming_log; + +SHOW CREATE TABLE mysql.wsrep_cluster; +SHOW CREATE TABLE mysql.wsrep_cluster_members; +#SHOW CREATE TABLE mysql.wsrep_cluster_members_history; +SHOW CREATE TABLE mysql.wsrep_streaming_log; + +DROP TABLE wsrep_cluster; +DROP TABLE wsrep_cluster_members; +DROP TABLE wsrep_cluster_members_history; +DROP TABLE wsrep_streaming_log; diff --git a/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema.result b/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema.result index ba01eab0e26..f51eb815cd5 100644 --- a/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema.result +++ b/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema.result @@ -14,7 +14,7 @@ wsrep_cluster CREATE TABLE `wsrep_cluster` ( `protocol_version` int(11) NOT NULL, `capabilities` int(11) NOT NULL, PRIMARY KEY (`cluster_uuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 SHOW CREATE TABLE mysql.wsrep_cluster_members; Table Create Table wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( @@ -23,7 +23,7 @@ wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( `node_name` char(32) NOT NULL, `node_incoming_address` varchar(256) NOT NULL, PRIMARY KEY (`node_uuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 SELECT COUNT(*) AS EXPECT_1 FROM mysql.wsrep_cluster; EXPECT_1 1 diff --git a/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema_init.result b/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema_init.result index 2a29afd62be..d9d3e817bed 100644 --- a/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema_init.result +++ b/mysql-test/suite/galera_3nodes/r/galera_wsrep_schema_init.result @@ -14,7 +14,7 @@ wsrep_cluster CREATE TABLE `wsrep_cluster` ( `protocol_version` int(11) NOT NULL, `capabilities` int(11) NOT NULL, PRIMARY KEY (`cluster_uuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 SHOW CREATE TABLE mysql.wsrep_cluster_members; Table Create Table wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( @@ -23,7 +23,7 @@ wsrep_cluster_members CREATE TABLE `wsrep_cluster_members` ( `node_name` char(32) NOT NULL, `node_incoming_address` varchar(256) NOT NULL, PRIMARY KEY (`node_uuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 SELECT @@sql_safe_updates; @@sql_safe_updates 1 diff --git a/mysql-test/suite/galera_sr/r/GCF-627.result b/mysql-test/suite/galera_sr/r/GCF-627.result index 65d8c95ad08..7cd2ab63ff3 100644 --- a/mysql-test/suite/galera_sr/r/GCF-627.result +++ b/mysql-test/suite/galera_sr/r/GCF-627.result @@ -2,7 +2,6 @@ connection node_2; connection node_1; connection node_1; CREATE TABLE t1 (f1 INTEGER, f2 VARCHAR(10)) ENGINE=InnoDB; -CREATE TABLE t2 (f1 INTEGER); SET SESSION wsrep_trx_fragment_size = 1; SET AUTOCOMMIT=OFF; START TRANSACTION; @@ -16,9 +15,10 @@ connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; INSERT INTO t1 VALUES (2); ERROR 42S02: Table 'test.t1' doesn't exist connection node_1; -SELECT * FROM mysql.wsrep_streaming_log; -node_uuid trx_id seqno flags frag +SELECT COUNT(*) FROM mysql.wsrep_streaming_log; +COUNT(*) +0 connection node_2; -SELECT * FROM mysql.wsrep_streaming_log; -node_uuid trx_id seqno flags frag -DROP TABLE t2; +SELECT COUNT(*) FROM mysql.wsrep_streaming_log; +COUNT(*) +0 diff --git a/mysql-test/suite/galera_sr/t/GCF-627.test b/mysql-test/suite/galera_sr/t/GCF-627.test index ad351eb9da6..6990c12314d 100644 --- a/mysql-test/suite/galera_sr/t/GCF-627.test +++ b/mysql-test/suite/galera_sr/t/GCF-627.test @@ -1,9 +1,7 @@ --source include/galera_cluster.inc ---source include/have_innodb.inc --connection node_1 CREATE TABLE t1 (f1 INTEGER, f2 VARCHAR(10)) ENGINE=InnoDB; -CREATE TABLE t2 (f1 INTEGER); SET SESSION wsrep_trx_fragment_size = 1; SET AUTOCOMMIT=OFF; @@ -25,11 +23,9 @@ INSERT INTO t1 VALUES (2); --let $wait_condition = SELECT COUNT(*) = 0 FROM mysql.wsrep_streaming_log --source include/wait_condition.inc -SELECT * FROM mysql.wsrep_streaming_log; +SELECT COUNT(*) FROM mysql.wsrep_streaming_log; --connection node_2 --let $wait_condition = SELECT COUNT(*) = 0 FROM mysql.wsrep_streaming_log --source include/wait_condition.inc -SELECT * FROM mysql.wsrep_streaming_log; - -DROP TABLE t2; +SELECT COUNT(*) FROM mysql.wsrep_streaming_log; diff --git a/mysql-test/suite/innodb/r/innodb_defrag_stats.result b/mysql-test/suite/innodb/r/innodb_defrag_stats.result index 598124e4ccb..b60f5f5c5a9 100644 --- a/mysql-test/suite/innodb/r/innodb_defrag_stats.result +++ b/mysql-test/suite/innodb/r/innodb_defrag_stats.result @@ -1,22 +1,8 @@ -DROP TABLE if exists t1; -select @@global.innodb_stats_persistent; -@@global.innodb_stats_persistent -0 -set global innodb_defragment_stats_accuracy = 20; +SET GLOBAL innodb_defragment_stats_accuracy = 20; +DELETE FROM mysql.innodb_index_stats; # Create table. CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; -# Populate data -INSERT INTO t1 VALUES(1, REPEAT('A', 256)); -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1_to_1024; # Not enough page splits to trigger persistent stats write yet. select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) = 0 @@ -27,7 +13,7 @@ count(stat_value) = 0 select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) = 0 1 -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1025_to_2048; # Persistent stats recorded. select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) > 0 @@ -39,6 +25,7 @@ select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like count(stat_value) > 0 1 # Delete some rows. +BEGIN; delete from t1 where a between 100 * 20 and 100 * 20 + 30; delete from t1 where a between 100 * 19 and 100 * 19 + 30; delete from t1 where a between 100 * 18 and 100 * 18 + 30; @@ -59,9 +46,7 @@ delete from t1 where a between 100 * 4 and 100 * 4 + 30; delete from t1 where a between 100 * 3 and 100 * 3 + 30; delete from t1 where a between 100 * 2 and 100 * 2 + 30; delete from t1 where a between 100 * 1 and 100 * 1 + 30; -# restart -# Server Restarted -# Confirm persistent stats still there after restart. +COMMIT; select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) > 0 1 @@ -74,9 +59,6 @@ count(stat_value) > 0 optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK -select sleep(2); -sleep(2) -0 select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) > 0 1 @@ -109,9 +91,6 @@ count(stat_value) > 0 1 # Table rename should cause stats rename. rename table t1 to t2; -select sleep(1); -sleep(1) -0 select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) = 0 1 @@ -130,48 +109,37 @@ count(stat_value) > 0 select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) > 0 1 -# Drop index should cause stats drop. +# Drop index should cause stats drop, but will not. drop index SECOND on t2; -select sleep(3); -sleep(3) -0 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 +SELECT stat_name, stat_value>0 FROM mysql.innodb_index_stats +WHERE table_name like '%t2%' AND index_name='SECOND'; +stat_name stat_value>0 +n_leaf_pages_defrag 1 +n_leaf_pages_reserved 1 +n_page_split 1 +n_pages_freed 1 +# +# MDEV-26636: Statistics must not be written for temporary tables +# +SET GLOBAL innodb_defragment_stats_accuracy = 1; +CREATE TEMPORARY TABLE t (a INT PRIMARY KEY, c CHAR(255) NOT NULL) +ENGINE=InnoDB; +INSERT INTO t SELECT seq, '' FROM seq_1_to_100; # restart -Server Restarted -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 +SELECT * FROM mysql.innodb_index_stats where table_name like '%t1%'; +database_name table_name index_name last_update stat_name stat_value sample_size stat_description +SELECT table_name, index_name, stat_name, stat_value>0 +FROM mysql.innodb_index_stats; +table_name index_name stat_name stat_value>0 +t2 PRIMARY n_leaf_pages_defrag 1 +t2 PRIMARY n_leaf_pages_reserved 1 +t2 PRIMARY n_page_split 1 +t2 PRIMARY n_pages_freed 1 +t2 SECOND n_leaf_pages_defrag 1 +t2 SECOND n_leaf_pages_reserved 1 +t2 SECOND n_page_split 1 +t2 SECOND n_pages_freed 1 # Clean up DROP TABLE t2; -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 +SELECT * FROM mysql.innodb_index_stats; +database_name table_name index_name last_update stat_name stat_value sample_size stat_description diff --git a/mysql-test/suite/innodb/t/innodb_defrag_stats.test b/mysql-test/suite/innodb/t/innodb_defrag_stats.test index 2a5026a68e5..e1e88a07477 100644 --- a/mysql-test/suite/innodb/t/innodb_defrag_stats.test +++ b/mysql-test/suite/innodb/t/innodb_defrag_stats.test @@ -1,41 +1,23 @@ --source include/have_innodb.inc ---source include/big_test.inc --source include/not_valgrind.inc --source include/not_embedded.inc +--source include/have_sequence.inc ---disable_warnings -DROP TABLE if exists t1; ---enable_warnings +SET GLOBAL innodb_defragment_stats_accuracy = 20; ---disable_query_log -let $innodb_defragment_stats_accuracy_orig=`select @@innodb_defragment_stats_accuracy`; ---enable_query_log - -select @@global.innodb_stats_persistent; -set global innodb_defragment_stats_accuracy = 20; +DELETE FROM mysql.innodb_index_stats; --echo # Create table. CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; ---echo # Populate data -INSERT INTO t1 VALUES(1, REPEAT('A', 256)); -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1_to_1024; --echo # Not enough page splits to trigger persistent stats write yet. select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1025_to_2048; --echo # Persistent stats recorded. select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); @@ -43,6 +25,7 @@ select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); --echo # Delete some rows. +BEGIN; let $num_delete = 20; while ($num_delete) { @@ -50,17 +33,13 @@ while ($num_delete) eval delete from t1 where a between $j and $j + 30; dec $num_delete; } +COMMIT; ---source include/restart_mysqld.inc ---echo # Server Restarted - ---echo # Confirm persistent stats still there after restart. select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); optimize table t1; -select sleep(2); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); @@ -84,7 +63,6 @@ select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like --echo # Table rename should cause stats rename. rename table t1 to t2; -select sleep(1); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); @@ -94,32 +72,30 @@ select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); ---echo # Drop index should cause stats drop. +--echo # Drop index should cause stats drop, but will not. drop index SECOND on t2; -select sleep(3); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_leaf_pages_defrag'); +--sorted_result +SELECT stat_name, stat_value>0 FROM mysql.innodb_index_stats +WHERE table_name like '%t2%' AND index_name='SECOND'; + +--echo # +--echo # MDEV-26636: Statistics must not be written for temporary tables +--echo # +SET GLOBAL innodb_defragment_stats_accuracy = 1; +CREATE TEMPORARY TABLE t (a INT PRIMARY KEY, c CHAR(255) NOT NULL) +ENGINE=InnoDB; +INSERT INTO t SELECT seq, '' FROM seq_1_to_100; --source include/restart_mysqld.inc ---echo Server Restarted -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); +SELECT * FROM mysql.innodb_index_stats where table_name like '%t1%'; -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); +--sorted_result +SELECT table_name, index_name, stat_name, stat_value>0 +FROM mysql.innodb_index_stats; --echo # Clean up DROP TABLE t2; -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); - ---disable_query_log -EVAL SET GLOBAL innodb_defragment_stats_accuracy = $innodb_defragment_stats_accuracy_orig; ---enable_query_log +SELECT * FROM mysql.innodb_index_stats; |