FiveM databases and oxmysql: setup and slow queries

Everything a FiveM roleplay server remembers between restarts lives in a database: characters, money, vehicles, property, jobs, bans. The framework does not talk to it directly. It goes through oxmysql, a resource that holds a pool of connections and exposes query functions to every other resource on the server. Get the connection string right and you will never think about it again. Get it wrong and the symptom is not a database error - it is players spawning with no character, money resetting on relog, or the whole server hanging at the loading screen while one resource waits forever for a query that will never answer.

What a FiveM server actually stores#

A framework schema is bigger than people expect. ESX Legacy and QBCore both ship an SQL dump that creates somewhere between fifteen and forty tables on import, and every resource you add afterwards can add its own.

The ones that matter, and that you will end up looking at by hand:

• The character table. ESX calls it users and keys it on identifier. QBCore and Qbox call it players and key it on citizenid, with the player's licence identifier in a separate column. One row per character.
• Vehicles. owned_vehicles on ESX, player_vehicles on QBCore, both keyed on the plate and linked back to the owner.
• Jobs and grades. On ESX these are database rows in jobs and job_grades. On QBCore and Qbox they are Lua, not SQL, which is one of the practical differences described in FiveM frameworks compared.
• Bans, logs, and whatever your phone, banking and housing resources decided to create.

The thing to understand about all of these is that the interesting columns are JSON blobs. A QBCore players row keeps money, charinfo, job, gang, metadata and inventory as JSON text. ESX does the same with accounts and inventory. That design keeps the framework flexible and means you cannot index inside those columns, cannot query "everyone with more than ten thousand in the bank" efficiently, and pull several kilobytes every time you SELECT * a player. It also means a corrupted blob breaks exactly one character rather than the table, which is the trade they took.

Characters are not written continuously. Both frameworks save on a timer and on disconnect - qb-core exposes the interval as Config.UpdateInterval, in minutes, and ESX has an equivalent in its config. Anything that kills the process without a clean shutdown loses up to one interval of progress for everybody online. That is the single best argument for restarting a FiveM server through the panel's Restart button rather than killing it.

The database slot, and what oxmysql needs from it#

oxmysql speaks the MySQL wire protocol, so it needs a MySQL or MariaDB compatible server. What it wants from you is five things: a host, a port, a user, a password and a database name.

On RE:NODE, a game plan includes one database slot, created from the panel's Databases tab. The panel generates the host, the user and the password for you; you do not install or administer a database server, and there is no root account to look after. Beside the slot is an Open in phpMyAdmin button, which signs you in with a single-use token that expires after sixty seconds - so browsing the tables is a button rather than another password to keep somewhere. The panel does not name the engine behind the slot, and oxmysql does not need you to: it negotiates the protocol on connect. What matters is that the credentials the panel gives you are the credentials you paste into server.cfg.

Two honest limits. One slot means one database - enough for a framework, since ESX and QBCore both put everything in one, but not enough if you also wanted a separate database for a Discord bot on the same plan. And our database hosting line is PostgreSQL and MongoDB, neither of which oxmysql can talk to, so that product is not an upgrade path for a FiveM schema. The panel's database slot is.

The connection string#

oxmysql reads one convar. Put it in server.cfg above the ensure lines, using set and never sets - a connection string published to the server list is scraped within minutes. The difference between the three is in FiveM server.cfg explained.

set mysql_connection_string "mysql://s7_user:PaSsW0rd@db.example.net:3306/s7_fivem?charset=utf8mb4"ensure oxmysqlensure ox_libensure qb-core

The pieces, in order: user, password, host, port, database name, options. Three things go wrong here more than anything else.

Special characters in the password. A URI has structural characters, and @, :, /, ?, # and % in a password will break the parse in ways that produce a nonsense error. Either percent-encode them (@ becomes %40) or use the semicolon form instead, which does not care:

set mysql_connection_string "server=db.example.net;port=3306;userid=s7_user;password=P@ss:word;database=s7_fivem"

