Problems with database clean-up

[Mathias Weiersmueller]

I want to remove old entries from the NetDB database. When issuing

 netdbctl.pl -dt -d 7

I get the following output:

NetDB Delete Statistics over 7 days
---------------------------------
MAC Entries:      151
ARP Entries:      121
Switch Entries:   4096
Registrations:    Unknown when using filter
Total Deletable Rows: 4368

I understand I should be able to have the above entries removed by issung:

netdbctl.pl -dm -d 7

Here's the output:

Querying database for data to delete older than 7 days...
No data found to delete.

=> The DB query was in this case:

SELECT * FROM mac WHERE lastseen < '2013-08-06T15:15:56' AND lastip like '%' ORDER BY lastseen

(the query is defined in NetDB.pm as $selectDeleteMacs_h_string)

=> ... which returned zero rows.

Looking deeper into it shows that the 151 rows which should be deleted have the value NULL in column lastip. NULL doesn't match '%' in a LIKE statement.

I think the queries should not contain the part "AND lastip like '%'" when not using an IP filter.

Just my 50 cents... and, hey, thanks for the good work!

[Jonathan Yantis]

Thanks for finding this Mathias, I'll have to tweak the query for null lastip data. I'll added this to my todo list. I have 5mil rows in my database, so it's easy to miss some data.

Jonathan

On Aug 13, 2013, at 10:01 AM, Mathias Weiersmueller mattiwei@users.sf.net wrote:

I want to remove old entries from the NetDB database. When issuing

netdbctl.pl -dt -d 7
I get the following output:

=====================================
NetDB Delete Statistics over 7 days

---

MAC Entries: 151
ARP Entries: 121
Switch Entries: 4096
Registrations: Unknown when using filter
Total Deletable Rows: 4368
=====================================

I understand I should be able to have the above entries removed by issung:

netdbctl.pl -dm -d 7
Here's the output:

Querying database for data to delete older than 7 days...
No data found to delete.
=====================================

=> The DB query was in this case:

SELECT * FROM mac WHERE lastseen < '2013-08-06T15:15:56' AND lastip like '%' ORDER BY lastseen
(the query is defined in NetDB.pm as $selectDeleteMacs_h_string)

=> ... which returned zero rows.

Looking deeper into it shows that the 151 rows which should be deleted have the value NULL in column lastip. NULL doesn't match '%' in a LIKE statement.

I think the queries should not contain the part "AND lastip like '%'" when not using an IP filter.

Just my 50 cents... and, hey, thanks for the good work!

Problems with database clean-up

Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/netdbtracking/discussion/939989/

To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

[Mathias Weiersmueller]

There's a neat way of handling NULL values in WHERE clauses... I had the chance to talk with a DBA about this. He said "best practice" is this:

instead of

SELECT * FROM mac WHERE lastseen < '$search_dt' AND lastip like ? ORDER BY lastseen

use

SELECT * FROM mac WHERE lastseen < '$search_dt' AND IFNULL(lastip,"abc") like ? ORDER BY lastseen

Here's the definition of IFNULL from the MySQL manual:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value

I can send you a patch to NetDB.pm containing the changed statements if you're interested.

[Mathias Weiersmueller]

I checked NetDB.pm and found three statements which had to be changed. In addition, I found one query which (I guess) never worked because it was looking for the column "ip" in table "supermac", but the column is actually called "lastip".

I've attached a patch.

Here are the instructions:
- check with md5sum that the NetDB.pm file has the following hash value so we're sure we're patching the right file:
c906ee00bdc60ef95168a2661fc8c516 NetDB.pm
- cp NetDB.pm NetDB-orig.pm (to keep the original safe)
- copy the patch file (NetDB.pm.patch) into the same directory as NetDB.pm
- execute:
patch NetDB.pm < NetDB.pm.patch
- you're done!

[Jonathan Yantis]

Mathias,

Thanks for the patch. I did have to escape the \"ABC\" quotes after applying the patch but afterwards it seems to be good to go. I applied it to both the v1.11 branch and the current development version.

If you run in to anymore issues let me know. I'm going to try and get a new major version out this fall with a few new features I have in the works.

Jonathan

[Abhay Karki]