MySQL Bugs: #20267: impropely migrating the fields in mssql containing apostrophe into mysql

Bug #20267	impropely migrating the fields in mssql containing apostrophe into mysql
Submitted:	5 Jun 2006 11:22	Modified:	14 Sep 2006 14:58
Reporter:	d k krishna damisetti	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	M.T 1.0.23 rc, MySQL version 5.0.18	OS:	Windows (win XP)
Assigned to:		CPU Architecture:	Any

Description:
we have a database in mssql. we migrated it to MySQL using MySQL mIgration tool. In the source database we have a varchar fields containg '(apostrophe) with   in the field. In MySQL it is displayed as ?.

How to repeat:
insert data into mssql which contains apostophe( eg: o'conner) . migrate it using migration tool. it is displayed as '?'.

Thank you for a problem report. Please, specif the exact version of MySQL server used. Try also to repeat with a newer version of MT, 1.0.25, and inform about the results.

Thank you for your response. 
I repeated the migration process with migration tool v 1.0.25. But the porblem was not solved. I am using MySQL server version 5.0.18.

Try to repeat with a newer version of MySQL server, 5.0.22, and inform about the results.

changed to MySQL server version 5.0.22 and agian performed migration from mssql to mysql. The problem was not solved. apostrophe in source data base is displayed as '?' in mysql data base.

What character set/code page/collation is used in source MS SQL database?

MS SQL Server collation is SQL_Latin1_General_CP1_CI_AS.

Please, add characterEncoding=latin1 to your JDBC connection string for MySQL, try to repeat migration and inform about the results.

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

added connection string as characterEncoding=latin1 and clicked next.

in that screen, i got the following error 
 an error occured.

a red cross(x) appeared instead of tick before test connection to target database system.

in message log:

Connecting to source database and retrieve schemata names.
Initializing JDBC driver ... 
Driver class MS SQL JDBC Driver
Opening connection ... 
Connection jdbc:jtds:sqlserver://192.168.0.101:1433/portal;user=sa;password=
Fetching schemata list.
SELECT TABLE_SCHEMA AS SCHEMANAME,  max(TABLE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA UNION SELECT ROUTINE_SCHEMA AS SCHEMANAME, max(ROUTINE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.ROUTINES GROUP BY ROUTINE_SCHEMA
Return schemata list.
Schemata names retrieved successfully.
The connection to the target database could not be established (error: 0).
ReverseEngineeringMysqlJdbc.getVersion :No suitable driver
Details: 
java.sql.DriverManager.getConnection(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)
com.mysql.grt.modules.ReverseEngineeringGeneric.establishConnection(Unknown Source)
com.mysql.grt.modules.ReverseEngineeringGeneric.getVersion(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)

Initializing JDBC driver ... 
Driver class MySQL JDBC Driver 3.1
Opening connection ... 
Connection characterEncoding=latin1

My problem once again.
When the original character in MS SQL Server is ’ (not apostrophe '), it displayed in MySQL as ?. This is also the case when – ( not hiphen -) appeared.

Please, try to repeat with a newer version, 1.1, available at http://dev.mysql.com/downloads/gui-tools/5.0.html, and inform about the results.

I installed MySQL GUI Tools Bundle for 5.0 and mentioned characterEncoding=latin1 in jdbc connection string for MySQL.

I got the same errors posted above.

Thank you.

Hi,

could you try again with the MT 1.1.1, using the normal parameters, not the JDBC connection string? 

If that still does not work, please specify the JDBC connection string to the MS SQL database in the form

jdbc:jtds:sqlserver://%host%:%port%/%database%;user=%username%;password=%password%;charset=utf-8;domain=%domain%"

Please note the charset=utf-8 parameter the jtds needs to convert the charset to UTF8.

Thanks!

hi,
 
I was able to connect with out using jdbc connection string.
I also used jdbc connection string for MS SQL server.

I could connect using both procedures but when i migrated the data, some special characters are not migrated propoerly

i also repeated the above procedure using MT 1.0

thanks,

Could you please copy both JDBC connection strings you use here? The one for MSSQL and the one for MySQL. For me they look like this.

MSSQL:
jdbc:jtds:sqlserver://192.168.1.87:1433/Northwind;user=mike;password=XXXXXXX;charset=utf-8;domain=

MySQL:
jdbc:mysql://localhost:3309/?user=root&password=XXXXXXX&useServerPrepStmts=false&characterEncoding=UTF-8

With these settings the apostrophe is migrated correctly.

Please also try to migrate the MS SQL Northwind database and check if the issue occurs there too.

Thanks

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