[SQL] Modify setup.sql to include collation/charset setting for tables

if ( version_compare(mysql_get_server_info(), '4.1.0', '>=') ) {...

CREATE TABLE IF NOT EXISTS `site_content` (
`id` int(10) NOT NULL auto_increment,
`type` varchar(20) NOT NULL default ’document’,
`contentType` varchar(50) NOT NULL default ’text/html’,
.....
`privatemgr` tinyint(1) NOT NULL default ’0’ COMMENT ’Private manager document’,
`content_dispo` tinyint(1) NOT NULL default ’0’ COMMENT ’0-inline, 1-attachment’,
`hidemenu` tinyint(1) NOT NULL DEFAULT ’0’ COMMENT ’Hide document from menu’,
PRIMARY KEY (`id`),
KEY `id` (`id`),
KEY `parent` (`parent`),
KEY aliasidx (alias),
FULLTEXT KEY `content_ft_idx` (`pagetitle`,`description`,`content`)
) CHARACTER SET utf8 COLLATE utf8_unicode_ci TYPE=MyISAM COMMENT=’Contains the site document tree.’;

Since the database collation and associated charset are what is used by MODx to do a SET CHARACTER SET, it is not necessary to do this. The problem occurs when you choose a utf8 collation during MODx installation, and install into an existing database that is already set to the server default, which for MySQL is latin1_swedish_ci (don’t ask). If you let MODx create the database, there is no problem, otherwise, you must alter the database to have a utf8 collation/charset before installing the MODx tables using the now established utf8 database_connection_charset value (set when you selected a utf8 collation). So essentially, we want the tables to get created with the database charset/collation settings rather than specify them explicitly.

An alternate idea would be to simply confirm that the database, if already existing, matches the user selected value, presenting a warning if not.

ALTER DATABASE db_name CHARSET utf8;

SET NAMES 'utf8';

httpd.conf:
AddCharset UTF-8 .utf8
AddDefaultCharset UTF-8

php.ini
default_charset = "utf-8"

my.cnf
character-set-server=utf8
default-collation=utf8_general_ci

I think everyone gets mixed up trying to display their database content stored as latin1 (or whatever) using UTF-8 on the site. This will simply never work properly; if you expect content encoded as UTF-8, then use utf8 in the database from the start or you will end up having to convert content when you decide to switch.

Once that’s done, is there still an issue with settings in httpd.conf, php.ini, or my.cnf?

Since the database collation and associated charset are what is used by MODx to do a SET CHARACTER SET, it is not necessary to do this.

Database Note: MODx uses a MySQL database. You will need the username and password to your database to install MODx. If your database user does not have database creation permissions on the server, you will also need to have a database already created for MODx to use.

Either I have misunderstood the purpose of "MySQL charset:" and "MySQL connection collation:", or it is misleading. But now I understand why this was happening.