UTF issue after sql restore

[lebisol]

Hello gurus,
I ran into strange issue so perhaps you can advise...
Here is how I got my self into trouble and I belive step2 is what did it:
1. Performed an sql backup of live site using phpMyAsdmin as ususal.
2. Created a new DB using sqlyog on my local machine... this db was created carelesly accepting "default local" utf settings.Did import with myphpAdmin  into blank db on my local machine.
3. Cleaned up a few dead tables and made the backup
4. Used this back to restore live site.

Now the issue is if I have language set to be "English" strange characters are being displayed eg. "Ã,«"   vs. just "«"
or "â„¢"  vs. "â,,¢".
If I switch forums to any other-utf language (eg. British-English-UTF8) all is well....except that now I am back to "missing tp related text" issue....

From what I saw on smf.org they are talking about setting tables columns utf settings but mine are corret and set to "latin1_swedish_ci" while my sql server is set to "UTF-8 Unicode (utf8)" ...which are default even on a clen smf install....
Anyone had this issue before?
Many thanks!

[Zetan]

Have you tried the Language packs?

[lebisol]

not sure I follow...what language packs?
u mean uploading TPxxxx.php yeah that is there has not changed....

[Zetan]

TP has Language Packs for download.. I'm no expert on Language Packs, but have they become corrupted?
If you remember when TP moved servers recently, the forum characters here displayed similar nonsense.

[lebisol]

hmm...I know it is on database side.
If I change my smf config to point to "smf_clean_install" database all is well...meaning I am using the same TPxxx.php files...

If I try to edit text manually (although I should NOT have to) and change "â„¢"  to "â,,¢" I get errors...and being how this edit is in the display name of user this is one of the errors:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'
File: /home/mysite/public_html/Sources/Subs-Members.php
Line: 899

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 1.1.4, while your database is at version 1.1.1. The above error might possibly go away if you execute the latest version of upgrade.php.

So where is this "utf8_general_ci" coming from?
It is driving me crazy and although I don't have too much content to restore I  would hate to have to wipe it down to tabula rasa...
Thanks Z!

[Zetan]

Sorry, I don't know then. I'm the first to admit that I know little about how the database works.
It's something I need to look into.

[lebisol]

Thanks ZTN,
beats me too...seemed like a straight forward process...I just didn't want to do it on live DB through myphpadmin since it is slow.
Maybe someone else has ideas?

[lebisol]

I was able to "fix" this...I think...I am just not sure of consequences...
-went through Admin-->Forum Maintenance and converted to UTF8
-then uploaded 4 files TPxxx.xxx-utf8.php from TP_097_english-utf8
-left the Default Forum Language to "English"

But still...this was a desparation move, not sure what this really did to TP nor why would I need to upload english-utf8 from old version of TP nor why would I need to convert whole database to to UTF8  :down:

Also why is there a mix of utf8 general and latin1 in default 0.983 install of TP:

smf_tp_articles        3 MyISAM utf8_general_ci 
smf_tp_blocks        9 MyISAM utf8_general_ci 
smf_tp_data        0 MyISAM latin1_swedish_ci
smf_tp_dlmanager        1 MyISAM utf8_general_ci
smf_tp_settings        98 MyISAM utf8_general_ci
smf_tp_shoutbox        0 MyISAM latin1_swedish_ci   
smf_tp_variables        2 MyISAM utf8_general_ci

going 

[lebisol]

Well...rebuilding it would be more fun. This is beyond what I need to know and since it is not smf standalone I can't have it running on utf8.
Thanks for listening!