Frameworks and most scripts store characters, money, cars and inventories in a database. FiveM accesses it through the oxmysql resource. We'll show you how to create the database, connect to it correctly, import SQL into it and back it up.
What you'll need
- A FiveM server at StormHost (or your own FiveM server with a MariaDB database)
- The Databases permission (the owner always has it)
- HeidiSQL (opens in a new window) on Windows (for importing and backups)
- About 20 minutes
Why oxmysql#
oxmysql is a library that other resources use to send queries to a MySQL or MariaDB database. It replaces the older mysql-async and ghmattimysql and understands their syntax, so older scripts keep working. The txAdmin recipes for ESX, QBCore and Qbox install it for you.
The oxmysql authors recommend a MariaDB database. Most FiveM scripts were written for MySQL 5.7 and often have problems with MySQL 8 (more reserved words, default values for JSON columns).
Creating the database#
- In the customer area, open My services, click your server and go to the Database tab.
- In the Database name field, type a name, for example
fivem. Letters, numbers and underscores are allowed. - In the Allowed connections from field, type
%(the default value). - Click Create database.
The database shows these details:
| Detail | What it means | Where you'll use it |
|---|---|---|
| Database name | The actual database name. It may differ from what you typed, so always copy it from the list. | Connection string, HeidiSQL, txAdmin (Database Name). |
| User | A database user with permissions for this database only. | Connection string, HeidiSQL. |
| Host | The database server address in the form IP:port, for example 203.0.113.20:3306. | The part before the colon is the host, the part after it is the port. |
| Password | The user's password. Show it with the eye icon, copy it with the icon next to it. | Connection string, HeidiSQL. |
| Allowed connections from | Where connections to the database are allowed from. % = anywhere, otherwise a single IP address. | You need % if you also want to connect from your computer with HeidiSQL. |
- New password generates a new password. The old one stops working, so you have to change it in
server.cfgtoo. - The number of databases is limited by your plan; you see the current state as
Used X of Y. - If Host, User or Allowed connections from are covered by a hatched bar, streamer mode is on. Click Show details at the top, or copy the value with the icon next to it.
Delete also deletes the data
The Delete button permanently removes the database along with all player characters and money. Backups in the Backups tab only contain the server files, not the database. Export the database before deleting it (see “Database backup”).
Installing oxmysql#
If you deployed the server with a txAdmin framework recipe, you already have oxmysql and can skip this section.
- Download the latest release from oxmysql on GitHub (opens in a new window) (the
oxmysql.zipfile, not the source code). - Extract the archive. You get an
oxmysqlfolder with anfxmanifest.luafile and thedist,lib,webandloggersubfolders. - Upload the whole
oxmysqlfolder toresourceson the server (create subfolders with the New folder button; the procedure is in FiveM resources).
Connection string#
oxmysql reads the login details from the mysql_connection_string variable in server.cfg. You can write it in two ways:
set mysql_connection_string "mysql://<USER>:<PASSWORD>@<DATABASE_IP>:<PORT>/<DATABASE_NAME>"set mysql_connection_string "user=<USER>;password=<PASSWORD>;host=<DATABASE_IP>;port=<PORT>;database=<DATABASE_NAME>"Use only one of these lines. For a password from the Database tab, the right choice is the first (URI) form, because it handles all the characters that appear in it (see “Special characters in the password”). Replace the values in angle brackets with the details from the Database tab, without the brackets:
| Part | Where to get it |
|---|---|
<USER> | User |
<PASSWORD> | Password |
<DATABASE_IP> | Host, the part before the colon (for example 203.0.113.20) |
<PORT> | Host, the part after the colon (usually 3306) |
<DATABASE_NAME> | Database name, exactly as shown in the list |
set– the variable stays on the server only. Never usesetrorsets; the password would be sent to players or made public.mysql://– marks the first (URI) form. The second form consists ofkey=valuepairs separated by semicolons.
Special characters in the password#
oxmysql doesn't decode the string in any way, so writing something like %40 instead of @ doesn't work. Paste the password exactly as it is. The oxmysql authors generally advise avoiding the characters ; , / ? : @ & = + $ # in the password, or switching to the second form. Based on how oxmysql actually splits the string:
| The password contains | Use |
|---|---|
only letters, numbers and the characters @ = + ! . ^ & $ , ; | The URI form (mysql://…). These characters don't bother it. The semicolon form, however, splits on = and ;. |
: / ? or # | The semicolon form (user=…;password=…). The URI form splits on these characters. |
" | Neither. The quote ends the value in server.cfg. |
: / ? # together with = or ; | Click New password in the Database tab and use the new password. |
Passwords created by the Database tab contain, apart from letters and numbers, only characters the URI form can handle (for example @, =, +, !, .). So use the URI form for them and don't change the password.
Where to put the line#
The set mysql_connection_string line must be in server.cfg before the ensure oxmysql line, and oxmysql must start before all scripts that use the database:
set mysql_connection_string "mysql://<USER>:<PASSWORD>@<DATABASE_IP>:3306/<DATABASE_NAME>"
ensure oxmysql
ensure ox_lib
ensure es_extendedImporting SQL in HeidiSQL#
Scripts often include an .sql file that creates the tables they need. To import it with HeidiSQL:
Download and install HeidiSQL (opens in a new window).
Start it. In the Session manager window, click New at the bottom left.
Fill in the Settings tab:
Field What to enter Network type MariaDB or MySQL (TCP/IP)Hostname / IP The IP address from the Host field (the part before the colon) User User from the Database tab Password Password from the Database tab Port The port from the Host field (the part after the colon), usually 3306Databases The database name (optional; HeidiSQL then shows only that database) Click Open. Your database appears on the left.
Click the database so that it's selected (active).
In the menu, choose File › Run SQL file… and select the
.sqlfile.For the Encoding option, choose
UTF-8and confirm.HeidiSQL runs the statements right away. When it's done, press F5 and you'll see the new tables in the database.
If HeidiSQL can't connect, check that Allowed connections from is % (or your public IP address).
Query tuning#
| Setting | What it does | Recommended value |
|---|---|---|
set mysql_slow_query_warning 300 | Warns in the console when a query takes longer than the given number of milliseconds. The default is 200. | Don't add the line; the default is enough. Only set a higher number if the warnings flood your console. Slow queries at server startup are fine. |
set mysql_debug true | Prints all queries to the console. Can be limited to selected resources (the syntax is in the oxmysql documentation). | Only while hunting a bug, then turn it off. |
set mysql_ui true | Enables the in-game /mysql command with an overview of queries and their timings (requires the command.mysql permission). | Only during development or with few players. |
Database backup#
The Backups tab only backs up the server files. Back up the database separately:
- In HeidiSQL, right-click the database and choose Export database as SQL.
- Tick the tables (all of them) and leave Data at
Insert. - For Output, choose
Single .sql file, pick a file and click Export.
Make a backup before updating the framework, before installing a large script, and regularly (for example once a week). To restore, import the file as described in “Importing SQL in HeidiSQL”.
Verification#
After restarting the server, you'll find this line in the console:
[<DATABASE_VERSION>] Database server connection established!The square brackets contain the database server version (for example with MariaDB in it). After that, frameworks report loading without database errors.
Troubleshooting#
Unable to establish a connection to the database (ER_ACCESS_DENIED_ERROR)! #
Wrong user or password. Copy them again from the Database tab. Check that you don't have the password in the semicolon form with an = character (use the URI form, see the table above), and that you didn't encode it in any way (%40 instead of @). New password can help too.
Unable to establish a connection to the database (ECONNREFUSED)! or (ETIMEDOUT)#
Wrong address or port. The connection string needs the IP address and port from the Host field, not localhost.
Unable to establish a connection to the database (ER_BAD_DB_ERROR)!#
A database with this name doesn't exist. Copy the Database name exactly from the list.
mysql_connection_string structure was invalid#
The URI-form string can't be parsed. Check that it starts with mysql:// and contains @, and that the password doesn't contain /, ? or #.
Table '….users' doesn't exist (or another table)#
The tables of the script or framework are missing. Import its .sql file.
Can't create table … (Foreign Key Constraint is incorrectly formed)#
The new table has a different character set (collation) than the existing ones. Add DEFAULT CHARSET=utf8mb4 COLLATE= to the end of the CREATE TABLE statements in the .sql file and run the import again.
Player data is missing after restoring a backup#
Backups in the Backups tab only contain the server files, not the database. Restoring a backup therefore doesn't bring the database back. Restore the database data from your own .sql export.
Summary
- Create the database in the Database tab. The host and port are in the Host field; copy the name from the list.
set mysql_connection_string "…"goes intoserver.cfgbeforeensure oxmysql, always withset.- A password from the Database tab goes into the URI form (
mysql://…) exactly as it is. Use the semicolon form only for passwords with:/?#. URL encoding doesn't work. - Import SQL in HeidiSQL with File › Run SQL file… and back up the database with Export database as SQL.