Setting up different DB during docker installation

Problems with installing OpenKM? No problemo, the solution is closer than you think.
Forum rules
Please, before asking something see the documentation wiki or use the search feature of the forum. And remember we don't have a crystal ball or mental readers, so if you post about an issue tell us which OpenKM are you using and also the browser and operating system version. For more info read How to Report Bugs Effectively.
Post Reply
LauryMenton
Junior Boarder
Junior Boarder
Posts: 24
Joined: Sun Apr 07, 2019 12:23 am

Setting up different DB during docker installation

Post by LauryMenton » Fri May 03, 2019 4:59 pm

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.
Last edited by LauryMenton on Fri May 03, 2019 5:19 pm, edited 4 times in total.

LauryMenton
Junior Boarder
Junior Boarder
Posts: 24
Joined: Sun Apr 07, 2019 12:23 am

Re: Setting up different DB during docker installation

Post by LauryMenton » Fri May 03, 2019 5:05 pm

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.

pavila
Moderator
Moderator
Posts: 3066
Joined: Tue Dec 11, 2007 6:02 pm
Location: Alicante, Spain
Contact:

Re: Setting up different DB during docker installation

Post by pavila » Sun May 05, 2019 12:30 pm

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.

LauryMenton
Junior Boarder
Junior Boarder
Posts: 24
Joined: Sun Apr 07, 2019 12:23 am

Re: Setting up different DB during docker installation

Post by LauryMenton » Sun May 05, 2019 7:36 pm

Thank you for your reply. I will try to edit the the image directly.

LauryMenton
Junior Boarder
Junior Boarder
Posts: 24
Joined: Sun Apr 07, 2019 12:23 am

Re: Setting up different DB during docker installation

Post by LauryMenton » 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...

LauryMenton
Junior Boarder
Junior Boarder
Posts: 24
Joined: Sun Apr 07, 2019 12:23 am

Re: Setting up different DB during docker installation

Post by LauryMenton » Mon May 06, 2019 1:52 pm

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.

LauryMenton
Junior Boarder
Junior Boarder
Posts: 24
Joined: Sun Apr 07, 2019 12:23 am

Re: Setting up different DB during docker installation

Post by LauryMenton » Mon May 06, 2019 2:23 pm

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

pavila
Moderator
Moderator
Posts: 3066
Joined: Tue Dec 11, 2007 6:02 pm
Location: Alicante, Spain
Contact:

Re: Setting up different DB during docker installation

Post by pavila » 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.

LauryMenton
Junior Boarder
Junior Boarder
Posts: 24
Joined: Sun Apr 07, 2019 12:23 am

Re: Setting up different DB during docker installation

Post by LauryMenton » Mon May 06, 2019 5:52 pm

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)?

LauryMenton
Junior Boarder
Junior Boarder
Posts: 24
Joined: Sun Apr 07, 2019 12:23 am

Re: Setting up different DB during docker installation

Post by LauryMenton » Mon May 06, 2019 6:26 pm

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

LauryMenton
Junior Boarder
Junior Boarder
Posts: 24
Joined: Sun Apr 07, 2019 12:23 am

Re: Setting up different DB during docker installation

Post by LauryMenton » Mon May 06, 2019 6:51 pm

After editing both files you have mentioned before ( OpenKM.cfg, server.xml):

Image

Image

Image

LauryMenton
Junior Boarder
Junior Boarder
Posts: 24
Joined: Sun Apr 07, 2019 12:23 am

Re: Setting up different DB during docker installation

Post by LauryMenton » Mon May 06, 2019 7:10 pm

And now, after restarting it, db is not reachable.

Image

pavila
Moderator
Moderator
Posts: 3066
Joined: Tue Dec 11, 2007 6:02 pm
Location: Alicante, Spain
Contact:

Re: Setting up different DB during docker installation

Post by pavila » Tue May 07, 2019 6:43 am

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.

LauryMenton
Junior Boarder
Junior Boarder
Posts: 24
Joined: Sun Apr 07, 2019 12:23 am

Re: Setting up different DB during docker installation

Post by LauryMenton » Tue May 07, 2019 9:52 am

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?

pavila
Moderator
Moderator
Posts: 3066
Joined: Tue Dec 11, 2007 6:02 pm
Location: Alicante, Spain
Contact:

Re: Setting up different DB during docker installation

Post by pavila » Tue May 07, 2019 10:37 am

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.

Post Reply