Unable to purge events

[JoelHaggar]

Hey Andrew, I think the instructions you provide will lose all your historical data. Also you can start Mango in SAFE mode which boots up with all the data sources and publishers disabled.

I would suggest trying to get the h2-web-console to start. I wonder if you could copy the database to a more powerful server. You might just need more memory and CPU available to run the database.

[cwangv]

@joelhaggar
Hi, Joel
can you elaborate how to enable/enter 'safe mode'?
thanks.

[andrewh]

Hi Joel

It's only the config DB that's regenerated, not the time series DB. We have done it several times when the config DB has blown out to about 100 times the normal size and we haven't lost any of the time series data. Having said that, starting in Safe Mode sounds like a good idea. I'll give it a try next time.

mseverin - see https://help.infiniteautomation.com/starting-in-safe-mode/

[CraigWeb]

@andrewh using the JSON import will result in loss of data, the correct way would be to use an H2 restore file that is generated by the backup procedure. When the JSON import is used the data point will get a different table id compared to what it was originally. The NoSQL uses this id as it is the fastest way to find a row in the table.

@mseverin There are a number of forum threads on how to delete events through the H2 console. By searching "delete events"

https://forum.infiniteautomation.com/topic/3501/mango-s-out-of-memory-problem/16
https://forum.infiniteautomation.com/topic/3680/100-cpu-after-user-login-attempt/3
https://forum.infiniteautomation.com/topic/2421/unable-to-connect-to-mangoes/3

These help docs will also be helpful:
https://help.infiniteautomation.com/about-the-h2-database
https://help.infiniteautomation.com/how-to-restore-a-h2-database-backup

[mseverin]

Thanks for all the suggestions.

I have started Mango in SAFE mode (verified by looking in the ma.log file but I still can't get logged in.

I changed the env.properties file to set set db.web.start=true and tried to run the H2 web Console using web-console.sh -webAllowOthers but I get the message "Web Console server running at http://127.0.1.1:8081 (only local connections).

I thought the flag -webAllowOthers would let me connect to the Console from my laptop.

Is there something I'm missing in order to run the H2 database manager?

[mseverin]

I'm really stuck. The events table has over 100,000 lines in it and even running in safe mode I am unable to purge the events.

I really need a way to access the H2 database outside of Mango so I can manually purge the table.

Mango is running on a Linux box with command line only.

Any help would be appreciated as Mango has been down for over a week now.

[CraigWeb]

Hi Mseverin

Edit:

By the looks, you are running the script with an argument. What you need to do is edit the script and add -webAllowOthers to the last line of code.

[mseverin]

@craigweb

Hello,

Yes, I was running it like ./h2-web-console.sh - webAllowOthers.

I modified the h2-web-console.sh and added -webAllowOthers to the end of the file but I still get the same result...

(Web Console server running at http://127.0.1.1:8081 (only local connections).

[CraigWeb]

I just edited my h2-web-console.sh file as such:
java -cp ../lib/h2*.jar org.h2.tools.Server -webAllowOthers -web -webPort "$H2_PORT" -baseDir "$MA_HOME"/
Save and run it and get the following :
Web Console server running at http://192.168.0.251:8081 (others can connect)

[mseverin]

When I modified my h2-web-console.sh like yours, I can now get to the Login screen but then I get

[JoelHaggar]

You have the incorrect JDBC URL and user and pass. Please check here for the correct settings https://help.infiniteautomation.com/about-the-h2-database

[mseverin]

@joelhaggar

I can't connect the same as the example because the database is on a different server from where I'm running the web browser so I get the message "Database /opt/mango/mah2 not found". However if I modify the file to -tcp -tcpAllowOthers as the help link suggests, then I just get "This page isn't working.

[terrypacker]

@mseverin the input on the page expects the local path to the db corresponding to the machine you are accessing. In later versions of Mango this is mah2.199, check the filename of the h2 dB file on your Mango machine and use the full path to the h2 file minus the .db extension. I don’t think you want the tcp url, use the file:// style.

[mseverin]

@terrypacker

Thanks for the reply.

I have finally been able to connect.

The solution was to change my h2-web-console.sh back to -webAllowOthers

and my JDBC URL to jdbc:h2:/opt/mango/databases/mah2.199