Bug #58094 MS Access ODBC DSN-Less connection displays wrong characters
Submitted: 9 Nov 2010 20:07 Modified: 11 Aug 2011 12:12
Reporter: Justin Ram Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.5 OS:Microsoft Windows (X86 & X64)
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: 5.1, 5.5, character sets, Latin1, MS Access

[9 Nov 2010 20:07] Justin Ram
Description:
Using both ODBC 5.1.7 & 5.1.5, when creating linked tables within MS Access using VBA utilizing a DSN-LESS connection, information comes back with very wrong character set. 
Additionally, text objects are coming up as OLE Objects within Ms Access VBA linked DSN-LESS tables.
This is after restoring all data from a dump to Server 5.5 from Server 5.1.34
All incompatible default-character-set instructions removed from my.ini and replaced with the character-set-server command.

How to repeat:
Have working data in 5.1 Server. Import into 5.5 Server. Connect with Ms Access 2007 & 2003, both through linked table manager as well as through a VBA DSN-Less connection. Characters will be wrong in the vba one but correct in the Linked Table Manager.

Suggested fix:
I have been unable to determine a fix at this time.
[9 Nov 2010 20:08] Justin Ram
bad characters of same view of table

Attachment: badcharacters-vba odbc.jpg (image/jpeg, text), 487.94 KiB.

[9 Nov 2010 20:10] Justin Ram
good characters of same view of table

Attachment: goodcharacters-ODBCLinkManager.jpg (image/jpeg, text), 250.04 KiB.

[10 Nov 2010 8:40] Bogdan Degtyariov
Hi Justin,

Have you tried setting CHARSET=latin1 (or other charset name relevant to your data) in the ODBC connection string?
[10 Nov 2010 13:54] Justin Ram
Just tried setting CHARSET flag in ODBC connection string, makes no difference.
[10 Nov 2010 14:25] Bogdan Degtyariov
Please provide the connection string.
Which character set you are setting? 
Try UTF8
[10 Nov 2010 14:51] Justin Ram
Tried UTF8 as well, still no change.
ODBC;DRIVER={MySQL ODBC 5.1 Driver};UID=jram;PWD=jr8794;PORT=3306;DATABASE=Texas;SERVER=127.0.0.1;CHARSET=UTF8;

Also tried with the option setting of OPTION=16410 included.
[11 Nov 2010 6:53] Tonci Grgin
Bogdan, I think maybe the table structure and/or server settings are different. Presumably there has been a mapping as such:
  MySQL 5.1 UTF8 -> (say) MySQL 5.5 UTF8MB4...

Justin, please do paste the output from mysql command line client of following for both servers:
  o SHOW CREATE TABLE mytable
  o SHOW VARIABLES LIKE "%char%"
[11 Nov 2010 14:16] Justin Ram
Server 5.5

| Table                | Create Table

