Announcement

Collapse
No announcement yet.

Some database errors in vB 5.6.0

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

  • Some database errors in vB 5.6.0

    Hello!
    Sometimes (several times a week, in the morning) I get several error reports on my eMail
    Error numbers - 1270 and 1271

    What could be the problem?

    Add in more detail:

    vBulletin Version - 5.6.0
    PHP Version - 7.3.16.
    MySQL Version - 10.4.12-MariaDB.
    Any Addons installed - NO
    Does the issue occur in a default style? - YES
    Does the issue occur using the English language provided? - NO, rus language


    Error Number: 1271

    Code:
    Database error in vBulletin 5.6.0:
    
    Invalid SQL:
    
    SELECT routenew.*
    FROM routenew AS routenew
    WHERE routenew.prefix IN ('blogs/����������-�������-50-�-����/469302-����������-���-������','blogs/����������-�������-50-�-����','blogs') OR ishomeroute = 1
    
    /**get_best_routes**/;
    
    MySQL Error : Illegal mix of collations for operation 'in'
    Error Number : 1271
    Request Date : Wednesday, May 6th 2020 @ 04:46:07 AM
    Error Date : Wednesday, May 6th 2020 @ 04:46:07 AM
    Script :
    Referrer :
    IP Address : 91.151.176.73
    Username : Гость
    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 293
    #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 /var/www/xxxxxxx/data/www/xxxxxxx.xx/includes/api/interface/collapsed.php on line 101
    #10 Api_Interface_Collapsed->callApi() called in /var/www/xxxxxxx/data/www/xxxxxxx.xx/includes/vb5/frontend/routing.php on line 121
    #11 vB5_Frontend_Routing->setRoutes() called in /var/www/xxxxxxx/data/www/xxxxxxx.xx/includes/vb5/frontend/application.php on line 21
    #12 vB5_Frontend_Application::init() called in /var/www/xxxxxxx/data/www/xxxxxxx.xx/index.php on line 48

    Error Number: 1270

    Code:
    Database error in vBulletin 5.6.0:
    
    Invalid SQL:
    
    SELECT routenew.*
    FROM routenew AS routenew
    WHERE routenew.prefix IN ('blogs/���-������-���','blogs') OR ishomeroute = 1
    
    /**get_best_routes**/;
    
    MySQL Error : Illegal mix of collations (utf8mb4_general_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation 'in'
    Error Number : 1270
    Request Date : Wednesday, May 6th 2020 @ 04:46:20 AM
    Error Date : Wednesday, May 6th 2020 @ 04:46:20 AM
    Script :
    Referrer :
    IP Address : 91.151.176.73
    Username : Гость
    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 293
    #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 /var/www/xxxxxxx/data/www/xxxxxxx.xx/includes/api/interface/collapsed.php on line 101
    #10 Api_Interface_Collapsed->callApi() called in /var/www/xxxxxxx/data/www/xxxxxxx.xx/includes/vb5/frontend/routing.php on line 121
    #11 vB5_Frontend_Routing->setRoutes() called in /var/www/xxxxxxx/data/www/xxxxxxx.xx/includes/vb5/frontend/application.php on line 21
    #12 vB5_Frontend_Application::init() called in /var/www/xxxxxxx/data/www/xxxxxxx.xx/index.php on line 48
    Last edited by elektro-kot; Wed 6 May '20, 12:12am.

  • #2
    You need to make sure that all the tables and fields in your database use the same character set and collations. It would seem that when you installed vBulletin previously that the character set and collation was one value and the default value is different or has changed. Or you changed the value on some tables and fields and not others. It is recommended that the utf8mb4 character set and an appropriate collation is used. You can see the best practices recommendations for MySQL here: https://forum.vbulletin.com/articles...best-practices

    There are provided scripts to make this change for you. They are located in /do_not_upload/dbtools and must be run from the server command line. You can find the instructions for them here: https://forum.vbulletin.com/articles...database-tools

    If you do not have access to the command line on your server, then your hosting provider may be able to run the scripts for you or they may have their own.
    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


    • #3
      Originally posted by Wayne Luke View Post
      You need to make sure that all the tables and fields in your database use the same character set and collations. It would seem that when you installed vBulletin previously that the character set and collation was one value and the default value is different or has changed. Or you changed the value on some tables and fields and not others. It is recommended that the utf8mb4 character set and an appropriate collation is used.
      Thanks for your reply, Wayne.

      Tell me please, should be i run the script (or change something else) if the SHOW VARIABLES LIKE 'character \ _set \ _%' query returns the following result?
      character_set_client utf8mb4
      character_set_connection utf8mb4
      character_set_database utf8
      character_set_filesystem binary
      character_set_results utf8mb4
      character_set_server utf8
      character_set_system utf8
      Last edited by elektro-kot; Wed 6 May '20, 9:12am.

      Comment


      • #4
        That doesn't tell me anything about each individual table or the individual fields within the tables. You would have to run the conversion script at least to make sure every single table and every text field in those tables has the same character set and collation. If you do not, then you will continue to receive these database errors for eternity.
        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


        • #5
          Originally posted by Wayne Luke View Post
          You would have to run the conversion script at least to make sure every single table and every text field in those tables has the same character set and collation. If you do not, then you will continue to receive these database errors for eternity.
          Okay, Wayne, I'll run the script and let you know the result.
          Thank for your help.

          Comment


          • #6
            Originally posted by Wayne Luke View Post
            You would have to run the conversion script at least to make sure every single table and every text field in those tables has the same character set and collation.
            Ok, I think the problem is resolved.
            There was a difference in several tables (two tables in utf8, and one in utf8_unicode_ci), now everything is in utf8mb4.

            Should I change something after that in the /core/includes/config.php file now?

            Comment

            Related Topics

            Collapse

            Working...
            X