MySQL Bugs: #6667: rs.update error

Bug #6667	rs.update error
Submitted:	16 Nov 2004 15:47	Modified:	3 Jul 2007 19:08
Reporter:	Ender Li	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.10	OS:	Windows (Windows2000)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
I installed "mysql-4.1.7-win.zip" & "MyODBC-3.51.10-x86-win-32bit.msi" on Windows2000. When I run "MySQLInstanceConfig.exe", I select "big5" at "Manual Selected Default Character Set/Collation".
I run the sample code which was posted on "http://dev.mysql.com/doc/mysql/en/ADO_-_rs.addNew.html" under "Microsoft ActiveX Data Objects 2.8 Library".
When the program run to the block: 

  'rs update
  rs.Open "SELECT * FROM my_ado"
  rs!Name = "update"
  rs!txt = "updated-row"
  rs.Update
  rs.Close

,get an error message:

'-2147217871(80040e31)
[MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]Illegal mix of collations(big5_chinese_ci,IMPLCIT) and (latin1_swedish_ci,COERCIBLE) for operation '='

If I select "Standard Character Set" at "MySQLInstanceConfig.exe" setup screen, everything is O.K.

How to repeat:
Just select "big5" at "Manual Selected Default Character Set/Collation" when 
run "MySQLInstanceConfig.exe", the error must be back!

Could you please show for us the output of the below 2 commands
using the mysql.exe client:

show create table my_ado\G

show variables like "%character%";

Thanks in advance.

mysql> show create table my_ado\G
ERROR 1046 (3D000): No database selected
mysql> use test
Database changed
mysql> show create table my_ado\G
*************************** 1. row ***************************
       Table: my_ado
Create Table: CREATE TABLE `my_ado` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(20) default NULL,
  `txt` text,
  `dt` date default NULL,
  `tm` time default NULL,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=big5
1 row in set (0.01 sec)

mysql> show variables like "%character%";
+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | latin1
     |
| character_set_connection | latin1
     |
| character_set_database   | big5
     |
| character_set_results    | latin1
     |
| character_set_server     | big5
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 4.1\share\chars
ets/ |
+--------------------------+----------------------------------------------------
-----+
7 rows in set (0.00 sec)

To use a different character set with Connector/ODBC, it is necessary to specify it as part of the DSN or connection string, which is a new feature in the upcoming 3.51.17.

Noted in 3.51.17 changelog.

It is now possible to specify a different character set as part of
the DSN or connection string.