Description:
Section 17.2.1 of the MySQL 5.0 manual (dated 2006-01-20, revision 944) states that this rule applies to stored procedure parameters:
"For character data types, if there is a CHARACTER SET clause in the declaration, the specified character set and its default collation are used. If there is no such clause, the database character set and collation are used. (These are given by the values of the character_set_database and collation_database system variables.)"
When a CHARACTER SET clause is omitted, the database character set does not seem to be used.
How to repeat:
Standard installation of MySQL 5.0.18 on Windows XP SP2. Default character set: latin1.
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 | C:\Program Files\... |
+--------------------------+----------------------+
SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
(latin1 is the default character set.)
Create a database with hebrew as the default character set (hebrew chosen arbitrarily):
CREATE DATABASE chartest CHARACTER SET hebrew;
USE chartest;
SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------+
| Variable_name | Value |
+--------------------------+----------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | hebrew |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\... |
+--------------------------+----------------------+
SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | hebrew_general_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
(The database character set has now changed to hebrew.)
CREATE TABLE testtable (A VARCHAR(50));
INSERT INTO testtable VALUES("test");
DELIMITER //
CREATE PROCEDURE showcolls (IN pA VARCHAR(50))
BEGIN
SELECT CHARSET(pA), COLLATION(pA), CHARSET(A), COLLATION(A) FROM testtable;
END
//
DELIMITER ;
CALL showcolls("test");
+-------------+-------------------+------------+-------------------+
| CHARSET(pA) | COLLATION(pA) | CHARSET(A) | COLLATION(A) |
+-------------+-------------------+------------+-------------------+
| latin1 | latin1_swedish_ci | hebrew | hebrew_general_ci |
+-------------+-------------------+------------+-------------------+
CHARSET(pA) should be hebrew - i.e. the character set of the database.
DROP PROCEDURE showcolls;
DELIMITER //
CREATE PROCEDURE showcolls (IN pA VARCHAR(50) CHARACTER SET greek)
BEGIN
SELECT CHARSET(pA), COLLATION(pA), CHARSET(A), COLLATION(A) FROM testtable;
END
//
DELIMITER ;
CALL showcolls("test");
+-------------+------------------+------------+-------------------+
| CHARSET(pA) | COLLATION(pA) | CHARSET(A) | COLLATION(A) |
+-------------+------------------+------------+-------------------+
| greek | greek_general_ci | hebrew | hebrew_general_ci |
+-------------+------------------+------------+-------------------+
In this case, because greek (chosen arbitrarily) was specified in the declaration, it is used as the character set of the parameter.
Suggested fix:
Make stored procedure parameters inherit the character set of the database, if no character set is specified in the SP declaration.
Description: Section 17.2.1 of the MySQL 5.0 manual (dated 2006-01-20, revision 944) states that this rule applies to stored procedure parameters: "For character data types, if there is a CHARACTER SET clause in the declaration, the specified character set and its default collation are used. If there is no such clause, the database character set and collation are used. (These are given by the values of the character_set_database and collation_database system variables.)" When a CHARACTER SET clause is omitted, the database character set does not seem to be used. How to repeat: Standard installation of MySQL 5.0.18 on Windows XP SP2. Default character set: latin1. 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 | C:\Program Files\... | +--------------------------+----------------------+ SHOW VARIABLES LIKE 'coll%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ (latin1 is the default character set.) Create a database with hebrew as the default character set (hebrew chosen arbitrarily): CREATE DATABASE chartest CHARACTER SET hebrew; USE chartest; SHOW VARIABLES LIKE 'char%'; +--------------------------+----------------------+ | Variable_name | Value | +--------------------------+----------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | hebrew | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | C:\Program Files\... | +--------------------------+----------------------+ SHOW VARIABLES LIKE 'coll%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | hebrew_general_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ (The database character set has now changed to hebrew.) CREATE TABLE testtable (A VARCHAR(50)); INSERT INTO testtable VALUES("test"); DELIMITER // CREATE PROCEDURE showcolls (IN pA VARCHAR(50)) BEGIN SELECT CHARSET(pA), COLLATION(pA), CHARSET(A), COLLATION(A) FROM testtable; END // DELIMITER ; CALL showcolls("test"); +-------------+-------------------+------------+-------------------+ | CHARSET(pA) | COLLATION(pA) | CHARSET(A) | COLLATION(A) | +-------------+-------------------+------------+-------------------+ | latin1 | latin1_swedish_ci | hebrew | hebrew_general_ci | +-------------+-------------------+------------+-------------------+ CHARSET(pA) should be hebrew - i.e. the character set of the database. DROP PROCEDURE showcolls; DELIMITER // CREATE PROCEDURE showcolls (IN pA VARCHAR(50) CHARACTER SET greek) BEGIN SELECT CHARSET(pA), COLLATION(pA), CHARSET(A), COLLATION(A) FROM testtable; END // DELIMITER ; CALL showcolls("test"); +-------------+------------------+------------+-------------------+ | CHARSET(pA) | COLLATION(pA) | CHARSET(A) | COLLATION(A) | +-------------+------------------+------------+-------------------+ | greek | greek_general_ci | hebrew | hebrew_general_ci | +-------------+------------------+------------+-------------------+ In this case, because greek (chosen arbitrarily) was specified in the declaration, it is used as the character set of the parameter. Suggested fix: Make stored procedure parameters inherit the character set of the database, if no character set is specified in the SP declaration.