Page 1 of 1

Setting up different DB during docker installation

PostPosted:Fri May 03, 2019 4:59 pm
by LauryMenton
Hello,

Since 2 weeks before I was deeply testing OpenKM (CE) 6.3.7, and I have to say that it left me sorely surprised of its potential, even being CE. (I have solved some small deficiencies in its configuration, or not "definciencies" but needed pre-configurations to make OpenKM OCR by itself; for example, pre-processing the files with Abbyy FineReader and its Hot Folder). So, now I would like to make a clean installation, using my own DB.

The problem is that I couldn't find a way to connect MySQL DB (container) to the OpenKM container, so integrated one is used all the time. All the available documentation I could find was for non-dockerized installations.
As I read, editing the 'OpenKM.cfg' file, located on the root of a dir called 'TOMCAT' (or TOMCAT_HOME), will allow us to specify a custom DB to be used:
https://www.openkm.com/wiki/index.php/U ... _databases
https://www.openkm.com/wiki/index.php/A ... figuration

These are the files and directories I can see on my linked docker volume:
Code: Select all
cache
datastore
index
okmdb.mv.db
okmdb.trace.db
(no OpenKM.cfg file anywhere)

And this is my docker setup:
Code: Select all
- VOLUMES
/volume1/docker/OpenKM:/opt/openkm/repository
- PORTS
custom:8080
- NETWORK
bridged
- VARIABLES
PATH: /user/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
JAVA_VER: 8
TOMCAT_VER: 8.5.24
OPENKM_VER: 6.3.7
- OTHERS
CMD: /bin/sh -c '/opt/openkm/bin/catalina.sh run'
Restart (error): always
(If I set '/opt/openkm' as working dir, as official doc and other users say, it just fails to initialize the container)


Thanks in advanced.

Re: Setting up different DB during docker installation

PostPosted:Fri May 03, 2019 5:05 pm
by LauryMenton
I decided to use MySQL as a production installation, but do you recommend another one? Maybe PostgreSQL, MariaDB...
https://hub.docker.com/_/mysql

And if it helps I forgot to mention the following things:
- The server is a Synology NAS (DSM)
- I'm using the integrated Docker app to set up and install the OpenKM container (GUI), but can use PuTTY if needed.

Re: Setting up different DB during docker installation

PostPosted:Sun May 05, 2019 12:30 pm
by pavila
This Docker image is configured to use an embedded database and not recommended for production. This is configured this way yo simplify the deployment and configuration. As Docker people recommend, the database server (in this case MySQL) should be launched in another container, managed by Docker Composer, for example. But this might be a "little difficult" for people who has no Docker expertise.

If you want to use MySQL I would recommend modifying this container and, if really needed, create another image based on your personalised container.

Comments are welcome.

Regards.

Re: Setting up different DB during docker installation

PostPosted:Sun May 05, 2019 7:36 pm
by LauryMenton
Thank you for your reply. I will try to edit the the image directly.

Re: Setting up different DB during docker installation

PostPosted:Mon May 06, 2019 1:42 pm
by LauryMenton
I just found a pre-configured installation template with necessary files that seems to be needed to be modified/adjusted to work, in this example, with PostgreSQL. (PostgreSQL is the other DB of my choise, as I already used it on other containers)
https://github.com/singhfulda/openkm-postgresql

My questions are:
- Can I just take these files as an example, edit original ones included in the openkm/openkm-ce docker image with the same content, taking PotsgreSQL as alternative DB? In other words, are these files the correct ones that I need to customize on my own image (included content)?
- As I won't use the DockerCompose file (don't need it, by now), is JAVA_OPTS=-Djdbc.url=jdbc:postgresql://openkm_db:DB_PORT/openkm variable/value correct to set on OpenKM container enviroment section? As I will create a separate container for DB...

Re: Setting up different DB during docker installation

PostPosted:Mon May 06, 2019 1:52 pm
by LauryMenton
As a suggestion I would like to say that some custom vars for alternative DB installation would be extremly useful, like other DMS containers. For example:
- OKM_DATABASE_NAME: db_name
- OKM_DATABASE_USER: db_user
- OKM_DATABASE_PASSWORD: db_pass
- OKM_DATABASE_PORT: connection_port
- OKM_DATABASE_ENGINE: example.db.postgresql

