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: | |
Category: | Connector / ODBC | Severity: | S2 (Serious) |
Version: | 3.51 and 5.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | access, microsoft, ODBC, utf8 |
[23 Mar 2007 16:28]
Fred Pauwels
[23 Mar 2007 16:36]
Fred Pauwels
Realised this should also appear in the Connector/ODBC bugs category
[26 Mar 2007 7:39]
Fred Pauwels
-
[11 Apr 2007 11:36]
Tonci Grgin
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.
[11 Apr 2007 11:59]
Fred Pauwels
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.
[11 Apr 2007 12:05]
Tonci Grgin
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.
[11 Apr 2007 12:49]
Fred Pauwels
Here are a table dump, unfortunaely I am not allowed to release any content though, and my ini file.
[13 Apr 2007 8:29]
Tonci Grgin
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...
[13 Apr 2007 9:23]
Fred Pauwels
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.
[13 Apr 2007 10:02]
Tonci Grgin
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?
[13 Apr 2007 10:13]
Fred Pauwels
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)
[13 Apr 2007 10:46]
Tonci Grgin
Thanks Fred. Am I right to presume that you see clearly all the words entered in that 3395 rows in console?
[13 Apr 2007 11:02]
Fred Pauwels
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 ?
[13 Apr 2007 11:20]
Tonci Grgin
Fred, thanks for great summary! I'm consulting with ODBC devs now. Hope to be back with good news soon.
[13 Apr 2007 14:37]
Tonci Grgin
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"
[13 Apr 2007 14:38]
Tonci Grgin
5.0.38BK and c/ODBC 2007-04-12 work
Attachment: 27403-5.jpg (image/jpeg, text), 20.66 KiB.
[13 Apr 2007 15:10]
Tonci Grgin
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.
[13 Sep 2007 18:08]
Jim Winstead
This should work with Connector/ODBC 5.1. Needs to be re-verified.
[13 Sep 2007 21:42]
Tonci Grgin
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 |
[2 Oct 2007 6:40]
Fred Pauwels
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.
[17 Oct 2007 19:19]
Tonci Grgin
Fred I'm glad it works now. Thanks for your feedback and your interest in MySQL!
[29 Jan 2010 19:33]
abub ela
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!