MikeW
26th May 2007, 06:47 PM
Hi,
I had a problem with SupportCenter - I had set up notifications, but wasn't
receiving any. If I added something into "Mail Gateway", I *did* get those.
I think I managed to get it working by:
Step 1 - I turned on debug level, and put stuff into the debug log.
Step 2 - I saw SQL in the debug log, so manually took the (very long) select statement out, and ran it manually.
This created an error:
ERROR 1270 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (utf8_general_ci,IMPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'concat'
I assume that this was happening in the application, even though it wasn't appearing in the logs.
I *hate* MySQL when it gets into character-set difficulties!
Step 3 - Investigated lots. Looks like the DB I used for this was set with a
default of "utf8", while the client connection I typed the command
in manually was defined to have a default of "latin1".
Step 4 - Changed the default character set of the manual client session:
mysql> set names 'utf8';
The SQL select worked, and gave me the expected notification.
Step 5 - Changed the default character set of the manual client back to 'latin1', as I assume that is how the SupportCenter PHP is connecting.
Step 6 - Use commands like:
mysql> show full columns from tickets;
mysql> show table status;
I could see that many tables were set to use the character set of UTF-8.
Step 7 - Used the following command to convert the tables used in the SQL Query:
mysql> ALTER TABLE tickets CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE user_notifications CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE queue_agents CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE queues CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE users CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE mails CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE mail_users CONVERT TO CHARACTER SET latin1;
A new ticket now caused the notification to work correctly.
Step 8 - Of course, a few other things were broken because of character-set problems, so I then had to convert the remaining tables.
I guess the root cause of this is that my MySQL was configured with a default character
set of UTF-8, at least at database level, which was probably picked up from the
system-level setting.
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
I guess the table-creation script will have allowed all the tables to default to
using UTF-8, while the specification of string literals will take their charset from the
client connection; I guess this just picks up a default from PHP?
Cheers,
Mike
I had a problem with SupportCenter - I had set up notifications, but wasn't
receiving any. If I added something into "Mail Gateway", I *did* get those.
I think I managed to get it working by:
Step 1 - I turned on debug level, and put stuff into the debug log.
Step 2 - I saw SQL in the debug log, so manually took the (very long) select statement out, and ran it manually.
This created an error:
ERROR 1270 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (utf8_general_ci,IMPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'concat'
I assume that this was happening in the application, even though it wasn't appearing in the logs.
I *hate* MySQL when it gets into character-set difficulties!
Step 3 - Investigated lots. Looks like the DB I used for this was set with a
default of "utf8", while the client connection I typed the command
in manually was defined to have a default of "latin1".
Step 4 - Changed the default character set of the manual client session:
mysql> set names 'utf8';
The SQL select worked, and gave me the expected notification.
Step 5 - Changed the default character set of the manual client back to 'latin1', as I assume that is how the SupportCenter PHP is connecting.
Step 6 - Use commands like:
mysql> show full columns from tickets;
mysql> show table status;
I could see that many tables were set to use the character set of UTF-8.
Step 7 - Used the following command to convert the tables used in the SQL Query:
mysql> ALTER TABLE tickets CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE user_notifications CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE queue_agents CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE queues CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE users CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE mails CONVERT TO CHARACTER SET latin1;
mysql> ALTER TABLE mail_users CONVERT TO CHARACTER SET latin1;
A new ticket now caused the notification to work correctly.
Step 8 - Of course, a few other things were broken because of character-set problems, so I then had to convert the remaining tables.
I guess the root cause of this is that my MySQL was configured with a default character
set of UTF-8, at least at database level, which was probably picked up from the
system-level setting.
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
I guess the table-creation script will have allowed all the tables to default to
using UTF-8, while the specification of string literals will take their charset from the
client connection; I guess this just picks up a default from PHP?
Cheers,
Mike