6/19/08

Setup MySQL with Ofbiz

Download ofbiz weekily build and extract it somewhere you want. From your ofbiz directory, edit file entityengine.xml in framework/entity/config
add new datasources below localmysql datasource part

    <datasource name="custommysql"
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://localhost/ofbiz"
jdbc-username="ofbizuser"
jdbc-password="mypass"
isolation-level="ReadCommitted"
pool-minsize="2"
pool-maxsize="250"/>
<!-- <jndi-jdbc jndi-server-name="localjndi" jndi-name="java:/MySqlDataSource" isolation-level="Serializable"/> -->
</datasource>
<datasource name="custommysqlolap"
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://localhost/ofbizolap?autoReconnect=true"
jdbc-username="ofbizuser"
jdbc-password="mypass"
isolation-level="ReadCommitted"
pool-minsize="2"
pool-maxsize="250"/>
<!-- <jndi-jdbc jndi-server-name="localjndi" jndi-name="java:/DerbyDataSource" isolation-level="ReadCommitted"/> -->
</datasource>

change the "default" delegator make it look like this
<delegator name="default" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main" distributed-cache-clea$
<group-map group-name="org.ofbiz" datasource-name="custommysql"/>
<group-map group-name="org.ofbiz.olap" datasource-name="custommysqlolap"/>
</delegator>


put mysql driver under framework/entity/lib/jdbc
[admin@localhost ofbiz]$ ls framework/entity/lib/jdbc
derby-10.4.1.3.jar mysql-connector-java-5.1.6-bin.jar
[admin@localhost ofbiz]$


prepare database <create database ,grant user>
mysql> create database ofbiz;
Query OK, 1 row affected (0.05 sec)

mysql> create database ofbizolap;
Query OK, 1 row affected (0.05 sec)

mysql> grant all on *.* to 'ofbizuser'@'localhost' identified by 'mypass';
Query OK, 0 rows affected (0.03 sec)

mysql>


now we're ready to run ant to load database into mysql. Let's go.
[admin@localhost ofbiz]$ ./ant run-install
...
[java] 2008-06-19 10:59:06,245 (main) [ ContainerLoader.java:114:INFO ] Shutting down containers

BUILD SUCCESSFUL
Total time: 5 minutes 25 seconds
[admin@localhost ofbiz]$

9 comments:

Anonymous said...

this is spot on..All the documentation say derby is not efficient, yet they never show how to setup another databse..lol

Anonymous said...

Very good documentation... I modified it a bit for my needs but I agree the ofbiz docs are missing this aspect.

Anonymous said...

I followed exactly the same was as mentioned , but i am getting a lot of MYSQL exceptions and finally unable to see the list of tables installed in my database named 'ofbiz' , so any way out

Anonymous said...

I have used java -jar ofbiz.jar -install command to install ofbiz and after a series of exceptions the control comes out to the command prompt

alam said...

hi why we need ofbizolap? the default ofbiz don't need this..

Anonymous said...

Hi,

I am using mysql as db for my ofbiz . I usually see 100's of established connections (netstat ) between ofbiz and mysql servers eventhough no threads utlizing it. All these connections used to be in sleep state. It frequently get refreshed by ofbiz and again be in sleep state. It never reaches the wait_timeout value set in the DB.

Where should I look at in ofbiz for the properties which control timeout for DB connections.

Patrice said...

There is a mistake in the code Allegatore at the end of the first line, it is truncated.
I cannot post the code here as the editor do not allow special characters !

Andrea Palmatè said...

to use mysql 5.5 you must change the file
framework/entity/src/org/fbiz/entity/jdbc/DatabaseUtil.java and change TYPE to ENGINE since TYPE is removed in mySQL 5.5

Anonymous said...

Andrea Palmatè ..
pls specify the line or describe where to change the TYPE to ENGINE..