The request landed on a Tuesday: finance wanted yesterday's payment-gateway logs — a 4 GB CSV dumped hourly by a vendor job — joined against the orders table, and they wanted it "in the BI tool, not in a spreadsheet." The ETL team's backlog was six weeks deep. Someone in the channel remembered that MariaDB ships a storage engine called CONNECT that can mount a CSV file as a table, and by Tuesday afternoon we had CREATE TABLE ... ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='/data/exports/gateway.csv' running in production. It was genuinely magical for three weeks. Analysts wrote plain SQL against a file. Joins worked. Then at 03:02 on a Sunday, logrotate rotated the CSV out from under a running report, and every query against the table started failing with ERROR 1296 (HY000): Got error 174 'file error' from CONNECT. Nobody owned the table. Nobody had documented that it was a file. The on-call runbook said "restart the report," which restarted it into the same missing file.
CONNECT is one of the most useful and most casually dangerous engines in MariaDB: it turns external data — files, remote servers, ODBC sources — into first-class tables, with all the operational consequences that implies and none of the guardrails a real pipeline would give you. These are the notes I wrote for our team after that month: what CONNECT actually is, the file-backed patterns that survive production, the remote-table patterns that mostly do not, and the monitoring you need around data the server does not manage.
What does the CONNECT engine actually do?
CONNECT is a storage engine bundled with MariaDB since 10.0 that exposes non-MariaDB data sources as normal tables you can SELECT, and in many cases INSERT and UPDATE, through the ordinary SQL layer. The table definition lives in the data dictionary, but the data lives wherever CONNECT points it: a CSV file, a JSON document, a remote MySQL or MariaDB server over the client API, or anything reachable through an ODBC or JDBC driver. To the optimizer it looks like a table; to the filesystem it is an open file handle:
-- a CSV export as a queryable table
CREATE TABLE gateway_log (
txn_id BIGINT NOT NULL,
merchant_ref VARCHAR(64) NOT NULL,
amount_cents INT NOT NULL,
status VARCHAR(16) NOT NULL,
processed_at DATETIME NOT NULL
) ENGINE=CONNECT
TABLE_TYPE=CSV
FILE_NAME='/data/exports/gateway.csv'
HEADER=1
SEP_CHAR=','
QCHAR='"';
-- a table that IS a table on another MariaDB server
CREATE TABLE remote_inventory (
sku VARCHAR(32) NOT NULL,
on_hand INT NOT NULL
) ENGINE=CONNECT
TABLE_TYPE=MYSQL
CONNECTION='mysql://etl:secret@warehouse.internal:3306/ops/inventory';The mental model that keeps you out of trouble: CONNECT is an adapter, not a storage engine. It has no buffer pool, no crash recovery, no transactional guarantees of its own, and no idea whether the thing behind it still exists. Every SELECT against a file-backed table re-reads the file — there is no caching layer between you and 4 GB of CSV, which is why our first finance query took eleven minutes and pinned a core. Indexes exist on some table types (CSV supports them, built on demand into a sidecar file), but the optimizer statistics are thin, so joins between a CONNECT table and InnoDB tables routinely choose plans that scan the file once per outer row. We capped that class of query with a hard timeout, using the max_statement_time pattern from the statement time limits notes, after a single bad join held a reporting connection for forty minutes.
Which file-backed patterns survive production?
The patterns that last are the ones where the file is append-only, immutable once written, or replaced atomically — because CONNECT's failure modes are all about the file changing shape mid-read. Our logrotate incident was the canonical one: the rotation job moved gateway.csv to gateway.csv.1 and created a fresh file, and any query that had opened the old descriptor either read a truncated stream or failed outright. The fixes are boring and they work: have the producer write to a dated file name and point the CONNECT table at a stable symlink that is swapped atomically, or use TABLE_TYPE=VEC over a directory of files that only ever gains members. The anti-pattern list is equally concrete: never point CONNECT at a file a long-running job is still appending to if rows are variable-length, never let the producer rewrite the file in place, and never assume the file exists — wrap the report in a check for ERROR 1296 and fail loudly instead of retrying into the same error for forty minutes.
One capability deserves special mention because it replaced a cron job for us: the PIVOT and XCOL table types, and the JSON table type for vendor payloads. A supplier sends a nightly JSON document with nested line items; TABLE_TYPE=JSON with JPATH expressions mapped the nested arrays into relational rows without a single line of parsing code. The catch is schema drift — when the supplier added a field and reordered another, the JPATH mapping silently returned NULLs for a week because JSON mapping failures are data, not errors. Any CONNECT table whose source you do not control needs a canary query that asserts row counts and non-NULL rates, run on a schedule, because the engine will not tell you the data went stale or shapeless. That is the same observability gap I wrote about in the userstat observability piece: the data is queryable, therefore everyone assumes it is healthy.
When does the remote MYSQL table type make sense?
TABLE_TYPE=MYSQL turns a table on another MariaDB or MySQL server into a local table — the spiritual successor to the old FEDERATED engine, and the right tool for exactly one job: occasional, low-volume cross-server lookups where standing up replication or an ETL pipeline is disproportionate. Our legitimate use was a nightly reconciliation job that pulled a few thousand rows of reference data from a vendor-managed MariaDB we could not replicate. It ran for two years without incident. Every other attempt I have watched has collapsed on the same three rocks. First, latency and availability coupling: a query joining a CONNECT-MYSQL table inherits the remote server's latency and downtime, and we had a Friday where the vendor server's maintenance window became our reporting outage. Second, credentials in the table definition: the CONNECTION string sits in the data dictionary and shows up in SHOW CREATE TABLE for anyone with sufficient privileges, which our auditors flagged immediately — rotate those credentials and you are running ALTER TABLE, which nobody remembers to do. Third, pushdown limits: the engine pushes some predicates to the remote side but not all, so a WHERE clause you think is selective can pull the entire remote table across the wire and filter locally.
The honest decision rule: if the data crosses servers more often than daily, or more rows than a few thousand per query, build the pipeline. Replicate the reference table with the multi-source setup from the multi-source replication notes if you need it fresh and local. CONNECT-MYSQL is a bridge you cross occasionally, not a road you commute on.
Where MonPG fits
The signals worth trending around CONNECT are the ones the server cannot see for you: query latency and error rates on CONNECT tables specifically, ERROR 1296/174 counts as your "the file moved" alarm, row-count and NULL-rate canaries on tables whose source is a vendor, and connection hold time on CONNECT-MYSQL tables as your coupling gauge. Full disclosure, as in every article of this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and per-engine error rates plus scheduled canary checks are on the list of things it is being built around. Until that ships, a cron job with the queries above is your kit. If PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview, or browse more field notes on the blog.