Updating from 1.4.25 (stable) bridged to SMF

[phill104]

It should be fine to adjust it but always make a backup of your database before making changes like this.

[Nightmaster]

Just changed collation of database to utf8_unicode_ci, no changes at all, still having the same weird characters on site.
Is there something else I can check or do? Can this be a bridge problem as usernames from forum are showed weird?

[Αndré]

You don't need to adjust the collation, but the encoding (actually you'd need to convert it, not sure if MySQL does that automatically). The main issue is the different encoding of your databases. As Phill said, make a backup of your database and then try to match the encodings of your databases. Unfortunately I cannot give you a more detailed advice, as it's been a while when I dealt with such issues.

[Nightmaster]

Thanks for your answer.

However, I have no idea what else to try as I dont know what else i can try in database but collation change.

Just to mention that before upgrade coppermine worked just fine, even though collation of it in database was sweedish, and forum was (and still is) utf8_unicode_ci.
What I done in order to make it work then was just adding one line in some of php files, cant remwmber which one exactly, but now whatever i tried wont work

[Αndré]

A quick google search returned this result: http://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8

[Nightmaster]

Okay, thanks, done.
Here's what I hot as a response:

ALTER TABLE parnat_imgalbums CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 126 rows affected.

ALTER TABLE parnat_imgbanned CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# MySQL returned an empty result set (i.e. zero rows).

ALTER TABLE parnat_imgbridge CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 27 rows affected.

ALTER TABLE parnat_imgcategories CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 5 rows affected.

ALTER TABLE parnat_imgcategorymap CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# MySQL returned an empty result set (i.e. zero rows).

ALTER TABLE parnat_imgcomments CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 9575 rows affected.

ALTER TABLE parnat_imgconfig CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 207 rows affected.

ALTER TABLE parnat_imgdict CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# MySQL returned an empty result set (i.e. zero rows).

ALTER TABLE parnat_imgecards CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# MySQL returned an empty result set (i.e. zero rows).

ALTER TABLE parnat_imgexif CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# MySQL returned an empty result set (i.e. zero rows).

ALTER TABLE parnat_imgfavpics CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 6 rows affected.

ALTER TABLE parnat_imgfiletypes CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 105 rows affected.

ALTER TABLE parnat_imghit_stats CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 148777 rows affected.

ALTER TABLE parnat_imglanguages CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 68 rows affected.

ALTER TABLE parnat_imgmark_config CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 6 rows affected.

ALTER TABLE parnat_imgmark_users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 2 rows affected.

ALTER TABLE parnat_imgmark_watermark CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 82 rows affected.

ALTER TABLE parnat_imgpictures CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 4893 rows affected.

ALTER TABLE parnat_imgplugins CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 1 row affected.

ALTER TABLE parnat_imgsessions CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 1 row affected.

ALTER TABLE parnat_imgtemp_messages CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# MySQL returned an empty result set (i.e. zero rows).

ALTER TABLE parnat_imgusergroups CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 12 rows affected.

ALTER TABLE parnat_imgusers CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 1 row affected.

ALTER TABLE parnat_imgvotes CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# 1 row affected.

ALTER TABLE parnat_imgvote_stats CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;# MySQL returned an empty result set (i.e. zero rows).

Still no changes on site at all, everything looks the same, and those username chacters are still just question marks "".

[Nightmaster]

I found the code in functions.inc.php in /include/ directory which is creating members name under the pictures:

Code Select Expand


        if ($CONFIG['display_uploader']) {
            if ($row['owner_id']) {
                $caption .= '<span class="thumb_title thumb_title_owner"><a href="profile.php?uid=' . $row['owner_id'] . '">' . $cpg_udb->get_user_name($row['owner_id']) . '</a></span>';
            }
        }

I have no idea if it's possible to try some tweaking there in order to display utf-8 characters normally and not just question marks though.

[phill104]

If you are bridged the usernames are being pulled from the bridged app. Are you sure the collation of the SMF tables is correct?

[Nightmaster]

Yes, collation is utf8_general_ci for all tables. I guess that if there's problems with character set on forum - we would have some problems on forum itself, and everything is working just fine there including profiles and all.

[Αndré]

I just setup an SMF testbed and created a user with a German umlaut (user name "täst"). Coppermine displays it as

t�st

This also happened in earlier versions of cpg1.5.x (tested with cpg1.5.24 and 1.5.20), so is no cpg1.5.26 issue.

It seems to work as expected when I change Coppermine's "Character encoding" setting to "Western", which currently doesn't make any sense to me at all. This change will probably break other things in the gallery. I haven't applied any modification to include/config.inc.php, as it made no difference.

[Nightmaster]