So, by just adding and filling the variables above, installation will be much easier than before. And no internal files will be needed to be customized.

Re: Setting up different DB during docker installation

PostPosted:Mon May 06, 2019 2:23 pm
by LauryMenton
LauryMenton wrote: Mon May 06, 2019 1:42 pm I just found a pre-configured installation template with necessary files that seems to be needed to be modified/adjusted to work, in this example, with PostgreSQL. (PostgreSQL is the other DB of my choise, as I already used it on other containers)
https://github.com/singhfulda/openkm-postgresql

My questions are:
- Can I just take these files as an example, edit original ones included in the openkm/openkm-ce docker image with the same content, taking PotsgreSQL as alternative DB? In other words, are these files the correct ones that I need to customize on my own image (included content)?
- As I won't use the DockerCompose file (don't need it, by now), is JAVA_OPTS=-Djdbc.url=jdbc:postgresql://openkm_db:DB_PORT/openkm variable/value correct to set on OpenKM container enviroment section? As I will create a separate container for DB...
I just realized that original image 'server.xml' file has the following DBs settings:
Code: Select all
    <!-- <Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
            maxTotal="100" maxIdle="30" maxWaitMillis="10000" validationQuery="select 1 from INFORMATION_SCHEMA.SYSTEM_USERS"
            username="sa" password="" driverClassName="org.hsqldb.jdbcDriver"
            url="jdbc:hsqldb:${catalina.home}/repository/okmdb"/> -->

    <!-- <Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
            maxTotal="100" maxIdle="30" maxWaitMillis="10000" validationQuery="select 1 from dual"
            username="openkm" password="*****" driverClassName="oracle.jdbc.driver.OracleDriver"
            url="jdbc:oracle:thin:@localhost:1521:XE"/> -->

    <!-- <Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
          maxTotal="100" maxIdle="30" maxWaitMillis="10000" validationQuery="select 1"
          username="sa" password="***" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
          url="jdbc:sqlserver://localhost:1433;databaseName=okmdb"/> -->

    <!-- <Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
            maxTotal="100" maxIdle="30" maxWaitMillis="10000" validationQuery="select 1"
            username="openkm" password="*****" driverClassName="org.postgresql.Driver"
            url="jdbc:postgresql://localhost:5432/okmdb"/> -->

    <!-- <Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
            maxTotal="100" maxIdle="30" maxWaitMillis="10000" validationQuery="select 1"
            username="openkm" password="*****" driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/okmdb?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8"/> -->
Should I delete all except? Or can I keep them on there?
Code: Select all
    <!-- <Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
            maxTotal="100" maxIdle="30" maxWaitMillis="10000" validationQuery="select 1"
            username="openkm" password="*****" driverClassName="org.postgresql.Driver"
            url="jdbc:postgresql://localhost:5432/okmdb"/> -->
And adjust it to fit to my PostgreSQL container custom settings? Like...
Code: Select all
    <!-- <Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
            maxTotal="100" maxIdle="30" maxWaitMillis="10000" validationQuery="select 1"
            username="OpenKM" password="A1B2C3D4" driverClassName="org.postgresql.Driver"
            url="jdbc:postgresql://localhost:db_port/okmdb"/> -->
But if I edit image as follows, OpenKM won't use PostgreSQL container DB. I mean, OpenKM creates its own DB file:
Code: Select all
openkm/openkm-ce > ###ID### > layer.tar > opt > openkm > OpenKM.cfg

# OpenKM Hibernate configuration values
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
#hibernate.dialect=org.hibernate.dialect.HSQLDialect
#hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
#hibernate.dialect=org.hibernate.dialect.SQLServerDialect
#hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
#hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
hibernate.hbm2ddl=create

# Logback configuration file
logback.config=logback.xml

Re: Setting up different DB during docker installation

PostPosted:Mon May 06, 2019 3:04 pm
by pavila
If you want to change the database you have to stop OpenKM, change the server.xml, change the OpenKM.cfg and then start OpenKM again. The "hibernate.hbm2ddl=create" configuration property tells OpenKM to create the database. If the database is already created it should be set to "none".

