I've been running NetDB for over 2 years and have never thought to schedule dataDeletion.pl to run in the crontab to prune out old entries. I am now being contacted by the sysadmin responsible for the server saying that the partition the MySQL database is running in has filled up.
I ran a qry to understand how many records I had older than 120 days:
Things appeared to be going fine near the beginning:
Thu Apr 20 11:35:24 2017: dataDeletion.pl(32532): Starting database cleanup of anything older then 120 days.
Thu Apr 20 11:36:01 2017: dataDeletion.pl(32532): |Notice|: Deleting 1399525 MAC addresses older then 120 days from database...
At this point the qry ran for several hours - I checked status by issuing a 'show processlist' in the SQL CLI.
Eventually I got distracted and my session timed out. I later checked the SQL processes and nothing was running.
I rechecked my record count of 120 day old records and still saw 8.5M eligible rows. Had the deletion completed successfully this number should have been zero.
I reran the deletion script and got alot of errors:
phx-pvnetdb-001:/opt/netdb # ./dataDeletion.pl -d 120 -v
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): Starting database cleanup of anything older then 120 days.
DBD::mysql::st execute failed: Can't create/write to file '/var/tmp/mysql.kDkuK7/MYgFiKUv' (Errcode: 2) at /usr/lib/perl5/5.10.0/NetDB.pm line 3074.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /usr/lib/perl5/5.10.0/NetDB.pm line 3076.
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): |Notice|: No MAC addresses older then 120 days found in database
DBD::mysql::st execute failed: Can't create/write to file '/var/tmp/mysql.kDkuK7/MYusnFIg' (Errcode: 2) at /usr/lib/perl5/5.10.0/NetDB.pm line 3152.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /usr/lib/perl5/5.10.0/NetDB.pm line 3154.
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): |Notice|: No switchport entries older then 120 days found in database
DBD::mysql::st execute failed: Can't create/write to file '/var/tmp/mysql.kDkuK7/MYy41Hv2' (Errcode: 2) at /usr/lib/perl5/5.10.0/NetDB.pm line 3189.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /usr/lib/perl5/5.10.0/NetDB.pm line 3191.
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): |Notice|: No WiFi entries older then 120 days found in database
DBD::mysql::st execute failed: Can't create/write to file '/var/tmp/mysql.kDkuK7/MYUto0jO' (Errcode: 2) at /usr/lib/perl5/5.10.0/NetDB.pm line 3115.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /usr/lib/perl5/5.10.0/NetDB.pm line 3117.
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): |Notice|: No ARP entries older then 120 days found in database
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): Removed:
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): 0 MAC addresses
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): 0 switchport entries
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): 0 WiFi entries
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): 0 entries
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): totaling in 0 rows deleted from the NetDB database.
I forced the deletion of abondoned locks and reran again but got the same error. I waited a few days thinking that some process might clean itself up - but upon trying again 20 minutes ago I have the same problem.
I also tried this approach:
phx-pvnetdb-001:/opt/netdb # ./netdbctl.pl -dw -d 120 -v
Mon Apr 24 22:27:44 2017: netdbctl(2118): NetDB Debug Level: 2
Mon Apr 24 22:27:44 2017: netdbctl(2118): Parsing Devices from Big Brother (optional)
Querying database for Wifi data to delete older than 120 days...
DBD::mysql::st execute failed: Can't create/write to file '/var/tmp/mysql.kDkuK7/MYYAy26A' (Errcode: 2) at NetDB.pm line 3189.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at NetDB.pm line 3191.
No data found to delete.
Any hints at what I can try to prune these old records?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I've been running NetDB for over 2 years and have never thought to schedule dataDeletion.pl to run in the crontab to prune out old entries. I am now being contacted by the sysadmin responsible for the server saying that the partition the MySQL database is running in has filled up.
I ran a qry to understand how many records I had older than 120 days:
phx-pvnetdb-001:/opt/netdb # ./netdbctl.pl -dt -d 120
Mon Apr 24 22:26:35 2017: netdbctl(2113): NetDB Debug Level: 2
NetDB Delete Statistics over 120 days
MAC Entries: 1398675
ARP Entries: 1587756
Switch Entries: 5521972
Registrations: Unknown when using filter
Total Deletable Rows: 8508403
I then issued the command to delete all records older than 120 days:
phx-pvnetdb-001:/opt/netdb # ./dataDeletion.pl -d 120 -v
Things appeared to be going fine near the beginning:
Thu Apr 20 11:35:24 2017: dataDeletion.pl(32532): Starting database cleanup of anything older then 120 days.
Thu Apr 20 11:36:01 2017: dataDeletion.pl(32532): |Notice|: Deleting 1399525 MAC addresses older then 120 days from database...
At this point the qry ran for several hours - I checked status by issuing a 'show processlist' in the SQL CLI.
Eventually I got distracted and my session timed out. I later checked the SQL processes and nothing was running.
I rechecked my record count of 120 day old records and still saw 8.5M eligible rows. Had the deletion completed successfully this number should have been zero.
I reran the deletion script and got alot of errors:
phx-pvnetdb-001:/opt/netdb # ./dataDeletion.pl -d 120 -v
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): Starting database cleanup of anything older then 120 days.
DBD::mysql::st execute failed: Can't create/write to file '/var/tmp/mysql.kDkuK7/MYgFiKUv' (Errcode: 2) at /usr/lib/perl5/5.10.0/NetDB.pm line 3074.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /usr/lib/perl5/5.10.0/NetDB.pm line 3076.
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): |Notice|: No MAC addresses older then 120 days found in database
DBD::mysql::st execute failed: Can't create/write to file '/var/tmp/mysql.kDkuK7/MYusnFIg' (Errcode: 2) at /usr/lib/perl5/5.10.0/NetDB.pm line 3152.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /usr/lib/perl5/5.10.0/NetDB.pm line 3154.
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): |Notice|: No switchport entries older then 120 days found in database
DBD::mysql::st execute failed: Can't create/write to file '/var/tmp/mysql.kDkuK7/MYy41Hv2' (Errcode: 2) at /usr/lib/perl5/5.10.0/NetDB.pm line 3189.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /usr/lib/perl5/5.10.0/NetDB.pm line 3191.
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): |Notice|: No WiFi entries older then 120 days found in database
DBD::mysql::st execute failed: Can't create/write to file '/var/tmp/mysql.kDkuK7/MYUto0jO' (Errcode: 2) at /usr/lib/perl5/5.10.0/NetDB.pm line 3115.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /usr/lib/perl5/5.10.0/NetDB.pm line 3117.
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): |Notice|: No ARP entries older then 120 days found in database
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): Removed:
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): 0 MAC addresses
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): 0 switchport entries
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): 0 WiFi entries
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): 0 entries
Thu Apr 20 15:45:07 2017: dataDeletion.pl(1393): totaling in 0 rows deleted from the NetDB database.
I forced the deletion of abondoned locks and reran again but got the same error. I waited a few days thinking that some process might clean itself up - but upon trying again 20 minutes ago I have the same problem.
I also tried this approach:
phx-pvnetdb-001:/opt/netdb # ./netdbctl.pl -dw -d 120 -v
Mon Apr 24 22:27:44 2017: netdbctl(2118): NetDB Debug Level: 2
Mon Apr 24 22:27:44 2017: netdbctl(2118): Parsing Devices from Big Brother (optional)
Querying database for Wifi data to delete older than 120 days...
DBD::mysql::st execute failed: Can't create/write to file '/var/tmp/mysql.kDkuK7/MYYAy26A' (Errcode: 2) at NetDB.pm line 3189.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at NetDB.pm line 3191.
No data found to delete.
Any hints at what I can try to prune these old records?
Got this figured out - had to add the following reference to my /etc/my.conf configuration:
under [mysqld]
tmpdir=/tmp
Then restart the SQL service with:
/etc/init.d/mysql restart
After that I had no problem issuing the deletion command:
phx-pvnetdb-001:/opt/netdb # ./netdbctl.pl -dm -d 120 -v