QCherry: Document upcoming 0.1.4 database recovery; organize wiki navigation

2026-09-19T10:51:40Z

Document upcoming 0.1.4 database recovery; organize wiki navigation

New page

{{DISPLAYTITLE: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 ==
<syntaxhighlight lang="lua">
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
</syntaxhighlight>

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 ==
<syntaxhighlight lang="lua">
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)
</syntaxhighlight>

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 [https://mariadb.com/docs/connectors/mariadb-connector-c/api-functions/mysql_ping MariaDB mysql_ping] and [https://mariadb.com/docs/server/reference/sql-statements/transactions/rollback MariaDB ROLLBACK].

[[Category:Database Functions]]
[[Category:Lua Examples]]

MySQL connection recovery - Revision history

QCherry: Document upcoming 0.1.4 database recovery; organize wiki navigation