CityRP Documentation Repository

Research: how `DB_MAIN` types result columns, and what type `cityrp.session`'s token should be

Last updated

  • Ticket: limelight-development/cityrp2#3442
  • Parent map: #3431 — spec for a server-only cityrp.session library at gamemode/core/libraries/sv_session.lua.
  • Surfaced by: #3432. Blocks #3436, #3437.
  • Date: 2026-09-02
  • Status: Research complete. Planning only — no gamemode code was changed.

Repo citations are path:line relative to gamemodes/cityrp_2_by_limelightgaming/ unless the path starts docs/. Module citations are against SuperiorServers/gm_tmysql4 at master, which is the module gamemode/db.lua:9 loads with require("tmysql4"). MySQL claims are quoted from dev.mysql.com and nothing else.

TL;DR

Question Answer
What type does an INT column come back as? A Lua number. tmysql4 converts it in C.
What about BIGINT? A Lua string — BIGINT is the one numeric type explicitly excluded from the conversion, on purpose.
Does it differ between Run and FRun? No. FRun is a string.format wrapper that calls Run; it never touches the result.
Does it differ between prepared and raw queries? No. Both Result constructors store every cell as a std::string and both dispatch through the same PopulateLuaTable, which types from the column metadata.
Is refund_ents/sv_init.lua:179 broken on Live? No. track.start_time is declared int, so row.start_time is a number and the == is number-to-number. No Bug filed for it.
Is sv_init.lua:241 broken? No. MAX() returns its argument's type, so MAX(start_time) over an int is also a number.
What type should cityrp.session.id be? A Lua number, backed by session_id int unsigned NOT NULL AUTO_INCREMENT. Never BIGINT, never VARCHAR.
Should the library expose Is() / Matches()? Yes — not because the DB lies today, but because the type is a function of the column declaration and the SQL expression, and both of those decay silently.

One live defect was found in passing, in a different plugin — bans/sv_init.lua:356, filed as #3451. See §5.

1. Where the typing decision actually happens

gamemode/db.lua:9 is require("tmysql4"), and DB:Connect (db.lua:47-53) calls tmysql.initialize(...). Every result the gamemode ever sees is built by that binary module. There is exactly one function in it that decides a Lua type, and it is Result::PopulateLuaTable:

for (unsigned int c = 0; c < m_colCount; c++)
{
    if (useNumbers == true)
        LUA->PushNumber(c + 1);

    if (m_nullRowValues[r][c])
        LUA->PushNil();
    else if (IS_NUM(m_columnTypes[c]) && m_columnTypes[c] != MYSQL_TYPE_LONGLONG)
        LUA->PushNumber(atof(m_rows[r][c].c_str()));
    else
        LUA->PushString(m_rows[r][c].c_str(), m_rows[r][c].length());
    ...
}

Three branches, in order:

  1. SQL NULL → Lua nil. Not "", not 0.
  2. IS_NUM and not LONGLONG → Lua number, via atof on the wire string.
  3. Everything else → Lua string.

IS_NUM is the connector's own macro, vendored in the same repo at include/connector/mysql.h:106:

#define IS_NUM(t) (((t) <= MYSQL_TYPE_INT24 && (t) != MYSQL_TYPE_TIMESTAMP) || (t) == MYSQL_TYPE_YEAR || (t) == MYSQL_TYPE_NEWDECIMAL)

