MySQL Bugs: #12944: Migration from Oracle fails on sequences

Bug #12944	Migration from Oracle fails on sequences
Submitted:	2 Sep 2005 7:29	Modified:	30 May 2013 11:17
Reporter:	Sander Robijns	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S3 (Non-critical)
Version:	1.0.19	OS:	Windows (Windows XP SP2 Prof)
Assigned to:		CPU Architecture:	Any

Description:
Migration fails when processing the sequences. Error message:

Fetch the number sequences of the schema IFCPROD.
SELECT COUNT(*) AS NUM FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER=?
Fetch 100 sequence(s) of the schema IFCPROD.
SELECT SEQUENCE_NAME, MIN_VALUE,  MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG,  CACHE_SIZE, LAST_NUMBER FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER=?
The schema could not be reverse engineered (error: 10008).
The function did not return an object.

See complete log (attached) for further details.

When I execute the query for selecting the sequence manually (replacing the ? for the owner with the correct owner), the result is correct. See also attachment.

How to repeat:
Difficult to say, maybe this is specific to the schema in use. A schema without sequences has no problems. Unfortunately, I have no DBA rights on the Oracle schema in use and am not able to create sequences in my own schema to test it (with a limited number of tables, sequences and such).

Suggested fix:
None, I have no idea what is wrong here. Could it be the max_value of the sequence giving problems?

Message log (zip, text > 200 kb)

Attachment: messagelog.zip (application/x-zip-compressed, text), 6.15 KiB.

10008 is a Migration Toolkit error message. So, it looks like a bug|problem in it.

Could you try the following steps to find out what causes the problem.

To solve the issue I would need XML files generated by the new debugging mode.

After installation of the new release the tool needs to be started as usual. On the schema selection page before pressing [Next >] the debug mode has to be turned on.

Press F4 to get the GRT shell. Execute

BaseJava:java2GrtXmlLogging(1)

This will turn the logging on. Now press [Next >] and let the tool do the reverse engineering. After the step is complete, look in

C:\Documents and Settings\[USERNAME]\Application Data\MySQL\log

This directory should contain several XML files. These need to be zipped and sent to mzinner@mysql.com

Thanks,
Mike

Hi Mike,

I did as requested, but only one (or two) XML log file was created containing nothing useful at all.
When I ran against another schema without sequences, migration worked and I got useful XML logs, but in the original schema, nothing.

Still the same error when processing the sequences...

The only thing I can do is send you a number of HTML pages (bundled in a ZIP file), containing most information of the schema that I want to migrate. Not all tables are included, but all sequences are.

I will send you that to the email address you gave me. Please treat that data model as confidential.

With kind regards,
Sander

Sander,

Have yoy sent that ZIP file with HTML pages to Mike? You may also uload it as a private file here, using the File tab. Please, clarify the current state of this bug report.

Hi Valeriy,

The bug has still the same status as when I first opened it. I can not post the zip file here, as it is > 2 Mb.

Regards,
Sander

Sander,

Please, upload the ZIP to our ftp server (ftp.mysql.com:/pub/mysql/upload) or split into smaller parts (200K) and upload here. Michael needs these data to be able to analyze the problem.

According to email from Michael, it looks like you have no permission to execute:

SELECT COUNT(*) AS NUM FROM ALL_PROCEDURES;

So, please, check it in Oracle, trying to connect as a user used for migration, in SQL*Plus. Inform about the results, please.

Hi Val,

Our server crashed this weekend and is still not up and running. Hopefully it will be restored somewhere this week. As soon as I can test it, I will let you know the results.

Regards,
Sander

Hi Val,

I just realized that the view ALL_PROCEDURES does not exist on Oracle 8i, so it is normal that I can not select from it. 
The toolkit should check for this and not try to select from it, or at least not fail on it.

Regards,
Sander

Yes, ALL_PROCEDURES was added in Oracle 9, so MT have to use ALL_OBJECTS, ALL_SOURCE, ALL_ARGUMENTS etc (other dictionary views) for Oracle 8.1.7.

And I agree with you:
 
The toolkit should check for this and not try to select from it, or at least not
fail on it.

Or we have just mention in the prominent place that MT do support Oracle 9 or newer versions only.

Bug #18082 was marked as a duplicate of this one.

Please, try to repeat with a newer version of Migration Toolkit, 1.0.25. It can not migrate sequences, but this does not prevent migration of other schema objects any more. Works for me with Oracle XE (10.2.0) and MT 1.0.25.

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

I can reproduce this problem. If there are many database objects (2000 or more), the migration tool kit cannot deal with all of them at one.

At that time, the same error is displayed.

[Workaround]
Reduce the amount of database objects for migration. 
(e.g. after backup, drop table to reduce the database object.
 If you have 6000 database objects, migrate them in unit 2000 database objects.)

A new migration implementation has been created.