Problem With Mango

---

Hi guys,

I have a problem with my Mango Installation, described below, I've submitted a ticket for this, but I will also post this here, maybe someone knows the solution for this or maybe the developers would like to solve it through here so anyone can use this later. Let's keep our forum dynamic! :)

Mango is installed on a windows 7 professional PC and I installed the service with the yajsw, it worked perfectly for most than a year.
The computer was also (sadly) used to surf the web, so this is what may have caused the problems.
The user says there was an electrical problem and the computer "blinked" twice and after that, Mango + DGLux stopped working.

The symptoms:

1. The service doesn't starts automatically.
2. If I manually start the service, it stops automatically after a few minutes.
3. If I manually start the service and launch Mango, I get the following error from the browser,
   "RequestError: Unable to load /status/mango.json?time=1495810520615 status 0"
   and the Mango Initialization stops at 55%, when
   Initializing Event Manager, and at the loading lines it states
   "INFO 2017-05-26 10:55:15,985 (com.serotonin.m2m2.dg.upgrade.DBUpgrade.checkUpgrade:36) - Starting instance with core version 2.5.2, db 9

I tried the following:
Tried to delete the service and the yajsw folders and reinstal, no luck.
Recover the computer to an early date backup, no luck.

Regards,
Rodrigo

Hi Rodrigo,

You should check the Mango\logs\ma.log file for any errors that occur during startup.

You can also launch it from a command prompt and you'll get the log output in the console, by navigating to Mango\bin\ and running ma-start.bat. You'll need either JAVA_HOME set or java on the PATH

I just noticed you emailed in your logs. One moment please.....

It looks like your database is corrupted, and that is preventing starting. Specifically, a binary chunk of data in your events table appears to have been misplaced.

You have a couple paths to resolve, you can

1. Use the Mango\bin\h2-web-console.bat (be sure to add the -webAllowOthers as the comment in the script suggests if you'll be accessing it from another computer) and run delete from events; delete from userEvents; then try to start again and see if the only issue was in the events table.

or
2) Move your Mango\databases\mah2.h2.db file somewhere else (back it up), start Mango on a fresh database (login admin/admin) and use the SQL Database Backup tools in the system settings to restore an H2 backup from your Mango\backups\ directory. After restore restart Mango and see if it'll start.

Hi Phil,
Thanks for answering so fast!

I followed the point 2,

• moved the mah2.h2.db somewhere else
• started mango with ma-start.bat (it started and everything I had on Mango and DGLux dissapeared, including licenses)
• went to system settings, Database Backup Settings, Get backups,
• choosed one from a month ago and clicked restore database, here I closed the browser and the command prompt, I couldn't do anything else.
• restarted from m-start.bat and now the loading lines goes to 100% and says "Running shutdown tasks", and keeps refreshing but it doesn't finish loading and mango doesn't starts.

The point 1 you recommended, I don't have the h2-web-console.bat file in that folder, or any other folder.

Certainly!

Did the database restore finish successfully? Can you post or email the log from the current startup?

Edit: It is very surprising to me that you would not have an h2-web-console.bat in your Mango/bin/ directory... I believe that was added in 2.7

@phildunlap

How do I know if the database has finished restoring?
After hitting restore it just stays there.

My mango version is 2.5.2

