Page History

I have used the following versions for this topic:

1. Operating system: Windows7 64 bit
2. MySQL: mysql-5.5.23-winx64 (zip) - the topic is still valid for 32 bit version
23. MySQL JDBC driver: mysql-connector-java-5.1.14-bin.jar - to be placed in <ofbiz-dir>/framework/entity/lib/jdbc
34. OfBiz: apache-ofbiz-10.04

Note

Above given information stands correct for OFBiz 10.04. If you want to use an external DBMS, instead of the embedded Derby with latest release 16.11 or trunk, the only thing you need to do is add a dependency in your component's build.gradle to the MySQL JDBC driver. Search in Jcenter for the database driver suitable for the database installed on your production system. For example, under the dependencies section you can add this for mysql: runtime 'mysql:mysql-connector-java:5.6.4' Of course you need to make sure the connector is compatible with your version of the database installed!

Also consider:

mysql.conf
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

Mysql Version: > 5.6.4 (supports datetime milliseconds)

Step - I

C:\mysql-5.5.23-winx64\bin>mysql -u root
mysql>create database ofbiz;
mysql>create database ofbizolap;
mysql>create database ofbiztenant;
mysql>use mysql;
mysql>select database();
mysql>create user ofbiz@localhost;
mysql>create user ofbizolap@localhost;
mysql>create user ofbiztenant@localhost;
mysql>update user set password=PASSWORD("ofbiz") where User='ofbiz';
mysql>update user set password=PASSWORD("ofbizolap") where User='ofbizolap';
mysql>update user set password=PASSWORD("ofbiztenant") where User='ofbiztenant';
mysql>grant all privileges on *.* to 'ofbiz'@localhost identified by 'ofbiz';
mysql>grant all privileges on *.* to 'ofbizolap'@localhost identified by 'ofbizolap';
mysql>grant all privileges on *.* to 'ofbiztenant'@localhost identified by 'ofbiztenant';

Step - II

Use webtools to export all data to XML: a.

Start OfBiz

...

Navigate to http://localhost:8080/

...

webtools

Go to section 'Entity XML Tools' and click the link 'XML Data Export All' - export the data to a directory of your choice

Step - III

1. Stop OfBiz server
2. Create a backup of <ofbiz-dir>/framework/entity/config/entityengine.xml
3. Edit entityengine.xml as follows:
a. Add Update the following datasources below the datasource 'localmysql'
<datasource name="localmysqlolap"

Code Block

	xml
	xml

    <datasource name="localmysql"
        helper-class="org.apache.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="utf8"
        collate="utf8_general_ci">
    <read-data reader-name="tenant"/>
    <read-data reader-name="seed"/>
    <read-data reader-name="seed-initial"/>
    <read-data reader-name="demo"/>
    <read-data reader-name="ext"/>
    <read-data reader-name="ext-test"/>
    <read-data reader-name="ext-demo"/>
    <inline-jdbc
            jdbc-driver="com.mysql.jdbc.Driver"
            jdbc-uri="jdbc:mysql://127.0.0.1/ofbiz?autoReconnect=true&amp;characterEncoding=UTF-8"
            jdbc-username="ofbiz"
            jdbc-password="ofbiz"
            isolation-level="ReadCommitted"
            pool-minsize="2"
            pool-maxsize="250"
            time-between-eviction-runs-millis="600000"/><!-- Please note that at least one person has experienced a problem with this value with MySQL
            and had to set it to -1 in order to avoid this issue.
            For more look at http://markmail.org/thread/5sivpykv7xkl66px and http://commons.apache.org/dbcp/configuration.html-->
    <!-- <jndi-jdbc jndi-server-name="localjndi" jndi-name="java:/MySqlDataSource" isolation-level="Serializable"/> -->
</datasource>    
<datasource name="localmysqlolap"
            helper-class="org.apache.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="

...

utf8"
            collate="

...

utf8_general_

...

ci">

...


        <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

...

&amp;characterEncoding=UTF-8"
                jdbc-username="ofbizolap"

...


                jdbc-password="ofbizolap"

...


                isolation-level="ReadCommitted"

...


                pool-minsize="2"

...


                pool-maxsize="250"

...


                time-between-eviction-runs-millis="600000"/>

...

 
    </datasource>

...


    
    <datasource name="localmysqltenant"

...


            helper-class="org.apache.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="

...

utf8"
            collate="

...

utf8_general_

...

ci">

...


        <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

...

&amp;characterEncoding=UTF-8"
                jdbc-username="ofbiztenant"

...


                jdbc-password="ofbiztenant"

...


                isolation-level="ReadCommitted"

...


                pool-minsize="2"

...


                pool-maxsize="250"

...


                time-between-eviction-runs-millis="600000"/>

...

 
    </datasource>

b. Replace derby with mysql in default, default-no-eca and test delegators as follows:

Code Block

	xml
	xml

    <delegator name="default" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main" distributed-cache-clear-enabled="false">

...


        <group-map group-name="org.apache.ofbiz" datasource-name="localmysql"/>

...


        <group-map group-name="org.apache.ofbiz.olap" datasource-name="localmysqlolap"/>

...


        <group-map group-name="org.apache.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.apache.ofbiz" datasource-name="localmysql"/>

...


        <group-map group-name="org.apache.ofbiz.olap" datasource-name="localmysqlolap"/>

...


        <group-map group-name="org.apache.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.apache.ofbiz" datasource-name="localmysql"/>

...


        <group-map group-name="org.apache.ofbiz.olap" datasource-name="localmysqlolap"/>

...


        <group-map group-name="org.apache.ofbiz.tenant" datasource-name="localmysqltenant"/>

...


    </delegator>

c. Save this file

Step -IV (It has already been changed in OFBiz latest versions so this step can be skipped, check if you are using ofbiz10.04)

1. Open ofbiz/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java in eclipse and replace sqlBuf.append(" TYPE ") with sqlBuf.append(" ENGINE ")
2. Save the file and run ofbiz/build.xml
Note: This is required because TYPE was deprecated in MySQL 4.0 and removed in MySQL 5.5. The older TYPE option was synonymous with the new option ENGINE

Step -V

...

(Given instructions stands correct for ofbiz10.04, instructions varies in latest versions, please refer Ant to Gradle doc here)

Run the following command from command prompt:
ofbiz-dir>java -jar ofbiz.jar -install

...

Start OfBiz

...

Use webtools to import all data from XML:

...

Navigate to http://localhost:8080/

...

webtools

Go to section 'Entity XML Tools' and click the link 'XML Data Import Dir' -> In the 'Absolute directory path:' enter the full path of the directory where you exported the data in Step - II

Space shortcuts

Child pages

Versions Compared

Old Version 1

New Version Current

Key