The host is not `localhost`. The database is a separate service reached over the network, so 127.0.0.1 connects to nothing. Copy the host exactly as the panel prints it.

The database name has a prefix. Panel-generated databases usually carry a server prefix such as s7_. Leave it in. ER_BAD_DB_ERROR is almost always someone who trimmed it off.

Add ?charset=utf8mb4 and mean it. Without it, the first player whose character name contains an accent, a Cyrillic letter or an emoji writes broken bytes into a column, and you find out weeks later. Two useful extras: ?connectionLimit=8 caps the pool, and oxmysql accepts the usual driver options in the same query string.

Turn the diagnostics on while you are setting up and off once it works:

set mysql_debug true                 # prints every query, very noisyset mysql_slow_query_warning 150     # milliseconds before a warning

mysql_debug belongs on a test server only. mysql_slow_query_warning belongs on permanently; it is the cheapest performance tool you have and it names the resource responsible.

Importing the framework schema#

Every framework ships an SQL dump. Import it before the first start, not after the server has spent ten minutes printing ER_NO_SUCH_TABLE.

1. Find the dump. It is usually in the core resource - qb-core/qb-core.sql for QBCore, an equivalent in the ESX release. Some builds split it across several files, and the order matters if there are foreign keys.
2. Open phpMyAdmin from the panel, pick your database in the left column, and use the Import tab.
3. Set the character set to utf8mb4 on import, matching what you put in the connection string.
4. Check the table count afterwards. A dump that stopped halfway leaves a server that half works, which is worse than one that does not start.

If the file is too large for the upload limit - and framework dumps often are - compress it. phpMyAdmin accepts .sql.gz and .sql.zip and will decompress on the way in, which typically shrinks a dump by eight or nine times. If the import times out partway, phpMyAdmin's Import page has a "Skip this number of queries" field: count what already ran and resume from there. The phpMyAdmin import and export guide goes through both in detail.

Writing queries that do not hurt#

oxmysql is loaded into a resource by adding one line to fxmanifest.lua:

server_scripts {    '@oxmysql/lib/MySQL.lua',    'server/main.lua',}

That gives you MySQL.query, MySQL.single, MySQL.scalar, MySQL.insert, MySQL.update, MySQL.prepare and MySQL.transaction, each with an .await variant for use inside a coroutine.

-- one row, one valuelocal money = MySQL.scalar.await(    'SELECT money FROM players WHERE citizenid = ?', { citizenid })-- one row as a tablelocal row = MySQL.single.await(    'SELECT charinfo, job FROM players WHERE citizenid = ?', { citizenid })-- many statements, one round trip, all or nothingMySQL.transaction.await({    { 'UPDATE players SET money = ? WHERE citizenid = ?', { newMoney, citizenid } },    { 'INSERT INTO bank_log (citizenid, amount) VALUES (?, ?)', { citizenid, delta } },})

Always use ? placeholders rather than concatenating values into the string. It is faster, because the server can reuse the plan, and it is the only defence against a player putting a quote in a name and rewriting your query for you.

Four habits separate a database that is idle at 64 players from one that is the bottleneck:

• Never query in a loop over players. One query returning thirty rows beats thirty queries returning one. If you are iterating players and calling .await inside the loop, you are also blocking the coroutine on every iteration.
• Never query on a tick. Cache the value in a Lua table, write it back on change and on save.
• Select the columns you need. SELECT * on a players row drags every JSON blob across the wire, and inventories are not small.
• Batch writes in a transaction. Ten updates in one transaction is one round trip and one lock window; ten separate updates is ten of each.

Connection pools and limits covers the pool side of this, which is what bites when twenty resources each decide to be clever at the same moment.

Finding the slow query#

With mysql_slow_query_warning set, oxmysql prints a line naming the resource, the time taken and the statement whenever a query crosses the threshold. That line is the whole investigation most of the time: a resource nobody has looked at since 2022 is running a SELECT with a LIKE '%name%' against a table that has grown to four hundred thousand rows.

