Updating from 1.4.25 (stable) bridged to SMF

[gmc]

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.

[gmc]

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.

[Nightmaster]

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.

[gmc]

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.

[gmc]

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:

Code Select Expand


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:

Code Select Expand


$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'),
                       );


[Nightmaster]

Just sent you a mail, please let me know if you need more details.
Thanks once again!

[gmc]

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

Code Select Expand


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:

Code Select Expand


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

Code Select Expand


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

Code Select Expand


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:

Code Select Expand


Id: 1 Target name: Личные Ñ,,оÑ,о
Id: 2 Target name: УчиÑ,еля
Target description: Ñ,уÑ, Ñ...раняÑ,ся наши любимые насÑ,авники
Id: 3 Target name: Сам ПарнаÑ,
Target description: Ð'сÑ' осÑ,альное о парнаÑ,е


And view in SMF tables:

Code Select Expand


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

[gmc]

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

Code Select Expand


Processing utf8 charset
Result:

Array
(
    [name] => Личные Ñ,,оÑ,о
)


The 'old' server:

Code Select Expand


Processing utf8 charset
Result:

Array
(
    [name] => Личные фото
)



My server:

Code Select Expand


Processing utf8 charset
Result:

Array
(
    [name] => Личные Ñ,,оÑ,о
)


Table DDL:

Code Select Expand


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

[Nightmaster]

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

[gmc]

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

[Dion]

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

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.

[gmc]

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:

Code Select Expand


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:

Code Select Expand

cid  name
1      Личные фото

In script:

Code Select Expand

Testing Character Sets against table parnat_imgcategories_convert
Processing DEFAULT charset
Result:
Array
(
    [name] => ?????? ????
    [description] =>
)

Processing utf8 charset
Result:
Array
(
    [name] => Личные Ñ,,оÑ,о
)

[Dion]

I'd be really curious what would happen if you ran the utf8 output through the mbstring mb_convert_encoding() function, using a CP1252 -> utf8  or CP1252 -> koi8-r conversion.

[gmc]

I'd be really curious what would happen if you ran the utf8 output through the mbstring mb_convert_encoding() function, using a CP1252 -> utf8  or CP1252 -> koi8-r conversion.

Here you go...
Run against the 'converted' database:

Code Select Expand


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:

Code Select Expand


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:

Code Select Expand


    // 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>";


[Dion]

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.

[gmc]

Added UTF-8 to KOI8-R:

Against 'unconverted':

Code Select Expand


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

Code Select Expand


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:

Code Select Expand


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:

Code Select Expand


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

[gmc]

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:

Code Select Expand


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

[Nightmaster]

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!

[gmc]

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

[Dion]

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.