Community Articles
Find and share helpful community-sourced technical articles
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.
Labels (1)
Super Guru

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.

8842-screen-shot-2016-10-24-at-70447-pm.png

.

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.

1,425 Views
Don't have an account?
Coming from Hortonworks? Activate your account here
Version history
Revision #:
2 of 2
Last update:
‎08-17-2019 08:41 AM
Updated by:
 
Contributors
Top Kudoed Authors