Three DBInterface behaviors that differ from SQLite.jl and Postgres.jl (transaction return value is a bug)

[quinnj]

Working on AbstractStores.jl — a key-value store interface with one SQL backend implemented against DBInterface and tested identically on SQLite, Postgres, and MySQL — I hit three places where MySQL.jl behaves differently from SQLite.jl and Postgres.jl. One looks like a plain bug (#3); the other two may be deliberate, but the divergence means portable DBInterface code needs MySQL-specific branches.

Repro

Self-contained, runs all three drivers against the same script (Postgres and MySQL in throwaway containers via Harbor.jl):

mysql_repro.jl

# Minimal repros for MySQL.jl behaviors that differ from SQLite.jl and Postgres.jl.
# Run against an environment with MySQL, SQLite, Postgres, DBInterface, Harbor.

using MySQL, SQLite, Postgres, DBInterface, Sockets, Harbor

function pick_port()
    s = Sockets.listen(Sockets.IPv4(0), 0)
    _, p = Sockets.getsockname(s)
    close(s)
    return Int(p)
end

report(f, label) = try
    f()
    println("  PASS  $label")
catch e
    println("  FAIL  $label\n          ", first(split(sprint(showerror, e), "\n")))
end

# `?` for SQLite/MySQL, `$n` for Postgres
function run_all(conn, label; numbered::Bool=false)
    ph(i) = numbered ? "\$$i" : "?"
    println("\n===== $label =====")
    DBInterface.execute(conn, "DROP TABLE IF EXISTS t")
    DBInterface.execute(conn, "CREATE TABLE t (k VARCHAR(64) PRIMARY KEY, v TEXT, n BIGINT)")

    report("1. DBInterface.execute(conn, sql, params)") do
        DBInterface.execute(conn,
            "INSERT INTO t (k, v, n) VALUES ($(ph(1)), $(ph(2)), $(ph(3)))", ("a", "x", 1))
    end

    stmt = DBInterface.prepare(conn,
        "INSERT INTO t (k, v, n) VALUES ($(ph(1)), $(ph(2)), $(ph(3)))")
    DBInterface.execute(stmt, ("a2", "x", 1))   # ensure a row exists regardless of #1

    report("2. bind `nothing` as SQL NULL") do
        DBInterface.execute(stmt, ("b", "x", nothing))
    end
    report("2b. bind `missing` as SQL NULL") do
        DBInterface.execute(stmt, ("c", "x", missing))
    end

    report("3. DBInterface.transaction returns the closure's value") do
        got = DBInterface.transaction(() -> 42, conn)
        got == 42 || error("returned $(repr(got))::$(typeof(got)), expected 42")
    end

    sel = DBInterface.prepare(conn, "SELECT k FROM t WHERE k = $(ph(1))")

    report("4. prepared stmt usable after a failed (duplicate-key) prepared insert") do
        try
            DBInterface.execute(stmt, ("a2", "dup", 1))    # duplicate PK -> expected error
        catch
        end
        collect(DBInterface.execute(sel, ("a2",)))
    end

    report("5. prepared stmt usable after abandoning a MULTI-row cursor") do
        all = DBInterface.prepare(conn, "SELECT k FROM t")
        iterate(DBInterface.execute(all))                 # read row 1 of N, walk away
        collect(DBInterface.execute(sel, ("a2",)))
    end
end

run_all(SQLite.DB(), "SQLite.jl (reference)")

let port = pick_port()
    Harbor.with_container("postgres"; tag="16", ports=Dict(5432 => port),
            environment=Dict("POSTGRES_USER" => "postgres", "POSTGRES_PASSWORD" => "postgres",
                             "POSTGRES_DB" => "t"),
            wait_strategy=(port=5432,), wait_timeout=120.0) do _
        conn = nothing
        for _ in 1:120
            try
                conn = DBInterface.connect(Postgres.Connection, "127.0.0.1", "postgres",
                                           "postgres"; dbname="t", port=port, connect_timeout=2)
                break
            catch
                sleep(1)
            end
        end
        run_all(conn, "Postgres.jl (reference)"; numbered=true)
    end
end

let port = pick_port()
    Harbor.with_container("mysql"; tag="8", ports=Dict(3306 => port),
            environment=Dict("MYSQL_ALLOW_EMPTY_PASSWORD" => "yes", "MYSQL_DATABASE" => "t"),
            wait_strategy=(port=3306,), wait_timeout=180.0) do _
        conn = nothing
        for _ in 1:120
            try
                conn = DBInterface.connect(MySQL.Connection, "127.0.0.1", "root", "";
                                           db="t", port=port)
                break
            catch
                sleep(1)
            end
        end
        run_all(conn, "MySQL.jl")
    end
end

Results

===== SQLite.jl (reference) =====
  PASS  1. DBInterface.execute(conn, sql, params)
  PASS  2. bind `nothing` as SQL NULL
  PASS  2b. bind `missing` as SQL NULL
  PASS  3. DBInterface.transaction returns the closure's value
  PASS  4. prepared stmt usable after a failed (duplicate-key) prepared insert
  PASS  5. prepared stmt usable after abandoning a MULTI-row cursor

===== Postgres.jl (reference) =====
  PASS  1. DBInterface.execute(conn, sql, params)
  PASS  2. bind `nothing` as SQL NULL
  PASS  2b. bind `missing` as SQL NULL
  PASS  3. DBInterface.transaction returns the closure's value
  PASS  4. prepared stmt usable after a failed (duplicate-key) prepared insert
  PASS  5. prepared stmt usable after abandoning a MULTI-row cursor

===== MySQL.jl =====
  FAIL  1. DBInterface.execute(conn, sql, params)
          `DBInterface.execute(conn, sql)` does not support parameter binding; see `?DBInterface.prepare(conn, sql)`
  FAIL  2. bind `nothing` as SQL NULL
          MethodError: no method matching bind!(::MySQL.API.BindHelper, ::Vector{MySQL.API.MYSQL_BIND}, ::Int64, ::Nothing)
  PASS  2b. bind `missing` as SQL NULL
  FAIL  3. DBInterface.transaction returns the closure's value
          returned false::Bool, expected 42
  PASS  4. prepared stmt usable after a failed (duplicate-key) prepared insert
  PASS  5. prepared stmt usable after abandoning a MULTI-row cursor

---

3. DBInterface.transaction discards the closure's return value — this one is a bug

src/load.jl:114:

function DBInterface.transaction(f::Function, conn::Connection)
    DBInterface.execute(conn, "START TRANSACTION")
    try
        f()                       # <-- result dropped
        API.commit(conn.mysql)    # <-- this is what gets returned
    catch
        API.rollback(conn.mysql)
        rethrow()
    end
end

DBInterface's own default returns f()'s value, and SQLite.jl and Postgres.jl both do too:

# DBInterface/src/DBInterface.jl
function transaction(f, conn::Connection)
    execute(conn, "BEGIN TRANSACTION;")
    try
        ret = f()
        execute(conn, "COMMIT;")
        return ret
    ...

julia> DBInterface.transaction(() -> 42, conn)
false          # MySQL.jl — this is `API.commit`'s return value
42             # SQLite.jl, Postgres.jl

This is easy to get bitten by, because the value is silently plausible rather than an error. In my case a compare-and-swap did won = DBInterface.transaction(conn) do ... end, and every CAS silently "lost", so the retry loop ran to exhaustion and threw. It looked like lock contention, not a dropped return value.

Suggested fix:

function DBInterface.transaction(f::Function, conn::Connection)
    DBInterface.execute(conn, "START TRANSACTION")
    try
        ret = f()
        API.commit(conn.mysql)
        return ret
    catch
        API.rollback(conn.mysql)
        rethrow()
    end
end

---

1. DBInterface.execute(conn, sql, params) is unsupported

DBInterface.execute(conn, "INSERT INTO t (k, v) VALUES (?, ?)", ("a", "x"))
# ERROR: `DBInterface.execute(conn, sql)` does not support parameter binding;
#        see `?DBInterface.prepare(conn, sql)`

Works on SQLite.jl and Postgres.jl. DBInterface documents the 3-arg form as part of the interface, so generic code reasonably reaches for it, and the error only shows up at runtime against MySQL.

I understand the rationale (MySQL's C API has no unprepared parameter binding), and switching to prepare + execute is the better path anyway. But it would help if either (a) MySQL.jl implemented the 3-arg form by preparing internally and caching, or (b) this were called out in the README as a known MySQL-only restriction.

2. nothing is not bindable as SQL NULL

stmt = DBInterface.prepare(conn, "INSERT INTO t (k, n) VALUES (?, ?)")
DBInterface.execute(stmt, ("b", nothing))
# ERROR: MethodError: no method matching bind!(::MySQL.API.BindHelper, ::Vector{MySQL.API.MYSQL_BIND}, ::Int64, ::Nothing)

DBInterface.execute(stmt, ("c", missing))   # works

SQLite.jl has bind!(::Stmt, ::Integer, ::Missing) and bind!(::Stmt, ::Integer, ::Nothing) (src/SQLite.jl:359,363); Postgres.jl accepts both too.

missing is the more Julian spelling of NULL and I've switched to it, so this is minor. But nothing is what you get from an unset Union{Nothing,T} field, which is extremely common in "optional column" code, and the failure is a MethodError deep in the bind path rather than a clear message. A one-line bind!(helper, binds, i, ::Nothing) = bind!(helper, binds, i, missing) would close the gap.

---

Also checked, no problem found

Cases 4 and 5 are in the repro because I originally suspected them (I saw (2014): Commands out of sync while developing) and wanted to record that they are not MySQL.jl's fault. A failed duplicate-key prepared insert, and abandoning a partially-read multi-row cursor, both leave the connection perfectly usable. My out-of-sync errors turned out to be my own code not draining result cursors before issuing the next statement — worth noting that MySQL is stricter here than SQLite/Postgres, which tolerate it, but MySQL.jl's clear! handles it correctly once you do.

Versions

MySQL.jl 1.5.1, SQLite.jl 1.7.1, Postgres.jl 1.0.0, DBInterface 2.6.1, Julia 1.12.6, mysql:8 / postgres:16 containers.