41
Why would a UTF-8 MySQL backup contain invalid UTF-8 characters?
(lemmy.hogru.ch)
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
this post was submitted on 02 Jan 2025
41 points (95.6% liked)
No Stupid Questions
36278 readers
950 users here now
No such thing. Ask away!
!nostupidquestions is a community dedicated to being helpful and answering each others' questions on various topics.
The rules for posting and commenting, besides the rules defined here for lemmy.world, are as follows:
Rules (interactive)
Rule 1- All posts must be legitimate questions. All post titles must include a question.
All posts must be legitimate questions, and all post titles must include a question. Questions that are joke or trolling questions, memes, song lyrics as title, etc. are not allowed here. See Rule 6 for all exceptions.
Rule 2- Your question subject cannot be illegal or NSFW material.
Your question subject cannot be illegal or NSFW material. You will be warned first, banned second.
Rule 3- Do not seek mental, medical and professional help here.
Do not seek mental, medical and professional help here. Breaking this rule will not get you or your post removed, but it will put you at risk, and possibly in danger.
Rule 4- No self promotion or upvote-farming of any kind.
That's it.
Rule 5- No baiting or sealioning or promoting an agenda.
Questions which, instead of being of an innocuous nature, are specifically intended (based on reports and in the opinion of our crack moderation team) to bait users into ideological wars on charged political topics will be removed and the authors warned - or banned - depending on severity.
Rule 6- Regarding META posts and joke questions.
Provided it is about the community itself, you may post non-question posts using the [META] tag on your post title.
On fridays, you are allowed to post meme and troll questions, on the condition that it's in text format only, and conforms with our other rules. These posts MUST include the [NSQ Friday] tag in their title.
If you post a serious question on friday and are looking only for legitimate answers, then please include the [Serious] tag on your post. Irrelevant replies will then be removed by moderators.
Rule 7- You can't intentionally annoy, mock, or harass other members.
If you intentionally annoy, mock, harass, or discriminate against any individual member, you will be removed.
Likewise, if you are a member, sympathiser or a resemblant of a movement that is known to largely hate, mock, discriminate against, and/or want to take lives of a group of people, and you were provably vocal about your hate, then you will be banned on sight.
Rule 8- All comments should try to stay relevant to their parent content.
Rule 9- Reposts from other platforms are not allowed.
Let everyone have their own content.
Rule 10- Majority of bots aren't allowed to participate here.
Credits
Our breathtaking icon was bestowed upon us by @Cevilia!
The greatest banner of all time: by @TheOneWithTheHair!
founded 2 years ago
MODERATORS
Not sure if this helps you, but for anyone working with utf8 and MySQL, it's worth reading up on the details of their Unicode support. Especially the part where it says that 'utf8' is an alias for 'utf8mb3', which may not be compatible with what other systems consider to be 'utf8'. If you aren't careful with this you will have problems, especially with high code points, like emoji.
Not only are there different character sets that seem like it’s Unicode, but the set in MySQL can change based on the session, the client, the server, the db , the table and the column. All six of them can have different encodings.
Just make sure all are using the same 4 byte Unicode. Different collation is ok when backing up because only important when comparing strings.
That’s… extremely useful to know and highlights the issues I have with databases like MySQL.
IMO, a DB should always have a type defined for a field, and if that type is UTF-8, and it means just the mb3 subset, you should only be able to store mb3 data in it. Not enforcing the field type is what leads to data-based function and security issues. There should also be restrictions on how data is loaded from fields depending on their type, with mb3 allowing for MySQL transform operations and binary requiring a straight read/write, with some process outside the DB itself handling the resulting binary data stream.
/rant
Character encoding and type coercion errors are so common. But a lot of bugs also come from programs trying to do "the right thing". Like in OP's case: they are just trying to import some data and maybe the data was never even intended to be interpreted as utf8, but the tool they are using to remove the commands wants to treat it that way. Sometimes the safest thing to do is to just assume data is binary until you care otherwise.
This is the right answer. I had the job of planning a schema update to fix this shitty design.
Saying that, unicode and character formats are incredibly complex things that are not easily implemented. For example two strings in utf-8 can contain the same number of characters but be hugely different in size (up to 3-4x different!). It's well worth reading through some articles to get a feel of the important points.