Debian Codegraph @

mysql-test/suite/innodb/t/innodb_stats_persistent.test

raw
1 --source include/have_innodb.inc
2 --source include/have_sequence.inc
3
4 SET @saved_include_delete_marked = @@GLOBAL.innodb_stats_include_delete_marked;
5 SET GLOBAL innodb_stats_include_delete_marked = ON;
6 SET @saved_traditional = @@GLOBAL.innodb_stats_traditional;
7 SET GLOBAL innodb_stats_traditional=false;
8 SET @saved_modified_counter = @@GLOBAL.innodb_stats_modified_counter;
9 SET GLOBAL innodb_stats_modified_counter=1;
10
11 CREATE TABLE t1 (id SERIAL, val INT UNSIGNED NOT NULL, KEY(val))
12 ENGINE=INNODB STATS_PERSISTENT=1,STATS_AUTO_RECALC=1;
13 CREATE TABLE t2 LIKE t1;
14
15 INSERT INTO t1 (val) SELECT 4 FROM seq_1_to_16;
16 SET STATEMENT use_stat_tables=never FOR
17 ANALYZE TABLE t1;
18
19 connect(con1, localhost, root,,);
20 START TRANSACTION;
21 DELETE FROM t1;
22 send SELECT COUNT(*) FROM t1;
23
24 connection default;
25 --echo # With innodb_stats_include_delete_marked=ON,
26 --echo # DELETE must not affect statistics before COMMIT.
27 EXPLAIN SELECT * FROM t1 WHERE val=4;
28
29 connection con1;
30 reap;
31 ROLLBACK;
32 SELECT COUNT(*) FROM t1;
33 EXPLAIN SELECT * FROM t1 WHERE val=4;
34
35 BEGIN;
36 DELETE FROM t1;
37 COMMIT;
38 SELECT COUNT(*) FROM t1;
39
40 connection default;
41 BEGIN;
42 INSERT INTO t2 (val) SELECT 4 FROM seq_1_to_16;
43
44 --echo # The INSERT will show up before COMMIT.
45 EXPLAIN SELECT * FROM t2 WHERE val=4;
46 SELECT COUNT(*) FROM t2;
47 --echo # The ROLLBACK of the INSERT must affect the statistics.
48 ROLLBACK;
49 SELECT COUNT(*) FROM t2;
50
51 connection con1;
52 EXPLAIN SELECT * FROM t2 WHERE val=4;
53 SET GLOBAL innodb_max_purge_lag_wait=0;
54 --echo # After COMMIT and purge, the DELETE must show up.
55 EXPLAIN SELECT * FROM t1 WHERE val=4;
56
57 SET GLOBAL innodb_stats_include_delete_marked = OFF;
58 BEGIN;
59 INSERT INTO t1 (val) SELECT 4 FROM seq_1_to_16;
60 EXPLAIN SELECT * FROM t1 WHERE val=4;
61 ROLLBACK;
62 EXPLAIN SELECT * FROM t1 WHERE val=4;
63 BEGIN;
64 INSERT INTO t1 (val) SELECT 4 FROM seq_1_to_16;
65 COMMIT;
66 EXPLAIN SELECT * FROM t1 WHERE val=4;
67 BEGIN;
68 DELETE FROM t1;
69 SELECT COUNT(*) FROM t1;
70 --echo # With innodb_stats_include_delete_marked=OFF,
71 --echo # DELETE must affect statistics even before COMMIT.
72 --echo # However, if there was a WHERE condition,
73 --echo # ha_innobase::records_in_range() would count the delete-marked records.
74 EXPLAIN SELECT * FROM t1;
75 ROLLBACK;
76 EXPLAIN SELECT * FROM t1;
77 SELECT COUNT(*) FROM t1;
78 disconnect con1;
79
80 connection default;
81
82 DROP TABLE t1,t2;
83 SET GLOBAL innodb_stats_include_delete_marked = @saved_include_delete_marked;
84 SET GLOBAL innodb_stats_traditional = @saved_traditional;
85 SET GLOBAL innodb_stats_modified_counter = @saved_modified_counter;
86
87 #
88 # Bug#12429573 TIMESTAMP COLUMN OF INNODB.INDEX_STATS ARE NOT UPDATED
89 # WHEN RE-RUNNING ANALYZE
90 #
91 CREATE TABLE bug12429573 (i INTEGER PRIMARY KEY, j INTEGER, KEY(j))
92 ENGINE=INNODB STATS_PERSISTENT=1;
93
94 SET STATEMENT use_stat_tables=never FOR
95 ANALYZE TABLE bug12429573;
96
97 # Cannot check the exact timestamp here because it is always different
98 # but at least check that both timestamps in innodb_table_stats and in
99 # innodb_index_stats have been updated to the same value. If the bug is
100 # present this check will fail.
101
102 --disable_cursor_protocol
103 SELECT last_update INTO @last FROM mysql.innodb_table_stats
104 WHERE table_name = 'bug12429573';
105 --enable_cursor_protocol
106 SELECT * FROM mysql.innodb_index_stats
107 WHERE table_name = 'bug12429573' AND last_update!=@last;
108
109 # The first ANALYZE would insert timestamp e.g. 17:23:39 in both
110 # innodb_table_stats and innodb_index_stats. The bug is that the second
111 # ANALYZE only updates the timestamp in innodb_table_stats. In order to
112 # check if the timestamp in innodb_index_stats has really been updated we
113 # need it to be different from the previous one (17:23:39) with at least
114 # one second.
115 -- sleep 1
116
117 SET STATEMENT use_stat_tables=never FOR
118 ANALYZE TABLE bug12429573;
119
120 SELECT * FROM mysql.innodb_table_stats
121 WHERE table_name = 'bug12429573' AND last_update=@last;
122 SELECT * FROM mysql.innodb_index_stats
123 WHERE table_name = 'bug12429573' AND last_update=@last;
124
125 DROP TABLE bug12429573;