Chronos/DBWaitlist

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.

shell
export CHRONOS_SOURCE_URL='mysql://chronos:example-password@db.internal:3306/shop?ssl-mode=VERIFY_IDENTITY'chronos attach shop --tables customers,orderschronos serve shop

This 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, BIT or spatial columns in an attached table (see types).
  • Tables of the database the URL names. A name written otherdb.t is 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, and sync_binlog = 1 when 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): .... Set CHRONOS_ATTACH_UNSAFE_SOURCE=1 to 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.

PartValues
schememysql:// or mariadb://
ssl-modeDISABLED, PREFERRED (the default), REQUIRED, VERIFY_CA, VERIFY_IDENTITY
ssl-cassl-ca=<file.pem>: the CA certificates to check the server’s against
connect-timeouthow long connecting may take
  • Logins: caching_sha2_password (MySQL 8’s default) and mysql_native_password.
  • A login that needs the password itself (the first one after a server restart with caching_sha2_password, or sha256_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:

TableHoldsThe user needs
chronos_mergesa row per merge written, plus the folder’s marker rowSELECT, INSERT
chronos_merge_rowseach row a merge wrote, before and after, kept to undo the merge by its idSELECT, 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:

an administrator runs, once
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_merges and 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_rows is missing and the user can’t make it, merges go on without undo records. The HTTP API’s attached then says undo.ready: false.
  • If the user may not delete from chronos_merge_rows, records past the window stay.

chronos attach

usage
chronos attach <new folder> --tables a,b [--yes]
FlagDoes
<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,bthe 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)
--yesgoes 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
--schemarefused: --schema is for Postgres: name MySQL’s tables with --tables a,b
--lazy-tenantrefused: --lazy-tenant works with a Postgres source only for now

What attach does, in order:

  1. Checks the server and Chronos’s table

    It reads the server’s version, then uses chronos_merges if it is there, or makes it (engine = InnoDB).

  2. 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 from information_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.

  3. 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.

  4. 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 columnHeld in the folder as
tinyintsmallint
smallintsmallint (smallint unsigned: integer)
mediumintinteger
int, integerinteger (int unsigned: bigint)
bigintbigint (bigint unsigned: numeric(20, 0))
yearinteger
decimal, numeric, dec, fixednumeric with the same precision and scale (numeric(10, 0) when none is given)
float, double, realdouble precision
char(n), varchar(n)varchar(n)
tinytext, text, mediumtext, longtexttext
enum, set, timetext
datedate
datetime, timestamptimestamp, read and written in UTC
jsonjsonb
binary, varbinary, tinyblob, blob, mediumblob, longblob, bitrefused: 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 writes DEFAULT there. 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 (...) STORED or VIRTUAL) 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.

SQL, in a world
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_INCREMENT column is narrower than INT, or is BIGINT 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’s attached gives inserts: false for them.
  • A folder attached before 0.5.0 takes no inserts into AUTO_INCREMENT tables: 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).

  1. Lock and compare

    START TRANSACTION, the folder’s marker row, and every row the merge writes read FOR UPDATE by key, at most 1,000 keys a statement. Each row is compared with main’s copy by type, so 1.5 and 1.50, or two spellings of the same JSON, are the same value. Text compares exactly, whatever the column’s collation.

  2. 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 in chronos_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.

  3. 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, and STRICT_ALL_TABLES added to sql_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_rows in 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 errorSQLSTATEKind
1062, 158623505duplicate key
1216, 1217, 1451, 145223503foreign key
1048, 136423502NULL where none may be
381923514a CHECK constraint
140622001data too long
1264, 169022003out of range
1292, 1366, 141122P02a value of the wrong form
1044, 1045, 1142, 114342501access denied
1205, 1213, 1317, 3024, 1927, or a lost connection40001a lock wait timeout, a deadlock, an interrupted or timed-out query: the message ends nothing was written: safe to merge again
any other23000

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.
  • attached says check_reads: true for 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

shell
chronos refresh shop                       # once, on a folder no server has openchronos serve shop --refresh-every 5m      # on a timer, while it serves

A 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-every takes a time such as 60s, 5m or 1h.
  • A folder chronos serve has open refreshes with POST /v1/refresh and the admin token, or serve --refresh-every.
  • serve --follow is 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.

SQL
UNDO MERGE ID 'merge:...' [, 'merge:...'] [SKIP CHANGED] [DRY RUN]
  • The id. A merge over HTTP answers it: merge, beside merged. HTTP undo_merge takes ids, with skip_changed and dry_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 with skip_changed while 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 yetInstead
serve --followchronos 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 sourcemerge checks still run on main, Chronos’s copy
writeback.jsonevery column is compared and written
pg_role (writing as the end user, act-as)every write runs as the user the URL names
--lazy-tenanttables are copied whole
--schemaname the tables with --tables
Binary, BIT and spatial columnsa table that has one is refused
Columns added or changed in MySQL, by a refreshattach again into a new folder
Inserts into tables whose AUTO_INCREMENT column is narrower than INT or is BIGINT UNSIGNEDupdates and deletes work
Counting cascades for an undocheck 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:

MySQLMariaDB
Oldest version8.010.5
URL schememysql://mariadb:// (or mysql://)
kind in attachedmysqlmariadb, for a folder attached from 0.5.0 on. One attached earlier says mysql, and works the same
Generated columnsVIRTUAL and STOREDVIRTUAL, STORED and PERSISTENT
Column defaultsread from information_schema as a literal’s bare text; an expression default is marked DEFAULT_GENERATEDread as SQL text; a default that isn’t a quoted literal, a number or CURRENT_TIMESTAMP is left to MariaDB at the merge

Next

Beside MySQL and MariaDB · Chronos