database error mails

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • sowmya002
    Member
    • Feb 2014
    • 80
    • 5.0.X

    database error mails

    Hi,

    We are getting mails of the below kind in from our vbulletin forum. Though not so frequent these kinds of mails keep coming from the forum. As you can see from the message pasted below the version is latest. Please let me know how to get rid of these.

    Database error in vBulletin 5.1.5:

    Invalid SQL:
    SELECT * FROM routenew
    WHERE `prefix` IN ('forum/xxxxx','forum/xxxx','forum');

    MySQL Error : Illegal mix of collations for operation ' IN '
    Error Number : 1271
    Request Date : Thursday, February 5th 2015 @ 05:56:07 AM
    Error Date : Thursday, February 5th 2015 @ 05:56:08 AM
    Script : http://
    Referrer :
    IP Address : 207.46.13.65
    Username : Guest
    Classname : vB_Database_MySQLi
    MySQL Version :

    Thanks!
    Sowmya
  • sowmya002
    Member
    • Feb 2014
    • 80
    • 5.0.X

    #2
    Hi,

    Any suggestion on the above issue will be really helpful. We do get errors of this kind.

    Thanks!
    Sowmya

    Comment

    • Lynne
      Former vBulletin Support
      • Oct 2004
      • 26255

      #3
      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: https://www.vbulletin.com/forum/show...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.

      Please don't PM or VM me for support - I only help out in the threads.
      vBulletin Manual & vBulletin 4.0 Code Documentation (API)
      Want help modifying your vbulletin forum? Head on over to vbulletin.org
      If I post CSS and you don't know where it goes, throw it into the additional.css template.

      W3Schools <- awesome site for html/css help

      Comment

      • sowmya002
        Member
        • Feb 2014
        • 80
        • 5.0.X

        #4
        Hi Lynne,

        Thanks for your reply!

        I logged into our vbulltin db and browsed through the tables to check the collation and engine. All tables were using engine: InnoDB, collation latin1_swedish_ci.
        except the discrepancies listed below. We had not changed the below listed items. Not sure how they became different from the others. So wanted to check with you before we change these also to match the others.

        -- below tables were using engine MyISAM

        autosavetext
        language
        nodehash
        phrase
        postedithistory
        userfield

        -- below fields have collation as latin1_bin unlike other fields

        table: faq -- field: faqname
        table: filedate -- field: extension
        table: phrase -- field: varname

        Please let me know whether we are safe to change the above discrepancies so that they will match the other tables and fields.

        Thanks!
        Sowmya


        Comment

        • Lynne
          Former vBulletin Support
          • Oct 2004
          • 26255

          #5
          The database error is stating that the issue is with the routenew table. What does the structure look like in there?

          Please don't PM or VM me for support - I only help out in the threads.
          vBulletin Manual & vBulletin 4.0 Code Documentation (API)
          Want help modifying your vbulletin forum? Head on over to vbulletin.org
          If I post CSS and you don't know where it goes, throw it into the additional.css template.

          W3Schools <- awesome site for html/css help

          Comment

          • sowmya002
            Member
            • Feb 2014
            • 80
            • 5.0.X

            #6
            I checked the routenew table. collation is latin1_swedish_ci. It is the same for all fields. Engine is InnODB. Will the error have something to do with the datatype?

            Also we used to get other similar errors related to DB as well. Will those have any connection with the discrepancies that I have listed?

            Thanks!
            Sowmya

            Comment

            • sowmya002
              Member
              • Feb 2014
              • 80
              • 5.0.X

              #7
              Hi,

              Any updates?

              Thanks!
              Sowmya

              Comment

              • Lynne
                Former vBulletin Support
                • Oct 2004
                • 26255

                #8
                Can you run these queries on your database:
                Code:
                show variables like "character_set_database";
                show variables like "collation_database";

                Please don't PM or VM me for support - I only help out in the threads.
                vBulletin Manual & vBulletin 4.0 Code Documentation (API)
                Want help modifying your vbulletin forum? Head on over to vbulletin.org
                If I post CSS and you don't know where it goes, throw it into the additional.css template.

                W3Schools <- awesome site for html/css help

                Comment

                • sowmya002
                  Member
                  • Feb 2014
                  • 80
                  • 5.0.X

                  #9
                  Hi Lynne,

                  The results of the above queries are as follows:

                  character_set_database value: latin1
                  collation_database value: latin1_swedish_ci

                  Thanks!
                  Sowmya

                  Comment

                  • Lynne
                    Former vBulletin Support
                    • Oct 2004
                    • 26255

                    #10
                    Those look just fine.

                    If you run that query manually in phpMyAdmin do you get an error then?

                    Please don't PM or VM me for support - I only help out in the threads.
                    vBulletin Manual & vBulletin 4.0 Code Documentation (API)
                    Want help modifying your vbulletin forum? Head on over to vbulletin.org
                    If I post CSS and you don't know where it goes, throw it into the additional.css template.

                    W3Schools <- awesome site for html/css help

                    Comment

                    • sowmya002
                      Member
                      • Feb 2014
                      • 80
                      • 5.0.X

                      #11
                      Hi Lynne,

                      I have executed the queries using heidisql since we have hosted the site in a windows system. We do not have phpmyadmin or cpanel.

                      Thanks!
                      Sowmya

                      Comment

                      • sowmya002
                        Member
                        • Feb 2014
                        • 80
                        • 5.0.X

                        #12
                        Hi Lynne,

                        Any thoughts on why did we get the mail?

                        Thanks!
                        Sowmya

                        Comment

                        • Lynne
                          Former vBulletin Support
                          • Oct 2004
                          • 26255

                          #13
                          I really have no idea why you would be getting the email now if you really have all your tables, and the table fields, all set to be the same. Are you sure these are recent emails and they aren't somehow queued on the server and being sent out at a later time?

                          Please don't PM or VM me for support - I only help out in the threads.
                          vBulletin Manual & vBulletin 4.0 Code Documentation (API)
                          Want help modifying your vbulletin forum? Head on over to vbulletin.org
                          If I post CSS and you don't know where it goes, throw it into the additional.css template.

                          W3Schools <- awesome site for html/css help

                          Comment

                          • sowmya002
                            Member
                            • Feb 2014
                            • 80
                            • 5.0.X

                            #14
                            Hi Lynne,

                            I was waiting to see if we are still getting DB errors. We got a recent one and the details are below. From the above conversation related to DB collations etc please let me know if you have any idea what could be the cause for the below mail? This is a recent one.
                            Subject: Database Error

                            Database error in vBulletin 5.1.5:

                            Invalid SQL:
                            DELETE FROM humanverify
                            WHERE `hash` = '' AND `answer` = 'vBulletin;0;xxxx.com/forum/registry/xxx
                            /**humanverify**/;

                            MySQL Error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
                            Error Number : 1267
                            Request Date : Tuesday, March 3rd 2015 @ 03:05:57 AM
                            Error Date : Tuesday, March 3rd 2015 @ 03:05:58 AM
                            Script : http:///forum/registration/registration
                            Referrer : http://xxx.com/forum/register
                            IP Address : 114.109.158.249
                            Username : HoraciSeat
                            Classname : vB_Database_MySQLi
                            MySQL Version :








                            Thanks!
                            Sowmya
                            Last edited by sowmya002; Sun 15 Mar '15, 8:20pm.

                            Comment

                            • sowmya002
                              Member
                              • Feb 2014
                              • 80
                              • 5.0.X

                              #15
                              Hi,
                              I was monitroing the mails and found that the below kind of mails were received very frequently more than one day continuously for few days! The IP address varies in the various mailas. Else contents seems same. Can anybody please help with this. Hope it is not a hack attempt!

                              Database error in vBulletin 5.1.5:

                              Invalid SQL:
                              SELECT * FROM routenew
                              WHERE `prefix` IN ('registerÿGIF89a\'','');

                              MySQL Error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation ' IN '
                              Error Number : 1270
                              Request Date : Wednesday, March 11th 2015 @ 04:45:33 PM
                              Error Date : Wednesday, March 11th 2015 @ 04:45:34 PM
                              Script : http://
                              Referrer :
                              IP Address : 66.249.67.133
                              Username : Guest
                              Classname : vB_Database_MySQLi
                              MySQL Version :

                              Thanks!
                              Sowmya

                              Comment

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