Open Source Document Management System | OpenKM

Because information matters
https://forum.openkm.com/

How to replace HSQL databse with MySQL in OpenKM 6.2

https://forum.openkm.com/viewtopic.php?t=9204

Page 1 of 1

How to replace HSQL databse with MySQL in OpenKM 6.2

PostPosted:Wed Jan 23, 2013 1:37 pm
by Muhammad Imran
Hi,
I have installed OpenKM 6.2 in on windows 7. It is working well with its embedded database HSQLDB. Now I want to configure to replace HSQLDB with MySQL.I have read the wiki http://wiki.openkm.com/index.php/MySQL_-_OpenKM_6.2 but unfortunately I remained unable to understand it.
Please can any one tell me the steps to replace HSQLDB with MySQL in OpenKM 6.2 .
Thanks in advance...!

Re: How to replace HSQL databse with MySQL in OpenKM 6.2

PostPosted:Thu Jan 24, 2013 4:29 pm
by jllort
You should export all repository nodes, users and roles tables ( these similar as migration from 5.1 to 6.2 ) and then configure openkm to run with mysql.
I suggest :
1- backup
2- export data from administration ( export tool )
3- configure to run with mysql
4- start and if you get running with mysql then import data

Re: How to replace HSQL databse with MySQL in OpenKM 6.2

PostPosted:Wed Jan 30, 2013 8:29 am
by Muhammad Imran
Successfully replace HSQLDB with MySQL in OpenKM 6.2 in just five simple steps

1. Export all your data in .zip file

2. Open OpenKM.cfg File in an Editor:
Replace its contents with the following 2 lines
Code: Select all
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
hibernate.hbm2ddl=create
3. Run your MySQL server(localhost)
Copy and paste the following lines in SQL query tab and run it.
Code: Select all
DROP DATABASE IF EXISTS okmdb;
CREATE DATABASE okmdb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
CREATE USER openkm@localhost IDENTIFIED BY '*secret*';
GRANT ALL ON okmdb.* TO openkm@localhost WITH GRANT OPTION;
4. Open server.xml File in an Editor:
Copy and paset the followning lines of code in server.xml
Code: Select all
<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
                maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"
                username="openkm" password="*secret*" driverClassName="com.mysql.jdbc.Driver"
                url="jdbc:mysql://localhost:3306/okmdb?autoReconnect=true&useUnicode=true&characterEncoding=UTF8"/>
Remove or comment the following lines of code in server.xml
Code: Select all
<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
                maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1 from INFORMATION_SCHEMA.SYSTEM_USERS"
                username="sa" password="" driverClassName="org.hsqldb.jdbcDriver"
                url="jdbc:hsqldb:${catalina.base}/repository/okmdb"/>
5. Import your data from .zip file

Enjoy OpenKM 6.2 with MySQL.....!

Re: How to replace HSQL databse with MySQL in OpenKM 6.2

PostPosted:Thu Jan 31, 2013 10:16 pm
by jllort
For OpenKM with few usage could be right, but for users who are using it for a long of time at least I got minimal suggestions:
- Better install other openkm ( tomcat ) in other path and do not remove older installation ( it's good option to prevent lose information ).
- Better if you export and import data from administration, because you can export with metadata, user credentials etc..
- I suggest backup your users and roles if you have defined and property groups.

Re: How to replace HSQL databse with MySQL in OpenKM 6.2

PostPosted:Tue Jun 07, 2016 3:50 pm
by chikeo
I would like to point out something very important.

It appears that this content area converts xml settings values to Camel case.

Please kindly ensure that the value of the driverClassName and url properties are in lowercase. This could have saved me some hours while
implementing OpenKM Community Edition on a Fedora Core 23 Server.
Code: Select all
<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
            maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"
            username="openkm" password="*secret*" driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/okmdb?autoReconnect=true&useUnicode=true&characterEncoding=UTF8"/>
It manifested in an error indicating that either there was no MySql driver or that the driver wasn't properly configured. The catalina.log entry can be shown below:
Code: Select all
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.MySQL.jdbc.Driver'
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1429)
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.hibernate.connection.DatasourceConnectionProvider.getConnection(DatasourceConnectionProvider.java:92)
        at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:113)
        at org.hibernate.cfg.Configuration.buildSettingsInternal(Configuration.java:2863)
        at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2859)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1870)
        at com.openkm.dao.HibernateUtil.getSessionFactory(HibernateUtil.java:180)
        at com.openkm.dao.HibernateUtil.getSessionFactory(HibernateUtil.java:91)
        at com.openkm.servlet.RepositoryStartupServlet.init(RepositoryStartupServlet.java:100)
        at javax.servlet.GenericServlet.init(GenericServlet.java:158)
        at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1284)
        at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1197)
        at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1087)
        at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:5266)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5554)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:652)
        at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1090)
        at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1900)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.ClassNotFoundException: com.MySQL.jdbc.Driver
        at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1720)
        at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1571)
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1420)
        ... 27 more
