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