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: 1614
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: 1614
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: 829
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 | 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: 1614
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: 829
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 | 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: 829
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 | konsultacje MikroTik Warszawa
It is a book about a Spanish guy called Manual. You should read it. - Dilbert
 
MikrotikOdessa
just joined
Posts: 12
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: 7475
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?

Who is online

Users browsing this forum: No registered users and 4 guests