MySQL Bugs: #22859: Migration Toolkit fails when migrating from Sybase

Bug #22859	Migration Toolkit fails when migrating from Sybase - jdbc and workaround
Submitted:	30 Sep 2006 13:46	Modified:	6 Sep 2007 5:32
Reporter:	Cesar Arnold	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S1 (Critical)
Version:	5.0-r4	OS:	Windows (Windows)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

Description:
Goog-morning,
I have installed Migration Toolkit 5.0-r3 for Windows to migrate a Sybase SQL Server version 11.9.2 to Mysql version 5.0.4

The Mysql and Sybase are installed on a Debian linux and are running normally.
The Migration Toolkit is installed on a Windows 2000 server and running normally (production).

The Sybase Client 11.9.2 is installed on the Windows 2000 server and connecting to the Sybase server normally. A Sybase ODBC account was created on Windows 2000 ODBC Administration and is working.

I was surprising and happy the the Migration Toolkit has so many resources and have "native" support to Sybase then I started the migration.
After starting and finished the hole process after checking the results, I found the Migration report log and found that many messages said like:
---------------------------------------------------------------
...
Processing schema dbo ...

Getting the number of rows of table ACESSO_USUARIO_AGENDA
SELECT count(*) AS num FROM "dbo"."ACESSO_USUARIO_AGENDA"

Transfering data from table ACESSO_USUARIO_AGENDA (6/6 rows)
SELECT "CodigoDoUsuario", "CodigoDaAgenda", "TemPermissaoDeLeitura", "TemPermissaoDeEscrita", "CodigoDoUsuarioQueRegistrou", "DataDoRegistro", "CodigoDoUsuarioUltimaAlteracao", "DataDaUltimaAlteracao" FROM "dbo"."ACESSO_USUARIO_AGENDA"

ERROR: The following error occured while transfering data from ACESSO_USUARIO_AGENDA
The identifier that starts with '"' is too long.  Maximum length is 28.
---------------------------------------------------------------

Also, after checking the tables using Mysql client (and also Webyog gui tool) I found that many tables were empty. The structure and index are ok.
The original Sybase tables have data and in Mysql it were migrated empty. Also, the views, triggers and stored procedures were *not* migrated. No views, no triggers and no sps. Regarding this, up to me, there is no problem because I can migrate manually all these stuffs, even it will give a lot of work writing again. I am concerned about the migration of the structures and mainlly with the data, that were not migrated in many cases.

So, I tried again using the Webyog tool (the free version that only allows to migrate one table by time. just trying to imagine when you have hundreds of tables how it could be ...). I have unchecked the option to migrate the structure and informed to import just the data from one table (one of those empties). After import the data "sucessfully", now the table have all rows, but some data still missing. The "datetime" fileds in Mysql were filled with "00/00/00 00:00" and the original Sybase are filled with valid dates. I now that Sybase uses the format "mm/dd/yy hh:mm" on "smalldatetime" fileds and maybe this could not be understood by the Migration Toolkit tool, I don't know exactly what happened.

Also I didn't understand why some tables were not migrated (data) and using another tool it could, even filled with incorrect dates.

I didn't find how to attach the report log from the Migration Toolkit and it could give you more detailed information about the hole migration process. But the reports were saved and the LUA script either. Just tell me how I can send it if necessary.

Well, my question are how can Sybase be migrated to Mysql and how can be done ? (initially just structures and data are enought to me)

Thanks and I hope I can give enough info to understand the problem and have a answer about this matter.

How to repeat:
Install mysql server running on linux.
Install Sybase SQL Server on linux with tables, date fileds, and valid data.
Install Sybase client.
Install Migration Toolkit on Windows 2000.
Start the migration connecting to Sybase conneting thru the Sybase ODBC driver.

Suggested fix:
upgrade the Sybase client to a newest ?

LUA migration script

Attachment: migration.lua (application/octet-stream, text), 10.06 KiB.

Report log from Migration Toolkit

