MySQL Bugs: #31342: Binary version is expecting data in Latin1 charset, UTF8 not displayed correctly

Bug #31342	Binary version is expecting data in Latin1 charset, UTF8 not displayed correctly
Submitted:	2 Oct 2007 13:27	Modified:	26 May 2009 13:13
Reporter:	Marcel Brouillet	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.2.12	OS:	Linux (Gentoo Linux 2.6.22.1-xxxx-std-ipv6-32 #3 SMP [...] i686 Intel(R) Pentium(R) 4 CPU 3.00GHz)
Assigned to:		CPU Architecture:	Any

Description:
My database is with tables ENGINE=InnoDB DEFAULT CHARSET=utf8.

Accented characters in VARCHAR fields get displayed:
- well on MQB 1.2.12 for windows
- well using mysql  Ver 14.12 Distrib 5.0.42, for pc-linux-gnu (i686) using readline 5.2
- incorrectly (2 ideograms for each accented character) on 1.2.12 binary for linux, downloaded from http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-gui-tools-5.0r12-linux-i386.tar.gz/...

A side-effect of the problem is that Blob fields containing images can no longer be displayed using the "view" pictogram.

Shell's environment variables are set as follows:

marcel@nx2 ~ $ locale
LANG=en_US.utf8
LC_CTYPE="en_US.utf8"
LC_NUMERIC="en_US.utf8"
LC_TIME="en_US.utf8"
LC_COLLATE="en_US.utf8"
LC_MONETARY="en_US.utf8"
LC_MESSAGES="en_US.utf8"
LC_PAPER="en_US.utf8"
LC_NAME="en_US.utf8"
LC_ADDRESS="en_US.utf8"
LC_TELEPHONE="en_US.utf8"
LC_MEASUREMENT="en_US.utf8"
LC_IDENTIFICATION="en_US.utf8"
LC_ALL=

Typing SHOW VARIABLES in MQB shows that 
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_results utf8
character_set_server utf8
character_set_system utf8

How to repeat:
Create a database with type DEFAULT CHARSET UTF8 and with a VARCHAR field.
Fill one record with an accented character in the VARCHAR.
View it using MQB linux 1.2.12 binary.

Suggested fix:
I tried executing
   SET character_set_results='latin1'
or
   SET SESSION character_set_results='latin1'

It is accepted by the server, but it seems that connection is reset at each request.  Indeed a SHOW VARIABLES; indicates the modification is not permanent.

Thank you for the report.

Please provide output of SHOW CREATE TABLE for the table you use and value of the field which dispalyed not correctly.

There were two points in my bug-report which I thought were related.

1/ accented characters badly displayed.  It seems that the data was incorrectly entered in the database.  The problem only displays on data that was restored via a mysqldump some time end september.  I haven't worked it all out yet, I'll test and submit another bug report if it's a mysqldump issue.  Recent records are displayed fine and coherently.  Sorry for the misleading report, I feel ashame of waisting some time.

2/ the non-display of images is still a fact that I can reproduce.  I thought they were related.  I set a blob to NULL and regenerated it using my PHP script.  The image displays fine on 1.2.12 for windows but not on linux.  If I save the binary in a file "test.jpg", that file can be displayed correclty.  Attached is a dump of the file with one given image

$ mysqldump compta invoices -w "inv_id=667" > dumpwithimage.sql

Viewed with QB.1.2.12 for linux, binary, I get in the preview window the text "can not display binary data as text" (both in Text and Binary tabs ; Image tab is empty).

I am not sure if changing the Synopsis of the bug report is a good idea, I'll let you do it.

Regards,
Marcel.

mysqldump compta invoices -w "inv_id=667" > dumpwithimage.sql

Attachment: dumpwithimage.sql (text/x-sql), 34.31 KiB.

Thank you for the feedback.

Verified as described on Ubuntu. On Mac bug is not repeatable.

Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Query Browser into MySQL Workbench. Unfortunately you are using an unsupported platform. More informations about supported platforms you will find here:

http://www.mysql.com/support/supportedplatforms/tools.html

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/