Bug #55245 Mysql VIEW in MS Access has character/collation corruption
Submitted: 14 Jul 2010 5:52 Modified: 15 Aug 2011 1:55
Reporter: David Boccabella Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.5.12 64bit OS:Any (SESU 11 64bit)
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: 5.5.12, MySQL, ODBC 5.1.8, Views

[14 Jul 2010 5:52] David Boccabella
Description:
I have tested this with ODBC 3.51.24 and 5.01.06 and MS Access 2003

I have 2 Mysql tables A and B. Both have collation of latin1_swedish_ci and a character set of latin1

Table A and B are of the following structure

ID int
Val varchar(30)

The contents of both are viewable in MS Access as linked tables

When a VIEW is created that joins the two tables together (ID, Val1, Val2)  and it is linked into MS Access as a table, the Val1 and Val2 fields are displayed in a Korean style character set.
If one does a design view on the table structure in MS Acess both the varchar fields are shown as type 'Binary' rather than 'Text'

For some reason MySQL 5.5.3 is returning back the data in an incorrect character format.

I have repeated this on 2  MySQl v5.5.3-m3 servers on windows.

I have tried this on a 5.1.3 Mysql and  I can see the results as expected

How to repeat:
Create 2 small tables in a MySQL 5.5.3  database

Table(s) format

ID  int
VAL varchar(30)

Add in some data and then using MS Access link to these tables. View the tables from within MS Acess and the contents should be as entered.

On MySQL create a simple view of joining the two tables together. View the results in MySQL and the join should display the correct information.

Now link the View into MS Access so that MS Access can use it like a table.  Open view and it should display non-English characters (looks like Korean)

Hops this helps.. This has caused us some issues as our older version of MySQL (5.1.3) did not produce this issue.
[14 Jul 2010 6:04] Valeriy Kravchuk
Please, send the results of:

show variables like 'char%';

when connected to the same server from mysql command line client.

Also, on the same machine where you use MS Access, please, start cmd.exe command line window and get the results of

set

command.
[14 Jul 2010 6:29] Tonci Grgin
David, please start MySQL command line client with --column-type-info option (display result set metadata), make your query and do a SELECT * FROM my_view. Paste the output here.
If fields in view have BINARY flag set, please check "Always handle binary function results as character data" DNS option and see if it helps.
[14 Jul 2010 6:30] David Boccabella
Sent details as private comment.
Please check that
[8 Sep 2010 2:54] David Boccabella
Is there any update on this one?
Many thanks
Dave
[27 Sep 2010 13:25] Susanne Ebrecht
Which language is your MS Access?
[27 Sep 2010 22:03] David Boccabella
I am using  the English version of MS-Access
Many thanks
Dave
[28 Sep 2010 8:52] Susanne Ebrecht
I have a problem here to make a deeper analysis.

In your access file I see Asian signs.
In your table that you pasted above I see English (ASCII).

I need the original signs.

Which signs should be in the database?

Also I need output of select col, length(col), hex(col) for the signs.
Of course I just need one or two rows ... so a where clause is necessary.
[28 Sep 2010 23:58] David Boccabella
Results of view in Access

Attachment: AccessIssue.jpg (image/jpeg, text), 89.08 KiB.

[29 Sep 2010 0:02] David Boccabella
All information should be in English.  The original signs are in English and should be in English when i use the view in Access.
Something is translating the signs into Asian characters.

This is a view in MySQL

create view testcase as
  (select 
  ItemNumber,
  length(ItemNumber),
  hex(ItemNumber)
  from items) ;

This is the result in MySQL

Back	        4	4261636B
BLMIRMAT	8	424C4D49524D4154
BLMIRSV	        7	424C4D49525356
BLMIRTB	        7	424C4D49525442

I have attached the result of the view in Access as an attachment.

Many thanks for your help
David
[19 Oct 2010 6:17] David Boccabella
Any update on this? 
Many thanks
Dave
[26 Dec 2010 15:59] Valeriy Kravchuk
Please, check if the same problem still happens with a newer version of server, 5.5.8.
[29 Dec 2010 3:30] David Boccabella
Thanks for the update, however the issue still exists.

I downloaded MySQL 5.5.8 and  connector 5.1.8.

Then imported a set of tables and created a view from them

After opening MS-Access 2003 I linked to the view and displayed the result.

In a native MySQL (SQLYog) I can see the correct results of the view
In MS Access all text fields had their character set changed.

