Chronos/DBWaitlist

Blog / AI agents

How to let an AI agent write to MySQL safely

The agent works in its own branch of a copy of your tables; each approved merge is one InnoDB transaction with every row compared first. Setup, the write-back, and what happens to AUTO_INCREMENT ids.

Abhishek DG · 5 min read
  • AI agents
  • MySQL
  • How-to

The same way as for Postgres: the agent works in its own branch of a copy of your tables, and each approved merge is written to MySQL in one InnoDB transaction, every row compared first. Chronos DB does this beside MySQL 8.0 or later and MariaDB 10.5 or later. This post covers the setup, how a merge is written, and the one thing that is different in MySQL: new rows in AUTO_INCREMENT tables.

How do I set it up beside MySQL?

terminal
export CHRONOS_SOURCE_URL='mysql://chronos:example-password@db.internal:3306/shop?ssl-mode=VERIFY_IDENTITY'chronos attach shop --tables customers,ordersexport CHRONOS_TOKEN=$(openssl rand -hex 24)         # the guest'sexport CHRONOS_ADMIN_TOKEN=$(openssl rand -hex 24)   # yourschronos serve shop

Attach copies the tables you name into a local folder. Your application keeps using MySQL as before. Agents connect to Chronos over the Postgres wire protocol or MCP, never to MySQL, and they write ordinary SQL in their own worlds. Start the server with both tokens, so a connection without an agent’s token is not the database’s own user.

  • InnoDB tables only. A table of another engine has no transactions and is refused.
  • A primary key on each table. Without one, a row can’t be told from another.
  • Tables, not views, and no binary, BIT or spatial columns in an attached table.
  • The user in the URL must be allowed to read and write the attached tables: every merge runs as that user.

How is a merge written to MySQL?

Chronos’s own checks run first: conflicts, constraints, your merge policy, merge checks, stale reads. Then the write-back is three round trips, whatever its size:

  1. 01Lock and compareEvery row the merge writes is read FOR UPDATE by key and compared with Chronos’s copy.
  2. 02Write and read backThe updates, inserts and deletes run. Each statement must touch exactly as many rows as the merge sent.
  3. 03CommitChronos takes the rows as MySQL stored them, so values a trigger or default set come back.
One InnoDB transaction. If any row differs from what the agent’s branch was based on, nothing is written and the merge comes back as a conflict.

Rows are compared by type, so 1.5 and 1.50, or two spellings of the same JSON, count as the same value.

What happens to new rows in AUTO_INCREMENT tables?

This is the part that differs from Postgres. MySQL gives an AUTO_INCREMENT column its value when the row is inserted, and values cannot be reserved ahead. So a row an agent inserts in its world gets a provisional id: a negative number that no row of MySQL’s has.

SQL, in the agent's 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, and that id is written wherever the provisional one stood: the row itself, and every row of the merge that points to it through a foreign key MySQL declares. Parents are inserted before their children.

What if MySQL changed while the agent worked?

Then nothing is written. A row the merge writes that differs in MySQL from Chronos’s copy is a conflict: Chronos takes MySQL’s current rows for those, and returns them for you to settle. An error from MySQL itself, such as a duplicate key or a foreign key, fails the merge with MySQL’s own message and writes nothing.

Can a merge be undone?

Yes, by its id, for 30 days. Each write-back keeps the rows it wrote, and the undo goes through MySQL the same checked way. One difference from Postgres: MySQL does not report what its cascades and triggers wrote beyond the undo’s own rows, so an undo is not held for them. Undoing a merge that inserted a row deletes that row, and any rows others have since hung on it through ON DELETE CASCADE go with it. Check those foreign keys yourself before undoing an insert.

What is not there yet beside MySQL?

Not yetUse instead
Following changes as they commit (serve --follow)chronos refresh, or serve --refresh-every
Rehearsing a merge and rolling it backa dry run, which reads and writes nothing
Copying one tenant’s rows on demand (--lazy-tenant)tables are copied whole
Writing as the end user’s own database roleevery write runs as the user in the URL

Each of these is refused with a message that says so. Rules, reviews and undo work as they do beside Postgres: see How to let an AI agent write to your Postgres safely for the policy and approval steps, and Beside MySQL and MariaDB for every detail.

Questions

Can an AI agent write to a MySQL database safely?

Yes, if it never connects to MySQL itself. With Chronos DB attached to MySQL 8.0 or later, the agent works in a branch of a copy of your tables, and each approved merge is written to MySQL in one InnoDB transaction after every row is locked and compared.

How do AUTO_INCREMENT ids work when an agent inserts rows in a branch?

The new row gets a provisional negative id in the branch. At the merge MySQL assigns the real id, and Chronos writes it into the row and into every merged row that refers to it through a declared foreign key.

Does this work with MariaDB?

Yes, with MariaDB 10.5 or later. One code path serves both, and a folder behaves the same beside either.

Written by

Abhishek DG

Co-founder & CEO, 2090 Labs

Abhishek builds Chronos in Bangalore: the database where AI agents work in their own copy of your data. Before it he built TailsMart and ACRED India.

More about Abhishek

Keep reading

How to let an AI agent write to MySQL safely · Chronos