Page 1 of 1
From HSQLDB to MySQL
PostPosted:Tue Dec 16, 2008 3:29 pm
by oconesa
Is it possible to change from Hsqldb to mysql in OpenKM 3.0? What I need to do?
Re:From HSQLDB to MySQL
PostPosted:Tue Dec 16, 2008 4:18 pm
by 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.
Re:From HSQLDB to MySQL
PostPosted:Tue Dec 16, 2008 5:03 pm
by 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)
Re:From HSQLDB to MySQL
PostPosted:Wed Dec 17, 2008 12:06 pm
by 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?
Re:From HSQLDB to MySQL
PostPosted:Wed Dec 17, 2008 1:03 pm
by 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.
Re:From HSQLDB to MySQL
PostPosted:Fri Dec 19, 2008 10:05 am
by 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.
Re:From HSQLDB to MySQL
PostPosted:Mon Dec 22, 2008 10:34 am
by 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)
Re:From HSQLDB to MySQL
PostPosted:Mon Dec 22, 2008 11:48 am
by 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\');
Re:From HSQLDB to MySQL
PostPosted:Mon Dec 22, 2008 4:46 pm
by oconesa
Now it works fine!
Thanks
Re:From HSQLDB to MySQL
PostPosted:Tue Jan 06, 2009 10:25 pm
by 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
Re:From HSQLDB to MySQL
PostPosted:Fri Jan 09, 2009 10:26 am
by pavila
Try to change usr_active=true to usr_active=1
Re:From HSQLDB to MySQL
PostPosted:Mon Jan 26, 2009 8:15 pm
by 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