No announcement yet.

Different collation tables after Upgrade

  • Filter
  • Time
  • Show
Clear All
new posts

  • Different collation tables after Upgrade

    Sorry for disturbing again after my upgrade from 4.2.4 to 5.5.5 ... but obviously the upgrade process generated tables of different collations, some are "latin1_swedish_ci", some are "latin1_german2_ci"

    And now I get a lot of Database errors, e.g.

    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 node AS follow
    INNER JOIN contenttype AS type ON type.contenttypeid = follow.contenttypeid

    INNER JOIN subscribediscussion AS sd ON sd.discussionid = follow.nodeid AND sd.userid = 3509

    ### 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 user AS follow
    INNER JOIN userlist AS ul ON ul.relationid = follow.userid AND ul.userid = 3509
    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 : Saturday, January 4th 2020 @ 07:47:17 PM
    Error Date : Saturday, January 4th 2020 @ 07:47:17 PM
    Script : XXXXX
    Referrer :
    IP Address :
    Username : Guest
    Classname : vB_Database_MySQLi
    MySQL Version :

    How could I solve this problem? By changing the collation of all tables? Howto?

    Thank you
    Markus Stein

  • #2
    When vBulletin creates a new table in the upgrade process, it asks MySQL what the default collation for the database is and then uses that. Most MySQL installations default to latin1_swedish_ci. If you have changed collations for any reason on tables in the past then you should change the database to use that as the default to prevent this issue from happening in the future.

    You will find more information in the MySQL manual here:

    You will need to use phpMyAdmin and make sure that all tables and fields within them have the same collation. Make sure your database collations are appropriate and consistent throughout the entire database. You need to use program like phpmyadmin to view your collations:

    When you click your database name in phpmyadmin it will list all tables, their collations, as well as the collation of the database itself at the bottom. And when you click the name of a table on the left it will list the collations of individual fields within that table.

    1) To edit the collation of the database itself you need to click the database name on the left and then click "Operations" on the top.

    2) To edit the collation of an individual table you need to click its name on the left and then click "Operations" on the top.

    3) To edit the collation of an individual field within a table you need to click the table name on the left and then click the edit image (a little pencil icon) for that field.

    For more information please see:

    Note: It is very very very important to make full database backups before proceeding if you are ever manually making any changes to your database, let it be issuing queries, or editing it via phpMyAdmin or any other interface. If done incorrectly, manually modifying the database can potentially cause irreversible damage to your database, and there is no way to perform a "partial restore" to restore just the part you may break.
    Vote for:

    - *Admin Settable Paid Subscription Reminder Timeframe*
    *PM - Add ability to reply to originator only*
    - Add Admin ability to auto-subscribe users to specific channel(s)
    - Highlight the correct navigation tab when you are on a custom page
    - "Quick Route" Interface...
    - Allow to use custom icons for individual forums


    • #3
      Thank you for this hints - I change the collation to the latin_swedish version; as this was also the collation in the old database - it is the action of my provider offering the new "German" collation.

      Markus Stein


      widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.