You can probably file this one under “weird article that will someday help a poor soul stuck in my position”. So I had written a nice little data processing system that worked on roughly a million records to do some fuzzy matching within a discrete problem space. I had used GORM objects with Hibernate 4 and MySQL 5.x. The domain objects were relatively standard with a handful of collections, but they had some nice logic for hashcode() and equals() that optimized for the data. The system hummed along on a single server and met the needs of the client.
Some of the data was PII information, so need to column encrypt them. Check out my other post on how I did that, because it has some interesting twists.
Key Differences in Database Behavior
GORM objects in MySQL tend to be declared with an id field of int(20) autoincrement. This means that MySQL takes care of the id field for each insertion and that the id space overlaps from 1 to n depending on how you may have configured GORM/Hibernate. For Oracle, Hibernate uses a single global sequence. This means that when you convert from MySQL to Oracle, you need to initialize this sequence to a number higher than that of your largest table rows (so that there is no collision on an insert). It also means that you will no longer have contiguous id fields for your Domain objects if your application depends on it.
One problem with Oracle sequences is that you cannot alter their starting value, so how do fix the nextval so that it does not collide with existing id values (thereby breaking the unique constraint on the id field). The trick can be found in this trick from Ask Tom – you change the increment to a value above your collision value and then change the increment back down to one afterward. This one item was the single biggest issue for me in doing this conversion.
Another item for Hibernate4 users is to make sure the hibernate.jdbc.use_get_generated_keys value is set to true in your application.yml or Config.groovy (depending on your version of Grails).
Our version of Oracle was 11g, so the correct dialect to choose for Hibernate4 was:
dialect: org.hibernate.dialect.Oracle10gDialect
Also the URL for Oracle’s thin jdbc driver looks like this:
url: "jdbc:oracle:thin:@10.10.200.1:1521:SIDname"
In my build.gradle file, I added the Oracle driver under dependencies:
runtime "com.oracle:ojdbc6:11.2.0"
Lastly, the boolean type in MySQL needs to be translated to number(1) in Oracle. This means that for Oracle true = 1 and false = 0. You may have to export or alter your data to accommodate this change. Most of the rest of my data came across without any problems, but I did double check my boolean fields since I had some issues on previous conversions.
When I first started my application using Oracle, I used the GORM datasource set to:
dbCreate: update
This made sure that the basic table structure was created in Oracle with the proper constraints. I added a wrinkle in that I used a custom naming strategy (prepending my tables with a prefix WNX_) so that the DBAs could tell my tables apart from others sharing the schema. I will detail how to do a custom naming scheme in another article.
Moving a large dataset is not a trivial task. I used two different methods to do it. The first was to use the mysqldump utility. I made sure to use the compatibility flags for oracle and also named the columns and generated one insert per line (Oracle has a very different multi-row insert syntax). This worked OK. I did have to order the tables so that constraints were in the right order. The second method I used was to have the Oracle DBAs setup an ODBC connection to the MySQL database. This was great because I could perform insert into table select from remote_table type of queries. I decided that the rest of the data
Conclusion
Based on the number of articles and help postings I had to scan to do this, I can conclude that very few people move in the direction of MySQL -> Oracle for relational databases in Grails applications. In fact, I think only a small percentage of Grails applications must be using Oracle. I hope that this article helps someone following in the same footsteps down the line.
