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