Announcement

Collapse
No announcement yet.

Invalid characters causing database error emails

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

  • Invalid characters causing database error emails

    I am getting a lot of emails with this database in regards to invalid characters. What could be causing this? (I know the characters are not good, but what can I do to fix this with the database? Something I can change?) As far as I know, this was not a problem before with the old database and system.

    Database error in vBulletin 5.5.6:

    Invalid SQL:

    SELECT routenew.*
    FROM vb3_routenew AS routenew
    WHERE routenew.prefix IN ('portal/forums/forum/sci-race-and-events/club-corner/derby-ho-racing-club/67591-round-26-of-26-–-le-mans-9-november-12','portal/forums/forum/sci-race-and-events/club-corner/derby-ho-racing-club','portal/forums/forum/sci-race-and-events/club-corner','portal/forums/forum/sci-race-and-events','portal/forums/forum','portal/forums','portal') OR ishomeroute = 1

    /**get_best_routes**/;

    MySQL Error : Illegal mix of collations for operation ' IN '
    Error Number : 1271
    Request Date : Thursday, January 30th 2020 @ 02:00:55 AM
    Error Date : Thursday, January 30th 2020 @ 02:00:55 AM
    Script : http://www.slotcarillustrated.com/po...r-12?p=1045619
    Referrer :
    IP Address : 192.169.68.162
    Username : Guest
    Classname : vB_Database_MySQLi
    MySQL Version :


    Stack Trace:
    #0 vB_Database->getErrorData() called in [path]/vb/database.php on line 1207
    #1 vB_Database->halt() called in [path]/vb/database/mysqli.php on line 249
    #2 vB_Database_MySQLi->execute_query() called in [path]/vb/database.php on line 576
    #3 vB_Database->query_read() called in [path]/vb/db/result.php on line 144
    #4 vB_dB_Result->rewind() called in [path]/vb/db/result.php on line 68
    #5 vB_dB_Result->__construct() called in [path]/vb/db/query/stored.php on line 104
    #6 vB_dB_Query_Stored->execSQL() called in [path]/vb/db/assertor.php on line 301
    #7 vB_dB_Assertor->assertQuery() called in [path]/vb/api/route.php on line 127
    #8 vB_Api_Route->getRoute() called in [path]/vb/api/wrapper.php on line 201
    #9 vB_Api_Wrapper->__call() called in /home/admin/web/slotcarillustrated.com/public_html/includes/api/interface/collapsed.php on line 101
    #10 Api_Interface_Collapsed->callApi() called in /home/admin/web/slotcarillustrated.com/public_html/includes/vb5/frontend/routing.php on line 121
    #11 vB5_Frontend_Routing->setRoutes() called in /home/admin/web/slotcarillustrated.com/public_html/includes/vb5/frontend/application.php on line 21
    #12 vB5_Frontend_Application::init() called in /home/admin/web/slotcarillustrated.com/public_html/index.php on line 48

  • #2
    So very interesting... I have been getting a LOT of emails over 60 today and counting about these illegal mix of collations. However I am also noting that some of the errors are referencing to from the old server:

    Database error in vBulletin 5.5.6:

    Invalid SQL:

    SELECT routenew.*
    FROM vb3_routenew AS routenew
    WHERE routenew.prefix IN ('portal/forums/forum/scale-racing/ho-scale-slot-cars/63880-dhorcÂ’s-20th-le-mans-24-hour-race-10-11nov12','portal/forums/forum/scale-racing/ho-scale-slot-cars','portal/forums/forum/scale-racing','portal/forums/forum','portal/forums','portal') OR ishomeroute = 1

    /**get_best_routes**/;

    MySQL Error : Illegal mix of collations for operation ' IN '
    Error Number : 1271
    Request Date : Thursday, January 30th 2020 @ 04:56:17 PM
    Error Date : Thursday, January 30th 2020 @ 04:56:17 PM
    Script : http://www.slotcarillustrated.com/po...ov12?p=1029765
    Referrer :
    IP Address : 192.169.68.162
    Username : Guest
    Classname : vB_Database_MySQLi
    MySQL Version :


    Stack Trace:
    #0 vB_Database->getErrorData() called in [path]/vb/database.php on line 1207
    #1 vB_Database->halt() called in [path]/vb/database/mysqli.php on line 249
    #2 vB_Database_MySQLi->execute_query() called in [path]/vb/database.php on line 576
    #3 vB_Database->query_read() called in [path]/vb/db/result.php on line 144
    #4 vB_dB_Result->rewind() called in [path]/vb/db/result.php on line 68
    #5 vB_dB_Result->__construct() called in [path]/vb/db/query/stored.php on line 104
    #6 vB_dB_Query_Stored->execSQL() called in [path]/vb/db/assertor.php on line 301
    #7 vB_dB_Assertor->assertQuery() called in [path]/vb/api/route.php on line 127
    #8 vB_Api_Route->getRoute() called in [path]/vb/api/wrapper.php on line 201
    #9 vB_Api_Wrapper->__call() called in /home/admin/web/slotcarillustrated.com/public_html/includes/api/interface/collapsed.php on line 101
    #10 Api_Interface_Collapsed->callApi() called in /home/admin/web/slotcarillustrated.com/public_html/includes/vb5/frontend/routing.php on line 121
    #11 vB5_Frontend_Routing->setRoutes() called in /home/admin/web/slotcarillustrated.com/public_html/includes/vb5/frontend/application.php on line 21
    #12 vB5_Frontend_Application::init() called in /home/admin/web/slotcarillustrated.com/public_html/index.php on line 48


    Notice the location of the script is http://www.slotcarillustrated.com/portal/forums/forum.... when I moved this server to its new location on a new server it is in the document root, so when I look at the address bar, it shows: http://www.slotcarillustrated.com/forum..... (everything is located in the public_html folder not in a sub folder. This is perplexing, does this mean people are finding this through stale links? How do I fix this?

    Comment


    • #3
      This happens when you are using one collation on an older version of vBulletin and the default collation of the database is changed. vBulletin will create new tables using the default collation of the database. When tables have different collations, MySQL doesn't know how to sort data properly. All tables and fields need to have the same collation for joins to work properly.

      You can use the dbtools in the do_not_upload folder to change the table collations. Instructions on using these tools are located here: https://forum.vbulletin.com/articles...database-tools
      Translations provided by Google.

      Wayne Luke
      The Rabid Badger - a vBulletin Cloud demonstration site.
      vBulletin 5 API - Full / Mobile
      Vote for your favorite feature requests and the bugs you want to see fixed.

      Comment


      • #4
        So I started running everything as indicated. However the folder dbtools, that came with the version 5.5.6 (build VBFDE63F60), does not have the file serializefix.phar, and it totally buggered up the site. Fortunately I did a backup (as indicated in the file) and I am restoring the database to that backup. Is there some further step by step directions I could follow?
        Last edited by Scaleracing; Thu 30th Jan '20, 5:59pm.

        Comment


        • #5
          So now I am getting different database errors after restoring the database. (And awaiting further instructions).

          Database error in vBulletin 5.5.6:

          Invalid SQL:
          SELECT `styleid`,`parentid`,`title`,`parentlist`,`newstylevars`,`replacements`,`editorstyles`,`us erselect`,`displayorder`,`dateline`,`guid`,`filedataid`,`previewfiledataid`,`styleattribut es` FROM vb3_style ORDER BY `displayorder`;

          MySQL Error : Table 'admin_slotcari.vb3_style' doesn't exist
          Error Number : 1146
          Request Date : Friday, January 31st 2020 @ 03:55:04 AM
          Error Date : Friday, January 31st 2020 @ 04:07:46 AM
          Script : http://www.slotcarillustrated.com/in..._articleid=577
          Referrer :
          IP Address : 192.169.68.162
          Username : Guest
          Classname : vB_Database_MySQLi
          MySQL Version :


          Stack Trace:
          #0 vB_Database->getErrorData() called in [path]/vb/database.php on line 1207
          #1 vB_Database->halt() called in [path]/vb/database/mysqli.php on line 249
          #2 vB_Database_MySQLi->execute_query() called in [path]/vb/database.php on line 576
          #3 vB_Database->query_read() called in [path]/vb/db/result.php on line 144
          #4 vB_dB_Result->rewind() called in [path]/vb/db/result.php on line 68
          #5 vB_dB_Result->__construct() called in [path]/vb/db/query/select.php on line 121
          #6 vB_dB_Query_Select->doSelect() called in [path]/vb/db/query/select.php on line 98
          #7 vB_dB_Query_Select->execSQL() called in [path]/vb/db/assertor.php on line 301
          #8 vB_dB_Assertor->assertQuery() called in [path]/vb/library/style.php on line 1068
          #9 vB_Library_Style->cacheStyles() called in [path]/vb/library/style.php on line 1574
          #10 vB_Library_Style->buildStyleDatastore() called in [path]/vb/library/style.php on line 53
          #11 vB_Library_Style->__construct() called in [path]/vb/library.php on line 46
          #12 vB_Library::instance() called in [path]/vb/api/style.php on line 34
          #13 vB_Api_Style->__construct() called in [path]/vb/api.php on line 213
          #14 vB_Api::instance() called in /home/admin/web/slotcarillustrated.com/public_html/includes/api/interface/collapsed.php on line 88
          #15 Api_Interface_Collapsed->callApi() called in /home/admin/web/slotcarillustrated.com/public_html/includes/vb5/template/stylevar.php on line 108
          #16 vB5_Template_Stylevar->getStylePreference() called in /home/admin/web/slotcarillustrated.com/public_html/includes/vb5/template/stylevar.php on line 37
          #17 vB5_Template_Stylevar->__construct() called in /home/admin/web/slotcarillustrated.com/public_html/includes/vb5/template/stylevar.php on line 29
          #18 vB5_Template_Stylevar::instance() called in /home/admin/web/slotcarillustrated.com/public_html/includes/vb5/frontend/application.php on line 22
          #19 vB5_Frontend_Application::init() called in /home/admin/web/slotcarillustrated.com/public_html/index.php on line 48

          However the table does exist.

          Comment


          • #6
            I don't know why MySQL would report a table doesn't exist when it does. MySQL does support permissions on the table level. You should investigate the permissions in the INFORMATION_SCHEMA.TABLES table on your server to make sure your database user can access that table. If you do not have access to the INFORMATION_SCHEMA database, your hosting provider would have to assist.

            Here is some information on MySQL Permissions:
            https://dev.mysql.com/doc/refman/8.0...-provided.html
            Translations provided by Google.

            Wayne Luke
            The Rabid Badger - a vBulletin Cloud demonstration site.
            vBulletin 5 API - Full / Mobile
            Vote for your favorite feature requests and the bugs you want to see fixed.

            Comment


            • #7
              I'm also getting a similar error email regularty:

              Database error in vBulletin 5.5.6:

              Invalid SQL:

              SELECT routenew.*
              FROM god_routenew AS routenew
              WHERE routenew.prefix IN ('forum/gaming/games/90-tι-παίζετε-αυτό-τον-καιρό/favicon.ico','forum/gaming/games/90-tι-παίζετε-αυτό-τον-καιρό','forum/gaming/games','forum/gaming','forum') OR ishomeroute = 1

              /**get_best_routes**/;

              MySQL Error : Illegal mix of collations for operation 'in'
              Error Number : 1271
              Request Date : Sunday, March 15th 2020 @ 09:27:19 PM
              Error Date : Sunday, March 15th 2020 @ 09:27:19 PM
              Script :
              Referrer :
              IP Address :
              Username : Guest
              Classname : vB_Database_MySQLi
              MySQL Version :


              Stack Trace:
              #0 vB_Database->getErrorData() called in [path]/vb/database.php on line 1207
              #1 vB_Database->halt() called in [path]/vb/database/mysqli.php on line 249
              #2 vB_Database_MySQLi->execute_query() called in [path]/vb/database.php on line 576
              #3 vB_Database->query_read() called in [path]/vb/db/result.php on line 144
              #4 vB_dB_Result->rewind() called in [path]/vb/db/result.php on line 68
              #5 vB_dB_Result->__construct() called in [path]/vb/db/query/stored.php on line 104
              #6 vB_dB_Query_Stored->execSQL() called in [path]/vb/db/assertor.php on line 301
              #7 vB_dB_Assertor->assertQuery() called in [path]/vb/api/route.php on line 127
              #8 vB_Api_Route->getRoute() called in [path]/vb/api/wrapper.php on line 201
              #9 vB_Api_Wrapper->__call() called in /public_html/includes/api/interface/collapsed.php on line 101
              #10 Api_Interface_Collapsed->callApi() called in /public_html/includes/vb5/frontend/routing.php on line 121
              #11 vB5_Frontend_Routing->setRoutes() called in /public_html/includes/vb5/frontend/application.php on line 21
              #12 vB5_Frontend_Application::init() called in /public_html/index.php on line 48
              So the recommendation here is to change Collation to utf8mb4_unicode_ci? I currently use utf8_unicode_ci.

              Comment


              • #8
                Illegal Mix of Collations is a completely different error and unrelated to the original topic of this post.

                Illegal Mix of Collations means one of the fields is not using utf8mb4_unicode_ci. You would have to convert that field. Every table and every text field within each table has to use the same collation or MySQL will error out.
                Translations provided by Google.

                Wayne Luke
                The Rabid Badger - a vBulletin Cloud demonstration site.
                vBulletin 5 API - Full / Mobile
                Vote for your favorite feature requests and the bugs you want to see fixed.

                Comment

                Related Topics

                Collapse

                Working...
                X