From HSQLDB to MySQL

[oconesa]

Is it possible to change from Hsqldb to mysql in OpenKM 3.0? What I need to do?

[pavila]

You need to configure it in repository.xml file. You will create a new repository, so you need to export document in your actual repository and import them in the new one.

[oconesa]

Well, I really want to change the Hypersonic Database from openkm-ds.xml and change it to MySQL in order to manage the access control directly from the database.

I am not interested on the performance (by the moment)

[pavila]

There are several databases in OpenKM.

* OKMActivity -> Log user activity
* OKMAuth -> User authentication
* OKMDashboardStats -> Store dashboard info
* OKMWorkflow -> Store workflow process and instances (Only in Enterprise Edition)

What database do you want to change?

[oconesa]

OKMAuth, I suppose.

I want to access to the database with a GUI client and change users and password directly, alternatively to the web admin interface.

[pavila]

This is the DDL for Auth database:

Code: Select all

#
# Generic auth database definition
#
CREATE TABLE users(usr_id VARCHAR(32), usr_pass VARCHAR(32) NOT NULL, usr_email VARCHAR(32) NOT NULL, usr_active BOOLEAN, PRIMARY KEY(usr_id));
CREATE TABLE roles(rol_id VARCHAR(32), PRIMARY KEY(rol_id));
CREATE TABLE user_role(ur_user VARCHAR(32), ur_role VARCHAR(32), PRIMARY KEY(ur_user, ur_role));

It can run in MySQL to create the tables. Then you have to modify the AuthDS datasource to match you MySQL configuration.

[oconesa]

I have created the database \"jboss\" with the 3 tables and I have changed the openkm-ds.xml file:

This part was changed:

<!-- OpenKM User Auth -->
<local-tx-datasource>
<jndi-name>OKMAuthDS</jndi-name>
<connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}OKMAuth</connection-url>
<driver-class>org.hsqldb.jdbcDriver</driver-class>
<user-name>sa</user-name>
<password></password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<track-statements/>
<!--<security-domain>HsqlDbRealm</security-domain>-->
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<metadata>
<type-mapping>Hypersonic SQL</type-mapping>
</metadata>
<depends>jboss:service=Hypersonic,database=OKMAuth</depends>
</local-tx-datasource>

<!-- For hsqldb accessed from jboss only, in-process (standalone) mode -->
<mbean code=\"org.jboss.jdbc.HypersonicDatabase\"
name=\"jboss:service=Hypersonic,database=OKMAuth\">
<attribute name=\"Database\">OKMAuth</attribute>
<attribute name=\"InProcessMode\">true</attribute>
</mbean>

by:

<!-- OpenKM User Auth -->
<local-tx-datasource>
<jndi-name>OKMAuthDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/jboss</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>jboss</user-name>
<password>password</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
</local-tx-datasource>


I do not have any ERROR on logs but I can not access with \"admin\" user.
I think that the empty tables are the problem. What values I have to write on the tables to initialize them?

These initial values do not work:

users:
usr_id (admin), usr_pass(admin), usr_email(admin@admin.com), usr_active(1)
roles:
rol_id (AdminRol)
user_role:
ur_user(admin), ur_role(AdminRol)

[pavila]

Sorry, I forgot to post this:

Code: Select all

# INSERT DEFAULT USER / ROLES
INSERT INTO users (usr_id, usr_pass, usr_email, usr_active) VALUES (\'admin\', \'admin\', \'\', true);
INSERT INTO roles (rol_id) VALUES (\'AdminRol\');
INSERT INTO roles (rol_id) VALUES (\'UserRol\');
INSERT INTO user_role (ur_user, ur_role) VALUES (\'admin\', \'AdminRol\');

[oconesa]

Now it works fine!
Thanks

[wape]

I also want to change to MySQL. So I followed all the instructions. How did you solve the following problem:

Within the database MySQL 5.0.51a on debian the value of the column usr_active is represented as \'1\' for the value true.

If I add an user within the user administration, a \'1\' is written into the column usr_active. But when I want to login from the starting page, the following sql statement will be sent to the database which gives no result:

select usr_pass as PASSWD from users where usr_id=\'admin\' and usr_active=\'true\'

...instead of usr_active=true

Did I make any mistake?

Any help would be appreciated. Thank\'s very much in advance!

Peter

[pavila]

Try to change usr_active=true to usr_active=1

[wape]

I found the solution where I have to modify the configuration file.

When I use MYSQL, I have to modify the server/default/conf/login-config.xml. There I modified the SELECT statement from ... and usr_active=\'true\' to ... and usr_active=true.

Now all works.

Best regards,
Peter