Debian Codegraph @

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

raw
1 #
2 # Bug#18384390 WRONG STATISTICS WITH BIG ROW LENGTH AND PERSISTENT STATS
3 #
4
5 --source include/have_innodb.inc
6 --source include/have_innodb_max_16k.inc
7 --source include/have_sequence.inc
8
9 CREATE TABLE bug18384390 (
10 id INT AUTO_INCREMENT PRIMARY KEY,
11 txt VARCHAR(10000)
12 ) ENGINE=INNODB STATS_PERSISTENT=1 STATS_AUTO_RECALC=0;
13
14 let $count=1024;
15 eval
16 INSERT INTO bug18384390 (txt) SELECT REPEAT('0', 10000) FROM seq_1_to_$count;
17
18 set use_stat_tables=never;
19 ANALYZE TABLE bug18384390;
20
21 -- let $n_rows = `SELECT n_rows FROM mysql.innodb_table_stats WHERE table_name = 'bug18384390'`
22
23 -- let $table_rows = `SELECT table_rows FROM information_schema.tables WHERE table_name = 'bug18384390'`
24
25 -- let $n_diff = `SELECT stat_value FROM mysql.innodb_index_stats WHERE table_name = 'bug18384390' AND stat_name = 'n_diff_pfx01'`
26
27 -- let $cardinality = `SELECT cardinality FROM information_schema.statistics WHERE table_name = 'bug18384390'`
28
29 -- let $margin_of_err_pct = 30
30 -- let $margin_of_err_rows = `SELECT ROUND($count * $margin_of_err_pct / 100)`
31
32 -- let $min_allowed = `SELECT $count - $margin_of_err_rows`
33 -- let $max_allowed = `SELECT $count + $margin_of_err_rows`
34
35 -- let $dump_sql = SELECT COUNT(*) FROM bug18384390; SELECT * FROM mysql.innodb_table_stats; SELECT * FROM mysql.innodb_index_stats; SELECT * FROM information_schema.tables WHERE table_name = 'bug18384390'; SELECT * FROM information_schema.statistics WHERE table_name = 'bug18384390';
36
37 -- vertical_results
38
39 if ($n_rows < $min_allowed) {
40 -- echo mysql.innodb_table_stats.n_rows is too small ($n_rows < $min_allowed)
41 -- eval $dump_sql
42 }
43
44 if ($n_rows > $max_allowed) {
45 -- echo mysql.innodb_table_stats.n_rows is too big ($n_rows > $max_allowed)
46 -- eval $dump_sql
47 }
48
49 if ($table_rows < $min_allowed) {
50 -- echo information_schema.tables.table_rows is too small ($table_rows < $min_allowed)
51 -- eval $dump_sql
52 }
53
54 if ($table_rows > $max_allowed) {
55 -- echo information_schema.tables.table_rows is too big ($table_rows > $max_allowed)
56 -- eval $dump_sql
57 }
58
59 if ($n_diff < $min_allowed) {
60 -- echo mysql.innodb_index_stats.stat_value is too small ($n_diff < $min_allowed)
61 -- eval $dump_sql
62 }
63
64 if ($n_diff > $max_allowed) {
65 -- echo mysql.innodb_index_stats.stat_value is too big ($n_diff > $max_allowed)
66 -- eval $dump_sql
67 }
68
69 if ($cardinality < $min_allowed) {
70 -- echo information_schema.statistics.cardinality is too small ($cardinality < $min_allowed)
71 -- eval $dump_sql
72 }
73
74 if ($cardinality > $max_allowed) {
75 -- echo information_schema.statistics.cardinality is too big ($cardinality > $max_allowed)
76 -- eval $dump_sql
77 }
78
79 DROP TABLE bug18384390;