How to replace HSQL databse with MySQL in OpenKM 6.2

[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...!

[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

[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.....!

[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.

[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

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.....!

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

[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/

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

catalina.rar

(72.33 KiB) Downloaded 243 times

[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.

[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.