MySQL Bugs: #27403: UTF8 support in db using the ODBC connector 5.0 to connect MsAccess to MySQL 4.1

Bug #27403	UTF8 support in db using the ODBC connector 5.0 to connect MsAccess to MySQL 4.1
Submitted:	23 Mar 2007 16:28	Modified:	2 Oct 2007 6:40
Reporter:	Fred Pauwels	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51 and 5.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	access, microsoft, ODBC, utf8

Description:
Windows XP sp2
MySQL 4.1.15 and 4.1.22
ODBC 3.51.14 and 5.0 beta
Character set on server/database/tables/fields: utf8 collation utf8_unicode_ci

One of our client wanted to translate a database created in MsAccess to MySQL 4.x and connect to the new translated database using the existing MsAccess frontend.
The data entered consisted of a mix of character sets from different languages therefore we needed to implement support for utf8.

The translation was achieved through the MySQL migration toolkit with fine tuning for know issues (primary keys,timestamp etc)

First connection was achieved using the ODBC connector 3.51.14 but as it turned out, utf8 was still a problem, we were unable to insert latvian or other eastern characters in the database, we tried all known fixes as reported here for instance:

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

We then decided to turn to the 5.0 connector, but that caused a loss of connection with 'ODBC call failed' or 'acces denied to user' messages, looks like others had similar problems as here:

http://www.teknoids.net/?q=node/7543

Searching deeper we found a possible problem could be the MSJET40 dll as reported here:

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

We tried the workaround, copying an older dll with no success.

Below is the solution that we came up with, it seems to work but is a very DIRTY approach and does not feel really reliable, we would like to know if any other stable workaround have been found or if the connector will solve this issue in its final release. We are aware that MySQL 4.1 is now in end of life but please do realize that customers might be reluctant to update to 5 we need to solve this issue, we have read many posts dated from up to 3 years ago from people facing all these issue and crying for proper utf8 support.

How to repeat:
Create a connection using ODBC 3.51.14 and MySQL version 4.1.x - not possible to enter data using special characters, use ŭ for instance, you enter it in access but it is not saved in the database.

You can however enter this character using the MySQL Query Browser (Other tools like SQLyog latest enterprise version prooved not to work either).

Create a connection to the same database using the connector 5.0 beta, cannot get external data and link the tables in MsAccess.

Suggested fix:
a) Open Data Sources (ODBC) in control panel/admin tools

b) create a DSN using connector 3.51

	Login Tab:

		datasourceName
		description
		serverName
		userName
		password
		databaseName

	Advanced Tab

		check 'Don't Optimize Column Width'
		check 'Return Matching Rows'
		check 'Change BIGINT Columns to Int'

c) Open an access database, get external data, link tables
   Select ODBC databases () in 'Files of type'
   Select the 'datasourceName'
   Select the tables to link

d) Quit access

e) Open Data Sources (ODBC)

f) Delete the DSN created in step b

g) Create a new DSN using the same 'datasourceName' as in step b but now use connector 5.0

	Login Tab:

		datasourceName
		description
		serverName
		userName
		password
		databaseName
		portNumber (or leave empty - 3306 is the default)

h) Open the access database

   DO NOT RELINK THE TABLES !
   
   Double click on a table - this should open a MyODBC dialog as in step g -   fill in the missing information

i) The database should now be linked through the MyODBC 5.0 and accept utf8 entries

Realised this should also appear in the Connector/ODBC bugs category

Hi Fred and thanks for your report.

It's a bit puzzling however so I'll try this approach:

> we tried all known fixes as reported here for instance: http://bugs.mysql.com/bug.php?id=3348
See my last post in that report. I was able to extract Croatian and Hebrew characters correctly from 4.1 in application written in VS2005, so it should work. So, if data is in UTF8 MyODBC 3.51.14 should be enough.

> like others had similar problems as here: http://www.teknoids.net/?q=node/7543
This is RT.M issue. Not applicable here.

