MySQL Bugs: #40198: oracle to mysql migration hang up on fetch information pk

Bug #40198	oracle to mysql migration hang up on fetch information pk
Submitted:	21 Oct 2008 10:02	Modified:	22 Oct 2008 15:33
Reporter:	Christophe CASSAIGNE	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S1 (Critical)
Version:	1.1.13	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	fetch information pk, migration, Oracle

Description:
When i try to migrate a small schema from oracle database to mysql, i succeed to migrate.
But when i try to migrate a big schema, mysql migration toolkit hangs up on the 'fetch information pk'. the sql statement where mysql migration toolkit stops is :
Fetching column information.
SELECT tc.TABLE_NAME, tc.COLUMN_NAME,  tc.DATA_TYPE, tc.DATA_TYPE_MOD, tc.CHAR_LENGTH, tc.DATA_LENGTH,  tc.DATA_PRECISION, tc.DATA_SCALE, tc.NULLABLE, tc.DEFAULT_LENGTH,  tc.DENSITY, tc.NUM_NULLS, tc.NUM_BUCKETS, tc.CHARACTER_SET_NAME,  tc.DATA_DEFAULT FROM ALL_TAB_COLUMNS tc, ALL_TABLES t WHERE tc.OWNER=? AND t.OWNER=tc.OWNER AND tc.TABLE_NAME=t.TABLE_NAME ORDER BY tc.TABLE_NAME, tc.COLUMN_ID
Fetch PK information.
oracle database version : 9.2.0.1.0
thanks.

How to repeat:
oracle database version : 9.2.0.1.0
mysql migration toolkit 
oracle to mysql

Thank you for a problem report. Had you tried to run this statement in SQL*Plus directly (from the same user as in MT, sys?):

SELECT tc.TABLE_NAME, tc.COLUMN_NAME,  tc.DATA_TYPE, tc.DATA_TYPE_MOD, tc.CHAR_LENGTH,
tc.DATA_LENGTH,  tc.DATA_PRECISION, tc.DATA_SCALE, tc.NULLABLE, tc.DEFAULT_LENGTH, 
tc.DENSITY, tc.NUM_NULLS, tc.NUM_BUCKETS, tc.CHARACTER_SET_NAME,  tc.DATA_DEFAULT FROM
ALL_TAB_COLUMNS tc, ALL_TABLES t WHERE tc.OWNER=<owner of big schema> AND t.OWNER=tc.OWNER AND
tc.TABLE_NAME=t.TABLE_NAME ORDER BY tc.TABLE_NAME, tc.COLUMN_ID;

How many rows it returns and how long it works?

When i try to run the statement : 15051 lines are returned and the time to return this rows is less than 1 minutes.

And how long you had waited with MT? It may take more than 1 minute, so, please, wait for an hour or so before declaring that MT "hangs up".

I launched the migration this morning. The migration is still being run. It is blocked on this select and the fetch PK information.

OK, it looks like a bug then. What version of Oracle's JDBC driver do you use? Can you try to run the same SELECT from some Java-based tool (like DbVisualizer) using the same JDBC driver and check how fast it runs?

I keep asking as I do not have Oracle 9.2.0.1 with that big schemas around :(

Where I see the jdbc driver version ?

Find driver's .jar file (ojdbc.jar in my case) and either extract files from it to some directory or view archive content with program like WinZip or WinRAR. In the META-INF subdirectory you will find a text file, MANIFEST.MF. It will contain version information, like this:

Manifest-Version: 1.0
Specification-Title:    Oracle JDBC driver classes for use with JDK14
Sealed: true
Created-By: 1.4.2_08 (Sun Microsystems Inc.)
Implementation-Title:   ojdbc14.jar
Specification-Vendor:   Oracle Corporation
Specification-Version:  Oracle JDBC Driver version - "10.2.0.1.0"
Implementation-Version: Oracle JDBC Driver version - "10.2.0.1.0"
...

My jdbc driver version :

Manifest-Version: 1.0
Specification-Title: "Oracle JDBC driver classes for use with JDK1.4"
Specification-Version: "Oracle JDBC Driver version - 9.0.2.0.0"
Specification-Vendor: "Oracle Corporation" .
Implementation-Title: "ojdbc14.jar"
Implementation-Version: "Oracle JDBC Driver version - 9.0.2.0.0"
Implementation-Vendor: "Oracle Corporation"
Implementation-Time: "Thu Apr 25 23:14:02 2002"

When i use this driver with dbvisualizer :
it takes 20 seconds to return 1000 rows.

OK. Looks like there is definitely some problem with Migration Toolkit.

Actually, this is a duplicate of http://bugs.mysql.com/bug.php?id=34117. I will re-open that older bug instead.

Looks like I need dump of some sample schema that you can not migrate. See my last comment at Bug #34117.

I put on the mysql ftp site (ftp://ftp.mysql.com/pub/mysql/upload/) a compressed archive of oracle dump file. The file name is bug-data-40198.zip

The file contains a oracle schema who can't migrate to mysql database.

What's news ?
Did you can use my oracle dump file ?
Did you see where they are a problem ?
Thanks