| Bug #32660 | MySQL Migration Toolkit hangs when Fetch PK from Oracle | ||
|---|---|---|---|
| Submitted: | 23 Nov 2007 14:42 | Modified: | 21 Oct 2008 15:48 |
| Reporter: | Mats Johansson | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Migration Toolkit | Severity: | S2 (Serious) |
| Version: | 1.1.12 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[25 Nov 2007 15:21]
Valeriy Kravchuk
Thank you for a problem report. Please, specify the exact version of JDBC driver used. Send also the results of "hanging" statement for the schema you are trying to migrate, like this: SQL> SELECT c.table_name, i.column_name 2 FROM all_constraints c, all_tables t, all_ind_columns i 3 WHERE c.owner = 'SCOTT' 4 AND t.owner = c.owner 5 AND c.table_name = t.table_name 6 AND c.constraint_type = 'P' 7 AND c.index_name = i.index_name 8 AND i.table_owner = c.owner 9 AND i.table_name = c.table_name 10 ORDER BY c.table_name; TABLE_NAME ------------------------------ COLUMN_NAME -------------------------------------------------------------------------------- TR REF_NO SQL> show release release 1002000100
[5 Dec 2007 16:41]
Tino Curti
I confirm the issue in the 1.1.12 MySQL MT with Oracle 9.2. Finally, I verified this select works fine: SELECT i.table_name, i.column_name FROM (SELECT table_owner, table_name, column_name FROM all_ind_columns WHERE table_owner = 'XXXX') i, all_constraints c, all_tables t WHERE c.owner = i.table_owner AND c.constraint_type = 'P' AND c.table_name = i.table_name AND t.table_name = c.table_name AND t.owner = c.owner ORDER BY 1,2
[26 Dec 2007 0:00]
Bugs System
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".
[3 Jan 2008 9:10]
Tino Curti
Simply, using the migration toolkit tool 1.1.12 to migrate from Oracle 9.2 to MySQL 5.1, during the reverse engineering the process hangs during fetch of Primary Keys. Please report the status to 'open' (I don't know how to do this) and feedback me if you need other info. I suggest you the problem is probably a wrong select (see my previous comment). Bye and goodyear !!
[18 Jan 2008 9:59]
Tino Curti
I'm sorry, but I cannot change the bug status.... Someone can help me ???
[21 Oct 2008 15:48]
Valeriy Kravchuk
Duplicate of Bug #34117. Still happens with 1.1.13.
Description: Reverse engineering process for Oracle hangs during fetch of Primary Keys. Oracle versions are 9.2.0.4 and 9.2.0.6 (same behaviour). Msg log from Migration Toolkit: Initializing JDBC driver ... Driver class Oracle Thin JDBC Driver using SID Opening connection ... Connection jdbc:oracle:thin:system/6laxari1ask@localhost:9521:admin Initializing JDBC driver ... Driver class Oracle Thin JDBC Driver using SID Opening connection ... Connection jdbc:oracle:thin:system/6laxari1ask@localhost:9521:admin Getting version information ... Initializing JDBC driver ... Driver class Oracle Thin JDBC Driver using SID Build simple Oracle datatypes. Call Oracle stored procedure ANALYZE_SCHEMA for ADMIN. CALL DBMS_UTILITY.ANALYZE_SCHEMA(?, 'ESTIMATE', 50, 0, 'FOR TABLE') Fetch the number of tables in the schema ADMIN. SELECT COUNT(*) AS TABLECOUNT FROM ALL_TABLES t, ALL_OBJECTS a WHERE t.OWNER=? AND a.OWNER=t.OWNER AND a.OBJECT_NAME=t.TABLE_NAME AND a.OBJECT_TYPE='TABLE' AND a.STATUS='VALID' Fetching 869 table(s) of the schema ADMIN. SELECT t.* FROM ALL_TABLES t, ALL_OBJECTS a WHERE t.OWNER=? AND a.OWNER=t.OWNER AND a.OBJECT_NAME=t.TABLE_NAME AND a.OBJECT_TYPE='TABLE' AND a.STATUS='VALID' ORDER BY t.OWNER, t.TABLE_NAME Fetch column information. 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 WARNING: The datatype TIMESTAMP(6) was not been defined yet. WARNING: The datatype ROWID was not been defined yet. WARNING: The datatype TIMESTAMP(6) was not been defined yet. Fetch PK information. --- hangs here ----- The SQL statement which hangs Oracle is: SELECT c.table_name, i.column_name FROM all_constraints c, all_tables t, all_ind_columns i WHERE c.owner = :1 AND t.owner = c.owner AND c.table_name = t.table_name AND c.constraint_type = 'P' AND c.index_name = i.index_name AND i.table_owner = c.owner AND i.table_name = c.table_name ORDER BY c.table_name; How to repeat: Migrate an Oracle 9.2.0.6 live database with Migration toolkit. Suggested fix: Make it possible to disable Fetch of Primary Keys or use a working SQL statement.