> reported here: http://bugs.mysql.com/bug.php?id=9932
Well, I'm too MS beta tester, nothing new there. I'm just a fool not to charge them for gigantic service packs I need to download constantly...

Now, what is the actual question? You are unable to load/store UTF8 data with connector/ODBC 5 in MySQL server 4.1.x?
If so, you should be aware of several things:
 - UTF storage is different in 5.0 than in 4.1. Please check changelogs.
 - Connector/ODBC 5 is still beta, so errors are likely to happen.
 - I will have to check with ODBC dev team if they support both servers. I presume it is so but need to check.
 - What version of c/ODBC 5 are you using?

If this is a problem, please post DDL script with tables and data suitable for import in 4.1.x server so I can check.

Hi,

We use an msAccess frontend to connect to a MySQL database (server 4.1.15)
Data stored in the database is a mix of all European and other languages, including cyrillic. Server, database, tables and text fields are all set to utf8.

Ideally, I would like to use the stable connector 3.51.14 to make the connection, but when I do that I am not able to insert certain characters like the ŭ for instance, when you save the data in access in turns into a regular u.

I just tried again with the connector 3.51.14 and by adding the SET NAMES cp1250 in the initial statement, no effect, I did try that before as with SET NAMES utf8.

Since we need to be able to use those characters, I tricked the system by using the odbc connector 5.0 beta, if you do that you will see that you can then save those characters using MSaccess.

But using this setup causes other problems, that is why I would like to stick with the 3.5 if possible, but there seem to be no support for utf8 with that connector.

Yes I see now. SET NAMES to_what_ever_you_need must be done when initializing ODBC connection thus it's not suitable if you have several different languages in same table. This will not be fixed in 3.51 branch.

I will have to ask you once more to do mysqldump of the table in question (remove any sensitive data or attach it with "Private" flag). Also, please attach my.ini (or my.cnf) from your server.

Here are a table dump, unfortunaely I am not allowed to release any content though, and my ini file.

Fred, let me clarify this:
 - you have this mix of languages all put into *same* table?
If so, what do you get in mysql cl client when issuing "SELECT * FROM my_table"? If different rows are entered in different character sets, I expect problems, not necessarily connected to MyODBC...

Hence I use utf8 and have no problems with that.
I also work on a php site retrieving info from that same database and have no problems with any of my queries.
We have several multilingual systems running online using mysql utf8 and php and have not experienced problems.

Fred, working php connector doesn't answer my questions. Does the same table have records with different charsets and what does "SELECT * FROM ..." in mysql command line client has to display?

Data are entered in the same table, the table and fields are set in utf8.
In rows, we have words in croatian, latvian, french, english, dutch etc

From the command line: issuing select * from tcontact for instance retrieves 3395 rows (0.24 sec)

Thanks Fred.

Am I right to presume that you see clearly all the words entered in that 3395 rows in console?

Ok, 

let me re-focus here: in debate we have the fact that we are unable to enter certain characters into the database by using the msAccess frontend and the connector 3.5 in a windows environment.

We tried to use the 'set names to whatever' in the initial statement, does not work, we agree that if I were to set names to cp1250 for instance we should be able to enter certain characters but others might not work, however this should not be the case using utf8, but none of them are entered correctly even using utf8.

Now of course if I use the command line, some characters might not show properly, but we know the data is there and is correct, it is just not interpreteda and showed correctly as it happens with other tools not supporting utf8 (sqlyog is one of them), also when you use another mean of showing the data like in a web browser and you specify utf8 for the content all data show correctly.

HOWEVER if you trick the system and use the odbc connector 5 beta none of the above is an issue, it supports utf8, and you are able to retrieve and show and insert or update data properly using the msAccess frontend, but the connector 5.0 causes other issues: you are not able to properly relink the tables, in fact if you do not apply my trick described in the suggested fix, you cannot even get a decent connection.

The question is, will the odbc connector 3.5.x ever support utf8 correctly, if not, at what stage is the beta version of 5, can we expect a stable release, reverse compatible with mysql 4.1, will we have to change the server to use mysql 5.0 with a stable connector 5 to ensure utf8 compatibility in the future ?

