Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25274

OPTIMIZE TABLE does not ignore read_only

    XMLWordPrintable

Details

    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

      Attachments

        Issue Links

          Activity

            People

              nikitamalyavin Nikita Malyavin
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.