MySQL Bugs: #68189: Migration from Oracle11g to MySQL 5.5 completely disabled/nonfunctional

Bug #68189	Migration from Oracle11g to MySQL 5.5 completely disabled/nonfunctional
Submitted:	26 Jan 2013 21:44	Modified:	31 Jan 2013 18:51
Reporter:	dave mausner	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S1 (Critical)
Version:	5.2.44	OS:	Windows (7)
Assigned to:		CPU Architecture:	Any
Tags:	migration, Oracle

Description:
Bug is still existing in Workbench Version 5.2.44 Rev 9933.

The migration feature is COMPLETELY DISABLED when transferring schema from Oracle11g to MySQL 5.5.  This is critical because the functionality no longer exists to move data from Oracle to MySQL in the Workbench.

A sample Oracle schema is supplied as a private attachment to bug 66609, which is the ancestor of this bug report.

How to repeat:
Create the oracle db using the CREATE TABLE DDL contained in the script attached to bug 66609.

Then use Workbench 5.2.44 Migration from Oracle source to MySQL target. Select the option to keep the schema name as-is (the default).

In Windows, an ODBC DSN to an Oracle11g instance was supplied to Workbench.  In my test case, both Oracle11g and MySQL were running on the same server.  But the same error occurs when the DSN points to a network instance.

When entering the actual migrate phase, it halts with a lengthy diagnostic which was reported in bugs 66609 and 67534.

Suggested fix:
There's an error somewhere in the python script involving out-of-range integer values. So the fix is to determine where the script is creating that value, and avoid doing that.

Starting...

Connect to source DBMS...

- Connecting...

Connecting to ...

Opening ODBC connection to DSN=Oracle XE...

Connected

Connect to source DBMS done

Reverse engineer selected schemata....

Connection to  apparently lost, reconnecting...

Connecting to ...

Opening ODBC connection to DSN=Oracle XE...

Connected

Reverse engineering FORD from XE

- Reverse engineering catalog information

- Preparing...

Traceback (most recent call last):

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.44\modules\db_sql92_re_grt.py", line 392, in reverseEngineer

    return Sql92ReverseEngineering.reverseEngineer(connection, catalog_name, schemata_list, context)

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.44\modules\db_generic_re_grt.py", line 232, in reverseEngineer

    trigger_count_per_schema[schema_name] = len(cls.getTriggerNames(connection, catalog_name, schema_name)) if get_triggers else 0

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.44\modules\db_sql92_re_grt.py", line 73, in getTriggerNames

    return sorted(list(set(row[0] for row in cls.execute_query(connection, query, catalog_name, schema_name))) )

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.44\modules\db_generic_re_grt.py", line 56, in execute_query

    return cls.get_connection(connection_object).cursor().execute(query, *args, **kwargs)

pyodbc.ProgrammingError: ('42S02', '[42S02] [Oracle][ODBC][Ora]ORA-00942: table or view does not exist\n (942) (SQLExecDirectW)')

Traceback (most recent call last):

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.44\workbench\wizard_progress_page_widget.py", line 191, in thread_work

    self.func()

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.44\modules\migration_schema_selection.py", line 160, in task_reveng

    self.main.plan.migrationSource.reverseEngineer()

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.44\modules\migration.py", line 332, in reverseEngineer

    self.state.sourceCatalog = self._rev_eng_module.reverseEngineer(self.connection, self.selectedCatalogName, self.selectedSchemataNames, self.state.applicationData)

SystemError: ProgrammingError("('42S02', '[42S02] [Oracle][ODBC][Ora]ORA-00942: table or view does not exist\n (942) (SQLExecDirectW)')"): error calling Python module function DbSql92RE.reverseEngineer

ERROR: Reverse engineer selected schemata: ProgrammingError("('42S02', '[42S02] [Oracle][ODBC][Ora]ORA-00942: table or view does not exist\n (942) (SQLExecDirectW)')"): error calling Python module function DbSql92RE.reverseEngineer

Failed

Additional information about duplicating the issue.
- Created an ODBC DSN for the Oracle source.
- Selected a SQL92 compliant RDBMS in the migration source panel.
- Specified the existing DSN.

Now I note that stock Oracle does not have INFORMATION_SCHEMA views, so I created them using the script attached (ZIP file).

INFORMATION_SCHEMA for Oracle

Attachment: information_schema.zip (application/x-zip-compressed, text), 11.22 KiB.

Thank you for the feedback. Duplicate of http://bugs.mysql.com/bug.php?id=66609 .

Dave: Is there something unique about this report such that it needs to be separate from http://bugs.mysql.com/bug.php?id=66609 ?

Agree this is same as 66609, but I could not update that one, so I put my additional notes and logs here.

Okay as long as you are able to access this evidence.