• Setting up different DB during docker installation

  • Problems with installing OpenKM? No problemo, the solution is closer than you think.
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.
 #47846  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.
Last edited by LauryMenton on Fri May 03, 2019 5:19 pm, edited 4 times in total.
 #47847  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.
 #47863  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.
 #47869  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...
 #47870  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.
 #47871  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
 #47872  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.
 #47875  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)?
 #47877  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
 #47884  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.
 #47886  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?
 #47888  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.

About Us

OpenKM is part of the management software. A management software is a program that facilitates the accomplishment of administrative tasks. OpenKM is a document management system that allows you to manage business content and workflow in a more efficient way. Document managers guarantee data protection by establishing information security for business content.