"Using C:\Mango as MA_HOME
C:\Mango\overrides\classes;C:\Mango\classes;C:\Mango\overrides\properties;C:\Man
go\overrides\lib*;C:\Mango\lib*
Using Java at java
INFO 2017-05-26 14:51:16,416 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'asciiFile', v1.1.5 by Infinite Automation
INFO 2017-05-26 14:51:16,423 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'BACnet', v2.1.3 by Infinite Automation Software
INFO 2017-05-26 14:51:16,427 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'controlcore', v1.0.5 by Infinite Automation
INFO 2017-05-26 14:51:16,429 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'dashboards', v1.0.3 by Infinite Automation
INFO 2017-05-26 14:51:16,430 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'dataFile', v1.0.8 by Infinite Automation
INFO 2017-05-26 14:51:16,435 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'dataImport', v1.3.6 by Infinite Automation Software
INFO 2017-05-26 14:51:16,438 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'dglux', v1.2.0 by Infinite Automation Software
INFO 2017-05-26 14:51:16,440 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'dnp3', v1.5.0 by Infinite Automation Software
INFO 2017-05-26 14:51:16,443 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'galil', v1.4.5 by Infinite Automation Software
INFO 2017-05-26 14:51:16,445 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'graphicalViews', v1.5.2 by Infinite Automation Software
INFO 2017-05-26 14:51:16,463 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'http', v1.4.7 by Infinite Automation Software
INFO 2017-05-26 14:51:16,467 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'internal', v1.4.6 by Infinite Automation Software
INFO 2017-05-26 14:51:16,469 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'jmxds', v1.4.5 by Infinite Automation Software
INFO 2017-05-26 14:51:16,472 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'jspViews', v2.0.5 by Infinite Automation Software
INFO 2017-05-26 14:51:16,482 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'log4jDS', v1.0.2 by Infinite Automation Software
INFO 2017-05-26 14:51:16,485 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'log4JReset', v1.0.6 by Infinite Automation Software
INFO 2017-05-26 14:51:16,486 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'loggingConsole', v1.0.2 by Infinite Automation
INFO 2017-05-26 14:51:16,487 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'maintenanceEvents', v1.4.8 by Infinite Automation Software
INFO 2017-05-26 14:51:16,490 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'mangoApi', v1.0.3 by Infinite Automation
INFO 2017-05-26 14:51:16,491 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'mbus', v1.0.7 by Infinite Automation Software
INFO 2017-05-26 14:51:16,496 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'meta', v2.1.1 by Infinite Automation Software
INFO 2017-05-26 14:51:16,500 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'modbus', v1.5.4 by Infinite Automation Software
INFO 2017-05-26 14:51:16,509 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'onewire', v1.4.6 by Infinite Automation Software
INFO 2017-05-26 14:51:16,512 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'opcda', v1.5.7 by Infinite Automation Software
INFO 2017-05-26 14:51:16,513 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'openv4j', v1.0.5 by aploese
INFO 2017-05-26 14:51:16,515 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'pachube', v1.4.8 by Infinite Automation Software
INFO 2017-05-26 14:51:16,517 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'persistent', v1.5.8 by Infinite Automation Systems Inc.
INFO 2017-05-26 14:51:16,520 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'pointLinks', v1.4.6 by Infinite Automation Software
INFO 2017-05-26 14:51:16,522 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'pop3', v1.4.5 by Infinite Automation Software
INFO 2017-05-26 14:51:16,525 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'reports', v2.1.3 by Infinite Automation Systems Inc.
INFO 2017-05-26 14:51:16,529 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'scheduledEvents', v1.4.7 by (unknown vendor)
INFO 2017-05-26 14:51:16,531 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'scripting', v1.0.8 by Infinite Automation Software
INFO 2017-05-26 14:51:16,535 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'serial', v1.1.5 by Infinite Automation
INFO 2017-05-26 14:51:16,537 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'snmp', v1.4.6 by Infinite Automation Software
INFO 2017-05-26 14:51:16,540 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'sqlConsole', v1.4.5 by Infinite Automation Software
INFO 2017-05-26 14:51:16,542 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'sqlds', v1.5.7 by Infinite Automation Software
INFO 2017-05-26 14:51:16,545 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'ssh', v1.0.5 by Infinite Automation Systems Inc.
INFO 2017-05-26 14:51:16,548 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'sstGlobalScripts', v1.4.6 by Infinite Automation Software
INFO 2017-05-26 14:51:16,550 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'sstGraphics', v1.0.6 by Infinite Automation Software
INFO 2017-05-26 14:51:16,551 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'sstTheme', v1.0.6 by Infinite Automation Software
INFO 2017-05-26 14:51:16,551 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'TCPIP', v1.0.3 by Infinite Automation Systems Inc.
INFO 2017-05-26 14:51:16,554 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'templateConfig', v1.0.5 by Infinite Automation Software
INFO 2017-05-26 14:51:16,556 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'twilio', v1.0.4 by (unknown vendor)
INFO 2017-05-26 14:51:16,560 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'virtualDS', v1.4.5 by Infinite Automation Software
INFO 2017-05-26 14:51:16,563 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'vmstat', v1.4.7 by Infinite Automation Software
INFO 2017-05-26 14:51:16,569 (com.serotonin.m2m2.Main.loadModules:402) - Loadin
g module 'watchlists', v1.6.1 by Infinite Automation Systems Inc.
INFO 2017-05-26 14:51:17,306 (com.serotonin.m2m2.Lifecycle.loadLic:411) - Check
ing license...
INFO 2017-05-26 14:51:17,373 (com.serotonin.m2m2.db.H2Proxy.initializeImpl:42)

• Initializing H2 connection manager
  INFO 2017-05-26 14:51:22,014 (com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgr
  ade:36) - Starting instance with core version 2.5.2, db 9
  Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: Prep
  aredStatementCallback; bad SQL grammar [select id, xid, alias, data from eventHa
  ndlers where eventTypeName=? and eventSubtypeName=? and (eventTypeRef1=? or ev
  entTypeRef1=0) and (eventTypeRef2=? or eventTypeRef2=0)]; nested exception is o
  rg.h2.jdbc.JdbcSQLException: Tabla "EVENTHANDLERS" no encontrada
  Table "EVENTHANDLERS" not found; SQL statement:
  select id, xid, alias, data from eventHandlers where eventTypeName=? and eventSu
  btypeName=? and (eventTypeRef1=? or eventTypeRef1=0) and (eventTypeRef2=? or
  eventTypeRef2=0) [42102-181]
  at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.d
  oTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat
  or.translate(AbstractFallbackSQLExceptionTranslator.java:73)
  at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:

