MySQL Bugs: #45054: Empty select fields when using non default collation

Bug #45054	Empty select fields when using non default collation
Submitted:	24 May 2009 14:10	Modified:	24 Feb 2010 14:37
Reporter:	Peter Maxwell	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Windows (XP Pro SP3)
Assigned to:		CPU Architecture:	Any

Description:
I have a table with charset latin1 and collation latin1_german1_ci. If I try to retrieve any column containing text from this table using the ODBC connector from the MySQL Server (v5.0.51), then the correct number of rows is displayed but all fields are blank. If I switch to latin1_swedish_ci, which is the default for latin1, everything is displayed.

The client software I am using is Microsoft Word 2007 SP2 (mail merge).

How to repeat:
set up table with latin1_german1_ci collation varchar columns, fill them with any data. then use mysql odbc 5.1 connector to fetch the data into microsoft word 2007 sp2. all fields will be blank.

Hi Peter and thanks for your report.

Can I please get sample dump of database in question? I would also like to see output of SHOW VARIABLES LIKE "%CHARSET%" and a snip from general query log from the moment Word connects till query is executed.
Then I'll see if I'll need anything else...

Sample dump of database

Attachment: drk_md2.sql (text/x-sql), 2.66 KiB.

Hi Tonci,

try the sample I attached to this bug. Originally, I used this query:

<snip>
SELECT Name, Vorname, Anrede, Strasse, PLZ, Ort, DATE_FORMAT(geburtstag, "%d.%m.%Y") as Geburtstag, year( now( ) ) - year( geburtstag ) AS "Runder", year(now()) - year(eintritt) as "Mitglied seit (Jahre)"
FROM drk_md2
WHERE import_num =15 and status<=1
AND (
(
year( geburtstag ) - year( now( ) )
) %5
) =0
AND year( now( ) ) - year( geburtstag ) >=10
AND geburtstag != '0000-00-00'
ORDER BY DATE_FORMAT(geburtstag, "%m%d"), Name
</snip>

I can reproduce the problem, even if I simply write "SELECT * FROM drk_md2". All text fields are blank while numbers are being shown. Interesting remark: Microsoft Query does show the data, word itself does not (neither in the data fields within the document nor in the recipient list edit box.

You'll also find a mysql log file attached. What I've done to produce it: I opened a mail merge word document which uses a .dgy file to retrieve data. While starting, the first selects where sent. When I opened the "recipient list edit" dialog (I don't know the exact english translation because I use the german version of word 2007), some more selects where sent.

I hope you find some interesting data.

Regards

Peter

MySQL server logging while starting word (see comment)

Attachment: mysql.log (application/octet-stream, text), 3.75 KiB.

Query executed

Attachment: Bug45054.jpg (image/jpeg, text), 33.76 KiB.

Peter, I will need more help here. MSOffice is so huge I don't think anyone knows more than 30% of it's functionality. And I stopped using it quite some time ago...

Now, I've run your query in MSQRY32 (Office is *32* bit application) against remote MySQL server 5.1.30 via c/ODBC 5.1.5 and, as can be seen in attached image, all data is retrieved. Word uses, or at least it used to use, same mechanism for data retrieval, so you should be able to see the same...

I failed miserably to create data connection with mail merge in Word. Can you please point me, step by step, on how to do it? Are you using DSN or configuring ODBC connection on your own or, maybe, you're using FileDSN? What are the options you're using?

Also, I am still missing output from queries:
  o SHOW VARIABLES LIKE "%CHAR%"
and
  o SHOW CREATE DATABASE what_ever_your_db_name_is

My environment:
  o MySQL server 5.1.30 on remote OpenSolaris x64 box
  o Client machine is Win2K8SE x64 with c/ODBC 5.1.5 and Office2007 Ultimate

I'm still living. Here is the requested data:

mysql> SHOW VARIABLES LIKE "%CHAR%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     | 
| character_set_connection | latin1                     | 
| character_set_database   | latin1                     | 
| character_set_filesystem | binary                     | 
| character_set_results    | latin1                     | 
| character_set_server     | latin1                     | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql> SHOW CREATE DATABASE drkverwaltung;
+---------------+---------------------------------------------------------------------------------------------------

-+
| Database      | Create Database                                                                                    

|
+---------------+---------------------------------------------------------------------------------------------------

-+
| drkverwaltung | CREATE DATABASE `drkverwaltung` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci */ 

| 
+---------------+---------------------------------------------------------------------------------------------------

-+
1 row in set (0.00 sec)

I get the same result set with MS Query. The problem occurs only with Word directly. How to reproduce (I don't have the exact english translations of the buttons/menus):

1) Open MS Query, enter the sql command until you get the result and save the query as .dgy file (file -> save under)
2) Create a new empty word document
3) Click the mailings rider
4) Click "start mail merge" and select "letters"
5) Click "select recipients" and select "use existing list" (I don't know the exact english names)
6) Choose your .dgy file
7) Click yes
8) Click "Edit recipient list"

