Hello,
I am experimenting with character sets and have encountered a problem that I do not understand.
I have installed MySQL 5.0.18 onto a Windows XP SP2 machine and used the default character set of latin1:
mysql> SHOW VARIABLES LIKE 'vers%';
+-------------------------+-------------------------------+
| Variable_name | Value |
+-------------------------+-------------------------------+
| version | 5.0.18-nt |
| version_comment | MySQL Community Edition (GPL) |
| version_compile_machine | ia32 |
| version_compile_os | Win32 |
+-------------------------+-------------------------------+
4 rows in set (0.00 sec)
When connecting to the server as root the character and collation variables are as follows:
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------+
| Variable_name | Value |
+--------------------------+----------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:\Program Files\... |
+--------------------------+----------------------+
7 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
I then USE a database which has a default collation of latin1_general_cs. The collation_database variable is updated accordingly:
mysql> USE UserManagement;
Database changed
mysql> SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_general_cs |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
I have a table called "tblUser", and the table itself, along with all its text columns, have collation "latin1_general_cs":
mysql> SHOW CREATE TABLE tblUser;
[snip]
| tblUser | CREATE TABLE `tbluser` (
`ID` int(11) NOT NULL auto_increment,
`Username` varchar(20) collate latin1_general_cs NOT NULL,
`FirstName` varchar(50) collate latin1_general_cs NOT NULL,
`LastName` varchar(50) collate latin1_general_cs NOT NULL,
`Email` varchar(50) collate latin1_general_cs NOT NULL,
`Password` varchar(20) collate latin1_general_cs NOT NULL,
`Active` int(1) NOT NULL default '1',
PRIMARY KEY (`ID`),
UNIQUE KEY `unq_tblUser_Username` (`Username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs |
[snip]
I have a stored procedure which determines if a username and password are valid:
CREATE PROCEDURE uspCheckUserPassword (IN pUsername VARCHAR(20),
IN pPassword VARCHAR(20), OUT result INT)
BEGIN
DECLARE userid_test INT;
SELECT ID INTO userid_test FROM tblUser WHERE Username = pUsername
AND Password = pPassword AND Active = 1;
IF userid_test IS NULL THEN
SET result = 0;
ELSE
SET result = 1;
END IF;
END
If I run the query in this SP directly, it executes fine:
mysql> SELECT ID FROM tblUser WHERE Username = "richard" AND Password = "rich" AND Active = 1;
+----+
| ID |
+----+
| 40 |
+----+
1 row in set (0.00 sec)
However, if I execute the stored procedure, I get an "Illegal mix of collations" error:
mysql> CALL uspCheckUserPassword("richard", "rich", @a);
ERROR 1267 (HY000): Illegal mix of collations
(latin1_general_cs,IMPLICIT)
and (latin1_swedish_ci,IMPLICIT) for operation '='
I know there was a bug in MySQL where parameters were being passed into SPs as BINARY, but this was fixed in 5.0.18, which I am using. More details about that bug can be found here:
http://bugs.mysql.com/bug.php?id=13909
I've placed the query SELECT CHARSET(pUsername) into my SP, and the result is always latin1 (as expected - bug 13909 is fixed). If I put SELECT COLLATION(pUsername) into the SP instead, the result is as follows:
mysql> CALL uspCheckUserPassword("richard", "rich", @a);
+----------------------+
| COLLATION(pUsername) |
+----------------------+
| latin1_swedish_ci |
+----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Even if I specify that the parameter is latin1_general_cs, MySQL seems to ignore this and gives latin1_swedish_ci to my SP:
mysql> call uspCheckUserPassword(_latin1 "richard" COLLATE latin1_general_cs, "rich", @a);
+----------------------+
| COLLATION(pUsername) |
+----------------------+
| latin1_swedish_ci |
+----------------------+
1 row in set (0.00 sec)
Is this a bug? Is MySQL using the default collation for the character set that I am passing in, rather than the specific collation that I specify?
I get the same result (latin1_swedish_ci) even if I set the collation variables to latin1_general_cs:
mysql> show variables like 'coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_general_cs |
| collation_database | latin1_general_cs |
| collation_server | latin1_general_cs |
+----------------------+-------------------+
3 rows in set (0.00 sec)
mysql> call uspCheckUserPassword(_latin1 "richard" COLLATE latin1_general_cs, "rich", @a);
+----------------------+
| COLLATION(pUsername) |
+----------------------+
| latin1_swedish_ci |
+----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Thanks
Richard Fearn