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

Spider: ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES when TEMPORARY table is used

Details

    Description

      This issue is seen very often in the test runs. There are likely various other paths to reproduce the same issue. It seems to be a locking vs temporary tables oversight.

      INSTALL PLUGIN Spider SONAME 'ha_spider.so';
      CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE'',USER'',PASSWORD'');
      CREATE TABLE t (c INT) ENGINE=InnoDB;
      CREATE TABLE tm (c INT) ENGINE=InnoDB;
      CREATE TABLE t1 (c INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
      CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "tm"';
      LOCK TABLES t1 READ;  # Not required, but proves incorrect outcome
      CREATE TEMPORARY TABLE t1 (c1 INT);
      LOCK TABLES t2 READ;
      DROP TABLE t1;
      LOCK TABLES non_existing WRITE;
      SELECT 1 FROM t1;
      

      Leads to:

      11.1.0 4e5b771e980edfdad5c5414aa62c81d409d585a4 (Debug)

      11.1.0-dbg>SELECT 1 FROM t1;
      ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES
      

      11.1.0 4e5b771e980edfdad5c5414aa62c81d409d585a4 (Debug)

      2023-06-06 14:26:13 4 [ERROR] Got error 1100 when reading table './test/t1'
      

      Interestingly, when we remove only the KEY from col c in CREATE TABLE t1, the error shows at the command line,* but not in the error log*. This is an additional inconsistency (or bug).

      Bug (or feature/syntax) confirmed present in:
      MariaDB: 10.4.30 (dbg), 10.4.30 (opt), 10.5.21 (dbg), 10.5.21 (opt), 10.6.14 (dbg), 10.6.14 (opt), 10.9.7 (dbg), 10.9.7 (opt), 10.10.5 (dbg), 10.10.5 (opt), 10.11.4 (dbg), 10.11.4 (opt), 11.0.2 (dbg), 11.0.2 (opt), 11.1.0 (dbg), 11.1.0 (opt)

      Attachments

        Issue Links

          Activity

            Roel Roel Van de Paar added a comment - - edited

            Additional testcase:

            INSTALL PLUGIN Spider SONAME 'ha_spider.so';
            CREATE USER Spider@localhost IDENTIFIED BY 'PWD123';
            CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock', DATABASE 'test', USER 'Spider', PASSWORD 'PWD123');
            CREATE TABLE t (c INT) ENGINE=InnoDB;
            CREATE TABLE tm (c INT) ENGINE=MyISAM;
            CREATE TABLE t1 (c INT PRIMARY KEY) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';
            CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "tm"';
            CREATE TEMPORARY TABLE t1 (c INT);
            LOCK TABLES t2 WRITE, t1 READ;
            LOCK TABLES Spider.slow_log READ;
            DROP TABLE t1;
            SELECT COUNT(*) FROM t1;
            

            With the same outcome. Removing PRIMARY KEY also has the same effect as described as above re: CLI vs error log output.

            Roel Roel Van de Paar added a comment - - edited Additional testcase: INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE USER Spider@localhost IDENTIFIED BY 'PWD123' ; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' , DATABASE 'test' , USER 'Spider' , PASSWORD 'PWD123' ); CREATE TABLE t (c INT ) ENGINE=InnoDB; CREATE TABLE tm (c INT ) ENGINE=MyISAM; CREATE TABLE t1 (c INT PRIMARY KEY ) ENGINE=Spider COMMENT= 'WRAPPER "mysql", srv "srv", TABLE "t"' ; CREATE TABLE t2 (c INT ) ENGINE=Spider COMMENT= 'WRAPPER "mysql", srv "srv", TABLE "tm"' ; CREATE TEMPORARY TABLE t1 (c INT ); LOCK TABLES t2 WRITE, t1 READ ; LOCK TABLES Spider.slow_log READ ; DROP TABLE t1; SELECT COUNT (*) FROM t1; With the same outcome. Removing PRIMARY KEY also has the same effect as described as above re: CLI vs error log output.

            This MTR version of the original testcase produces the error 1100 including in the error log

            --source include/have_innodb.inc
            --source include/have_partition.inc
            --let $SOCKET= `SELECT @@global.socket`
            INSTALL PLUGIN Spider SONAME 'ha_spider.so';
            CREATE USER spider@localhost IDENTIFIED BY 'pwd';
            GRANT ALL ON test.* TO spider@localhost;
            eval CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$SOCKET",DATABASE 'test',USER 'spider',PASSWORD 'pwd');
            CREATE TABLE t (c INT) ENGINE=InnoDB;
            CREATE TABLE tm (c INT) ENGINE=InnoDB;
            CREATE TABLE t1 (c INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
            CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "tm"';
            LOCK TABLES t1 READ;  # Not required, but proves incorrect outcome
            CREATE TEMPORARY TABLE t1 (c1 INT);
            LOCK TABLES t2 READ;
            DROP TABLE t1;
            --error ER_NO_SUCH_TABLE
            LOCK TABLES non_existing WRITE;
            SELECT 1 FROM t1;
            

            Roel Roel Van de Paar added a comment - This MTR version of the original testcase produces the error 1100 including in the error log --source include/have_innodb.inc --source include/have_partition.inc --let $SOCKET= `SELECT @@global.socket` INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE USER spider@localhost IDENTIFIED BY 'pwd' ; GRANT ALL ON test.* TO spider@localhost; eval CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$SOCKET" , DATABASE 'test' , USER 'spider' , PASSWORD 'pwd' ); CREATE TABLE t (c INT ) ENGINE=InnoDB; CREATE TABLE tm (c INT ) ENGINE=InnoDB; CREATE TABLE t1 (c INT KEY ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; CREATE TABLE t2 (c INT ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "tm"' ; LOCK TABLES t1 READ ; # Not required, but proves incorrect outcome CREATE TEMPORARY TABLE t1 (c1 INT ); LOCK TABLES t2 READ ; DROP TABLE t1; --error ER_NO_SUCH_TABLE LOCK TABLES non_existing WRITE; SELECT 1 FROM t1;

            Further to the error log vs no error log, we can see something similar with:

            INSTALL PLUGIN Spider SONAME 'ha_spider.so';
            CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE'test',USER'',PASSWORD'');
            CREATE TABLE t (c INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t1"';
            CREATE TABLE t1 (c INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
            SELECT * FROM t1 WHERE c=0; 
            

            Which work as expected, i.e. it produces the CLI error An infinite loop is detected when opening table test.t which is to be expected. Versus:

            INSTALL PLUGIN Spider SONAME 'ha_spider.so';
            CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE'test',USER'',PASSWORD'');
            CREATE TABLE t (c INT KEY) ENGINE=InnoDB COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
            CREATE TABLE t1 (c INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
            SHOW CREATE TABLE t1;
            ALTER TABLE t ENGINE=Spider;
            SELECT * FROM t1 WHERE c=0;
            

            Which produces both the CLI and an error log 1429 error.

            Roel Roel Van de Paar added a comment - Further to the error log vs no error log, we can see something similar with: INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' , DATABASE 'test' , USER '' , PASSWORD '' ); CREATE TABLE t (c INT KEY ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t1"' ; CREATE TABLE t1 (c INT KEY ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; SELECT * FROM t1 WHERE c=0; Which work as expected, i.e. it produces the CLI error An infinite loop is detected when opening table test.t which is to be expected. Versus: INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' , DATABASE 'test' , USER '' , PASSWORD '' ); CREATE TABLE t (c INT KEY ) ENGINE=InnoDB COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; CREATE TABLE t1 (c INT KEY ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; SHOW CREATE TABLE t1; ALTER TABLE t ENGINE=Spider; SELECT * FROM t1 WHERE c=0; Which produces both the CLI and an error log 1429 error.

            People

              ycp Yuchen Pei
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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