Bookmark and Share
spider data sharding and replication
(Publish Date: 2009-10-22 5:20pm, Total Visits: 2343, Today: 1, This Week: 1, This Month: 6)

A. what is spider? (extracted from spider document)

1. Spider Storage Engine creates table links from local databases to remote databases.
   Spider makes available tables in remote MySQL servers to be used like that in local MySQL servers.
2. Spider can create sharding of the databases.
3. Spider supports XA transaction and table partitioning.
4. Spider can be used for DB clustering
5. Spider is being offered to the public by GPL, the system diagram likes the following:

And in spider data engine:

– Tables in different servers can be joined.
– The application does not need to implement synchronized update. (Spider does it.)
– The application engineers can develop applications without DB sharding skills.
– It is very easy to deploy on the database for it, usually requires no changes in the application and the SQL.


B. to test spider data engine, please follow this link

C. spider and replication

If you do replication between spider data engines, you must use XA transactions. Here is the example:

On spider data engine main server:

CREATE TABLE `article` (
  `col_a` int(11) NOT NULL DEFAULT '0',
  `col_b` int(11) DEFAULT NULL,
  PRIMARY KEY (`col_a`)
) ENGINE=SPIDER DEFAULT CHARSET=latin1 CONNECTION=' table "article", user "username", password "password" '
BY RANGE ( col_a)
COMMENT = 'host "xx.xx.xx.xx", port "6001"' ENGINE = SPIDER,
COMMENT = 'host " xx.xx.xx.xx ", port "6002"' ENGINE = SPIDER,
COMMENT = 'host " xx.xx.xx.xx ", port "6003"' ENGINE = SPIDER);


on a partition server, p1, p2, p3:

CREATE TABLE `article` (
  `col_a` int(11) NOT NULL DEFAULT '0',
  `col_b` int(11) DEFAULT NULL,
  PRIMARY KEY (`col_a`)
) ENGINE=innoDB;


transactions should be like the following:

XA START 'xatest';
insert into article values (2004,4);
delete from article where col_a=2001;
update article set col_b=1 where col_a=1;
XA END 'xatest';
XA PREPARE 'xatest';
XA COMMIT 'xatest';


If you don't put the transaction in XA, you will get the following errors:

ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  NON-EXISTING state

ERROR 12605 (HY000): This xid is already exist
ERROR 12605 (HY000): This xid is already exist