Insight has broken "database disk image is malformed" for insight aggregator

[NetGobbler]

EDIT!:

Please see bottom post, I just want to nuke my SQ3lite database to get data capture working again, I miss my logging and detailed info.





I haven't logged into my system in about a month and I just noticed unfortunately monitoring isn't working again for me.


Pics:
https://i.imgur.com/DxyShfA.png
https://i.imgur.com/q5LI8o7.png

Unsure why this isn't working. Netflow seems to be enabled and I haven't changed anything.

OPNsense 22.1.6-amd64
FreeBSD 13.0-STABLE
OpenSSL 1.1.1n 15 Mar 2022

I've rebooted and updated (I had the previous version) and it's still not working.

I did just see "Insight Aggregator" in a stopped state in the dashboard, despite update and reboot. (flowd_aggregate)
However I wouldn't have stopped this. Also starting it, only results in it stopping again.

Log files say:

lowd aggregate died with message Traceback (most recent call last): File "/usr/local/opnsense/scripts/netflow/flowd_aggregate.py", line 160, in run aggregate_flowd(self.config, do_vacuum) File "/usr/local/opnsense/scripts/netflow/flowd_aggregate.py", line 80, in aggregate_flowd stream_agg_object.add(copy.copy(flow_record)) File "/usr/local/opnsense/scripts/netflow/lib/aggregates/interface.py", line 72, in add super(FlowInterfaceTotals, self).add(flow) File "/usr/local/opnsense/scripts/netflow/lib/aggregates/init.py", line 187, in add self._update_cur.execute(self._insert_stmt, flow) sqlite3.DatabaseError: database disk image is malformed

Here's the file system.

root@OPNsense:~ # df
Filesystem 1K-blocks Used Avail Capacity Mounted on
zroot/ROOT/default 21351364 1141180 20210184 5% /
devfs 1 1 0 100% /dev
/dev/ada0p1 266176 1808 264368 1% /boot/efi
zroot/tmp 20210464 280 20210184 0% /tmp
zroot/var/log 20676696 466512 20210184 2% /var/log
zroot 20210280 96 20210184 0% /zroot
zroot/usr/home 20210280 96 20210184 0% /usr/home
zroot/usr/src 20210280 96 20210184 0% /usr/src
zroot/var/audit 20210280 96 20210184 0% /var/audit
zroot/var/crash 20210280 96 20210184 0% /var/crash
zroot/usr/ports 20210280 96 20210184 0% /usr/ports
zroot/var/mail 20210312 128 20210184 0% /var/mail
zroot/var/tmp 20210292 108 20210184 0% /var/tmp
devfs 1 1 0 100% /var/dhcpd/dev
devfs 1 1 0 100% /var/unbound/dev

Have I done something wrong? I actually switched to ZFS to get a more resilient filesystem. I was having some serious power failure issues, possibly it was that?

Any advice would be so lovely, thank you.

P.S are there any scripts which email you when a service stops or breaks?
Thanks for the hard work!

[NetGobbler]

EDIT:

See bottom post, just want to kill the database please.

[NetGobbler]

If anyone else has this issue I hope this helps with the googling indexer in some capacity.
https://github.com/opnsense/core/issues/5739
Hopefully I'll have a solution in the near future.

Thank you for the hard work.

[NetGobbler]

Hello

Could anyone here please tell me how to destroy my insight database from scratch, therefore just fixing the problem outright?

It's clearly corrupted from the line

lowd aggregate died with message Traceback (most recent call last): File "/usr/local/opnsense/scripts/netflow/flowd_aggregate.py", line 160, in run aggregate_flowd(self.config, do_vacuum) File "/usr/local/opnsense/scripts/netflow/flowd_aggregate.py", line 80, in aggregate_flowd stream_agg_object.add(copy.copy(flow_record)) File "/usr/local/opnsense/scripts/netflow/lib/aggregates/interface.py", line 72, in add super(FlowInterfaceTotals, self).add(flow) File "/usr/local/opnsense/scripts/netflow/lib/aggregates/init.py", line 187, in add self._update_cur.execute(self._insert_stmt, flow) sqlite3.DatabaseError: database disk image is malformed


Sorry to bump the thread but I just need the logging / capturing of usage to work.

[NetGobbler]

Ok I have used the menu under settings to reset RRD data
Reset netflow data
and Repair netflow data

I hit all 3 twice, rebooted, I've got reporting back.   

I suspect my corruption was due to power issues in my location where we lost power (a lot) over the course of several weeks.
Though I thought ZFS was more resilient, based on posts here.

So I hope this helps others who google with a similar issue.