Migrate Oozie DB from Derby to Mysql

[#1]

Below are the steps for Oozie database migration from Derby to Mysql.

Step 1 - Have MySQL server installed and ready to be configured.

.

Step 2 - Stop Oozie service from Ambari UI.

.

Step 3 - On Ambari Server, run below command

ambari-server setup --jdbc-db=mysql --jdbc-driver=/usr/share/java/mysql-connector-java.jar 

Note - Please pass appropriate driver if /usr/share/java/mysql-connector-java.jar does not exists. .

.

Step 4 - Login to Mysql server as root user and create a blank 'oozie' database and grant required permissions to the 'oozie' user.

#create database oozie; 

#grant all privileges on oozie.* to 'oozie’@‘<oozie-server>' identified by 'oozie’; 

Note - 'oozie' is your oozie database password, if you want you can change it in above command .

.

Step 5 - Add mysql database server details in Oozie configuration via Ambari UI.

.

Step 6 - Make sure that you have mysql connector jar under Oozie libext (/usr/hdp/<version>/oozie/libext/mysql-connector-java.jar), if not then copy it from available machine.

.

Step 7 - Prepare Oozie war file

/usr/hdp/<version>/oozie/bin/oozie-setup.sh prepare-war 

Note - Run above command on oozie server as oozie user.

.

Step 8 - Prepare Oozie schema using below command (Run below command on Oozie host as oozie user)

/usr/hdp/<version>/oozie/bin/oozie-setup.sh db create -run 

Please note - Above steps does not have information to migrate historical data from Derby to Mysql. Basically Oozie stores workflows/coordinator configuration related information as MEDIUMBLOB datatype and there is no straightforward way to convert these information in mysql compatible format.

.

If you take a sql dump from Derby and import directly into mysql, Oozie server will start, you will see all historical data however whenever Oozie will try to change the coordinator action state/workflow action state, you might see parsing issues like below(because of incorrect BLOBs in Mysql):

2016-08-22 06:48:19,036 WARN CoordMaterializeTransitionXCommand:523 - SERVER[oozienode2.openstacklocal] USER[root] GROUP[-] TOKEN[] APP[test] JOB[0000003-160822025623541-oozie-oozi-C] ACTION[-] Configuration parse error. read from DB :4f424a00000001000000010005636f6465630002677a1f8b0800000000000000c5945d4bc3301486ef05ffc32ebc6d9276acce520afe8121526fbc8be9a9ab4b73e269639de27f37eb9c30d6099d8237219fcffbe6cd47aad094d5a323d95668b2f3b3c924b58416a85df72ddf36b286ac5e378a2adbdec87699f2beeb6bf84java.io.IOException: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 1; Content is not allowed in prolog. at org.apache.oozie.util.XConfiguration.parse(XConfiguration.java:289) at org.apache.oozie.util.XConfiguration.<init>(XConfiguration.java:80) at 

Happy Hadooping!! Please comment your feedback or questions in the comment section.