What is this database error and how to fix?

[coshotgun]

Database error in vBulletin 5.5.4:

Invalid SQL:

SELECT routenew.*
FROM forum2routenew AS routenew
WHERE routenew.prefix IN ('forum/message-board/general-discussion-with-sub-forms-for-recent-purchase-looking-at-to-buy-pets/266188-service-experience-with-lowryÂ’s-in-nm-or-desert-autoplex-in-mesa','forum/message-board/general-discussion-with-sub-forms-for-recent-purchase-looking-at-to-buy-pets','forum/message-board','forum') OR ishomeroute = 1

/**get_best_routes**/;

MySQL Error : Illegal mix of collations for operation ' IN '
Error Number : 1271
Request Date : Sunday, September 29th 2019 @ 07:50:37 PM
Error Date : Sunday, September 29th 2019 @ 07:50:37 PM
Script : https://www.nroa2003.com/forum/forum...-mesa?p=266236
Referrer :
IP Address : 54.36.149.12
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 163
#8 vB_Api_Route->getRoute() called in [path]/vb/api/wrapper.php on line 197
#9 vB_Api_Wrapper->__call() called in /home/nroacom/public_html/forum/includes/api/interface/collapsed.php on line 101
#10 Api_Interface_Collapsed->callApi() called in /home/nroacom/public_html/forum/includes/vb5/frontend/routing.php on line 121
#11 vB5_Frontend_Routing->setRoutes() called in /home/nroacom/public_html/forum/includes/vb5/frontend/application.php on line 21
#12 vB5_Frontend_Application::init() called in /home/nroacom/public_html/forum/index.php on line 48

[Wayne Luke]

The collation is how MySQL determines how to sort various text fields. This error occurs when the default collation is changed on your database after installing vBulletin.

Some fields in your database have a different collation than other fields. You need to make sure they all have the same collation. What languages are you using on the site because the solution will be different based on that question.

[coshotgun]

English

[coshotgun]

Please help on this. What is the correct collation to use for both the database and the tables? Right now the database is set to default utf8_general_ci and some of the tables in the database are set to latin1_swedish_ci. Some of the tables have nothing at all for the collation.

[Wayne Luke]

For your immediate situation, you need to pick either utf8_general_ci or latin1_swedish_ci and make all the tables have the same collation. Each text field (char, varchar, text, mediumtext, etc...) needs to have the same collation as well.

---

The best collations would be utf8mb_general_ci or utf8_unicode_ci. Unless you need to support diacritic and umlaut sorting, then you would use utf8mb4_swedish_ci. utf8mb4 is needed if you want to support the UTF-8 Emoji standard in the future. Those are all the smilies you can select on your phone keyboard. Unfortunately the emoji aren't compatible with utf8_general_ci because utf8 characters use 2-3 bytes for storage and utf8mb4 uses 4 bytes for the storage of each character.

The utf8tablefix.phar file in your vBulletin Package's /do_not_upload/dbtools directory will convert your database character set and collation.

1. Upload this file and the corresponding vbutils_config.php.new to your forum directory.
2. Rename vbutils_config.php.new to vbutils_config.php.
3. Edit vbutils_config.php with your database information.
4. Turn off your forums in the AdminCP.
5. Create a database backup, just in case.
6. Run utf8tablefix.phar from the command line of your server with this command php utf8tablefix.phar.
7. Turn your forums back on.