Bug #35309 Strange behaviour using non ascii code as à, é, é, ì, ò, ù
Submitted: 15 Mar 2008 12:28 Modified: 18 May 2008 13:34
Reporter: francesco peretti Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.51a OS:Microsoft Windows
Assigned to: Susanne Ebrecht CPU Architecture:Any
Tags: gui tools, stored procedure, utf8

[15 Mar 2008 12:28] francesco peretti
Some question about utf8.

Used tools:
MySQL 5.0.51a
MySql GUI Tools 5.0.r12
MySQL ODBC Connector 3.51.23
Windows XP SP2, Italian localization
Access 2003 with RDO, Italian version

I prepared a mySQl database using GUI Tools with table and coulumns containing some non ascii code as italian letters à, è, é, ù, ì, ò.
This is an example from the production database:

  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `anagrafe_pmo`;
DROP TABLE IF EXISTS `anagrafe_pmo`.`td_attività`;
CREATE TABLE `anagrafe_pmo`.`td_attività` (
  `Attività` VARCHAR(2) NOT NULL,
  `des_Attività` VARCHAR(32) NOT NULL,
  PRIMARY KEY 		(`Cod_Attività`)

All table and fields are referened correcly using à, ... 
But database file have different name:
td_attività .frm
td_attività .MYD
td_attività .MYI
utf8 code xC3A0 is used instead of 'à'. Is this correct ? 
Some problem can arise if i need to reference a file to copy it from console !

More, I wrote a stored procedure referencing the Cod_Attività field (this is an example)

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
  SELECT Cod_Attività FROM TD_Attività;

and created it using:

1) GUI tools 
2) RDO from Access (because I need to build all the stored procedure automatically from scratch):

    Dim DB As New rdoConnection
    sql="CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()"+vbcrlf
  	sql=sql+"SELECT Cod_Attività FROM TD_Attività;"+vbcrlf
    DB.Connect = ConnectionString
    DB.CursorDriver = rdUseOdbc
    DB.Execute sql

When i use Gui Tools any reference to "à" is stored in proc table as utf8 code xC3A0 and the stored procedure work fine.
On the contrary when i use RDO 'à' is stored as 'à' and: again i can use stored procedure but when I try to select the database with MySQL Browser o MySQL Administrator the error message "Could not fetch stored procedures" is displayed.

I solved encoding, before sending the execute command, in utf8 the stored procedure text but i don't thing this is the right way to work.

How to repeat:
in description
[17 Mar 2008 15:59] Sveta Smirnova
Thank you for the report.

Please provide your ODBC connect options (see http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-dsn-windows.html how to find them). Also please turn on general query log, then try to create procedure using Access and provide general query log to us. I believe this is most likely wrong configured client character set.
[17 Apr 2008 23:00] Bugs System
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".
[6 May 2008 16:47] Susanne Ebrecht
Many thanks for writing bug report.

How the files look like depends on the code page your Windows is using.

For a German Windows, which uses code page 850 it is correct that:
td_attività will get into td_attività .MYD.

Italian, French, German and some other Western European languages have not so different signs so that you often can find one character set for all these languages. That's why I don't expect a great difference here between an Italian and a German Windows.

The tools use UTF8 and your way was correct.

It should also work, when you used: SET NAMES UTF8;
[18 May 2008 13:34] francesco peretti
Thank you for your answer but, unfortunately, only today I read your e-mails.
After the bug I created, I worked with MySQL 5.1.24RC and I noted that the anomaly reported isn't there.
Since the release of MySQL 5.1 was/is imminent, and "my bug" is not crtical, I suspended further investigation.
I intend to resume testing with the official release of MySql and to notify you with the result.
best regards