Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3, 10.4
-
Tested with MariaDB 10.4.5 & 10.3.15, derived from official Docker image + "apt-get install mariadb-plugin-spider"
Description
IIUC, if I configure Spider table to shard over several backends, and the transaction given to the Spider table affects multiple shards and those shards cross more than one machine, it should use a two-phase XA commit in the group of (remote) nodes.
Well, it doesn't. I observe regular transactions happening in series, not even in parallel.
All backend servers are running InnoDB with the same MariaDB version as the Spider. According to docs, XA support is enabled by default; haven't touched a thing regarding that.
I may be doing something wrong, this being my first Spider installation. In that case, this issue is filed against the documentation. Thanks!
I've started this as a Q on DBA.SE, but I believe it's a bug, hence this issue. Details from there:
- mariadb-server version 10.4.5+maria~bionic
- mariadb-plugin-spider version 1:10.4.5+maria~bionic
- (Also happens with 1:10.3.15+maria~bionic, excluded this is 10.4.x only.)
CREATE OR REPLACE SERVER srvA
|
FOREIGN DATA WRAPPER mysql
|
OPTIONS (USER 'spider', PASSWORD 'spider', PORT 3306, HOST '192.168.199.10', DATABASE 'testdb');
|
|
|
CREATE OR REPLACE SERVER srvB
|
FOREIGN DATA WRAPPER mysql
|
OPTIONS (USER 'spider', PASSWORD 'spider', PORT 3306, HOST '192.168.199.11', DATABASE 'testdb');
|
|
|
create table mytable (
|
id INT unsigned NOT NULL,
|
some_field TEXT(100) DEFAULT NULL,
|
PRIMARY KEY (id),
|
)
|
ENGINE=spider
|
partition by list (mod(id, 2)) (
|
partition p00 values in(0) comment = 'server "srvA", table "mytable_shard0"',
|
partition p01 values in(1) comment = 'server "srvB", table "mytable_shard1"'
|
);
|
Everything else on the Spider is left at the default settings.
Looking at Wireshark PCAPs, I see this in chronological packet-id order on the single bridge interface all my test nodes are connected on:
- client connects to Spider node, setting connection parameters, disable autocommit, set transaction level SERIALIZABLE.
- client doing insert on Spider node:
- sending START TRANSACTION.
- large 1000-row single-statement insert spreading shards (unknowingly about shards). awaiting response...
- Spider communicates with srv1:
- Spider connect to srv1, setting connection parameters, set transaction level SERIALIZABLE, etc.
- Spider sends the insert statement of rows for srv1 to srv1.
- Spider receiving OK from srv1, but in-transaction.
- Spider communicates with srv2:
omitted, same as with srv1. - client receiving OK, but in-transaction.
- client sending COMMIT.
- Spider sending COMMIT to srv1, srv1 acks commit
- Spider sending COMMIT to srv2, srv2 acks commit
- client receiving OK from Spider, not in-transaction.
My observations derived from the above events:
- I don't see any XA commit statements happening at all from Spider to backend servers. All seem serial regular commits. Huh!?
- All seems serially committed in one-phase to backend servers one-after-another (3 and 4 in the observation list do not cross in time), not in parallel. Why? It seems slower and unnecessary.
(I've double-checked with Wireshark filters mysql.query contains "prepare", mysql.query contains "xa" that no single MySQL query packet contains this.)
How can I obtain XA-2PC as depicted in the image from https://mariadb.com/kb/en/library/spider-storage-engine-overview/#sharding-setup ? I need to have strict guarantees that ALL shards have seen and committed their part of the original client-side commit, OR rollback as a group.
Double-checked that my backend-servers are indeed capable of XA (run directly on srvA):
mysql> XA START 'test';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql> XA END 'test';
|
Query OK, 0 rows affected (0.00 sec)
|
And that the Spider node has it configured OK:
mysql> show variables WHERE Variable_name like 'spider%xa%' or Variable_name like 'spider%semi%' or Variable_name like 'spider%commit%';
|
+-----------------------------------+-------+
|
| Variable_name | Value |
|
+-----------------------------------+-------+
|
| spider_force_commit | 1 |
|
| spider_internal_xa | OFF |
|
| spider_internal_xa_id_type | 0 |
|
| spider_internal_xa_snapshot | 0 |
|
| spider_remote_autocommit | -1 |
|
| spider_semi_split_read | -1 |
|
| spider_semi_split_read_limit | -1 |
|
| spider_semi_table_lock | 1 |
|
| spider_semi_table_lock_connection | -1 |
|
| spider_semi_trx | ON |
|
| spider_semi_trx_isolation | -1 |
|
| spider_support_xa | ON |
|
| spider_sync_autocommit | ON |
|
| spider_xa_register_mode | 1 |
|
+-----------------------------------+-------+
|