1

Following this tutorial to connect OFBiz to a MySQL database.

I'm trying to create an OFBiz deployment with persistent storage where new pods will store and retrieve data from MySQL.

Dockerfile for OFBiz pods

FROM openjdk:8

RUN apt-get update && \
apt-get install -y gradle unzip wget vim

RUN wget https://downloads.apache.org/ofbiz/apache-ofbiz-17.12.05.zip && \
unzip apache-ofbiz-17.12.05

WORKDIR apache-ofbiz-17.12.05

RUN gradle wrapper && \
./gradlew cleanAll loadAll

# Allowing URL $URL
RUN sed -i -e 's/host-headers-allowed=/host-headers-allowed=$URL,/g' /apache-ofbiz-17.12.05/framework/security/config/security.properties

COPY entityengine.xml /apache-ofbiz-17.12.05/framework/entity/config/

CMD ["/apache-ofbiz-17.12.05/gradlew", "ofbiz"]

I am hosting everything on GCP with OFBiz inside a pod running on GKE and am using Google Cloud SQL as a managed MySQL service. I have Cloud SQL set up with a public IP at 34.75.32.160.

Preliminary SQL Commands I ran the following commands on my database before starting OFBiz.

create database ofbiz;  
create database ofbizolap;  
create database ofbiztenant;  
use mysql;  
select database();  
create user ofbiz@localhost;  
create user ofbizolap@localhost;  
create user ofbiztenant@localhost;  
update user set password=PASSWORD("FAKEPASSWORD") where User='ofbiz';  
update user set password=PASSWORD("FAKEPASSWORD") where User='ofbizolap';  
update user set password=PASSWORD("FAKEPASSWORD") where User='ofbiztenant';  
grant all privileges on *.* to 'ofbiz'@localhost identified by 'ofbiz';  
grant all privileges on *.* to 'ofbizolap'@localhost identified by 'ofbizolap';  
grant all privileges on *.* to 'ofbiztenant'@localhost identified by 'ofbiztenant';

OFBiz Configuration with entityengine.xml The data backend for ofbiz is configured inside a file entityengine.xml which I have configured in the following way:

Data Source Declarations I declared three different datasources localmysql, localmysqlolap, and localmysqltenant each connecting to my Cloud SQL server with JDBC.

<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://34.75.32.160/ofbiz?autoReconnect=true&amp;characterEncoding=UTF-8"
                jdbc-username="ofbiz"
                jdbc-password="<password>"
                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="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://34.75.32.160/ofbizolap?autoReconnect=true&amp;characterEncoding=UTF-8"
                jdbc-username="ofbizolap"
                jdbc-password="<password>"
                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="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="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://34.75.32.160/ofbiztenant?autoReconnect=true&amp;characterEncoding=UTF-8"
                jdbc-username="ofbiztenant"
                jdbc-password="<password>"
                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>

Delegators The delegators tell OFBiz which datasource to use for which functions (in this case: the functions are normal, olap, and tenant). There is one entry for the different ways we can run OFBiz: default, default-no-eca, and test.

I have it set up here to use the MySQL data sources I defined above.

    <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="localsqltenant"/>
    </delegator>
    <!-- May be used when you create a service that manages many data for massive imports, this for performance reason or to escape functional cases --> 
    <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>

    <!-- Be sure that your default delegator (or the one you use) uses the same datasource for test. You must run "gradlew loadAll" before running "gradlew testIntegration" -->
    <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>

Error When I run OFBiz with this configuration I receive the following HTTP 500 error:

java.lang.IllegalArgumentException: Could not get next sequenced ID for sequence name: Visit
    org.apache.ofbiz.entity.GenericDelegator.getNextSeqId(GenericDelegator.java:2280)
    org.apache.ofbiz.entity.GenericDelegator.getNextSeqId(GenericDelegator.java:2268)
    org.apache.ofbiz.entity.GenericEntity.setNextSeqId(GenericEntity.java:657)
    org.apache.ofbiz.entity.GenericDelegator.createSetNextSeqId(GenericDelegator.java:771)
    org.apache.ofbiz.webapp.stats.VisitHandler.getVisit(VisitHandler.java:187)
    org.apache.ofbiz.webapp.stats.VisitHandler.getVisitId(VisitHandler.java:100)
    org.apache.ofbiz.webapp.control.ControlServlet.doGet(ControlServlet.java:201)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:686)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:791)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    org.apache.ofbiz.webapp.control.SameSiteFilter.doFilter(SameSiteFilter.java:44)
    org.apache.ofbiz.webapp.control.ContextFilter.doFilter(ContextFilter.java:191)
    org.apache.ofbiz.webapp.control.ControlFilter.doFilter(ControlFilter.java:157)
    org.apache.logging.log4j.web.Log4jServletFilter.doFilter(Log4jServletFilter.java:71)

Kubernetes error logs Using kubectl logs $POD, here is one of the errors that shows up. All of the errors are related to jdbc driver it seems.

