Spider/Federated Data Access Concepts, Distributed Queries, and Operational Tradeoffs

Learning outcomes

ServiceHub’s reporting team wants a local table that transparently reads rows stored on another MariaDB server. Remote-table engines can make that possible, but “SELECT looks local” does not mean the data is local, the transaction is local, or the failure modes are local. This lesson treats federation as a distributed system with explicit ownership.

1. A remote table is a network client disguised as a table

Federation minimizes copy latency because the query reaches the source at execution time. The price is coupling: source availability, network latency, credentials, remote locks and remote schema changes enter the local query path.

2. Verify the engine before writing DDL

SELECT VERSION() AS server_version;SHOW ENGINES;SELECT ENGINE,SUPPORT,COMMENTFROM information_schema.ENGINESWHERE ENGINE IN ('SPIDER','FEDERATED');SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARYFROM information_schema.PLUGINSWHERE PLUGIN_NAME LIKE '%SPIDER%' OR PLUGIN_NAME LIKE '%FEDERAT%';

No result is evidence: the feature is not currently available in this server package. Do not jump directly to INSTALL SONAME on production; first verify the OS package, plugin file, exact server version, backups, privileges and rollback behavior.

3. Optional free local Spider topology

# Debian/Ubuntu example from current MariaDB documentation:sudo apt install mariadb-plugin-spider# RHEL-family MariaDB repository example may use MariaDB-spider-engine.# Other distributions may bundle the engine differently.# Then, on the disposable Spider node only:# INSTALL SONAME 'ha_spider';

This lab needs two local MariaDB instances: a Spider node that receives the query and a data node that owns the real table. Containers, VMs, or two local ports are all acceptable free-learning paths. Never use real production credentials.

CREATE DATABASE remote21;CREATE USER 'spider_lab'@'%' IDENTIFIED BY 'replace-with-disposable-secret';GRANT SELECT,INSERT,UPDATE,DELETE ON remote21.* TO 'spider_lab'@'%';CREATE TABLE remote21.ticket_fact (  ticket_id BIGINT PRIMARY KEY,  status VARCHAR(20) NOT NULL,  updated_at DATETIME(6) NOT NULL) ENGINE=InnoDB;INSERT INTO remote21.ticket_fact VALUES (1,'open',NOW(6)),(2,'closed',NOW(6));

INSTALL SONAME 'ha_spider';SELECT ENGINE,SUPPORT FROM information_schema.ENGINES WHERE ENGINE='SPIDER';CREATE SERVER dataNode1 FOREIGN DATA WRAPPER mysqlOPTIONS ( HOST 'data-node-hostname', DATABASE 'remote21', USER 'spider_lab', PASSWORD 'replace-with-disposable-secret', PORT 3306);CREATE DATABASE advanced21_l4;CREATE TABLE advanced21_l4.ticket_fact (  ticket_id BIGINT PRIMARY KEY,  status VARCHAR(20) NOT NULL,  updated_at DATETIME(6) NOT NULL) ENGINE=SPIDERREMOTE_SERVER=dataNode1 REMOTE_DATABASE=remote21 REMOTE_TABLE=ticket_fact;SELECT * FROM advanced21_l4.ticket_fact ORDER BY ticket_id;

4. Observe remote ownership instead of trusting the abstraction

Run the same query while measuring local statement time and data-node activity. Then stop only the disposable data node. The local table definition still exists, but reads should fail or wait according to engine/network settings. That failure is the proof that availability is remote.

SHOW CREATE TABLE advanced21_l4.ticket_fact\GEXPLAIN SELECT * FROM advanced21_l4.ticket_fact WHERE ticket_id=1;SHOW FULL PROCESSLIST;-- On the data node, observe incoming sessions while a federated query runs:SHOW FULL PROCESSLIST;

EXPLAIN on the Spider node does not magically reveal every remote execution decision. For production analysis, inspect both sides and use exact Spider diagnostics/version docs. A predicate can be cheap locally but expensive remotely depending on pushdown and remote indexes.

5. Wrong approach: put a privileged password in table DDL

Embedding a broad administrative account in a connection string turns schema metadata/backups into credential material and increases blast radius. The safer pattern is a dedicated least-privilege remote identity, restricted network reachability, TLS where supported/configured, secret-management controls, and explicit credential-rotation procedures. Even CREATE SERVER definitions are sensitive metadata.

6. Transaction and consistency boundaries

Distributed storage engines can support some transaction features, and Spider documents XA-related capabilities, but application correctness still depends on the exact topology, backend engines, connection options and failure phase. A network loss after one remote participant changes state can create outcomes that are fundamentally different from a single local InnoDB transaction. Do not advertise atomicity until you have failure-injection evidence for the exact design.

7. Cleanup and checks

DROP TABLE IF EXISTS advanced21_l4.ticket_fact;DROP DATABASE IF EXISTS advanced21_l4;DROP SERVER IF EXISTS dataNode1;-- Drop disposable remote user/database on the data node.-- Uninstall the Spider plugin only if this lab installed it and no other objects depend on it.

Production judgment and bridge to Lesson 5

Use federation only when the value of live remote access exceeds the operational coupling it creates. Name the owner of the remote schema, network, credentials, latency SLO and incident response before launch. Lesson 5 generalizes that decision discipline across MariaDB’s temporal, search, spatial, vector and federation features: the right question is not “Can MariaDB do it?” but “Should this workload live here?”

Distributed access means distributed failure domains

A federated table makes a remote data source look local, but network latency and partial failure do not disappear. Each remote lookup can introduce DNS/TCP/TLS/authentication cost, remote lock waits, remote optimizer decisions, and network transfer. A local query plan may therefore hide a remote bottleneck. Measure which predicates and projections are pushed down, how many remote rows cross the network, and what happens when the remote endpoint is slow rather than completely unavailable.

Transaction semantics require especially careful documentation. Do not assume that a local transaction containing local InnoDB work and remote-engine work has one atomic commit protocol. Verify the exact engine's transaction guarantees and failure behavior. If the network breaks after one side changes state, define how the application detects and reconciles partial outcomes. For workflows that require cross-system atomicity, a federated table may be the wrong integration boundary.

Credentials and ownership are operational concerns. Remote connection information must be protected, rotated, and scoped to least privilege. The team that owns the local MariaDB query also needs an escalation path for the remote service because “database query slow” can actually mean a network or remote-system incident. Observability should include remote endpoint latency/errors and not only the local statement digest.

Compare alternatives by data freshness and ownership. ETL creates a copied dataset with scheduled freshness; CDC streams changes into another store; application/API composition keeps system boundaries explicit; a dedicated federated query engine may offer richer cross-source optimization. Spider/Federated-style access can be useful for specific MariaDB-centered integration cases, but the architecture decision should state why its consistency, latency, failure, and maintenance model fits the workload.

Remote-query test matrix: latency, outage, partial results, and recovery

Test more than the happy path. Add fixed network latency, terminate the remote endpoint, revoke/rotate the remote credential, and make a remote query block on a lock. Record what the local statement returns, how long it waits, which error is surfaced, and whether the connection/engine recovers automatically for the next request. Those observations define the application timeout and retry policy more accurately than a feature list.

For production ownership, document where the authoritative data lives, who may change the remote schema, how compatibility is coordinated, and how local queries are alerted when remote latency or row shape changes. A federated mapping is an API contract disguised as a table; schema evolution and availability therefore need cross-team coordination.

Authoritative references

• MariaDB Documentation — Spider Installation
• MariaDB Documentation — Spider Storage Engine Overview
• MariaDB Documentation — FederatedX
• MariaDB Documentation — About FederatedX