Do all the things like ++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatarSign Up
BobbyTables317647dI've spent way too much of my life fighting with MySQL character encodings.
Unfortunately I dont have any answers just want to let you know you are not alone.
AlmondSauce845447dHave you definitely checked you have the correct encoding on all tables and columns, not just globally?
Had a similar issue a while back, and turns out we still had one sneaky column that was stuck on something non-unicode.
@IntrusionCM Yeah, the 𠮟 character in particular seems to account for half the issues.
Dev server is running MariaDB 10.4.11 (which came with the latest version of XAMPP); the server I'm hosting things on is using MySQL 5.5.65.
Interestingly, I went ahead and did a hack where it base64 encoded the information and flagged it if it got this error, and I was able to fix it manually on the server directly using Sequel Pro. The SQL dump to send things over to the remote server converted the offending characters to question marks, but it's letting me fix them there as well without any issues.
Based on that, I'm almost wondering if it's just PHP's MySQLi having issues with it, perhaps?
@Kaji when you tested against mariadb 10.4 it's definitely Unicode 3.1 support.
Did you set the correct client encoding?
$mysqli = new mysqli(...)
To verify, I'd dump the Session variables straight from that connection, eg.:
SHOW SESSION VARIABLES LIKE 'character\_set\_%';
SHOW SESSION VARIABLES LIKE 'collation\_%';
Do not use SET NAMEs... Or stuff like that.
@IntrusionCM Just checked in my config.php file for the project and saw the following:
Think you may have just hit on the missing link there. The variable dumps in the top of the post were gotten using the following:
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
@IntrusionCM Just did a check to be certain, and yeah, it was the set_charset() call in the PHP code. Changed it to $db->set_charset("utf8mb4"); and then ran a query inserting the string '𠮟る叱る' (which should definitely trip things, based on past tests) and it entered it without issue.
Thanks to everyone for the assistance sorting this one out!