Tips for migrating a MySQL database to Derby/Java DB

A more and more commonly asked question on the Derby user list is "how do I best migrate my database from MySQL to Derby".

A very useful way to do this is through DDLUtils, a very nice utility that lets you export a database schema from one database and create it in another. Here is the MySQL page from DDLUtils.

But I also thought I'd share what looked like a very useful email from one person who did it. Note the interesting tips about non-standard JDBC that worked in MySQL but which Derby did not accept.

---

>If you don't mind - what were your top three pains when moving from
>MySQL to Derby?

Glad to, if it will be of any interest, although the problems were
probably a result of our* own inexperience and naivity.

1. We used DdlUtils to do the migration, did the conversion form
MySQL to xml ok, but then encountered errors on conversion to Derby.
These threw us initially because they related to primary keys not
being unique, but eventually we realized that this was because
MediumInt is only supported in MySQL and the conversion changed it to
a Small integer. I guess we should have inspected the xml schema more
carefully. I had consulted the page:

http://db.apache.org/ddlutils/databases/mysql.html

And my one suggestion here is that the conversion table on that page
should include the recasting of mediumint - presumably an oversight.

2. The other problems arose at runtime after apparent successful
conversion, and also arose from the non-standard nature of MySQL.
There were three aspects of the java code of my app that Derby took
exception to. One was a JDBC ResultSet method (first()) I had used in
a hack to check for empty tables. It turned out Derby didn't like
this and made me use some JDBC2 method for scrolling the table. It
also wouldn't allow a finally{} clause for closing a connection that
it already handled itself (easy enough to remove) and wouldn't allow
a faulty SQL query that MySQL had ignored (typo of GROUP instead of
ORDER - for the correction of which I thank it).

So the moral is perhaps that Derby is to MySQL as Java is to Perl -
it won't tolerate the sloppy habits you had become accustomed to. :-)
(Apologies to the Perl community)