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.
Import H2 database from MangoES 2.5.2 to MangoES 2.7.10
-
Hi,
I want to extract H2 database from a mangoES version 2.5.2 and restore it into a mangoES version 2.7.10. I tired that, but it didn’t work and the following is the log file output, I just want to know is it possible to do that with different version and how should I do that. I should say that I cannot upgrade the version 2.5.2 to 2.7.10 as it’s a running project and it’s in remote area, I want to restore the database one a mango with version 2.7.10 and send that to site to substitute. Appreciate your help in this regard.ERROR 2016-05-31 14:15:14,494 (com.serotonin.m2m2.Main.main:120) - Error during initialization
com.serotonin.ShouldNeverHappenException: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE templates ( id int NOT NULL auto_increment, xid varchar(50) NOT NULL, name VARCHAR(255), template
Type VARCHAR(50), readPermission VARCHAR(255), setPermission VARCHAR(255), data longblob NOT NULL, PRIMARY KEY (id) ); ]; nested exception is org.h2.jdbc.JdbcSQLException: Table "TEMPLATES" already exists; SQL statement:
CREATE TABLE templates ( id int NOT NULL auto_increment, xid varchar(50) NOT NULL, name VARCHAR(255), templateType VARCHAR(50), readPermission VARCHAR(255), setPermission VARCHAR(255), data longblob NOT NULL, PRIMARY KEY (id) ); [42101-
181]
at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:102)
at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:34)
at com.serotonin.m2m2.db.DatabaseProxy.initialize(DatabaseProxy.java:180)
at com.serotonin.m2m2.Lifecycle.databaseInitialize(Lifecycle.java:602)
at com.serotonin.m2m2.Lifecycle.initialize(Lifecycle.java:280)
at com.serotonin.m2m2.Main.main(Main.java:116)
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE templates ( id int NOT NULL auto_increment, xid varchar(50) NOT NULL, name VARCHAR(255), templateType VARCHAR(50), readPermissio
n VARCHAR(255), setPermission VARCHAR(255), data longblob NOT NULL, PRIMARY KEY (id) ); ]; nested exception is org.h2.jdbc.JdbcSQLException: Table "TEMPLATES" already exists; SQL statement:
CREATE TABLE templates ( id int NOT NULL auto_increment, xid varchar(50) NOT NULL, name VARCHAR(255), templateType VARCHAR(50), readPermission VARCHAR(255), setPermission VARCHAR(255), data longblob NOT NULL, PRIMARY KEY (id) ); [42101-
181]
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:439)
at com.serotonin.m2m2.db.H2Proxy.runScript(H2Proxy.java:146)
at com.serotonin.m2m2.db.upgrade.DBUpgrade.runScript(DBUpgrade.java:129)
at com.serotonin.m2m2.db.upgrade.DBUpgrade.runScript(DBUpgrade.java:143)
at com.serotonin.m2m2.db.upgrade.DBUpgrade.runScript(DBUpgrade.java:134)
at com.serotonin.m2m2.db.upgrade.Upgrade10.upgrade(Upgrade10.java:30)
at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:98)
... 5 more
Caused by: org.h2.jdbc.JdbcSQLException: Table "TEMPLATES" already exists; SQL statement:
CREATE TABLE templates ( id int NOT NULL auto_increment, xid varchar(50) NOT NULL, name VARCHAR(255), templateType VARCHAR(50), readPermission VARCHAR(255), setPermission VARCHAR(255), data longblob NOT NULL, PRIMARY KEY (id) ); [42101-
181]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.command.ddl.CreateTable.update(CreateTable.java:111)
at org.h2.command.CommandContainer.update(CommandContainer.java:78)
at org.h2.command.Command.executeUpdate(Command.java:254)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:185)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:159)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:431)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404)
... 12 more
INFO 2016-05-31 14:15:14,500 (com.serotonin.m2m2.Lifecycle.terminate:344) - Mango Lifecycle terminating... -
Hi Mohammad,
I have seen that before, and the easiest way for me to handle it has been to ensure the commands the database upgrade attempted have all been completed, then increment the schema version number. It looks like your Mango may have suffered an interrupted boot while performing Upgrade10.
You could start the Mango/bin/h2-web-console.sh script to get access to the H2 database shell (consider adding -webAllowOthers as the script discusses), then run the SQL for the upgrade manually,
CREATE TABLE templates ( id int NOT NULL auto_increment, xid varchar(50) NOT NULL, name VARCHAR(255), templateType VARCHAR(50), readPermission VARCHAR(255), setPermission VARCHAR(255), data longblob NOT NULL, PRIMARY KEY (id) );
ALTER TABLE templates ADD CONSTRAINT templatesUn1 UNIQUE (xid);
ALTER TABLE dataPoints ADD COLUMN templateId INT;
ALTER TABLE dataPoints ADD CONSTRAINT dataPointsFk2 FOREIGN KEY (templateId) REFERENCES templates(id);And then increment your database schema number via: UPDATE systemSettings SET settingvalue="11" where settingname="databaseSchemaVersion"
Alternatively, since the problem you are experiencing is with the first command, you could try dropping the templates table and letting Mango perform the upgrade.
I would guess this was caused by importing the JSON configuration from the old Mango to the new. We discovered an issue that the database schema versions were being imported/exported through the emport page, so if you had started a fresh 2.7.10 Mango, then imported your configuration, you could potentially get into this situation. The other way I'm aware of is to stop Java during one of these upgrades. If you suspect this is the case, you could try to simply update your systemSettings table to the current database schema (12) but you may also wish to check the existence of the jsonData table.
-
Your other option would be to start this database in a Mango 2.5.2 installation and then do a full upgrade from scratch on the Modules page. After the upgrade is done go to your system setting page and perform a H2 backup. Now take this file to the new 2.7.10 and it will work.
Proper database upgrading only works when modules are also being upgraded or installed.
-
Hi guys,
Thanks for your support. I went for Joel solution and could restore the database on another mangoES 2.5.2 but when I tried to open whatch list or do any thing on Web interface, it's really slow (I could see the watch list), when I tried to open datasources I recived Server error:Timeout, and I couldn't upgrade that to version 2.7.10. I tried several backups for different days but the result was the same. I also tried to boot the mango in SAFE mode but that couldn't help. Appreciate your help.
Cheers -
Hi,
I could resolve the slowness issue by purging the events and could upgrade the MangoES to 2.7.10 successfully, but I have got another issue, I cannot see the data sources but can see the data points, do you have any idea?
Cheers -
Usually in a situation like that some serialization errors which are being printed to the console, which means you probably do not have the modules installed for all the data sources you have defined. I'm not sure which version you're using that's experiencing the problem, but if it is 2.7.10 you can use the modules page to make sure you have all the modules you are supposed to.
-
Also, clearing your browser's cache will likely resolve it assuming you have all the correct modules installed.
-
Thanks guys, clearing browser cache worked and all good now, but I found the follwing error in ma.log, Appreciate your help.
ERROR 2016-06-07 11:10:00,014 (com.serotonin.m2m2.meta.JavaScriptPointLocatorRT.executeImpl:54) - TypeError: Cannot read property "value" from undefined in <eval> at line number
8 in <eval> at line number 8
javax.script.ScriptException: TypeError: Cannot read property "value" from undefined in <eval> at line number 8 in <eval> at line number 8
at com.serotonin.m2m2.rt.script.ScriptExecutor.prettyScriptMessage(ScriptExecutor.java:211)
at com.serotonin.m2m2.rt.script.CompiledScriptExecutor.execute(CompiledScriptExecutor.java:83)
at com.serotonin.m2m2.meta.JavaScriptPointLocatorRT.executeImpl(JavaScriptPointLocatorRT.java:50)
at com.serotonin.m2m2.meta.MetaPointLocatorRT.execute(MetaPointLocatorRT.java:291)
at com.serotonin.m2m2.meta.MetaPointLocatorRT$ScheduledUpdateTimeout.run(MetaPointLocatorRT.java:206)
at com.serotonin.timer.TimerTask.runTask(TimerTask.java:148)
at com.serotonin.timer.OrderedTimerTaskWorker.run(OrderedTimerTaskWorker.java:29)
at com.serotonin.timer.OrderedThreadPoolExecutor$OrderedTask.run(OrderedThreadPoolExecutor.java:278)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Cheers,
Mohammad -
It could be a typeo in a variable name, or perhaps a point was deleted that was in the context of a meta point. Something like...
p1 = random numeric, script:
return p7.value*2
