This is not a duplicate of http://drupal.org/node/110789 but I have investigated further with my ISP and a weird situation arised, I need clarifications, because I'm very very worried.
What happened:
my web site has drupal 4.7.9 and mysql 4.0.27
it is hosted at atjeu.com, on a shared hosting plan and has cpanel
The server got seriously hacked so the technicians re-initialized the server and restored the backups. The only difference is that the new cpanel they installed has a newer version of mysql (4.1.21)
My web site did not display accented letters anymore, all garbled.
After three days of struggling, when the ISP just replied "your db is not mysql-4.1 ready", I googled a lot and learnt that mysql-4.0 stores all data as latin1, so --I guess-- drupal is clever enough to convert it on the fly to utf8.
My question is: how to upgrade a working drupal installation from mysql-4.0.x to mysql-4.1 or even 5.x?
Reading here: http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html
you have to take serious precautions before and after upgrade to preserve existing data and to properly convert the character set to utf8.
In any case, the ISP says he doesn't care about such things, because all the procedure with cpanel is automated all the way through, backup and restore, no command line and minimal human intervention.
My guessing is that cpanel restored the backup to the newer mysql-4.1.21 without caring about charset conversion, just dumping data back in.
If my guessing is right, there are serious problems with all drupals stored on mysql-4.0 dbs and cpanel, because things will break up sooner or later, with no way to predict or prevent data loss with the necesary steps.
On the other hand, if my guessing is wrong, what am I missing???
1) what is the safest way to upgrade from mysql-4.0.x?
2) what can I do against my ISP when it will be ready and willing to install the newer mysql-4.1?
I convinced my ISP to move my space to the last server with mysql-4.0.27 (restoring again the backup), and everything works now, but they say they will soon upgrade this older cpanel, meaning it will have mysql-4.1, and the ploblem will rise again.
I am very worried, what can I do now?
Hope you can help me understand... :-(
