"Incorrect string value" errors with MySQL even with utf8mb4_unicode_ci

Question

My database stores server hostnames of Garry's Mod servers (Source Dedicated Servers) and sometimes they seem to contain characters that utf8mb4_unicode_ci can't store. What are these characters and how do I fix the issue?

insert into `servers` (`steamid64`, `script_id`, `ip_address`, `port`, `uses`, `hostname`, `maxplayers`, `map`, `last_active`) values (XXXXXXXXXXXXXXX, 1599, XXXXXXXXXXX, 27095, 1, [FR] USA vs F?d?ration | Apocalypse | SeriousRP | TSF, 20, rp_evocity_dc, CURRENT_TIMESTAMP())

Incorrect string value: '\xE9d\xE9rat...'

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE9d\xE9rat...' for column 'hostname' at row 1 (SQL: insert into `servers` (`steamid64`, `script_id`, `ip_address`, `port`, `uses`, `hostname`, `maxplayers`, `map`, `last_active`) values (XXXXXXXXXXXXXXX, 1599, XXXXXXXXXXXXXXXX, 27095, 1, [FR] USA vs F?d?ration | Apocalypse | SeriousRP | TSF, 20, rp_evocity_dc, CURRENT_TIMESTAMP()))
  File "app/Http/Controllers/PayloadController.php", line 243, in GetPayload
    $Server -> save();
  File "public/index.php", line 55
    $request = Illuminate\Http\Request::capture()

Rick James · Accepted Answer · 2018-11-28 05:16:42Z

1

You were hoping for ...édérat... ?

Your client is using latin1, not utf8mb4.

Perhaps the simplest fix is to tell mysql that you are using latin1 in the client. This is best done in the connection string. (Are you using PDO or mysqli?)

Note: The encoding of the client and the encoding in the database table are independent. The transcoding is automatic. But you must be correct in declaring the encoding in the client.

answered Nov 28, 2018 at 5:16

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Billy Over a year ago

I'm using PDO, via Laravel. It should already be set to utf8mb4. The encoding sent by the client is just sent via a HTTP request from a gameserver running Lua, so I would have no idea what that could be, but I'm fairly sure it should just use UTF-8.

Rick James Over a year ago

@Billy - Hex E9 is not a valid UTF-8 encoding. It is (at least) the latin1 encoding for e-acute. If you can change the html, the form should say something like <form accept-charset="UTF-8">.

Collectives™ on Stack Overflow

"Incorrect string value" errors with MySQL even with utf8mb4_unicode_ci

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related