How to migrate Ofbiz from Derby to MySQL DB.

It’s been a while but finally I’m here. I know it’s a lame excuse but the last few weeks were indeed very hectic not only because I’ve joined a freelance community but mostly because I was snowed under on my permanent position. Anyway, today I’d like to guide you through the steps required to migrated Ofbiz from Derby to MySQL DB. These steps are really easy and straightforward but you need to know them to perform the migration without a single hiccup.

First of all you’ll need jdbc connector for MySQL copied to {ofbiz}/framework/entity/lib/jdbc/ and also you will have to prepare MySQL in advance, i.e. create all required databases and grant necessary privileges to respective database users. In my example I use three database:

This is how easy one could setup MySQL’s part:


# mysqladmin -uroot -p create ofbiz
# mysqladmin -uroot -p create ofbizolap
# mysqladmin -uroot -p create ofbiztenant

# mysql -uroot -p
Enter password: 

mysql> grant all privileges on ofbiz.* to 'ofbiz'@'localhost' identified by 'ofbiz_password_here';
mysql> grant all privileges on ofbizolap.* to 'ofbizolap'@'localhost' identified by 'ofbizolap_password_here';
mysql> grant all privileges on ofbiztenant.* to 'ofbiztenant'@'localhost' identified by  'ofbiztenant_password_here';

Next, go to webtools urls and dump all data to the xml which you could use later to restore the data. Once the dump is over stop ofbiz and open (don’t forget to back up it first) {ofbiz}/framework/entity/config/entityengine.xml and edit it similarly to what is shown below:


<delegator name="default" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main" entity-eca-enabled="false" distributed-cache-clear-enabled="false">
<group-map group-name="org.ofbiz" datasource-name="localmysql"/>
        <group-map group-name="org.ofbiz.olap" datasource-name="localmysqlolap"/>
        <group-map group-name="org.ofbiz.tenant" datasource-name="localmysqltenant"/>
    </delegator>
    <delegator name="default-no-eca" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main" entity-eca-enabled="false" distributed-cache-clear-enabled="false">
        <group-map group-name="org.ofbiz" datasource-name="localmysql"/>
        <group-map group-name="org.ofbiz.olap" datasource-name="localmysqlolap"/>
        <group-map group-name="org.ofbiz.tenant" datasource-name="localmysqltenant"/>
    </delegator>

    <delegator name="test" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main">
        <group-map group-name="org.ofbiz" datasource-name="localmysql"/>
        <group-map group-name="org.ofbiz.olap" datasource-name="localmysqlolap"/>
        <group-map group-name="org.ofbiz.tenant" datasource-name="localmysqltenant"/>
    </delegator>

<datasource name="localmysql"
            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
            field-type-name="mysql"
            check-on-start="true"
            add-missing-on-start="true"
            check-pks-on-start="false"
            use-foreign-keys="true"
            join-style="ansi-no-parenthesis"
            alias-view-columns="false"
            drop-fk-use-foreign-key-keyword="true"
            table-type="InnoDB"
            character-set="latin1"
            collate="latin1_general_cs">
        <read-data reader-name="seed"/>
        <read-data reader-name="seed-initial"/>
        <read-data reader-name="demo"/>
        <read-data reader-name="ext"/>
        <inline-jdbc
                jdbc-driver="com.mysql.jdbc.Driver"
                jdbc-uri="jdbc:mysql://127.0.0.1/ofbiz?autoReconnect=true"
                jdbc-username="ofbiz"
                jdbc-password="ofbiz_password"
                isolation-level="ReadCommitted"
                pool-minsize="2"
                pool-maxsize="250"
                time-between-eviction-runs-millis="600000"/>
    </datasource>

<datasource name="localmysqlolap"
            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
            field-type-name="mysql"
            check-on-start="true"
            add-missing-on-start="true"
            check-pks-on-start="false"
            use-foreign-keys="true"
            join-style="ansi-no-parenthesis"
            alias-view-columns="false"
            drop-fk-use-foreign-key-keyword="true"
            table-type="InnoDB"
            character-set="latin1"
            collate="latin1_general_cs">
        <read-data reader-name="seed"/>
        <read-data reader-name="seed-initial"/>
        <read-data reader-name="demo"/>
        <read-data reader-name="ext"/>
        <inline-jdbc
                jdbc-driver="com.mysql.jdbc.Driver"
                jdbc-uri="jdbc:mysql://127.0.0.1/ofbizolap?autoReconnect=true"
                jdbc-username="ofbizolap"
                jdbc-password="ofbizolap_password"
                isolation-level="ReadCommitted"
                pool-minsize="2"
                pool-maxsize="250"/>
</datasource>

