Announcement

Collapse
No announcement yet.

Upgrade from 3.7.1 to 3.8

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Upgrade from 3.7.1 to 3.8

    On my way to upgrading to a 4.x version, I did this upgrade and have received the following error:

    Database error in vBulletin 3.7.1:

    Invalid SQL:

    SELECT prefix.*, prefixpermission.usergroupid AS restriction
    FROM prefix AS prefix
    LEFT JOIN prefixpermission AS prefixpermission ON (prefix.prefixid = prefixpermission.prefixid)
    ORDER BY prefix.displayorder;

    MySQL Error : Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
    Error Number : 1267
    Request Date : Wednesday, August 24th 2011 @ 03:15:33 PM
    Error Date : Wednesday, August 24th 2011 @ 03:15:33 PM
    Script : http://www.legion-whiterose.com/foru...0a2.php?step=5
    Referrer : http://www.legion-whiterose.com/foru...0a2.php?step=4
    IP Address : 96.53.72.170
    Username :
    Classname : vB_Database
    MySQL Version : 5.0.92-community
    Any assistance would be appreciated. If you need any more info, please let me know.

  • #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:
    http://dev.mysql.com/doc/refman/5.1/en/charset.html

    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:

    http://www.phpmyadmin.net/home_page/index.php

    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: http://www.vbulletin.com/forum/showt...ight=collation

    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.
    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
    Change CKEditor Colors to Match Style (for 4.1.4 and above)

    Steve Machol Photography


    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


    Comment


    • #3
      Ok, that does make sense, and in looking at my database, I see that some tables are using latin1_general_ci and far more are using the latin1_swedish_ci. So, just to ensure that I am reading this right and not confusing it somehow, I should change all the non-swedish collations to swedish.

      At that point do I just run the upgrade steps again?

      Comment


      • #4
        Yes, fix the collations then rerun the upgrade.
        Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
        Change CKEditor Colors to Match Style (for 4.1.4 and above)

        Steve Machol Photography


        Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


        Comment


        • #5
          ok, I've updated all the tables to swedish, verified that the db was set to swedish (which it was), and even ensured that the host was set to swedish, if only for during this upgrade, and I still get the exact same error message? I can't see anything else set to general, is there any way I can do a query to find what I've missed?

          Comment


          • #6
            Did you check the tables columns? Each column can have its own stoage type as well.

            Comment


            • #7
              That's probably it, thanks. Will let you know how this turns out.

              Comment


              • #8
                That worked beautifully, thank you for your assistance. Now onto the 4.x upgrade...

                Comment

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