Illegal mix of collations error after moving to new server

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • fionix
    Senior Member
    • Jul 2007
    • 140

    Illegal mix of collations error after moving to new server

    Have exact same problems as others here on the forum, after moving to a new server!

    I get a lot of error message e-mails like the one below:

    Code:
    Database error in vBulletin 5.1.4:
     
                      Invalid SQL:
    SELECT * FROM routenew
     WHERE `prefix` IN ('forum/general-forums/the-talk-about-everything-lounge/12745-i-donÂ’t-like-your-attitude','forum/general-forums/the-talk-about-everything-lounge','forum/general-forums','forum');
     
                      MySQL Error   : Illegal mix of collations for operation ' IN '
                      Error Number  : 1271
                      Request Date  : Thursday, January 8th 2015 @ 11:13:08 PM
                      Error Date    : Thursday, January 8th 2015 @ 11:13:08 PM
                      Script        : [URL="http://forum/forum/general-forums/the-talk-about-everything-lounge/12745-i-donÂ't-like-your-attitude?p=31205"]http:///forum/forum/general-forums/the-talk-about-everything-lounge/12745-i-donÂ’t-like-your-attitude?p=31205[/URL]
                      Referrer      :
                      IP Address    : 68.180.228.219
                      Username      : Guest
                      Classname     : vB_Database_MySQL
                      MySQL Version :
    I tried the script which was posted for a 3.8.2 and up to 4.2 board, but it can't run with VBulletin 5!

    Anyone has a similar script that just work for VB 5? The below is for VB 3.8.2

    PHP Code:
      <?php // Don't forget to enter your db infos.  define('THIS_SCRIPT', 'convert'); require './global.php';  //---------------  header('Content-type: text/plain');  $dbconn = mysql_connect('localhost', 'DB-user', 'DB-pass') or die( mysql_error() ); mysql_select_db("db_name");  $sql = "ALTER DATABASE `DB-Name` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci"; $result = mysql_query($sql) or die( mysql_error() ); print "Database changed to latin\n";  $sql = 'SHOW TABLES'; $result = mysql_query($sql) or die( mysql_error() );  while ( $row = mysql_fetch_row($result) ) { $table = mysql_real_escape_string($row[0]); $sql = "ALTER TABLE $table DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci, CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci"; mysql_query($sql) or die( mysql_error() ); print "$table changed to latin.\n"; }  mysql_close($dbconn); ?>
    Cyprus Company Formation
    |Cyprus Offshore Bank Account| Offshore Company Formation| Asset Protection Offshore
  • fionix
    Senior Member
    • Jul 2007
    • 140

    #2
    What I notices is that in SCRIPT: it don't show the domain name of the forum but only http:///forum/forum/ not sure what does that means.
    Cyprus Company Formation
    |Cyprus Offshore Bank Account| Offshore Company Formation| Asset Protection Offshore

    Comment

    • fionix
      Senior Member
      • Jul 2007
      • 140

      #3
      Anyone has any input?
      Cyprus Company Formation
      |Cyprus Offshore Bank Account| Offshore Company Formation| Asset Protection Offshore

      Comment

      • zfi
        New Member
        • Oct 2009
        • 14
        • 3.8.x

        #4
        I have seen this happen in 4.x when one or more of the database tables have different collation sequence definitions. The error occurs in a SQL query where the query is trying to UNION two subqueries and one of the tables in the subquery has a different collation sequence than the other tables involved in the query.

        If you have access to the database, you can run SHOW TABLE STATUS; to display details for all of the tables. Check the collation sequence column to find the ones that differ.

        Comment

        • Zachery
          Former vBulletin Support
          • Jul 2002
          • 59097

          #5
          When you moved to the new server, your database settings weren't preserved. You'd need correct the tables/columns to have the same encoding as the rest of the database.

          Comment

          • fionix
            Senior Member
            • Jul 2007
            • 140

            #6
            Thanks for the hint ZFI, they are ALL "latin1_swedish_ci" so will I have to change something somwhere now?

            In Language & Phrases -> Language Manager -> HTML Character Set is set to: ISO-8859-1 is that correct?
            Cyprus Company Formation
            |Cyprus Offshore Bank Account| Offshore Company Formation| Asset Protection Offshore

            Comment

            • fionix
              Senior Member
              • Jul 2007
              • 140

              #7
              Originally posted by Zachery
              When you moved to the new server, your database settings weren't preserved. You'd need correct the tables/columns to have the same encoding as the rest of the database.
              Thank you, what tables/columns are we speaking, can you please be more specific?
              Cyprus Company Formation
              |Cyprus Offshore Bank Account| Offshore Company Formation| Asset Protection Offshore

              Comment

              • Zachery
                Former vBulletin Support
                • Jul 2002
                • 59097

                #8
                No, I'm not sure which tables/columns are the problem, the database error isn't 100% clear. But ALL of the tables in the database should match.

                Comment

                • fionix
                  Senior Member
                  • Jul 2007
                  • 140

                  #9
                  As I already wrote, the one in the "collation" do all match. Are there other tables/columns that have to match with something ? if so, which one please?

                  Is there any other way to get a better "error" so you may know what is wrong?

                  Code:
                                    Invalid SQL:
                  SELECT * FROM routenew
                   WHERE `prefix` IN ('forum/general-forums/forum-news-announcements-and-suggestions/12056-panama-legal-scam-and-cclogic–-a-message-to-the-customers-and-users-of-this-forum','forum/general-forums/forum-news-announcements-and-suggestions','forum/general-forums','forum');
                   
                                    MySQL Error   : Illegal mix of collations for operation ' IN '
                                    Error Number  : 1271
                                    Request Date  : Tuesday, January 13th 2015 @ 05:44:21 PM
                                    Error Date    : Tuesday, January 13th 2015 @ 05:44:21 PM
                                    Script        : [URL="http://forum/forum/general-forums/forum-news-announcements-and-suggestions/12056-panama-legal-scam-and-cclogic–-a-message-to-the-customers-and-users-of-this-forum"]http:///forum/forum/general-forums/forum-news-announcements-and-suggestions/12056-panama-legal-scam-and-cclogic–-a-message-to-the-customers-and-users-of-this-forum[/URL]
                                    Referrer      : [URL]http://www.cclogic.com/[/URL]
                                    IP Address    : 107.153.7.62
                                    Username      : Guest
                                    Classname     : vB_Database_MySQL
                                    MySQL Version :
                  Cyprus Company Formation
                  |Cyprus Offshore Bank Account| Offshore Company Formation| Asset Protection Offshore

                  Comment

                  • Zachery
                    Former vBulletin Support
                    • Jul 2002
                    • 59097

                    #10
                    If all tables and all columns did match, you wouldn't be getting the error message.

                    Comment

                    • fionix
                      Senior Member
                      • Jul 2007
                      • 140

                      #11
                      Okay.. which tables do I have to check then and which fields, would you be so kind and be more specific please?

                      I run this: SHOW TABLE STATUS and it shows all the tables and fields, I assume.

                      Then I checked the collation sequence column

                      There all the way down it says: latin1_swedish_ci

                      So I would appreciate it much if you could let me know what other columns I have to check? From a Google search it seems I'm not the only one that have problems with it
                      Cyprus Company Formation
                      |Cyprus Offshore Bank Account| Offshore Company Formation| Asset Protection Offshore

                      Comment

                      • fionix
                        Senior Member
                        • Jul 2007
                        • 140

                        #12
                        Okay I made another Google search, and it seems there is something more complicated that has to be done:

                        When I run this command: show variables like 'collation%'

                        I get the below output:
                        collation_connection utf8mb4_general_ci
                        collation_database latin1_swedish_ci
                        collation_server utf8_general_ci
                        So I guess that's where the problems start right?

                        I read the below, does this apply for this error as well:
                        The utf8_general_ci collation will do but ideally we want utf8_unicode_ci. The former basically ignores all accents: it treats ‘ü’ the same as ‘u’. The latter pays attention to accents; it’s a tiny bit slower but more accurate.
                        Cyprus Company Formation
                        |Cyprus Offshore Bank Account| Offshore Company Formation| Asset Protection Offshore

                        Comment

                        • Zachery
                          Former vBulletin Support
                          • Jul 2002
                          • 59097

                          #13
                          Again, is every single table, and column in the ENTIRE database, set to the correct/same setting as every other one?

                          Comment

                          • fionix
                            Senior Member
                            • Jul 2007
                            • 140

                            #14
                            What is the correct same setting?

                            From your replies it seems you don't want to help.

                            Otherwise I think we can agree that if I enter the SQL command " SHOW TABLE STATUS " then it should pull up ALL tables in the entire database for vbulletin right?

                            So, and if I go to the column " Collation " which is the only column I can find there where you have encoding parameters and the whole row down it says :
                            latin1_swedish_ci
                            Then I assume we can agree that it all is OK.. is there any other SQL command I should run and any other culumn I should check? If so, would you be so kind and spend 2 sec. to tell me that?

                            There is another Table "Datatype" there it sometimes changes from MyISAM to InnoDB - can this be an error maybe?
                            Last edited by fionix; Fri 16 Jan '15, 12:12am.
                            Cyprus Company Formation
                            |Cyprus Offshore Bank Account| Offshore Company Formation| Asset Protection Offshore

                            Comment

                            • fionix
                              Senior Member
                              • Jul 2007
                              • 140

                              #15
                              delete please, error.
                              Last edited by fionix; Fri 16 Jan '15, 9:43am.
                              Cyprus Company Formation
                              |Cyprus Offshore Bank Account| Offshore Company Formation| Asset Protection Offshore

                              Comment

                              Related Topics

                              Collapse

                              Working...