by clear » Fri Oct 05, 2007 8:40 pm
Well, I'm a little closer to fixing the problem, but still not quite there. I noticed that MYSQL variables were still set to the old Latin (default) settings, so the database was created using the default Latin settings as well. I changed this around and ensured that all variables are now set to UTF8/utf8_unicode_ci (at least all that I can find):
mysql> show variables like 'col%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
mysql> show create database wowroster;
+-----------+--------------------------------------------------------------------------------------------+
| Database | Create Database |
+-----------+--------------------------------------------------------------------------------------------+
| wowroster | CREATE DATABASE `wowroster` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |
+-----------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table roster_guild;
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| roster_guild | CREATE TABLE `roster_guild` (
`guild_id` int(11) unsigned NOT NULL auto_increment,
`guild_name` varchar(64) NOT NULL default '',
`server` varchar(32) NOT NULL default '',
`region` varchar(2) NOT NULL default '',
`faction` varchar(32) NOT NULL default '',
`factionEn` varchar(32) NOT NULL default '',
`guild_motd` varchar(255) NOT NULL default '',
`guild_num_members` int(11) NOT NULL default '0',
`guild_num_accounts` int(11) NOT NULL default '0',
`update_time` datetime default NULL,
`GPversion` varchar(6) NOT NULL default '0.0.0',
`DBversion` varchar(6) NOT NULL default '0.0.0',
`guild_info_text` mediumtext,
PRIMARY KEY (`guild_id`),
KEY `guild` (`guild_name`,`server`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Now, I can manually enter the problematic UPDATE request from the commandline and it works fine:
mysql> UPDATE `roster_menu` SET `config` = CONCAT(`config`,':','b',LAST_INSERT_ID()) WHERE `section` = 'guild' LIMIT 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from roster_menu;
+-----------+---------+----------+
| config_id | section | config |
+-----------+---------+----------+
| 1 | util | b1:b2:b3 |
| 2 | realm | |
| 3 | guild | :b0 |
| 4 | char | |
+-----------+---------+----------+
4 rows in set (0.00 sec)
However, when trying to use the web interface to execute the same command I still receive the standard collate error (as shown above in DaarianneDarkmoon's 2nd error post).
So, now the question: is there another setting in MYSQL that needs to set to UTF8 to make this work from the web interface? Or is there some other thing I'm missing to make this work?
Thanks in advance for all the help!!