m_columnTypes[c] is field->type read from mysql_fetch_field_direct — the declared column type as MySQL reports it in the result metadata, not a guess from the value. So the answer is entirely determined by the schema (or, for an expression, by what MySQL says that expression's type is).

The resulting table

MySQL column type field->type Lua type in row.x
TINYINT, SMALLINT, MEDIUMINT, INT TINY/SHORT/INT24/LONG number
FLOAT, DOUBLE FLOAT/DOUBLE number
DECIMAL, NUMERIC NEWDECIMAL number
YEAR YEAR number
BIGINT LONGLONG string
TIMESTAMP, DATETIME, DATE, TIME — string
CHAR, VARCHAR, TEXT, BLOB, ENUM, SET, JSON — string
any column, value NULL — nil

BIGINT is excluded deliberately: a Lua number is an IEEE-754 double, which cannot represent every 64-bit integer exactly, so pushing a BIGINT as a number would silently corrupt large values (SteamID64s being the obvious case). The module chooses lossless-as-a-string over lossy-as-a-number. That is the right call, and it is also the trap — BIGINT is a numeric type that does not behave numerically in Lua.

Run vs FRun

No difference. DB:FRun (db.lua:169-173) is:

function DB:FRun(query, ...)
	local cb = select(-1, ...)
	if not isfunction(cb) then cb = nil end
	self:Run(query:format(...), cb)
end

It formats a string and delegates. DB:Run (db.lua:130-156) is the only place that unwraps the result, and it hands the callback r.data verbatim. RunChecked, QueryParams and QueryParamsChecked all funnel into Run/RunChecked the same way.

Prepared vs raw

No difference either, and the source is unambiguous about why. Result has two constructors:

  • Result(MYSQL*) — raw path. mysql_fetch_row gives char* per cell; each is stored as m_rows[i][n] = std::string(row[n], len[n]).
  • Result(PStatement*) — prepared path. Every bind is forced to bind.buffer_type = MYSQL_TYPE_STRING (with the source comment "I really don't like making prepped statements mimic the char array style of MYSQL_ROW"), and each cell lands in the same m_rows std::string store.

Both constructors populate m_columnTypes[i] = field->type from the same mysql_fetch_field_direct, and both results are rendered by the single PopulateLuaTable above. So the wire representation is a string in both cases, and the Lua type is decided from column metadata in both cases — identically.

CityRP2 does not use prepared statements through DB_MAIN at all today, so this is future-proofing rather than a live concern.

Two more properties worth pinning down

Column keys are names, not indices. Database::lua_Query (database.cpp:351-374) reads the ColumnNumbers switch off argument 5: mysqldb->QueueQuery(query, callbackfunc, callbackref, LUA->GetBool(5)). DB:Run calls self.Connection:Query(query, function(res) ... end) — two arguments — so argument 5 is nil, GetBool is false, and rows are always keyed by column name. row[1] is nil in every callback this gamemode writes. (That is the sidebar bug in §5.)

lastid is always a number. PopulateLuaTable pushes it with LUA->PushNumber(m_iLastID) unconditionally, from mysql_insert_id. DB:Run (db.lua:150-152) routes it to the callback for any query matching query:find("INSERT INTO"). So an AUTO_INCREMENT value arrives as a number on insert, whatever the column's declared type.

Hold that thought — it is the whole argument in §4.

2. Aggregates and expressions change the answer

The type comes from result metadata, so wrapping a column in an expression can change it.

  • COUNT() — the MySQL manual is explicit: "The result is a BIGINT value." (Aggregate Functions). BIGINT is LONGLONG, so COUNT() always comes back as a Lua string.
  • SUM() / AVG() — "return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments" (ibid.). Both map to IS_NUM and neither is LONGLONG, so number.
  • MAX() / MIN() — the manual documents these as taking the type of their argument ("MAX() may take a string argument; in such cases, it returns the maximum string value"), and MySQL reports the argument's field type in the result metadata. MAX(int_col) is therefore MYSQL_TYPE_LONG → number.
  • CAST(x AS SIGNED) / CAST(x AS UNSIGNED) — yields BIGINT → string. Casting to make something "a number" in SQL makes it a string in Lua. This is the most counterintuitive case in the whole set.

The codebase already knows this

Two live call sites are written against the COUNT()-is-a-string rule, which is about as strong an empirical confirmation as a static read can offer:

  • vehicles/sv_init.lua:1160 — if (res[1]["COUNT(*)"] == "1") then. Compared against a string literal. This is on the vehicle-spawn path, so it demonstrably works on Live.
  • container_code/sv_dbcode.lua:39 — if (tonumber(res[1]["COUNT(*)"]) > 0) then. Explicit tonumber, which would be redundant if COUNT() were a number.
  • meska/hooks.lua:129 — local count = tonumber(results[1]["total"]) over COUNT(id) AS total. Same.

3. Verdict on refund_ents: not broken, no Bug filed

The ticket's hypothesis was that row.start_time == self.session (refund_ents/sv_init.lua:179) compares a string to a number and is false for every row, refunding every still-standing entity. That would be a duplication bug in footgun #7's family. It is not what happens.

track.start_time is declared in the baseline migration (gamemode/core/migrations/0001_baseline_live_schema.lua:800-809):

CREATE TABLE IF NOT EXISTS `track` (
    `server` varchar(40) NOT NULL,
    `start_time` int NOT NULL,
    `ent_index` int unsigned NOT NULL,
    `owner_steamid` varchar(50) NULL,
    `entity` varchar(45) NULL,
    `data` text NULL,
    `flagged` tinyint DEFAULT 0 NULL,
    PRIMARY KEY (`server`, `start_time`, `ent_index`)
) ENGINE=InnoDB CHARSET=latin1

int, not bigint. MYSQL_TYPE_LONG passes IS_NUM and is not LONGLONG, so PopulateLuaTable takes the PushNumber(atof(...)) branch. row.start_time is a Lua number.

PLUGIN.session is os.time() (refund_ents/sv_init.lua:21), also a number. The comparison at line 179 is number-to-number and behaves as intended.

Line 241 — self.lastSession = results[1].start_time from SELECT MAX(start_time) start_time ... (sv_init.lua:235) — is also safe, per the MAX() rule in §2. And even had it returned a string, that value is only ever interpolated back into SQL with %s (sv_init.lua:169), never ==-compared, so it would have survived anyway.

Nothing needs fixing here, and nothing has been filed against refund_ents. The hypothesis was worth testing — it is the exact shape of bug this codebase produces — but the schema saves it.

The near-miss is instructive rather than reassuring, though. refund_ents is correct because somebody in 2015 wrote int and not bigint, and no comment anywhere records that the plugin's correctness depends on that choice. An ALTER TABLE track MODIFY start_time bigint — an entirely reasonable-looking change, and one somebody might make for Y2038 headroom — would turn line 179 false for every row and start refunding live contraband while the entities stayed standing, with no error, no log line, and nothing in the diff that looks wrong. That is the argument for §6.

4. What type cityrp.session.id and session_id should be

cityrp.session.id: a Lua number

The column: session_id int unsigned NOT NULL AUTO_INCREMENT — never BIGINT, never VARCHAR

The map (#3431) settled that identity is "a locally generated token plus the DB row's autoincrement". Those are two values with two different types, and only the autoincrement is at risk here.

The decisive constraint is the insert/select asymmetry. The autoincrement reaches Lua by two different routes:

Route Source Lua type
At session open — DB:Run's INSERT INTO callback LUA->PushNumber(m_iLastID) always a number, whatever the column type
At read-back — SELECT session_id FROM ..., or a joined track.session_id PopulateLuaTable, from column metadata number if INT, string if BIGINT

With an int column, both routes give a number and every comparison is consistent. With a BIGINT column, cityrp.session.id would be a number for the whole life of the process — because it came from lastid — while every row read back carrying that same id would be a string. == would be false everywhere, and it would be false only in the read path, so unit-style poking at the library in isolation would show nothing wrong. That is precisely the failure mode the ticket went looking for in refund_ents, and choosing BIGINT is how we would actually build it.

int unsigned is 4.29 billion sessions. At a generous ten world-loads per server per day across the fleet, that is not a bound anyone will reach; it is not worth buying BIGINT's range at the cost of the type asymmetry. If range ever genuinely matters, the answer is to normalise on read (§6), not to widen the column and hope.

VARCHAR is worse and there is a live precedent for it going wrong-ish already: crash_container.container_id is varchar(40) (migrations/0001_baseline_live_schema.lua:261) while the server_stats.session_id it stores is int NOT NULL AUTO_INCREMENT (:771). container_code/sv_dbcode.lua:14,61 gets away with it only because both comparisons happen inside SQL, where MySQL coerces. The moment that value is read into Lua and compared there, it is a string against a number. When container_code migrates onto cityrp.session (a decision the map has already taken), its session_id column must be declared int unsigned and not inherit container_id's varchar(40).

Every table that references a session must declare the FK column int unsigned — track, the container_code replacement, and phase two's player_sessions alike. A single varchar or bigint among them reintroduces the split.

The opaque token stays a string

#3433 settled the token as a util.SHA256 digest — a 64-character hex string. Store it char(64) (or varchar(64)); CHAR/VARCHAR is outside IS_NUM, so it round-trips as a string with no coercion at any point. There is no hazard here, and no reason to normalise it.

Two consequences worth writing into the spec:

  • The token is never the join key. The map already decided this (rejecting FearlessRP's join-on-token); the typing analysis independently agrees, since the numeric autoincrement is the value that compares cleanly and cheaply.
  • The token must never be fed to tonumber by the comparison helper's normalisation path. A 64-character hex digest is not a number, tonumber returns nil, and a naive tonumber(a) == tonumber(b) helper would then report nil == nil → true and match everything. See the contract below.

5. Sidebar: a real bug found in passing

Not refund_ents, not this map, filed on its own.

bans/sv_init.lua:355-363:

DB_MAIN:Run("SELECT COUNT(*) FROM bans WHERE ... steamid = '" .. Escape(sid) .. "' ...", function(bans)
    if (bans[1][1] == nil) then return end

    local banc = tonumber(bans[1][1]) or 0
    if (banc > 0) then
        msg_admins("Bans System Init Spawn Check has kicked and re-banned " .. ply:Name() ...)
        ...
    end
end)

bans[1][1] indexes the row by column number. As established in §1, DB:Run never passes tmysql4's ColumnNumbers argument, so rows are keyed by name only and bans[1][1] is nil unconditionally. The == nil guard therefore returns on every single invocation, and this PlayerInitialized re-ban check has never fired. The column's actual key is "COUNT(*)", exactly as vehicles/sv_init.lua:1160 and container_code/sv_dbcode.lua:39 use it.

Filed as #3451. It has nothing to do with sessions beyond having been turned up by the same read, and should not wait on this map.

6. The comparison contract

Recommendation: yes, ship cityrp.session.Is(value) and cityrp.session.Matches(row, field), and make a bare == against a result-set value a reviewable defect.

The justification is not "the database lies about integers" — §1 establishes it does not, and §3 establishes the existing code is correct. The justification is that the Lua type of a session id is a function of three things that all live somewhere else and all change silently:

  1. The column declaration — int works, bigint and varchar do not, and the difference is invisible at the Lua call site. A migration written a year from now by someone who has never read this document is the realistic vector.
  2. The SQL expression — session_id is a number, MAX(session_id) is a number, COUNT(session_id) is a string, CAST(session_id AS UNSIGNED) is a string. Adding an aggregate or a cast to an existing query changes the Lua type of its result with no other visible effect.
  3. NULL — a LEFT JOIN that misses, or a row written before the column existed, yields nil, which == against a number is quietly false rather than an error. A helper is the only place to make that a deliberate answer rather than an accident.

None of the three produces a lint error, a runtime error, or a stack trace. All three produce "the comparison is false, so this entity is unowned, so refund it" — a duplication bug, on the contraband path, which is what this whole map exists to stop.

Proposed contract

--- Test whether a value identifies the current session.
--- Normalises result-set values, which arrive as strings for `BIGINT`, `VARCHAR` and every
--- aggregate expression (`COUNT()` in particular). Fails closed: `nil`, a non-numeric value,
--- and "the session is not ready yet" are all `false`.
--- @param value any Candidate id — a Lua number, or a value straight out of a `DB_MAIN` row.
--- @return boolean isCurrent True only when `value` is unambiguously this session's id.
function cityrp.session.Is(value)

--- Test whether a result-set row belongs to the current session.
--- @param row table? A row from a `DB_MAIN` callback's result set.
--- @param field string? Column name holding the session id. Defaults to `"session_id"`.
--- @return boolean isCurrent True only when the row exists and its session column is this session.
function cityrp.session.Matches(row, field)

Rules the implementation has to hold to, each of which is a way to get this wrong:

  • Is(nil) is false, never true. The tonumber(a) == tonumber(b) shape gives nil == nil → true and matches every row with a NULL session — the worst possible answer on a fail-closed path. Reject a nil normalisation before comparing.
  • Is(anything) is false while the library is not ready. The map's fail-closed decision: before the DB row lands there is no current session, so nothing is current. This must be the helper's answer too, not just Track's.
  • Normalise the candidate, not the stored id. cityrp.session.id is known to be a number by construction (§4); only the incoming value is of unknown provenance. Normalising both invites someone to make id a string later.
  • tonumber, not tostring. Comparing as strings makes 1 = "01" and 1 = "1.0"; comparing as numbers collapses every representation MySQL might emit for an integer. It also correctly refuses the SHA256 token (tonumber → nil → false), which string comparison would not.
  • Matches takes the field name. Phase two's player_sessions and the track replacement will not agree on a single column name forever, and a Matches(row) that hardcodes "session_id" gets copy-pasted with a manual == the first time it doesn't fit.

Matches is not sugar over Is — it is where the nil-row case is handled once. Is(row.session_id) on a nil row is a hard error inside an async callback; Matches(row) is false.

Where the contract has to be enforced

A helper nobody calls is worse than no helper. The spec should require that after phase one, grep -n "session_id ==\|== .*session" over the migrated plugins returns nothing, and that the cityrp.session docs page states the rule directly: result-set values are compared with cityrp.session.Is, never ==. This is the same shape as footgun #9's "one image-host allowlist" — the failure there was every consumer carrying its own copy of a check, each correct in isolation.

Sources

Module source (SuperiorServers/gm_tmysql4, master — the module db.lua:9 requires):

  • src/result.cpp — Result::PopulateLuaTable, both Result constructors, Resize.
  • src/database.cpp — Database::lua_Query (the ColumnNumbers argument).
  • include/connector/mysql.h:106 — the IS_NUM macro, vendored MariaDB Connector/C.

MySQL manual:

Repo (paths relative to gamemodes/cityrp_2_by_limelightgaming/):

  • gamemode/db.lua:9,47-53,130-156,169-173 — module load, connect, Run, FRun.
  • gamemode/core/migrations/0001_baseline_live_schema.lua:259-268,770-775,800-809 — crash_container, server_stats, track.
  • gamemode/core/plugins/refund_ents/sv_init.lua:21,165-170,179,235,241.
  • gamemode/core/plugins/vehicles/sv_init.lua:1158-1160 — COUNT(*) compared to "1".
  • gamemode/core/plugins/container_code/sv_dbcode.lua:14,39,61.
  • gamemode/core/libraries/meska/hooks.lua:116,129.
  • gamemode/core/plugins/bans/sv_init.lua:355-363 — the sidebar bug.