Fred, thanks for great summary! I'm consulting with ODBC devs now. Hope to be back with good news soon.

Notes:
 - Trying with 5.0.38BK, MyODBC 3.51.14 (SET NAMES utf8) crashes my Access
 - Trying with 5.0.38BK, c/ODBC 5.0 2007-04-12 works as expected (see attached image)
 - Trying with 4.1.22BK, c/ODBC 5.0 2007-04-12 - "ODBC call failed"

5.0.38BK and c/ODBC 2007-04-12 work

Attachment: 27403-5.jpg (image/jpeg, text), 20.66 KiB.

Fred, I've cleared this problem with our ODBC devs and here's what I've found out:

 Fact: Access is using utf16(wide strings on windows) to communicate with the driver.
 Consequence: There's *no* workaround nor there will be in MyODBC 3.51

 Fact: MyODBC 3.51 doesn't use INFORMATION_SCHEMA (I_S) database
 Consequence: Table linking works with MyODBC 3.51
 Fact: c/ODBC v5 makes extensive use of INFORMATION_SCHEMA (I_S) database
 Consequence: Table linking is problematic with c/ODBC v5

There has been talk of making c/ODBC v5 less I_S dependant but some things are just to fragile to be done without it, like foreign keys for example.

Of your "trick": MyODBC 3.51 creates DSN becuase its able to show the table schema without using I_S. Pointing that DSN to c/ODBC v5 afterwards is a neat trick to get Unicode support. Nice touch.

Conclusion: I'm sorry but, for now, all I can suggest is to upgrade to MySQL server 5.x and use c/ODBC v5.

This should work with Connector/ODBC 5.1. Needs to be re-verified.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Explanation: Hi Fred. This problem is fixed in new MyODBC 5.1 release. You do not need to set any c-set related option in MyODBC for this to work (see attached image).

A paste from Access 2003
Id	c1
1	Кириллица      Ukr. Cyrillic
2	Latin text     Latin1 text
3	šđčćžŠĐČĆŽ     Cro. text

Host OS: XP Pro SP2
MySQL server version: 4.1.22-log
mysql> show variables like "%char%";
+--------------------------+--------------------------+
| Variable_name            | Value                    |
+--------------------------+--------------------------+
| character_set_client     | utf8                     |
| character_set_connection | utf8                     |
| character_set_database   | utf8                     |
| character_set_results    | utf8                     |
| character_set_server     | utf8                     |
| character_set_system     | utf8                     |
| character_sets_dir       | C:\mysql\share\charsets/ |
+--------------------------+--------------------------+

| ttest | CREATE TABLE `ttest` (
  `c1` varchar(20) default NULL,
  `Id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Hi,

first of all thank you for your great work and feedback.

We have been testing the connection with the new connector 5.1 in our test environement and everything seems to be working fine, we are still monitoring this before going to production with the new connection, but at the moment we do not have any more problems with the characters.

Fred I'm glad it works now. Thanks for your feedback and your interest in MySQL!

I wonder if this has been solved only for InnoDB tables but not for MyISAM

My environment:
SERVER:
- WinXP SP2
- MySQL Server 5.1.37-community
CLIENT:
- WinXP SP2
- MySQL ODBC 5.1 Driver: mysql-connector-odbc-5.1.6-win32.msi
- Microsoft Access 2002 SP2

I use linked MySQL tables on a MS Access frontend.

- If the table is InnoDB, I have no problems to UPDATE/INSERT values in VARCHAR fields
- But for MyISAM tables, I get this error when inserting:

ODBC: INSERT failed on linked table 'testing_MyISAM_table'.
Incorrect string value: '\xC4\x9Bhor\xC3...' for column 'testing_varchar_field' at row 1 (#1366)

This was the string (Czech characters): 'Jonas Rěhorék'

All my char-related server variables are set to 'utf8'
 
- But on the same MyISAM table, if the field type is TEXT or CHAR, I get no errors though!