| propertydescriptions | CREATE TABLE `propertydescriptions` (
  `PropStatus` varchar(20) DEFAULT NULL,
  `Taxpin` varchar(25) DEFAULT NULL,
  `fkeyLseRec` int(15) NOT NULL,
  `PKPropDesc` int(15) unsigned NOT NULL AUTO_INCREMENT,
  `GrossAcreage` decimal(15,10) DEFAULT NULL,
  `MineralInterest` decimal(15,10) DEFAULT NULL,
  `Net` decimal(15,10) DEFAULT NULL,
  `SurveyName` varchar(255) DEFAULT NULL,
  `AbstractNumber` varchar(50) DEFAULT NULL,
  `Description` varchar(5000) DEFAULT NULL,
  `County` varchar(50) DEFAULT NULL,
  `PropertyAddress` varchar(200) DEFAULT NULL,
  `InstrumentNumber` varchar(25) DEFAULT NULL,
  `DeedBook` varchar(25) DEFAULT NULL,
  `DeedPage` varchar(25) DEFAULT NULL,
  `DeedDate` date DEFAULT NULL,
  `FullLegalDesc` varchar(5000) DEFAULT NULL,
  `City` varchar(50) DEFAULT NULL,
  `SurfaceOwnerStreet` varchar(50) DEFAULT NULL,
  `SurfaceOwnerCityStateZip` varchar(50) DEFAULT NULL,
  `SurfaceOwnerName` varchar(100) DEFAULT NULL,
  `SubdivisionCode` varchar(50) DEFAULT NULL,
  `SubdivisionName` varchar(50) DEFAULT NULL,
  `Blk` varchar(50) DEFAULT NULL,
  `Lot` varchar(50) DEFAULT NULL,
  `UnitTract` varchar(5000) DEFAULT NULL,
  `UnitName` varchar(50) DEFAULT NULL,
  `DocForLse` varchar(50) DEFAULT NULL,
  `RIMineralInterest` decimal(15,10) DEFAULT NULL,
  `HBP` tinyint(1) NOT NULL DEFAULT '0',
  `UnitAcreage` decimal(15,10) DEFAULT NULL,
  `LessorRoyalty` decimal(15,10) DEFAULT NULL,
  `MapTractNumber` double(7,2) DEFAULT NULL,
  `CopyPKPD` int(15) DEFAULT NULL,
  `AssociatedLease` int(15) DEFAULT NULL,
  `CertifiedMailNumber` varchar(25) DEFAULT NULL,
  `MIPA` varchar(50) DEFAULT NULL,
  `CertifiedMailReturned` tinyint(1) NOT NULL DEFAULT '0',
  `CertifiedReceiptDate` date DEFAULT NULL,
  `CertifiedDeliveryConfirmationNumber` varchar(25) DEFAULT NULL,
  `CertifiedDeliveryConfirmationDate` date DEFAULT NULL,
  `TaxDelinquency` tinyint(1) NOT NULL DEFAULT '0',
  `TadAccountNum` varchar(12) DEFAULT NULL,
  `TaxSuit` tinyint(1) NOT NULL DEFAULT '0',
  `TractRemarks` varchar(5000) DEFAULT NULL,
  `ImportID` int(10) DEFAULT NULL,
  `ImportID2` int(10) DEFAULT NULL,
  `Foreclosure` tinyint(1) NOT NULL DEFAULT '0',
  `DOUTractNumber` int(10) DEFAULT NULL,
  `PDTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_T
IMESTAMP,
  `TitleOpinionReceivedDate` date DEFAULT NULL,
  `DateTitleOpinion` date DEFAULT NULL,
  `CurativeStatus` varchar(15) DEFAULT '0',
  `TitleOpinionReceived` tinyint(1) NOT NULL DEFAULT '0',
  `TitleOpinionWorking` tinyint(1) NOT NULL DEFAULT '0',
  `TitleOpinionNumber` int(20) DEFAULT NULL,
  `TitleOpinionatAttorney` tinyint(1) NOT NULL DEFAULT '0',
  `TitleReportCompleted` tinyint(1) NOT NULL DEFAULT '0',
  `TOStatus` varchar(25) DEFAULT NULL,
  `SurveyorMapTractNumber` double(7,2) DEFAULT NULL,
  `PKStore` int(15) DEFAULT NULL,
  `AssignmentIntoXTO` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`PKPropDesc`),
  KEY `fkeyLseRec` (`fkeyLseRec`),
  KEY `MapTractNumber` (`MapTractNumber`),
  KEY `MI` (`MineralInterest`),
  KEY `Taxpin` (`Taxpin`),
  KEY `Unit` (`UnitName`(15)) USING BTREE,
  KEY `fkunit` (`fkeyLseRec`,`UnitName`(15)),
  CONSTRAINT `FK_propertydescriptions` FOREIGN KEY (`fkeyLseRec`) REFERENCES `le
aserecords` (`PKLseRec`) ON DELETE CASCADE,
  CONSTRAINT `LeaseRecordsPropertyDescriptions` FOREIGN KEY (`fkeyLseRec`) REFER
ENCES `leaserecords` (`PKLseRec`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=61217 DEFAULT CHARSET=latin1 |

mysql> show variables like "%char%";

| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | cp850
     |
| character_set_connection | cp850
     |
| character_set_database   | latin1
     |
| character_set_filesystem | binary
     |
| character_set_results    | cp850
     |
| character_set_server     | latin1
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.5\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)

I have tried setting the 
character-set-client=latin1
character-set-connection=latin1
character-set-retults=latin1

in my.ini but after starting the server I first get the error: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ignoring option '--character-set-client-handshake' due to invalid value 'latin1'

then after running show variables like "%char%" none of the variables I set earlier have changed.
[11 Nov 2010 14:43] Justin Ram
I have also updated the connection strings while the server was running using :

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       | C:\Program Files\MySQL\MySQL Server 5.5\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)

SET character_set_results = 'latin1', character_set_client = 'latin1', character_set_connection = 'latin1', character_set_database = 'latin1', character_set_server = 'latin1';

However, even after verifying these changes have taken place, the connections I make in access still show up with chinese characters.
[12 Nov 2010 9:08] Bogdan Degtyariov
Justin,

Does the same problem happen if you try creating a simple table?

create table tab1(id int primary key, vc varchar(32));

insert into tab1 values (1, "text string 1"), (2, "text string 2"), (3, "text string 3");
[12 Nov 2010 15:11] Justin Ram
The problem did not appear with the simple table. Could the import of my old data have had an issue that was not reported perhaps? How would I go about fixing the problem with my old data?
[10 Dec 2010 15:30] Justin Ram
Is any more work being done on this issue?
Why no responses? I would imagine this is a serious problem for many people seeing as you cannot upgrade from 5.1 to 5.5 with your data working correctly.
[14 Dec 2010 7:33] Tonci Grgin
Justin, the bug system is not the appropriate place for asking help on using MySQL products. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Now, I guess (as there is no test case attached) there is nothing wrong with your data or upgrade. My best bet is that some of your columns are returned with BINARY flag set thus assuming form of 0xYour_Correct_Text. Please try with "Always handle binary functions result as character data" option set.
[15 Jan 2011 0:00] Bugs System
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".
[20 Jan 2011 7:11] EDV Wipruef
I get the error from MySQL version 5.5.8. Before this error has not occurred to 
me. 
I link tables and views in MS-Access 2007 via MySQL-ODBC-Connector 5.1.6 using the Table Manager. The error occurs only with views.
[24 Jan 2011 15:34] Dan Rosauer
I get the same problem using:

* mysql 5.5.8(x64), 
* odbc connector 5.1.8
* Access 2007
* on a 64 bit machine, running Windows 7

MySQL tables display correctly in Access,  but MySQL views have the text fields in (apparently) Chinese characters.  It is not just a display issue, as the 'Chinese' fields fail to join with an equivalent text field in Access.

See my uploaded screenshot with this bug, and also other discussion of the problem at: http://forums.mysql.com/read.php?65,403947,403947#msg-403947
[24 Jan 2011 15:36] Dan Rosauer
Screen shot of the same MySQL view displayed correctly in MySQL and with Chinese characters via an OBDC link in Ms Access 2007

Attachment: MySQL-Access_characterset_bug.PNG (image/png, text), 191.73 KiB.

[24 Jan 2011 16:11] EDV Wipruef
Hi,

Access binds the mysql-char-fields to binary :-(
[24 Jan 2011 16:43] Dan Rosauer
Thanks E. Langner

Do you know a workaround?  For example, is there a suitable alternative data type one can use in MySQL, or a conversion at the Access end?

And do you think this is therefore a MySQL bug, ODBC or Access??

cheers
Dan
[25 Jan 2011 7:31] EDV Wipruef
Hi,
here is the same problem: http://bugs.mysql.com/bug.php?id=55245
[25 Jan 2011 8:27] Tonci Grgin
Ekkehard, true. I have posted my thoughts there. Feel free to check.
[2 Feb 2011 18:29] Danny Belisle
Hi !
I have the same issue.
Mysql 5.5.8 (XAMMP 1.7.4) 32bits.
ODBC Connector 5.1.5 (Windows 7)

My workaround: Make the link form older version of MySQL. After the link is done with the old version, the view still show the good data even after changing MySQL version. 
The trouble seem to be when you make the firstime link.

Thanks !
[4 Mar 2011 20:32] Lukas Gadola
Definitely a bug. Got exactly the same. Just for views. Works as long as you don't re-attach your views in Access. As soon as you do re-attach to MySQL 5.5.9, the datatype mapping for all character fields of the view is completely messed up.
[4 Mar 2011 20:44] Justin Ram
Well I am glad that the bug handlers made me feel like an idiot back in November for what is DEFINITELY a bug!
Hopefully someone with more clout than me can speak to whomever needs to be contacted to get this bug fixed because it is preventing me from bringin my company to the current version.
[4 Mar 2011 20:56] Lukas Gadola
Yes, Justin, you have done a lot of research on this, thanks.

It should be rather easy to reproduce, either, if someone cared to try:
create a table with a varchar field,
create a view on it,
link it into any version of Access using any version of MySQL ODBC and I'll eat my table if you don't get chinese characters back.
[4 Mar 2011 20:59] Justin Ram
I just wanted to mention after reading through the other thread that the workaround proposed only works with statically linked tables. If you dynamically link your tables with a DSNLESS connection, then the workaround is invalid.
[7 Mar 2011 5:51] Bogdan Degtyariov
Finally, after many attempts I was able to repeat the problem.
Setting the bug status to "Verified".

It looks that Connector/ODBC driver returns wrong type identifiers for varchar/text columns in MySQL Server 5.5.

This could happen because ODBC driver is built using mysql 5.1 client library.
Trying linking it with 5.5 client library. I will post the update soon after giving a test to Connector/ODBC 5.1.8 with 5.5 client library.
[7 Mar 2011 6:30] Bogdan Degtyariov
Linking 5.5 client library did not help. Still see Chinese symbols in where Latin1 was supposed to be.
[7 Mar 2011 12:22] Bogdan Degtyariov
Actual problem is: SQLColumns() returns varbinary type for varchar columns in views.
[9 Mar 2011 3:53] Bogdan Degtyariov
Reported a new server bug (#60386):

http://bugs.mysql.com/bug.php?id=60386

The problem is in 5.5 server.
[12 Mar 2011 0:41] Vincent Bloemen
I upgraded my MySQL development environment from 5.1 to 5.5 and encountered the same problem.

After searching a while i found 2 workaround's for this bug: It still works in VB with an ADODB connection and in MsAccess with Pass through (PT-) queries:

Used ODBC flags:
FLAG_AUTO_RECONNECT = 4194304
FLAG_FOUND_ROWS = 2
FLAG_FIELD_LENGTH = 1
FLAG_BIG_PACKETS = 8
FLAG_NO_BIGINT = 16384
FLAG_NO_BINARY_RESULT = 268435456

ADODB (VB code)
--------
Cstring = "Driver={MySQL ODBC 5.1 Driver};Server=****;Option=272646155;Port=3306;charset=latin1;Database=****;"
set a = new ADODB.Connection
a.open Cstring, "username", "Password"
set q = new ADODB.Recordset
q.open "select * from aMySQLView;", a, adOpenDynamic, adLockOptimistic
msgbox q(0).value
... Close code...
---> returns normal values for tables, views (and procedures)

MsAccess (tested on 2003)
---------
In this workaround you don't have to link the view as a table.
You can open the view correctly by using a Pass Through Query in MsAccess. 

When the connectstring of the PT query equals the connectstring of an existing table in MsAccess, it will use that connection. Even when the User (UID) and Password (PWD) parameter is not present (in the PT-Query), it will connect using the existing connection from an other linked table in MsAccess.

this workaround will not always work in MsAccess. 
Sometimes MsAccess gives an error when you use the PT-query in a linked Subform. an Error pops up telling you that it can't use PT-queries with variant column names (even when you set the column names in the select statement of the PT-query or in a sub-query in MsAccess). I think this is a limitation from MsAccess when using PT-Queries with subforms in generally.

I've only tested this with linked tables created by a visual basic script and with a dns-less connectstring. 

Example Table Connection (connecting to a view = Fail) to mysql 5.5:
--------------------------------------------------------------------------
ConnectString: Driver={MySQL ODBC 5.1 Driver};Server=******;Option=272646155;Port=3306;charset=latin1;Database=****;Uid=****;Pwd=****;
--> tables Works
--> Views fails with wrong Column Types and Charsets

Pass Through query in MsAccess with connectionString to mysql 5.5 View:
--------------------------------------------------------------------------
MsAccess PT Query: Select * from aMySQLView;
ConnectString: Driver={MySQL ODBC 5.1 Driver};Server=*****;Option=272646155;Port=3306;charset=latin1;Database=****;
--> tables Works
--> Views Works
(optionally you can add the UID and PWD parameter in the connectstring as well)
--------------------------------------------------------------------------

Updating my production environment now from 5.1 to 5.5 would result in lots of applications creating errors. Which means changing lots of code and queries in the applications to use this workarounds.
So i still hope this bug is getting fixed soon.
[30 Mar 2011 11:32] Marcelo Souza
Another problem, same bug: LONGTEXT columns are converted, in MS-Access, to OLE Object column type.
[6 May 2011 9:45] Lukas Gadola
Interesting: bug does not seem to hit when using 64-bit version of MS Access

My configuration: MySQL 5.5.11 64-bit on Windows 2008 R2
Access 2010 (14.0.4760.1000) 64-bit:

managed to link a table correctly

same MySQL server, but Access 2007 (12.0.6545.5004) 32-bit or Access 2010 32-bit:

same table links wrongly
[6 May 2011 9:50] Lukas Gadola
this is a potential workaround:

re-link your tables in a 64-bit version of MS Access (2010).
once the tables are linked you can continue to use the database on older and 32-bit versions of Access.
[11 May 2011 17:53] 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:15] 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.
[15 Jun 2011 12:43] Jim Fiorini
I see this bug is being reported under Bug #'s 55245, 58094 and 60386.

I tested the OpenLink Lite ODBC driver (http://www.openlinksw.com/) with MySQL 5.5 and the character sets for the Views are correct. No issues.

I tested with an old MySQL 3.51.26 driver and ran in to the same issue as with the 5.1.8 driver.

Any update as to the status of this bug and an ETA on a fix?
[20 Jun 2011 12:07] Marcello Rosiello
the workaround suggested by Vincent Bloemen works fine
I created a pass-through query in Access 2007 copying exactly the sql code of my view in mysql (the one that didn't work as a linked table).
Everything worked fine.
Thanks Vincent
[19 Jul 2011 14:08] Louis Breda van
Hello,

I have been reporting this problem for a long time. It is very nasty. Its time to see fixed drivers !!!

Sincerely,

Louis
[11 Aug 2011 12:12] Bogdan Degtyariov
The server bug #60386 related to this problem has been fixed.
Please use MySQL 5.5.15, which is available for downloading from

http://dev.mysql.com/downloads/mysql/