Unlock the Power of Exceptional Design with WebGate

In a digital landscape where first impressions are everything, WebGate stands out as a beacon of creativity and innovation. Elevate your online presence with our unparalleled Website UI+UX design services, meticulously crafted to captivate your audience and drive results.

Our Services:

  • Landing Page
  • Business Card Website
  • Multi-Page Site
  • Redesign

How to Change the Encoding of All Tables in a MySQL Database

How to Change the Encoding of All Tables in a MySQL Database

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
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.

query in the DB to change encoding

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:

Comparison encoding, after change

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

You might also like