forum.coppermine-gallery.net
Support => cpg1.5.x Support => cpg1.5 upgrading => Topic started by: Nightmaster on January 13, 2014, 11:00:39 pm
-
Hello there! :)
I'm currently using 1.4.25 (stable) version of Coppermine, bridged to SMF 1.1.19. It all works great, and I'm having 4893 files in 126 albums and 5 categories with 9575 comments.
Now, I need to upgrade my SMF to latest version 2.0.6, and I'm wondering what should I do with Coppermine in order to keep all files/categories and comments? Do i need to upgrade coppermine as well? Can you please advise me about this and provide some documentation link if there is something that can help me?
I never did upgrade for coppermine so I'm a bit worried if that's too complicated to perform, sorry if my question above is too dumb :)
Regards,
Nikola
-
Coppermine should certainly be upgraded - we are now at 1.5.26...
Here is latest announcement thread with documentation link.
http://forum.coppermine-gallery.net/index.php/topic,76998.0.html (http://forum.coppermine-gallery.net/index.php/topic,76998.0.html)
Please review and see what questions you have...
-
Thanks for your quick answer, I appreciate it.
Okay, I just take a quick look at that, all seams to be okay besides the fact that I'm not sure what about bridge itself, do I need to remove it before upgrade? Do I need to use another bridge for 2.0.6 version of SMF after upgrade?
Regards,
Nikola
-
I wouldn't expect any issues from the bridge - as that just moves user authentication to SMF...
The main areas would be any plugins you are using (downloading V1.5 versions if still needed) and if you have a custom theme - or theme not supported for 1.5...
Always a good idea to upgrade a test gallery first to get comfortable with it - and make sure all works as planned.
-
Hey again.
So as you suggested I firstly wanted to try all this upgrade thing on my localhost (wamp) server.
So i copy database and files of forum + Coppermine, and put it all on my localhost, and fix settings so it work.
Forum is working fine, and coppermine is working I think, but there's some weird errors at the top of pages. Screenshot attached.
I just changed settings for database access inside config.inc file (which is located in includes folder), and i edit URL inside General Settings, is there something else I should change/fix paths or something?
-
Your WAMP server has a 'more aggressive' setting for warnings/notices than your (and most) production servers...
These settings and associated messages are intended for developers - and should not be on or displayed in production.
It is telling you about functions that should be changed to be compatible in the future... These changes are being incorporated into Coppermine as we move forward.
You can either ignore them for purposes of your testing - or adjust your php settings in WAMP
Look for error_reporting in php.ini - the comments in WAMP's php.ini clearly list the possible options and examples of concatenating...
Since WAMP is often used by developers/testers - it is shipped with something like:
error_reporting = E_ALL (may have ~E_NOTICE as well)
Values like:
error_reporting = E_ALL & ~E_NOTICE & ~E_DEPRECATED
error_reporting = E_ALL & ~E_NOTICE & ~E_DEPRECATED & ~E_STRICT
would suppress these messages and more closely represent a production server.
If everything else looks fine (the messages you provided don't affect the operation) - you are good to go... ;D
-
I think (if I can remember) What I did, when I faced the same scenario ;)
1: Backup all files and database(s)
2: Upgrade Coppermine
3: Disconnect Bridge
4: Upgrade SMF 1 to SMF 2
5: Re-bridge using the SMF 2 version of the bridge.
-
While I'm quite inexperienced regarding bridging, I'd swap step 2 and 3. But I'm quite sure there are some advices or threads that deal with the bridging question while upgrading from cpg1.4.x to cpg1.5.x.
-
Hello there again.
So, i just copied all to new server and repair settings inside include/config.inc.php and plus i edited file cpmfetch/cpmfetch_config.php - i edited urls and paths there, and my gallery is working, but it takes few minutes to load a single page. I can see "Waiting for www.myoldurl.com" at the end of browser when opening some page, and that old domain is now turned off untill i fix all on new one. I can't find if there's some more settings to fix in order to remove pulling from old domain?
Also can someone tell me how can i turn the bridge between coppermine and smf off? I should do that before starting upgrade on smf and coppermine right?
Thanks!
-
Take a look in the docs at the bridging section. There are full details on disabling the bridge.
Your speed problem, have you when you moved server had to change any dns settings on your hosting? That can take a couple of days to be fully stable.
-
No actually it's not a DNS problem because I'm having that server and domain for a 2 years now and I was using it just for testing purposes, and now i decided that it's a time to move the whole site there. My old domain is now on working again and new site is working much faster, almost instantly loading pages.
Anyway, will take a look at that bridging section, thanks.
-
One important item to check when you move server like this is the URL of your coppermine gallery folder - http://documentation.coppermine-gallery.net/en/configuration.htm#admin_general_coppermine-url
-
Looks like the problem was with bridge itself as it's connected with old database. Nevermind it, I just uploaded new files in order to update coppermine.
Now, i have to login in order to use update.php - "Authentication needed" - and I have a problem as I used just my account from forum and now it's not working as bridge is disconnected. Can I somehow create new admin account or add it to database or smth?
-
I can see in database that there's just one account with username "admin" which was last used back in 2009, can I somehow update the password for it?
-
Oh, gotcha! :)
Nevermind, sorry for bothering. :)
-
Oh, just when I was thinking that it's all fine...
I just finished update and I got that message at the end of page saying to check file versions or to go to start page. Whatever I click i just got a white page with:
Fatal error :
That's a new :-X :-X
-
Sorry for double-posting, but i couldn't see an option for edit previous post though.
Here's what i'm getting when i turn on debug mode:
While executing query 'SELECT u.ID_MEMBER AS id, u.realName AS username, SHA1(CONCAT(passwd, passwordSalt)) AS password, u.ID_GROUP AS group_id FROM `pavel_forum`.parnat_forum_members AS u LEFT JOIN `pavel_forum`.parnat_forum_membergroups AS g ON u.ID_GROUP=g.ID_GROUP WHERE u.ID_MEMBER='23699'' in bridge/udb_base.inc.php on line 70
mySQL error: Unknown column 'u.realName' in 'field list'
-
So, I'm starting to really like coppermine 8) 8)
I figured out what the problem is - so i turned off the bridge and setup new SMF 2.0 brigde and it all works nice and smooth now! :)
Now, one last problem that I have is regards utf-8 characters after i bridge coppermine with smf. I'm having Russian site, so many usernames consist utf-8 characters, and those are appearing like "????" right now.
Please see image attached for example. How can i fix it? :-\
-
I assume "Character encoding" is already set to utf-8 in the Coppermine config? If so, you could try to add
$CONFIG['dbcharset'] = 'utf8';to include/config.inc.php. If it works as expected, make sure it doesn't break something else.
-
Interesting is that when I add that line, i got description changed to those weird characters, and nothing else, usernames are still just "???".
See attached images for example.
What I noticed is that coppermine tables in phpMyAdmin are encoded in latin1_swedish_ci (collation is set like that I mean), so may that cause the problem? Should I just change collation for those tables to utf8_general_ci, like I have for forum tables, and is it safe to do now when I already have full database with comments and all?
Thanks for your support once again :)
-
It should be fine to adjust it but always make a backup of your database before making changes like this.
-
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?
-
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.
-
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 :(
-
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
-
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 "???".
-
I found the code in functions.inc.php in /include/ directory which is creating members name under the pictures:
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. :(
-
If you are bridged the usernames are being pulled from the bridged app. Are you sure the collation of the SMF tables is correct?
-
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.
-
I just setup an SMF testbed and created a user with a German umlaut (user name "tst"). 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.
-
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.
-
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.
-
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);
-
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
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:
<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).
-
This seems to work as expected, at least in my testbed.
Open bridge/udb_base.inc.php, find
$cache[$uid] = $row['user_name'];and replace with
$cache[$uid] = utf8_encode($row['user_name']);
-
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.
-
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. :(
-
Please post a link to your gallery.
-
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! :)
-
When I browse categories table in phpMyAdmin, I can see weird characters there
Do your SMF tables look the same?
-
No, all looks fine in tables for SMF, here's attached messages table, but as I can see in all SMF tables I can see all characters fine.
Forum and gallery is stored in the same database, they just have a different prefix, just for the record.
-
Just fishing in the muddy waters: please try what happens when you use utf8_decode instead of utf8_encode in bridge/udb_base.inc.php.
-
Again nothing. I'm not sure if I need to clean some cache or something in order to see actual changes, but as for now - no changes at all.
-
Honestly, I've currently no idea what else to test. Your issue could probably solved by exhaustive investigation of your gallery and/or a lot of trial and error.
I'm still puzzled why each character of the user names are displayed as question marks, as I'd expect some more cryptic characters or symbols.
Maybe it works as expected if you find out why your Coppermine tables looks so odd and fix this.
-
Maybe it works as expected if you find out why your Coppermine tables looks so odd and fix this.
Yes, that makes sense, but also the user names are taken from SMF members table, not from coppermine, right, so that's more strange as SMF table have no problems like coppermine with those weird characters in phpMyAdmin.
-
ok.. As I think this through... If coppermine can properly read the tables with the 'unusual' characters - then it won't be able to read the 'normal' ones (smf)...
The key I think is getting the CPG tables to look 'normal' and be properly read by coppermine... Then it should read the smf tables as well.
When you unload/export the tables, does the output look normal?
would you provide the backup of your database taken BEFORE you attempted the conversion to utf8? Add it to a zip file and attach to post. I assume those will contain the create table statements and data...
I'd like to load that into my sandbox for testing.
Also confirm any settings in config.inc.php related to dbcharset.
-
Perhaps the answer is in the MySQL documentation: http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html
-
I haven't read the whole page, but for the record, the optional "dbcharset" setting in include/config.inc.php just adds "SET NAMES" to the connection:
$CONFIG['dbcharset'] = 'utf8';
if (!empty($CONFIG['dbcharset'])) {
cpg_db_query("SET NAMES '{$CONFIG['dbcharset']}'", $result);
}
-
ok.. As I think this through... If coppermine can properly read the tables with the 'unusual' characters - then it won't be able to read the 'normal' ones (smf)...Yeah, that's what I was thinking as well.
As database is too big to be attached, I can provide you a link to it via pm (sending right now).
Config file attached (with snipped database details tho'). I'm not sure if it's normal for that end lines to be commented out? ::)
-
Looks like I'm not allowed to send personal messages... I sent the databse link via pm form on your profile, hope that it's okay.
-
Config file attached (with snipped database details tho'). I'm not sure if it's normal for that end lines to be commented out?
Those lines aren't part of cpg1.5.x, maybe they're a leftover of an earlier version, though they look more like a custom mod, because somebody already had trouble with the encoding. You can savely delete them, additionally the chosen charset is invalid anyway.
-
As database is too big to be attached, I can provide you a link to it via pm (sending right now).
A little too big to attach at 800+MB...
Downloaded and restored to my sandbox - all 3.3million rows.. :)
The download per your email was from before your 1.5.26 upgrade (and obviously pre utf-8 conversion).
A quick look shows forum tables looking like: (boards)
Здесь хранятся только удаленные сообщения и темы!...
and cpg tables looking like: (categories)
сдесь можно разместить фот...
Your cpg tables all restored with collation of latin1_sweedish_ci... Your forum tables are mostly utf8_general_ci with a few using latin1_sweedish_ci.
If this replicates your database successfully - I think the approach is to find a way to extract the cpg data, redefine and reload in a format matching smf - with appropriate settings to read it all.
I can't picture what in 1.5.26 would have changed the behavior - as the conflicting types were obviously there... unless you had some mod in the previous release?? You said 1.5.25 was your previous release?? - the normal release numbers are even (1.5.24, 1.5.26) If you pulled a specific SVN level after 1.5.24 - let me know which one and if that was to address a specific issue you were having...
As a side note... 2.9 million of your rows are in the `parnat_forum_log_errors` table... I would suggest emptying/truncating that table - unless you really have a need for that information... Will leave you with a much more manageable database...
The messages in there are all readable from character set perspective
8: Undefined variable: screen_res<br />Файл: /home...
-
Regarding database collation, there seems to be a widespread misunderstanding according to the developer of the famous MySQLDumper.
Snippet of the German article:
die Collation (Kollation=Sortierreihenfolge) wirkt sich in keinster Weise beim Einspielen von Daten aus.
Lediglich beim Auslesen der Datenbank durch Queries mit "ORDER BY"-Klausel wird so festgelegt, nach welchen Kriterien sortiert werden soll. Siehe offizielle MySQL-Dokumentation: http://dev.mysql.com/doc/refman/5.1/de/charset-we-sets.html
Das war zu theoretisch?
Ok, hier ein Beispiel:
Gehen wir davon aus, dass wir 3 Datenstze in einer Tabelle haben:
ID Name
1 Zappa, Frank
2 Zppel, Ernst
3 Zander, Bernd
und uns die Liste der Datenstze mittels 'SELECT * FROM `tabelle` ORDER BY `Name`' zurckgeben lassen.
Rckgabe bei Sortierung (Collation) latin1_german1_ci (Wrterbuchsortierung - Umlaute werden alphabetisch einsortiert):
3 Zander, Bernd
1 Zappa, Frank
2 Zppel, Ernst
Rckgabe bei Sortierung (Collation) latin1_german2_ci (Telefonbuchsortierung - Umlaute stehen oben):
2 Zppel, Ernst
3 Zander, Bernd
1 Zappa, Frank
Du siehst, dass sich die Daten ber die Collation (Sortierreihenfolge) nach verschiedenen landesspezifischen Sortierkriterien sortieren lassen.
Das hat aber berhaupt gar nichts mit der Zeichensatz- und damit der Umlautproblematik zu tun. Wenn Du in einem Forum also liest, dass man die Collation bei Umlautproblemen ndern sollte, dann kannst Du den Hinweis getrost berlesen. Wink
Das ist alleinige Sache der eingesetzten Software und braucht Dich nicht zu kmmern.
As the most of you probably don't speak or understand German and the English translation seems to lack of that part: he mainly clarifies that the collation is irrelevant in cases such as we currently deal with.
-
gmc, the previous version of coppermine that I was using was 1.4.25 (as topic name indicates), sorry If i wrote different somewhere. ;)
table smf_members (from which coppermine should take usernames) is utf-8 encoded, collations is utf8_general_ci, so it should still work okay even though there's few swedish collations in some forum tables, but those are mostly tables created by smf mods.
parnat_forum_log_errors is clean now, I'm aware of it's previous size but I couldn't empty it before conversion of SMF (i converted from 1.1.x to 2.0.x), so sorry for such huge table.
I can't picture what in 1.5.26 would have changed the behavior - as the conflicting types were obviously there... unless you had some mod in the previous release??
I didn't had mods, but I had the similar problem with SMF bridge back in 2007 as I have now.
The solution to the problem was this:
http://forum.coppermine-gallery.net/index.php/topic,40675.msg192886.html#msg192886
Topic that my admin colleague started then regards that problem is here (if that may be of any help):
http://forum.coppermine-gallery.net/index.php/topic,47872.0.html
-
As the most of you probably don't speak or understand German and the English translation seems to lack of that part: he mainly clarifies that the collation is irrelevant in cases such as we currently deal with.
Possible.Actually I'm quite sure that collation is not a problem, but maybe it's something with the way the specific collation data is used in specific encoded environment. I don't know...
Sorry for bothering you guys once again, but I'm out of clue how to fix this myself. I never had similar problems with any kind of CMS. As I'm part of official SMF team I'm pretty sure that SMF is not a problem here as it's also working fine, but I'm thinking that bridge itself can cause some problems. And weird characters in database as well, but I also have no idea where's that coming from. :-X
-
gmc, the previous version of coppermine that I was using was 1.4.25 (as topic name indicates), sorry If i wrote different somewhere. ;)
You wrote it right... I read it wrong... too many characters flying by... sorry for the confusion..
I didn't had mods, but I had the similar problem with SMF bridge back in 2007 as I have now.
Reviewing the threads you provided - appears you did have a mod to bridge/smf10.inc.php to
I see. Try this quick code change to bridge/smf10.inc.php
find
$this->connect($db_connection);
change to
$this->connect($db_connection);
cpg_db_query("SET NAMES DEFAULT", $this->link_id);
Upgrading to cpg 1.5.26 would have eliminated this change... and in addition upgrading to SMF 2.x changes the bridge code used to bridge/smf20.inc.php.
I would try re-introducing this mod and see your results. The current connect statement doesn't contain an explicit reference to $db_connection - but should still be able to insert the additional line below...
Again - update bridge/smf10.inc.php if using SMF 1.x and bridge/smf20.inc.php if using SMF 2.x...
Let me know your results..
-
Sorry I don't really understand, you mean to try that code change in my smf20.inc.php file?
If so, as there's no
$this->connect($db_connection);
code in that file, i just added the second code to 82 line in smf20.inc.php file, and it shows no changes on site. I'm not sure if there's some specific place in file you want me to add it.
-
Sorry... I tried to say the new code doesn't contain $db_connection variable explicitly...
Assuming SMF 2.x now - update bridge/smf20.inc.php
Find: should be at or near line 112.
// Connect to db - or supply a connection id to be used instead of making own connection.
$this->connect();
replace with:
// Connect to db - or supply a connection id to be used instead of making own connection.
$this->connect();
cpg_db_query("SET NAMES DEFAULT", $this->link_id);
and re-test...
-
Okay, done, still no changes at all. :/
-
OK.. going to take me a little time to do some testing with my copy of the db....
Let me see what I can come up with.
Greg
-
Thank you so much! I'll try to test something out on my localhost copy of gallery as well, hope that this problem can be fixed at the end.
Thanks once again!
-
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.
I took a second look at this and realized something...if the newly-added row shows the same as the other rows, then the character set and/or collation is still messed up in the table. Go to phpMyAdmin, and then to the database where the parnat_imgcategories table resides. Execute the following four statements individually and let us know the results:
SHOW VARIABLES LIKE 'character_set%'
SHOW VARIABLES LIKE 'collation%'
SHOW TABLE STATUS where name = 'parnat_imgcategories'
SHOW FULL COLUMN FROM parnat_imgcategories
For the last two statements, all that's of interest for this discussion is the Collation column. If a collation is listed, it should be a utf-8 variant. (I'll bet they aren't all utf-8 variants.)
-
Looks like you're right.
Here's results attached.
-
I seem to be getting some different results... which tells me (I think) that some MySQL settings are different in my sandbox...
Here is a quick script to try... Unzip and place dbtest.php in your Coppermine root folder - and execute it from browser.
It will connect to your database using your coppermine config file, and run through the character sets defined to mysql - printing a single user record for each... (Some are obviously not right - but just easier to code to go through them all).
I see the difference primarily in the 'signature' field.. but see if any of these make the data appear 'normal' to you...
If one works - note the name above the array print "Processing charset: latin1" and use that value to specify in smf20.inc.php...
So if 'latin1' works - change the update we made earlier to be:
cpg_db_query("SET NAMES latin1", $this->link_id);
If none of them work - just let me know... and I'll look at it more tomorrow...
Good luck...
-
So if 'latin1' works - change the update we made earlier to be:
cpg_db_query("SET NAMES latin1", $this->link_id);
This corresponds to
$CONFIG['dbcharset'] = 'latin1';in include/config.inc.php (to use Coppermine's built-in features instead of some custom mods).
-
This corresponds to
$CONFIG['dbcharset'] = 'latin1';in include/config.inc.php (to use Coppermine's built-in features instead of some custom mods).
Except appears to me that $CONFIG['dbcharset'] applies to only the CPG DB connection in init.inc.php, and not to the bridge connection established in smf20.inc.php.
My thought is similar support is needed in the bridge code.. A 'dbcharset' setting in bridge table, loaded into $BRIDGE, and used in appropriate bridge code just like the $CONFIG version today.
It is possible a different setting is needed for the bridge tables versus CPG'ss own tables.
That is where I was heading... Based on Nibbler' s 2007 suggestion, and what I was seeing in my testing. Depending on the test results. My 'test script' just tests all 40 possible values in one execution to see what works.
-
Good point I haven't thought of yet.
-
Here's the results attached in a text file. I encoded text file as utf-8 without BOM, so it should display results correctly.
-
Here's another interesting test with the same script. I just modified it so it shows info for specific member ID = 531. That member is the problematic on current site - his name is under the first image shown as "???" currently:
http://www.parnat.tv/pictures/
Results with his ID attached.
-
What is confusing me is how SMF can read the user data normally - yet no specification of character set will allow CPG to read it properly??
I can view id '531' via phpMyAdmin and see the real name of 'Исаев Артём' - none of the charset specifications even came close...
I do see some field name differences between your test and when I run it... For example:
id_member vs ID_MEMBER
real_name vs realName
The CPG tables (some of them - including categories and albums certainly) seem to have a different format - yet CPG reads them fine.
Have to think about it some more...
Greg
-
I was thinking, if I modify database entries (usernames) and write it in some other encoding would that help in showing them on coppermine but keep them showing fine on forum as well?
-
Yes, that would work. How many are corrupt?
-
I have no idea really, i guess all with russian utf-8 cyrilic characters.
However, the main problem is that I don't know to which encoding i should put it in, as those characters are shown just fine on forum though.
-
That's the problem with changing the data in the user table... which I expect would 'fix' the display in Coppermine, but then 'break' it in SMF.
Maybe need to look at this from SMF perspective... What is SMF doing to properly read the data??
I don't know if SMF has an equivalent to CPG's implementation of MySQL's dbcharset..
If we understand how it is reading/displaying the data - should be able to duplicate that in the bridge connection.
I'm still puzzled how no available encoding could properly translate the data while SMF can read it just fine...
-
So, i just copied all to new server and repair settings inside include/config.inc.php and plus i edited file cpmfetch/cpmfetch_config.php - i edited urls and paths there, and my gallery is working, but it takes few minutes to load a single page.
I'm backing up a ways in this thread... based on a new experience I ran into assisting another with a CPG upgrade...
His 'new' gallery at 1.5.x was displaying unusual characters for text fields (in categories, albums, pictures) when 'international' characters were used... The 'old' 1.4.x galleries displayed normally...
No setting of DBCHARSET would properly display the data
(sound familliar?)
Further digging found the issue to be related to the host - the galleries displaying normally were pointing to the old ISPs databases - while the improper display was from the new ISP...
In BOTH cases - the data appeared the same via phpMyAdmin - with 'incorrect' characters - just displayed differently in CPG (or separate test scripts) depending on the server connected to.
In this case I wrote/ran a script in the new environment with 2 database connections:
- The normal Coppermine DB connnection to read all pictures (albums, categories) where text fields (title and caption for pictures) where populated
- A second DB connection was made to the old host/database and the matching picture records extracted.
-
Compared the text fields (title and caption for pictures) between the databases
- Updated the new environment where they were different using data from old DB...
worked in both 1.4 and 1.5 galleries... (After of course 'looking first' at what updates would be made...)
This seemed to correct the issue... (over 700 discrepancies found and corrected...)
In phpMyAdmin on the new host, the data now appears 'normal'.
If you still have access to your old server environment - and can connect remotely via MySQL - might be worth a test... I am guessing it was the server move that affected the change in data display...
I have no idea WHAT MySQL setting might cause this... I'll do some digging, but maybe will ring a bell with someone else.
Hope this helps get to a solution for this mystery....
Greg
-
One more interesting resource in the event there isn't another database we can properly read... The title of the article accurately describes where you are... And gives a detailed (very extensive) procedure to convert everything to UTF-8 and repair the damage.
https://www.bluebox.net/insight/blog-article/getting-out-of-mysql-character-set-hell (https://www.bluebox.net/insight/blog-article/getting-out-of-mysql-character-set-hell)
(also explains how you might have gotten there...)
We are well outside of a Coppermine issue, but others may find themselves in this position... A pro-active conversion to UTF-8 (all tables, all settings, all connections) can prevent this from occurring.
CPG has been recommending use of UTF-8...
I do think CPG needs to add support for dbcharset specification in the bridge connection as it has for its own connection.. It won't fix this issue, since the data appears to be truly 'corrupted' (actually double or triple encoded per the article) - it will be needed as people convert databases.
-
I do think CPG needs to add support for dbcharset specification in the bridge connection as it has for its own connection..
Is this even possible in all cases? As far as I know we have some queries which join tables from the Coppermine and the bridged application. Of course this will likely fix issues when we just query the bridged application. But I assume it's not possible when fetching data from both databases.
-
I have access to old server and database.
Database on old server is from 1.4.25 version though, i made a dump of it and restored it in localhost wamp server, then i upgraded coppermine on localhost for test, and upgraded it on new server independently. I got the same results at both, new server and localhost. If you think that its kind of server related issues i can do a upgrade on old server as well to see if i get the same ower there.
-
Is this even possible in all cases? As far as I know we have some queries which join tables from the Coppermine and the bridged application. Of course this will likely fix issues when we just query the bridged application. But I assume it's not possible when fetching data from both databases.
Probably most likely that the 2 reside in the same database...
The documentation hints that the database is shared:
Coppermine can be integrated with the following applications (eg. Coppermine and your bulletin board will share the same user database).
but not stated as a pre-requisite...
And the code supports them being different in bridge/udb_base.inc.php
class core_udb {
function connect($id = '')
{
global $CONFIG;
// Define whether we can join tables or not in SQL queries (same host & same db or user or positive check)
$this->can_join_tables = ($this->db['host'] == $CONFIG['dbserver'] && ($this->db['name'] == $CONFIG['dbname'] || $this->db['user'] == $CONFIG['dbuser'] || mysql_query("SELECT NULL FROM ".$this->usertable." LIMIT 1")));
if ($id){
$this->link_id = $id;
} else {
// Connect to udb database if necessary
if (!$this->can_join_tables) {
$this->link_id = mysql_connect($this->db['host'], $this->db['user'], $this->db['password']);
if (!$this->link_id) {
die("<strong>Coppermine critical error</strong>:<br />Unable to connect to UDB database !<br /><br />MySQL said: <strong>" . mysql_error() . "</strong>");
}
mysql_select_db ($this->db['name'], $this->link_id);
} else {
$this->link_id = 0;
}
}
}
// end function connect
init.inc.php of course drives the main CPG connect:
// Connect to database
$CONFIG['LINK_ID'] = cpg_db_connect();
The bridge files also have a connect coded.. bridge/coppermine.inc.php calls it with already provided linkid:
// Connect to db
$this->connect($CONFIG['LINK_ID']);
Other bridge files (using smf20.inc.php as example) - don't pass the link_id, and let the udb code determine if connection is made...
// Connect to db - or supply a connection id to be used instead of making own connection.
$this->connect();
So... if the bridge and coppermine tables are in the same database, we have a requirement that the same dbcharset parm be usable for both...
If they are in different databases, we should support a different dbcharset setting for the second connection...
-
To avoid editing core files I assume the best place for that parameter again is include/config.inc.php. Can you force an incompatible bridge database, so we can test if the additional parameter (e.g. $CONFIG['dbcharset_bridge']) fixes the issue?
-
I have access to old server and database.
Database on old server is from 1.4.25 version though, i made a dump of it and restored it in localhost wamp server, then i upgraded coppermine on localhost for test, and upgraded it on new server independently. I got the same results at both, new server and localhost. If you think that its kind of server related issues i can do a upgrade on old server as well to see if i get the same ower there.
Before trying the upgrade
- look at any data on the new server added AFTER the move/upgrade - I assume in both phpMyAdmin and CPG this looks correct.
- look at the data on the old server in phpMyAdmin... I am guessing you will see the same 'incorrect' characters we see in the live database...
- verify the data is indeed displaying correctly on the old server in CPG
If these are all true - I don't even think we need to upgrade the old server... just use it's data. None of the relevant fields should have changed sufficiently to matter.
If all looks as stated above on old server - we should be able to 'fix' the current data using that information...
Is remote MySQL access to your old server possible? (not all hosts allow it... mine lets me specifically enable it for selected MySQL users via cPanel - you may need to ask the hosting provider.)
If so - we should be able to do it in a single script run per table... (well 2 - a verification and an execution... after backing everything up)
If not - we will need to run a script on the old server to extract and save the needed data (without using mysqldump) and rebuild on the new server.
I assume the following tables have issues: users, categories, albums, pictures, comments... Any others?
Also assume that all user_id, cid, aid, pid, and msgid's remained the same and data has not been updated (at least not relevant) since the move for already existing entries... (new entries are fine and will not be affected...)
If these assumptions are true... I think the technique I used in prior post will work here... I only coded the script for pictures - so will have to do some coding to accommodate the other tables...
If any of my assumptions are not true, let me know...
(If we can't connect remotely - will need some more coding...)
Let me know your results... I should be able to refine my script/coding this weekend if it looks like it will help you... or can provide as is and you can modify.
-
To avoid editing core files I assume the best place for that parameter again is include/config.inc.php. Can you force an incompatible bridge database, so we can test if the additional parameter (e.g. $CONFIG['dbcharset_bridge']) fixes the issue?
Let me work on saving Nightmaster's data first... then I'll try to create the requested situation in my sandbox next week...
My bridged gallery is all latin1 at the moment (will be converting sooner rather than later now... )...
If I convert the SMF tables to UTF-8 and add some non-latin characters to select fields... then specifying a dbcharset of latin1 for CPG should cause the issue... and allow testing the fix when ready...
Ideally (1.6?) having the specification in the bridge database/$BRIDGE would seem best - prompted for in wizards - but for 1.5 include/config.inc.php seems best/easiest with change to udb_base.inc.php to recognize and honor the setting.
-
I can't remotely access the old database sadly.
I can provide you with the login info for FTP/hosting panel (this is not cPanel but some weird outdated thing because of which i moved to new server) if you'd like, just let me know where I can send you the details.
However, the database backup i have there is the one I already sent you (huge database dump with forum + coppermine 1.4.25).
From what I can see, data in phpMyAdmin looks quite the same on old and new server. I didn't browse through all tables and entries ofc, but from what i saw - it's the same.
-
I can't remotely access the old database sadly.
OK... can we remotely access the NEW db from the OLD server??
My code worked with 1.4 and 1.5 base cpg code for the DB interface... just need to change the logic to update remote instead of local...
I can provide you with the login info for FTP/hosting panel ...
FTP/hosting panel access will help when I have something to test... I'll let you know... (when needed - the envelope under my name to left of the post has my email address... Obviously don't post such connection info here... I just always say that for the benefit of others that may read this... )
Script already has a 'look' versus 'touch' switch - so can give you a listing of what it would change including SQL before it actually makes any DB updates...
From what I can see, data in phpMyAdmin looks quite the same on old and new server. I didn't browse through all tables and entries ofc, but from what i saw - it's the same.
OK... and does display properly still on CPG/forum running there?
If you can take a little time and look through the tables... be sure we cover all (both SMF and CPG) tables/fields where the characters are 'incorrect'... Don;'t need detail on rows affected - just a list of tables/fields where you see incorrect data. Only text fields (char, varchar, text) with user/admin input should be affected... Those columns internally maintained by the code or non-text should all be fine... Check any plugin created tables as well...
Will save me some time getting the code ready.
-
I have a script ready to run...
Assuming remote access is available from old host to new db - I will need (via email):
FTP access credentials to OLD server... (hostname, userid, password)
The coppermine root folder name/path on OLD server
Your include/config.inc.php file contents from NEW server
The correct setting for NEW MySQL server host and port for remote connection... (assuming your config uses localhost...)
Something like: Host: mysql.servername.com Port: 3306
If I need to use a different username/password than your config file shows - please provide...
Please run a backup on the NEW server of your 1.5.x CPG and related SMF tables...
I will provide you with the output of compares and SQL to be processed for you to review before making any updates.
I expect the compares to show 'incorrect' characters from the NEW server, and matching row correct characters from OLD server - and then SQL to make the NEW row match the OLD...
Any data in NEW for which there isn't a match in OLD will be bypassed (left unchanged) These should be newly added rows which I suspect will look 'normal'...
Output will look like:
Processing TABLE_CATEGORIES
...
Id: 20 Target name: Spe??@ Colle%^*s
Source name: Special Collections
Target description: For c^&$*^&ns of p%$$# from a@#s other cat@#$ries - and some un@#ue photos too!
Source description: For collections of photos from across other categories - and some unique photos too!
Id: 20 Update: UPDATE greg_cpg_categories SET `name` = 'Special Collections' , `description` = 'For collections of photos from across other categories - and some unique photos too!' WHERE `cid` = '20' LIMIT 1;
...
Id: 22 Target name: 2010 Event Photos
Target description: Pictures from our 2010 Events - including meetings and flying events.
No match found in source table!
...
TABLE_CATEGORIES Completed 17 rows to be updated - 2 rows matched - 3 rows with no matching row
By default I will process the following tables/fields... Let me know of any others I need to verify based on your review of the tables:
$tablefieldlist = array('TABLE_CATEGORIES' => array('name', 'description'),
'TABLE_ALBUMS' => array('title', 'description', 'keyword'),
'TABLE_PICTURES' => array('title', 'caption', 'keywords'),
'TABLE_COMMENTS' => array('msg_body'),
'TABLE_USERS' => array('user_name'),
'TABLE_USERGROUPS' => array('group_name'),
);
-
Just sent you a mail, please let me know if you need more details.
Thanks once again!
-
OK... after a weekend (today was a holiday here) of testing... I thought I had a way out of this...
Documenting what I've done so far...
I compared the 1.4.25 code from old server to the distribution copy - and identified a mod in init.inc.php that issued SET NAMES latin1... The equivalent to having $CONFIG[dbcharset] = 'latin1' in CPG 1.5... Nightmaster tried this with no change observed...
No other relevant mods appeared in the code.
I wrote a script to run on the old server, and remotely connect to the new server to do some comparisons...
In all cases below - reference to 'Target' is the new server...
For this set of tests - source is the old server...
Compared MySQL variables... Some differences in the default charsets - but since these are only defaults, they are overridden by individual database settings...
character_set_database Target: utf8 - Source: latin1
collation_database Target: utf8_general_ci - Source: latin1_swedish_ci
Compared data:
In CPG tables - while the data viewed in phpMyAdmin looks 'unusual' - both display properly when selected:
Categories Table:
Id: 1 Target name: Личные фото
Source name: Личные фото
Id: 2 Target name: Учителя
Source name: Учителя
Target description: тут хранятся наши любимые наставники
Source description: тут хранятся наши любимые наставники
Id: 3 Target name: Сам Парнат
Source name: Сам Парнат
Target description: Всё остальное о парнате
Source description: Всё остальное о парнате
...
In SMF tables, select users did not display properly from either connection:
(I deleted last names for privacy of those that displayed normally...)
Id: 31 Target member_name: ?????
Source memberName: ?????
Target real_name: ?????
Source realName: ?????
Id: 32 Target member_name: Arol
Source memberName: Arol
Target real_name: Dzianis
Source realName: Dzianis
Id: 33 Target member_name: ???????
Source memberName: ???????
Target real_name: Alesia
Source realName: Alesia
...
Id: 531 Target member_name: off
Source memberName: off
Target real_name: ????? ?????
Source realName: ????? ?????
Using ID 531 from above... I tried accessing it on the Target server using every available charset...
UTF8 and Binary properly viewed the data
Processing utf8 charset
Result:
Array
(
[id_member] => 531
[member_name] => off
[real_name] => Исаев Артём
}
Processing binary charset
Result:
Array
(
[id_member] => 531
[member_name] => off
[real_name] => Исаев Артём
}
This matches the charset of the SMF tables... set to utf8.
I ran the same script on MY server... Target remains the new server... My server is source..
Compared MySQL variables with no significant differences noted...
Accessed remote data using exactly same PHP (my server doesn't have source data - that is only in my localhost sandbox)...
View of same data as above in CPG tables:
Id: 1 Target name: Личные фото
Id: 2 Target name: Учителя
Target description: тут хранятся наши любимые наставники
Id: 3 Target name: Сам Парнат
Target description: Всё остальное о парнате
And view in SMF tables:
Id: 531 Target member_name: off
Target real_name: ????? ?????
OK - so my thought how to get out of this...
Everything needs to be set to utf8... and data must truly be utf8...
Convert all CPG data by 'reading' in latin1 and 'writing' back out in utf8...
Setting $CONFIG['dbcharset'] = 'utf8' in include/config.inc.php...
I expected this should display all data correctly in both CPG and when viewed with phpMyAdmin.
Backed up (again) the categories, albums, and picture tables - ran the conversion... set dbcharset to utf8... removed mod to setnames default in bridge code...
And the names still display wrong and categories/albums display incorrectly too now...
But all looks good in phpMyAdmin...
Renamed backups and un-did utf8 change in config.inc.php... to return site as it was...
Nightmaster -take a look at the tables ending in _convert and see if contents look right to you...
I'm going to sleep on it and see what ideas the morning brings...
Greg
-
One additional test that has me confused...
Using the converted category table... defined as UTF8 charset - and with 'normal' UTF8 characters when viewed with phpMyAdmin...
One physical database/table...
3 servers - one with local connection... 2 with remote connection... and 3 different results??
In all cases issued 'SET NAMES 'utf8' and then selected/displayed a category entry
Localhost (the 'new' server):
Processing utf8 charset
Result:
Array
(
[name] => Личные фото
)
The 'old' server:
Processing utf8 charset
Result:
Array
(
[name] => Личные фото
)
My server:
Processing utf8 charset
Result:
Array
(
[name] => Личные фото
)
Table DDL:
CREATE TABLE `prefix_categories_convert` ( `cid` int(11) NOT NULL auto_increment, `owner_id` int(11) NOT NULL default '0', `name` varchar(255) NOT NULL default '', `description` mediumtext NOT NULL, `pos` int(11) NOT NULL default '0', `parent` int(11) NOT NULL default '0', `thumb` int(11) NOT NULL default '0', `lft` mediumint(8) unsigned NOT NULL default '0', `rgt` mediumint(8) unsigned NOT NULL default '0', `depth` mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (`cid`), KEY `cat_parent` (`parent`), KEY `cat_pos` (`pos`), KEY `cat_owner_id` (`owner_id`), KEY `depth_cid` (`depth`,`cid`), KEY `lft_depth` (`lft`,`depth`) )
ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='Used to store categories'
So what is unique when coming from old server?? Again accessing the same physical database/table...
Ideas welcome...
-
Thanks for the effort once again!
Tables:
parnat_imgalbums_convert - Looks good.
parnat_imgcategories_convert - Looks good.
I asuume you're thinking about new servers tadabase, so i checked there.
Is it possible that there's a problem with template encoding? I mean, I'm really not that much into encoding things, but I guess that even if data is taken from database and it's okay - it could not be shown correctly if the encoding in template files are not utf-8 (or utf-8 without BOM)?
-
It appears to be an issue coming out of the database...
When Coppermine displayed the info incorrectly after the conversion, I ran scripts that just directly accessed the DB and displayed results...
If I can figure out why the results are different depending on the server I'm running the script on (in all cases going to same physical DB) - I think we would have the key to the remaining problem...
-
OK - so my thought how to get out of this...
Everything needs to be set to utf8... and data must truly be utf8...
Convert all CPG data by 'reading' in latin1 and 'writing' back out in utf8...
Setting $CONFIG['dbcharset'] = 'utf8' in include/config.inc.php...
I expected this should display all data correctly in both CPG and when viewed with phpMyAdmin.
You need to use 'binary' as an intermediary or the data will not be converted correctly. This can be done directly from phpMyAdmin (documented here (http://forum.coppermine-gallery.net/index.php/topic,77017.msg372866.html#msg372866)):
UPDATE table SET column = CONVERT(CONVERT(CONVERT(column USING latin1) USING binary) USING utf8);
Note that these queries might take a while if there are more than a couple thousand records to convert. They also assume the table is using the utf8 character set and a utf8-based collation.
-
Thanks Dion,
I've tried 3 different alternatives - including yours - with the same results...
Starting with copies of the table with 'undisplayable' data in phpMyAdmin
- using an external script - read all data as latin1 - then wrote back as utf8. The table appeared correct afterwards in phpMyAdmin. (Since the data was taken out of the database and placed back - I expect the extra binary step isn't needed.)
- Taking the result of above test - exporting it via phpMyAdmin, creating a NEW table with utf8 and reloading data. The table appeared correct afterwards in phpMyAdmin.
- Going back to a copy of the original table and using the convert command you indicated.The table appeared correct afterwards in phpMyAdmin.
But in all cases - I still cannot get the data to display properly when accessed on localhost with either default or utf8 charset... Note the default and utf8 options actually display different looking bad results...
Here are results from last test (three converts taking latin1 thru binary to utf8):
Commands used:
UPDATE parnat_imgcategories_convert SET name = CONVERT(CONVERT(CONVERT(name USING latin1) USING binary) USING utf8);
UPDATE parnat_imgcategories_convert SET description= CONVERT(CONVERT(CONVERT(description USING latin1) USING binary) USING utf8);
(and a variation that combined CONVERT and CAST: CONVERT(CAST(CONVERT(description USING latin1) AS binary) USING utf8); )
In phpMyAdmin:
cid name
1 Личные фото
In script:
Testing Character Sets against table parnat_imgcategories_convert
Processing DEFAULT charset
Result:
Array
(
[name] => ?????? ????
[description] =>
)
Processing utf8 charset
Result:
Array
(
[name] => Личные фото
)
-
I'd be really curious what would happen if you ran the utf8 output through the mbstring mb_convert_encoding() (http://us2.php.net/manual/en/function.mb-convert-encoding.php) function, using a CP1252 -> utf8 or CP1252 -> koi8-r conversion.
-
I'd be really curious what would happen if you ran the utf8 output through the mbstring mb_convert_encoding() (http://us2.php.net/manual/en/function.mb-convert-encoding.php) function, using a CP1252 -> utf8 or CP1252 -> koi8-r conversion.
Here you go...
Run against the 'converted' database:
Testing Character Sets against table: parnat_imgcategories_convert field: name
Processing DEFAULT charset
Result:
Array
(
[name] => ?????? ????
)
Convert: CP1252 -> UTF-8: ?????? ????
Convert: CP1252 -> KOI8-R: ?????? ????
Processing utf8 charset
Result:
Array
(
[name] => Личные фото
)
Convert: CP1252 -> UTF-8: Личные фото
Convert: CP1252 -> KOI8-R: ???????????? ????????
and run against the 'unconverted' database:
Testing Character Sets against table: parnat_imgcategories field: name
Processing DEFAULT charset
Result:
Array
(
[name] => Личные фото
)
Convert: CP1252 -> UTF-8: Личные фото
Convert: CP1252 -> KOI8-R: ???????????? ????????
Processing utf8 charset
Result:
Array
(
[name] => Личные фото
)
Convert: CP1252 -> UTF-8: Ûøчýыõ фþтþ
Convert: CP1252 -> KOI8-R: ??????????????????????????? ??????????????????
Code used in both cases:
// string mb_convert_encoding ( string $str , string $to_encoding [, mixed $from_encoding = mb_internal_encoding() ] )
$string_CP1252_utf8 = mb_convert_encoding($data[$field], 'UTF-8', 'CP1252');
echo "<tr><td>Convert:</td><td>CP1252 -> UTF-8: $string_CP1252_utf8</td></tr>";
$string_CP1252_koi8 = mb_convert_encoding($data[$field], 'KOI8-R', 'CP1252');
echo "<tr><td>Convert:</td><td>CP1252 -> KOI8-R: $string_CP1252_koi8</td></tr>";
-
OK, this makes me wonder about something. In your program, are you using a header() statement to explicitly set the character set to utf8? If not, please try it. If utf8 does nothing, try setting the output character set to koi8-r.
And finally, in your program, try a utf8 -> koi8-r conversion.
You know, this problem may be as simple as the display font being used. If CPG and SMF are using different fonts, try using the same font that is being used in SMF.
-
Added UTF-8 to KOI8-R:
Against 'unconverted':
Testing Character Sets against table: parnat_imgcategories field: name
Processing DEFAULT charset
Result:
Array
(
[name] => Личные фото
)
Convert: CP1252 -> UTF-8: Личные фото
Convert: CP1252 -> KOI8-R: ???????????? ????????
Convert: UTF-8 -> KOI8-R:
Processing utf8 charset
Result:
Array
(
[name] => Личные фото
)
Convert: CP1252 -> UTF-8: Ûøчýыõ фþтþ
Convert: CP1252 -> KOI8-R: ??????????????????????????? ??????????????????
Convert: UTF-8 -> KOI8-R: ???????????? ????????
Against 'converted':
Testing Character Sets against table: parnat_imgcategories_convert field: name
Processing DEFAULT charset
Result:
Array
(
[name] => ?????? ????
)
Convert: CP1252 -> UTF-8: ?????? ????
Convert: CP1252 -> KOI8-R: ?????? ????
Convert: UTF-8 -> KOI8-R: ?????? ????
Processing utf8 charset
Result:
Array
(
[name] => Личные фото
)
Convert: CP1252 -> UTF-8: Личные фото
Convert: CP1252 -> KOI8-R: ???????????? ????????
Convert: UTF-8 -> KOI8-R:
Didn't have header specified charset - but added that... and the results are interesting...
Against 'unconverted' database:
Testing Character Sets against table: parnat_imgcategories field: name
Processing DEFAULT charset
Result:
Array
(
[name] => Личные фото
)
Convert: CP1252 -> UTF-8: ›¸Ñ‡½Ñ‹µ Ñ„¾Ñ‚¾
Convert: CP1252 -> KOI8-R: ??????????????????????????? ??????????????????
Convert: UTF-8 -> KOI8-R: ???????????? ????????
Processing utf8 charset
Result:
Array
(
[name] => Личные фото
)
Convert: CP1252 -> UTF-8: ›¸Ñ‡½Ñ‹µ Ñ„¾Ñ‚¾
Convert: CP1252 -> KOI8-R: ??????????????????????????? ??????????????????
Convert: UTF-8 -> KOI8-R: ???????????? ????????
Against 'converted' database:
Testing Character Sets against table: parnat_imgcategories_convert field: name
Processing DEFAULT charset
Result:
Array
(
[name] => ?????? ????
)
Convert: CP1252 -> UTF-8: ?????? ????
Convert: CP1252 -> KOI8-R: ?????? ????
Convert: UTF-8 -> KOI8-R: ?????? ????
Processing utf8 charset
Result:
Array
(
[name] => Личные фото <=========== We have a winner!!
)
Convert: CP1252 -> UTF-8: Личные фото
Convert: CP1252 -> KOI8-R: ???????????? ????????
Convert: UTF-8 -> KOI8-R: ������ ����
BUT... using these same 'converted' databases with CPG - and specifying $CONFIG['dbcharset'] = 'utf8'... CPG uses the exact header with charset as I added to my script... CPG still doesn't display anything properly...
I've asked Nightmaster to reset the environment to earlier backups we took - and refresh the CPG files to run a clean test... as I found some previous attempts still in place yesterday.. I believe I removed them all - but the reset of CPG files and databases will be sure.
Getting closer I think...
Greg
-
And I'm happy to say.... SUCCESS!!!!
After the restore of DB and refresh of CPG files (the second part was the key I think... some previous attempted fixes were getting in the way.)
Leaving $CONFIG['dbcharset'] out, or setting to 'latin1' properly displayed CPG info, but wouldn't display forum data properly (user).
Setting $CONFIG['dbcharset'] to 'utf8' display forum data properly, but not CPG info.
The CPG tables had already been altered to UTF-8 in the restored DB.
Ran the following SQL:
UPDATE parnat_imgcategories SET name= CONVERT(CAST(CONVERT(description USING latin1) AS binary) USING utf8);
UPDATE parnat_imgcategories SET description= CONVERT(CAST(CONVERT(description USING latin1) AS binary) USING utf8);
UPDATE parnat_imgalbums SET title= CONVERT(CAST(CONVERT(title USING latin1) AS binary) USING utf8);
UPDATE parnat_imgalbums SET description= CONVERT(CAST(CONVERT(description USING latin1) AS binary) USING utf8);
UPDATE parnat_imgpictures SET title= CONVERT(CAST(CONVERT(title USING latin1) AS binary) USING utf8);
UPDATE parnat_imgpictures SET caption= CONVERT(CAST(CONVERT(caption USING latin1) AS binary) USING utf8);
UPDATE parnat_imgpictures SET filename= CONVERT(CAST(CONVERT(filename USING latin1) AS binary) USING utf8);
UPDATE parnat_imgcomments SET msg_body= CONVERT(CAST(CONVERT(msg_body USING latin1) AS binary) USING utf8);
UPDATE parnat_imgconfig SET value= CONVERT(CAST(CONVERT(value USING latin1) AS binary) USING utf8);
(only 2 rows in config affected - gallery description and a user field...)
And each step along the way watched the display get better and better... :)
Final result is at http://parnat.tv/pictures/ (http://parnat.tv/pictures/)
And while it all looks foreign to me... I think it looks great!!
I'll let Nightmaster mark this solved after reviewing the changes and gallery...
(edited to add SQL to update pictures 'filename' column which also contained 'undisplayable' characters...)
(edited to add omitted spaces to SQL)
Greg
-
I can't say how thankful I am for all the help I got here from you guys!
Actually, I think I never got such committed support on any software support forum, nor I think that I will.
Thank you so much once again!
Anyway, after all, is this something can can appear as a problem with other coppermine installations? Is the problem caused by the sql tables or some weird bug between different collations/character-sets in database and files?
Damn, that's why I look forward for SMF being full-utf by default (which will happen from SMF 2.1 version) and I hope that coppermine would consider such thing eventually. :)
Anyway, keep up the great work, coppermine is really a great software and I look forward to use it on even more my sites! :)
-
You're very welcome...
And I'll add my thanks to those that contributed ideas and thoughts that helped my testing/experimenting and connecting the missing dots... and to Nightmaster for all the access that was needed to do so.
I do think the trigger here was your change in hosting providers - and some setting I cannot see affecting charset interpretation. While looking 'strange' in the DB - this worked fine on your old server... And it may be your new server has the correct implementation - as I would have actually expected problems on the old server.
As Αndr indicated - the implementation of $CONFIG['dbcharset'] was initially to address issues where a host had a unique implementation that CPG needed to override (and you had a mod in 1.4.25 doing the same to force 'latin1')
Can this happen to others? absolutely... especially when non-English languages are involved (though even English users - a comment could easily use different characters from users... as well as picture titles, captions, and even filenames as we saw here.)
A change of hosts... an upgrade of server software (MySQL, etc) could trigger a change in behavior.
Certainly a 'warning sign' appears to be if your data doesn't view 'normally' in phpMyAdmin - something unusual is happening in those cases.
Coppermine does recommend (rather strongly) use of UTF8 charset in the doc - though many old galleries (including I have to say some of mine) are still latin1 or other local settings - (but mine won't be for long after seeing two issues the last couple of weeks...)
I don't know if there are specific plans already (1.6 or beyond...) - I'll let others comment on already in place plans... I expect we will be talking about it... but CPG doesn't need to change anything for users to update their databases...
Yours of course are now already done... :)
Greg
-
I can't say how thankful I am for all the help I got here from you guys!
Actually, I think I never got such committed support on any software support forum, nor I think that I will.
Thank you so much once again!
Anyway, after all, is this something can can appear as a problem with other coppermine installations? Is the problem caused by the sql tables or some weird bug between different collations/character-sets in database and files?
Damn, that's why I look forward for SMF being full-utf by default (which will happen from SMF 2.1 version) and I hope that coppermine would consider such thing eventually. :)
Anyway, keep up the great work, coppermine is really a great software and I look forward to use it on even more my sites! :)
I'm glad your database has been fixed!
I realize that the Coppermine dev team is populated with folks from the SMF dev team, but phpBB3 has had full utf8 support since its release in 2007. It's one (of many) reasons why I prefer phpBB to SMF. Which reminds me...I revised the CPG udb_base bridge code so it could support external applications that used database engines other than MySQL, creating a UDB database abstraction layer (DBAL). I rewrote the phpBB3 bridge to support the UDB DBAL, and it would be trivial to rewrite the other bridges. I'll post some information about it later today.
I don't know if there are specific plans already (1.6 or beyond...) - I'll let others comment on already in place plans... I expect we will be talking about it... but CPG doesn't need to change anything for users to update their databases...
Given that the mysql extension has been deprecated in PHP 5.5 and will be removed in PHP 6, one would hope that converting the hard-coded mysql statements in the CPG core to using a DBAL would be a high priority.
-
Oh, sorry for posting here again, just one last thing...
I have custom php code addded on portal which shows latest comments from gallery and latest images as well.
Here's the code I used until now:
<?php
include "../pictures/cpmfetch/cpmfetch.php";
$objCpm = new cpm("../pictures/cpmfetch/cpmfetch_config.php");
$options = array("subtitle" => "
<strong>{{pTitle}}</strong>
{{pCaption}}
<br> from <a href=index.php?action=profile;u={{pOwnerId}}><strong>{{pOwner_name}}</strong></a><br><hr>");
$options2 = array(
//"noimage" => "",
"cellstyle" => "align='center'",
"subtitle" => "
<strong>{{pTitle}}</strong>
<br> <hr>
<strong> {{%A}}: </strong> {{%C}}
<br><hr>"
);
$objCpm->cpm_viewRandomMedia(2,4, $options);
echo'
<br>
<div class="sp_block tborder">
<table class="sp_block">
<tbody><tr>
<td class="sp_block_padding catbg">
<img id="sp_collapse_20" src="http://parnat.com/forum/Themes/SoftWhite1/images/collapse.gif" alt="*">
Последние Коментарии в Галереи </td></tr>
</tbody></table>
<br>';
$objCpm->cpm_viewLastCommentedImages(1,5, $options2);
$objCpm->cpm_close();
?>
Attached current preview of it.
-
CPMFetch opens its own database connection... I'll take a look at its settings/config to see what support is there for dbcharset...
Greg
-
Not sure if you had rerun the CPMFetch install - but I did to be sure it was set to correct database and see if it picked up the $CONFIG parm change...
CPMFetch (at least at the 2.0.0 level you are running doesn't seem to honor the $CONFIG['dbcharset']... (At install, the script copies the dbconfig and many CPG config vars into its own config file - but not that variable...)
I made a temp change to pictures/cpmfetch/cpmfetch_dao.php to issue the same "SET NAMES 'utf8';" as the mainline code does at database connection time...
I'll put together a more permanent fix for CPMFetch 2.0.0 (and check 2.1.1) over the weekend... and post to the CPMFetch thread.
But your comments should appear normal now...
Greg
-
Is it possible to still use cpmfetch functions like {{pOwner_name}}?
Seems like that one is not working, but {{pTitle}}, {{pCaption}} and {{pOwnerId}} are working just fine.
-
Is it possible to still use cpmfetch functions like {{pOwner_name}}?
Seems like that one is not working, but {{pTitle}}, {{pCaption}} and {{pOwnerId}} are working just fine.
http://forum.coppermine-gallery.net/index.php/topic,55415.msg270618.html#msg270618