| Review: How good is MySQL Migration Toolkit
Feature: It moves objects+data from a broad range of JDBC compliant databases
Almost every database vendor has a one way ETL-Extract Transform and Load ticket to their own home database. The Target of the transfer is always the same - the one and true database (MySQL in this case). The source is any JDBC database in the case of MySQL Migration Toolkit - remote or co-resident on the same server. Now as a developer I have done my share of table and database transfers. Its a dogs breakfast.
The problems are threefold. First if the databases are not of the same type - and one has a lot of network, hierarchic, b-tree, object and other specialized databases to choose from - then the mapping of data structures and tables can be problematic at best; a 1-3 week data design mapping problem more often than not. Second, even if they are both relational databases, the departures among the vendors from ANSI SQL standards are such that conversions of data, views, triggers, constraints and then stored procedures are usually progressively difficult. Finally, even if you get the data schema and objects right, the mapping and migration of the actual data - the transform step - can be fraught with danger, especially across a network and hardware divide.
So MySQL has delivered its own Wizard for getting you from there - mistaken use of a competitors database, to here and the promised land - MySQL 5.x. As you can see from the first two screen shots above, the Migration Toolkit takes users through an disciplined 8 step process where all of the coding and scripting is done behind the scenes. Only at two steps, the Schema/Database transfer and then the data object structure migration (definitions of the underlying tables, views, triggers, constraints, stored procedures, and other database objects) does the end user see the transfer scripts and is given an opportunity to amend the scripts as required to fix any errors. Otherwise if everything goes according to Hoyle the scripts stay under the covers.
Now as noted at the outset, the Migration Toolkit uses JDBC as its transfer agent. The Opening Dialog checks to see that you have the right Java and JDBC software at your disposal. Since I run a number of Java tools I have my CLASSPATH, PATH, and JAVAHOME OS environment variables all properly set. If you don't - also don't expect help from the Toolkit to resolve any Java setup problems other than some short cursory error and help notes .
The next part of the setup process links the Source database, Oracle in this case, with a small 7 table tutorial database, by means of JDBC. Again if you don't know your JDBC connection code and database connection parameters, do not expect any help from the Migration Toolkit. MySQL needs to improve on this important connection help.
Finish with a summary page - this is what all the stages will do.
Here is the summary dialog Window for the database setup step. Note that the Migration Toolkit has already gone out and done the reverse engineering to know what's in the Oracle database and what the target MySQL database looks like and can do.
But once we have applied the Source database and Target database connections we are ready to roll. And so that means choosing what schema and tables to move etc. The first step:
I could choose to move several databases at once but for this tutorial the familiar Oracle HR database schema is the choice.
The summary page for the Reverse Engineering dialog.
Again the Migration Toolkit provides a summary page; but a lot is taking place in the background. The Migration Toolkit is actually reading the Oracle database and examining the HR table schema. In a previous use with an Access table this step reported an error and I had to load a new copy of the Access database to get the transfer to work.
Here is a nice feature of the Migration Toolkit, users get to decide what tables and objects
are to be migrated. As you can see I have dropped the Explain table and also some of the stored procedures. For big transfers it pays to do this in steps. Tables in the first 2-3 runs, then stored procedures, etc. However, note the caution below. By the way, the Oracle Auto Sequences are not transferred over.
The next step allows users to set the parameters for how objects will be mapped from Oracle
to MySQL. Users get to set the parameters but I was disappointed a bit on the parameters that could be set for table using CLOBs and stored procedure mappings. Neither was as broad as hoped for and this leads to problems in the stored procedure and view transfers.
also since we only migrated 3 database types - Oracle, Access, and Sybase, we have no idea how much support the Migration Toolkit provides for the various databases. For example, I know that Access routines and reports are not eligible for transfer, Oracle AutoSequences and Tablespace assignments are not picked up. And we did not get to check triggers, constraints and other Oracle objects. So caution is advised - until you reach this step you will not know how much the Migration Toolkit will be able to transfer over - so be careful because clearly the Migration Toolkit is not as robust as commercial ETL-Extract Transform and Load utilities.
That said, the Migration Toolkit does provide a lot of assistance in getting tables and data over - in most systems the bulk of the transfer. So the summary step below is useful.
The error logs available through the Advanced button were invaluable in figuring out what was going wrong with a Sybase transfer. Its helpful steps like this that can save a lot of time over your own manual transfer.
Just from a sheer learning standpoint, this dialog and the access it provides to how
conversions should be done is invaluable. This also will clue you in when tricky table datatype conversions may be going wrong so you can intervene. Likewise the whole area of stored procedure conversions is fraught with difficulty. Users will spend a lot of time here cleaning up the stored procedure code.
In the screen shot above, MySQL does not support the %type operation available in Oracle PL/SQL. So users have to manually replace job_history.employee_id%type with its counterpart, INTEGER(6), in MySQL. Again, this is very helpful.
The next step allows users to pick how the conversion is going to be done, live or through
scripts run later. I prefer to run right away 1)because I have already reduced the scope of the transfer to manageable chunks; and 2)I want to take advantage of the Back key and make corrections right away at the Manual Editing Step. Let each user choose their own poison.
Likewise users have a choice of what data transfer to use now and "live" or later with scripts.
I must admit a bias for live; but also consider that none of my transfers of data have exceeded 20MB or 20,000 thousand records - relatively small for most purposes. Some users, knowing they will have difficulties in the data may prefer the script file approach - especially with MySQL's Query Browser's debug capability. Like spice, choose to taste.
Here is data transfer summary report - success!
As noted I have yet to push the conversion state of the art on bulk data loads; so success here is to be expected.
And it is really quite convenient that we can check the data transfer values visually.
When there are data transfer problems, much of the time the problems are obvious. One or more columns are skewed, missing data, or pickup data from a neighboring column. So this page is a good quick visual check opportunity.
The final summary page:
Note the errors in the view and stored procedures. Now users can take advantage of the Back button and move 3 steps back to the Manual Edit page and make corrections there. Or just take the report, and do the changes from the Command lIne or in MySQL Query Browser. This reviewer like the tools and immediate problem report available in the Manual Edit page, so I did the corrections there. Both errors involved verbatim translations of Oracle properties that ultimately got rejected by the MySQL parser. So they were easy to correct.
So we are done. The conversion process is thorough and provides for a lot of user customization and intervention. The ability to Generate Migration Script in the last step (see screenshot)is an added bonus I have had occasion to take advantage of when repeated transfer are required.
Given that the Migration Toolkit is very good but does not match the technical virtuosity of commercial ETL tools, many users reactions to the toolkit will be swayed by their expectations. If users are looking for a versatile, comprehensive and nearly faultless migration tool, the Migration Toolkit is pretty good but ultimately it will disappoint - it does not deliver faultless performance and conversions all of the time. On the other hand, if you are looking for a process that will give you a major helping hand in getting a fairly broad set of databases converted much of the time without extra manual effort, then the Migration Toolkit will fill your bill.
There are very good and fairly expensive ETL tools that do provide nearly faultless transfers including very robust data checks for data that might have become obsolete, redundant or just erroneously entered in the first place. If there is interest, we shall cover such tools in a separate review.
Finally, some developers may only want to transfer some fields from one or two tables in which case an SELECT ... INTO OUTFILE will probably be faster and easier. So the Migration Toolkit is for those intermediate uses transfer is either broad (lots of tables and fields and other objects) or deep (lots of data) in which case the Migration Toolkit should meet 70-100% of your needs - and save time and effort over manual conversion. Taken in that light, the Migration Toolkit is a very useful utility in your MySQL arsenal.
Top of Page Home Tutorials
Developers Weblog Graphics Reviews and Tips