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

Writing to TEMPORARY TABLE not possible in read-only

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

            arjen Arjen Lentz created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 5.5 [ 15800 ]
            Affects Version/s 5.5.39 [ 16301 ]
            Assignee Sergei Golubchik [ serg ]
            Labels upstream
            elenst Elena Stepanova made changes -
            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.
            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
            {code:sql}
            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
            {code}
            ===

            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.
            elenst Elena Stepanova made changes -
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 5.5 [ 15800 ]
            Fix Version/s 10.0 [ 16000 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            elenst Elena Stepanova made changes -
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 52400 ] MariaDB v3 [ 64920 ]
            elenst Elena Stepanova made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.1 [ 16100 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2 [ 14601 ]
            serg Sergei Golubchik made changes -
            Labels upstream upstream upstream-fixed
            serg Sergei Golubchik made changes -
            Fix Version/s 5.5 [ 15800 ]
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 10.1 [ 16100 ]
            serg Sergei Golubchik made changes -
            Affects Version/s 10.1 [ 16100 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Component/s Replication [ 10100 ]
            Fix Version/s 5.5.51 [ 22015 ]
            Fix Version/s 10.0.27 [ 22017 ]
            Fix Version/s 10.1.17 [ 22102 ]
            Fix Version/s 5.5 [ 15800 ]
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Component/s Data Definition - Temporary [ 10123 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 64920 ] MariaDB v4 [ 148126 ]

            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.