• Recent
    • Tags
    • Popular
    • Register
    • Login

    Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.

    Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website Mango 5 Documentation Website

    Migrate from SQL to NoSQL

    User help
    3
    9
    1.1k
    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.
    • P
      psysak
      last edited by

      Just finally purchased a license for a full Mango instance and would like to migrate over to NoSQL. Are there any guides to do that?

      1 Reply Last reply Reply Quote 0
      • JoelHaggarJ
        JoelHaggar
        last edited by

        https://help.infiniteautomation.com/migrating-into-nosql/

        1 Reply Last reply Reply Quote 0
        • P
          psysak
          last edited by

          Of course! Sorry about that, and thanks Joel

          1 Reply Last reply Reply Quote 0
          • P
            psysak
            last edited by psysak

            OK let me ask this. There are a couple of articles there, the one you referenced and this one
            https://help.infiniteautomation.com/database-conversions/

            So if I understand correctly, H2 is the "configuration" db and then the historical db is the one which is stored in mysql and the one I am attempting to migrate now?

            Also that little db icon doesn't exist anymore on the menu.

            1 Reply Last reply Reply Quote 0
            • P
              psysak
              last edited by

              OK!! I don't have the module installed, that would do it!

              1 Reply Last reply Reply Quote 0
              • P
                psysak
                last edited by psysak

                Finally got it started but a fw seconds in this happened

                org.h2.jdbc.JdbcSQLException: IO Exception: "java.io.EOFException"; "/home/ubuntu/mango/databases/mah2.h2.db"; SQL statement: select pv.dataPointId, pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceMessage from pointValues pv left join pointValueAnnotations pva on pv.id = pva.pointValueId [90031-196]
File corrupted while reading record: "page[136688] b-tree leaf table:78 entries:161". Possible solution: use the recovery tool; SQL statement: COMMIT [90030-196]

                And during startup I get this

                INFO  2018-02-06T09:54:10,202 (com.serotonin.m2m2.Lifecycle.initialize:218) - Initializing Spring Object Mapper
INFO  2018-02-06T09:54:11,248 (com.serotonin.m2m2.Lifecycle.loadLic:523) - Checking license...
INFO  2018-02-06T09:54:11,532 (com.serotonin.m2m2.db.H2Proxy.initializeImpl:42) - Initializing H2 connection manager
FATAL 2018-02-06T09:54:14,143 (com.serotonin.m2m2.db.AbstractDatabaseProxy.initialize:160) - Unable to connect to database of type H2
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.h2.jdbc.JdbcSQLException: File corrupted while reading record: "index not found 74". Possible solution: use the recovery tool [90030-196]
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:625) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:690) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:732) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at com.serotonin.db.spring.ExtendedJdbcTemplate.queryForObject(ExtendedJdbcTemplate.java:39) ~[mango-3.3.0.jar:?]
        at com.serotonin.db.spring.ExtendedJdbcTemplate.queryForObject(ExtendedJdbcTemplate.java:63) ~[mango-3.3.0.jar:?]
        at com.serotonin.m2m2.db.H2Proxy.tableExists(H2Proxy.java:256) ~[mango-3.3.0.jar:?]
        at com.serotonin.m2m2.db.AbstractDatabaseProxy.newDatabaseCheck(AbstractDatabaseProxy.java:178) ~[mango-3.3.0.jar:?]
        at com.serotonin.m2m2.db.AbstractDatabaseProxy.initialize(AbstractDatabaseProxy.java:81) [mango-3.3.0.jar:?]
        at com.serotonin.m2m2.Lifecycle.databaseInitialize(Lifecycle.java:667) [ma-priv-3.3.0.jar:?]
        at com.serotonin.m2m2.Lifecycle.initialize(Lifecycle.java:289) [ma-priv-3.3.0.jar:?]
        at com.serotonin.m2m2.Main.main(Main.java:127) [ma-priv-3.3.0.jar:?]
Caused by: org.h2.jdbc.JdbcSQLException: File corrupted while reading record: "index not found 74". Possible solution: use the recovery tool [90030-196]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.getPage(PageStore.java:769) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:232) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.index.PageDataNode.getRowCount(PageDataNode.java:289) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.index.PageDataIndex.<init>(PageDataIndex.java:87) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.table.RegularTable.<init>(RegularTable.java:83) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.addMeta(PageStore.java:1697) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.readMetaData(PageStore.java:1628) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.recover(PageStore.java:1406) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.openExisting(PageStore.java:368) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.open(PageStore.java:289) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Database.getPageStore(Database.java:2490) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Database.open(Database.java:697) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Database.openDatabase(Database.java:276) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Database.<init>(Database.java:270) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Engine.openSession(Engine.java:64) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Engine.openSession(Engine.java:176) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Engine.createSessionAndValidate(Engine.java:154) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Engine.createSession(Engine.java:137) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Engine.createSession(Engine.java:27) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:354) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:116) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:100) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.Driver.connect(Driver.java:69) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbcx.JdbcDataSource.getJdbcConnection(JdbcDataSource.java:188) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbcx.JdbcDataSource.getXAConnection(JdbcDataSource.java:351) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbcx.JdbcDataSource.getPooledConnection(JdbcDataSource.java:383) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbcx.JdbcConnectionPool.getConnectionNow(JdbcConnectionPool.java:226) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbcx.JdbcConnectionPool.getConnection(JdbcConnectionPool.java:198) ~[h2-1.4.196.jar:1.4.196]
        at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        ... 12 more