Attachment: reportlog.zip (application/x-zip-compressed, text), 45.17 KiB.

Hi, after submit the bug could attach 2 files:
migration.lua (migration script)
reportlog.zip (reportlog.doc generated by the Migration Toolkit after the migration process).

Thanks again.

I changed the Severity from Serious to Critical because I didn't find a workaround yet.

Thank you for a problem report. Please, try to repeat with a newer version, from GUI Tools 5.0-r4, and inform about the results.

Report log after data migration using version 5.0-r4

Attachment: report.txt (text/plain), 83.96 KiB.

Hi Valeriy Kravchuk and everybody,
I was out for a while and could not try immediatelly the last release (r4).
I'm sorry, but still happening the same reported bugs.
The data are not imported (just some tables) and the report.txt captured from the Migration Toolkit is attached.

If you need more details, please advise.
So, I will wait for your comments.
Thanks.

Verified based on uploaded report.txt analysis. What is that max lenght 28 messages about? I am not sure is it apply to Sybase, but surely not to MySQL, according to http://dev.mysql.com/doc/refman/5.0/en/legal-names.html.

Good-morning,
thanks for this reply. 
the question above was gave around 3 or more weeks after my reply and I thought the process of fixing the bug was more quicly.
anyway the number 28 does not represents nothing to Sybase. Sybase max field lenght is 30 and all field names are 30 lenght or less (they come from the sybase database migration).
Can I help in another question to accelerate the bug fixing ?
Can this message be generated by the Sybase ODBC client or it´s from the Migration toolkit help message system ?

Thanks and regards.

Based on this post http://www.dbtalk.net/946659-post3.html it seems there is a Sybase bug that counts the " to the number of characters in the identifier, reducing the real usable characters to 28 when quoted.

As " is returned by Sybase for 
  targetQuoteChar = targetConn.getMetaData().getIdentifierQuoteString();
there is no easy way of changing that to '.

Could you try to the other suggestion, running with 'set quoted identifier off'?

Thanks Michael,
I'm not in my Migration Toolkit installation right now and cannot test at this moment. I would like to ask if the option to 'set quoted identifier off' it's in the Migration Toolkit or do we need to set it on ths Sybase server ? 
Sorry, but I'm exciting to see this migration finished.

Thanks again.

Hi, That would be a Sybase setting. Btw, which version of Sybase are you running?

Hi Michael,
thanks, the Sybase version is Adaptive Server Enterprise 11.9.2.
I'll try again setting 'set quoted identifier off', but as I'm informed the default value for ASE is off and we didn't change it.
Regards.

Report file from nov/28/2006 migration

Attachment: report.txt (text/plain), 80.88 KiB.

Hi Michael,
we finished the precedure of migration using the setting of 'set quoted_identifier off' and unfortunatelly we got the same results, as you can see on the attached files from the Migration Toolkit.

Let me say what what steps were done.
- started the Sybase ASE;
- opened a client connection using the Sybase client (isql) using the "sa" account;
- in the client prompt :
1> set quoted_identifire off
2> go
1>
the command was accepted;
- after that we started the Migration toolkit and started the migration, and we got the same results ...

I'm not sure if the 'set quoted_identifier off' turns all the Sybase environment with this new setting definivelly and I don't know another way to do it permanent (if there is...). So maybe there is a way to continue with this issue but I don't know what to do.

We used the last Migration Toolkit version downloaded from your site today (1.1.6). Is this the first migration from Sybase ? Using 30 lenght fields in a database installation isn't a new issue and any table can use this size ...

If there are other stuffs we can do, please advise.
We hope this can help and we're waiting for your reply.
Thanks.

Good-morning Mysql team,
after the last message from Nov/28, is there any progress on this subject ?

We are waiting for the advantages of using Migration Toolkit and we don´t know if we can still waiting for it. There is a database to be migrated or if not, maybe forget the migration to mysql ans still using Sybase.

