The Hortonworks Community Connection is now live. A completely rebuilt Q&A forum, Knowledge Base, Code Hub and more, backed by the experts in the industry.

You will be redirected here in 10 seconds. If your are not redirected, click here to visit the new site.

The legacy Hortonworks Forum is now closed. You can view a read-only version of the former site by clicking here. The site will be taken offline on January 31,2016

Oozie Forum

Oozie to use MySQL instead of Derby

  • #12273


    When I am doing the config change in oozie-site.xml to replace the Derby database with MySQL, the configurations are getting overridden with the original one. So, how can I use MySQL in place of Derby under Oozie.

    Also, we need to do the config change for providing the error code entry in the oozie-site.xml, e.g. the following property: oozie.service.LiteWorkflowStoreService.user.retry.error.code.ext

    Are these possible or not?


  • Author
  • #12328
    Seth Lyubich

    Hi Vimlesh,

    Can you please let me know which installation method you used to deploy your cluster?


    York Yao

    Hi Vimlesh,

    Have you resolved this problem? I encountered the same problem. Can you share your solution?

    Hi Sef, I deployed the cluster using ambari.





    Ambari will overwrite the changes made to the xml files directly.
    Any changes must be made in Ambari itself or you must use /var/lib/ambari-server/resources/scripts/
    This enables a simpler way to use the Ambari API to change configurations.
    If you run
    “ get CLUSTERNAME oozie-site”
    This will give you all the configurations which you can change, then you can find what you want to modify to MySQL and update them accordingly.



    Neal Rutta

    I’m currently running HDP 2.1 (upgraded recently from HDP 2.0). I currently have Derby configured as the Oozie database and would like to change this to MySQL. Unfortunately I cannot make this change using Ambari, as it won’t let me change the database (there are 4 radio buttons, one for each different database, but they are disabled/read-only) via the web user-interface.

    The discussion about using /var/lib/ambari-server/resources/scripts/ to change properties makes sense, and I have tested it out. However I’m not sure which values need to be changed, and what those values should be. For example, if I do a “get” on the “global” config type I can see

    “oozie_database”: “New Derby Database”
    “oozie_database_type”: “derby”
    “oozie_derby_database” : “Derby”

    But what should these values be if I want to use MySQL? Note that there is also “oozie_jdbc_driver” too and I assume that will need to be set to the correct mysql jdbc connector.

    Related to this, I’m also a bit confused as to whether I should configure oozie by updating its configuration files directly, or if I should use Ambari. For example, this link provides instructions for configuring a database for oozie. I suspect however that I really need to make these changes through ambari, but would like confirmation on this.

    Rizki Nanda


    if you want migrate from derby to mysql you can run this command below

    #install mysql driver
    yum install mysql-connector-java*

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

    #create database
    mysql -u root -p
    create database oozie;
    grant all on oozie.* to ‘oozie’@’oozie_host’ identified by ‘password’;
    flush privileges;

    su -l oozie
    /usr/hdp/ create -sqlfile oozie.sql -run
    /usr/hdp/  upgrade -sqlfile oozie.sql -run

    #get oozie conf
    /var/lib/ambari-server/resources/scripts/ get ambari_host cluster_name oozie-env
    /var/lib/ambari-server/resources/scripts/ get ambari_host cluster_name oozie-site

    #set oozie to mysql
    /var/lib/ambari-server/resources/scripts/ set ambari_host cluster_name oozie-site ‘oozie.service.JPAService.jdbc.url’ ‘jdbc:mysql://mysql_host:3306/oozie’
    /var/lib/ambari-server/resources/scripts/ set ambari_host cluster_name oozie-site ‘oozie.service.JPAService.jdbc.driver’ ‘com.mysql.jdbc.Driver’
    /var/lib/ambari-server/resources/scripts/ set ambari_host cluster_name oozie-env oozie_existing_mysql_host localhost
    /var/lib/ambari-server/resources/scripts/ set ambari_host cluster_name oozie-env oozie_hostname
    /var/lib/ambari-server/resources/scripts/ set ambari_host cluster_name oozie-env ‘oozie_database’ ‘Existing MySQL Database’

    Hopefully help you



The forum ‘Oozie’ is closed to new topics and replies.

Support from the Experts

A HDP Support Subscription connects you experts with deep experience running Apache Hadoop in production, at-scale on the most demanding workloads.

Enterprise Support »

Become HDP Certified

Real world training designed by the core architects of Hadoop. Scenario-based training courses are available in-classroom or online from anywhere in the world

Training »

Hortonworks Data Platform
The Hortonworks Data Platform is a 100% open source distribution of Apache Hadoop that is truly enterprise grade having been built, tested and hardened with enterprise rigor.
Get started with Sandbox
Hortonworks Sandbox is a self-contained virtual machine with Apache Hadoop pre-configured alongside a set of hands-on, step-by-step Hadoop tutorials.
Modern Data Architecture
Tackle the challenges of big data. Hadoop integrates with existing EDW, RDBMS and MPP systems to deliver lower cost, higher capacity infrastructure.