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

DROP TEMPORARY TABLE IF EXISTS causes error 1290 with read_only option

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.1.28, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 5.5.58, 10.0.33, 10.1.29, 10.2.10
    • Server
    • None
    • CentOS 6

    Description

      I have a replicated slave with "read_only=1" set in my.cnf

      I have a user set up as follows:

      CREATE USER 'slave_user'@'localhost' IDENTIFIED BY 'slave_pswd';
      		 
      GRANT ALL ON mydb.* TO 'slave_user'@'localhost' WITH GRANT OPTION;

      If I log in as 'slave_user', I can create a temporary table, write to it, and then drop it successfully:

      CREATE TEMPORARY TABLE temp_x (id integer);
      		 
      INSERT INTO temp_x values (1);
      		 
      DROP TEMPORARY TABLE IF EXISTS temp_x;

      However, if I have not created a temporary table, and I start with:

      DROP TEMPORARY TABLE IF EXISTS temp_x;

      I get the error: ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement

      I have countless lines of application code which always checks to make sure a temporary table is dropped before creating it to avoid an "already exists" error. Always worked on MySQL 5.6 and lower, on the exact same slave database with read_only. After upgrading to MariaDB 10.1, this problem emerged.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          Thanks for the report.

          The problem appeared in 5.5.57 with this commit:

          commit 9b3360ea4417ed653d5c7eed29f4ef7e80618e43
          		 
          Author: Sergei Golubchik <serg@mariadb.org>
          		 
          Date:   Tue Jul 18 14:47:40 2017 +0200
          		 
           
          		 
              BUG#25250768: WRITING ON A READ_ONLY=ON SERVER WITHOUT SUPER PRIVILEGE
          		 
              
              simplify.
          		 
              add a test case.

          Test case

          		 
          CREATE USER 'foo';
          		 
          SET GLOBAL read_only= on;
          		 
          --connect (con1,localhost,foo,,)
          		 
          DROP TEMPORARY TABLE IF EXISTS t1;
          		 
           
          		 
          # Cleanup
          		 
          --disconnect con1
          		 
          --connection default
          		 
          DROP USER 'foo';
          		 
          SET GLOBAL read_only= DEFAULT;

          elenst Elena Stepanova added a comment - Thanks for the report. The problem appeared in 5.5.57 with this commit: commit 9b3360ea4417ed653d5c7eed29f4ef7e80618e43 Author: Sergei Golubchik <serg@mariadb.org> Date: Tue Jul 18 14:47:40 2017 +0200   BUG#25250768: WRITING ON A READ_ONLY=ON SERVER WITHOUT SUPER PRIVILEGE simplify. add a test case. Test case CREATE USER 'foo' ; SET GLOBAL read_only= on ; --connect (con1,localhost,foo,,) DROP TEMPORARY TABLE IF EXISTS t1;   # Cleanup --disconnect con1 --connection default DROP USER 'foo' ; SET GLOBAL read_only= DEFAULT ;
          markalanpeter Mark Peter added a comment -

          Thank you for the acknowledgment, Elena, and also for creating the simplified test case. If a fix is made, will there likely be a patch? Or would it be included in a new 10.1 minor version?

          markalanpeter Mark Peter added a comment - Thank you for the acknowledgment, Elena, and also for creating the simplified test case. If a fix is made, will there likely be a patch? Or would it be included in a new 10.1 minor version?
          elenst Elena Stepanova added a comment -

          markalanpeter,

          As soon as the fix is made, which will hopefully happen soon, it will appear in the git repository in the lowest branch where it is fixed (I expect it to be 5.5), and will be merged up to higher versions soon afterwards; so, if you build from sources, you'll be able to take it as a patch from git. Otherwise, it will be released with every minor release which happen after the fix.

          elenst Elena Stepanova added a comment - markalanpeter , As soon as the fix is made, which will hopefully happen soon, it will appear in the git repository in the lowest branch where it is fixed (I expect it to be 5.5), and will be merged up to higher versions soon afterwards; so, if you build from sources, you'll be able to take it as a patch from git. Otherwise, it will be released with every minor release which happen after the fix.

          People

            serg Sergei Golubchik
            markalanpeter Mark Peter
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.