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

  • Hello,

    Mango crashed last week and won't restart.

    In the ma.log I found the error:

    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 93". Possible solution: use the recovery tool [90030-180]

    Based on what I found in the forum, I did the following:

    1. Stop Mango.
    2. mv Mango/databases/mah2.h2.db Mango/databases/trouble-mah2.h2.db
    3. Start Mango on a clean database, login admin/admin
    4. Use the system settings H2 Backup section to restore a database backup.

    When trying step 4, I get SQL restore failed. I have tried several different backups.

    The ma.log shows:

    INFO  2019-08-26T04:35:51,338 (com.serotonin.m2m2.rt.maint.work.DatabaseBackupWorkItem.restore:319) - Attempting to restore database backup from: /opt/mango/backup/core-database-H2-Aug-23-2019_045635.zip 
    ERROR 2019-08-26T04:36:18,422 (com.serotonin.m2m2.rt.maint.work.DatabaseBackupWorkItem.restore:360) - StatementCallback; uncategorized SQLException for SQL [RUNSCRIPT FROM '/opt/mango/backup/core-database-H2-Aug-23-2019_045635.zip' COMPRESSION ZIP; ]; SQL state [90028]; error code [90028]; IO Exception: "java.io.EOFException: Unexpected end of ZLIB input stream"; SQL statement:
    RUNSCRIPT FROM '/opt/mango/backup/core-database-H2-Aug-23-2019_045635.zip' COMPRESSION ZIP; [90028-199]; nested exception is org.h2.jdbc.JdbcSQLNonTransientException: IO Exception: "java.io.EOFException: Unexpected end of ZLIB input stream"; SQL statement:
    RUNSCRIPT FROM '/opt/mango/backup/core-database-H2-Aug-23-2019_045635.zip' COMPRESSION ZIP; [90028-199] 
    org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [RUNSCRIPT FROM '/opt/mango/backup/core-database-H2-Aug-23-2019_045635.zip' COMPRESSION ZIP; ]; SQL state [90028]; error code [90028]; IO Exception: "java.io.EOFException: Unexpected end of ZLIB input stream"; SQL statement:
    RUNSCRIPT FROM '/opt/mango/backup/core-database-H2-Aug-23-2019_045635.zip' COMPRESSION ZIP; [90028-199]; nested exception is org.h2.jdbc.JdbcSQLNonTransientException: IO Exception: "java.io.EOFException: Unexpected end of ZLIB input stream"; SQL statement:
    RUNSCRIPT FROM '/opt/mango/backup/core-database-H2-Aug-23-2019_045635.zip' COMPRESSION ZIP; [90028-199]
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1442) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:387) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:417) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at com.serotonin.m2m2.db.H2Proxy.runScript(H2Proxy.java:280) ~[mango-3.6.4.jar:?]
    	at com.serotonin.m2m2.rt.maint.work.DatabaseBackupWorkItem.restore(DatabaseBackupWorkItem.java:326) ~[mango-3.6.4.jar:?]
    	at com.serotonin.m2m2.module.definitions.actions.SqlRestoreActionDefinition$Action.runImpl(SqlRestoreActionDefinition.java:82) ~[mango-3.6.4.jar:?]
    	at com.serotonin.m2m2.util.timeout.SystemActionTask.run(SystemActionTask.java:74) ~[mango-3.6.4.jar:?]
    	at com.serotonin.timer.Task.runTask(Task.java:179) ~[mango-3.6.4.jar:?]
    	at com.serotonin.timer.TaskWrapper.run(TaskWrapper.java:23) ~[mango-3.6.4.jar:?]
    	at com.serotonin.timer.OrderedThreadPoolExecutor$OrderedTaskCollection.run(OrderedThreadPoolExecutor.java:336) ~[mango-3.6.4.jar:?]
    	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_191]
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_191]
    	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_191]
    Caused by: org.h2.jdbc.JdbcSQLNonTransientException: IO Exception: "java.io.EOFException: Unexpected end of ZLIB input stream"; SQL statement:
    RUNSCRIPT FROM '/opt/mango/backup/core-database-H2-Aug-23-2019_045635.zip' COMPRESSION ZIP; [90028-199]
    	at org.h2.message.DbException.getJdbcSQLException(DbException.java:502) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.message.DbException.get(DbException.java:194) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.message.DbException.convertIOException(DbException.java:390) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.util.ScriptReader.readStatement(ScriptReader.java:101) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.command.dml.RunScriptCommand.update(RunScriptCommand.java:54) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.command.CommandContainer.update(CommandContainer.java:133) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.command.Command.executeUpdate(Command.java:267) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:233) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205) ~[h2-1.4.199.jar:1.4.199]
    	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:408) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:375) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	... 11 more
    Caused by: java.io.EOFException: Unexpected end of ZLIB input stream
    	at java.util.zip.InflaterInputStream.fill(InflaterInputStream.java:240) ~[?:1.8.0_191]
    	at java.util.zip.InflaterInputStream.read(InflaterInputStream.java:158) ~[?:1.8.0_191]
    	at java.util.zip.ZipInputStream.read(ZipInputStream.java:194) ~[?:1.8.0_191]
    	at sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:284) ~[?:1.8.0_191]
    	at sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:326) ~[?:1.8.0_191]
    	at sun.nio.cs.StreamDecoder.read(StreamDecoder.java:178) ~[?:1.8.0_191]
    	at java.io.InputStreamReader.read(InputStreamReader.java:184) ~[?:1.8.0_191]
    	at java.io.BufferedReader.fill(BufferedReader.java:161) ~[?:1.8.0_191]
    	at java.io.BufferedReader.read1(BufferedReader.java:212) ~[?:1.8.0_191]
    	at java.io.BufferedReader.read(BufferedReader.java:286) ~[?:1.8.0_191]
    	at org.h2.util.ScriptReader.readBuffer(ScriptReader.java:281) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.util.ScriptReader.read(ScriptReader.java:254) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.util.ScriptReader.readStatementLoop(ScriptReader.java:144) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.util.ScriptReader.readStatement(ScriptReader.java:99) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.command.dml.RunScriptCommand.update(RunScriptCommand.java:54) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.command.CommandContainer.update(CommandContainer.java:133) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.command.Command.executeUpdate(Command.java:267) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:233) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205) ~[h2-1.4.199.jar:1.4.199]
    	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:408) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:375) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	... 11 more
    INFO  2019-08-26T04:38:43,657 (com.serotonin.m2m2.rt.maint.work.DatabaseBackupWorkItem.restore:319) - Attempting to restore database backup from: /opt/mango/backup/core-database-H2-Aug-17-2019_000500.zip 
    ERROR 2019-08-26T04:38:49,585 (com.serotonin.m2m2.rt.maint.work.DatabaseBackupWorkItem.restore:360) - StatementCallback; bad SQL grammar [RUNSCRIPT FROM '/opt/mango/backup/core-database-H2-Aug-17-2019_000500.zip' COMPRESSION ZIP; ]; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Sequence "SYSTEM_SEQUENCE_37D5345F_2CD1_48D1_AFF1_FA8783F60058" already exists; SQL statement:
    
    CREATE SEQUENCE "PUBLIC"."SYSTEM_SEQUENCE_37D5345F_2CD1_48D1_AFF1_FA8783F60058" START WITH 4 BELONGS_TO_TABLE [90035-199] 
    org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [RUNSCRIPT FROM '/opt/mango/backup/core-database-H2-Aug-17-2019_000500.zip' COMPRESSION ZIP; ]; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Sequence "SYSTEM_SEQUENCE_37D5345F_2CD1_48D1_AFF1_FA8783F60058" already exists; SQL statement:
    
    CREATE SEQUENCE "PUBLIC"."SYSTEM_SEQUENCE_37D5345F_2CD1_48D1_AFF1_FA8783F60058" START WITH 4 BELONGS_TO_TABLE [90035-199]
    	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1442) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:387) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:417) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at com.serotonin.m2m2.db.H2Proxy.runScript(H2Proxy.java:280) ~[mango-3.6.4.jar:?]
    	at com.serotonin.m2m2.rt.maint.work.DatabaseBackupWorkItem.restore(DatabaseBackupWorkItem.java:326) ~[mango-3.6.4.jar:?]
    	at com.serotonin.m2m2.module.definitions.actions.SqlRestoreActionDefinition$Action.runImpl(SqlRestoreActionDefinition.java:82) ~[mango-3.6.4.jar:?]
    	at com.serotonin.m2m2.util.timeout.SystemActionTask.run(SystemActionTask.java:74) ~[mango-3.6.4.jar:?]
    	at com.serotonin.timer.Task.runTask(Task.java:179) ~[mango-3.6.4.jar:?]
    	at com.serotonin.timer.TaskWrapper.run(TaskWrapper.java:23) ~[mango-3.6.4.jar:?]
    	at com.serotonin.timer.OrderedThreadPoolExecutor$OrderedTaskCollection.run(OrderedThreadPoolExecutor.java:336) ~[mango-3.6.4.jar:?]
    	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_191]
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_191]
    	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_191]
    Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Sequence "SYSTEM_SEQUENCE_37D5345F_2CD1_48D1_AFF1_FA8783F60058" already exists; SQL statement:
    
    CREATE SEQUENCE "PUBLIC"."SYSTEM_SEQUENCE_37D5345F_2CD1_48D1_AFF1_FA8783F60058" START WITH 4 BELONGS_TO_TABLE [90035-199]
    	at org.h2.message.DbException.getJdbcSQLException(DbException.java:573) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.message.DbException.get(DbException.java:205) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.message.DbException.get(DbException.java:181) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.command.ddl.CreateSequence.update(CreateSequence.java:53) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.command.dml.RunScriptCommand.execute(RunScriptCommand.java:79) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.command.dml.RunScriptCommand.update(RunScriptCommand.java:58) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.command.CommandContainer.update(CommandContainer.java:133) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.command.Command.executeUpdate(Command.java:267) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:233) ~[h2-1.4.199.jar:1.4.199]
    	at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205) ~[h2-1.4.199.jar:1.4.199]
    	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:408) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:375) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    

    Can someone suggest what I should try next, I need to get this server back up.


  • Hi mseverin,

    Do you have an older backup, or a JSON configuration?

    The error you posted looks like that backup didn't get to complete, so the file doesn't end appropriately. If you have another SQL backup you could try restoring that one. Or, if you have only a JSON backup your could import that into a fresh instance to get running again, then I can explain how to change the database IDs so that recovering the old data will go smoothly.


  • Hello Philip,

    Thanks for the suggestions. I have tried restoring several old backups and they all failed. The attempted restore also caused Mango to stop responding so I had to stop it and restart it.

    I will try restoring a JSON configuration and see if that works.

    Mark


  • Hello Philip,

    I imported a json backup and I appear to have everything back, except for the formatting. The colour is all wrong, my data columns don't line up with the data and I don't have my company logo. I also have no history before the JSON import.

    Can you explain what you mean by changing the database ID's.

    Thanks,
    Mark


  • Can you explain what you mean by changing the database ID's.

    The NoSQL database associates the data by the data point's ID in the dataPoints SQL table. The JSON export doesn't have IDs, so the points were probably created with sequential IDs which may or may not match what they previously were. Fortunately the SQL backup is a zipped SQL dump, so most likely the data points section can be found and the IDs recovered - if the zip file can be extracted that is. So, one could generate update statements based off XID to set the ID to what's recorded in the SQL backup.

    I imported a json backup and I appear to have everything back, except for the formatting. The colour is all wrong, my data columns don't line up with the data and I don't have my company logo. I also have no history before the JSON import.

    Is there a jsonData key in your JSON backup? Perhaps this would be another thing to search for in the SQL dump, if you can unzip it.

    I'm not sure what you mean about the columns not lining up. Maybe your UI settings for some custom CSS isn't present anymore? That would make sense if the jsonData wasn't in the JSON backup, as that is where UI settings are.


  • I was able to get all my formatting and Logos back, the only problem I have now is that I have no history.

    I tried to extract the file core-database-H2-current_date.zip but I get the error message that it is not a valid zip file. Is there another way I can get my previous history back.

    Can I now restore one of my backup databases instead of trying to open the file in Windows?


  • I would expect the same issue as before with the zip would occur if you attempted to restored it. Weird that all your SQL backups would have this issue...

    Is there another way I can get my previous history back.

    I think so. Because the JSON export will export the points in the order they're in the table (which would be by ID), you could list all the series numbers from the old_databases/mangoTSDB/*/ and sort, then enumerate them. I would expect that would be the mapping of old IDs to new IDs. Then you'd have to generate SQL update statements to change the IDs in the dataPoints table. Run the statements, then restart Mango. Then you could do a merge on the old NoSQL data.


  • I have the system back up now.

    Mango is running on a VMWare virtual machine so I restored a snapshot that was taken a month ago and this restored the database so I had the correct id's. I was lucky that the meters still had data back to time of the snapshot so I didn't loose anything.

    Do you have any idea why the zip file backups of the database can't be restored? I'd prefer to do backups using Mango because they can be automated but they don't do me any good if I can't restore them.

    Mark


  • I've only seen that when Mango terminated abruptly while making the backup. I wonder, if you run an SQL backup now, does it give you a zip you can extract?


  • Yes, I can open all zip files created since my restore, so everything seems to be back to normal.

    Thanks for your help.


  • Certainly! Glad you got it figured out!