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.
on August 11, 2010 at 5:48 pm
· Permalink
It was very helpful tips from you dear friend.
I am a newbie in Ofbiz & your guidance was highly appreciated
Thanks Again.
on August 23, 2010 at 11:29 am
· Permalink
Hello Vivek,
I’m truly and endlessly happy to know that this post made your life a little bit easier ;-)
Thank you.
on August 26, 2010 at 12:10 pm
· 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?
on August 26, 2010 at 2:01 pm
· 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.
on September 15, 2010 at 12:12 pm
· Permalink
great blog thank you
on December 13, 2011 at 12:37 pm
· Permalink
Thank you for your post.. Its very helpful to connect with MySQL DB
on December 13, 2011 at 6:09 pm
· Permalink
Hi Srijith,
You’re truly welcome. Glad to hear it helped you.
Regards,
Sergey.
on January 16, 2012 at 10:57 am
· 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
on January 16, 2012 at 9:01 pm
· 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.
on June 7, 2012 at 12:24 pm
· Permalink
Hi,
is it necessary to have three database users (ofbiz, ofbizolap, ofbiztenant) or it will work with one user too?
Cheers
on June 7, 2012 at 7:53 pm
· 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.
on September 7, 2012 at 9:03 am
· 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)
on September 14, 2012 at 9:34 am
· 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.
on October 12, 2012 at 12:58 am
· 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
on December 25, 2014 at 2:21 am
· 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.
on December 26, 2014 at 11:41 pm
· 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
on December 6, 2015 at 2:19 pm
· 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?
on December 6, 2015 at 2:30 pm
· 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
on December 7, 2015 at 11:23 am
· 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.