Huge mycontroller.mv.db

[hoegaarden_bier]

My system has been running for a long time (still version 0.0.3-SNAPSHOT).
Recently I noticed that it has become very slow.
After a brief review I noticed that the database seems to be >9Gigs (9047543808 Jul 21 08:47 mycontroller.mv.db) and
the log mentions the following:

2018-07-21 08:44:19,242 ERROR [Quartz_Scheduler_Worker-6] [org.mycontroller.standalone.metrics.MetricsAggregationBase:66] Error,
java.sql.SQLException: Could not run raw execute statement DELETE FROM metrics_double_type_device WHERE aggregationType=1 AND timestamp <= 1532133798687 
	at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:25)
	at com.j256.ormlite.dao.BaseDaoImpl.executeRaw(BaseDaoImpl.java:686)
	at org.mycontroller.standalone.metrics.MetricsAggregationBase.aggregateAndInsertForBucketDuration(MetricsAggregationBase.java:63)
	at org.mycontroller.standalone.metrics.MetricsAggregationBase.executeBucketByBucket(MetricsAggregationBase.java:80)
	at org.mycontroller.standalone.metrics.MetricsAggregationBase.runAggregation(MetricsAggregationBase.java:175)
	at org.mycontroller.standalone.metrics.jobs.MetricsAggregationJob.doRun(MetricsAggregationJob.java:51)
	at org.knowm.sundial.Job.execute(Job.java:57)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:178)
	at org.quartz.core.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:515)
Caused by: org.h2.jdbc.JdbcSQLException: File corrupted while reading record: "type: 230". Possible solution: use the recovery tool; SQL statement:
DELETE FROM metrics_double_type_device  WHERE aggregationType=1 AND timestamp <= 1532133798687  [90030-193]
	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.mvstore.db.ValueDataType.readValue(ValueDataType.java:608)
	at org.h2.mvstore.db.ValueDataType.readValue(ValueDataType.java:561)
	at org.h2.mvstore.db.ValueDataType.read(ValueDataType.java:171)
	at org.h2.mvstore.db.TransactionStore$VersionedValueType.read(TransactionStore.java:1644)
	at org.h2.mvstore.Page.read(Page.java:711)

Trying using the H2 Runscript to execute this is not possible since it complains that the
metrics_double_type_device does not exist.
Running the backup from within the mycontroller fails, purging does not seem to work.

Any suggestions on how to recover from this situation and upgrade the system?

[jkandasa]

@hoegaarden_bier

• backup your database
• unpack SNAPSHOT version and place your database under mycontroller/conf
• Run MyController server.

I remember I fixed some pure issue after(0.0.3-SNAPSHOT) in this version.
Can you post your metric retention settings?

[hoegaarden_bier]

How can I backup the database?
Isn't this the 9 Gig DB?

[jkandasa]

@hoegaarden_bier Yes, you have to back up 9GB. you may compress and save it(we may need to use this backup on the worst case, in case if we face issue with the recent version on upgrade).

Command to compress: tar cvf mycontroller.tar.gz mycontroller.mv.db (may reduce some amount of size).

You may copy the database to your computer. If you are running in Raspberry PI, just take the memory card and copy it in your computer.

[hoegaarden_bier]

No luck ....

After the unpack and db move I still get same issue:

2018-07-24 08:10:07,951 INFO [main] [org.mycontroller.standalone.StartApp:113] MyController.org server started in [1231838] ms
2018-07-24 08:11:05,600 ERROR [Quartz_Scheduler_Worker-8] [org.mycontroller.standalone.db.dao.MetricsDoubleTypeDeviceDaoImpl:334] Unable to execute countOf query
org.h2.jdbc.JdbcSQLException: File corrupted while reading record: "type: 230". Possible solution: use the recovery tool; SQL statement:
SELECT * FROM metrics_double_type_device WHERE ((timestamp > 1532368338687 AND timestamp <= 1532368398687 ) AND aggregationType = 0 ) [90030-194]
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.mvstore.db.ValueDataType.readValue(ValueDataType.java:608)
at org.h2.mvstore.db.ValueDataType.readValue(ValueDataType.java:561)
at org.h2.mvstore.db.ValueDataType.read(ValueDataType.java:171)
at org.h2.mvstore.db.TransactionStore$VersionedValueType.read(TransactionStore.java:1644)
at org.h2.mvstore.Page.read(Page.java:711)
at org.h2.mvstore.Page.read(Page.java:195)
at org.h2.mvstore.MVStore.readPage(MVStore.java:1952)

[jkandasa]

@hoegaarden_bier Sorry for the inconvenience.
You have some of the options,

• do restore of your last successful backup
• share your corrupted database with me(I will try to fix it), I understand it is difficult to upload 9GB, If possible
• Start from zero with the latest build.