Announcement

Collapse
No announcement yet.

Database error only since upgrading..

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

  • Database error only since upgrading..

    I downloaded 3.81 and ran the upgrade script. Maybe the upgrade did not get fully completed because my forum footer says 3.7.3 and this database error says 3.7.3 too. My admincp control panel says

    Admin Control Panel (vBulletin 3.8.1 Patch Level 1)

    At the bottom it also says 3.8.1 PL 1 too.

    This is the database error I get and it happens a lot, does anyone know why?

    Thanks

    Database error in vBulletin 3.7.3:

    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, March 18th 2009 @ 02:59:31 PM
    Error Date : Wednesday, March 18th 2009 @ 02:59:31 PM
    Script : http://www.disneyworldonline.co.uk/f....php?do=update
    Referrer : http://www.disneyworldonline.co.uk/f...p?do=edit&f=26
    IP Address : 86.4.6.204
    Username : Nick Collins
    Classname : vB_Database
    MySQL Version : 5.0.67-community

  • #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
      Originally posted by Steve Machol View Post
      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.
      Hi Steve,

      I have not done any changes to the database or anything like that, the only thing I have done is the upgrade script. Ill look at the above things though but I dont have a clue about it all really. I only did the upgrade because it was quite old and needed to do it. I would rather have not done the upgrade really lol

      Would doing the upgrade again fix the problem? I have not ever logged into mysqul phpmyadmin either..

      Thanks

      Nick

      Comment


      • #4
        Then I suggest asking your host for help with this. Something changed on the server end to cause this.
        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
          cheers. its only happened since upgrading and nothing has been done to the server either.

          is it possible to install a new copy but keep users, forums, threads etc? I keep getting database error email alerts so other users must be getting errors too.

          Will upgrading again fix it? i had issues upgrading to begin with (see other thread)

          Comment


          • #6
            Something had to have changed on the server to cause this. Nonetheless the only fix is what I have already posted. Upgrading will not fix this.
            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


            • #7
              Originally posted by Steve Machol View Post
              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.
              Got into this phpmyadmin thing. Looking down the list there are most of them that say latin1_general_ci but some say swedish. Is it the swedish ones I need to change to latin?





              access 0 MyISAM latin1_general_ci - adminhelp 1,613 MyISAM latin1_general_ci - administrator 2 MyISAM latin1_general_ci 80 B adminlog 4,997 MyISAM latin1_general_ci - adminmessage 0 MyISAM latin1_general_ci - adminutil 1 MyISAM latin1_general_ci - album 4 MyISAM latin1_general_ci - albumpicture 18 MyISAM latin1_general_ci - albumupdate 0 MyISAM latin1_swedish_ci - announcement 0 MyISAM latin1_general_ci - announcementread 0 MyISAM latin1_general_ci - attachment 128 MyISAM latin1_general_ci - attachmentpermission 0 MyISAM latin1_general_ci - attachmenttype 12 MyISAM latin1_general_ci - attachmentviews 0 MyISAM latin1_general_ci - avatar 0 MyISAM latin1_general_ci - bbcode 2 MyISAM latin1_general_ci - bookmarksite 4 MyISAM latin1_general_ci - calendar 0 MyISAM latin1_general_ci - calendarcustomfield 0 MyISAM latin1_general_ci - calendarmoderator 0 MyISAM latin1_general_ci - calendarpermission 0 MyISAM latin1_general_ci - cellproviders 189 MyISAM latin1_general_ci - cellsettings 6 MyISAM latin1_general_ci - cpsession 0 MEMORY latin1_general_ci 123 B cron 17 MyISAM latin1_general_ci - cronlog 849 MyISAM latin1_general_ci - customavatar 7 MyISAM latin1_general_ci - customprofilepic 8 MyISAM latin1_general_ci - cybppdonate 1 MyISAM latin1_general_ci - cybvilxh_guests 6 MyISAM latin1_general_ci - cyb_chatbox 228 MyISAM latin1_swedish_ci - datastore 29 MyISAM latin1_general_ci 50.5 KiB deletionlog 1 MyISAM latin1_general_ci - discussion 0 MyISAM latin1_swedish_ci - discussionread 0 MyISAM latin1_swedish_ci - editlog 52 MyISAM latin1_general_ci - event 0 MyISAM latin1_general_ci - externalcache 0 MyISAM latin1_general_ci 110.9 KiB faq 42 MyISAM latin1_general_ci - fbdatamap 3 MyISAM latin1_general_ci - fbuser 11 MyISAM latin1_general_ci - force_read_guests 0 MyISAM latin1_swedish_ci - force_read_users 59 MyISAM latin1_swedish_ci - forum 73 MyISAM latin1_general_ci 68 B forumpermission 91 MyISAM latin1_general_ci - forumprefixset 0 MyISAM latin1_general_ci - forumread 0 MyISAM latin1_general_ci - groupmessage 0 MyISAM latin1_general_ci - groupmessage_hash 0 MyISAM latin1_general_ci - groupread 0 MyISAM latin1_swedish_ci - holiday 4 MyISAM latin1_general_ci - humanverify 78 MyISAM latin1_general_ci 1.3 KiB hvanswer 0 MyISAM latin1_general_ci - hvquestion 0 MyISAM latin1_general_ci - icon 14 MyISAM latin1_general_ci - imagecategory 3 MyISAM latin1_general_ci - imagecategorypermission 0 MyISAM latin1_general_ci - infraction 0 MyISAM latin1_general_ci - infractionban 0 MyISAM latin1_general_ci - infractiongroup 0 MyISAM latin1_general_ci - infractionlevel 4 MyISAM latin1_general_ci - language 1 MyISAM latin1_general_ci - mailqueue 0 MyISAM latin1_general_ci - moderation 2 MyISAM latin1_general_ci - moderator 1 MyISAM latin1_general_ci - moderatorlog 447 MyISAM latin1_general_ci - notice 4 MyISAM latin1_general_ci - noticecriteria 9 MyISAM latin1_general_ci - noticedismissed 0 MyISAM latin1_swedish_ci - passwordhistory 384 MyISAM latin1_general_ci - paymentapi 7 MyISAM latin1_general_ci - paymentinfo 54 MyISAM latin1_general_ci - paymenttransaction 191 MyISAM latin1_general_ci - phrase 11,064 MyISAM latin1_general_ci 304 B phrasetype 66 MyISAM latin1_general_ci - picture 18 MyISAM latin1_general_ci - picturecomment 0 MyISAM latin1_general_ci - picturecomment_hash 0 MyISAM latin1_general_ci - plugin 78 MyISAM latin1_general_ci - pm 2,159 MyISAM latin1_general_ci - pmreceipt 3 MyISAM latin1_general_ci - pmtext 92 MyISAM latin1_general_ci - pmthrottle 0 MyISAM latin1_swedish_ci - podcast 0 MyISAM latin1_general_ci - podcastitem 0 MyISAM latin1_general_ci - poll 3 MyISAM latin1_general_ci - pollvote 26 MyISAM latin1_general_ci - post 6,847 MyISAM latin1_general_ci - postedithistory 212 MyISAM latin1_general_ci - posthash 26 MyISAM latin1_general_ci 2.0 KiB postindex 0 MyISAM latin1_general_ci - postlog 0 MyISAM latin1_general_ci 1.2 KiB postparsed 4 MyISAM latin1_general_ci - prefix 0 MyISAM latin1_general_ci - prefixpermission 0 MyISAM latin1_swedish_ci - prefixset 0 MyISAM latin1_general_ci - product 16 MyISAM latin1_general_ci - productcode 21 MyISAM latin1_general_ci - productdependency 5 MyISAM latin1_general_ci - profileblockprivacy 0 MyISAM latin1_swedish_ci - profilefield 7 MyISAM latin1_general_ci 120 B profilefieldcategory 0 MyISAM latin1_general_ci - profilevisitor 142 MyISAM latin1_general_ci - ranks 0 MyISAM latin1_general_ci - reminder 0 MyISAM latin1_general_ci - reputation 1 MyISAM latin1_general_ci - reputationlevel 15 MyISAM latin1_general_ci - rssfeed 4 MyISAM latin1_general_ci - rsslog 6,619 MyISAM latin1_general_ci - search 92 MyISAM latin1_general_ci 130.2 KiB session 34 MEMORY latin1_general_ci 133.5 KiB setting 587 MyISAM latin1_general_ci 52.9 KiB settinggroup 57 MyISAM latin1_general_ci - sigparsed 10 MyISAM latin1_general_ci - sigpic 4 MyISAM latin1_general_ci 36 B smilie 11 MyISAM latin1_general_ci - socialgroup 1 MyISAM latin1_general_ci - socialgroupcategory 1 MyISAM latin1_swedish_ci - socialgroupicon 0 MyISAM latin1_swedish_ci - socialgroupmember 1 MyISAM latin1_general_ci - socialgrouppicture 0 MyISAM latin1_general_ci - spamlog 0 MyISAM latin1_general_ci - stats 91 MyISAM latin1_general_ci - strikes 1 MyISAM latin1_general_ci 308 B style 3 MyISAM latin1_general_ci - subscribediscussion 0 MyISAM latin1_swedish_ci - subscribeevent 0 MyISAM latin1_general_ci - subscribeforum 1 MyISAM latin1_general_ci - subscribegroup 0 MyISAM latin1_swedish_ci - subscribethread 180 MyISAM latin1_general_ci - subscription 15 MyISAM latin1_general_ci - subscriptionlog 31 MyISAM latin1_general_ci - subscriptionpermission 50 MyISAM latin1_general_ci - tachyforumcounter 0 MyISAM latin1_general_ci - tachyforumpost 0 MyISAM latin1_general_ci - tachythreadcounter 0 MyISAM latin1_general_ci - tachythreadpost 0 MyISAM latin1_general_ci - tag 0 MyISAM latin1_general_ci - tagsearch 0 MyISAM latin1_general_ci - tagthread 0 MyISAM latin1_general_ci - template 664 MyISAM latin1_general_ci 8.4 KiB templatehistory 0 MyISAM latin1_general_ci - thread 6,730 MyISAM latin1_general_ci - threadrate 1 MyISAM latin1_general_ci - threadread 0 MyISAM latin1_general_ci - threadredirect 0 MyISAM latin1_general_ci - threadviews 1 MyISAM latin1_general_ci - upgradelog 4 MyISAM latin1_general_ci - user 222 MyISAM latin1_general_ci 2.6 KiB useractivation 6 MyISAM latin1_general_ci - userban 4 MyISAM latin1_general_ci - userchangelog 157 MyISAM latin1_general_ci - usercss 25 MyISAM latin1_general_ci - usercsscache 1 MyISAM latin1_general_ci - userfield 222 MyISAM latin1_general_ci 736 B usergroup 14 MyISAM latin1_general_ci - usergroupleader 0 MyISAM latin1_general_ci - usergrouprequest 0 MyISAM latin1_general_ci - userlist 24 MyISAM latin1_general_ci - usernote 0 MyISAM latin1_general_ci - userpromotion 0 MyISAM latin1_general_ci - usertextfield 222 MyISAM latin1_general_ci 304 B usertitle 3 MyISAM latin1_general_ci - visitormessage 12 MyISAM latin1_general_ci - visitormessage_hash 0 MyISAM latin1_general_ci 52 B word 0 MyISAM latin1_general_ci - 167 table(s) 46,566 MyISAM latin1_swedish_ci 495.6 KiB

              Comment


              • #8
                Dont know why that lost all its formatting.

                Comment


                • #9
                  Sorry I meant the other way around.

                  THe first line is
                  access 0 MyISAM latin1_general_ci -

                  Then there are around 10 or so that are like this

                  albumupdate 0 MyISAM latin1_swedish_ci 1.0 KiB -

                  This is the bottom of the list:

                  167 table(s) Sum 46,566 MyISAM latin1_swedish_ci 49.1 MiB 495.6 KiB


                  Do I need to change them all to latin1 swedish?

                  Comment


                  • #10
                    Someone fixed this for me, thanks please close thread.

                    Comment

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