Some database errors in vB 5.6.0

[elektro-kot]

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

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

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

[Wayne Luke]

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.

[elektro-kot]

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

[Wayne Luke]

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.

[elektro-kot]

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.

[elektro-kot]

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?