We could try to migrate manually all the +-500 tables, +-400 procedures, ... and it will take a loooong time. But regarding data, how can we migrate it if we cannot use the toolkit ? Write scripts for each table ?

Export to text and import into mysql did not work because the date formats are differents. Sybase stores in mm-dd-yyyy hh:mm or Mmm-dd-yyyy hh:mm and mysql does not import the data.

So, is there a possibility to wait for a Migration Toolkit version that can change this situation ? 

Thanks and we hope can receive an answer.

I do not see a possibility to fix this issue since the cause for it is on the Sybase side and I am not aware of a workaround I could apply on the Migration Toolkit side.

Good morning,
thanks for the reply 5 months after the last contact.

Is there a way we can help? maybe responding in a period of time not so looong isn't ?

A question I would like to know : is this problem just for this Sybase release 11.9.2 (as reported) or it happens to newest versions too? Depending on the answer we could try to migrate to a newest version of Sybase and then to Mysql.
If it's for all Sybase versions, it was not tested and released ? or what Sybase version was tested to announce the release ?

We know that the problem is not just up to you from Mysql because we need to migrate as soon as possible, so would be better if we could keep contact and receive feedbacks.

Arounf six months ago when the Mysql announced in the site that the Migration Toolkit was free of errors (or almost) and who find an error would receive and iPod, what do they would like to say? I sincerelly do not want to receive and iPod, but just use the tools and hope they work as annouced.

I hope we can continue working on this matter, try to find a workaround or solution, and that we can receive an answer as soon as possible (please, do not answer after some months).

Thanks.

Hello Michael,
I found an example that maybe can help at: http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=50d07893

As I know, the "set quoted_identifier off" is just for the current session.
If I issue the command at the Sybase client and then start the migration it does not matter to the Migration Toolkit because it opens another connection to the server, and the "quoted_identifier" for this new session wasn't modified.

So maybe the solutuion is to insert the "quoted_identifier" parameter during the JDBC connection in the Migration Toolkit. It would be something like (text extracted from the original url above):

--------------------------------------------------------------------------------
jdbc:sybase:Tds:db_server:port_no/database_nameSQLINITSTRING=set quoted_identifier off?

Where db_server is the name of the database server.

Where server_port is the port number for the data source connection (the Sybase default port is 5000).

Where database_name is the name of the database you want to use.

For example:

jdbc:sybase:Tds:ps-db:5000/training_dept?SQLINITSTRING=set quoted_identifier off

Note: The information in this field is case-sensitive.
--------------------------------------------------------------------------------

As you can see, the JDBC driver accepts this way and it could give us the workaround to connect to Sybase setting the "quoted_identifier off" as we need.

During the Migration Toolkit connection, it could have a checkbox asking to use or not the "quoted_identifier" on/off.

I hope it can help. Anyway, please give us a feedback.
Thanks.

Reopening bug for further comment on potential work around

Good night everybody,
great to hear this bug was re-opened.

I was thinking it was not closed and someone was working on it.
But anyway, thanks a lot for the news.

I hope we can help if something from our side is necessary.
Please advise.

Thanks and regards,
Cesar Arnold

I have now added a new option to the Data Mapping Options, Advanced Options section that is called "Do not use quotes for source database connection" which will prevent the tool from adding " to the identifiers and therefore should present a workaround for the Sybase bug.

Please find a patch here.
ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-migration-toolkit-1.1.12-patch-1.zi...
ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-migration-toolkit-1.1.12-patch-1.zi...

Installation Instructions
-------------------------

The zip file contains a MySQLMigrationTool.exe which needs to replace the existing one in C:\Program Files\MySQL\MySQL GUI Tools for 5.0

The modules.zip file needs to be extracted to 
C:\Program Files\MySQL\MySQL GUI Tools for 5.0\java\com\mysql\grt\modules and replace the .java and .class files there.

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Great news Michael!

Thanks for all your help on this matter.
We will test from now on.

Success and congratulations.