MySQL Bugs: #43336: ANALYZE and OPTIMIZE do not honour --read-only

Bug #43336	ANALYZE and OPTIMIZE do not honour --read-only
Submitted:	3 Mar 2009 17:01	Modified:	19 May 2010 14:33
Reporter:	Andrew Hutchings	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.0 / 5.1	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The documentation states:
This variable is off by default. When it is enabled, the server allows no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. This variable does not apply to TEMPORARY tables, nor does it prevent the server from inserting rows into the log tables (see Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”).

Yet you can still run ANALYZE and OPTIMIZE from any user whilst in --read-only mode.

How to repeat:
1. Run a server with --read-only
2. As a non-super: ANALYZE TABLE or OPTIMIZE TABLE

Suggested fix:
In sql_parse.cc make the cases for SQLCOM_OPTIMIZE and SQLCOM_ANALYZE use the deny_updates_if_read_only_option() to deny them in --read-only mode when not super user.

Yes you can. What exactly is the problem here?
Would you like us to document explicitly that OPTIMIZE and ANALYZE are allowed under --read-only?

Yes, lets document this explicitly. Lets reassign?

After analysing this issue further I have determined this is a bug, not a feature request.

ANALYZE and OPTIMIZE TABLE requires INSERT and SELECT privileges, yet in read only mode you cannot insert into tables without SUPER privilege.  You can, however, run both ANALYZE and OPTIMIZE table.  So either it is a bug or the INSERT privilege requirement should be removed for these two operations.

They also modify data files although not strictly changing the table structure.  This is not the behaviour expected of a option called 'read only'.

In InnoDB neither ANALYZE nor OPTIMIZE change data.
MyISAM does happen to write the new table statistics on disc. But MyISAM would also happen to try to do it in case of FLUSH TABLE, which is allowed under read_only (nothing new information is written though since setting read_only has a FLUSH TABLES under the hood).

Actually, since OPTIMIZE in InnoDB performs re-create, it does change data.

09:53 < serg> what --read-only was created for ? according to the manual
09:53 < serg>    *  `--read-only'
09:53 < serg>      Cause the slave to allow no updates except from slave threads or
09:53 < serg>      from users having the `SUPER' privilege. On a slave server, this
09:53 < serg>      can be useful to ensure that the slave accepts updates only from
09:53 < serg>      its master server and not from clients. This variable does not
09:53 < serg>      apply to `TEMPORARY' tables.
09:53 < serg>  
09:53 < serg> I personally don't think that OPTIMIZE or ANALIZE break this, 
              they (sic:tables) cannot be considered "updated" in my opinion

Tobias notes: if we change this behaviour, running mysqlcheck -Aa on slave will require SUPER, which it currently doesn't.

In which case please see my comment from the 25th June 2009.

This should be documented. Please mention in --read-only description that OPTIMIZE and ANALYZE are allowed, and why (--read-only is an option which purpose is to protect the slave from going out of sync. We allow to check consistency of the slave with mysqlcheck -aA and OPTIMIZE/ANALYZE even when this option is ON).

In reply to Tobias' comment.  My suggested fix would only require SUPER privilege in read-only mode.

See also Bug#53798 "OPTIMIZE TABLE breaks repeatable read"

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Modified read_only description:

read_only does not apply to TEMPORARY tables, nor does it prevent the
server from inserting rows into the log tables (see ). This variable
does not prevent the use of ANALYZE TABLE or OPTIMIZE TABLE
statements because its purpose is to prevent changes to table
structure or contents. Analysis and optimization do not qualify as
such changes. This means, for example, that consistency checks on
read-only slaves can be performed with mysqlcheck --all-databases
--analyze.