Our first post showed MySQL 9.7 with one change: mark a table ENGINE=DuckDB and its analytical queries run in DuckDB instead of InnoDB. The question we kept getting after that was about replication. Can you keep a normal InnoDB primary for the writes, and run a replica where the big tables are ENGINE=DuckDB? Then the heavy reports run on a column store, and ordinary MySQL replication keeps it current. No export job. No second database to sync by hand.
So we tried it. The first run failed, and it failed in a way that is easy to miss: the replica took every transaction, reported success, and stored nothing. We tracked down why, fixed it, and the whole test suite passes now. This post is what we tested, how we checked it, the bug we found, and where it stands.
It’s still an experiment, not production software. The code and the test harness are on GitHub under GPLv2: https://github.com/Percona-Lab/ducksdb-mysql-engine.
Why replicate into DuckDB
A DuckDB table on one server is already useful. The analytical queries get fast and the application does not change. But almost nobody runs their reports on the primary – they run them on a replica, so the big scans stay out of the way of the OLTP traffic.
So the shape of it is simple. The primary stays InnoDB and takes the writes. The replica has the same tables, only marked ENGINE=DuckDB. Row-based replication ships the changes across, the replica writes them into the column store, and the reports run there. You get an analytics replica out of the replication you already run.
Row events are engine-agnostic on purpose. The primary logs the row changes, not the SQL, and the replica applies them through the storage-engine API. On paper, then, the replica should not care that one side is InnoDB and the other DuckDB. We wanted to see the paper version hold up on a running server.
The setup
Two containers from the same image, one primary and one replica. It’s all in Docker, so it repeats cleanly.
- Primary: InnoDB, binlog_format=ROW, GTID on.
- Replica: same server, GTID on, tables made with ENGINE=DuckDB.
- Replication uses SOURCE_AUTO_POSITION=1.
One thing you have to get right before any data moves. Create the replica tables as ENGINE=DuckDB yourself. A CREATE TABLE … ENGINE=InnoDB on the primary goes into the binlog with the ENGINE word still in it, and the replica runs it exactly as written, so you would end up with an InnoDB table there, not a DuckDB one. There is no automatic mapping. Pre-create the DuckDB tables on the replica, and let the row changes flow into them.
-- primary (InnoDB) CREATE TABLE t1 (id BIGINT PRIMARY KEY, region INT, amount DECIMAL(12,2)) ENGINE=InnoDB; -- replica (same columns, DuckDB) CREATE TABLE t1 (id BIGINT PRIMARY KEY, region INT, amount DECIMAL(12,2)) ENGINE=DuckDB;
The other rule is a primary key on the replica table. UPDATE and DELETE row events find the row by its old image, and the engine needs the key for that. INSERT works without one, but put a key on it anyway.
One script drives all of this: bench/tb/07-replication-spike.sh. It starts both containers, wires up replication, runs every scenario below, and prints PASS or FAIL for each.
What we tested, and how
The part that matters is the checking. Row counts are not enough – the replica can hold the right number of rows and still have the wrong data in them. So after each step the script dumps the whole table on both sides, ordered by primary key, and compares an md5 of the two dumps. One byte off is a FAIL. And rather than sleep between steps, it waits on WAIT_FOR_EXECUTED_GTID_SET(), so the checks do not race the replica.
Here is what went through it.
Basic DML. Insert, update a row, delete a row, compared after each one.
All the column types, in a single wide table: signed and unsigned integers, DECIMAL, DOUBLE, DATE, DATETIME, TIMESTAMP, CHAR, VARCHAR, TEXT, BLOB, a few NULLs, and a unicode string. Insert it, update it, compare byte for byte. Blobs get their own note below.
DDL. ALTER TABLE ADD COLUMN, ALTER TABLE ADD INDEX, and DROP TABLE against a DuckDB replica table. These arrive as statements. We check that the column shows up, the index shows up, the old rows survive, and the drop removes the table.
Transactions. A transaction with two inserts and an update has to land on the replica as one unit. A transaction the primary rolls back has to leave nothing behind. We also open a transaction straight on the replica and both roll it back and commit it, to check the engine’s own commit and rollback.
Bulk load. 5000 rows through LOAD DATA on the primary, has to arrive and match.
Durability. Two cases, and the second is the hard one.
- Clean restart. Stop the replica properly, write on the primary while it is down, start it again, and see it pick up from its GTID position.
- Crash. Apply some rows, then SIGKILL the replica. No clean shutdown, no checkpoint. Bring it back, write more on the primary, and check one exact thing: every row present once. Nothing lost – DuckDB has to replay its write-ahead log when it opens the file – and nothing applied twice, which means the saved position has to line up with the data that actually reached disk.
The bug: multi-engine transactions lost data
The first full run fell down on the wide-table test. Zero rows on the replica, and then everything after it failed too. The applier had stopped with HA_ERR_KEY_NOT_FOUND. It went to UPDATE a row that was not there, because the INSERT before it had returned success and written nothing.
When a scenario fails, the harness saves the applier error, both server logs, and both schemas. The replica log had the line that mattered:
[Warning] Combining the storage engines InnoDB and DuckDB is deprecated, but the
statement or transaction updates both the InnoDB table mysql.slave_worker_info and the
DuckDB table rpl.wide.
That line is the whole thing. A replica does not only write your data. In the same transaction it also writes its own position into InnoDB system tables – mysql.slave_worker_info, the relay-log info, gtid_executed. So every applied transaction touches two engines at once: InnoDB for the position, DuckDB for the data. Two engines means MySQL runs a real two-phase commit: prepare, then commit.
Our prepare was wrong. It took the open DuckDB transaction, moved it into a registry meant for external XA COMMIT, and cleared the per-connection state. Then commit looked at that state, found it empty, and committed nothing. The position went into InnoDB, the GTID advanced, the binlog moved on, and the DuckDB rows were thrown away. No error anywhere. The replica looked healthy while it dropped every write.
We cut it down to the smallest case, with no replication at all. One server, one transaction into a DuckDB table and an InnoDB table:
BEGIN; INSERT INTO duck VALUES (1,10),(2,20),(3,30); -- DuckDB INSERT INTO inno VALUES (1,10),(2,20),(3,30); -- InnoDB COMMIT; -- duck: 0 rows inno: 3 rows
InnoDB kept its three rows, DuckDB kept none, and COMMIT said it was fine. A DuckDB-only transaction was fine as well, because with one engine MySQL skips the prepare step. It only broke with a second engine in the transaction. And on a replica, that is every transaction.
The fix
Small change, in the engine’s transaction code. prepare now remembers which prepared transaction belongs to the connection, and commit finishes that one instead of an empty state. External XA is untouched. It went out as v0.2.3.
With that in place the reproducer keeps three rows in both tables, and the full run comes back clean, crash test included:
[8] data integrity: all column types, NULL / unicode / negatives ....... PASS [9] DDL replication (ALTER ADD COLUMN / ADD INDEX / DROP) .............. PASS [10] transactions (atomic commit, rollback, engine commit/rollback) ..... PASS [11] bulk LOAD DATA on master -> replica ................................ PASS [12] durability: graceful restart, then SIGKILL crash recovery .......... PASS VERDICT: PASS=24 FAIL=0
The crash case is the important one. After a SIGKILL in the middle of applying, the replica came back with every committed row exactly once, matching the primary. Committed transactions survive the kill, and the position stays in step with them.
We left two tests behind so this cannot slip back in quietly: an MTR test, txn_mixed_engine, that runs a mixed DuckDB+InnoDB transaction on every build, and scripts/repro-2pc-dataloss.sh, which you can point at any published image to check it.
What works, and what doesn’t yet
Where it stands on v0.2.3, for an InnoDB primary feeding a DuckDB replica:
| Scenario | Result |
|---|---|
| INSERT / UPDATE / DELETE | works, content matches |
| All column types (numeric, temporal, string, BLOB, NULL, unicode) | works |
| ALTER ADD COLUMN / ADD INDEX, DROP TABLE | works |
| Transaction commit / rollback | works, atomic |
| Bulk LOAD DATA | works |
| Graceful restart, resume from GTID | works |
| SIGKILL crash, no loss / no duplicates | works |
The things to keep in mind:
- Create the replica tables as ENGINE=DuckDB yourself. A replicated CREATE TABLE keeps the primary’s engine, so it will not turn into DuckDB on its own.
- Replica tables need a primary key for UPDATE and DELETE.
- The applier goes row by row. That is fine for a normal OLTP change stream. It is not fine for keeping up with a primary that bulk-loads at full speed – the replica will fall behind.
- Committed transactions are crash-safe, with one small gap. The engine holds a prepared-but-not-committed transaction in memory only, so a crash in the short window between prepare and commit can lose that single transaction. The applier commits right away, so the window is small, but it is not zero.
- Blobs behave differently over replication than through a direct statement. A plain UPDATE of a BLOB or TEXT column has a known limit in the engine and does not apply. Over replication it does apply, because the row event carries a full before-and-after image instead of the shared buffer the direct path uses.
And the obvious one. This is an experiment. It is a functional result from a test harness on small data, not an HA or failover benchmark. We did not test multi-source replication, filters, or a real write rate.
Where it stands
An InnoDB primary feeding a DuckDB replica works on v0.2.3. Inserts, updates, deletes, every common type, schema changes, transactions, bulk load – they all replicate and match, and it comes back clean from both a graceful restart and a hard kill. The one real bug, silent data loss on every replicated transaction, is found, understood, fixed, and covered by tests.
It is not production-ready, and we do not treat it as such. But the idea holds up. Point normal MySQL replication at a DuckDB replica, and you get an analytics copy that keeps itself in sync.
The post Replicating from InnoDB into a DuckDB storage engine appeared first on Percona.
The replication manager script can be particularly useful in complex PXC/Galera topologies that require Async/Multi-source replication. This will ease the auto source and replica failover to ensure all replication channels are healthy and in sync. If certain nodes shouldn’t be part of a async/multi-source replication, we can disable the replication manager script there to tightly controlled the flow. Alternatively, node participation can be controlled by adjusting the weights in the percona.weight table, allowing replication behavior to be managed more precisely.
A unique constraint specifies, one or more columns as unique it identifies. It is satisfied only when no two rows store the same non-null values at its core. A primary key constraint is a unique one that will say PRIMARY KEY in its defined way. It is satisfied only when rows unfold, and none may […]
I’m lazy when I’m speakin’ I’m lazy when I walk I’m lazy when I’m dancin’ I’m lazy when I talk X-Press 2 Feat. David Byrne – Lazy While preparing a blog post to compare how PostgreSQL and MySQL handle locks, as part of a series covering the different approaches to MVCC for these databases, […]
In a recent post, The Quirks of Index Maintenance in Open Source Databases, I compared the IO load generated by open source databases while inserting rows in a table with many secondary indexes. Because of its change buffer, InnoDB was the most efficient solution. However, that’s not the end of the story. Evolution of the […]
When you run a simple write, …it may look simple, but under the hood, MySQL’s InnoDB engine kicks off a pretty complex sequence to ensure your data stays safe, consistent, and crash-recoverable. In the top-left corner of the diagram, we see exactly where this begins — the moment the query is executed: [crayon-68cb7b0282a22221090106/] The log […]
In this blog, we continue from where we left off in the previous post, InnoDB Cluster Setup: Building a 3-Node High Availability Architecture, where we demonstrated how to set up a MySQL InnoDB Cluster with three nodes to achieve high availability. Here, we walk through the step-by-step process of performing a rolling upgrade of that […]
Setting up an InnoDB Cluster requires three key components: Group Replication, MySQL Shell, and MySQL Router. In the previous post, we covered the process of building a 3-node InnoDB Cluster. In this post, we shift our focus to configuring MySQL Router and validating failover functionality. Environment overview We are using three InnoDB Cluster nodes along […]