[MDEV-29454] Spider XA Failures | ERROR 1440 (XAE08): XAER_DUPID: The XID already exists | Got error 1440 when reading table on SELECT after XA START | mysql_ha_read: Got error 1440 - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Fix Version/s: 10.5, 10.6
Component/s: Storage Engine - Spider, XA
Labels:
- XA
- affects-tests
- hang

Description

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);

CREATE TABLE t1 (f INT,f2 DATE,f3 DATE,KEY(f,f3,f2)) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';

XA START 'xid1';

SELECT COUNT(*)=1 FROM t1;

Leads to:

10.11.0 bc563f1a4b0b38de3b41fd0f0d3d8b7f1aacbd8b (Optimized)
10.11.0-opt>SELECT COUNT(*)=1 FROM t1;
ERROR 1440 (XAE08): XAER_DUPID: The XID already exists

And in the error log:

10.11.0 bc563f1a4b0b38de3b41fd0f0d3d8b7f1aacbd8b (Optimized)
2022-09-03 10:02:13 4 [ERROR] Got error 1440 when reading table './test/t1'

When the last query is changed to:

SELECT COUNT(*)=1 FROM t1;

Only the CLI message appears.

Attachments

Issue Links

relates to

MDEV-27575 Spider: UBSAN member access within null pointer of type 'struct st_plugin_int and SIGSEGV in intern_plugin_lock on SHUTDOWN when setting Spider as default storage engine (temporary or global)

Closed

Activity

Ascending order - Click to sort in descending order

Roel Van de Paar added a comment - 2022-09-03 03:35

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.

Roel Van de Paar added a comment - 2022-09-03 03:35 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.

Roel Van de Paar added a comment - 2022-09-03 03:36

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'

Roel Van de Paar added a comment - 2022-09-03 03:36 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'

Roel Van de Paar added a comment - 2022-09-03 03:39 - edited

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'

Roel Van de Paar added a comment - 2022-09-03 03:39 - edited 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'

Roel Van de Paar added a comment - 2022-09-03 03:49

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'

Roel Van de Paar added a comment - 2022-09-03 03:49 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'

Roel Van de Paar added a comment - 2022-09-03 03:49

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.

Roel Van de Paar added a comment - 2022-09-03 03:49 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.

Roel Van de Paar added a comment - 2022-10-07 05:55

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);

Roel Van de Paar added a comment - 2022-10-07 05:55 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);

People

Assignee:: Alexey Botchkov

Reporter:: Roel Van de Paar

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2022-09-03 00:08

Updated:: 2024-09-10 14:56

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration