I wrote:
Despite a few problems in porting the stuff from MySQL, I must say
that overall I'm very pleased with the result. My thanks to the
Derby team.
And Thomas replied:
Thanks for letting the community know :)
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)
3. Finally, my enthusiasm to produce a neatly packaged product made
me (and my app) waste time with the zipped database. Perhaps there
could be a health warning about this somewhere.
David
* 'Our' because a summer student was doing this as part of a Masters
project, but I needed to help him debug it.
--
_______________________________________________________________
David P.Leader, Biochemistry and Molecular Biology
Davidson Building, University of Glasgow, Glasgow G12 8QQ, UK
Phone: +44 41 330-5905 http://doolittle.ibls.gla.ac.uk/leader
_______________________________________________________________
