So I've been using this database for almost two decades now (first entry says 2001). The last few years my clients are asked by the webshop what their name is, they enter something and then this is put into the database as their name. Now when that name has to be printed we've written the software to put their name in the right spot by merging it into a ps template. Of course we're getting weird characters every now and then so now we've made a table that will change ä into whatever makes ä come out in postscript. Works! So last week I upgraded to ubuntu 20.04, and now someone has put some funny characters in his name (Not his fault.... his parents!). But the database refuses to accept the name with the funny characters. Turns out the encoding is still set to latin1 or something like that. When I try to set the encoding to "utf8" it seems some conversion is going to happen, but most of the data is already in utf8. Someone here on stackexchange suggested to first convert to binary and then convert to utf8. Nice idea, but then when I tell it to change to utf8, it tells me that there is an error on row 410 and... no change: it keeps saying it is binary data.
With 4000+ entries in this single table, I'm NOT looking forward to manually reviewing and modifying 10000+ records in my database. How can I proceed with the database that has been working for almost two decades?
I cannot throw everything I have away and start over. I cannot manually review all the error records. I cannot tolerate that the data stays marked as "binary", because the software was written "in-house" some maintenance tasks don't have the pretty programmed gui, but are done in phpmyadmin. OK. I've solved the "display" part. I can mark the column to be displayed as text in phpmyadmin but then I can't edit the data in phpadmin anymore like I used to be able to.
Personally, I like the days when I could just put a text into a database, and the database would not interfere and just store the data. Today I spent 4 hours fighting some new consistency check that IMHO does not help at all. In this case, the client has already left and then suddenly behind the scenes his order does not get put into the database. Great fun!