When the warning is not enough, take the statement into phpMyAdmin and put EXPLAIN in front of it. What you are looking for is the scan type. A row that says the query examined most of the table for one result is a missing index, and the fix is usually one line:

ALTER TABLE player_vehicles ADD INDEX idx_citizenid (citizenid);ALTER TABLE owned_vehicles ADD INDEX idx_owner (owner);

Index the columns you filter or join on - owner, citizenid, identifier, plate. Do not index everything: every index is another thing to write on every insert, and a table with twelve indexes is slower to save a character to than one with three.

The other half of the problem is tables that grow without limit. Logging resources, phone messages, transaction histories and admin action logs all accumulate forever unless somebody prunes them. A monthly clear-out keeps them from becoming the slow query:

DELETE FROM <your log table> WHERE created_at < NOW() - INTERVAL 30 DAY;

Run it once by hand, check the row count, and then decide whether it is worth scheduling. Deleting a million rows in one statement holds locks for a long time, so on a big table do it in batches with LIMIT 10000 and repeat.

Backups, and the thing that is easy to miss#

A backup of your server takes the files: resources/, server.cfg, the cache. The database is a separate service on a separate host, so it is not in that archive. If you restore a server backup after something goes wrong, you get every resource back and every character exactly as it was in whatever state the database is in now, which may be the state you were trying to escape.

So export the database as well, and treat that as the real backup:

1. In phpMyAdmin, select the database, then Export, then Custom.
2. Format SQL, output compressed with gzip, and tick "Add DROP TABLE" so the file restores cleanly onto a database that already has tables.
3. Download it and put it somewhere that is not the game server. A copy sitting in resources/ is not a backup - it dies with the thing it was protecting.

Do that before every framework update, before running any resource's .sql, and on a routine you will actually keep. Then restore one, once, into a scratch database and log in - testing a restore before you need it exists because the export that has never been imported is a hypothesis. Database backups and restores has the general procedure, and the panel guides cover where the slot lives.

Connection errors and what they mean#

Two more that are not errors but look like them. Connection lost: the server closed the connection on an idle server is the database dropping a stale connection, and oxmysql reopens it - if it happens once an hour and nothing breaks, ignore it. And a first query that takes two seconds after a restart is the pool warming up, not a slow database.

If nothing connects at all, check in this order: the convar is set and spelled correctly, ensure oxmysql comes before everything that uses it, the host is the panel's host and not localhost, and the password has no unencoded special characters. That sequence resolves nearly every case.

FAQ#

Does a FiveM server need a database?

Only if it needs to remember anything. A freeroam or racing server with no persistence runs fine with no database at all. Every roleplay framework needs one, because a character that does not survive a restart is not a character.

Can I use PostgreSQL or MongoDB instead?

Not with oxmysql or with any mainstream FiveM framework. They are written against the MySQL protocol and its SQL dialect. Our PostgreSQL and MongoDB lines are for applications, not for ESX or QBCore.

How big does a FiveM database get?

Smaller than people fear. A server with a few thousand characters, their vehicles and a year of logs is usually in the low hundreds of megabytes. Log tables are what grow, not characters - if yours is measured in gigabytes, one logging resource is responsible and pruning it will win most of it back.

Why did everyone lose money after a crash?

Because characters are written on a timer, not on every change. A process that dies without a clean shutdown loses whatever happened since the last save for every player online. Shorten the framework's save interval if the loss matters more than the write load.

Can two servers share one database?

Technically yes, and it is how people run a test server against real data. Do not do it with two live servers on the same framework: both will cache and write the same character rows and the last writer wins, which players experience as items disappearing.

Do I need to know SQL to run a FiveM server?

Enough to read an EXPLAIN, add an index and export a dump. The framework's own dump does the rest. Everything in this post is four or five statements you can copy, and phpMyAdmin will write most of them for you.