MySQL Bugs: #78401: "ANALYZE TABLE" may assign temporary values to table stats during its execution

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:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6, 5.6.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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
https://github.com/percona-ysorokin/mysql-server/commit/9b55d6f56675c57d4a541f2655f144cb1e...
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.

Test case file

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

Test results file

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

dict0stats.cc with new debug sync point

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

"--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.

Hello Yura Sorokin,

Thank you for the report and test case.

Thanks,
Umesh

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".

See bug 82968 and bug 82969