Bug #78401 "ANALYZE TABLE" may assign temporary values to table stats during its execution
Submitted: 10 Sep 2015 20:56 Modified: 11 Sep 2015 19:38
Reporter: Yura Sorokin (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6, 5.6.26 OS:Any
Assigned to: CPU Architecture:Any

[10 Sep 2015 20:56] Yura Sorokin
While "ANALYZE TABLE" is executing other statements which depend on index stats 
(like "SELECT ... FROM ... ORDER BY ..." ) may see intermediate (temporary)
values assigned to "stat_xxx" members and therefore make false assumptions.

The same is true for background stats recalculation.

How to repeat:
Here is a test case which simulates this scenario
1. Disable background stats recalculation.
2. Create a simple table with one unindexed field.
3. Insert 40 000 records in the table.
4. Run "ANALYZE TABLE" to make sure table stats are up to date.
5. Execute "SELECT * FROM table ORDER BY <field>".
6. Determine how many sort merges this statement had to do "SHOW SESSION STATUS LIKE 'Sort_merge_passes'".
7. In another connection run "ANALYZE TABLE" again and using "SET DEBUG_SYNC" make it pause at 
   "dict_stats_analyze_index_middle" debug sync point in the "storage/innobase/dict/dict0stats.cc"
   (a new sync point introduced in this commit).
8. In primary connection repeat the same "SELECT" statement as in (5).
9. As in (6), determine how many sort merges this statement had to do again.
10. Notice the difference between added "Sort_merge_passes" in (6) and (9).

To see the difference run "analyze_table_corrupted_stats.test" two times, with debug sync turned on and off.
"./mtr analyze_table_corrupted_stats" and "./mtr analyze_table_corrupted_stats --debug-sync-timeout=0".

Suggested fix:
Rework "dict_stats_analyze_index()" from "storage/innobase/dict/dict0stats.cc" so that it would update
"index->stats_xxx" members atomically from old set of values to a new one without resetting them with
"dict_stats_empty_index(index)" in between.
[10 Sep 2015 20:59] Yura Sorokin
Test case file

Attachment: analyze_table_corrupted_stats.test (application/octet-stream, text), 2.95 KiB.

[10 Sep 2015 20:59] Yura Sorokin
Test results file

Attachment: analyze_table_corrupted_stats.result (application/octet-stream, text), 1.49 KiB.

[10 Sep 2015 21:00] Yura Sorokin
dict0stats.cc with new debug sync point

Attachment: dict0stats.cc (text/plain), 121.25 KiB.

[10 Sep 2015 21:08] Yura Sorokin
"--sleep 2" in the "analyze_table_corrupted_stats.test" is only needed to make "./mtr analyze_table_corrupted_stats --debug-sync-timeout=0" run.

If this bug is approved, then this test case can be added directly to "mysql-test".
Just remove "--sleep 2" and uncomment "#--source include/have_debug_sync.inc" before adding to the source control.
[11 Sep 2015 6:27] Umesh Shastry
Hello Yura Sorokin,

Thank you for the report and test case.

[11 Sep 2015 19:38] Yura Sorokin
In this particular case the number of "Merge_passes" directly depends on the result of "ha_innobase::estimate_rows_upper_bound()" which in turn depends on the approximate number of leaf pages in the index tree "stat_n_leaf_pages".
[14 Sep 2016 5:15] Laurynas Biveinis
See bug 82968 and bug 82969