Community discussions

 
duanes1967
just joined
Topic Author
Posts: 8
Joined: Fri Jun 29, 2012 6:29 pm

Where is db cleanup and maintenance info

Thu Sep 06, 2012 12:14 am

I need instructions on how to drop (or at least delete large portions) of the historical data.

The forum mentions a thread on database maintenance and cleanup procedures followed by the use of vacuum, but I cannot locate that thread.

This 2gb issue really needs some attention.
 
lebowski
Forum Guru
Forum Guru
Posts: 1616
Joined: Wed Aug 27, 2008 5:17 pm

Re: Where is db cleanup and maintenance info

Thu Sep 06, 2012 1:01 am

I wrote these instructions long ago...

--------------
An easy way to vacuum the database is to copy dude.db and sqlite3.exe into a new folder "somewhere other than the dude folder" this will let you modify a copy of dude.db instead of the original. Make a shortcut to sqlite3.exe, edit the properties of the shortcut so that dude.db is on the command line.

"C:\Documents and Settings\person\Desktop\sqlite3.exe" dude.db

Then double click the shortcut, type VACUUM; A journal file will be created while it is vacuuming...
--------------

Here is a screenshot of the process...
vacuum.png
You do not have the required permissions to view the files attached to this post.
 
duanes1967
just joined
Topic Author
Posts: 8
Joined: Fri Jun 29, 2012 6:29 pm

Re: Where is db cleanup and maintenance info

Thu Sep 06, 2012 4:28 pm

I REALLY appreciate the prompt response... Thanks.

From the other post, it appeared that there might be a more in depth process.

That said, I get a Primary key error as follows:

SQLite version 3.7.14 2012-09-03 15:42:36
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> vacuum;
Error: PRIMARY KEY must be unique
sqlite>


I don't know where to turn other than completely remove all data but I have a considerable number of items being watched. Is there a table description or database dictionary anywhere ?
 
lebowski
Forum Guru
Forum Guru
Posts: 1616
Joined: Wed Aug 27, 2008 5:17 pm

Re: Where is db cleanup and maintenance info

Fri Sep 07, 2012 2:13 am

Wow primary key is corrupt you will need to go Google how to make it unique (although I know nothing about this), the database is also compressed so you need to extract it,some where on here someone mentions the tool they used... probably easier to start over or find a backup before you had issues... bummer.
 
ditonet
Forum Veteran
Forum Veteran
Posts: 839
Joined: Mon Oct 19, 2009 12:52 am
Location: Europe/Poland/Konstancin-Jeziorna
Contact:

Re: Where is db cleanup and maintenance info

Fri Sep 07, 2012 3:17 am

Grzegorz | MTCNA, MTCRE, MTCSE | konsultacje MikroTik Warszawa
It is a book about a Spanish guy called Manual. You should read it. - Dilbert
 
duanes1967
just joined
Topic Author
Posts: 8
Joined: Fri Jun 29, 2012 6:29 pm

Re: Where is db cleanup and maintenance info

Fri Sep 07, 2012 5:21 pm

Followed the link and learned a few things.

1 - Apparently, there is a object created for EVERY MIB entry for EVERY map item.
2 - If the same item appears on two different maps, they are treated as unique items.
3 - I have 21,000+ items in my objs table.