Thanks for your answer Αndré.

I upgraded my gallery to 1.5.26, so it's the latest version.

One more weird thing to me is this. When I browse categories table in phpMyAdmin, I can see weird characters there, but categories are shown just fine on gallery itself.
Please see attached images (first one is in phpMyAdmin, and second how categories are displayed on site).

I'm a bit desperate now as I have no idea how to fix this characters thing. I tried whatever comes to my mind, including even hard-core adding utf-8 characters in template in order to see if it will work fine (as i was thinking that it may be template based problem) but those characters are shown just fine, as well as characters from functions.ini.php file.

[Nightmaster]

Also just a note: I tried western character-set, but it brake everything, even categories are then shown with those weird characters, so I changed it back to utf-8.

[Dion]

Please see attached images (first one is in phpMyAdmin, and second how categories are displayed on site).

I'm a bit desperate now as I have no idea how to fix this characters thing. I tried whatever comes to my mind, including even hard-core adding utf-8 characters in template in order to see if it will work fine (as i was thinking that it may be template based problem) but those characters are shown just fine, as well as characters from functions.ini.php file.

I'm new here but not new to Coppermine or MySQL.

Before reading what I say next, there's something I'd like to see. If you changed the collation on the categories table to utf8_general_ci and its character set to UTF8, please create a new category, and then post images of what that category data looks like in phpMyAdmin and on the screen. If the data in phpMyAdmin looks fine but the screen has a bunch of "question-make-inside-diamond" characters, then what I say next will apply.

I'm astounded that, based on what is being stored in the database, the category information is being displayed properly. Coppermine must have a built-in "convert latin to UTF8" function to deal with the data it stores in the database with incorrect collations and character sets.

You have two choices. Either add a function to the bridge to "break" the username in the same way the stored CPG data is broken, or find the conversion function and comment out everything it does.

If the latter, once you comment out the function, you'll need to run a number of additional SQL statements in phpMyAdmin, of the form:

UPDATE parnat_imgcategories SET name = CONVERT(CONVERT(CONVERT(name USING latin1) USING binary) USING utf8);

[Αndré]

One more weird thing to me is this. When I browse categories table in phpMyAdmin, I can see weird characters there, but categories are shown just fine on gallery itself.

That also occurred at my gallery on a former hosting company, which uses a "wrong" database (connection) charset, that's why we added

Code Select Expand

    if (!empty($CONFIG['dbcharset'])) {
        cpg_db_query("SET NAMES '{$CONFIG['dbcharset']}'", $result);
    }
as an optional setting to fix such issues. Unfortunately it usually break your gallery if you don't convert the already stored data, as the data doesn't match the expected format. However, it currently doesn't affect your gallery, as the data is stored and retrieved the same wrong way.

I tried western character-set, but it brake everything, even categories are then shown with those weird characters, so I changed it back to utf-8.

Of course "Cyrillic" is the more appropriate setting for your gallery. Feel free to test it, though it's recommended to use utf-8.

Coppermine must have a built-in "convert latin to UTF8" function to deal with the data it stores in the database with incorrect collations and character sets.

I'm not aware of such a conversion. But we use this in the HTML header:

Code Select Expand

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

Depending on Coppermine's "Character encoding" setting, the used charset will change (which was also the reason why "Western" displayed the user name correctly in my gallery).

[Αndré]

This seems to work as expected, at least in my testbed.

Open bridge/udb_base.inc.php, find

Code Select Expand

$cache[$uid] = $row['user_name'];
and replace with

Code Select Expand

$cache[$uid] = utf8_encode($row['user_name']);

[Αndré]

Note: the above code change doesn't affect all possible occurrences of the user's name (e.g. it's still wrong at the logout button). If it works for you for the thumbnail description, we can try to fix it also for the other places.

[Nightmaster]

Thanks for your answer again guys

Dion - here's the results attached - in phpMyAdmin there's still the same weird characters as there was in the alst preview. The last row is newly added category. On the site it shows just fine though.

Αndré - tried that change it udb_base.inc.php - no change at all.
Changing chatset to cyrilic also didn't helped - it breaks everything as you can see in third attached image.

[Αndré]

Please post a link to your gallery.

[Nightmaster]

Sure, here's the link for gallery:
http://www.parnat.tv/pictures/
And bridged SMF forum:
http://www.parnat.tv/forum/

The main domain will be .com, but it's not yet added untill this problem is fixed. I also have copy of all of that on my wamp server.
If you want me to send you admin details for gallery or ftp access I can send you a pm with details. Thanks!

[Αndré]

When I browse categories table in phpMyAdmin, I can see weird characters there

Do your SMF tables look the same?