MySQL: Block ANALYZE TABLE Queries Is there a way to revoke access for ANALYZE TABLE which is granted by needed Insert privilege?
I have an application that throw multiple ANALYZE TABLE queries per second. And am looking for a way to decline these queries from the DB side.
Update
It can't be blocked from the app side, since tracing which part of the code is doing so will cost days searching between millions line of code.
The answer is no, ANALYZE TABLE privilege is implicit if the user has SELECT and INSERT privilege on the table. There's no way to use access privileges alone to revoke privilege to run ANALYZE TABLE.
Are you sure the client is explicitly running ANALYZE TABLE statements? It's possible they are simply running SHOW TABLE STATUS or quering INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS. If so, then this might help:
SET GLOBAL innodb_stats_on_metadata=OFF;

For InnoDB tables, the work done by ANALYZE TABLE is identical to those metadata queries. Querying metadata triggers the resampling of table statistics, which can be a burden if it's done too frequently. Turning off the automatic update of statistics on any metadata query is useful. We disable that on all our MySQL instances at my company.
I recall years ago when phpMyAdmin added queries to the INFORMATION_SCHEMA to discover metadata, it caused some large hosting providers to crash, because hundreds of users at a time were viewing their databases in phpMyAdmin.
I also ran into this when I developed the database component for the Zend Framework 1.0. I found a workaround: use DESCRIBE instead of INFORMATION_SCHEMA queries. DESCRIBE doesn't trigger the update of table statistics.
If the client is really running the literal statement ANALYZE TABLE, and you can't change the client code (which would be the obvious solution), my only other suggestion is to run an SQL proxy in between the client and the database, and filter out ANALYZE TABLE statements.
For example, here's a blog describing use of ProxySQL to filter out offending queries:
https://www.percona.com/blog/2016/08/23/how-to-stop-an-offending-query-with-proxysql/