| Bug #6215 | Using stored procedures with previous SET NAMES .. causes collation mix.. | ||
|---|---|---|---|
| Submitted: | 22 Oct 2004 14:58 | Modified: | 23 Oct 2004 14:02 |
| Reporter: | Levap Aretnyd | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | OS: | ||
| Assigned to: | Reggie Burnett | CPU Architecture: | Any |
[22 Oct 2004 19:05]
Reggie Burnett
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: Pavel Why are you needed to run SET NAMES? There are significant issues to consider when supporting sending SET NAMES and changing the character set midstream.
[23 Oct 2004 9:20]
Levap Aretnyd
I need to call SET NAMES x, because without it my values are not inserted to the db with correct coding. SET CONNECTION x issued by connector is not enough. If I cared only about insertions (not results sets) I could of course only issue "SET collation_connection = @@collation_database" or "SET character_set_connection = utf8" but that is exactly source of the problem - that then causes collation mix in that query. I think lots of pople will need to call this and this problem will arise.
[23 Oct 2004 14:02]
Reggie Burnett
Then you'll be glad to hear that beta 2 of the connector has been changed to call set names internally instead of set character_set_connection. :-)
[23 Oct 2004 14:59]
Levap Aretnyd
I have mixed up the first SET in my previous comment, but you seem to understand what I wanted to say. Connector 1.0.0 issues "SET character_set_client = utf8" (instead of SET CONNECTION x i wrote). If .NET Connector 2 beta uses SET NAMES x instead - and stored procedure calls are working, than everything is fine. Thanks
Description: The call to stored procedure fails after "SET NAMES x" to charater set other than in field mysql.proc.name or mysql.proc.db (default latin1 - latin1_swedish_ci) causes "Illegal mix of collations..." error in StoredProcedure.GetParameterList(). Sql "SELECT param_list FROM mysql.proc WHERE db={0}db AND name={0}name" fails because `db` and `name` comparations are invalid (different character sets) - this is checked since 4.1. How to repeat: See description. Suggested fix: One way to fix this is rewrite the sql query to "SELECT `param_list` FROM `mysql`.`proc` WHERE `db`=_latin1{0}db AND `name`=_latin1{0}name" - insert '_latin2' before string literals (parameters) - this works because default character set for fields `db` and `name` in `mysql`.`proc` is 'latin1'. But because character set for those fields can be changed to something more suitable and special characters would propably not work, I think that query "SELECT `param_list` FROM `mysql`.`proc` WHERE CONVERT(`db` USING utf8)=CONVERT({0}db USING utf8) AND CONVERT(`name` USING utf8)=CONVERT({0}name USING utf8)" - with explicit converts is better.