Hope this helps.
Dave
[15 Jan 2011 16:47] Peter Seitz
Hi !  I've experienced the same kind of issues as David described, except that I was using Win XP Pro, Access 2007 and mySQL 5.5.8. (connector 5.1.6 and 5.1.8 both yield the same behaviour).  When I link to a mySQL table, everything looks fine in Access.  When I link to a view of that table, all text-related datatypes appear in Access either as binary or OLE and dispaly as Asian characters.  However, any numerical or date datatypes in the view are OK.  The linked object in Access appears to show the correct number of records, but the text-related datatype fields are unusable.  I also was using the latin1 character set witn latin1-swedish collation.  My only solution was to down grade from 5.5.8 back to 5.1.54 and then everything was fine - all my Access links were behaving normally again.
[25 Jan 2011 8:26] Tonci Grgin
I'd say there could be several problems here, maybe even hitting all of them at the same time:
  o Double encoding:
character_set_client     utf8 
character_set_connection utf8 
character_set_database   latin1 
character_set_results    utf8 
character_set_server     latin1 
Data, afaiu, is in LATIN1 thus character_set_server and character_set_database are fine but all of the rest of settings will cause conversion from latin1 bytes to UTF8 stream (server -> cli) and vice versa (cli -> server). I guess there is an UTF8 in client section of my.ini or, if not (and there should not be!), this can come from default behavior of c/ODBC. One should try with character_set_results set to NULL (default behavior of all of the connectors, means "autodetect") thus allowing c/ODBC to pick up on latin1. This is what c/ODBC (and nay other MySQL connector does) so character_set_results is definitely set by you in your code.
  o BINARY flag: Please test with "function always return string" setting set to TRUE and see what's happening.
  o On Windows, MySQL server 5.5(.3 and up I think) starts with UTF8MB4 as default. Please check my.ini and correct this to plain UTF8.
  o Bug in VIEW processing in server...
[26 Jan 2011 15:55] Andrew Curry
Hello,

I am also having this issue.  Upgraded from MYSQL 5.1.37 to 5.5.8.  Upgrade went smoothly, apart from not being able to create users in 5.5.8 using Workbench 5.2.31 rev. 7115.

I also concur with an earlier posting, where this issue only affects linked views with the ODBC driver and Access 2003.  I created a table using exactly the same information and linked to this, and all data came through successfully.

I would therefore submit that the problem lies somewhere within either the views in 5.5.8 or the ODBC connector.

I would be happy to send any further information as required in order to resolve this issue.

Andy Curry
IT Manager
Summerfield Musical Instruments Ltd
Blaydon on Tyne
UK
[21 Feb 2011 9:06] Marselinus Magtal
I have the same problem. Maybe this information will help someone find the solution.

Before I have a MySQL 5.5 database installed in Ubuntu 64bit system. There is no problem with the link table from MS Access 2003. The VarChar datatype from the View read as TEXT.

Then I moved the database to Windows 2008 Server 64bit system. I Installed MySQL 5.5 also for this server (windows 64bit version). Then this problem occur.
But its only happen with view object, the table object with VARCHAR datatype still read as TEXT.

The different between this two database is in Ubuntu I am using MyISAM as MySQL engine. In 2008 Server I am using both MyISAM and InnoDB as the engine. But the database still using MyISAM both in Ubuntu and 2008 server.

The character set using latin1 as default.

The ODBC connector using the same version (5.1).

I am still trying to find the solution until now.
[22 Feb 2011 4:22] Marselinus Magtal
Following my previous post, I think I already solved the problem. But I don't know the root problem.

Because the new server have windows 64bit, I must migrate all the database to the new server (Ubuntu 64-bit to Windows 2008 64-bit). I found the problem for link table (MSACCESS 2003) when trying to run the existing application on it.

So today I download new mysql-5.1.55-winx64(noinstall). I configure my.ini from my-huge.ini manually, change some of the parameter value. Then running it.

The result is when I renew the link table in MSAccess, the column that should be a VARCHAR read as TEXT by MSAccess and not as BINARY. Problem solved. But what the root problem, dunno... lol

My suggestion is:
1. Use Unix/Linux for your server
2. Use the solution above, download the noinstall version, configure my.ini manually. Then try it. If it doesn't work then try the first suggestion :d

Thanks and regards,

Marselinus Magtal
Nokia Siemens Network
Performance Management
marselinus.m.0032112@gmail.com
marsel.magtel.ext@nsn.com
[22 Feb 2011 11:22] Tonci Grgin
Marselinus, this does not surprise me. There can be a metadata sent by server change (you can test this by starting command line client with --column-type-info and checking metadata returned when issuing your query for both old and new MySQL server), it could be that you changed some setting in c/ODBC and so on...
[25 Apr 2011 21:06] Grant Mitchell
I am encountering the same issue.  This thread suggests it's a bug in newer version, possibly related to InnoDB.  Is the issue being worked at Oracle?
[25 Apr 2011 21:17] Peter Seitz
Grant,

