[MDEV-31409] Spider: ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES when TEMPORARY table is used Created: 2023-06-06  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Temporary, Locking, Storage Engine - Spider
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: affects-tests, locking

Issue Links:
Relates

 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)

 Comments   
Comment by Roel Van de Paar [ 2023-06-06 ]

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.

Comment by Roel Van de Paar [ 2023-06-06 ]

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;

Comment by Roel Van de Paar [ 2023-06-06 ]

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.

Generated at Thu Feb 08 10:23:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.