MySQL Bugs: #81210: Please allow /*+ MAX_EXECUTION_TIME(XXX) */ hint to work on more commands

Bug #81210	Please allow /+ MAX_EXECUTION_TIME(XXX) / hint to work on more commands
Submitted:	27 Apr 2016 8:25	Modified:	15 Jul 2016 12:00
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.7.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I'm not sure if the category is right but I know this is dealt with by the optimizer.

I sometimes get application locks triggered by running ANALYZE TABLES on a server. The Analyze tables get blocked by a slow query and further access to the table then gets blocked by ANALYZE TABLES.

MySQL 5.7 includes the new query hinting format /*+ MAX_EXECUTION_TIME(XXXX) */ but this does not work for ANALYZE TABLES. It would be convenient to make it work for more than just select and some other queries but make it more generic.

How to repeat:
In one connection do:

root@some-host [db]> lock tables some_table write;
Query OK, 0 rows affected (0.00 sec)

in another connection do:

root@some-host [db]> analyze table /*+ MAX_EXECUTION_TIME(1000) */ some_table;
^C^C -- query aborted

Notice this does not get interrupted after 1 second.

Suggested fix:
Enhance the optimizer behaviour to include the EXECUTION TIME limit to other commands where this addition may be useful. Start with ANALYZE TABLES as that comes to mind immediately but I can imagine other commands would also benefit from this.

Note: adding this to 5.7 should be "safe" in the sense the optimizer hint syntax does not change, and the it won't be recognised on other versions of MySQL.

Specifically for 'ANALYZE TABLE' I rather see this implemented as a 'DELAYED' attribute, such that ANALYZE TABLE will only try to get the lock for a few seconds, and then time out, and wait a bit, and try again, until it acquires a slot to do its thing.

Hello Simon,

Thank you for the feature request!

Thanks,
Umesh