ERROR 2018-02-06T09:54:14,155 (com.serotonin.m2m2.Main.main:131) - Error during initialization
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.h2.jdbc.JdbcSQLException: File corrupted while reading record: "index not found 74". Possible solution: use the recovery tool [90030-196]
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:625) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:690) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:732) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at com.serotonin.db.spring.ExtendedJdbcTemplate.queryForObject(ExtendedJdbcTemplate.java:39) ~[mango-3.3.0.jar:?]
        at com.serotonin.db.spring.ExtendedJdbcTemplate.queryForObject(ExtendedJdbcTemplate.java:63) ~[mango-3.3.0.jar:?]
        at com.serotonin.m2m2.db.H2Proxy.tableExists(H2Proxy.java:256) ~[mango-3.3.0.jar:?]
        at com.serotonin.m2m2.db.AbstractDatabaseProxy.newDatabaseCheck(AbstractDatabaseProxy.java:178) ~[mango-3.3.0.jar:?]
        at com.serotonin.m2m2.db.AbstractDatabaseProxy.initialize(AbstractDatabaseProxy.java:81) ~[mango-3.3.0.jar:?]
        at com.serotonin.m2m2.Lifecycle.databaseInitialize(Lifecycle.java:667) ~[ma-priv-3.3.0.jar:?]
        at com.serotonin.m2m2.Lifecycle.initialize(Lifecycle.java:289) ~[ma-priv-3.3.0.jar:?]
        at com.serotonin.m2m2.Main.main(Main.java:127) [ma-priv-3.3.0.jar:?]
Caused by: org.h2.jdbc.JdbcSQLException: File corrupted while reading record: "index not found 74". Possible solution: use the recovery tool [90030-196]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.getPage(PageStore.java:769) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:232) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.index.PageDataNode.getRowCount(PageDataNode.java:289) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.index.PageDataIndex.<init>(PageDataIndex.java:87) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.table.RegularTable.<init>(RegularTable.java:83) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.addMeta(PageStore.java:1697) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.readMetaData(PageStore.java:1628) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.recover(PageStore.java:1406) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.openExisting(PageStore.java:368) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.store.PageStore.open(PageStore.java:289) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Database.getPageStore(Database.java:2490) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Database.open(Database.java:697) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Database.openDatabase(Database.java:276) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Database.<init>(Database.java:270) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Engine.openSession(Engine.java:64) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Engine.openSession(Engine.java:176) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Engine.createSessionAndValidate(Engine.java:154) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Engine.createSession(Engine.java:137) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Engine.createSession(Engine.java:27) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:354) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:116) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:100) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.Driver.connect(Driver.java:69) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbcx.JdbcDataSource.getJdbcConnection(JdbcDataSource.java:188) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbcx.JdbcDataSource.getXAConnection(JdbcDataSource.java:351) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbcx.JdbcDataSource.getPooledConnection(JdbcDataSource.java:383) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbcx.JdbcConnectionPool.getConnectionNow(JdbcConnectionPool.java:226) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbcx.JdbcConnectionPool.getConnection(JdbcConnectionPool.java:198) ~[h2-1.4.196.jar:1.4.196]
        at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
        ... 12 more
INFO  2018-02-06T09:54:14,156 (com.serotonin.m2m2.Lifecycle.terminate:382) - Mango Lifecycle terminating...
INFO  2018-02-06T09:54:14,168 (com.infiniteautomation.nosql.MangoNoSqlProxy.shutdown:115) - Terminating NoSQL Batch Write Manager.
INFO  2018-02-06T09:54:14,181 (com.serotonin.m2m2.Lifecycle.terminate:491) - Mango Lifecycle terminated.

                Hooray! :)

                1 Reply Last reply Reply Quote 0
                • P
                  psysak
                  last edited by

                  I restored a backup from this morning and removed the NoSQL databases module, system is running for now.

                  1 Reply Last reply Reply Quote 0
                  • phildunlapP
                    phildunlap
                    last edited by

                    Not sure what happened there. When we attempted the migration together, it worked without issue.

                    1 Reply Last reply Reply Quote 0
                    • P
                      psysak
                      last edited by

                      There is an old acronym, PEBKAC....

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