Not sure what kind of attention this is getting from the mySQL team - keeping my fingers crossed that someone will look at it.

Based on our own experience with this issue (notably with specific datatypes), I would guess that the gremlin is hiding in the handoff between mySQL and the ODBC connector.  Either the connector is not getting what it's expecting from mySQL and the connector requires tweaking, or mySQL is not talking the kind of language that an ODBC connection needs (I say ODBC connector in general, but I've only used the Windows connector).  I doubt that the INNODB engine per se is in question, but then again, I'm not familiar with most of what's under the mySQL hood.  At any rate, we had to reverse a 5.1 to 5.5 migration on account of this, and I'm not going down that path again until I have reason to hope that things will be better.
[11 May 2011 17:54] Paul Bossi
Minimal test case:

OS: Windows 7 **64 BIT**
mysql: 5.5.8 or 5.6.2 **64 BIT**
Microsoft Access: Access 2007 32-bit

use test;
create table items ( f varchar(255) );
insert into items values("one"), ("two"), ("three"), ("four");
create or replace view vw_items as select *,hex(f) as fhex from test;

Then create DSN to test database.

Then create a new Access 2007 database, and link the above via External
Data:Import:More:ODBC then choosing Link option and the vw_items table 

RESULT: all Asian characters in the text field.
RESULT FOR NON-VIEW: normal characters.
[12 May 2011 14:14] Paul Bossi
Here's an improved bug report and minimal test case that fixes one typo in the prior and also mentions the Connector/ODBC version:

FIX: UNINSTALL 5.5/5.6 64-bit AND INSTALL 5.1.57 **64 bit**

OS: Windows 7 **64 BIT**
MySQL: 5.5.8 or 5.6.2 **64 BIT**
MySQL Connector/ODBC: 5.1.8 x64 **64 BIT**
Microsoft Access: Access 2007 **32-bit**

SYMPTOM: All Asian characters in **VIEW** text fields in Access 2007.
SYMPTOM FOR NON-VIEWS: No Asian characters; things work fine.

Minimal test case:
                                                                 
use test;
create table items ( f varchar(255) );
insert into items values("one"), ("two"), ("three"), ("four");
create or replace view vw_items as select *,hex(f) as fhex from items;

Then create DSN to test database.

Then create a new Access 2007 database, and link the above, via External Data:Import:More:ODBC then choosing Link option and the vw_items table 

SYMPTOM: BUG: All Asian characters in **VIEW** text fields in Access 2007.
SYMPTOM FOR NON-VIEWS: NO BUG: No Asian characters; things work fine viewing regular table.
[29 May 2011 3:27] David Boccabella
Hi Guys.
I am being REALLY patient here considering that this issue has NEVER been resolves despite several versions of MySQL being released.

I created a brand new SESU 11 installation and installed MySQL 5.5.12 on it.
I ten created the simple Test that Paul Bossi created and then linked the view into Access 2003 and 2007

Any characters or tex show as Asian characters.

PLEASE Fix!!!!  If I cannot get views running in MsAccess then I WILL have to move to MS SQL.  As much as I love MySQL if it cannot do the job I need it to do then it is effectively useless to me.

Many of my customers use MS Access as a quick searching tool to make queries. If I can use views then I can effectively (and simply) stop the changing of information.  I know I can do this with permissions but a non-changable view is easier.

Yours in deperation
Dave
[30 May 2011 11:36] Peter Seitz
David,

We encountered the same issue when we first migrated to 5.5.8, but when we downgraded to 5.1.54 everything went back to normal (same ODBC version 5.1.8, Windows XP 32 bit).  My suggestion to you would be to use the latest version in the 5.1.xx series as this stream seems to still be maintained.  Once 5.5.xx (or 5.6.xx) addresses the problem, then you could upgrade.  But in the mean time, 5.1.54 or higher should be a safe bet to allow connectivity via Access (which is what we do as well).  Good luck !
[31 May 2011 3:10] David Boccabella
Moving back to 5.1..  What would I loose. Well SIGNALs in stored procedures would be one, plus supposed performance increases.

We are upto 5.5.12 (with a lot of fanfare) yet they still cannot fix an issue with talking to a very common application!

Sorry - really not a Solution
Dave
[31 May 2011 16:35] Rudi De Koninck
I'm having the same issue (Assian characters in text fields) when I link a MySQL view in Access.

However when I do the same in Excel, all characters appear in the correct way.
[5 Jul 2011 10:42] wadah Yasean
Hi,

I also have this problem. My current Config is
OS: XP
MySQl Server 5.5
Ms Access 2007

