MySQL Bugs: #44849: Cannot update records in a table with a column of type CHAR(3) UNICODE PRIMARY

Bug #44849	Cannot update records in a table with a column of type CHAR(3) UNICODE PRIMARY
Submitted:	13 May 2009 16:04	Modified:	29 May 2013 6:08
Reporter:	Viorel Preoteasa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1	OS:	Windows
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any

Description:
Cannot update records in a table with a column of type CHAR(3) UNICODE PRIMARY KEY

I tried in Windows, using MFC to update a record in a table which contains a column of type CHAR(3) UNICODE PRIMARY KEY. The operation succeeded but the record was not updated. The myodbc.sql log file shows:

SELECT `currency`.`id`,`currency`.`Code`,`currency`.`Currency`,`currency`.`Country`  FROM `currency` WHERE  (`id` = 76);
SELECT `currency`.`id`,`currency`.`Code`,`currency`.`Currency`,`currency`.`Country`  FROM `currency` WHERE  (`id` = 76);
SELECT `currency`.`id`,`currency`.`Code`,`currency`.`Currency`,`currency`.`Country`  FROM `currency` WHERE  (`id` = 76);
SHOW KEYS FROM `currency`;
UPDATE `currency` SET `Currency`='yy' WHERE `Code`=_latin1'' LIMIT 1;

which is obviously wrong. The column Code has the type mentioned earlier, and the value of the updated record is not '', but something like 'XYZ'

The way I tried in Windows is:

Open the record
Edit
Change the Currency field,
Update

all using the MFC CRecordset class. The same approach worked on
other tables which have a primary key an integer field. The log in this case displayed:

UPDATE `account` SET `Name`='zzz' WHERE `id`='1' LIMIT 1;

How to repeat:
Create a table with a field Code of type CHAR(3) UNICODE PRIMARY KEY, and field Name of type CHAR(12)
Add one record with the value of Code = '123', Name='Test'

Update the table using the MFC CRecordset class:

Open the only record from the table,
Edit
Set the filed name to 'modified'
Update
Close the recordset
Close the database

Viroel,
By "UNICODE", which character set do you mean specifically? Can you attach a test program or ODBC trace log?

Viorel, further more:
  o there is nothing strange in slapping charset introducer as shown here
  o I will need complete DDL/DML statements both for table and for database
  o an output of "show variables like %char%"
  o and a complete test case attached

So far, this looks like you messed something up with charsets and collations.

I did not have much time, but I will try to produce a small sample program which reproduces the bug. Currently I have a big project.

By UNICODE I really want Unicode, i.e. being able to represent any character from any script. The ODBC drivers for Access and Sql Server support wide character string operations. So using these drivers it is possible to write directly a wide character string to the database. I tried the same thing for Mysql, but it did not work, so actually when I write to the mysql database I convert everything to utf8.

The problem seem strange with the sql statement:

UPDATE `currency` SET `Currency`='yy' WHERE `Code`=_latin1'' LIMIT 1;

I am not using directly ODBC functions, but I use them via the class CRecordset from MFC. There I open one specific record (id=...) and then I update it. To be correct the log should display something like:

UPDATE `currency` SET `Currency`='yy' WHERE `Code`='ABC' LIMIT 1;

where 'ABC' is the value of teh Code field of the record I am updating. The
word _latin1 may get in the way, but still the string must be 'ABC' and not empty.

I will produce a sample program in a couple of days. Is using CRecordset from MFC OK for you?

Viorel, there is no "UNICODE" keyword in MySQL.
> UPDATE `currency` SET `Currency`='yy' WHERE `Code`=_latin1'' LIMIT 1;
This looks like you messed up your charset settings and characters that should go into quotes in _latin1'' fail out latin1 bitmap.

I will wait for your test case but my guess is that your configuration is plain wrong. So I'll need everything I asked for, not only test case.

How do I get:

 o will need complete DDL/DML statements both for table and for
database
 o an output of "show variables like %char%"

Viorel:
  o DDL/DML are all commands you used to create database, tables and modify data. For example, a part of it can be obtained by calling "show create table 'your_table'" from mysql command line client.
  o issue "show variables like %char%" from mysql command line client.

Please check MySQL manual and find such answers there.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.