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

spider xa savepoint not supported/inconsistent

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5.9
    • Fix Version/s: 10.5
    • Labels:
      None
    • Environment:
      offical docker image+spider plugin installed;
      mariadb-plugin-spider/now 1:10.5.9+maria~focal amd64

      Description

      While this is both a bug report and a feature request. It would be great if the spider engine would support savepoint's like InnoDB does.

      as test i've created 2 backend servers with tables;

      create database spider;
       
      CREATE TABLE spider.sbtest (
        id bigint unsigned NOT NULL AUTO_INCREMENT,
        n1 int(10) unsigned NOT NULL DEFAULT '0',
        v1 varchar(120) NOT NULL DEFAULT '',  
        PRIMARY KEY (id)
      ) ENGINE=InnoDB;
      

      on the main database instance i've created the connections and remote table;

      create database spider;
       
      CREATE SERVER spider1 
        FOREIGN DATA WRAPPER mysql 
      OPTIONS( 
        HOST 'spider1', 
        DATABASE 'spider',
        USER 'root',
        PASSWORD 'xxx',
        PORT 3306
      );
       
      CREATE SERVER spider2
        FOREIGN DATA WRAPPER mysql 
      OPTIONS( 
        HOST 'spider2', 
        DATABASE 'spider',
        USER 'root',
        PASSWORD 'xxx',
        PORT 3306
      );
       
      CREATE  TABLE spider.sbtest
      (
        id bigint unsigned NOT NULL AUTO_INCREMENT,
        n1 int(10) unsigned NOT NULL DEFAULT '0',
        v1 varchar(120) NOT NULL DEFAULT '',  
        PRIMARY KEY (id)
      ) ENGINE=spider COMMENT='wrapper "mysql", table "sbtest"'
       PARTITION BY KEY (id) 
      (
       PARTITION pt1 COMMENT = 'srv "spider1"',
       PARTITION pt2 COMMENT = 'srv "spider2"' 
      ) ;
      

      When i try to start a XA transaction with a savepoint, and put the savepoint before i insert data, the savepoint actually works;

      xa start 'xatrans';
      SAVEPOINT point1;
      insert into spider.sbtest (n1,v1) values (1,'abc');
      ROLLBACK TO SAVEPOINT point1;
      XA END 'xatrans';
      XA PREPARE 'xatrans'; 
      XA COMMIT 'xatrans';
       
      #data in savepoint is rolled back and no error's are thrown;
      select * from spider.sbtest;
      

      throws error;

      xa start 'xatrans';
      SAVEPOINT point1;
      insert into spider.sbtest (n1,v1) values (1,'abc');
      SAVEPOINT point2; #throws error: Error Code: 1178. The storage engine for the table doesn't support SAVEPOINT
      insert into spider.sbtest (n1,v1) values (2,'abc');
      XA END 'xatrans';
      XA PREPARE 'xatrans'; 
      XA COMMIT 'xatrans';
      

      It seems the spider engine seems to store the "to insert data" first locally, otherwise the savepoint 1 wouldn't have worked either.
      I also suspect this, because the first time I started the XA transaction and ran a XA PREPARE, i received this warning:
      1030 Got error 131 "Command not supported by the engine" from storage engine Aria
      Every subsequent call to

      XA PREPARE 'xxx'; 
      

      didn't produce this warning

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nayuta-yanagisawa Nayuta Yanagisawa
              Reporter:
              olafbuitelaar Olaf Buitelaar
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.