Chronos DB, self-hosted
Beside MySQL and MariaDB
The same promise beside MySQL 8.0 or MariaDB 10.5 and later: what comes over, how a merge is written, and what is not there yet.
Self-hostedChronos DB is one binary you run yourself. Releases and install script. To put agents on a database you already run, with approvals: Chronos Gateway
How it works
chronos attach also works beside a MySQL 8.0 or later, or MariaDB 10.5 or later, database. The promise is the same as beside Postgres: main is a copy of the tables you name, agents work in worlds, and a merge into main is written to MySQL first, in one InnoDB transaction. Each row it writes must still be as main has it. If MySQL changed one meanwhile, the merge comes back as a conflict and nothing is written. chronos refresh brings main up to MySQL.
export CHRONOS_SOURCE_URL='mysql://chronos:example-password@db.internal:3306/shop?ssl-mode=VERIFY_IDENTITY'chronos attach shop --tables customers,orderschronos serve shopThis page describes Chronos DB 0.6.2 (2026-11-19). Attach beside MySQL and MariaDB came in 0.5.0. Several things that work beside Postgres are not there yet, and each is refused with a message that says so. To put runs, rules and approvals on top of an attached folder, use Chronos Gateway.
Requirements
- MySQL 8.0 or later, or MariaDB 10.5 or later. An older server is refused:
MySQL <version> is too old: attach needs MySQL 8.0 or later, or MariaDB 10.5 or later (nothing was copied). - InnoDB. A table of another engine is refused:
its engine is MyISAM, which has no transactions: only InnoDB tables are attached. - A primary key on each table. Without one:
no primary key, so a row can’t be told from another: ALTER TABLE <name> ADD PRIMARY KEY (...), then attach. - Tables, not views. A view is refused:
a view: attach the tables it reads. - No binary,
BITor spatial columns in an attached table (see types). - Tables of the database the URL names. A name written
otherdb.tis refused:only tables of the database the URL names (shop) are attached. - Commits MySQL keeps (from 0.6.2):
innodb_flush_log_at_trx_commit = 1, andsync_binlog = 1when the binary log is on (both are MySQL’s defaults). Each merge into main reads them first. Otherwise MySQL may lose a commit it acknowledged after a crash of its own while main keeps the merge, so the merge is refused, nothing written, naming the setting:... may lose commits it acknowledged (innodb_flush_log_at_trx_commit = 2): .... SetCHRONOS_ATTACH_UNSAFE_SOURCE=1to merge anyway.
The connection URL
The URL comes from CHRONOS_SOURCE_URL, or from a file only you can read (CHRONOS_SOURCE_FILE, or chronos serve --source-file f). It is never a command argument and is written nowhere in the folder. chronos serve and chronos refresh on the folder need it too.
| Part | Values |
|---|---|
| scheme | mysql:// or mariadb:// |
ssl-mode | DISABLED, PREFERRED (the default), REQUIRED, VERIFY_CA, VERIFY_IDENTITY |
ssl-ca | ssl-ca=<file.pem>: the CA certificates to check the server’s against |
connect-timeout | how long connecting may take |
- Logins:
caching_sha2_password(MySQL 8’s default) andmysql_native_password. - A login that needs the password itself (the first one after a server restart with
caching_sha2_password, orsha256_password) is only sent over TLS.
The user and its grants
Attach, refresh and every merge connect as the user the URL names. It must be allowed to read the attached tables and to write them, since a merge runs its inserts, updates and deletes as that user. Besides those, Chronos keeps two tables of its own in the attached database:
| Table | Holds | The user needs |
|---|---|---|
chronos_merges | a row per merge written, plus the folder’s marker row | SELECT, INSERT |
chronos_merge_rows | each row a merge wrote, before and after, kept to undo the merge by its id | SELECT, INSERT, DELETE (DELETE prunes records past the undo window) |
A user who may create tables needs nothing more: attach makes chronos_merges, and the first merge makes chronos_merge_rows. For a least-privilege user, an administrator makes both tables ahead, and the user needs no right to create tables. These are the statements attach prints, for a database named shop:
create table `shop`.chronos_merges (id varchar(255) primary key, at datetime(6) not null default current_timestamp(6));grant select, insert on `shop`.chronos_merges to <the user the URL names>;create table `shop`.chronos_merge_rows (merge_id varchar(255) not null, n int not null, table_name varchar(255) not null, op varchar(10) not null, row_key json, before_row json, after_row json, at datetime(6) not null default current_timestamp(6), primary key (merge_id, n), key chronos_merge_rows_at (at));grant select, insert, delete on `shop`.chronos_merge_rows to <the user the URL names>;- If the user can’t make
chronos_mergesand it isn’t there, attach stops with<user> can’t make the table chronos_merges in MySQL (...)and prints the statements above. - If the user can’t insert the marker row, attach stops with
recording the attach in chronos_merges: ...and prints the same statements. The folder is removed again. - If
chronos_merge_rowsis missing and the user can’t make it, merges go on without undo records. The HTTP API’sattachedthen saysundo.ready: false. - If the user may not delete from
chronos_merge_rows, records past the window stay.
chronos attach
chronos attach <new folder> --tables a,b [--yes]| Flag | Does |
|---|---|
<new folder> | the folder attach makes. One that exists is refused: shop exists: attach makes a new folder (to attach again, give another) |
--tables a,b | the tables to attach, of the database the URL names. Required: name the tables to attach: --tables a,b (tables of the database the URL names) |
--yes | goes on above about 1,000,000 rows. Without it: about N rows in all (MySQL’s estimate): copying holds one read open on MySQL for that long. Run again with --yes to go on |
--schema | refused: --schema is for Postgres: name MySQL’s tables with --tables a,b |
--lazy-tenant | refused: --lazy-tenant works with a Postgres source only for now |
What attach does, in order:
Checks the server and Chronos’s table
It reads the server’s version, then uses
chronos_mergesif it is there, or makes it (engine = InnoDB).Reads every table in one consistent snapshot
REPEATABLE READ,START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY. It reads each table’s engine, columns and primary key frominformation_schema, and prints each table with MySQL’s estimate of its rows. A table that isn’t there:no table x in MySQL’s shop.Refuses what it can’t hold
can’t attach, nothing was copied:followed by one line per table or column with the reason: a view, an engine other than InnoDB, no primary key, a column type not attached yet.Copies the rows and records the attach
Rows are copied into the new folder. The folder’s marker row is then inserted into
chronos_merges, and the folder’s own record is saved. On any failure the folder is removed again.
Attach ends by naming any table that takes no inserts (see AUTO_INCREMENT) and what is not there yet.
Types
| MySQL column | Held in the folder as |
|---|---|
tinyint | smallint |
smallint | smallint (smallint unsigned: integer) |
mediumint | integer |
int, integer | integer (int unsigned: bigint) |
bigint | bigint (bigint unsigned: numeric(20, 0)) |
year | integer |
decimal, numeric, dec, fixed | numeric with the same precision and scale (numeric(10, 0) when none is given) |
float, double, real | double precision |
char(n), varchar(n) | varchar(n) |
tinytext, text, mediumtext, longtext | text |
enum, set, time | text |
date | date |
datetime, timestamp | timestamp, read and written in UTC |
json | jsonb |
binary, varbinary, tinyblob, blob, mediumblob, longblob, bit | refused: a binary column (<type>): not attached from MySQL yet |
| anything else (the spatial types) | refused: a column of type <type>: not attached from MySQL yet |
One refused column refuses its table, and nothing is copied.
Defaults and generated columns
- A constant default, or
CURRENT_TIMESTAMP, comes over as the copy’s own default, so a row inserted in a world holds the value MySQL would give. - A default that is another expression (
DEFAULT (uuid())) is MySQL’s to work out. The column is NULL in the world, and the merge writesDEFAULTthere. A NULL you meant as NULL is written as the default too. - MySQL’s zero date (
0000-00-00) as a default is no date in the folder: the column is filled by MySQL at the merge, as an expression default is. - A generated column (
AS (...) STOREDorVIRTUAL) is held as a plain column that only MySQL gives values. A world reads it. A merge never writes it, and main takes MySQL’s value back with the row. - A merge that gives a generated column a value of its own is refused:
<world> gives <table>.<column> a value, and MySQL generates that column: leave it as it is (main takes MySQL’s value with the row at the merge); nothing was merged.
MySQL 8 marks a column whose default is an expression (DEFAULT CURRENT_TIMESTAMP, DEFAULT (expr)) as DEFAULT_GENERATED. That is not a generated column, and 0.5.0 attaches such tables.
AUTO_INCREMENT and provisional ids
MySQL gives an AUTO_INCREMENT column its value when the row is inserted, and no values can be reserved ahead. So a row inserted in a world gets a provisional id: a negative number, from -1,000,000,001 down, that no row of MySQL’s has. Leave the column out of the INSERT, as you would in MySQL.
insert into orders (customer) values ('ada'); -- id is -1000000001 in this worldinsert into order_lines (order_id, sku) values (-1000000001, 'x');At the merge the row is inserted without the id, MySQL gives its own (LAST_INSERT_ID()), and that id is written wherever the provisional one stood: the row itself, and every row of the merge that refers to it through a foreign key MySQL declares to the AUTO_INCREMENT column. Parents are inserted before their children. After the merge, main holds the rows under MySQL’s ids, and no provisional id is left anywhere.
- The id an agent saw in its world isn’t the row’s id after the merge. Read the row again from main by something else it holds (the order’s reference, the customer and time) to learn it.
- A provisional id in a column with no declared foreign key to the new row’s table is refused, and nothing is written: no number written there would be right. Declare the foreign key in MySQL and attach again, or merge the parent first and write its real id in a second world.
- A provisional id of a row this merge doesn’t insert is refused:
... a provisional id this folder gave a new row that this merge doesn’t insert (it was merged before, or never): read the row’s id from main and write that; nothing was merged. - An id given by hand (
insert into orders (id, ...) values (900, ...)) is refused. - New rows that refer to each other in a circle (or one to itself) are refused:
... so none can be inserted first: merge them in two steps; nothing was merged. - A table whose
AUTO_INCREMENTcolumn is narrower thanINT, or isBIGINT UNSIGNED, takes no inserts from worlds: no provisional id fits it. Updates and deletes work. Attach says which tables these are, and the HTTP API’sattachedgivesinserts: falsefor them. - A folder attached before 0.5.0 takes no inserts into
AUTO_INCREMENTtables: attach again into a new folder. - Each new row is its own
INSERT, so a merge of many new rows sends more SQL than one of updates. - If a trigger gives ids of its own or moves rows, the merge finds fewer rows under the ids MySQL gave than it inserted and is refused, with nothing merged.
The write-back
Chronos’s own checks run first, as for any merge: conflicts with main, constraints, merge policies, merge checks on main, stale reads. Then the write-back is three round trips to MySQL, whatever its size, up to 4 MB of SQL each (more goes in another trip of the same transaction).
Lock and compare
START TRANSACTION, the folder’s marker row, and every row the merge writes readFOR UPDATEby key, at most 1,000 keys a statement. Each row is compared with main’s copy by type, so1.5and1.50, or two spellings of the same JSON, are the same value. Text compares exactly, whatever the column’s collation.Write and read back
The updates, inserts and deletes, then the rows read back as MySQL stored them (MySQL has no
RETURNING), and the merge’s row inchronos_merges. Each statement must touch as many rows as the merge sent. If one doesn’t:MySQL updated 0 rows of orders where the merge sent 1: nothing was merged.Commit
COMMIT. Main then takes the rows as MySQL stored them, so values a trigger or default set come back with them.
- Session settings on every connection:
time_zone = '+00:00',READ COMMITTED,innodb_lock_wait_timeout = 10, andSTRICT_ALL_TABLESadded tosql_mode, so a value MySQL would cut or change is an error rather than a silent difference. - The marker row. If the database the URL names isn’t the one the folder was attached to:
the MySQL CHRONOS_SOURCE_URL names isn’t the one this folder was attached to (...): its chronos_merges has no row ...; nothing was merged. - Undo records go into
chronos_merge_rowsin the same transaction. Keeping them adds one round trip to a merge. - The kept connection. The folder keeps one connection to MySQL between uses. One that MySQL closed while unused is replaced at the merge’s first trip, as nothing was written yet.
Conflicts and errors
A row the merge writes that differs in MySQL from main’s copy has changed in MySQL since main last had them. Nothing is written, main takes MySQL’s current rows for the ones in conflict, and the merge returns the conflicts. Merge again and settle them as any conflict.
A statement MySQL refuses fails the merge with MySQL, <step>: <MySQL’s error>; nothing was written. The SQLSTATE the merge answers follows MySQL’s error:
| MySQL error | SQLSTATE | Kind |
|---|---|---|
| 1062, 1586 | 23505 | duplicate key |
| 1216, 1217, 1451, 1452 | 23503 | foreign key |
| 1048, 1364 | 23502 | NULL where none may be |
| 3819 | 23514 | a CHECK constraint |
| 1406 | 22001 | data too long |
| 1264, 1690 | 22003 | out of range |
| 1292, 1366, 1411 | 22P02 | a value of the wrong form |
| 1044, 1045, 1142, 1143 | 42501 | access denied |
| 1205, 1213, 1317, 3024, 1927, or a lost connection | 40001 | a lock wait timeout, a deadlock, an interrupted or timed-out query: the message ends nothing was written: safe to merge again |
| any other | 23000 |
A crash around commit
If the connection is lost during COMMIT, or Chronos stops there, the merge’s row in chronos_merges tells whether it committed. It is written in the write-back’s own transaction, so it is there exactly when the writes are. The outcome is settled at the next merge or at chronos serve: the merge is made in the folder, or dropped. From 0.6.1 the merge made here is exactly the one written there, at the world’s version and keys as the merge had them; a world written since keeps those writes, and main takes the source’s rows. 0.6.0 and earlier merged the whole world again, so after a partial merge or a write to the world after the crash, main could take rows the source never got: upgrade.
- If MySQL can be asked at once and the transaction didn’t commit:
MySQL, the connection was lost at COMMIT, which didn’t happen: ..., and the merge is safe to run again. - If it can’t be told yet:
the connection to MySQL was lost during COMMIT (...), and whether it committed can’t be told yet (...): merges into main wait until it can; <world> is as it was. Everything else keeps working.
Checking reads
A world that checks its reads (WITH (check_reads = true), or an agent whose merge policy asks for it) has what it read checked against MySQL inside the write-back, before anything is written, and by CHECK READS between steps, as with Postgres. A row it read that changed in MySQL since its fork, or a row added there that its read would have found, holds the merge (40001), and nothing is written. A world that read for one tenant is held by that tenant’s rows only.
- One difference from Postgres: a table read by a filtered scan (
where status = 'new') is read whole from MySQL to check it, for the world’s tenant when it has one. Postgres is asked only for the rows the filter can match. - Past 100,000 rows the read is too broad to check, and a person decides.
attachedsayscheck_reads: truefor MySQL and MariaDB, from 0.5.0.
Dry runs
MERGE WORLD x DRY RUN (and dry_run: true over HTTP, preview() in the clients) compares each row the merge would write with MySQL’s, in one snapshot with no locks, and checks the world’s reads, as with Postgres. It names the triggers on the tables it would write; they aren’t run. Nothing is written.
- The read is
REPEATABLE READ,START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY, and it checks the folder’s marker row first. - What it shows is a snapshot. The merge itself locks and compares again.
- Rehearsals (
MERGE WORLD x REHEARSE) are Postgres’s only.
Refresh
chronos refresh shop # once, on a folder no server has openchronos serve shop --refresh-every 5m # on a timer, while it servesA refresh reads each table whole in one consistent snapshot, compares it with main’s rows by type, and gives main MySQL’s inserts, updates and deletes in one change.
- A table whose columns changed in MySQL is left as it was, and the refresh says to attach again. Columns don’t come over by a refresh yet.
--refresh-everytakes a time such as60s,5mor1h.- A folder
chronos servehas open refreshes withPOST /v1/refreshand the admin token, orserve --refresh-every. serve --followis not there beside MySQL or MariaDB: main is as old as the last refresh for rows no merge touched.
Undo by merge id
Each write-back keeps the rows it wrote in chronos_merge_rows, in its transaction, and a merge can be undone by its id for 30 days (CHRONOS_UNDO_WINDOW_DAYS), as beside Postgres. The records outlive the folder.
UNDO MERGE ID 'merge:...' [, 'merge:...'] [SKIP CHANGED] [DRY RUN]- The id. A merge over HTTP answers it:
merge, besidemerged. HTTPundo_mergetakesids, withskip_changedanddry_run. - A row the merge added goes, under the id MySQL gave it. A row it deleted comes back with its old id.
- A row changed since refuses the undo with nothing written (409,
40001), or is left withskip_changedwhile the rest go back. - A row already put back is
already, from 0.5.2: neither undone nor skipped, and it never refuses the undo. So a merge undone in part can be undone again for the rows left, once they hold what the merge wrote, and undoing a merge twice does nothing. - The table is made at the first merge if the user may create tables; otherwise the statements attach prints make it.
- Pruning: after a merge, records past the window are deleted, 5,000 at a time.
What is not there yet
Each of these is refused with a message that says so:
| Not yet | Instead |
|---|---|
serve --follow | chronos refresh, or serve --refresh-every |
chronos refresh --check (the reconciliation scan) | DRY RUN of a merge compares its rows with MySQL |
Rehearsals (MERGE WORLD x REHEARSE) | DRY RUN, which reads and runs no statement |
| Merge checks run in the source | merge checks still run on main, Chronos’s copy |
writeback.json | every column is compared and written |
pg_role (writing as the end user, act-as) | every write runs as the user the URL names |
--lazy-tenant | tables are copied whole |
--schema | name the tables with --tables |
Binary, BIT and spatial columns | a table that has one is refused |
| Columns added or changed in MySQL, by a refresh | attach again into a new folder |
Inserts into tables whose AUTO_INCREMENT column is narrower than INT or is BIGINT UNSIGNED | updates and deletes work |
| Counting cascades for an undo | check ON DELETE CASCADE foreign keys yourself |
Asking rather than trying
A program can ask rather than learn from a refusal. The HTTP API’s attached (the database’s own user only) says kind (mysql or mariadb) and capabilities. For MySQL and MariaDB they are dry_run: true and check_reads: true, with follow, act_as, lazy_tenant and source_checks false. It also says undo: {ready, window_days} and, for each table, source_table (its name in MySQL’s SQL) and inserts.
MySQL and MariaDB
One code path serves both, and a folder behaves the same beside either. The differences:
| MySQL | MariaDB | |
|---|---|---|
| Oldest version | 8.0 | 10.5 |
| URL scheme | mysql:// | mariadb:// (or mysql://) |
kind in attached | mysql | mariadb, for a folder attached from 0.5.0 on. One attached earlier says mysql, and works the same |
| Generated columns | VIRTUAL and STORED | VIRTUAL, STORED and PERSISTENT |
| Column defaults | read from information_schema as a literal’s bare text; an expression default is marked DEFAULT_GENERATED | read as SQL text; a default that isn’t a quoted literal, a number or CURRENT_TIMESTAMP is left to MariaDB at the merge |