1--source include/have_innodb.inc 2--source include/not_valgrind.inc 3--source include/not_embedded.inc 4--source include/have_sequence.inc 5 6SET GLOBAL innodb_defragment_stats_accuracy = 20; 7 8DELETE FROM mysql.innodb_index_stats; 9 10--echo # Create table. 11CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), 12 KEY SECOND(a, b)) ENGINE=INNODB STATS_PERSISTENT=0; 13 14INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) 15FROM seq_1_to_1024; 16 17--echo # Not enough page splits to trigger persistent stats write yet. 18SELECT * FROM mysql.innodb_index_stats; 19 20INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) 21FROM seq_1025_to_1433; 22 23BEGIN; 24let $num_delete = 20; 25while ($num_delete) 26{ 27 eval INSERT INTO t1 SELECT 100*$num_delete+seq, REPEAT('A', 256) 28 FROM seq_70_to_99; 29 dec $num_delete; 30} 31ROLLBACK; 32 33SELECT @@GLOBAL.innodb_force_recovery<2 "have background defragmentation"; 34 35# Wait for defrag_pool to be processed. 36 37let $wait_timeout=30; 38let $wait_condition = SELECT COUNT(*)>0 FROM mysql.innodb_index_stats; 39--source include/wait_condition.inc 40 41--sorted_result 42SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; 43 44optimize table t1; 45 46--sorted_result 47SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; 48 49set global innodb_defragment_stats_accuracy = 40; 50 51INSERT INTO t1 (b) SELECT b from t1; 52 53--sorted_result 54SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; 55 56INSERT INTO t1 (b) SELECT b from t1; 57 58--sorted_result 59SELECT stat_name FROM mysql.innodb_index_stats WHERE table_name='t1'; 60 61--echo # Table rename should cause stats rename. 62rename table t1 to t2; 63 64--sorted_result 65SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; 66 67--echo # Drop index should cause stats drop, but will not. 68drop index SECOND on t2; 69 70--echo # 71--echo # MDEV-26636: Statistics must not be written for temporary tables 72--echo # 73SET GLOBAL innodb_defragment_stats_accuracy = 1; 74CREATE TEMPORARY TABLE t (a INT PRIMARY KEY, c CHAR(255) NOT NULL) 75ENGINE=InnoDB; 76INSERT INTO t SELECT seq, '' FROM seq_1_to_100; 77 78--source include/restart_mysqld.inc 79 80--sorted_result 81SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; 82 83--echo # Clean up 84# Starting with 10.6, DROP TABLE will not touch persistent statistics 85# (not defragmentation statistics either) if the table has none! 86ALTER TABLE t2 STATS_PERSISTENT=1; 87DROP TABLE t2; 88 89SELECT * FROM mysql.innodb_index_stats; 90