|
The issue is more serious than I originally thought. It is seen in many trials, and in a variety of situations as exemplified by the testcases below. It is thought that many XA transactions are affected. It also significantly affects testing.
|
|
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);
|
CREATE TABLE t1 (a INT NOT NULL, b CHAR(110),PRIMARY KEY(a,b (100))) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"' COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"' DEFAULT CHARSET=utf8;
|
XA START 'XA1';
|
SELECT DISTINCT GREATEST(a, (SELECT b FROM t1 LIMIT 1)) FROM t1 UNION SELECT 1;
|
Leads to
|
10.11.0 bc563f1a4b0b38de3b41fd0f0d3d8b7f1aacbd8b (Debug)
|
10.11.0-dbg>SELECT DISTINCT GREATEST(a, (SELECT b FROM t1 LIMIT 1)) FROM t1 UNION SELECT 1;
|
ERROR 1440 (XAE08): XAER_DUPID: The XID already exists
|
And
|
10.11.0 bc563f1a4b0b38de3b41fd0f0d3d8b7f1aacbd8b (Debug)
|
2022-09-03 13:36:30 4 [ERROR] Got error 1440 when reading table './test/t1'
|
|
|
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 BLOB);
|
CREATE TABLE t1 (user_id INT(10) DEFAULT '0' NOT NULL, name VARCHAR(100), phone VARCHAR(100), ref_email VARCHAR(100) DEFAULT '' NOT NULL, detail VARCHAR(200), PRIMARY KEY(user_id,ref_email)) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';
|
XA START 'xa';
|
SET @c=(SELECT 1 NOT IN (SELECT 1 FROM t1 AS t1 GROUP BY 1 LIKE (SELECT 1 FROM t1 AS t2)) AS col);
|
Leads to:
|
10.11.0 bc563f1a4b0b38de3b41fd0f0d3d8b7f1aacbd8b (Optimized)
|
10.11.0-opt>SET @c=(SELECT 1 NOT IN (SELECT 1 FROM t1 AS t1 GROUP BY 1 LIKE (SELECT 1 FROM t1 AS t2)) AS col);
|
ERROR 1440 (XAE08): XAER_DUPID: The XID already exists
|
And
|
10.11.0 bc563f1a4b0b38de3b41fd0f0d3d8b7f1aacbd8b (Optimized)
|
2022-09-03 13:38:35 4 [ERROR] Got error 1440 when reading table './test/t1'
|
|
|
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
|
CREATE USER Spider@localhost IDENTIFIED BY 'PWD123';
|
GRANT ALL ON test.* TO Spider@localhost;
|
CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock', DATABASE 'test', USER 'Spider', PASSWORD 'PWD123');
|
CREATE TABLE t1 (id INT, grp TINYINT, id_rev INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"' COMMENT='TABLE "st"' PARTITION BY LIST COLUMNS (c1) (PARTITION p1 DEFAULT COMMENT='srv "d"' ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"');
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"' COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';
|
CREATE TEMPORARY TABLE t24918_tmp (id INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"' COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';
|
XA START 'XA2';
|
HANDLER t1 OPEN;
|
SHOW WARNINGS;
|
DROP TABLE t703;
|
HANDLER t1 READ NEXT;
|
Leads to:
|
10.11.0 bc563f1a4b0b38de3b41fd0f0d3d8b7f1aacbd8b (Debug)
|
10.11.0-dbg>CREATE TEMPORARY TABLE t24918_tmp (id INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"' COM
|
MENT='WRAPPER "mysql", srv "srv", TABLE "t"';
|
ERROR 1478 (HY000): Table storage engine 'SPIDER' does not support the create option 'TEMPORARY'
|
...
|
10.11.0-dbg>HANDLER t1 OPEN;
|
Query OK, 0 rows affected, 2 warnings (0.031 sec)
|
10.11.0-dbg>SHOW WARNINGS;
|
+-------+-------+------------------------------------+
|
| Level | Code | Message |
|
+-------+-------+------------------------------------+
|
| Error | 12702 | Remote table 'test.t' is not found |
|
| Error | 1146 | Table 'test.t' doesn't exist |
|
+-------+-------+------------------------------------+
|
2 rows in set (0.000 sec)
|
10.11.0-dbg>DROP TABLE t703;
|
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
|
10.11.0-dbg>HANDLER t1 READ NEXT;
|
ERROR 1440 (XAE08): XAER_DUPID: The XID already exists
|
And
|
10.11.0 bc563f1a4b0b38de3b41fd0f0d3d8b7f1aacbd8b (Debug)
|
2022-09-03 13:31:57 4 [ERROR] mysql_ha_read: Got error 1440 when reading table 't1'
|
|
|
Many (95%+) of the trials for which this issue happened end up crashing like MDEV-27575.
It is also possible that they result in a hang. This latter item is still being researched.
|
|
Same result (CLI + error log output of error 1440) with:
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
|
CREATE USER Spider@localhost IDENTIFIED BY 'PWD1';
|
CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE 'test',user 'Spider',PASSWORD 'PWD1');
|
CREATE TABLE t (c INT) ENGINE=InnoDB;
|
CREATE TABLE t2 (c1 INT,c2 VARCHAR(20),KEY(c1,c2)) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
|
XA START 'a';
|
SELECT 1 IN (SELECT 1 FROM t2);
|
|