MySQL Bugs: #58094: MS Access ODBC DSN-Less connection displays wrong characters

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:	Windows (X86 & X64)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	5.1, 5.5, character sets, Latin1, MS Access

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.

bad characters of same view of table

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

good characters of same view of table

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

Hi Justin,

Have you tried setting CHARSET=latin1 (or other charset name relevant to your data) in the ODBC connection string?

Just tried setting CHARSET flag in ODBC connection string, makes no difference.

Please provide the connection string.
Which character set you are setting? 
Try UTF8

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.

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

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.

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.

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");

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?

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.

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.

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

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

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.

Hi,

Access binds the mysql-char-fields to binary :-(

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

Hi,
here is the same problem: http://bugs.mysql.com/bug.php?id=55245

Ekkehard, true. I have posted my thoughts there. Feel free to check.

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 !

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.

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.

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.

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.

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.

Linking 5.5 client library did not help. Still see Chinese symbols in where Latin1 was supposed to be.

Actual problem is: SQLColumns() returns varbinary type for varchar columns in views.

Reported a new server bug (#60386):

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

The problem is in 5.5 server.

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.

Another problem, same bug: LONGTEXT columns are converted, in MS-Access, to OLE Object column type.

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

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.

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.

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.

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?

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

Hello,

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

Sincerely,

Louis

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/