{"id":193033,"date":"2025-08-16T17:57:41","date_gmt":"2025-08-16T15:57:41","guid":{"rendered":"https:\/\/fivemx.com\/?p=193033"},"modified":"2025-12-23T16:43:20","modified_gmt":"2025-12-23T15:43:20","slug":"mysql-assincrono-para-oxmysql","status":"publish","type":"post","link":"https:\/\/fivemx.com\/pt\/mysql-async-to-oxmysql\/","title":{"rendered":"De mysql-async para oxmysql: Migra\u00e7\u00e3o segura e consultas seguras\u2026"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><strong>Audience:<\/strong> FiveM server owners, scripters, maintainers<br><strong>Goal:<\/strong> Replace <code>mysql-async<\/code> with <code>oxmysql<\/code> safely, speed up queries, and modernize your SQL usage.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Also read:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>FiveM Server Optimization: The Definitive 2025 Playbook<\/strong> \u2014 <a href=\"https:\/\/fivemx.com\/fivem-server-optimization\/\">https:\/\/fivemx.com\/fivem-server-optimization\/<\/a><\/li>\n\n\n\n<li><strong>Adapter Patterns: ESX\u2194QBCore\u2194QBOX Exports, Events &amp; Player Models<\/strong> \u2014 <a href=\"https:\/\/fivemx.com\/adapter-patterns\/\">https:\/\/fivemx.com\/adapter-patterns\/<\/a><\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">TL;DR<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Use <code>oxmysql<\/code><\/strong>: prepared statements, promise\/await API, better diagnostics, strong performance.<\/li>\n\n\n\n<li><strong>Minimal code changes<\/strong>: swap <code>@param<\/code> \u2192 <code>?<\/code> (positional) or <code>:name<\/code> (named) params; replace <code>MySQL.Async.*<\/code> calls with <code>MySQL.*<\/code>\/<code>exports.oxmysql:*<\/code>.<\/li>\n\n\n\n<li><strong>Run the SQL \u201cUP\u201d scripts<\/strong> below (charset\/index fixes) and keep the <strong>rollback<\/strong> handy.<\/li>\n\n\n\n<li><strong>Verify with the micro\u2011benchmark harness<\/strong> at the end to confirm wins on your hardware.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">1) Pre\u2011flight Safety Checklist<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Full backup<\/strong>: <code>mysqldump --single-transaction yourdb &gt; backup.sql<\/code>.<\/li>\n\n\n\n<li><strong>Staging env<\/strong> mirroring production schema + data subset.<\/li>\n\n\n\n<li><strong>Artifact &amp; deps<\/strong>: Current FXServer build, latest <code>oxmysql<\/code>.<\/li>\n\n\n\n<li><strong>Downtime window<\/strong> for prod switch (usually &lt; 5 minutes).<\/li>\n\n\n\n<li><strong>Health probes<\/strong> ready: <code>\/players<\/code>, login flow, economy ops, garage ops, inventory ops, ban checks.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">2) Install &amp; Wire Up <code>oxmysql<\/code><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">2.1 server.cfg<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># Stop using mysql-async\ndefault_prio 500\n# ensure mysql-async        # \u2190 comment out or remove\n\n# Start oxmysql\ndefault_prio 50\nensure oxmysql\n\n# Connection string consumed by oxmysql\nset mysql_connection_string \"mysql:\/\/user:pass@127.0.0.1:3306\/yourdb?charset=utf8mb4\"\n\n# Optional diagnostics\nset mysql_slow_query_warning 200   # log queries slower than 200ms\nset mysql_debug false              # true for verbose logging during staging\n<\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Keep <code>mysql-async<\/code> disabled but available in your resources folder during the staging phase (for rapid rollback).<\/p>\n<\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\">3) API Mapping: mysql\u2011async \u2192 oxmysql<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><code>mysql-async<\/code> (legacy):<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Async: <code>MySQL.Async.fetchAll<\/code>, <code>MySQL.Async.fetchScalar<\/code>, <code>MySQL.Async.execute<\/code><\/li>\n\n\n\n<li>Sync: <code>MySQL.Sync.fetchAll<\/code>, <code>MySQL.Sync.fetchScalar<\/code>, <code>MySQL.Sync.execute<\/code><\/li>\n\n\n\n<li>Parameters: <code>@param<\/code> style tables like <code>{ ['@identifier']=identifier }<\/code><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><code>oxmysql<\/code> (modern):<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Callback style via <strong>export<\/strong>: <code>exports.oxmysql:query|scalar|single|insert|update(sql, params, cb)<\/code><\/li>\n\n\n\n<li>Promise\/await via <strong>global<\/strong>: <code>MySQL.query|scalar|single|insert|update.await(sql, params)<\/code> and non-await callbacks without <code>.await<\/code><\/li>\n\n\n\n<li>Parameters: <strong>positional<\/strong> <code>?<\/code> via array, or <strong>named<\/strong> <code>:name<\/code> via object<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">3.1 Common replacements<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>SELECT many<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- mysql-async\nMySQL.Async.fetchAll(\n  'SELECT * FROM users WHERE identifier = @id',\n  { ['@id'] = identifier },\n  function(rows) ... end\n)\n\n-- oxmysql (callback via export)\nexports.oxmysql:query(\n  'SELECT * FROM users WHERE identifier = ?',\n  { identifier },\n  function(rows) ... end\n)\n\n-- oxmysql (await)\nlocal rows = MySQL.query.await(\n  'SELECT * FROM users WHERE identifier = ?',\n  { identifier }\n)\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>SELECT single row<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- mysql-async (fetchAll + rows[1])\n\n-- oxmysql\nlocal row = MySQL.single.await(\n  'SELECT * FROM users WHERE identifier = ?',\n  { identifier }\n)\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>SELECT scalar<\/strong> (e.g., count, id)<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- mysql-async\n\n-- oxmysql\nlocal count = MySQL.scalar.await(\n  'SELECT COUNT(*) FROM owned_vehicles WHERE owner = ?',\n  { owner }\n)\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>INSERT<\/strong> (get insertId)<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- mysql-async (execute)\n\n-- oxmysql\nlocal insertId = MySQL.insert.await(\n  'INSERT INTO notes (owner, text) VALUES (?, ?)',\n  { cid, text }\n)\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>UPDATE\/DELETE<\/strong> (affectedRows)<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- mysql-async (execute)\n\n-- oxmysql\nlocal changed = MySQL.update.await(\n  'UPDATE users SET job = ?, job_grade = ? WHERE identifier = ?',\n  { job, grade, identifier }\n)\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Transactions<\/strong> (manual)<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- oxmysql manual transaction\nMySQL.query.await('START TRANSACTION')\nlocal ok = true\n\nlocal r1 = MySQL.update.await('UPDATE users SET bank = bank - ? WHERE identifier = ? AND bank &gt;= ?', { amount, fromId, amount })\nlocal r2 = MySQL.update.await('UPDATE users SET bank = bank + ? WHERE identifier = ?', { amount, toId })\n\nif r1 == 1 and r2 == 1 then\n  MySQL.query.await('COMMIT')\nelse\n  MySQL.query.await('ROLLBACK')\nend\n<\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Some frameworks expose wrappers (e.g., <code>ox_lib<\/code>) that add <code>MySQL.ready<\/code>, <code>.transaction<\/code>, etc. The calls above are safe without extra wrappers.<\/p>\n<\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\">4) Prepared Statements Cheat\u2011Sheet<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Param styles<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>mysql\u2011async (legacy):<\/strong> <code>@name<\/code> placeholders with a table: <code>{ ['@name']=value }<\/code><\/li>\n\n\n\n<li><strong>oxmysql (positional):<\/strong> <code>?<\/code> placeholders with an <strong>array<\/strong>: <code>{ value1, value2 }<\/code><\/li>\n\n\n\n<li><strong>oxmysql (named):<\/strong> <code>:name<\/code> placeholders with an <strong>object<\/strong>: <code>{ name = value }<\/code><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Examples<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Named params (recommended for readability)\nlocal row = MySQL.single.await(\n  'SELECT * FROM users WHERE identifier = :id',\n  { id = identifier }\n)\n\n-- IN (...) list\n-- Build placeholders dynamically and pass a flat array\nlocal ids = { 'cid1','cid2','cid3' }\nlocal qs = ('?,' ):rep(#ids):sub(1,-2) -- \"?, ?, ?\"\nlocal rows = MySQL.query.await('SELECT * FROM players WHERE citizenid IN ('..qs..')', ids)\n\n-- JSON fields (MySQL 5.7+\/MariaDB 10.2+)\nlocal name = MySQL.scalar.await('SELECT JSON_UNQUOTE(JSON_EXTRACT(data, \"$.name\")) FROM players WHERE citizenid = ?', { cid })\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Do<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use <strong>prepared statements<\/strong> everywhere (never string\u2011concatenate user input).<\/li>\n\n\n\n<li>Prefer <strong>named params<\/strong> for clarity in complex statements.<\/li>\n\n\n\n<li>Add <strong>LIMIT 1<\/strong> when reading a single entity.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Avoid<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Wildcard <code>SELECT *<\/code> in hot paths (project needed columns).<\/li>\n\n\n\n<li>Per\u2011row N+1 queries; batch with <code>IN (...)<\/code>.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">5) Database \u201cUP\u201d Migration Scripts (Ready\u2011to\u2011Run)<\/h2>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Choose the blocks that match your framework (ESX\/QBCore) and server (MySQL 8+ or MariaDB 10.4+). Run on staging first.<\/p>\n<\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\">5.1 Normalize Charset &amp; Collation (UTF\u20118 everywhere)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(A) MySQL 8+<\/strong> \u2014 replace <code>yourdb<\/code> once<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Force database default to utf8mb4 (emoji\u2011safe)\nALTER DATABASE `yourdb` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;\n\n-- Convert common tables (extend list as needed)\nALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nALTER TABLE `owned_vehicles` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nALTER TABLE `players` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nALTER TABLE `player_vehicles` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(B) MariaDB 10.4+<\/strong> \u2014 same statements are valid.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Add other hot tables (inventory, billing, phone, society, jobs) as present in your server.<\/p>\n<\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\">5.2 ESX Indexes (safe performance wins)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>MySQL 8+<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ALTER TABLE `users`\n  ADD INDEX IF NOT EXISTS `idx_users_identifier` (`identifier`),\n  ADD INDEX IF NOT EXISTS `idx_users_job` (`job`),\n  ADD INDEX IF NOT EXISTS `idx_users_name` (`name`);\n\nALTER TABLE `owned_vehicles`\n  ADD UNIQUE INDEX IF NOT EXISTS `ux_owned_vehicles_plate` (`plate`),\n  ADD INDEX IF NOT EXISTS `idx_owned_vehicles_owner` (`owner`);\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>MariaDB 10.4+<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Drop first to be idempotent where IF NOT EXISTS is unavailable\nDROP INDEX IF EXISTS `idx_users_identifier` ON `users`;\nDROP INDEX IF EXISTS `idx_users_job` ON `users`;\nDROP INDEX IF EXISTS `idx_users_name` ON `users`;\nCREATE INDEX `idx_users_identifier` ON `users` (`identifier`);\nCREATE INDEX `idx_users_job` ON `users` (`job`);\nCREATE INDEX `idx_users_name` ON `users` (`name`);\n\nDROP INDEX IF EXISTS `ux_owned_vehicles_plate` ON `owned_vehicles`;\nDROP INDEX IF EXISTS `idx_owned_vehicles_owner` ON `owned_vehicles`;\nCREATE UNIQUE INDEX `ux_owned_vehicles_plate` ON `owned_vehicles` (`plate`);\nCREATE INDEX `idx_owned_vehicles_owner` ON `owned_vehicles` (`owner`);\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">5.3 QBCore\/QBOX Indexes<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>MySQL 8+<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ALTER TABLE `players`\n  ADD UNIQUE INDEX IF NOT EXISTS `ux_players_citizenid` (`citizenid`),\n  ADD INDEX IF NOT EXISTS `idx_players_license` (`license`),\n  ADD INDEX IF NOT EXISTS `idx_players_steam` (`steam`),\n  ADD INDEX IF NOT EXISTS `idx_players_last_name` (`lastname`);\n\nALTER TABLE `player_vehicles`\n  ADD UNIQUE INDEX IF NOT EXISTS `ux_player_vehicles_plate` (`plate`),\n  ADD INDEX IF NOT EXISTS `idx_player_vehicles_citizenid` (`citizenid`);\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>MariaDB 10.4+<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">DROP INDEX IF EXISTS `ux_players_citizenid` ON `players`;\nDROP INDEX IF EXISTS `idx_players_license` ON `players`;\nDROP INDEX IF EXISTS `idx_players_steam` ON `players`;\nDROP INDEX IF EXISTS `idx_players_last_name` ON `players`;\nCREATE UNIQUE INDEX `ux_players_citizenid` ON `players` (`citizenid`);\nCREATE INDEX `idx_players_license` ON `players` (`license`);\nCREATE INDEX `idx_players_steam` ON `players` (`steam`);\nCREATE INDEX `idx_players_last_name` ON `players` (`lastname`);\n\nDROP INDEX IF EXISTS `ux_player_vehicles_plate` ON `player_vehicles`;\nDROP INDEX IF EXISTS `idx_player_vehicles_citizenid` ON `player_vehicles`;\nCREATE UNIQUE INDEX `ux_player_vehicles_plate` ON `player_vehicles` (`plate`);\nCREATE INDEX `idx_player_vehicles_citizenid` ON `player_vehicles` (`citizenid`);\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">5.4 Optional: ox_inventory (if installed)<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ALTER TABLE `ox_inventory`\n  ADD INDEX IF NOT EXISTS `idx_inv_owner` (`owner`),\n  ADD INDEX IF NOT EXISTS `idx_inv_type` (`type`);\n\nALTER TABLE `ox_inventory_items`\n  ADD INDEX IF NOT EXISTS `idx_items_inv_owner_name` (`inventory`, `owner`, `name`);\n<\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Adjust table names if your schema differs (some setups use <code>inventories<\/code> \/ <code>items<\/code>).<\/p>\n<\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\">6) Rollback Plan (Zero\u2011Panic)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">6.1 Code rollback<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Revert your resource changes (keep a <code>legacy-mysql-async<\/code> branch).<\/li>\n\n\n\n<li>In <code>server.cfg<\/code> swap: <code># ensure oxmysql ensure mysql-async<\/code><\/li>\n\n\n\n<li>Restart FXServer or the affected resources in dependency order.<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">6.2 SQL rollback<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If you only <strong>added indexes<\/strong>: drop them (see the <strong>MariaDB<\/strong> blocks above \u2014 use <code>DROP INDEX IF EXISTS<\/code>).<\/li>\n\n\n\n<li>If you <strong>changed charset\/collation<\/strong> and must undo, revert the DB and tables:<\/li>\n<\/ul>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ALTER DATABASE `yourdb` CHARACTER SET = utf8 COLLATE = utf8_general_ci;\nALTER TABLE `users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;\nALTER TABLE `owned_vehicles` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;\nALTER TABLE `players` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;\nALTER TABLE `player_vehicles` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;\n<\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Prefer restoring from <strong>backup.sql<\/strong> instead of mass charset reversals when possible.<\/p>\n<\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\">7) End\u2011to\u2011End Migration Procedure (Scriptable)<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Freeze deploys<\/strong>, back up DB.<\/li>\n\n\n\n<li>Apply <strong>Section 5 \u201cUP\u201d SQL<\/strong> on staging \u2192 verify \u2192 prod.<\/li>\n\n\n\n<li>Commit code refactor: replace calls (Section 3) + parameter styles (Section 4).<\/li>\n\n\n\n<li>Deploy, <code>ensure oxmysql<\/code>, restart server.<\/li>\n\n\n\n<li>Run <strong>smoke tests<\/strong> (login, paychecks, inventory, vehicle spawn\/despawn, bans, society money, job duty toggles).<\/li>\n\n\n\n<li>Watch logs for 15\u201330 minutes (<code>mysql_slow_query_warning<\/code> helps); address any missed params or schema mismatches.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">8) Micro\u2011Benchmarks (Bring Your Own Numbers)<\/h2>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">A tiny resource you can drop in to compare hot\u2011path queries on <em>your<\/em> hardware and dataset.<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>fxmanifest.lua<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">fx_version 'cerulean'\ngame 'gta5'\nserver_script 'bench.lua'\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>bench.lua<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">local COUNT = 2000  -- adjust for your server\n\nlocal function bench(name, fn)\n  local t0 = os.clock()\n  local ok, err = pcall(fn)\n  local dt = (os.clock() - t0) * 1000.0\n  print(('[bench] %s: %.2f ms %s'):format(name, dt, ok and '' or ('ERR: '..tostring(err))))\nend\n\n-- Hot path 1: ownership lookup\nbench('SELECT single', function()\n  for i=1,COUNT do\n    local row = MySQL.single.await('SELECT owner FROM owned_vehicles WHERE plate = :p LIMIT 1', { p = ('TEST%04d'):format(i % 500) })\n  end\nend)\n\n-- Hot path 2: batched fetch\nbench('SELECT batch IN', function()\n  local ids = {}\n  for i=1,100 do ids[#ids+1] = ('cid%04d'):format(i) end\n  local qs = ('?,' ):rep(#ids):sub(1,-2)\n  local rows = MySQL.query.await('SELECT citizenid, firstname, lastname FROM players WHERE citizenid IN ('..qs..')', ids)\nend)\n\n-- Hot path 3: update with guard\nbench('UPDATE guarded', function()\n  for i=1,COUNT do\n    local changed = MySQL.update.await('UPDATE users SET bank = bank + :d WHERE identifier = :id AND bank &gt;= 0', { d = 1, id = ('lic:%04d'):format(i % 500) })\n  end\nend)\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>How to run<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Put the resource in a folder (e.g., <code>ox-bench\/<\/code>), add <code>ensure ox-bench<\/code> to <code>server.cfg<\/code>.<\/li>\n\n\n\n<li>Tail server console; results print as lines like: <code>[bench] SELECT single: 134.21 ms<\/code>.<\/li>\n\n\n\n<li>For a <strong>before\/after<\/strong> comparison, run once with <code>mysql-async<\/code> (adjust the calls if necessary), then with <code>oxmysql<\/code>.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What to look for<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Lower total ms per section after migration.<\/li>\n\n\n\n<li>Lower P95\/P99 latency on gameplay actions tied to queries.<\/li>\n\n\n\n<li>Fewer slow\u2011query warnings over an hour of live play.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">9) Troubleshooting<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: I get \u201cno such export: query\/single\/\u2026\u201d.<\/strong><br>A: <code>oxmysql<\/code> isn\u2019t started early enough. Ensure <code>ensure oxmysql<\/code> is above resources that use it.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: Parameter errors or empty results.<\/strong><br>A: You likely kept <code>@param<\/code> placeholders. Replace with <code>?<\/code> or <code>:name<\/code> and pass an array\/object accordingly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: Deadlocks or partial writes.<\/strong><br>A: Wrap multi\u2011step balances\/transfers in a transaction (see Section 3), add indexes from Section 5.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: JSON path returns NULL.<\/strong><br>A: Confirm your engine supports JSON functions (MySQL \u22655.7\/MariaDB \u226510.2) and that the column type is <code>JSON<\/code> (not <code>LONGTEXT<\/code>).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: Slow after migration.<\/strong><br>A: Check missing indexes, <code>EXPLAIN<\/code> your query, project only needed columns, and review the Server Optimization Playbook.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">10) Code Review Checklist (copy\/paste)<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>No string\u2011concatenated SQL; all queries parameterized.<\/li>\n\n\n\n<li>Use <code>.single<\/code>\/<code>.scalar<\/code> with <code>LIMIT 1<\/code> when only one row\/value is required.<\/li>\n\n\n\n<li>Batch <code>IN (...)<\/code> reads for collections.<\/li>\n\n\n\n<li>Transactions around multi\u2011step money\/inventory ops.<\/li>\n\n\n\n<li>Index present for every hot <code>WHERE<\/code>\/<code>JOIN<\/code> column.<\/li>\n\n\n\n<li>Avoid <code>SELECT *<\/code> in hot paths.<\/li>\n\n\n\n<li>Log slow queries; track top offenders weekly.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h3 class=\"wp-block-heading\">Internal Links<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>FiveM Server Optimization: The Definitive 2025 Playbook<\/strong> \u2014 <a href=\"https:\/\/fivemx.com\/fivem-server-optimization\/\">https:\/\/fivemx.com\/fivem-server-optimization\/<\/a><\/li>\n\n\n\n<li><strong>Adapter Patterns: ESX\u2194QBCore\u2194QBOX Exports, Events &amp; Player Models<\/strong> \u2014 <a href=\"https:\/\/fivemx.com\/adapter-patterns\/\">https:\/\/fivemx.com\/adapter-patterns\/<\/a><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Credits<\/strong><br>Maintained by fivemx.com. Contributions welcome (send diffs of additional safe indexes or wrapper helpers).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Audience: FiveM server owners, scripters, maintainersGoal: Replace mysql-async with oxmysql safely, speed up queries, and modernize your SQL usage. TL;DR 1) Pre\u2011flight Safety Checklist 2) Install &amp; Wire Up oxmysql 2.1 server.cfg Keep mysql-async disabled but available in your resources folder during the staging phase (for rapid rollback). 3) API Mapping: mysql\u2011async \u2192 oxmysql mysql-async [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":193034,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2881],"tags":[],"class_list":["post-193033","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-performance"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/posts\/193033","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/comments?post=193033"}],"version-history":[{"count":0,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/posts\/193033\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/media\/193034"}],"wp:attachment":[{"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/media?parent=193033"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/categories?post=193033"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/tags?post=193033"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}