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?


  • ADMIN



  • 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 20

    I 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)
    
    seant100 created this issue in mycontroller-org/mycontroller

    open Slow graphs #392


  • ADMIN

    @ragflyer I have fixed @cimba007 solution. yet to fix it fully.
    Your log says something like your trying on the corrupted database. can you copy the running database and try again. Kindly do a backup before any actions.
    I will try to publish json API. We can use it anywhere.


 

Suggested Topics

11
Online

392
Users

365
Topics

2.2k
Posts