<datasource name="localmysqltenant"
            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
            field-type-name="mysql"
            check-on-start="true"
            add-missing-on-start="true"
            check-pks-on-start="false"
            use-foreign-keys="true"
            join-style="ansi-no-parenthesis"
            alias-view-columns="false"
            drop-fk-use-foreign-key-keyword="true"
            table-type="InnoDB"
            character-set="latin1"
            collate="latin1_general_cs">
        <read-data reader-name="seed"/>
        <read-data reader-name="seed-initial"/>
        <read-data reader-name="demo"/>
        <read-data reader-name="ext"/>
        <inline-jdbc
                jdbc-driver="com.mysql.jdbc.Driver"
                jdbc-uri="jdbc:mysql://127.0.0.1/ofbiztenant?autoReconnect=true"
                jdbc-username="ofbiztenant"
                jdbc-password="ofbiztenant_password"
                isolation-level="ReadCommitted"
                pool-minsize="2"
                pool-maxsize="250"/>
  </datasource>

To load initial data into MySQL – otherwise, you’ll not be able to login into webtools to perform data import, and run this command from the ofbiz directory:

 # java -jar ofbiz.jar -install 

The last step is to login to webtools url again with the default login/password and import the data back using the xml you’ve dumped before.

That’s how easy it is.

Posted on June 29, 2010 at 7:37 pm by sergeyt · Permalink
In: Linux

19 Responses

