Comment on Why would a UTF-8 MySQL backup contain invalid UTF-8 characters?
adespoton@lemmy.ca 1 month agoThat’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
folekaule@lemmy.world 1 month ago
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.