No announcement yet.

Illegal mix of collations error after moving to new server

  • Filter
  • Time
  • Show
Clear All
new posts

  • Zachery
    Please start your own topic, don't hijack others.

    Leave a comment:

  • fionix
    Have exact same problem after moving to a new server!

    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?

    // 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() );
    $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";
    Any input would be much appreciated.

    Leave a comment:

  • jcard
    commented on 's reply
    Sorry to hear that grayloon
    Other suggestion would be to search online here for what others did to resolve their Illegal mix of collations errors and try those
    If still not resolved, perhaps search online will identify a solution.
    Would be great if you posted here what worked for you if you do find.

  • grayloon
    I checked that my database, tables, and columns mentioned in the query were all using UTF8. I also set $config['Mysqli']['charset'] = 'utf8' in core/includes/config.php. Finally, I changed Admin CP -> Language Manager -> English to use HTML Character SET UTF-8. Then, I cleared the system cache. I'm still getting "Illegal mix of collations" errors.

    Leave a comment:

  • jcard
    Hi grayloon, unfortunately no one from vbulletin gave me any pointers other than what I posted I did on this thread and Joe D. suggested.
    I still continue to get the errors I posted initially.

    Who knows, yours could be a simple fix, in that you just have one table that is an invalid collation.
    If so, I would recomend doing what I did in the initial post where you need to confirm that all tables are set to the same collation, and open each table and confirm says so inside too. Hope that helps.

    Leave a comment:

  • Illegal mix of collations error after moving to new server

    I've just upgraded from 4.2.2 to 5.1.1, and I'm getting email messages detailing an "Illegal mix of collations" error. The MySQL database and all tables use latin1_swedish_ci collation with latin1 character set. I can't figure out what's causing the error. Here's an example:

    Database error in vBulletin 5.1.1:
                Invalid SQL:
                        ### Channels ###
                        SELECT follow.title AS title, follow.nodeid AS keyval, 'node' AS sourcetable, IF(follow.lastcontent = 0, follow.lastupdate, follow.lastcontent) AS lastactivity,
                        follow.totalcount AS activity, type.class AS type,
                        (follow.nodeoptions & 512) AS noUnsubscribe
                        FROM vb_node AS follow
                        INNER JOIN vb_contenttype AS type ON type.contenttypeid = follow.contenttypeid
                        INNER JOIN vb_subscribediscussion AS sd ON sd.discussionid = follow.nodeid AND sd.userid = 131
                        ### Users ###
                        SELECT follow.username AS title, follow.userid AS keyval, 'user' AS sourcetable, IFNULL(follow.lastpost, follow.joindate) AS lastactivity,
                        follow.posts as activity, 'Member' AS type,
                        0 AS noUnsubscribe
                        FROM vb_user AS follow
                        INNER JOIN vb_userlist AS ul ON ul.relationid = follow.userid AND ul.userid = 131
                        WHERE ul.type = 'follow' AND ul.friend = 'yes'
    ORDER BY title ASC LIMIT 100;
                MySQL Error   : Illegal mix of collations for operation 'UNION'
                Error Number  : 1271
                Request Date  : Tuesday, June 3rd 2014 @ 04:48:59 PM
                Error Date    : Tuesday, June 3rd 2014 @ 04:49:00 PM
                Script        : http:///blogs/sureshot?u=131&blogcategoryid=-1
                Referrer      :
                IP Address    :
                Username      : Guest
                Classname     : vB_Database_MySQLi
                MySQL Version :
    My host is running PHP 5.3.20 and MySQL 5.1.61.
    Last edited by grayloon; Tue 3 Jun '14, 2:57pm.

Related Topics