• Categories
    • Recent
    • Tags
    • Popular
    • Register
    • Login

    Open and export from H2 Database

    Scheduled Pinned Locked Moved Developers Zone
    4 Posts 2 Posters 1.4k Views 1 Watching
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • R Offline
      ragflyer
      last edited by

      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?

      jkandasaJ 1 Reply Last reply Reply Quote 0
      • jkandasaJ Offline
        jkandasa @ragflyer
        last edited by

        @ragflyer This post may help you. https://forum.mycontroller.org/topic/140/data-export

        1 Reply Last reply Reply Quote 0
        • R Offline
          ragflyer
          last edited by jkandasa

          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)
          jkandasaJ 1 Reply Last reply Reply Quote 0
          • jkandasaJ Offline
            jkandasa @ragflyer
            last edited by

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

            1 Reply Last reply Reply Quote 0
            • First post
              Last post

            0

            Online

            587

            Users

            529

            Topics

            3.4k

            Posts
            Copyright © 2015-2025 MyController.org | Contributors | Localization