I'm pretty sure the database configuration can be automated, but need some work.

Re: Setting up different DB during docker installation

PostPosted:Mon May 06, 2019 5:52 pm
by LauryMenton
I don't have direct access to the container files, even using PuTYY as root. That's why I modify images directly to perform any changes, and make them permanent for future installations.

I'm following these two tutorials:
- https://www.openkm.com/wiki/index.php/P ... OpenKM_6.2
- https://docs.openkm.com/kcenter/view/ok ... resql.html (preffered)
... but I'm lost on the Configure application login:
Code: Select all
<security:authentication-manager alias="authenticationManager">
  <security:authentication-provider>
    <security:password-encoder hash="md5"/>
    <security:jdbc-user-service 
        data-source-ref="dataSource"
        users-by-username-query="select usr_id, usr_password, 1 from OKM_USER where usr_id=? and usr_active='T'"
        authorities-by-username-query="select ur_user, ur_role from OKM_USER_ROLE where ur_user=?"/>
  </security:authentication-provider>
</security:authentication-manager>
Specifically on:
Code: Select all
users-by-username-query="select usr_id, usr_password, 1 from OKM_USER where usr_id=? and usr_active='T'"
authorities-by-username-query="select ur_user, ur_role from OKM_USER_ROLE where ur_user=?"/>
On the above portion of XML do I need to change 'usr_id=x' to the username of my DB (usr=openkm)? Same for 'usr_active=xxx' (usr=openkm), ur_user=xxx' (not sure about what is that)?

Re: Setting up different DB during docker installation

PostPosted:Mon May 06, 2019 6:26 pm
by LauryMenton
pavila wrote: Mon May 06, 2019 3:04 pm If you want to change the database you have to stop OpenKM, change the server.xml, change the OpenKM.cfg and then start OpenKM again. The "hibernate.hbm2ddl=create" configuration property tells OpenKM to create the database. If the database is already created it should be set to "none".

I'm pretty sure the database configuration can be automated, but need some work.
I can't do that, I need OpenKM to be running while trying to edit some file (if I can actually).
So, this is what I see after navigating inside the docker container. Is the 'TOMCAT_HOME' directory the one listed on the pic: /opt/openkm?

Image

Re: Setting up different DB during docker installation

PostPosted:Mon May 06, 2019 6:51 pm
by LauryMenton
After editing both files you have mentioned before ( OpenKM.cfg, server.xml):

Image

Image

Image

Re: Setting up different DB during docker installation

PostPosted:Mon May 06, 2019 7:10 pm
by LauryMenton
And now, after restarting it, db is not reachable.

Image

Re: Setting up different DB during docker installation

PostPosted:Tue May 07, 2019 6:43 am
by pavila
If you post 5 messages with different questions and issues is a bit difficult to keep an order in this thread. In will only answer the last one.

About the error, is PostgreSQL database installed? Is the "okmdb" database created?

Regards.

Re: Setting up different DB during docker installation

PostPosted:Tue May 07, 2019 9:52 am
by LauryMenton
Sorry about that. Ok, let's talk about last one.

Is PostgreSQL DB installed?
Yes. The container is already created and running, listening for external connections.

Is 'okmdb' DB created?
I didn't create any db file manually. I thought if I set 'hibernate.hbm2ddl=' string to create, OpenKM should create it automatically, connecting to the PostgreSQL container via 32785:5432 port.

Maybe, to simulate I stop OpenKM, edit files, then launch it again, should I export OpenKM container (.tar), edit files, re-compress it. And finally, import it?

Re: Setting up different DB during docker installation

PostPosted:Tue May 07, 2019 10:37 am
by pavila
In the server.xml it's configured a PostgreSQL in the same container where OpenKM is installed. If PostgreSQL is in another container you should configure the JDBC URL properly. Also you need to create an "openkm" user and a database called "okmdb". Of course you can choose another user and database, but remember to modify the server.xml according to this info.

When you set the "hibernate.hbm2ddl" configuration property to "create", OpenKM will create the tables but not the database.