• Migration from Hypersonic to MySQL or Postgre

  • 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.
 #19165  by fonnerk
 
I have a small installation running that I would like to migrate from the built in database to an external Postgre Database? I have access to all the documentation for setting up on Postgre (which is what I prefer), however I did not see any documentation on how best to migrate an existing repository from the built in database to a MySQL or Postgre Database. Is there an existing document or forum post that I missed? How should I tackle this?

Thanks,
Kevin
 #19182  by fonnerk
 
I read that page before and I read over that page again just to be sure... maybe I'm missing something. I see how to setup a server with a database (that's not an issue) but I don't see how to migrate my data from the embedded database that openkm comes with to postgresql or mysql database. What if I have an existing installation and want to migrate to an architecture with a postgresql or mysql database database?

Thanks,
Kevin
 #19183  by techexpress
 
OK , I think you need to first connect to the Hypersonic of OpenKM , export Tables and import them in MySQL
After change parameter of OpenKM connection , adapt tables
I searching to understand how backup the Hypersonic server, because I do not have to run the ver 6.2 version with My SQL
source : http://hsqldb.org/doc/guide/ch01.html#N100B7
To run the DatabaseManager go to the opt/openkm-6.2.0-community/tomcat/lib folder
and run
Code: Select all
java -cp hsqldb.jar org.hsqldb.util.DatabaseManager
For connection information user name is sa and no password
For URL I take the info in /opt/openkm-6.2.0-community/tomcat/conf/server.xml
Code: Select all
   username="sa" password="" driverClassName="org.hsqldb.jdbcDriver"
            url="jdbc:hsqldb:${catalina.base}/repository/okmdb"/>
catalina.base is path of OpenKM (Ex: opt/openkm-6.2.0-community/tomcat)
okmdb is the name of database and all these files

so url must be jdbc:hsqldb:file:/opt/openkm-6.2.0-community/tomcat/okmdb
I had to stop catalina.sh stop because fil was lock , I think if we can use connection direct instead by files we should connect to database without stopping OpenKM

Please leave to us your feedback , it will be very apprecied

Some more info for connection URL http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html
 #19192  by jllort
 
The only way for doing this kind of migration is exporting repository from openkm administration export tools, then configure openkm to use mysql or other database and import repository.

You can extract some users, etc... before switch openkm to mysql or other database ( is good idea configure new openkm and maintaining older installation without any change to preventing possible mistakes during migration procedure ).
 #19324  by fonnerk
 
Thanks everybody for you help... I was hoping that a repository export and import would work... That seems the easiest. I am working on trying that now but one problem I have noticed right off the bat is that multiple versions of files do not seem to come across in an export and import. I read in another forum post that version history was now supported in one of the 5.1.x versions. I just upgraded to 5.1.10 to be sure but it still seems like during an import I am only getting one version of each file from the old repository (not the previous versions of a file or the history information). Is there something I am missing or are old versions of files not handled by the export or import of a repository??? Or is this going to be in 5.1.11?

Kevin
 #19377  by jllort
 
I think no, it will continues being at integration.openkm.com as a patched version. Really the actual objective is migrate to 6.x because future migrations procedure will be more easy than what we have done until now.
 #19446  by fonnerk
 
Ok... I have proceeded forward with my migration.. I updated a test server to a snapshot 5.1.11 running on an embedded database. Exported the repository. Then I imported the repository with metadata and history checked... I get an error on the import that looks like the new database could not handle the length of some of the data that the old system could handle... Below is the error... How do you think I should proceed?
Code: Select all
2012-11-20 13:11:06,574 [http-bio-0.0.0.0-8080-exec-4] WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 22001
2012-11-20 13:11:06,574 [http-bio-0.0.0.0-8080-exec-4] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: value too long for type character varying(2048)
2012-11-20 13:11:06,574 [http-bio-0.0.0.0-8080-exec-4] ERROR com.openkm.util.impexp.RepositoryImporter - Could not execute JDBC batch update
com.openkm.core.DatabaseException: Could not execute JDBC batch update
        at com.openkm.util.impexp.metadata.DbMetadataAdapter.importWithMetadata(DbMetadataAdapter.java:449)
        at com.openkm.util.impexp.RepositoryImporter.importDocumentsHelper(RepositoryImporter.java:164)
        at com.openkm.util.impexp.RepositoryImporter.importDocumentsHelper(RepositoryImporter.java:192)
        at com.openkm.util.impexp.RepositoryImporter.importDocumentsHelper(RepositoryImporter.java:192)
        at com.openkm.util.impexp.RepositoryImporter.importDocumentsHelper(RepositoryImporter.java:192)
        at com.openkm.util.impexp.RepositoryImporter.importDocuments(RepositoryImporter.java:87)
        at org.apache.jsp.admin.repository_005fimport_jsp._jspService(repository_005fimport_jsp.java:168)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
        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:243)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:999)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:565)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:309)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:662)
