Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5491 CREATE OR REPLACE TABLE
  3. MDEV-5601

Strange error on CREATE OR REPLACE .. SELECT under lock

Details

    • Technical task
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Won't Fix
    • None
    • 10.0.8
    • None
    • None

    Description

      The following set of commands ends with the error "ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES" which is untrue and hence confusing:

      MariaDB [test]> create table t1 (i int);
      		 
      Query OK, 0 rows affected (0.68 sec)
      		 
       
      		 
      MariaDB [test]> lock table t1 write;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [test]> create or replace table t1 as select * from t1;
      		 
      ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

      Test case:

      drop table if exists t1;
      		 
      create table t1 (i int);
      		 
      lock table t1 write;
      		 
      create or replace table t1 as select * from t1;
      		 

      revision-id: monty@askmonty.org-20140202093802-a6jtuy6ni29biigz
      		 
      revno: 3968
      		 
      branch-nick: mariadb-monty

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          monty Michael Widenius added a comment -

          I don't think I can do much about this
          This happens because we try to use t1 twice. If succeed first for the SELECT part, but when we try to lock it again for the create part, it will fail.

          This is the same error that you would get from:

          create table t1 (a int) engine = myisam;
          lock table t1 write;
          select * from t1 where a in (select * from t1);

          This gives the error "Table 't1' was not locked with LOCK TABLES"

          monty Michael Widenius added a comment - I don't think I can do much about this This happens because we try to use t1 twice. If succeed first for the SELECT part, but when we try to lock it again for the create part, it will fail. This is the same error that you would get from: create table t1 (a int) engine = myisam; lock table t1 write; select * from t1 where a in (select * from t1); This gives the error "Table 't1' was not locked with LOCK TABLES"
          monty Michael Widenius added a comment -

          Same error as with other similar usage of locked tables.

          monty Michael Widenius added a comment - Same error as with other similar usage of locked tables.
          monty Michael Widenius made changes -
          Resolution Won't Fix [ 2 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 33772 ] MariaDB v2 [ 42507 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 42507 ] MariaDB v3 [ 61470 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 61470 ] MariaDB v4 [ 147453 ]

          People

            monty Michael Widenius
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.