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_max_16k.inc
6
7 CREATE TABLE bug18384390 (
8 id INT AUTO_INCREMENT PRIMARY KEY,
9 txt VARCHAR(10000)
10 ) ENGINE=INNODB STATS_AUTO_RECALC=0;
11
12 INSERT INTO bug18384390 (txt) VALUES (REPEAT('0', 10000));
13 -- let $i = 0
14 while ($i < 10) {
15 INSERT INTO bug18384390 (txt) SELECT txt FROM bug18384390;
16 -- inc $i
17 }
18
19 # Now we have 1024 rows in bug18384390
20
21 -- let $count = `SELECT COUNT(*) FROM bug18384390`
22
23 ANALYZE TABLE bug18384390;
24
25 -- let $n_rows = `SELECT n_rows FROM mysql.innodb_table_stats WHERE table_name = 'bug18384390'`
26
27 -- let $table_rows = `SELECT table_rows FROM information_schema.tables WHERE table_name = 'bug18384390'`
28
29 -- let $n_diff = `SELECT stat_value FROM mysql.innodb_index_stats WHERE table_name = 'bug18384390' AND stat_name = 'n_diff_pfx01'`
30
31 -- let $cardinality = `SELECT cardinality FROM information_schema.statistics WHERE table_name = 'bug18384390'`
32
33 -- let $margin_of_err_pct = 30
34 -- let $margin_of_err_rows = `SELECT ROUND($count * $margin_of_err_pct / 100)`
35
36 -- let $min_allowed = `SELECT $count - $margin_of_err_rows`
37 -- let $max_allowed = `SELECT $count + $margin_of_err_rows`
38
39 -- 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';
40
41 -- vertical_results
42
43 if ($n_rows < $min_allowed) {
44 -- echo mysql.innodb_table_stats.n_rows is too small ($n_rows < $min_allowed)
45 -- eval $dump_sql
46 }
47
48 if ($n_rows > $max_allowed) {
49 -- echo mysql.innodb_table_stats.n_rows is too big ($n_rows > $max_allowed)
50 -- eval $dump_sql
51 }
52
53 if ($table_rows < $min_allowed) {
54 -- echo information_schema.tables.table_rows is too small ($table_rows < $min_allowed)
55 -- eval $dump_sql
56 }
57
58 if ($table_rows > $max_allowed) {
59 -- echo information_schema.tables.table_rows is too big ($table_rows > $max_allowed)
60 -- eval $dump_sql
61 }
62
63 if ($n_diff < $min_allowed) {
64 -- echo mysql.innodb_index_stats.stat_value is too small ($n_diff < $min_allowed)
65 -- eval $dump_sql
66 }
67
68 if ($n_diff > $max_allowed) {
69 -- echo mysql.innodb_index_stats.stat_value is too big ($n_diff > $max_allowed)
70 -- eval $dump_sql
71 }
72
73 if ($cardinality < $min_allowed) {
74 -- echo information_schema.statistics.cardinality is too small ($cardinality < $min_allowed)
75 -- eval $dump_sql
76 }
77
78 if ($cardinality > $max_allowed) {
79 -- echo information_schema.statistics.cardinality is too big ($cardinality > $max_allowed)
80 -- eval $dump_sql
81 }
82
83 DROP TABLE bug18384390;