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:

• ofbiz;
• ofbizolap;
• ofbiztenant;

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.