660. at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:69

661.  at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:72
     

662.  at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:73
     

663.  at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:78
     

664.  at com.serotonin.db.DaoUtils.query(DaoUtils.java:308)
      at com.serotonin.m2m2.db.dao.EventDao.getEventHandlers(EventDao.java:611
     

)
at com.serotonin.m2m2.db.dao.EventDao.getEventHandlers(EventDao.java:581
)
at com.serotonin.m2m2.rt.EventManager.setHandlers(EventManager.java:415)

    at com.serotonin.m2m2.rt.EventManager.raiseEvent(EventManager.java:94)
    at com.serotonin.m2m2.rt.event.type.SystemEventType.raiseEvent(SystemEve

ntType.java:97)
at com.serotonin.m2m2.Lifecycle.terminate(Lifecycle.java:321)
at com.serotonin.m2m2.Main.main(Main.java:108)
Caused by: org.h2.jdbc.JdbcSQLException: Tabla "EVENTHANDLERS" no encontrada
Table "EVENTHANDLERS" not found; SQL statement:
select id, xid, alias, data from eventHandlers where eventTypeName=? and eventSu
btypeName=? and (eventTypeRef1=? or eventTypeRef1=0) and (eventTypeRef2=? or
eventTypeRef2=0) [42102-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.Parser.readTableOrView(Parser.java:5227)
at org.h2.command.Parser.readTableFilter(Parser.java:1221)
at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1860)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1969)
at org.h2.command.Parser.parseSelectSub(Parser.java:1854)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1675)
at org.h2.command.Parser.parseSelect(Parser.java:1663)
at org.h2.command.Parser.parsePrepared(Parser.java:433)
at org.h2.command.Parser.parse(Parser.java:305)
at org.h2.command.Parser.parse(Parser.java:277)
at org.h2.command.Parser.prepareCommand(Parser.java:242)
at org.h2.engine.Session.prepareLocal(Session.java:446)
at org.h2.engine.Session.prepareCommand(Session.java:388)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1189)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:7
2)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:277)
at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCre
ator.createPreparedStatement(JdbcTemplate.java:1557)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:
638)
... 12 more
"

Ah yes you said that, my mistake. Not that you'll necessarily need it, but the h2-web-console.bat script should work on your version as well, so, here is the current version just for fun:

@echo off
rem
rem | Copyright (C) 2006-2015 //infintie Automation. All rights reserved.
rem | @author Phillip Dunlap
rem
rem | Runs H2 Web Console. (Script Version 1.0.0 - To be run from MA_HOME\bin)

rem | Define the port to start H2 on...
set H2_PORT=8085

rem | Check if MA_HOME is properly defined
if "%MA_HOME%" == "" goto useCD
if exist "%MA_HOME%\bin\ma-start.bat" goto okHome
echo The MA_HOME environment variable is not defined correctly: %MA_HOME%. Trying the current directory instead...

rem | Check if the current directory is ok to use
:useCD
set BIN_DIR=%CD%
pushd ..
set MA_HOME=%CD%
popd
if exist "%MA_HOME%\bin\ma-start.bat" goto okHome

rem | Don't know where home is.
echo Cannot determine the MA home directory
goto end

rem | Found a good home. Carry on...
:okHome
echo Using %MA_HOME% as MA_HOME

set EXECJAVA=java
if "%JAVA_HOME%" == "" goto gotJava
set EXECJAVA=%JAVA_HOME%\bin\java

:gotJava
echo Using Java at %EXECJAVA%

rem | Put the whole lib folder on CP because we cannot do h2*.jar...
rem | Add -webAllowOthers if you will be using the web console from an origin other than localhost
"%EXECJAVA%" -cp "%MA_HOME%\lib\*" org.h2.tools.Server -web -webPort "%H2_PORT%" -baseDir "%MA_HOME%\"

:end

Which can be put in Mango\bin\

You will know the restore finished if it said "Database Restored" under the restore setting sections. Any errors in the restore would have made it to your log file.

I would wonder if the restore didn't finish before being terminated. The backup sql file in the Mango/backups directory is a ZIP of of a dump file, so one can read the SQL it's doing if desired.

So you know, closing a command prompt is somewhat equivalent to doing a hard kill in linux (-9) in that Mango doesn't get to shutdown nicely and wait for tasks to finish. To kill a Mango running in a command prompt cleanly, you want to click the command prompt and hit Ctrl + C to send a polite termination request instead of an abrupt termination.

To provide closure to the thread, the database restore simply took a long time (was fairly large) and was killed during the database restore.

The steps provided did yield resolution once the restore was allowed to finish.

@phildunlap

Absolutely, It was my lack of patience :$

Thanks again Phil,

Regards