Open and export from H2 Database
-
I would like to try to edit my database to get some older data for other purposes and then reduce file size.
Unfortunately I could not find any data when opening the file on another machine.
I tried H2 commandline tool and SQL Workbench on Windows after copying the big mycontroller database to this machine.
Could you give a hint on a setup you might be using for debugging and on the db structure, e.g. where to find the sensor data? -
@ragflyer This post may help you. https://forum.mycontroller.org/topic/140/data-export
-
Thanks, was looking for that. Also Cimba007 explained the steps at https://github.com/mycontroller-org/mycontroller/issues/392
Still it won't work here likely because I can't find the correct version of h2.
I'm still running 0.0.3.Final-SNAPSHOT with db 1.03.03 - 2016 Sep 20I tried to upgrade, but backup fails (400: Bad Request Database backup failed!).
I tried to find mentioned db version, the newest from 1.3 branch (1.3.176) did seem to open the db but showed no user or metrics tables.Can I convert it somehow to the new version and also fix the index issue Cimba007 explained (I'm suffering very slow UI even running the server on a decent workstation)?
logfile:
2018-05-11 10:22:43,754 ERROR [Acme.Utils.ThreadPool(7)-PooledThread: Acme.Serve.Serve$ServeConnection@41ba151f] [org.mycontroller.standalone.db.DataBaseUtils:208] Exception, backup failed! org.h2.jdbc.JdbcSQLException: File corrupted while reading record: "[64043] stream data key:4220 pos:11 remaining:0". Possible solution: use the recovery tool; SQL statement: SCRIPT TO ? COMPRESSION ZIP [90030-176] at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) at org.h2.message.DbException.get(DbException.java:178) at org.h2.message.DbException.get(DbException.java:154) at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:242) at org.h2.index.PageDataNode.getNextPage(PageDataNode.java:233) at org.h2.index.PageDataLeaf.getNextPage(PageDataLeaf.java:400) at org.h2.index.PageDataCursor.nextRow(PageDataCursor.java:95) at org.h2.index.PageDataCursor.next(PageDataCursor.java:53) at org.h2.command.dml.ScriptCommand.generateInsertValues(ScriptCommand.java:403) at org.h2.command.dml.ScriptCommand.query(ScriptCommand.java:301) at org.h2.command.CommandContainer.query(CommandContainer.java:91) at org.h2.command.Command.executeQuery(Command.java:197) at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:193) at org.mycontroller.standalone.db.DataBaseUtils.backupDatabase(DataBaseUtils.java:204) at org.mycontroller.standalone.backup.Backup.backup(Backup.java:60) at org.mycontroller.standalone.api.BackupApi.backupNow(BackupApi.java:182) at org.mycontroller.standalone.api.BackupApi.backupNow(BackupApi.java:193) at org.mycontroller.standalone.api.jaxrs.BackupHandler.backupNow(BackupHandler.java:107) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:139) at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:295) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:249) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:236) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:395) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:202) at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:221) at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:56) at org.jboss.resteasy.plugins.server.tjws.TJWSServletDispatcher.service(TJWSServletDispatcher.java:40) at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:51) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at Acme.Serve.Serve$ServeConnection.runServlet(Serve.java:2328) at Acme.Serve.Serve$ServeConnection.parseRequest(Serve.java:2282) at Acme.Serve.Serve$ServeConnection.run(Serve.java:2054) at Acme.Utils$ThreadPool$PooledThread.run(Utils.java:1402) at java.lang.Thread.run(Thread.java:748) 2018-05-11 10:22:43,757 ERROR [Acme.Utils.ThreadPool(7)-PooledThread: Acme.Serve.Serve$ServeConnection@41ba151f] [org.mycontroller.standalone.api.jaxrs.BackupHandler:109] Error, org.mycontroller.standalone.exceptions.McException: Database backup failed! at org.mycontroller.standalone.backup.Backup.backup(Backup.java:75) at org.mycontroller.standalone.api.BackupApi.backupNow(BackupApi.java:182) at org.mycontroller.standalone.api.BackupApi.backupNow(BackupApi.java:193) at org.mycontroller.standalone.api.jaxrs.BackupHandler.backupNow(BackupHandler.java:107) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:139) at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:295) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:249) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:236) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:395) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:202) at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:221) at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:56) at org.jboss.resteasy.plugins.server.tjws.TJWSServletDispatcher.service(TJWSServletDispatcher.java:40) at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:51) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at Acme.Serve.Serve$ServeConnection.runServlet(Serve.java:2328) at Acme.Serve.Serve$ServeConnection.parseRequest(Serve.java:2282) at Acme.Serve.Serve$ServeConnection.run(Serve.java:2054) at Acme.Utils$ThreadPool$PooledThread.run(Utils.java:1402) at java.lang.Thread.run(Thread.java:748)
-