Thursday, November 26, 2015

HOW TO MIGRATE OFBIZ FROM DERBY TO MYSQL DATABASE

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
b. Navigate to http://localhost:8080/catalog/
c. Go to Applications>WebTools
d. 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 the following datasources below the datasource 'localmysql'
<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/ofbiz?autoReconnect=true"
            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.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="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:
<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.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
c. Save this file
Step -IV
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
1. Run the following command from command prompt:
ofbiz-dir>java -jar ofbiz.jar -install
2. Start OfBiz


ref: https://cwiki.apache.org/confluence/display/OFBIZ/How+to+migrate+OfBiz+from+Derby+to+MySQL+database