We Accidentally Ran One SQL Script Across Multiple Database Connections
The import was supposed to create 144 child rows, eight for each of 18 parents. It created 83.
Two parents had no children. One parent's first child appeared five times. Two others got 14 and 10 rows instead of eight. Auto-increment IDs from unrelated parents were interleaved. The broken child IDs also fed a schedule table that was supposed to receive 1,008 rows.
There was no useful error. Every SQL statement looked successful in the UI.
This happened in Tabularis v0.10.2 against MySQL. The execution path was open source, which made the failure straightforward to reconstruct once we stopped looking for a mistake in the SQL.
The same file produced the correct data outside the application's multi-statement path. The SQL was not the problem. The transaction was not the problem either, at least not in the way we first suspected.
The application had taken one SQL script and executed it as a collection of unrelated, concurrent database sessions.
The script depended on a session
The original report involved a longer MySQL import, but its essential dependency fits in four statements:
INSERT INTO parent (name) VALUES ('A');
SET @parent_id = LAST_INSERT_ID();
INSERT INTO child (parent_id, name) VALUES (@parent_id, 'A-1');
INSERT INTO child (parent_id, name) VALUES (@parent_id, 'A-2');
This is valid MySQL. LAST_INSERT_ID() returns the auto-increment value generated by the most recent insert on the current connection. User-defined variables such as @parent_id are also scoped to the current session. The MySQL documentation is explicit about both properties: user variables are session-specific, and the server maintains LAST_INSERT_ID() on a per-connection basis.
The word "connection" is doing all the work there.
In the editor, the user had selected one saved connection and clicked Run All. At the UI level, it looked like one destination. At the driver level, that saved connection referred to a pool containing up to ten physical MySQL connections. Those are ten independent server sessions.
There is nothing wrong with that. A pool is supposed to let concurrent work borrow different connections. The bug was treating a sequence that required one session as concurrent work.
Promise.allSettled was the transaction coordinator
The old execution path split the script into statements and mapped each one to a separate Tauri command:
await Promise.allSettled(
statements.map((statement) =>
invoke("execute_query", {
connectionId,
query: statement,
}),
),
);
That code was originally concerned with result rendering. Each query got its own result tab, execution time and error state. Promise.allSettled was convenient because one failed statement did not prevent the UI from collecting the others.
It also meant every statement started independently.
On the Rust side, every execute_query call did what a normal single-query path should do: resolve the pool and acquire a connection from it.
let pool = get_mysql_pool(params).await?;
let mut conn = pool.acquire().await?;
execute_on(&mut conn, query).await
SQLx returns the connection to the pool when the checkout is dropped. Another statement may later get the same physical connection, but that is an implementation detail, not a guarantee. With several calls in flight concurrently, multiple physical connections are exactly what the pool is there to provide.
The four-statement script could therefore become this:
INSERT INTO parent ... connection 17
SET @parent_id = ... connection 24
INSERT INTO child ... connection 31
INSERT INTO child ... connection 42
Connection 17 owned the new auto-increment value. Connection 24 could not see it. Connections 31 and 42 could not see the variable set on connection 24.
An uninitialized MySQL user variable evaluates to NULL. Because parent_id in the affected import accepted it, the child insert was still valid SQL. Other pooled connections could carry stale session state from earlier work. Concurrency also removed statement ordering, so a dependent statement could reach the server before the statement it depended on.
The database was doing exactly what each client asked. We had accidentally created several clients.
Why the failure looked healthy
Most database failures are kind enough to produce an error code. This one often produced plausible data.
The statements were individually valid. A child row with a nullable or incorrect foreign key can still be inserted. Promise.allSettled waited for every invocation and let the interface render successes alongside failures. Worse, the MySQL, PostgreSQL and SQLite execution paths were returning affected_rows: 0 for every non-result statement, regardless of what the server reported.
So a successful insert and an insert that did nothing had the same summary: zero rows affected.
That hardcoded value did not cause the session bug, but it removed one of the few signals that might have exposed it. It was fixed in the same change.
This is a particularly unpleasant class of corruption because the output retains structure. Eight missing rows are easy to notice. Sixty-one missing rows spread across several parents, mixed with duplicates and valid inserts, look like an error in the source data or in the import logic. By the time somebody counts the records, the script is no longer running and the pool has long since returned every connection.
Serial execution is necessary, but not sufficient
The obvious first fix is to replace Promise.allSettled with a loop:
for (const statement of statements) {
await invoke("execute_query", { connectionId, query: statement });
}
That restores ordering. It does not restore session identity.
Each invocation still calls pool.acquire(). The pool is free to return connection 17 for the first statement and connection 24 for the second. Under light load it may happen to return the same idle connection repeatedly, which is worse than a deterministic failure because the broken implementation appears correct in development.
There are two separate invariants:
- Statement B must start after statement A finishes.
- Statement B must execute in the same database session as statement A.
Awaiting each query gives you the first. Holding one connection for the lifetime of the script gives you both.
This distinction matters far beyond SQL editors. Any abstraction that accepts a pool and exposes a sequence of calls can get it wrong: migration runners, background jobs, import tools, home-grown transaction helpers and repository layers. If the API does not make connection affinity visible, a loop can look safe while still changing sessions between iterations.
The fix was one checkout, then a loop
We replaced the per-statement command fan-out with one execute_query_batch call. The built-in drivers acquire one physical connection before the first statement and keep it until the last result has been collected.
The core of the fix is deliberately boring:
let mut conn = pool.acquire().await?;
let mut results = Vec::with_capacity(statements.len());
for statement in statements {
let result = execute_on(&mut conn, statement).await;
results.push(result);
}
Ok(results)
The important part is not the loop. It is where acquire() sits.
Previously it was inside execute_query, so it ran once per statement. In the batch path it runs before the loop, so all statements share the same checked-out connection. The UI still receives one result per statement and can show partial failures, but execution order and session state now agree with what Run All means to a person reading the script.
MySQL, PostgreSQL and SQLite each implement the batch operation against their native connection type. External plugin drivers get a sequential default implementation for compatibility, but must override it if they promise connection-local continuity. That limitation is documented in the driver trait rather than hidden behind a method named execute_batch.
The full change is in commit 8eed14b2. The original report, including the damaged row counts and minimal reproduction, is issue #199.
Fixing the connection exposed a protocol problem
Keeping one connection alive made transactions structurally possible, but MySQL added another constraint.
SQLx normally sends sqlx::query() through MySQL's prepared-statement protocol, using COM_STMT_PREPARE and COM_STMT_EXECUTE. In our path, preparing transaction control still surfaced MySQL error 1295 on commands outside the protocol's supported set. MySQL documents COMMIT as preparable, but does not list the whole family, including BEGIN, START TRANSACTION and savepoint operations.
The driver now routes the complete transaction-control family through the text protocol with sqlx::raw_sql(), which uses COM_QUERY. Regular statements continue through the prepared path. Sharing a connection was the semantic fix; selecting the correct wire protocol was required to make explicit MySQL transactions actually use it.
This was not visible while every statement had its own connection. A transaction could not work correctly in that model anyway. Removing one broken assumption exposed the next one.
Testing session identity instead of query success
The regression tests do not merely assert that each statement returns Ok. That was already happening when the data was wrong.
The MySQL test creates a parent, stores LAST_INSERT_ID() in @pid, inserts two children and then verifies that both children reference the parent that was just created. A second test wraps two inserts in BEGIN and COMMIT and checks the committed rows.
The PostgreSQL test uses a temporary table:
BEGIN;
CREATE TEMP TABLE batch_tmp (id serial primary key, value text);
INSERT INTO batch_tmp (value) VALUES ('a'), ('b'), ('c');
SELECT count(*) FROM batch_tmp;
COMMIT;
PostgreSQL requires each session to create its own temporary table. A later statement on another connection cannot see it. That makes the table a useful assertion about session identity, not just query ordering. PostgreSQL also exposes pg_backend_pid(), while MySQL exposes CONNECTION_ID(), if you want a minimal diagnostic that prints the server session used by each statement.
These are integration tests against live MySQL and PostgreSQL instances, so they are ignored in the ordinary unit test run. That is worth stating because a test existing in a repository is not evidence that somebody ran it against a server. The issue reproducer and the fixed execution path are the primary evidence; the tests preserve the invariant for future driver changes.
Finding Tabularis useful? Star it on GitHub — it takes a second and helps more developers discover the project. Star on GitHubA script is a conversation
Connection pools encourage a useful mental model: take a connection, perform one independent unit of work, return it quickly. Problems start when "independent" is inferred from an API boundary instead of from database semantics.
These operations all depend on session identity:
BEGIN,COMMIT, savepoints and transaction-scoped locks- MySQL user variables and
LAST_INSERT_ID() - PostgreSQL
currval()and prepared statements - temporary tables
- session settings such as
search_path,sql_modeorFOREIGN_KEY_CHECKS - advisory locks and other connection-owned resources
A pool may choose any suitable connection for the next operation. It cannot infer that two strings originated from the same editor buffer, migration file or job. If continuity matters, the application has to express it by holding a connection or transaction handle across the whole unit of work.
That is the general lesson from this bug:
A SQL script is not a Vec<String>. It is a conversation with one database session.
We had preserved the strings and discarded the conversation. The database accepted every sentence. It was listening on four different calls.