MySQL connection recovery

MySQL connection recovery

Available since 0.1.4 (in development; not included in the public 0.1.3 release). Server-side only.

Enable automatic recovery

local db = dbConnect("mysql", "host=127.0.0.1;port=3306;dbname=g1r_mp",
    "g1r_server", "env:G1R_DB_PASSWORD", "charset=utf8mb4;autoreconnect=true")
if not db then
    outputDebugString("Initial database connection failed", 1)
end

The initial call still returns a connection or false synchronously. After a successful initial connection, the same resource-owned handle can recover in the background. Existing scripts retain the previous opt-in policy: autoreconnect defaults to false. TLS, charset, credentials and connection timeouts from dbConnect are reused; application SQL session settings are not replayed.

Guarantees and limits

• No failed SQL statement is automatically replayed. A lost response to a write does not prove that the write failed: it may already have committed.
• SQL queued behind a failed operation on that connection is cancelled with error 90011, preserving completion order. New queries are rejected while disconnected or reconnecting. There is no unbounded offline SQL queue.
• Transport errors 2006, 2013 and 2055 mark the connection unavailable. Ordinary SQL errors do not cause reconnects.
• Automatic recovery first waits about 1 second, then failed attempts back off to at most 30 seconds plus a small per-connection stagger. Connect/read/write timeouts still apply. Work and idle health checks run on database workers, not the game thread.
• With autoreconnect enabled, an idle connection is checked after about 30 seconds without work. dbIsConnected and dbGetConnectionState report the last observed status, not a synchronous network probe. Extremely short database wait_timeout values can still cause an operation to fail before recovery.
• A transaction, autocommit-disabled session, or failed control/unknown statement that might have changed session state is fenced as transaction_lost. Even with autoreconnect enabled it requires an explicit dbReconnect call. This state means recovery is required, not that a lost COMMIT definitely rolled back.
• Reconnection increments generation. Reapply application session initialization and reconcile uncertain writes before resuming gameplay operations. Temporary tables, locks, variables and open transactions do not survive a replaced session.
• dbClose and resource shutdown cancel future recovery. Handles and events belong to their creating resource. Event history is bounded; use dbGetConnectionState as the current snapshot.

State notifications

addEventHandler("onDatabaseConnectionStateChange", resourceRoot,
    function(connection, state, generation, errorCode, errorMessage)
        if connection ~= db then return end
        outputDebugString("Database state: " .. state .. ", generation=" .. generation)
        -- Pause new database-dependent operations while unavailable.
        -- Do not automatically resend failed INSERT/UPDATE/COMMIT statements.
        -- For transaction_lost, reconcile the operation, then explicitly
        -- request a fresh session with dbReconnect(connection).
    end)

See dbReconnect, dbGetConnectionState and onDatabaseConnectionStateChange for exact contracts.

gothic_rp integration

The 0.1.4 gamemode uses automatic recovery for its regular connection and explicit recovery for its serialized transaction connection. During an outage it rejects new database-dependent work. Recovery resets any surviving abandoned transaction and uses a separate databaseRecovered event: it does not replay startup migrations, world restoration or login for already authenticated players.

Transactions in the gamemode's helper write a UUID receipt in the same InnoDB transaction as the business data. If the COMMIT response is lost, a locking receipt lookup resolves whether it committed before releasing the transaction callback. Receipt handling is a gothic_rp implementation, not an automatic guarantee for arbitrary dbQuery/dbExec users. The helper rejects transaction-control and DDL steps; its tables must remain transactional. Successfully resolved receipts are deleted on a best-effort basis; records left after a process crash are harmless and may require administrative retention cleanup.

Character autosave now waits for SQL acknowledgement before clearing the dirty flag; an older acknowledgement cannot clear a newer save. Live characters are saved again after recovery. This is not durable offline persistence: disconnecting a player or stopping/crashing the game server while the database is unavailable can lose unsaved in-memory state. Failed writes are not blindly replayed after login.

Test checklist

On an isolated test database, test idle timeout, a killed connection, database restart, prolonged downtime, interrupted transaction, a lost COMMIT response, stopping the resource during recovery, and an ordinary SQL syntax error. Verify callback uniqueness, no duplicate monetary/item updates, unchanged ownership limits and continued server responsiveness. Never run destructive connection tests against a live player database.

Background

The native connector's implicit reconnect remains disabled; recovery is controlled by G1R:MP. See MariaDB mysql_ping and MariaDB ROLLBACK.