Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.9
-
None
-
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
- relates to
-
MDEV-16610 Torn reads from spider, with XA and isolation level REPEATABLE READ
- Open