Subscribe to comments via RSS

  1. Written by Vivek
    on August 11, 2010 at 5:48 pm
    Reply · Permalink

    It was very helpful tips from you dear friend.
    I am a newbie in Ofbiz & your guidance was highly appreciated
    Thanks Again.

  2. Written by sergeyt
    on August 23, 2010 at 11:29 am
    Reply · Permalink

    Hello Vivek,

    I’m truly and endlessly happy to know that this post made your life a little bit easier ;-)

    Thank you.

  3. Written by Sudha
    on August 26, 2010 at 12:10 pm
    Reply · Permalink

    I keep getting “Password hash should be a 41-digit hexadecimal number” when I do the line
    grant all privileges on ofbiz.* to ‘ofbiz’@’localhost’ identified by password ‘ofbiz_password_here’;

    Can you help please?

  4. Written by sergeyt
    on August 26, 2010 at 2:01 pm
    Reply · Permalink

    Hello, Sudha,

    Thank you very much for pointing the problem out.
    My bad, was obviously thinking about something different when I was typing that and surely the actual MySQL statement should look like this (without ‘password’ word after ‘identified by’) :

    grant all privileges on ofbiz.* to ‘ofbiz’@’localhost’ identified by ‘ofbiz_password_here’;

    I’ve updated the post to fix this typo.

    Thank you.

  5. Written by davenycity
    on September 15, 2010 at 12:12 pm
    Reply · Permalink

    great blog thank you

  6. Written by Srijith Surendran
    on December 13, 2011 at 12:37 pm
    Reply · Permalink

    Thank you for your post.. Its very helpful to connect with MySQL DB

    • Written by sergeyt
      on December 13, 2011 at 6:09 pm
      Reply · Permalink

      Hi Srijith,

      You’re truly welcome. Glad to hear it helped you.

      Regards,
      Sergey.

  7. Written by Smiht
    on January 16, 2012 at 10:57 am
    Reply · Permalink

    After install mysql and run I got this error.

    ” Error in request handler:
    Exception: org.ofbiz.widget.screen.ScreenRenderException
    Message: Error rendering screen [component://common/widget/CommonScreens.xml#GlobalDecorator]: java.lang.IllegalArgumentException: Template location is empty (Template location is empty)

    Please help

    • Written by sergeyt
      on January 16, 2012 at 9:01 pm
      Reply · Permalink

      Howdy.

      That’s been a while since I’ve touched Ofbiz but anyway. If you could provide more details, i.e. Ofbiz, Java and MySQL versions, I could try to reproduce the error or at least to go through the steps listed in my post to get an idea of what might be missing and what is the root cause of your problem.

      Cheers.

  8. Written by Valentin
    on June 7, 2012 at 12:24 pm
    Reply · Permalink

    Hi,

    is it necessary to have three database users (ofbiz, ofbizolap, ofbiztenant) or it will work with one user too?

    Cheers

    • Written by sergeyt
      on June 7, 2012 at 7:53 pm
      Reply · Permalink

      Hi,

      Thanks for stopping by.
      It would certainly work with a single DB user. It’s just a good practice and a precautionary measure to have a separate user for every database.

      Cheers.

  9. Written by shravan
    on September 7, 2012 at 9:03 am
    Reply · Permalink

    i follow same procedure but i got exception

    org.ofbiz.widget.screen.ScreenRenderException: Error rendering screen [component://common/widget/CommonScreens.xml#GlobalDecorator]: java.lang.IllegalArgumentException: Template location is empty (Template location is empty)

    • Written by sergeyt
      on September 14, 2012 at 9:34 am
      Reply · Permalink

      Hi Shravan,
      Thank you for stopping by. Unfortunately, I don’t have the answer but once I’m back I will try to use the latest version of Ofbiz to make sure that my instructions are still correct.

      Cheers.

      • Written by sergeyt
        on October 12, 2012 at 12:58 am
        Reply · Permalink

        Hi all,
        Sorry for the overdue reply. As I promised I tried the instructions to migrate from Derby to MySQL and I’m happy to confirm that they are still valid. I took the latest version of Ofbiz, 10.04.02, together with the latest GA version of mysql jdbc connector – 5.1.22 and everything went smoothly. I noticed that in the latest version of Ofbiz

  10. Written by Raul Munoz
    on December 25, 2014 at 2:21 am
    Reply · Permalink

    Hi there,

    I have been trying to install Apache Ofbiz on my 32-bit processor computer, with Windows 7 as the operating system. I have downloaded Java\jdk1.8.0_25: and apache-ant-1.9.4: and :apache-ofbiz-13.07.01. I made two environmental variables, JAVA_HOME and ANT_HOME; in addition, I included both Java and Ant Apache Bin addresses respectively into the environmental the PATH variable. Then I ran the CMD and entered the Ofbiz directory’ cd [apache ofbiz address], thenceforward, I resumed into entering the the ‘ant run-install’ command, followed by ‘ant run-install-seed’, I received a ‘build successful’ message for both of the commands I entered. After the foregone commands were executed I proceeded into clicking the startofbiz located inside the apache ofbiz folder. A cmd screen opened listing all sorts of commands. Once all these were carried out I typed the following address into my internet search bar:

    https://localhost:8443/webtools
    Notwithstanding, I constantly received the following error message that says THE TEMPLATE LOCATION IS EMPTY even after a numberless amount of permutations, deleting java, re-installing Ant Apache, etc:

    :ERROR MESSAGE: org.ofbiz.widget.screen.ScreenRenderException: Error rendering screen [component://common/widget/CommonScreens.xml#GlobalDecorator]: java.lang.IllegalArgumentException: Template location is empty (Template location is empty)

    I have not been able to come to grasps with this issue. I don’t believe things should be this difficult to install. I stand ready for any guidance, thank you.

    • Written by sergeyt
      on December 26, 2014 at 11:41 pm
      Reply · Permalink

      Hi Raul,
      I wish I could help but unfortunately I don’t have a single Windows system in my possession where I could reproduce the issue you’ve described.
      I see that you ran “ant run-install-seed”, but could you try “ant load-demo” as suggested at Demo and Test Setup Guide and at StackOverflow?

      Cheers.

      Kind regards,
      Sergey

  11. Written by BY
    on December 6, 2015 at 2:19 pm
    Reply · Permalink

    I follow the same step, but when I run ./ant load-demo, i always have this error return

    There are more than 1 create index error message, I just post 1 of that.

    ………
    2015-12-05 18:36:51,487 |source(localmysql)-0 |DatabaseUtil |E| Could not create foreign key indices for entity [GlReconciliationEntry]: SQL Exception while executing the following:
    CREATE INDEX GL_RECENT_ACTTXE ON GL_RECONCILIATION_ENTRY (ACCTG_TRANS_ID, ACCTG_TRANS_ENTRY_SEQ_ID)
    Error was: java.sql.SQLException: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use ‘ulimit’ to allow mysqld to use more memory or you can add more swap space
    2015-12-05 18:36:52,681 |source(localmysql)-0 |DatabaseUtil |E| Could not create foreign key indices for entity [GoodIdentification]: SQL Exception while executing the following:
    CREATE INDEX GOOD_ID_TYPE ON GOOD_IDENTIFICATION (GOOD_IDENTIFICATION_TYPE_ID)
    Error was: java.sql.SQLException: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use ‘ulimit’ to allow mysqld to use more memory or you can add more swap space
    ………

    However, at the end of the process is pending non-stop while it able to create few foreign key indices as below.

    ………
    2015-12-05 18:56:57,000 |source(localmysql)-0 |DatabaseUtil |I| Created 2 foreign key indices for entity [OrderHeaderNote]
    2015-12-05 18:57:13,980 |source(localmysql)-0 |DatabaseUtil |I| Created 2 foreign key indices for entity [OrderHeaderWorkEffort]
    2015-12-05 18:59:08,164 |source(localmysql)-0 |DatabaseUtil |I| Created 13 foreign key indices for entity [OrderItem]
    ……….

    This process is pending more than 8 hours.

    Can you please advise?

  12. Written by BY
    on December 6, 2015 at 2:30 pm
    Reply · Permalink

    Added information:
    Below is ofbiz and mysql version which I am using:
    1. apache-ofbiz-13.07.02
    2. mysql-community-server-5.7.9
    3. mysql-connector-java-5.1.6.jar

    • Written by sergeyt
      on December 7, 2015 at 11:23 am
      Reply · Permalink

      Hi,

      Frankly, speaking it’s been awhile since I fiddled with Ofbiz and can’t give a definitive answer to your question right away. But I found these exceptions quite worrisome – “Error was: java.sql.SQLException: Out of memory;”.
      Just out of curiosity, what information does “free” command return if you run it in a console while you’re doing “ant load-demo”?
      Additionally, since you use ant, what are the values of -Xms and -Xmx parameters in your build.xml file? Double check that are set reasonably.

      Cheers.

Subscribe to comments via RSS

Leave a Reply