[MySQL] QBCoreで起きた "Illegal mix of collations ~" エラーの解決方法 #MySQL

まえがき

最近はGTA5 RPサーバのエンジニアとして携わらせていただいてます
そんな自分用メモ書き
あるいは同じ状況で困ってる人向け

エラー文の発生状況

[script:qb-core] SCRIPT ERROR: citizen:/scripting/lua/scheduler.lua:739: SCRIPT ERROR: @ps-mdt/server/main.lua:256: ps-mdt was unable to execute a query!
[script:qb-core] Query: SELECT p.citizenid, p.charinfo, md.pfp, md.fingerprint FROM players p LEFT JOIN mdt_data md on p.citizenid = md.cid WHERE LOWER(CONCAT(JSON_VALUE(p.charinfo, '$.firstname'), ' ', JSON_VALUE(p.charinfo, '$.lastname'))) LIKE ? OR LOWER(charinfo) LIKE ? OR LOWER(citizenid) LIKE ? OR LOWER(md.fingerprint) LIKE ? AND jobtype = ? LIMIT 20
[script:qb-core] ["%admin%","%admin%","%admin%","%admin%","police"]
[script:qb-core] Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

MySQl(MariaDB)環境下
ps-mdtという外部プラグインを導入した時の話
どうやらそのスクリプトが持つ、Profiles検索を行うと出る模様

結論から言うと

MariaDB(MySQL)のテーブル同士の照合順序が異なることが原因

エラー文で指定されている ps-mdt/server/main.lua:256 を確認すると…

local people = MySQL.query.await("SELECT p.citizenid, p.charinfo, md.pfp, md.fingerprint FROM players p LEFT JOIN mdt_data md on p.citizenid = md.cid WHERE LOWER(CONCAT(JSON_VALUE(p.charinfo, '$.firstname'), ' ', JSON_VALUE(p.charinfo, '$.lastname'))) LIKE :query OR LOWER(`charinfo`) LIKE :query OR LOWER(`citizenid`) LIKE :query OR LOWER(md.fingerprint) LIKE :query AND jobtype = :jobtype LIMIT 20", { query = string.lower('%'..sentData..'%'), jobtype = JobType })

となっている
が、しかしデータベースから照合順序を確認してみると、

何故か大量に存在するが、playersテーブルの照合順序

mdt_dataの照合順序

と、画面右側の TABLE_COLLATIONに指定されている照合順序が異なることがわかる

これが原因であるが、既に存在するテーブルの照合順序を書き換えるとバグの原因となりかねないため、SELECT文の方を修正していく

個人的には不必要にSQLを操作してDBをイジるのはオススメしない
ソースコードは戻せても、DBのデータを簡単に戻すことは出来ない

コード修正

単純に照合順序(COLLATION)を合わせればよい

どちらに合わせるかはその時次第
今回はより厳密に検索させたかったので utfmb4_general_ci に統一

修正後 > ps-mdt/server/main.lua:256

local people = MySQL.query.await("SELECT p.citizenid, p.charinfo, md.pfp, md.fingerprint FROM players p LEFT JOIN mdt_data md on p.citizenid COLLATE utf8mb4_general_ci = md.cid WHERE LOWER(CONCAT(JSON_VALUE(p.charinfo, '$.firstname'), ' ', JSON_VALUE(p.charinfo, '$.lastname'))) LIKE :query OR LOWER(`charinfo`) LIKE :query OR LOWER(`citizenid`) LIKE :query OR LOWER(md.fingerprint) LIKE :query AND jobtype = :jobtype LIMIT 20", { query = string.lower('%'..sentData..'%'), jobtype = JobType })

分かりづらいが、LEFT JOIN mdt_data ON players ～ の後ろに
COLLATE utf8mb4_general_ci が挿入されている

これにより、JOINされる2つのテーブルが同じ照合順序になったため、エラーを修正できた

解説

ここから先はスルーOK
あくまで、何故このエラーが発生したか？ を解説する

少しSQLの知識が必要。詳しくは解説しない

照合順序とは

MySQLは 文字コード と ソート順序(照合順序) を持っていて、
文字コードがCharactor Set
ソート順序(照合順序)がCollation
と呼ばれている

そして、文字コードが一致しているが照合順序が異なるという場合に
今回のエラーが発生する

今回はともに" utf8mb4 "という文字コードだが、
Collationは" general_ci " と " unicode_ci " となっていた

general_ci と unicode_ci の違い

WHERE句やJOIN句での検索順に違いが出る

なんのこっちゃって人は、とりあえず検索ヒット数が違うと覚えておくと良いかも

同じSQL文なら、unicode_ciの方が処理が遅い(らしい)

MySQLでの標準は utf8mb4_0900_ai_ci であるが、
大体の日本語対応DBでは utf8mb4_general_ci か utf8mb4_unicode_ciが使われてるはず

(utf8mb4_0900_ai_ciは "="と"≠"を同一視してしまってるとかなんとか)

utf8mb4_general_ci の特徴

・半角大文字と半角小文字　同じと認識
・カタカナとひらがな　　　別物として認識

恐らく大文字小文字しか区別しない模様

utfmb4_unicode_ci の特徴

・半角大文字と半角小文字　同じと認識
・カタカナとひらがな　　　同じと認識
・全角大文字と全角小文字　同じと認識
・全角と半角　　　　　　　同じと認識

unicode_ciの方があいまい検索が可能

以上

[MySQL] QBCoreで起きた "Illegal mix of collations ~" エラーの解決方法