Updating from 1.4.25 (stable) bridged to SMF

[Nightmaster]

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!

[Dion]

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

[Nightmaster]

Looks like you're right.
Here's results attached.

[gmc]

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:

Code Select Expand


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

[Αndré]

So if 'latin1' works - change the update we made earlier to be:

Code Select Expand


cpg_db_query("SET NAMES latin1", $this->link_id);


This corresponds to

Code Select Expand

$CONFIG['dbcharset'] = 'latin1';
in include/config.inc.php (to use Coppermine's built-in features instead of some custom mods).

[gmc]

This corresponds to

Code Select Expand

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

[Αndré]

Good point I haven't thought of yet.

[Nightmaster]

Here's the results attached in a text file. I encoded text file as utf-8 without BOM, so it should display results correctly.

[Nightmaster]

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.

[gmc]

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

[Nightmaster]

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?

[phill104]

Yes, that would work. How many are corrupt?

[Nightmaster]

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.

[gmc]

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

[gmc]

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

[gmc]

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

[Αndré]

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.

[Nightmaster]

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.

[gmc]

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

Code Select Expand


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:

Code Select Expand

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

Code Select Expand

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

Code Select Expand

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

[Αndré]

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?