search

alliedmodders / sourcemod

SourceMod - Source Engine Scripting and Administration
http://www.sourcemod.net/
975 stars 422 forks source link

dbi.mysql incompatability #1150

Closed mgeerse closed 4 years ago

mgeerse commented 4 years ago

Help us help you

Environment

Description

Hello, I've first asked the plugin maker about this issue. He has referred me to here instead, saying that it's a problem related to the incompatibility of my database with dbi.mysql sourcemod driver.

The problem is that a player with a special name, such as πŒπŽπ‘π†π€πƒπŽ or 𝓼𝔁𝓡𝓽𝔂 cannot be stored in a database.

Problematic Code (or Steps to Reproduce)

Database db;
char query[256];
Format(query, sizeof(buffer), "INSERT INTO steam_names(names) VALUES('%s'), GetPlayerName(client));
db.AddQuery(query);
db.Execute();

So in reality, when you want to store a playername into your database that is similar to πŒπŽπ‘π†π€πƒπŽ or 𝓼𝔁𝓡𝓽𝔂, it will not work. The name gets converted to \xF0\x9F\x93\x97^1... which I think represents a utf8mb4 string which is 4 bytes long instead of 3 from utf8.

The new utf8mb4 is introduced in MySQL version 5.5.3, but I'm unsure against which driver SourceMod is build.

Logs

The logs from the plugin looks as follows:

L 12/28/2019 - 00:50:35: Info (map "de_mirage") (file "/home/container/csgo/addons/sourcemod/logs/errors_20191228.log")
L 12/28/2019 - 00:50:35: [levelsranks.smx] SQL_Callback Error (8546): Incorrect string value: '\xF0\x9F\xA4\xA7' for column 'name' at row 1

I hope to have informed you enough.

asherkin commented 4 years ago

SM 1.10 has had support for the utf8mb4 charset for over 2 years.

mgeerse commented 4 years ago

Hello @asherkin, I've spoken in person with the plugin developer and he has confirmed to me once again that the problem lies within the dbi.mysql compatability.

He has given me a dev version that sets the table to utf8mb4, but this also doesn't work. As he claims it, the problem lies with sourcemod. Don't shoot the messenger.

but the same error still persists:

L 12/28/2019 - 19:21:12: SourceMod error session started
L 12/28/2019 - 19:21:12: Info (map "de_inferno") (file "/home/container/csgo/addons/sourcemod/logs/errors_20191228.log")
L 12/28/2019 - 19:21:12: [levelsranks.smx] SQL_Callback Error (9618): Incorrect string value: '\xF0\x9D\x93\xBC\xF0\x9D...' for column 'name' at row 1
peace-maker commented 4 years ago

Are the tables created using the utf8mb4 charset? Check the CREATE TABLE statements.

mgeerse commented 4 years ago

Yes they are. The client, connection and server are all using utf8mb4. The database, table and column are also set to use utf8mb4. So far I have not found a fix

peace-maker commented 4 years ago

Are they set in your live database as well? Sorry, but the problem is on your end. We even use new api only available in the MySQL 5.5 client library, where utf8mb4 was introduced, so accidentally compiling against an older version wouldn't work.

mgeerse commented 4 years ago

Everything that is adjustable I have adjusted to utf8mb4

/etc/mysqld/my.cnf retrieved from https://mathiasbynens.be/notes/mysql-utf8mb4#character-sets:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

Live database:

Field Type Collation Null Key Default Extra Privileges Comment Β 
steam varchar(22) utf8mb4_general_ci NO PRI NULL Β  select,insert,update,references Β 
name varchar(32) utf8mb4_general_ci NO Β  Β  Β  select,insert,update,references
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Β 
lvl_retakes_base InnoDB 10 Dynamic 2356 111 262144 0 0 0 NULL 2019-12-31 01:46:23 2020-01-01 13:13:34 NULL utf8mb4_general_ci NULL Β  Β 
Β  Database Collation Β 
Β  lvl_retakes_base utf8mb4_general_ci

Just hitting a brick wall again and again

peace-maker commented 4 years ago

Maybe your fields are too small and the name gets truncated mid multibyte char. 32 vs. 128.

This issue tracker isn't the right place for such questions though.

mgeerse commented 4 years ago

This issue was originally made because the plugin author asked me to, but I've indeed gone off topic. I've had a lot of help on the SourceMod discord without any luck. Thanks for your time anyway, I really do appreciate it.

crashzk commented 3 years ago

Sorry to revive this topic, but managed to solve @mgeerse, I'm having this problem even following all the information here, nothing to solve.