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

Writing to TEMPORARY TABLE not possible in read-only

    XMLWordPrintable

Details

    Description

      MySQL 5.5 refman for read-only:
      === http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_read-only
      Cause the slave to permit no updates except from slave threads or from users having the SUPER privilege. 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.
      ===

      However, this appears to not be true.
      Upstream a bug has existed since 2011: http://bugs.mysql.com/bug.php?id=62008

      I've just repeated this with MariaDB 10.0.12

      mysql -u root
      GRANT SELECT,INSERT,UPDATE,DELETE,CREATE TEMPORARY TABLES ON *.* TO slaveuser@localhost IDENTIFIED BY 'slaveuser';
      SET GLOBAL read_only=1;
       
      mysql -u slaveuser -pslaveuser
      SELECT CURRENT_USER();  -- verify we're not anonymous
      SELECT @@SQL_LOG_BIN; -- verify binary logging is enabled
      USE test
       
      MariaDB [test]> CREATE TABLE t (i INT);
      ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
       
      MariaDB [test]> CREATE TEMPORARY TABLE t (i INT);
      Query OK, 0 rows affected (0.20 sec)
       
      MariaDB [test]> INSERT INTO t VALUES (1);
      ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement

      ===

      As per the original upstream bug report, the problem only shows up when binary logging is enabled. Obviously that's merely a fact and not a valid workaround

      Furthermore, MyISAM behaviour on the above is also broken.
      If a specific ENGINE=... is provided for the temporary table, and the engine is MyiSAM, then the INSERT still reports the read-only error but the table will in fact contain the new row.

      From the above, I guess it's the binary log code reporting the error, and I would reckon that that's also where the bug exists.

      Note: Client applications are failing on this after upgrading to MariaDB 10.0, which is why I have marked the bug as critical. While the upstream bug info is somewhat ambiguous in terms of when the problem first appeared, it definitely existed in 5.5. At some point in the past, it didn't occur, and obviously the manual indicates how it should work. So it's a regression.

      Mind that any testcase needs to ensure that the binlog is enabled and the other possibilities described above are covered, so that it effectively catches the problem.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              arjen Arjen Lentz
              Votes:
              4 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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