Special character/emoji string message crashes SqlLog logger

[jujaga]

TShock Version 4.3.24, APIv2.1, OTAPI v2.0.0.27

Stack Trace

2017-06-27 11:27:36 - SqlLog: ERROR: SQL Log insert query failed: MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect string value: '\xF0\x9F\x91\x8F.' for column 'Message' at row 1
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at TShockAPI.DB.DbExt.Query(IDbConnection olddb, String query, Object[] args)
   at TShockAPI.SqlLog.Write(String message, TraceLevel level)
2017-06-27 11:27:55 - SqlLog: ERROR: SQL Logging disabled due to errors. Reverting to text logging.
2017-06-27 11:27:55 - SqlLog: ERROR: SQL log failed at: 2017-06-27 18:27:36. Message: Utils: INFO: zeff executed: /me [c/ffoooo:jumps, HOOraY!] good for u! 👏.

Reproducibility

• Running the following in game causes the error: /me [c/ffoooo:jumps, HOOraY!] good for u! 👏
• Logging falls back to textfile afterwards - must reboot server in order to restore database logging again.

Screenshot N/A

[bartico6]

To fix the issue, create the server log table with a default charset utf8mb4 and the collation corresponding to that (so instead of utf8_bin you'd do utf8mb4_bin).

Reason for this crash is because some emojis are 4-bytes long, and by default utf8 in MySQL is 3-bytes long. I ran into this importing usernames from SQLite to MySQL during server maintenance.

It's literally a one-liner.

[QuiCM]

I put my comment in the wrong place. From #1516:

We should update our table creation method though, so that future versions are compatible with emojis. If @bartico6 could set up a small walkthrough we can provide upgrade steps for existing DBs

[hakusaro]

From Quake in Discord:

since I'm in charge of doing this utf8 -> utf8mb4 stuff people should use binary or case insensitive comparison for mysql if I do utf8mb4_bin like you tried in that commit you later scrapped it'll allow duplicate names with varying letter cases aka "quaKe" "QUAKE" and "quake" can exist alongside each other decision?(

[bartico6]

I meant to ask if we should enforce binary or case insensitive comparisons? IIRC binary might be faster, and it was already in @hakusaro's PR before it got scrapped - but that will allow duplicate names with varying casing to be used, which you guys mentioned shouldn't be a thing. I'll need a final decision on whether dupe names are okay from the team before I make any sort of importer/walkthrough.