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

Strange error message if you try to FLUSH TABLES <view> after LOCK TABLES <view>

Details

    Description

      If you try to execute FLUSH TABLES on a view after read-locking that view with LOCK TABLES ... READ|WRITE, then you get an error message that doesn't seem to make a lot of sense.

      For example, if we have this view:

      CREATE TABLE t (qty INT, price INT);
      		 
      CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;

      Then let's see what happens:

      MariaDB [db1]> LOCK TABLES v READ;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [db1]> FLUSH TABLES v;
      		 
      ERROR 1100 (HY000): Table 'v' was not locked with LOCK TABLES

      If we execute the same statement on the base table, then we get a different error message:

      MariaDB [db1]> LOCK TABLES t READ;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [db1]> FLUSH TABLES t;
      		 
      ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated

      Trying to flush a write-locked view has the same problem:

      MariaDB [db1]> LOCK TABLES v WRITE;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [db1]> FLUSH TABLES v;
      		 
      ERROR 1100 (HY000): Table 'v' was not locked with LOCK TABLES

      But this does work for base tables:

      MariaDB [db1]> LOCK TABLES t WRITE;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [db1]> FLUSH TABLES t;
      		 
      Query OK, 0 rows affected (0.00 sec)

      Is the error message wrong, or is FLUSH partially broken for views at the moment?

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-15888 Implement FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK for views

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Descending order - Click to sort in ascending order
            serg Sergei Golubchik added a comment -

            ok to push

            serg Sergei Golubchik added a comment - ok to push
            holyfoot Alexey Botchkov added a comment -

            http://lists.askmonty.org/pipermail/commits/2018-September/012905.html

            holyfoot Alexey Botchkov added a comment - http://lists.askmonty.org/pipermail/commits/2018-September/012905.html
            holyfoot Alexey Botchkov added a comment -

            http://lists.askmonty.org/pipermail/commits/2018-September/012873.html

            holyfoot Alexey Botchkov added a comment - http://lists.askmonty.org/pipermail/commits/2018-September/012873.html
            holyfoot Alexey Botchkov added a comment -

            http://lists.askmonty.org/pipermail/commits/2018-September/012870.html

            holyfoot Alexey Botchkov added a comment - http://lists.askmonty.org/pipermail/commits/2018-September/012870.html
            holyfoot Alexey Botchkov added a comment -

            Fix proposal v2
            http://lists.askmonty.org/pipermail/commits/2018-June/012645.html

            holyfoot Alexey Botchkov added a comment - Fix proposal v2 http://lists.askmonty.org/pipermail/commits/2018-June/012645.html
            View 6 older comments

            People

              holyfoot Alexey Botchkov
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.