MySQL Bugs: #52046: Problem on retrieving EuroSign from a table-col with collation=latin1_general

Bug #52046	Problem on retrieving EuroSign from a table-col with collation=latin1_general_ci
Submitted:	14 Mar 2010 11:04	Modified:	27 Apr 2011 7:06
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:		OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	.net Connector, 128, 63, Euro, latin1_general_ci, utf8_general_ci

Description:
When storing an EuroSign (character code #128) into a latin1-column you will retrieve a different character (character code #63) when reading the data with .NET-Connector.

The storing-process itself seems to work fine (e.g. inserted data can be read correctly using php-Tools like phpMyAdmin)

Reproduced with:
   - MySQL 5.1.34-community and 5.1.44-community
   - several .NET-Connectors (5.2.7 through 6.2.2)

How to repeat:
The following .NET-Code creates a simple table with a latin1- and a utf8-column.
Afterwards it inserts the Euro Symbol in three different ways.
When reading the data into a DataSet, the column "test1" contains the wrong character code #63. Column "test2" contains the right character code #128.

Imports MySql.Data.MySqlClient
Public Class Form1

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim Pass As String = "DBpass"
        Dim DB As String = "DBname"
        Dim connStr As String = String.Format("host=localhost;user id=root; password={0}; database={1}; port=3306; connect timeout=10; compress=false; logging=false; pooling=false; Allow Zero Datetime=false; charset=utf8; persist security info=true; Use Performance Monitor=false;", Pass, DB)
        Dim Conn As New MySqlConnection(connStr)
        Conn.Open()

        MySqlHelper.ExecuteNonQuery(Conn, "DROP TABLE IF EXISTS eurotest; CREATE TABLE eurotest ( " & _
                                    "id INT(10) NOT NULL, " & _
                                    "test1 TEXT CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '', " & _
                                    "test2 TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT  '', " & _
                                    "PRIMARY KEY(id) " & _
                                    ") ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;")

        MySqlHelper.ExecuteNonQuery(Conn, "INSERT INTO eurotest VALUES (1,'" & Chr(128) & "','" & Chr(128) & "')")
        MySqlHelper.ExecuteNonQuery(Conn, "INSERT INTO eurotest VALUES (2,'€','€')")
        Dim cmd As New MySqlCommand("INSERT INTO eurotest VALUES (3,?a,?b)", Conn)
        cmd.Parameters.AddWithValue("?a", "€")
        cmd.Parameters.AddWithValue("?b", "€")
        cmd.ExecuteNonQuery()

        Dim DA As New MySqlDataAdapter("SELECT * FROM eurotest", Conn)
        Dim DS As New DataSet
        DA.Fill(DS)
    End Sub

End Class

Suggested fix:
.NET-Connector should take columns-collation into account.
Character-translation should be performed when retrieving data.

VB-Code to reproduce

Attachment: form1.vb (text/plain), 1.70 KiB.

Hi and thanks for your report.

This actually a duplicate of Bug#42500 but for Latin1 (see also Bug#28853). Checking on http://en.wikipedia.org/wiki/ISO/IEC_8859-1 I do not see EUR sign mapped thus #3F is the correct response.

So, if you need to mix latin1 and non-latin1 characters you might wish to use UTF8 or Win1251 or some other charset that suits your needs, but not the ISO-8859-1.

I expected an answer like that (i.e. latin1 does not contain Eurosign, so don't use it).

Let me try another approach to this bug (without Eurosign):
I want to save the special character code #128 (i.e. 80h) in a latin1-column and retrieve it.
According to your WikiPedia-Link the code #128 represents the special-character "Padding Character" - Okay: That's what I want to store now (not a Eurosign).

If I want to retrieve the value, I will get code #63 (i.e. 3Fh) back. That's a bug of the connector when retrieving latin1-data.

If you say it's by design and perhaps I'm too stupid to handle the right codepages: Please explain to me why PHP will not modify the retrieved data with the following code:

<?php
  $db = @mysql_connect("myServer", "myUser", "myPass");
  mysql_select_db("mySchema", $db);

  mysql_query("DROP TABLE IF EXISTS eurotest;", $db);
  mysql_query("CREATE TABLE eurotest (id INT(10) NOT NULL, test1 TEXT CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '', test2 TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT  '', PRIMARY KEY(id)) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;", $db);
  mysql_query("INSERT INTO eurotest VALUES (1,'" . chr(128) . "','" . chr(128) . "');", $db);
  $result = mysql_query("SELECT * FROM eurotest WHERE id=1;", $db);
  list($id, $test1, $test2) = mysql_fetch_row($result);
  echo "row-id=$id: " . ord($test1) . " " . ord($test2);
?>

This code saves chr(128) to a latin1- and a utf8-column.
Then it retrieves the row and outputs the codepage-numbers for both columns.
The output is as expected: "row-id=1: 128 128"
(and not "row-id=1: 63 128" as the .NET-Connector would do).

Why are you so hostile to me? This is a *free* support and we're doing our best to help everyone...

I do not wish to fight you and will try to answer to the best of my knowledge both c/NET and PHP questions.

First of all, PHP doesn't know anything about encodings, only binary safe strings. Effectively, string = byte stream. Not so in c/NET.

However, I did conduct more tests and it appears to me .NET compiler assumes things about source file encoding. Thus (char)128 (or Chr(128) in VB) sends wrong code to MySQL server (C280) while both
                cmd.CommandText = "INSERT INTO bug52046 VALUES (2,'€',...)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO bug52046 VALUES (3,0x80,...)";
                cmd.ExecuteNonQuery();
work as expected (E282AC).

Let me consult on this with colleagues although I do not think this is a bug in MySQL SW.

Sorry - I didn't meant to be hostile to you ... I'll try to find better words next time. (Not always easy for a non-native speaker - I'm from germany).
BTW: I appreciate the support and I'm very happy with those short response-times (something you won't find along some charges supports).

Back to the problem: If the .NET-Connector-Team decides to not handle unmapped latin1-Codes (I assume http://www.collation-charts.org/mysql60/mysql604.latin1_general_ci.html to be the supported character-map), then I would suggest the connector should not even insert these values into the DB.

But the current behavior to write "(char)128" and get "(char)63" is bad ;-)
(and could eventually be called a bug ... :-)

Yes it is and probably related to Bug#51927... We're working on it.

I've just tried to verfiy this bug with the most recent Connector-Version (actually v6.3.6)

Result: This bug does no longer exist! :-)

When having a look at Bug #51927 I assume it to be fixed by v6.2.4 (unverfied assumption).

Nevertheless: 6.3.6 definitive returns "(char)128" correctly.