The error text normally looks like this:
Execution of a query to the database failed - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'if'
Warning: Cannot modify header information - headers already sent by (output started at /sata1/home/users/foldername/www/sitename.com/manager/includes/header.inc.php:7) in /sata1/home/users/shenevmer/www/sitename.com/manager/includes/document.parser.class.inc.php on line 2732
« MODx Parse Error »
| MODx encountered the following error while attempting to parse the requested resource: | ||
| « Execution of a query to the database failed - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'if' » | ||
| SQL: SELECT `db_she`.`modx_site_templates`.templatename as name, `db_she`.`modx_site_templates`.id, `db_she`.`modx_site_templates`.description, `db_she`.`modx_site_templates`.locked, if(isnull(`db_she`.`modx_categories`.category),'Uncategorized',`db_she`.`modx_categories`.category) as category FROM `db_she`.`modx_site_templates` left join `db_she`.`modx_categories` on `db_she`.`modx_site_templates`.category = `db_she`.`modx_categories`.id ORDER BY 5,1
[Copy SQL to ClipBoard] |
||
| Parser timing | ||
| MySQL: | 0,0442 s | (0 Requests) |
| PHP: | 1333655752,1659 s | |
| Total: | 1333655752,2100 s | |
If you go into database management (e.g. MySQL), you can see the problem like this:
Even though you set the encoding in the settings during installation utf8_general_ci
How to Change the Encoding of All Tables in a MySQL Database
Since there can be dozens, even hundreds of tables in a database, and changing the comparison encoding parameter manually in each one is redundant work. There's a solution.
So, MySQL server version 5.0 and above can execute such a query:
SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as sqlcode
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'db_name'
ORDER BY 1
just replace db_name with the name of your DB.
Press OK
The result will be the script we need to copy into this window and execute again.
We will get something like:
ALTER TABLE `db_she`.`modx_active_users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `db_she`.`modx_categories` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `db_she`.`modx_documentgroup_names` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; .... .... ALTER TABLE `db_she `.`modx_document_groups` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `db_she`.`modx_event_log` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `db_she`.`modx_keyword_xref` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `db_she`.`modx_manager_log` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Select this code - insert it into the DB query window and press OK.
As a result, we get for the entire database, the comparison parameter instead of latin1_swedish_ci --> utf8_general_ci, and it will look like this:
Also, in MODx, you may encounter errors like: Cannot modify header information - headers already sent by. The problem lies in the incorrectly set comparison parameter of your database. For instance, if the encoding is set to utf8_general_ci and if you access PHP MyAdmin - and go into one of the tables, you will see Collation: latin1_swedish_ci