You can find the proper case that you can copy, paste and use without errors at this page (http://wiki.openkm.com/index.php/MySQL-OpenKM_6.2).

Thank you very much.

Regards,

Chike

Muhammad Imran wrote:Successfully replace HSQLDB with MySQL in OpenKM 6.2 in just five simple steps

1. Export all your data in .zip file

2. Open OpenKM.cfg File in an Editor:
Replace its contents with the following 2 lines
Code: Select all
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
hibernate.hbm2ddl=create
3. Run your MySQL server(localhost)
Copy and paste the following lines in SQL query tab and run it.
Code: Select all
DROP DATABASE IF EXISTS okmdb;
CREATE DATABASE okmdb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
CREATE USER openkm@localhost IDENTIFIED BY '*secret*';
GRANT ALL ON okmdb.* TO openkm@localhost WITH GRANT OPTION;
4. Open server.xml File in an Editor:
Copy and paset the followning lines of code in server.xml
Code: Select all
<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
                maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"
                username="openkm" password="*secret*" driverClassName="com.MySQL.jdbc.Driver"
                url="jdbc:MySQL://localhost:3306/okmdb?autoReconnect=true&useUnicode=true&characterEncoding=UTF8"/>
Remove or comment the following lines of code in server.xml
Code: Select all
<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
                maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1 from INFORMATION_SCHEMA.SYSTEM_USERS"
                username="sa" password="" driverClassName="org.hsqldb.jdbcDriver"
                url="jdbc:hsqldb:${catalina.base}/repository/okmdb"/>
5. Import your data from .zip file

Enjoy OpenKM 6.2 with MySQL.....!

Re: How to replace HSQL databse with MySQL in OpenKM 6.2

PostPosted:Fri Aug 05, 2016 8:27 am
by han
I have the same question.

And I did every step as above.

I still can't load my login page.

My catalina.log below:
Code: Select all
2016-08-05 16:01:03,338 [http-bio-0.0.0.0-8080-exec-10] ERROR org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/OpenKM].[jsp]- Servlet.service() for servlet [jsp] in context with path [/OpenKM] threw exception [org.apache.jasper.JasperException: javax.servlet.ServletException: com.openkm.core.DatabaseException: Cannot open connection] with root cause
java.lang.ClassNotFoundException: com.MySQL.jdbc.Driver
	at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1720)
	at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1571)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1420)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
	at org.hibernate.connection.DatasourceConnectionProvider.getConnection(DatasourceConnectionProvider.java:92)
	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
	at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
	at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1700)
	at org.hibernate.loader.Loader.doQuery(Loader.java:801)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
	at org.hibernate.loader.Loader.doList(Loader.java:2542)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
	at org.hibernate.loader.Loader.list(Loader.java:2271)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:459)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:365)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
	at com.openkm.dao.LanguageDAO.findAll(LanguageDAO.java:104)
	at com.openkm.dao.LanguageDAO.findAll(LanguageDAO.java:78)
	at org.apache.jsp.login_005fdesktop_jsp._jspService(login_005fdesktop_jsp.java:167)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:395)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:339)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:748)
	at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:604)
	at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:543)
	at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:954)
	at org.apache.jsp.login_jsp._jspService(login_jsp.java:63)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:395)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:339)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:311)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:101)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:182)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:173)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Did I miss something?
And what can I do with it?
Or I can just use hsql as default?

Re: How to replace HSQL databse with MySQL in OpenKM 6.2

PostPosted:Sat Aug 06, 2016 2:54 pm
by jllort
Seems in your tomcat/lib the mysql jdbc driver is not present, you must download and restart the application. You can get it from here https://dev.mysql.com/downloads/connector/j/

Re: How to replace HSQL databse with MySQL in OpenKM 6.2

PostPosted:Tue Aug 09, 2016 9:51 am
by han
Thanks first,

I tried these ways these days.

I still can't solve it.

And I reinstall my VM enviroment, and try again.

It run success.

But my openoffice is off... , I can't use preview now.
(openKM can't connect and start openoffice to convert office file to pdf)
(72.33 KiB) Downloaded 263 times

Re: How to replace HSQL databse with MySQL in OpenKM 6.2

PostPosted:Thu Aug 11, 2016 12:54 am
by han
My problem is solved.

My enviroment variable "PATH" in Windows:
Code: Select all
%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\MySQL\MySQL Fabric 1.5 & MySQL Utilities 1.5\;C:\Program Files (x86)\MySQL\MySQL Fabric 1.5 & MySQL Utilities 1.5\Doctrine extensions for PHP\;C:\openkm\tomcat\lib\sigar
I found that, in my catalina.log the path only load
Code: Select all
"%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\MySQL\MySQL Fabric 1.5".
The part that after the symbol "&" is lost.

Then I edit it to %
Code: Select all
SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\openkm\tomcat\lib\sigar;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\MySQL\MySQL Fabric 1.5 & MySQL Utilities 1.5\;C:\Program Files (x86)\MySQL\MySQL Fabric 1.5 & MySQL Utilities 1.5\Doctrine extensions for PHP\
Everything goes well!
Many thanks, jllort. :D

Re: How to replace HSQL databse with MySQL in OpenKM 6.2

PostPosted:Sat Aug 13, 2016 8:14 am
by jllort
Some additional considerations about "com.MySQL.jdbc.Driver" class not found. Take in consideration java is a case sensitive, and the package com.MySQL.jdbc.Driver not exists into the jar library the correct package name is "com.mysql.jdbc.Driver". If you have found in our documentation bad written, please tell us and will correct it.
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited