Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34113

Spider XA: ERROR 1440 (XAE08): XAER_DUPID: The XID already exists

Details

    Description

      Consider this case, placed under the spider/bugfix suite

        --disable_query_log
      		 
        --disable_result_log
      		 
        --source ../../t/test_init.inc
      		 
        --enable_result_log
      		 
        --enable_query_log
      		 
        set spider_same_server_link= 1;
      		 
        evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql
      		 
        OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root');
      		 
        CREATE TABLE t1(a int) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
      		 
        XA START 'xid2';
      		 
        BINLOG ' O1ZVRw8BAAAAZgAAAGoAAAAAAAQANS4xLjIzLXJjLWRlYnVnLWxvZwAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAA7VlVHEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC ';
      		 
        BINLOG 'wlZOTxMBAAAAKgAAADwCAAAAACkAAAAAAAEABHRlc3QAAnQxAAIDAwAC wlZOTxcBAAAAJgAAAGICAAAAACkAAAAAAAEAAv/8AgAAAAgAAAA=';
      		 
        drop server srv;
      		 
        --disable_query_log
      		 
        --disable_result_log
      		 
        --source ../../t/test_deinit.inc
      		 
        --enable_result_log
      		 
        --enable_query_log

      leads to ERROR 1440 (XAE08): XAER_DUPID: The XID already exists

      If we change Spider to INNODB then we get the following instead:
      query 'drop server srv' failed: ER_XAER_RMFAIL (1399): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-7487 Semi-join optimization for single-table update/delete statements

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            ycp Yuchen Pei added a comment -

            This is caused by spider passing the same xid to the remote server in the second binlog command. The effect is equivalent to executing XA START 'xid2'; from two clients.

            Somehow the second binlog statement causes spider to send insert high_priority into `test`.`t`(`a`)values(2) to the remote node. In this case, the XA START statement indirectly causes later during the binlog statement spider to send pass the same XA statement but with the xid hexed: xa start 0x78696432. It looks like all this is intended and not a bug.

            ycp Yuchen Pei added a comment - This is caused by spider passing the same xid to the remote server in the second binlog command. The effect is equivalent to executing XA START 'xid2'; from two clients. Somehow the second binlog statement causes spider to send insert high_priority into `test`.`t`(`a`)values(2) to the remote node. In this case, the XA START statement indirectly causes later during the binlog statement spider to send pass the same XA statement but with the xid hexed: xa start 0x78696432 . It looks like all this is intended and not a bug.
            Roel Roel Van de Paar added a comment - - edited

            If it helps, here is an additional testcase which results in ERROR 1440 (XAE08): XAER_DUPID: The XID already exists with Spider yet succeeds when t1 is InnoDB:

            INSTALL PLUGIN spider SONAME 'ha_spider.so';
            		 
            CREATE server srv FOREIGN DATA wrapper mysql options(socket '../socket.sock',DATABASE 'a',USER 'a',PASSWORD'');
            		 
            CREATE TABLE t1(c INT KEY,c1 BLOB,c2 TEXT) ENGINE=Spider COMMENT='wrapper "mysql",srv "srv",TABLE "t"';
            		 
            XA BEGIN 'a','a';
            		 
            BINLOG ' SOgWTg8CAAAAbgAAAHIAAAAAAAQANS42LjMtbTUtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABI6BZOEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAAVAYI8=';
            		 
            BINLOG ' SVtYRxMBAAAAKQAAADQBAAAAABAAAAAAAAAABHRlc3QAAnQxAAEDAAE=SVtYRxcBAAAAIgAAAFYBAAAQABAAAAAAAAEAAf/+AgAAAA==';

            Roel Roel Van de Paar added a comment - - edited If it helps, here is an additional testcase which results in ERROR 1440 (XAE08): XAER_DUPID: The XID already exists with Spider yet succeeds when t1 is InnoDB: INSTALL PLUGIN spider SONAME 'ha_spider.so' ; CREATE server srv FOREIGN DATA wrapper mysql options(socket '../socket.sock' , DATABASE 'a' , USER 'a' , PASSWORD '' ); CREATE TABLE t1(c INT KEY ,c1 BLOB,c2 TEXT) ENGINE=Spider COMMENT= 'wrapper "mysql",srv "srv",TABLE "t"' ; XA BEGIN 'a' , 'a' ; BINLOG ' SOgWTg8CAAAAbgAAAHIAAAAAAAQANS42LjMtbTUtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABI6BZOEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAAVAYI8=' ; BINLOG ' SVtYRxMBAAAAKQAAADQBAAAAABAAAAAAAAAABHRlc3QAAnQxAAEDAAE=SVtYRxcBAAAAIgAAAFYBAAAQABAAAAAAAAEAAf/+AgAAAA==' ;
            Roel Roel Van de Paar added a comment -

            Please check the following testcase, without binlog, leading to the same outcome:

            --source include/have_innodb.inc
            		 
            --let $SOCKET= `SELECT @@global.socket`
            		 
            INSTALL PLUGIN Spider SONAME 'ha_spider.so';
            		 
            SET SESSION spider_same_server_link=1;
            		 
            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 KEY,c1 BLOB,c2 TEXT) ENGINE=InnoDB;
            		 
            CREATE TABLE t2 (c INT KEY,c1 BLOB,c2 TEXT) ENGINE=Spider COMMENT='WRAPPER "mysql",SRV "srv",TABLE "t"';
            		 
            XA START 'a';
            		 
            DELETE t2.* FROM t2 t2 WHERE (SELECT 1 FROM t2);

            Leads to:

            11.2.5 a21e49cbcc5f4adb1a1b4970ceead6a85e968063 (Debug)

            		 
            DELETE t2.* FROM t2 t2 WHERE (SELECT 1 FROM t2);
            		 
            main.test                                [ fail ]
            		 
                    Test ended at 2024-07-06 12:25:13
            		 
             
            		 
            CURRENT_TEST: main.test
            		 
            mysqltest: At line 12: query 'DELETE t2.* FROM t2 t2 WHERE (SELECT 1 FROM t2)' failed: ER_XAER_DUPID (1440): XAER_DUPID: The XID already exists

            Roel Roel Van de Paar added a comment - Please check the following testcase, without binlog, leading to the same outcome: --source include/have_innodb.inc --let $SOCKET= `SELECT @@global.socket` INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; SET SESSION spider_same_server_link=1; 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 KEY ,c1 BLOB,c2 TEXT) ENGINE=InnoDB; CREATE TABLE t2 (c INT KEY ,c1 BLOB,c2 TEXT) ENGINE=Spider COMMENT= 'WRAPPER "mysql",SRV "srv",TABLE "t"' ; XA START 'a' ; DELETE t2.* FROM t2 t2 WHERE ( SELECT 1 FROM t2); Leads to: 11.2.5 a21e49cbcc5f4adb1a1b4970ceead6a85e968063 (Debug) DELETE t2.* FROM t2 t2 WHERE (SELECT 1 FROM t2); main.test [ fail ] Test ended at 2024-07-06 12:25:13   CURRENT_TEST: main.test mysqltest: At line 12: query 'DELETE t2.* FROM t2 t2 WHERE (SELECT 1 FROM t2)' failed: ER_XAER_DUPID (1440): XAER_DUPID: The XID already exists
            Roel Roel Van de Paar added a comment -

            This looks to be a regression, present in 11.1+
            In 10.11 we see ER_UPDATE_TABLE_USED:

            10.11.9 c37b2a9f045f5170189e1ed18a7d177b61beab93 (Debug)

            		 
            DELETE t2.* FROM t2 t2 WHERE (SELECT 1 FROM t2);
            		 
            main.mtr13                               [ fail ]
            		 
                    Test ended at 2024-07-06 12:33:46
            		 
             
            		 
            CURRENT_TEST: main.mtr13
            		 
            mysqltest: At line 12: query 'DELETE t2.* FROM t2 t2 WHERE (SELECT 1 FROM t2)' failed: ER_UPDATE_TABLE_USED (1093): Table 't2' is specified twice, both as a target for 'DELETE' and as a separate source for data

            Roel Roel Van de Paar added a comment - This looks to be a regression, present in 11.1+ In 10.11 we see ER_UPDATE_TABLE_USED: 10.11.9 c37b2a9f045f5170189e1ed18a7d177b61beab93 (Debug) DELETE t2.* FROM t2 t2 WHERE (SELECT 1 FROM t2); main.mtr13 [ fail ] Test ended at 2024-07-06 12:33:46   CURRENT_TEST: main.mtr13 mysqltest: At line 12: query 'DELETE t2.* FROM t2 t2 WHERE (SELECT 1 FROM t2)' failed: ER_UPDATE_TABLE_USED (1093): Table 't2' is specified twice, both as a target for 'DELETE' and as a separate source for data
            Roel Roel Van de Paar added a comment - - edited

            It looks like it exists as early as 11.1.0 @ 2b61ff8f2221745f0a96855a0feb0825c426f993 (1/4/23), but not in 11.0.

            Also note:

            INSTALL PLUGIN Spider SONAME 'ha_spider.so';
            		 
            Warnings:
            		 
            Warning	1105	Cannot enable tc-log at run-time. XA features of SPIDER are disabled

            Though this is shown in all versions, inc for example 10.5 i.e. not just those resulting in ER_XAER_DUPID (1440). Perhaps this can be cleaned up at the same time?

            Roel Roel Van de Paar added a comment - - edited It looks like it exists as early as 11.1.0 @ 2b61ff8f2221745f0a96855a0feb0825c426f993 (1/4/23), but not in 11.0. Also note: INSTALL PLUGIN Spider SONAME 'ha_spider.so'; Warnings: Warning 1105 Cannot enable tc-log at run-time. XA features of SPIDER are disabled Though this is shown in all versions, inc for example 10.5 i.e. not just those resulting in ER_XAER_DUPID (1440) . Perhaps this can be cleaned up at the same time?
            Roel Roel Van de Paar added a comment - - edited

            The bisect points to this as the first bad commit:
            https://github.com/MariaDB/server/commit/554278e24dbc2c0af9fcfd66c54ca6a99a3cf17f

            commit 554278e24dbc2c0af9fcfd66c54ca6a99a3cf17f
            		 
            Author: Igor Babaev <igor@askmonty.org>   
            Date:   Mon Jan 9 22:39:39 2023 -0800
            		 
             
            		 
                MDEV-7487 Semi-join optimization for single-table update/delete statements
            		 
             
            		 
                This patch allows to use semi-join optimization at the top level of
            		 
                single-table update and delete statements.
            		 
                The problem of supporting such optimization became easy to resolve after
            		 
                processing a single-table update/delete statement started using JOIN
            		 
                structure. This allowed to use JOIN::prepare() not only for multi-table
            		 
                updates/deletes but for single-table ones as well. This was done in the
            		 
                patch for mdev-28883:
            		 
                Re-design the upper level of handling UPDATE and DELETE statements.
            		 
             
            		 
                Note that JOIN::prepare() detects all subqueries that can be considered
            		 
                as candidates for semi-join optimization. The code added by this patch
            		 
                looks for such candidates at the top level and if such candidates are found
            		 
                in the processed single-table update/delete the statement is handled in
            		 
                the same way as a multi-table update/delete.
            		 
             
            		 
                    Approved by Oleksandr Byelkin <sanja@mariadb.com>

            While it the first bad commit would be close to this commit, this may not be (or may be) the actual introducing commit as the last commit tested in the bisect asserts on startup with Assertion `!lex->explain' failed.

            OTOH, the last crashing testcase does use DELETE. It may be that the original BINLOG testcases are a different issue, though the outcome is the same and XA is used in both cases.

            Roel Roel Van de Paar added a comment - - edited The bisect points to this as the first bad commit: https://github.com/MariaDB/server/commit/554278e24dbc2c0af9fcfd66c54ca6a99a3cf17f commit 554278e24dbc2c0af9fcfd66c54ca6a99a3cf17f Author: Igor Babaev <igor@askmonty.org> Date: Mon Jan 9 22:39:39 2023 -0800   MDEV-7487 Semi-join optimization for single-table update/delete statements   This patch allows to use semi-join optimization at the top level of single-table update and delete statements. The problem of supporting such optimization became easy to resolve after processing a single-table update/delete statement started using JOIN structure. This allowed to use JOIN::prepare() not only for multi-table updates/deletes but for single-table ones as well. This was done in the patch for mdev-28883: Re-design the upper level of handling UPDATE and DELETE statements.   Note that JOIN::prepare() detects all subqueries that can be considered as candidates for semi-join optimization. The code added by this patch looks for such candidates at the top level and if such candidates are found in the processed single-table update/delete the statement is handled in the same way as a multi-table update/delete.   Approved by Oleksandr Byelkin <sanja@mariadb.com> While it the first bad commit would be close to this commit, this may not be (or may be) the actual introducing commit as the last commit tested in the bisect asserts on startup with Assertion `!lex->explain' failed . OTOH, the last crashing testcase does use DELETE. It may be that the original BINLOG testcases are a different issue, though the outcome is the same and XA is used in both cases.
            Roel Roel Van de Paar added a comment - - edited 

            SET sql_mode='';
            		 
            INSTALL PLUGIN Spider SONAME 'ha_spider.so';
            		 
            GRANT ALL ON * TO Spider@localhost;
            		 
            CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE 'test',user 'Spider',PASSWORD'');
            		 
            CREATE TABLE t1 (a INT) ENGINE=Spider COMMENT='WRAPPER "mysql",SRV "srv",TABLE "t"';
            		 
            XA START 'conn1_trx_1';
            		 
            BINLOG ' O1ZVRw8BAAAAZgAAAGoAAAAAAAQANS4xLjIzLXJjLWRlYnVnLWxvZwAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAA7VlVHEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC ';
            		 
            SET SESSION max_error_count=-1;
            		 
            BINLOG 'wlZOTxMBAAAAKgAAADwCAAAAACkAAAAAAAEABHRlc3QAAnQxAAIDAwAC wlZOTxcBAAAAJgAAAGICAAAAACkAAAAAAAEAAv/8AgAAAAgAAAA=';

            Leads to:

            11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Debug)

            		 
            ERROR 1440 (XAE08): XAER_DUPID: The XID already exists

            11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Debug)

            		 
            2024-08-12  0:31:26 4 [ERROR]  BINLOG_BASE64_EVENT: Could not execute Write_rows_v1 event on table test.t1; handler error No Error!; the event's master log FIRST, end_log_pos 610, Internal MariaDB error code: 1440

            Roel Roel Van de Paar added a comment - - edited SET sql_mode= '' ; INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; GRANT ALL ON * TO Spider@localhost; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' , DATABASE 'test' , user 'Spider' , PASSWORD '' ); CREATE TABLE t1 (a INT ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",SRV "srv",TABLE "t"' ; XA START 'conn1_trx_1' ; BINLOG ' O1ZVRw8BAAAAZgAAAGoAAAAAAAQANS4xLjIzLXJjLWRlYnVnLWxvZwAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAA7VlVHEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC ' ; SET SESSION max_error_count=-1; BINLOG 'wlZOTxMBAAAAKgAAADwCAAAAACkAAAAAAAEABHRlc3QAAnQxAAIDAwAC wlZOTxcBAAAAJgAAAGICAAAAACkAAAAAAAEAAv/8AgAAAAgAAAA=' ; Leads to: 11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Debug) ERROR 1440 (XAE08): XAER_DUPID: The XID already exists 11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Debug) 2024-08-12 0:31:26 4 [ERROR] BINLOG_BASE64_EVENT: Could not execute Write_rows_v1 event on table test.t1; handler error No Error!; the event's master log FIRST, end_log_pos 610, Internal MariaDB error code: 1440

            People

              ycp Yuchen Pei
              ycp Yuchen Pei
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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