1. When linking access directly to a table, the datatype mapping is correct.
2. When linking access directly to a view, the datatype mapping is incorrect.
2. When using a pass through query to open a view, data is correct.

I need to access the link directly as I need the data to be up-datable, therefore the 3rd option will not work in my case.

Does anyone know if this bug is fixed or if I should use MySQL Server 5.1 until there is a fix available?

Thanks
[5 Jul 2011 11:43] Peter Seitz
Wadah,  as far as I can tell, this problem has not been addressed and doesn't appear to be on the mySQL radar screen yet.  It was raised under 5.5.8 and then someone moved it under 5.5.12, but for some reason stuck it under Linux 64 bit, which is incorrect - the bug applies to Windows 32-bit (XP for sure, probably other versions also) and the Access 2003/2007.  I would like to see someone fix this (either the gremlin lies within mySQL itself or with the ODBC connector) so that we can migrate off 5.1.54 (which does not have this problem).
[6 Jul 2011 2:23] David Boccabella
Hi Folks
That would have been myself that indicated Linux.  I raised this bug so long ago that it's not funny. After seeing some people say that moving to Linux 64 bit would fix the issue I did so.
But alas to no solution.

Myself - seriously thinking of moving from MySQL to MSServer. Views are a vey good and secure way to limit access to data - esp when most of my organisation use MS-Access to generate ad-hoc reports and stats.

However I am stuck with this view issue still and unless I implement a huge security setup on my MySQl tables I cannot easily prevent folks from modifying the information.

Am I happy. HELL No..  It's just that I feel like I am pissing in the wind to get this issue resolved by the MySQl team.

Dave
[6 Jul 2011 11:29] Peter Seitz
David,

I sympathise as I too am anxious to see this resolved.  However, mySQL 5.1.54 does what we need it to do. We to have users accessing views and certain tables via MS Access & have implementated user roles functionality via home-grown procedures to control who has access to what.  For now, there's no impetus to move onto MSSQL, although I fully agree that it is an attractive environment.  I truly hope that the mySQL team will address this issue so that we don't stay orphaned on 5.1 while they launch 5.6 and work on 5.7, 5.8 etc.

Keeping fingers crossed - Peter.
[6 Jul 2011 23:42] David Boccabella
Unfortunately I need 5.5
As I make use of stored procedures I was really waiting for 5.5 to come out so that it would fix some of the parameter issues that were in with 5.1 like Iin/Out parameters and Signals for error conditions.
So I a stuck between two places - needing 5.5 Sp capability but needing 5.1 Views.
Dave
[6 Jul 2011 23:58] Grant Mitchell
I challenge Oracle to chime in on this.  I have only been on this forum for about a year, and I don't know what things were like with Sun, but I have not seen any type of official response from Oracle.  A bug is categorized as S1 (Critical) for a year and we hear only crickets?
[7 Jul 2011 0:30] Victor Nuovo
I'm also suffering from this problem after upgrading a server and MySQL to 5.5.  Downgrading back to 5.1 isn't a big deal for me, but it certainly wouldn't be my first choice.  Seems like this should have been fixed by now.
[7 Jul 2011 3:51] Bogdan Degtyariov
The patch for this bug has been pushed into MySQL 5.5.15 tree.
[9 Jul 2011 16:54] Victor Nuovo
Is there an ETA on when 5.5.15 will be out?
[10 Jul 2011 5:39] David Boccabella
A Fix.. Atlast!!!

Well - we are upto 5.5.14 so hopefully 5.5.15 will be here SOON

Take Care
Dave
[8 Aug 2011 13:36] David Muoz Vicente
I've been looking for this bug in the change history for 5.5.15 and it's not in the list. Can anybody confirm if the patch have been applied in this version?

Thank you very much.
[11 Aug 2011 18:20] Lucas Gomes Palhares
I also had the same issue. I upgraded to 5.5.15 and so far it seems to have been solved. MSAccess reads the views in MYSQL without any problems with contents of varchar fields.
[15 Aug 2011 1:53] David Boccabella
At long last I can confirm that this bug has been fixed in 5.5.15

On my 5.5.14 system I accessed a View from MSAccess and saw that the text was non-english.

I upgraded to 5.5.15 and did the same and saw that the text was now in english.

Many many thanks for fixing this. Took several years though.
[15 Aug 2011 1:55] David Boccabella
I'll close this bug as I was one of the original reporters
[1 Sep 2011 8:43] zeng qiang
Very helpful fix! 

Thank you!

ZQ
[27 Nov 2011 19:44] glenn martin
broken again with 5.5.8

assume it is in the odbc driver

views work perfectly with OpenLink's mysql odbc driver