Updating from 1.4.25 (stable) bridged to SMF

[Nightmaster]

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.

[Αndré]

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.

[Nightmaster]

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.

[Αndré]

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.

[Nightmaster]

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.

[gmc]

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.

[Dion]

Perhaps the answer is in the MySQL documentation: http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html

[Αndré]

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:

Code Select Expand

$CONFIG['dbcharset'] = 'utf8';

Code Select Expand

    if (!empty($CONFIG['dbcharset'])) {
        cpg_db_query("SET NAMES '{$CONFIG['dbcharset']}'", $result);
    }

[Nightmaster]

Code Select Expand

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? 

[Nightmaster]

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.

[Αndré]

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.

[gmc]

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)

Code Select Expand


Здесь хранятся только удаленные сообщения и темы!...


and cpg tables looking like: (categories)

Code Select Expand


сдесь можно размесÑ,иÑ,ÑŒ Ñ,,оÑ,Ð...


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

Code Select Expand


8: Undefined variable: screen_res<br />Файл: /home...


[Αndré]

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 Datensätze in einer Tabelle haben:
ID Name
1 Zappa, Frank
2 Zäppel, Ernst
3 Zander, Bernd

und uns die Liste der Datensätze mittels 'SELECT * FROM `tabelle` ORDER BY `Name`' zurückgeben lassen.

Rückgabe bei Sortierung (Collation) latin1_german1_ci (Wörterbuchsortierung - Umlaute werden alphabetisch einsortiert):
3 Zander, Bernd
1 Zappa, Frank
2 Zäppel, Ernst

Rückgabe bei Sortierung (Collation) latin1_german2_ci (Telefonbuchsortierung - Umlaute stehen oben):
2 Zäppel, 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 kümmern.

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.

[Nightmaster]

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

[Nightmaster]

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. 

[gmc]

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

Code Select Expand


                $this->connect($db_connection);

change to

Code Select Expand


                $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..

[Nightmaster]

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.

[gmc]

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.

Code Select Expand


            // Connect to db - or supply a connection id to be used instead of making own connection.
            $this->connect();

replace with:

Code Select Expand


            // 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...

[Nightmaster]

Okay, done, still no changes at all. :/

[gmc]

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