[MDEV-31409] Spider: ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES when TEMPORARY table is used - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
Fix Version/s: 10.5, 10.6, 10.11
Component/s: Data Definition - Temporary, Locking, Storage Engine - Spider
Labels:
- affects-tests
- locking

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

relates to

MDEV-34990 Inconsistent locking message for Spider target table; ERROR 1099 Table was locked with a READ lock and can't be updated

Open

Activity

Ascending order - Click to sort in descending order

Roel Van de Paar added a comment - 2023-06-06 04:37 - 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 Van de Paar added a comment - 2023-06-06 04:37 - 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 Van de Paar added a comment - 2023-06-06 05:41

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 Van de Paar added a comment - 2023-06-06 05:41 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 Van de Paar added a comment - 2023-06-06 06:09

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 Van de Paar added a comment - 2023-06-06 06:09 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.

MariaDB Server

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

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration