I’ve got my script to convert databases to utf8 (or any other character set) working, more or less, but one thing is still driving me crazy.
The script generates many lines of SQL queries to paste into PhpMyAdmin. If I do an Export of the DB before and after, the before tables look like this:
--
-- Table structure for table `modx_active_users`
--
CREATE TABLE IF NOT EXISTS `modx_active_users` (
`internalKey` int(9) NOT NULL default '0',
`username` varchar(50) collate latin1_general_ci NOT NULL default '',
`lasthit` int(20) NOT NULL default '0',
`id` int(10) default NULL,
`action` varchar(10) collate latin1_general_ci NOT NULL default '',
`ip` varchar(20) collate latin1_general_ci NOT NULL default '',
PRIMARY KEY (`internalKey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Contains data about active users.';
After the conversion, the same table looks like this in the export:
--
-- Table structure for table `modx_active_users`
--
CREATE TABLE IF NOT EXISTS `modx_active_users` (
`internalKey` int(9) NOT NULL default '0',
`username` varchar(50) NOT NULL default '',
`lasthit` int(20) NOT NULL default '0',
`id` int(10) default NULL,
`action` varchar(10) NOT NULL default '',
`ip` varchar(20) NOT NULL default '',
PRIMARY KEY (`internalKey`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Contains data about active users.';
The charset and collation are missing for individual text columns and the table collation is also missing.
Here’s the relevant SQL:
ALTER TABLE modx_active_users MODIFY username BLOB;
ALTER TABLE modx_active_users MODIFY action BLOB;
ALTER TABLE modx_active_users MODIFY ip BLOB;
ALTER DATABASE dummy CHARSET utf8 COLLATE utf8_general_ci;
ALTER TABLE modx_active_users MODIFY username varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE modx_active_users MODIFY action varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE modx_active_users MODIFY ip varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE modx_active_users DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
All the character sets and collations are set correctly when I look in the database (after conversion) and the data in each table’s text fields appears to be converted to utf8.
I can’t figure out why the explicit charset and collation settings aren’t there in the export after conversion. I’ve tried everything I can think of to put them there, but nothing has worked.
MODX Staff
