I've never had or seen this issue. I've gone through what info I could find here but it still has not helped. What exactly has gone wrong here? Is there an easy way to fix this? Thanks
cpanel move to new server and now character issues with some foreign users..
Collapse
X
-
Your old server used a Latin character set and your new server uses a UTF8 character set. Hard to see in the screenshots but can still be made out. They should use the same character set.Translations provided by Google.
Wayne Luke
The Rabid Badger - a vBulletin Cloud demonstration site.
vBulletin 5 APIComment
-
Hi Wayne, here is the full size image: http://i.imgur.com/5RSnq.png
I'm just trying to get my head around this so I know which direction to go. Is this as simple as reconfiguring my new servers mysql server, or am I going to need to manually do something to the database? If someone could point me in the right direction that would be great. ThanksComment
-
The best thing would to make sure the character set and collation for the database matches on both servers before importing the database. Not sure you can do that with a cPanel move though.Translations provided by Google.
Wayne Luke
The Rabid Badger - a vBulletin Cloud demonstration site.
vBulletin 5 APIComment
-
Yes, trying to do this after the fact doesn't look pretty but the old server is gone... My english userbase 97% of users is fine, it's just my few foreign lang forums suffering at the moment until I find a fix.
*edit, ok messing with the server does not seem wise. I am trying to contact a specialist to help out with this. I did see one thing:
Code:[SIZE=2]How to change the collation for all tables in a MySQL database to UTF-8?[/SIZE] Changing the collation for all tables in a MySQL database can be time consuming depending on how many tables you have. That's why we recommend using the following PHP script for changing the collation for all tables at a time: [I]<?php $db = mysql_connect('localhost','[B]myuser_mydbuser[/B]','[B]mypassword[/B]'); if(!$db) echo "Cannot connect to the database - incorrect details"; mysql_select_db('myuser_mydbname'); $result=mysql_query('show tables'); while($tables = mysql_fetch_array($result)) { foreach ($tables as $key => $value) { mysql_query("ALTER TABLE $value COLLATE [B]utf8_general_ci[/B]"); }} echo "The collation of your database has been successfully changed!"; ?>[/I] Make sure to substitute in the above script: - [I]myuser_mydbname[/I] with your database name; - [I]myuser_mydbuser[/I] with your mysql username; - [I]mypassword[/I] with your password for the mysql user; - [I]utf8-general_ci[/I] with your new collation if different;
Last edited by sross; Wed 9 Nov '11, 2:46pm.Comment
-
Storage type such as MyISAM won't matter here.
In phpMyAdmin, you should be able to change the character set and collation of the database under Operations. Should be latin1, latin1_swedish_ci. According to the screen shot. latin1 corresponds to Western European languages. The collation is for searching purposes i.e. A comes before B.Translations provided by Google.
Wayne Luke
The Rabid Badger - a vBulletin Cloud demonstration site.
vBulletin 5 APIComment
-
Yes, trying to do this after the fact doesn't look pretty but the old server is gone... My english userbase 97% of users is fine, it's just my few foreign lang forums suffering at the moment until I find a fix.
*edit, ok messing with the server does not seem wise. I am trying to contact a specialist to help out with this. I did see one thing:
Code:[SIZE=2]How to change the collation for all tables in a MySQL database to UTF-8?[/SIZE] Changing the collation for all tables in a MySQL database can be time consuming depending on how many tables you have. That's why we recommend using the following PHP script for changing the collation for all tables at a time: [I]<?php $db = mysql_connect('localhost','[B]myuser_mydbuser[/B]','[B]mypassword[/B]'); if(!$db) echo "Cannot connect to the database - incorrect details"; mysql_select_db('myuser_mydbname'); $result=mysql_query('show tables'); while($tables = mysql_fetch_array($result)) { foreach ($tables as $key => $value) { mysql_query("ALTER TABLE $value COLLATE [B]utf8_general_ci[/B]"); }} echo "The collation of your database has been successfully changed!"; ?>[/I] Make sure to substitute in the above script: - [I]myuser_mydbname[/I] with your database name; - [I]myuser_mydbuser[/I] with your mysql username; - [I]mypassword[/I] with your password for the mysql user; - [I]utf8-general_ci[/I] with your new collation if different;
- How was the database backed up and restored ? Exact commands and options i.e. mysqldump and options passed or steps you took ?
- Be careful of automatic conversion scripts as sometimes it's just the table and db character set that changed and not actual data contained in the tables. If a conversion script converts actual data within the tables as well, then it would be a double conversion leading to messed up characters
- Do you still have the old backup .sql file created from old server ? You can possible at least determine the old charset/collation settings by inspecting the old .sql so know what to set for new server.
- How large is the database backup sql file ?
I wrote a checkcharset.sh script (fill out a few variable settings within script) which only checks a server and vB installs character set configuration as phpmyadmin can be misleading http://vbtechsupport.com/1375/. Example of it's usage https://www.vbulletin.com/forum/show...=1#post2213118
If you have access to old and new server, run script on both and save output to compare the character set environments for both servers.
Be sure to set the POSTID appropriate as outlined which is just to confirm whether or not MySQL is displaying right characters or if vB/client side is interpreting it differently.
Code:# How to set POSTID # find a post preferably with short text i.e. 1 or 2 lines of text # which has incorrectly displayed characters or create a new post with # 1 or 2 lines of mixed english and native charset of your forum text # note the posts' postid i.e. postid = 1 POSTID='1'
:: Always Back Up Forum Database + Attachments BEFORE upgrading !
:: Nginx SPDY SSL - World Flags Demo [video results]
:: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]Comment
-
Storage type such as MyISAM won't matter here.
In phpMyAdmin, you should be able to change the character set and collation of the database under Operations. Should be latin1, latin1_swedish_ci. According to the screen shot. latin1 corresponds to Western European languages. The collation is for searching purposes i.e. A comes before B.
Could that be a problem that my server is set to utf8? ThanksComment
-
sross, whatever you do, only test changes/conversions on a copy of your database or test server and not on your live database.
- How was the database backed up and restored ? Exact commands and options i.e. mysqldump and options passed or steps you took ?
- Be careful of automatic conversion scripts as sometimes it's just the table and db character set that changed and not actual data contained in the tables. If a conversion script converts actual data within the tables as well, then it would be a double conversion leading to messed up characters
- Do you still have the old backup .sql file created from old server ? You can possible at least determine the old charset/collation settings by inspecting the old .sql so know what to set for new server.
- How large is the database backup sql file ?
I wrote a checkcharset.sh script (fill out a few variable settings within script) which only checks a server and vB installs character set configuration as phpmyadmin can be misleading http://vbtechsupport.com/1375/. Example of it's usage https://www.vbulletin.com/forum/show...=1#post2213118
If you have access to old and new server, run script on both and save output to compare the character set environments for both servers.
Be sure to set the POSTID appropriate as outlined which is just to confirm whether or not MySQL is displaying right characters or if vB/client side is interpreting it differently.
Code:# How to set POSTID # find a post preferably with short text i.e. 1 or 2 lines of text # which has incorrectly displayed characters or create a new post with # 1 or 2 lines of mixed english and native charset of your forum text # note the posts' postid i.e. postid = 1 POSTID='1'
I have the original cpanel full backup file on my system at home. Maybe I can extract that and find some info from the old db. I will try running your script. Unfortunately the old server is gone.Comment
-
Hi George,
I am getting errors running the script (my unix skills are pretty low), I'm pretty sure it's configured right. Do i need to run it from a certain location and as root maybe? Sorry...
-bash-4.2$ bash checkcharset_v0.03.sh
: command not foundsh: line 12:
: command not foundsh: line 49:
: command not foundsh: line 53:
: command not foundsh: line 58:
: command not foundsh: line 62:
: command not foundsh: line 67:
: command not foundsh: line 71:
: command not foundsh: line 76:
: command not foundsh: line 83:
: command not foundsh: line 85:
'heckcharset_v0.03.sh: line 86: syntax error near unexpected token `{
'heckcharset_v0.03.sh: line 86: `function funct_checkcharset {Comment
-
You could grep the sql file in ssh2 telnet
command to run
Code:grep -E '(DEFAULT CHARSET|COLLATE)' dbbackup.sql | uniq > charsetinfo.txt
Code:) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM AUTO_INCREMENT=1829 DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM AUTO_INCREMENT=64 DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; `extension` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
:: Always Back Up Forum Database + Attachments BEFORE upgrading !
:: Nginx SPDY SSL - World Flags Demo [video results]
:: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]Comment
-
Hi George,
I am getting errors running the script (my unix skills are pretty low), I'm pretty sure it's configured right. Do i need to run it from a certain location and as root maybe? Sorry...
-bash-4.2$ bash checkcharset_v0.03.sh
: command not foundsh: line 12:
: command not foundsh: line 49:
: command not foundsh: line 53:
: command not foundsh: line 58:
: command not foundsh: line 62:
: command not foundsh: line 67:
: command not foundsh: line 71:
: command not foundsh: line 76:
: command not foundsh: line 83:
: command not foundsh: line 85:
'heckcharset_v0.03.sh: line 86: syntax error near unexpected token `{
'heckcharset_v0.03.sh: line 86: `function funct_checkcharset {:: Always Back Up Forum Database + Attachments BEFORE upgrading !
:: Nginx SPDY SSL - World Flags Demo [video results]
:: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]Comment
widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Comment