| 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; |