THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
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
3. MySQL JDBC driver: mysql-connector-java-5.1.14-bin.jar - to be placed in <ofbiz-dir>/framework/entity/lib/jdbc
4. OfBiz: apache-ofbiz-10.04
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:
- 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. Update the following datasources
<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 ;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 ;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 ;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:
<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