How to purge all statistics from DB

RouterOS The Dude
1

Hello. Mikrotik router with Dude takes time from GPS receiver. And for one moment GPS supply “2026 year january 1st” date to router.
Later GPS was restored and date and time are correct, but impact was bad.
Please see picture.
stats.png
.

So, how may I clean/purge all stored data from DB?

I need to keep all config, of course

2

I downloaded database to Windows PC.
7-zip extracted it. File header looks like “dude.db xxxxxxxx SQLite format 3”
If you cut 1FF bytes from DB start, it will opened by any SQLite tools. So, Dude DB almost standard DB, but not.
Anyway, I did vacuum on router itself:
[admin@R0B] /dude> vacuum-db file=nvme1/dude/dude.db
But this does not delete statistics from DB.

This thread discuss exactly deleting statistics http://forum.mikrotik.com/t/dude-db-vacuuming/131375/1 , but since DB can not be opened on PC without cutting header, and I do not know what stored in this header, may be checksum for db?

So, I deleted header, got normal DB, removed historical data as was proposed in mentioned thread. inserted back header. Dude does not start.
Looks like header has control checksum to protect Dude form People.
FYI: DB Header
Dude DB Header.png

3

Good job it doesn’t. It never does and never will. Why do people think vacuum is just going to delete some data or other from the tables?
Did you actually read up what the command does before running it?
No, I thought not.

4
  1. Do not use Dude DB backup.
  2. Just stop Dude service and copy actual dude.db file to work with.
  3. run: sqlite3.exe dude.db
  4. copy-paste following commands into sqlite3 session
    – Begin transaction for safety
    BEGIN TRANSACTION;

– Delete all rows from statistics tables
DELETE FROM outages;
DELETE FROM chart_values_raw;
DELETE FROM chart_values_10min;
DELETE FROM chart_values_2hour;
DELETE FROM chart_values_1day;

– Commit changes
COMMIT;

pragma integrity_check;
vacuum;
reindex;
.quit
5) move statistics-free dude.db back to dude server and start service.