cpanel move to new server and now character issues with some foreign users..

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • sross
    Senior Member
    • Mar 2004
    • 660

    [Forum] cpanel move to new server and now character issues with some foreign users..

    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

  • sross
    Senior Member
    • Mar 2004
    • 660

    #2
    Ok I have done a comparison in phpmyadmin of my old server (working database) and the new server (character issues). The last line differs between my old server and the new server. Maybe there is an easy way to correct this? (hopefully)

    Comment

    • sross
      Senior Member
      • Mar 2004
      • 660

      #3
      bump...

      Comment

      • Wayne Luke
        vBulletin Technical Support Lead
        • Aug 2000
        • 73976

        #4
        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 API

        Comment

        • sross
          Senior Member
          • Mar 2004
          • 660

          #5
          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. Thanks

          Comment

          • Wayne Luke
            vBulletin Technical Support Lead
            • Aug 2000
            • 73976

            #6
            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 API

            Comment

            • sross
              Senior Member
              • Mar 2004
              • 660

              #7
              Here is my servers config. So, Would it be adjusting this to be "Latin1" ? Also my last server was a storage type of MyISAM should that be changed too?

              Comment

              • sross
                Senior Member
                • Mar 2004
                • 660

                #8
                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;
                Maybe something like that is an option. I may setup a test server and do some testing until an expert can help.
                Last edited by sross; Wed 9 Nov '11, 2:46pm.

                Comment

                • Wayne Luke
                  vBulletin Technical Support Lead
                  • Aug 2000
                  • 73976

                  #9
                  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 API

                  Comment

                  • George L
                    Former vBulletin Support
                    • May 2000
                    • 32996
                    • 3.8.x

                    #10
                    Originally posted by sross
                    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;
                    Maybe something like that is an option. I may setup a test server and do some testing until an expert can help.
                    sross, whatever you do, only test changes/conversions on a copy of your database or test server and not on your live database.
                    1. How was the database backed up and restored ? Exact commands and options i.e. mysqldump and options passed or steps you took ?
                    2. 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
                    3. 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.
                    4. 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

                    • sross
                      Senior Member
                      • Mar 2004
                      • 660

                      #11
                      Originally posted by Wayne Luke
                      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.
                      Ok changed this and now the db seems to all be in latin1 collation however nothing has changed in the forums, the text is still garbled in my foreign language areas. I noticed on the phpmyadmin page the mysql settings:



                      Could that be a problem that my server is set to utf8? Thanks

                      Comment

                      • sross
                        Senior Member
                        • Mar 2004
                        • 660

                        #12
                        Originally posted by eva2000
                        sross, whatever you do, only test changes/conversions on a copy of your database or test server and not on your live database.
                        1. How was the database backed up and restored ? Exact commands and options i.e. mysqldump and options passed or steps you took ?
                        2. 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
                        3. 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.
                        4. 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'
                        Hi George, wow, thanks for the info. I have not run anything on the prod database other than phpmyadmin - operations - changing db collation to latin1_swedish_ci

                        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

                        • sross
                          Senior Member
                          • Mar 2004
                          • 660

                          #13
                          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

                          • George L
                            Former vBulletin Support
                            • May 2000
                            • 32996
                            • 3.8.x

                            #14
                            You could grep the sql file in ssh2 telnet

                            command to run

                            Code:
                            grep -E '(DEFAULT CHARSET|COLLATE)' dbbackup.sql | uniq > charsetinfo.txt
                            charsetinfo.txt will contain something like

                            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 '',
                            if they all return latin1 then your old server was default latin1 set
                            :: 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

                            • George L
                              Former vBulletin Support
                              • May 2000
                              • 32996
                              • 3.8.x

                              #15
                              Originally posted by sross
                              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 {
                              yup run as root user
                              :: 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.
                              Working...