Replacing the Programs is not the problem. The problem would be that I then have inconsistent data because there are many of them doing different tasks. I would have to switch all of them including the data in a split-second.
... And have i mentioned the database is BIG.
I cannot shut down the programms for long because the gathered data is very short lived, that is the reason why I started the project in the first place.The end-goal is a database correcty encoded, but I need a mid-term solution.
It's gonna be a step by step solution.
I don't know how complex (not big) your database is or how complex your programs are.
Depending if (and how long) your database could be offline I suggest two approaches.
I.) If database could be offline for some time then you would prepare, test and distribute new version of programs which correctly work with UTF-8. After that you put
database offline (to this programs) and correct the encoding the data in database. After that you start using new programs. You should ensure that old programs aren't able to access database.
II.) If you can't take database offline then things get much more complicated.
1.) Add column named 'version' or similar to your tables and set it to be default '1'.
2.) Change your program to be able to decode "bad" UTF-8 and "good" UTF-8 depending of 'version' value in row you are reading ("bad" UTF-8 is version 1, "good" UTF-8 is version 2).
3.) Start updating your programs. Programs still should use "bad" UTF-8 for writing, but they should be able to read both versions. After all programs are updated with they can read both versions of table rows.
4.) You can start switching programs to use version '2' for writing into the database.
5.) After all programs writing "good" UTF-8 to database (and can read both versions) you start to correct encoding of strings in database (and update 'version' value to 2).
6.) When you are done with that you deploy new version of program where it doesn't need to take care of row 'version' value - only uses correct encoding.
7.) Remove 'version' columns from your tables.
While I think number II. is doable, number I. is definitely much easier to accomplish.