Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Symptom:

Sometimes there is a need to remove old log entries from the Men & Mice database. This can be because the SQLite database file has grown too large and is slowing down the system, or for privacy reasons.

Problem:

The Men & Mice Suite can be configured to purge log entries automatically (see system settings -> logging). However, sometimes the administrator needs to manually trim the log entries in the database.

The following steps use the SQLite shell, e.g. for Windows from the sqlite download site:

...

https://sqlite.org/

...

2020/sqlite-

...

tools-win32-x86-

...

3310100.zip

To use the SQLite shell, download and unzip it.

Solution

Men & Mice object history and messages

1) Stop the M&M Central process and take a backup of the mmsuite.db file

2) open the mmsuite.db file with the sqlite command line tool and test the amount of entries which would be deleted by changing the date and running the following SQL select statement (in the example it is set to the first of Aug 2011 - so all entries older than that will be included in the select statement). This gives you an idea how many entries would be included in the deletion run (see step 4):


select count(*) from mm_eventitems where mm_eventitems.eventid in (select eventid from mm_events where datetime(timestamp,"unixepoch") < datetime("2011-08-01 00:00:00"));

and for the mm_messages table:


select count(*) from mm_events where datetime(timestamp,"unixepoch") < datetime("2011-08-01 00:00:00");


3) Optional: Dump the entries into files by running the following commands (this will
dump the message/event items older than 1 Aug 2011).


.mode CSV
.header on
.output mm_eventitems.csv
select * from mm_eventitems where mm_eventitems.eventid in (select eventid from mm_events where datetime(timestamp,"unixepoch") < datetime("2011-08-01 00:00:00"));

.output mm_events.csv
select * from mm_events where datetime(timestamp,"unixepoch") < datetime("2011-08-01 00:00:00");

.output mm_messages.csv
select * from mm_messages where datetime(timestamp,"unixepoch") < datetime("2011-08-01 00:00:00");

.output stdout
.header off


4) Delete the entries from the database (alter the datetime to the one you've found by running the select statements in step 1):


delete from mm_eventitems where mm_eventitems.eventid in (select eventid from mm_events where datetime(timestamp,"unixepoch") < datetime("2011-08-01 00:00:00"));

delete from mm_events where datetime(timestamp,"unixepoch") < datetime("2011-08-01 00:00:00");

delete from mm_messages where datetime(timestamp,"unixepoch") < datetime("2011-08-01 00:00:00");


5) Run vacuum to purge the free pages, which should shrink the DB file significantly:


vacuum;
analyze;


6) Quit the SQLite command line tookit and start the M&M Central service:


.quit

Men & Mice DHCP Lease history

The same procedure can be used to purge the DHCP lease history, which is usually a huge burden on a SQLite database, and not recommended.
1) Stop the M&M Central process and take a backup of the mmsuite.db file

2) open the mmsuite.db file with the sqlite command line tool and test the amount of entries which would be deleted by changing the date and running the following SQL select statement (in the example it is set to the first of Aug 2011 - so all entries older than that will be included in the select statement). This gives you an idea how many entries would be included in the deletion run (see step 4):


select count(*) from mm_leaseeventitems where datetime(timestamp,"unixepoch") < datetime("2011-08-01 00:00:00");


3) Optional: Dump the entries into files by running the following commands (this will
dump the message/event items older than 1 Aug 2011).


.mode CSV
.header on
.output mm_leaseeventitems.csv
select * from mm_leaseeventitems where datetime(timestamp,"unixepoch") < datetime("2011-08-01 00:00:00");

.output stdout
.header off


4) Delete the leaseeventitems entries from the database (alter the datetime to the one you've found by running the select statements in step 1):


delete from mm_leaseeventitems where datetime(timestamp,"unixepoch") < datetime("2011-08-01 00:00:00");


5) Optional: Dump the lease entries to a file


.mode CSV
.header on
.output mm_leases.csv
select * from mm_leases where leaseid not in (select distinct leaseid from mm_leaseeventitems);

.output stdout
.header off


6) Delete the leases items entries from the database (alter the datetime to the one you've found by running the select statements in step 1)


delete from mm_leases where leaseid not in in (select distinct leaseid from mm_leaseeventitems);


7) Run vacuum to purge the free pages, which should shrink the DB file significantly:


vacuum;
analyze;


8) Quit the SQLite command line tookit and start the M&M Central service:


.quit