Caused by: org.hibernate.exception.DataException: Could not execute JDBC batch update
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:102)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:268)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
        at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383)
        at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)
        at com.openkm.dao.HibernateUtil.commit(HibernateUtil.java:317)
        at com.openkm.util.impexp.metadata.DbMetadataAdapter.importWithMetadata(DbMetadataAdapter.java:446)
        ... 51 more
Caused by: java.sql.BatchUpdateException: Batch entry 0 /* insert com.openkm.dao.bean.NodeNote */ insert into OKM_NODE_NOTE (NNT_AUTHOR, NNT_CREATED, NNT_PARENT, NNT_TEXT, NNT_UUID) values ('kevin', '2012-01-04 00:35:18.000000 -05:00:00', '59280bbc-b662-47ad-9415-158933b9eb67', '<span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: ''Times New Roman''; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><span class="Apple-style-span" style="font-family: sans-serif; font-size: 13px; text-align: -webkit-center; "><table class="boldtable" style="font-family: sans-serif; font-size: 12px; " align="center" border="1" cellpadding="5" cellspacing="0" width="600"><tbody><tr bgcolor="#eab060"><td style="font-family: sans-serif; font-size: 12px; "><div align="center"><font color="white" face="Arial, Helvetica, sans-serif"><b>Your request number is A105801WW</b></font></div></td></tr><tr><td style="font-family: sans-serif; font-size: 12px; "><br><b>Your reservation request has been received and is currently pending confirmation. Please make a record of this request number for future use. If you entered an email address, you should expect an email regarding the status of your reservation. To review the status of your reservation, please click "Check on a current Reservation" and enter your request number.<span class="Apple-converted-space">&nbsp;</span><br><br><table class="boldtablewhite" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); " align="center" bgcolor="#ffffff" cellpadding="3" cellspacing="1" width="360"><tbody><tr><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); " width="60"><b>Room</b></td><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); " width="300">3 - No. 3 (King High Poster)</td></tr><tr><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); "><b>Dates</b></td><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); ">6/11/2012 - 6/13/2012</td></tr></tbody></table><br><table class="boldtablewhite" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); " align="center" bgcolor="#ffffff" border="0" cellpadding="3" cellspacing="1" width="360"><tbody><tr><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); " width="300"><div><font color="#FFFFFF"><b>Charge Type</b></font></div></td><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); " width="60"><div align="center"><font color="#FFFFFF"><b><font size="2" face="Arial, Helvetica, sans-serif">Price</font></b></font></div></td></tr><tr><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); ">Room Charge</td><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); "><div align="right">$638.00</div></td></tr><tr><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); " align="right"><b>Sub Total:</b></td><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); "><div align="right"><b>$638.00</b></div></td></tr><tr><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); " align="right">Lodging Tax:</td><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); "><div align="right">$38.28</div></td></tr><tr><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); " align="right">Sales Tax:</td><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); "><div align="right">$36.37</div></td></tr><tr><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); " align="right"><b><font size="2" face="Arial, Helvetica, sans-serif">Total Cost:</font></b></td><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); "><div align="right"><b><font size="2" face="Arial, Helvetica, sans-serif">$712.65</font></b></div></td></tr><tr><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); " align="right">Amount Paid:</td><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); "><div align="right"><b>$712.65</b></div></td></tr><tr><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); " align="right"><b><font size="2" face="Arial, Helvetica, sans-serif">Balance:</font></b></td><td class="boxoff" style="font-family: sans-serif; font-size: 12px; color: rgb(255, 255, 255); padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; background-color: rgb(234, 176, 96); "><div align="right"><b><font size="2" face="Arial, Helvetica, sans-serif">$0.00</font></b></div></td></tr></tbody></table></b></td></tr></tbody></table></span></span>', '0871dc0d-b491-424f-a6a1-74a6adf23c87') was aborted.  Call getNextException to see the cause.
        at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2746)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1887)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2893)
        at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
        at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
        ... 60 more
 #19482  by jllort
 
I think in 6.2.1 problem with postgres was already solved. Only to be sure try update to integration.openkm.com it's possible has not been included in release, give us your feedback if problem persist after update or not.
 #19514  by pavila
 
The problem is the note field size which is limited to 2048 chars. I need to change to another database field type to avoid this limitation.
 #19524  by fonnerk
 
That's what I figured but I had not had the time to setup another test instance from integration to be sure... Are you planning on making the database change? If so... into integration sometime soon?
 #19555  by fonnerk
 
I did some checking and PostgreSQL does not have a 2048 character limit on varchars unless you specifically set it to that size. You would not have to change the field type (unless your using a database framework that is defining that size for some reason).

I was able to alter the column to remove a set size as postgresql allows...

ALTER TABLE OKM_NODE_NOTE ALTER COLUMN nnt_text TYPE varchar;

After that modification, the import worked and OpenKM seems to be dealing with the notes fine. How are you planning on fixing the problem? I would rather not do anything to my database that is going to break compatibility with you down the road. Do you think this type of change is ok to proceed with?

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.