MySQL Bugs: #6322: #HY000Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general

Bug #6322	#HY000Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_
Submitted:	29 Oct 2004 16:54	Modified:	9 Nov 2004 22:51
Reporter:	kris Bekkers
Status:	Closed
Category:	Connector/Net	Severity:	S1 (Critical)
Version:	mysql-connector-net-1.0.1-beta	OS:	Microsoft Windows (WinXP (no sP2))
Assigned to:	Reggie Burnett	Target Version:

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.

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.

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

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)

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.