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.

WP 3.0

Whilst I’m sitting in a train which is driving me to the airport I’d like t mention that recently I’ve upgraded to WP 3.0. It was a straightforward on-click procedure. Can’t speak of any single great advantages which this update has brought to me but presumably the biggest one is that nothing has broken so far ;-)

Enjoy the life and take care

Living in a rush

I’m still here – on air. But simply don’t have much time to post anything more substantial than saying that I’m leaving to Saint-Petersburg for a couple of days. I’ve already finished my short note about Ofbiz but still need to re-check everything before posting it online. Hope I could do that either from the airport or from the comfort of my hotel’s room.

Thumbs up!

Freelancer. Count me in.

I’ve been hatching this idea for a long time but only on the last week I took the final decision and registered on Freelancer.com. It’s hard to tell what was the catalyst to become a part-time freelancer but I think there were several that drove me:

  • Curiosity
  • Social aspect.

Yes, you read it correctly and there is no mistake in avoiding the financial theme because it’s not a key intent. Let me elaborate a bit. I was and actually I’m still interested in what are the most common issues and problem that people experience in regard to OSS and if there is something new I could learn from helping people to resolve these issues. And if I’m paid for doing that – it’s an obvious double win. The social component may not sound persuasive and it’s not obvious but it’s actually an important part since being a freelancer implies that one will have to communicate, talk to the customers and explain the inner technical details they might be not quite familiar with. And that’s another big advantage for me since it gives a chance to practice English more.
And there is of course a win for my readers because I will be doing more Linux related posts explaining different tasks I have completed as a freelancer.
See you soon.