You'll see the result set, but only numbers and no text (see screen shot). If you try to insert some merge fields into the document body, then press "Preview results", you'll see only numbers, no text.

My environment: 
* Office 2007 Enterprise SP2 32 bit, german
* Windows XP SP3 32 bit, german
* Connector/ODBC 5.1.5
* MySQL Server 5.0.51a-24+lenny1 (Debian testing)

Word 2007 with edit recipients dialog showing the bug

Attachment: editrecipients.JPG (image/jpeg, text), 67.84 KiB.

Thank you Peter, now I see what you see which is no First/Last name...

Can you please paste output of SHOW VARIABLES LIKE "collation%". If client collation is not latin1_german1_ci could you please change it in my.cnf, client section, and retest.

Peter,

I am the encoding expert and will overtake this now.

I just made some tests on Linux and couldn't reproduce it.

This problem is either related to our server on Windows or related to MyODBC or related to MS Office.

I will make some tests later this day. I first have to set up all that stuff on Windows (need to install Office and MyODBC).

I need to know which Windows are you using.

I will make my tests with German Windows. But the issue already could be related here.

If you are using another Windows then German Windows let me know.

mysql> SHOW VARIABLES LIKE "collation%";
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci | 
| collation_database   | latin1_german1_ci | 
| collation_server     | latin1_swedish_ci | 
+----------------------+-------------------+
3 rows in set (0.00 sec)

I generated this output by connecting to the server with the command line client directly on the server. I don't know how to generate it via ODBC. Which my.cnf should I modify? The client section in the my.cnf server file? Is there a my.cnf file for the ODBC driver? Thank you!

Hi Susanne,

please see my comment on 26 Aug 0:08 for the details of my environment. I use german Windows XP.

Peter

Hello Peter,

I tested with my own test tables and used heavy words like Grüße and so on ... but I wasn't able to repeat this.

Of course I tested with your test szenario.

I have an idea why it will fail but therefor I need some feedback from you:

'DÃ¼ckhausen' <--- that looks like if it is wrong stored in the database.

Googling for Postleitzahl results: 67592 Flörsheim-Dalsheim

That is not helping me. So I need to know if it is Däckhausen, Dückhausen or Döckhausen.

Also I need output from:

Select hex(ort) from drk_md2 where name='Peter' and vorname='Pan';

Shouldn't it be vise versa? Peter Pan and not Pan Peter? According to your test data you put Pan into Vorname and Peter into Nachname ...

Anyway .... 

Better would be anyway something shorter with Umlaut like Müller or village Bär.

So what do I need:

1) output of:
SELECT HEX(col) FROM tab WHERE <where condition that you just will get a single row back and where the word in the row contains a umlaut (ä, ö, ü or ß).

2) For this word I would need what the word should be. Means when hex() output is:
4DC3BC6C6C6572

I would need the information that the original word should be 'Müller'.

Or:
62E472 should be Bär.

Whatever hex values you will get ... I need what you expect which word it should be.

2) I need output of:

SHOW CREATE TABLE <your_table>;

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

I will close this now as two of us were unable to repeat the problem.