MySQL Bugs: #12748: Migration from access with utf8 data in latin1 fields broken

Bug #12748	Migration from access with utf8 data in latin1 fields broken
Submitted:	23 Aug 2005 6:07	Modified:	8 Jun 2006 16:37
Reporter:	Aku Kauste	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S1 (Critical)
Version:	1.0.13	OS:	Windows (Windows)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

Description:
We have utf8 data in MS Access database. Access does not support utf8 fields so the data is in latin1 fields. Migration from Access to MySQL fails with this data on capital scandic letters: ÄÖÅ and with some cyrillic (russian) letters also.

How to repeat:
1. Create Access database with some text fields
2. Fill it with utf8-data with capital scandic letters (ÄÖÅ)
   - We do this from web-form, but you can probably just paste the string below
3. Run MySQL migration toolkit
4.  Check the data from MySQL with QueryBrowser

  Originally: HÃ„MÃ„RÃ„KYTKIN
  In MySQL: HÃ?MÃ?RÃ?KYTKIN

Suggested fix:
Data should migrate properly. To prove that it is possible try the same migration with Access to MySQL Pro 2.3

After the migration you should be able to alter the field to binary and then to utf8 text/char.

Hi!

Can you please send me your MDB file so that we can test this out ?

Thanks a lot!

test case 1

Attachment: utf8_test.mdb (application/msaccess, text), 136.00 KiB.

Sorry, but I wont be in the office before friday, so this is not the exact mdb file, but you should be able to reproduce the problem with this file. 

Table1 and field txt1

If, for some reason this does not reproduce the problem I'll get back to you on friday.

Hi!

Thanks for sending me the MDB file.  I was able to reproduce your described behaviour with it.

I have tried the following steps, but didn't solve the problem totally:

1.  Created the structure in MySQL:

  CREATE TABLE `table1` (
  `id1` int(11) NOT NULL,
  `txt1` varchar(50) default NULL,
  PRIMARY KEY  (`id1`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8

2. Link the MySQL table in MS Access (can use utf8_test.mdb).  Define the DSN first before linking the table.

3. Append record from MS Access to the linked MySQL table from within MS Access.

The linked MySQL table will display the string correctly from MS Access.  But when viewed from MySQL it will display:

mysql> select * from table1;
+-----+--------------+
| id1 | txt1         |
+-----+--------------+
|   1 | HÄMÄRÄKYTKIN |
+-----+--------------+
1 row in set 
 
mysql>

I think the problem here is the availability of a migration toolkit that can completely translate MS Access tables (or other types of databases) to MySQL.

We are experiencing the same thing as we are in the process of migrating our database from MS Access to MySQL as well.  We have tried different software and method like, Intelligent Converter (ACC2MySQL), Navicat Import Wizard, Exporting table from MS Access through ODBC and linking predefined tables in MySQL then populate them with data from MS Access using APPEND query.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html