Unable to purge events

[mseverin]

Hello,

We are in the middle of switching BAS providers and as a result Mango has been flooded with events as devices go offline and then come back online.

My events table has too many records and now I can't connect to Mango.

I've tried to use the "purge all events" button from System Settings, but cannot stay logged in long enough to get to this page.

I also tried using Mango/bin/h2-web-console.sh but the page doesn't load.

Is there any other way I can delete all events?

[andrewh]

I had a similar problem, but with a publisher. I disabled the publisher that was generating the events (immediately after a restart was easiest). The system was then stable/responsive enough for me to get to and click on "purge all events".

If this doesn't work, you could try this (assuming h2) [you may want to confirm with someone from IA first]:

• stop mango
• rename the config file (/opt/mango/databases/mah2.h2.db)
• download and edit the most recent config backup json file to set enabled=false for the problem component(s)
• start mango (this will generate a new out-of-the -box config)
• login with the default admin login (the other logins will have been cleared)
• go to "Administration -> Configuration import/export", click "upload json file" and upload the file you modified.
• probably restart mango from the Modules page
• if this fails and you need to roll back, simply stop mango, rename the config files back as they were and start mango again.
  I think this will effectively purge all the events, but if not, the system should then be responsive enough to do that. As a bonus, this can significantly reduce the size of the h2 config DB.

Good luck.
Andrew

[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