2021-03-27 23:33:50,408 |OFBiz-JobPoller      |DBCPConnectionFactory         |E| null
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
    at java.net.URLClassLoader.findClass(URLClassLoader.java:382) ~[?:1.8.0_282]
    at java.lang.ClassLoader.loadClass(ClassLoader.java:418) ~[?:1.8.0_282]
    at java.lang.ClassLoader.loadClass(ClassLoader.java:351) ~[?:1.8.0_282]
    at java.lang.Class.forName0(Native Method) ~[?:1.8.0_282]
    at java.lang.Class.forName(Class.java:348) ~[?:1.8.0_282]
    at org.apache.ofbiz.entity.connection.DBCPConnectionFactory.getConnection(DBCPConnectionFactory.java:90) [ofbiz.jar:?]
    at org.apache.ofbiz.entity.transaction.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:79) [ofbiz.jar:?]
    at org.apache.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:261) [ofbiz.jar:?]
    at org.apache.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:367) [ofbiz.jar:?]
    at org.apache.ofbiz.entity.datasource.GenericDAO.selectCountByCondition(GenericDAO.java:1049) [ofbiz.jar:?]
    at org.apache.ofbiz.entity.datasource.GenericHelperDAO.findCountByCondition(GenericHelperDAO.java:157) [ofbiz.jar:?]
    at org.apache.ofbiz.entity.GenericDelegator.findCountByCondition(GenericDelegator.java:1694) [ofbiz.jar:?]
    at org.apache.ofbiz.entity.GenericDelegator.findCountByCondition(GenericDelegator.java:1653) [ofbiz.jar:?]
    at org.apache.ofbiz.service.job.JobManager.isAvailable(JobManager.java:154) [ofbiz.jar:?]
    at org.apache.ofbiz.service.job.JobPoller$JobManagerPoller.run(JobPoller.java:223) [ofbiz.jar:?]
    at java.lang.Thread.run(Thread.java:748) [?:1.8.0_282]```
Vy Do
  • 46,709
  • 59
  • 215
  • 313
Karl Cagalawan
  • 11
  • 2
  • The configuration looks good to me. OFBiz should have created the datamodel/tables in your database, can you see them? Are there any errors in console.log/ofbiz.log regarding the database connection when you start OFBiz? Have you checked that you database is accessible whit a database client and user/password? – Michael Brohl Mar 27 '21 at 17:19
  • Thanks for the reply. OFBiz has not created any tables in my database. As for the errors, there are a whole list of them related to connecting to the SQL connection which I am not sure how to parse. – Karl Cagalawan Mar 27 '21 at 23:46
  • I have updated my post with my OFBiz Dockerfile at the start and the one of the related errors in the cloud shell terminal towards the end. – Karl Cagalawan Mar 28 '21 at 00:18
  • You dońt have the MySQL driver installed. Please follow the INSTALL document to get everything setup correctly. – Michael Brohl Mar 28 '21 at 00:23
  • I have tried using multiple mysql-connector-java files and none of them have worked so far. I have made the following changes to my dockerfile after changing the allowed URL headers ```WORKDIR lib RUN wget https://bintray.com/bintray/jcenter/mysql%3Amysql-connector-java/8.0.23#files/mysql%2Fmysql-connector-java%2F8.0.23``` The mysql-connector-java file is in the dir /apache/lib/. Is there a step I'm missing? – Karl Cagalawan Mar 28 '21 at 01:45
  • I have added the line ```runtime 'mysql:mysql-connector-java:8.0.23'``` in build.gradle and have overcome that driver problem. Now I am running into issues connecting to the database. – Karl Cagalawan Mar 28 '21 at 02:07
  • I figured it out! In my preliminary SQL commands, I had kept the command specifying $users@'localhost' instead of pointing to my pods. ```kubectl logs $POD``` indicates that the pod is running OFBiz without error. Checking the MySQL database shows that the tables have populated. Despite all of this, I am still getting the same ```HTTP Status 500 – Internal Server Error``` – Karl Cagalawan Mar 28 '21 at 04:41
  • I think I have it working. I had some problems with how my DNS was set up because I was working on two different OFBiz deployments and got them mixed up. I am able to see the login page but the default logins `username=admin password=ofbiz` are unavailable and I can no longer log in. – Karl Cagalawan Mar 28 '21 at 19:31
  • @KarlCagalawan Could you put as an answer to the post the solution/workaround you found to your issue? It would be helpful for other members of the community who are facing similar problems. – Nahuel Apr 15 '21 at 08:07

1 Answers1

0

${ofbiz install dir}/framework/entity/lib/jdbc; replacing the old or incorrect version of the driver there

JDBC Driver for MySQL (Connector/J): https://www.mysql.com/products/connector/

Read more at https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=7045252#ApacheOFBizTechnicalProductionSetupGuide-DatabaseSetup

Vy Do
  • 46,709
  • 59
  • 215
  • 313