MySQL Bugs: #35309: Strange behaviour using non ascii code as à, é, é, ì, ò, ù

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:	None
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

Description:
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:

CREATE DATABASE IF NOT EXISTS `anagrafe_pmo`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `anagrafe_pmo`;
DROP TABLE IF EXISTS `anagrafe_pmo`.`td_attività`;
CREATE TABLE `anagrafe_pmo`.`td_attività` (
  `cod_Attività` INTEGER UNSIGNED NOT NULL,
  `Attività` VARCHAR(2) NOT NULL,
  `des_Attività` VARCHAR(32) NOT NULL,
  `Attivo` TINYINT(1) UNSIGNED NOT NULL,
  PRIMARY KEY 		(`Cod_Attività`)
)
ENGINE = MyISAM;

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`()
BEGIN
  SELECT Cod_Attività FROM TD_Attività;
END

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+"BEGIN"+vbcrlf
  	sql=sql+"SELECT Cod_Attività FROM TD_Attività;"+vbcrlf
		sql=sql+"END"
    DB.Connect = ConnectionString
    DB.CursorDriver = rdUseOdbc
    DB.EstablishConnection
    DB.Execute sql
    DB.Close

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

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.

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".

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;

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
Francesco