MySQL Bugs: #48838: € char in Text Fields become ?

Bug #48838	€ char in Text Fields become ?
Submitted:	17 Nov 2009 13:07	Modified:	18 Nov 2009 8:14
Reporter:	Wolfgang Ahrens	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.1.x	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When i update a text field with the following command :
UPDATE set t = '€ 100' where id = 1

with the net connector the result will be '? 100'.

when i do this with a different program like MYSQL QUERY BROWSER, ... it works fine and the result will be '€ 100'.

How to repeat:
      sqlCmd.CommandText = "UPDATE set t = '€ 100' where id = 1"
      sqlCmd.ExecuteNonQuery()

Hi Wolfgang and thanks for your report.

Please see general query log from MySQL server in time QB connects and in time your c/NET app connects and notice the differences.

Also, I'll dare speculate here (since you did not post self-sufficient test case) that if I put "Character Set=utf8;" in my connection string I will not be able to repeat your problem...

So, please read entire chapter on charsets, set you environment properly for the signs you want to store and retest.

Hello Wolfgang,

where? I mean where do you get output ? instead of Euro? in  DOS Command line shell? On Printer? In a specific GUI Tool? Does somebody read it for you?

Hello Susanne,
there are diffents ways to get the output :
Update with NETCONNECTOR - select result in a MessageBox or
Update with NETCONNECTOR - select with the Mysql Query Browser or ...

Hello Tonci,

everything ( database, table, field ) is declared as 'utf8'.

You can use the example from the connector documentation with the only difference that there is a Table with two fields (id and t as text )
CREATE TABLE `test_db` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `t` text NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Public Sub InsertRow(myConnectionString As String)
          ' If the connection string is null, use a default.
          If myConnectionString = "" Then
          myConnectionString = "Database=Test;DataSource=localhost;
             UserId=username;Password=pass"
          End If
          Dim myConnection As New MySqlConnection(myConnectionString)
          Dim myInsertQuery As String = "INSERT INTO test_db (id, t) 
             Values(0, '€ 100')"
          Dim myCommand As New MySqlCommand(myInsertQuery)
          myCommand.Connection = myConnection
          myConnection.Open()
          myCommand.ExecuteNonQuery()
          myCommand.Connection.Close()
          End Sub

Wolfgang, so what happens if you change conn string to look like this:
myConnectionString = "Database=Test;DataSource=localhost;UserId=username; Password=pass; Character Set=utf8;"?

Hello Tonci,

that works fine - Thanks !

Wolfgang

Wolfgang, sure it works :-) However, you might wish to check your environment (mysql> show variables like "%char%"; for example) and general query logs with and without that conn string option so to better understand what was happening (my guess is you had character_set_client set to Latin1).

Closing the report now.