4 - Apparently, there is something about the objects stored elsewhere. If I delete the db and recreate the schema then import just the objs table, the passwords, maps and all other configs are gone as well. :(
 
lebowski
Forum Guru
Forum Guru
Posts: 1616
Joined: Wed Aug 27, 2008 5:17 pm

Re: Where is db cleanup and maintenance info

Mon Sep 10, 2012 4:36 pm

That is an impressive number of things to monitor and yes every map item is unique. Many times when I need a copy of an object on another map I make it a static object and leave the real on on the main map. I didn't get to read about primary key but it sounds like you might have to start over?
 
ditonet
Forum Veteran
Forum Veteran
Posts: 839
Joined: Mon Oct 19, 2009 12:52 am
Location: Europe/Poland/Konstancin-Jeziorna
Contact:

Re: Where is db cleanup and maintenance info

Mon Sep 10, 2012 7:03 pm

@duanes1967
First of all make a backup copy of your Dude database.
Next connect to database with 'SQLite' utility (or try this one: http://www.yunqa.de/delphi/doku.php/pro ... espy/index)
and execute following SQL statements, one by one:
SELECT id FROM "objs" GROUP BY id HAVING (COUNT(id) > 1);
SELECT timeAndServiceID FROM "outages" GROUP BY id HAVING (COUNT(timeAndServiceID) > 1);
SELECT sourceIDandTime FROM "chart_values_raw" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
SELECT sourceIDandTime FROM "chart_values_1day" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
SELECT sourceIDandTime FROM "chart_values_2hour" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
SELECT sourceIDandTime FROM "chart_values_10min" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
Every executed SQL SELECT statement will show you duplicated 'primary key' records in specified table.
Write down values returned for every table and delete duplicated records.
Finally execute :
REINDEX;
VACUUM;
HTH,
Grzegorz | MTCNA, MTCRE, MTCSE | konsultacje MikroTik Warszawa
It is a book about a Spanish guy called Manual. You should read it. - Dilbert
 
duanes1967
just joined
Topic Author
Posts: 8
Joined: Fri Jun 29, 2012 6:29 pm

Re: Where is db cleanup and maintenance info

Mon Sep 10, 2012 7:58 pm

A HUGE thanks to everyone helping.It looks like the db file is corrupted. I think that the .dump worked, so maybe hunting the duplicates from there might work although I'm still not sure my the maps and all config data was missing after I killed the db and then recreated the tables (from .schema) and then re-imported only the objs table. It looked like the blob data contained each item's info about maps, options etc.

Here is what I get from your scripts. The first two table are OK.
==============================
sqlite> delete from chart_values_raw where sourceidandtime in (0, 1);
sqlite> SELECT timeAndServiceID id FROM "outages" GROUP BY id HAVING (COUNT(timeAndServiceID) > 1);
sqlite> SELECT sourceIDandTime id FROM "chart_values_raw" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
0
1
0
0
0
1
1
0
0
0
1
0
1
0
0
Error: database disk image is malformed
sqlite> delete from chart_values_raw where sourceidandtime = '0';
sqlite> delete from chart_values_raw where sourceidandtime = "0";
sqlite> SELECT sourceIDandTime id FROM "chart_values_raw" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
0
1
0
0
0
1
1
0
0
0
1
0
1
0
0
Error: database disk image is malformed
 
ditonet
Forum Veteran
Forum Veteran
Posts: 839
Joined: Mon Oct 19, 2009 12:52 am
Location: Europe/Poland/Konstancin-Jeziorna
Contact:

Re: Where is db cleanup and maintenance info

Mon Sep 10, 2012 10:28 pm

Try to dump database (structure and data) as SQL script, then import it to newly created database.
Next peform all steps described in my previous post.

HTH,
Grzegorz | MTCNA, MTCRE, MTCSE | konsultacje MikroTik Warszawa
It is a book about a Spanish guy called Manual. You should read it. - Dilbert
 
MikrotikOdessa
just joined
Posts: 22
Joined: Wed Feb 14, 2018 11:14 am

Re: Where is db cleanup and maintenance info

Sun Sep 16, 2018 3:11 pm

Hello,
The Dude 6.42.7 running on RB750Gr3
Command /dude vacuum-db works fine in CLI.
But it can not be correctly scheduled. Checkbox "Policy: Dude" can not be saved.
 
jarda
Forum Guru
Forum Guru
Posts: 7602
Joined: Mon Oct 22, 2012 4:46 pm

Re: Where is db cleanup and maintenance info

Wed Sep 19, 2018 4:51 pm

Have you seen any real impact of vacuuming so it makes sense to schedule it for you?
 
MikrotikOdessa
just joined
Posts: 22
Joined: Wed Feb 14, 2018 11:14 am

Re: Where is db cleanup and maintenance info

Sat Jan 19, 2019 11:59 am

Yes.
I am monitoring OpenWRT access points interfaces.
OpenWrt 18.06-SNAPSHOT r7407-1cd945ea22

Without periodic vacuuming there "ghost" interfaces occurs like (42949672985) instead of normal like wlan0, wlan1
After vacuuming all OK.
 
billjellis
newbie
Posts: 36
Joined: Mon Dec 15, 2014 11:04 pm

Re: Where is db cleanup and maintenance info

Mon Feb 18, 2019 12:50 am

Hi All,

I have a Dude server that is sitting at 8 GB running. What is the best way to purge old data?

Bill
 
User avatar
amt
Long time Member
Long time Member
Posts: 526
Joined: Fri Jan 16, 2015 2:05 pm

Re: Where is db cleanup and maintenance info

Tue Apr 16, 2019 9:36 am

Hi All,

I have a Dude server that is sitting at 8 GB running. What is the best way to purge old data?

Bill
any solution ? I'm also wanting to clean old data
 
msatter
Forum Guru
Forum Guru
Posts: 1200
Joined: Tue Feb 18, 2014 12:56 am
Location: Netherlands / Nīderlande

Re: Where is db cleanup and maintenance info

Tue Apr 16, 2019 10:52 am

The Wiki on this:

https://wiki.mikrotik.com/wiki/Manual:T ... /db_vacuum

Also have a look at this script to backup and vacuum:

https://github.com/sayajin101/Dude-Backup-Script
Two RB760iGS (hEX S) in series. One does PPPoE and both do IKEv2.
Running:
RouterOS 6.46Beta / Winbox 3.19 / MikroTik APP 1.3.2
Having an Android device, use https://github.com/M66B/NetGuard/releases (no root required)
 
User avatar
amt
Long time Member
Long time Member
Posts: 526
Joined: Fri Jan 16, 2015 2:05 pm

Re: Where is db cleanup and maintenance info

Tue Apr 16, 2019 5:57 pm

The Wiki on this:

https://wiki.mikrotik.com/wiki/Manual:T ... /db_vacuum

Also have a look at this script to backup and vacuum:

https://github.com/sayajin101/Dude-Backup-Script
Hello,
vacumm not helped. I found another solutin and it's great now :)

here is a solution;
http://www.mtin.net/blog/cleaning-the-dude-database/

Who is online

Users browsing this forum: No registered users and 4 guests