SQL files and database errors
Import a FiveM resource's SQL file safely, and fix missing tables, duplicate entries and oxmysql connection problems.
Many scripts store data in your server's MySQL or MariaDB database and ship a .sql file that creates it. A wrong import can overwrite data, so take these precautions every time.
Before you import
- Back up the database. Export it with HeidiSQL, phpMyAdmin or your host's panel.
- Read the file. Open it in a text editor and check what it does:
CREATE TABLE IF NOT EXISTS: skips creation if the table already exists; it does not verify or update that table's structure.CREATE TABLEwithoutIF NOT EXISTS: normally fails if that table exists. Compare the schema and use the correct install or update file instead of deleting the existing table.DROP TABLE: deletes a table and all its data. Never run this on a live server without checking which table it is.INSERT INTO: adds rows, often items or jobs.ALTER TABLE: changes an existing table; dropping columns or changing types can lose data.TRUNCATE,DELETEandREPLACE: can remove or replace existing rows. Check the affected tables and conditions.
- Pick the right database. Import into the database your server uses, the one named in
mysql_connection_string.
Use a test copy first. A successful import is not proof that the resource works or that existing data was preserved. After importing, review the SQL tool's errors and compare the expected tables and rows. The creator's version-specific migration takes priority over a generic example. See Update a resource.
Import
In HeidiSQL: select your server database on the left, then File → Run SQL file. In phpMyAdmin: select the database, then Import. Comparison of the tools: HeidiSQL vs Navicat.
Common errors
| Error | Cause and fix |
|---|---|
Table '...' doesn't exist | The SQL file was not imported, or it went into another database. Import it into the database from your connection string. |
Duplicate entry '...' for key 'PRIMARY' | Compare the existing row with the intended definition. A multi-row insert may contain other missing rows; do not discard the whole statement or delete the existing row without checking. |
Table '...' already exists | The table was created before. Compare it with the file; for updates, use the creator's ALTER TABLE statements instead. |
Unknown column '...' | Check that the resource and database schema are from matching versions, and that it uses the right database. Apply the documented migration; do not invent a column to silence the error. |
| Foreign key or collation error | Compare the referenced columns, types and collations with the creator's schema. Ask for the matching migration rather than dropping live tables. |
oxmysql cannot connect
oxmysql reads the connection from server.cfg:
set mysql_connection_string "mysql://user:password@localhost/database_name?charset=utf8mb4"Set the connection string before ensure oxmysql. Check that the user, password, host, port and database name are right, that the database server is running, and that oxmysql starts before every resource that uses it. localhost means the machine or container running FXServer; use your host's supplied database address if the database is elsewhere.
Keep this value private. Do not use setr or sets for database credentials, and redact the connection string from support logs. See the oxmysql configuration guide.
oxmysql supports many older mysql-async and ghmattimysql interfaces. Where a resource uses unsupported calls or a different manifest import, follow Migrate mysql-async resources to oxmysql.
SQL reference: MySQL CREATE TABLE, ALTER TABLE.