[MDEV-25274] OPTIMIZE TABLE does not ignore read_only Created: 2021-03-26  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.2.37, 10.3.28, 10.4.18, 10.5.9
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Blocks
is blocked by MDEV-26618 OPTIMIZE table not replicating Confirmed

 Description   

If you try to run the OPTIMIZE TABLE statement while the read_only system variable is set, you see an error:

MariaDB [(none)]> OPTIMIZE TABLE mydb.tab;
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement

This contradicts the KB, which says that OPTIMIZE TABLE should ignore the read_only system variable:

When the variable read_only is set to 1, no updates are permitted except from users with the SUPER privilege (<= MariaDB 10.5.1) or READ ONLY ADMIN privilege (>= MariaDB 10.5.2) or replica servers updating from a primary. Inserting rows to log tables, updates to temporary tables and OPTIMIZE TABLE or ANALYZE TABLE statements are excluded from this limitation.

https://mariadb.com/kb/en/read-only-replicas/

And this also contradicts the MySQL 5.6 documentation, which also says that OPTIMIZE TABLE should ignore the read_only system variable:

Even with read_only enabled, the server permits these operations:

  • Updates performed by replica threads, if the server is a replica. In replication setups, it can be useful to enable read_only on replica servers to ensure that replicas accept updates only from the source server and not from clients.
  • Use of ANALYZE TABLE or OPTIMIZE TABLE statements. The purpose of read-only mode 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 replicas can be performed with mysqlcheck --all-databases --analyze.
  • Operations on TEMPORARY tables.
  • Inserts into the log tables (mysql.general_log and mysql.slow_log; see Section 5.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”).

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_read_only


Generated at Thu Feb 08 09:36:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.