Bug #6322 #HY000Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_
Submitted: 29 Oct 2004 14:54 Modified: 9 Nov 2004 21:51
Reporter: kris Bekkers Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:mysql-connector-net-1.0.1-beta OS:Microsoft Windows (WinXP (no sP2))
Assigned to: Reggie Burnett CPU Architecture:Any

[29 Oct 2004 14:54] kris Bekkers
Description:
Following error occures in the ReadHeader() 

#HY000Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

	StackTrace	"   at MySql.Data.MySqlClient.MySqlDataReader.NextResult() in C:\\_Net\\_MySqlDriver\\MySqlClient\\datareader.cs:line 601\r\n   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) in C:\\_Net\\_MySqlDriver\\MySqlClient\\command.cs:line 425\r\n   at MySql.Data.MySqlClient.MySqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) in C:\\_Net\\_MySqlDriver\\MySqlClient\\command.cs:line 392\r\n   at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)\r\n  .....

How to repeat:
install mysql 5.0.0-alpha-nt-log (clean install)

Try to connect with the mysql connector 1.0.1 beta.
[29 Oct 2004 16:14] kris Bekkers
I believe the problem lies in the configure method (driver.cs) . However I may be wrong.

It writes
string charSet = null;
if (!version.isAtLeast(4,1,0))
{
if (serverProps.Contains( "character_set" ))
charSet = serverProps["character_set"].ToString();
}
else 
{
charSet = "utf8";
cmd.CommandText = "SET NAMES utf8; SET character_set_results=NULL";
cmd.ExecuteNonQuery();
}

However, I think it should write
string charSet = null;
if (version.isAtLeast(4,1,0))                          <<======
{
if (serverProps.Contains( "character_set_server" ))     <<===============
charSet = serverProps["character_set_server"].ToString(); <<=============
}
else 
{
charSet = "utf8";
cmd.CommandText = "SET NAMES utf8; SET character_set_results=NULL";
cmd.ExecuteNonQuery();
}

but again, I maybe wrong.
[2 Nov 2004 12:25] Ringo
I get this error while send the following sql statement
SELECT * FROM table1 WHERE Valid = 'Y' AND UserCode = 'username' AND Password = AES_ENCRYPT('Password','abc')

The Normal Select statement is ok  but use THE Function AES_ENCRYPT OR AES_DECRYPT 
have error.

The Connector Beta 1.0.0 is ok but 1.0.1 error
[5 Nov 2004 14:41] Bob Dankert
Same problem here:

MySql.Data.MySqlClient.MySqlException: #HY000Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MyDBTools.DBExecute.executeMySqlQuery(DBConnect connection, String DBSQL)
[9 Nov 2004 21:51] Reggie Burnett
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

This item is fixed but the user should really understand what is happening with the character sets.  The fix to this is to specify at connection time what character set to use for sending all queries.  Now, if the user wants to insert or use literals in the SQL that should be sent as a different character set, the user will need to use a character set introducer such as _utf8.