[#MDEV-29756] Spider self-locks for the duration of innodb_lock_wait

Set to Minor because the scenario is unlikely to be important from the user perspective.

When the "remote" and "local" tables are on the same server, INSERT INTO <spider> SELECT FROM <underlying InnoDB table> locks up until innodb_lock_wait_timeout is exceeded. Same for CREATE .. SELECT.

# Spider init should be here instead

install soname 'ha_spider';

--source include/have_innodb.inc

SET spider_same_server_link= on;

--eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);

CREATE TABLE t (a INT) ENGINE=InnoDB;

INSERT INTO t VALUES (1);

SET spider_same_server_link= on;

SET GLOBAL innodb_lock_wait_timeout= 1;

CREATE TABLE t_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'";

INSERT INTO t_spider SELECT * FROM t;

# Cleanup

DROP TABLE t_spider, t;

DROP SERVER s;

SET GLOBAL innodb_lock_wait_timeout= @@innodb_lock_wait_timeout;

# Spider de-init should be here

10.4 e4043152
mysqltest: At line 14: query 'INSERT INTO t_spider SELECT * FROM t' failed: 1205: Lock wait timeout exceeded; try restarting transaction

Reproducible on 10.4+, didn't try 10.3, I don't think there is any point in fixing it there even if it's fixable.

[MDEV-29756] Spider self-locks for the duration of innodb_lock_wait_timeout upon INSERT .. SELECT Created: 2022-10-10 Updated: 2023-11-28
Status:	Open
Project:	MariaDB Server
Component/s:	Storage Engine - Spider
Affects Version/s:	10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s:	10.4, 10.5, 10.6

[MDEV-29756] Spider self-locks for the duration of innodb_lock_wait_timeout upon INSERT .. SELECT Created: 2022-10-10 Updated: 2023-11-28