Update 1.5.44 to 1.6.04 went fine, but special chacacters (umlauts) are wrong

[kriegaex]

I use MySQL and the database and tables already use UTF-8, as far as I can see. It was like that before upgrading and remains so afterwards. I used cpg_installer_stub.php which went just fine, thanks to the developers for that. The only remaining problem is that even though my category and album names, image descriptions etc. are displayed correctly after a MySQL dump to a text file when the file is viewed as UTF-8 and even though phpMyAdmin also displays them correctly when browsing tables directly, they are displayed wrong (as little black parallelograms with a white "?" in the middle, "�") in the Coppermine front-end in my browser. If I edit e.g. a category name and add umlauts in the Coppermine web client, they are stored in a strange series of characters in MySQL, e.g. "ÄÖÜ" becomes "Ã,,ÖÜ" when viewed in the phpMyAdmin table browser.

My assumption is that not MySQL character sets or encoding are the problem but somehow the chain of application layers and conversions in between, but I cannot prove it. I do not even speak PHP and have no idea how to debug this. All I know is that in 1.5.44 right before upgrading to 1.6.04 everything was fine. The databases looked the same in phpMyAdmin and the umlauts were displayed correctly in the front-end. What can I do to fix this?

[ron4mac]

I added some files to a 1.5.46 gallery that had umlauts in the title. I checked the database and the titles were there with he double characters (Ä Ö Ü). The titles displayed okay in the gallery. I then used the installer stub to upgrade the gallery to 1.6.04. The titles still displayed correctly. So I think there is something other than just the upgrade to 1.6.04 involved in the issue.

Your title and description fields are likely set to latin1_swedish_ci. In today's utf8 world, they should probably be set to utf8_general_ci. Making that change, however, is not easy because the database software wants to double translate the fields.

I've used this method with success:

Code Select Expand

drop table if exists temptable;
create table temptable (select * from cpg145_pictures where LENGTH(title) != CHAR_LENGTH(title));
alter table temptable modify temptable.title varchar(255) character set latin1;
alter table temptable modify temptable.title blob;
alter table temptable modify temptable.title varchar(255) character set utf8;
delete from temptable where LENGTH(title) = CHAR_LENGTH(title);
replace into cpg145_pictures (select * from temptable);

This would have to be done for each field in question.

It finds all the db rows where the field in question has multibyte characters. Before setting the field character set to utf8, it has to change the field type to blob. This keeps the database software from doing a double translate. After the character set change, rows with single byte characters are removed and the rest are replaced into the original table.

If you want to try something like this, backup your database first.

[ron4mac]

One other thought...

Is there a 'dbcharset' config item in your include/config.inc.php?