Purging Monitoring Records Older than 30 Days is Problematic

Diladele Web Safety is supposed to remove records older than 30 days from the monitoring database stored in /opt/qlproxy/var/db/monitor.sqlite. This is done to reduce the amount of disk space this database allocates. This is especially important when you are monitoring all objects (which is NOT recommended with SQLite database activated by default).

When 30 days pass you might see the following error in the report log (note here it says 1 day because I am reproducing the problem):

2014-11-27 16:01:18,815 Purging events older than 1 days (2014-11-26 16:01:18.815857)...
Traceback (most recent call last):
File "/opt/qlproxy/var/console/import_all.py", line 190, in
File "/opt/qlproxy/var/console/import_all.py", line 185, in main
upload(args.leave, resolve)
File "/opt/qlproxy/var/console/import_all.py", line 123, in upload
File "/opt/qlproxy/var/console/import_all.py", line 70, in purge
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/models/query.py", line 468, in delete
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/models/deletion.py", line 260, in delete
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/models/query.py", line 479, in _raw_delete
sql.DeleteQuery(self.model).delete_qs(self, using)
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/models/sql/subqueries.py", line 85, in delete_qs
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/models/sql/compiler.py", line 786, in execute_sql
cursor.execute(sql, params)
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/backends/util.py", line 69, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/backends/util.py", line 53, in execute
return self.cursor.execute(sql, params)
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/utils.py", line 99, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/backends/util.py", line 53, in execute
return self.cursor.execute(sql, params)
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/backends/sqlite3/base.py", line 452, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: too many SQL variables

This happens because of the way Django handles deletion of filtered objects and it turns to be a is well known issue (unfortunately not to us 😦 ) as described in for example http://stackoverflow.com/questions/7106016/too-many-sql-variables-error-in-django-witih-sqlite3. The solution is pretty simple and requires deleting objects in series. This is already fixed in current 4.0 build and will be included into upcoming beta.

We are now preparing the fix for users of 3.4 stable… BTW those who use MySQL storage as described http://docs.diladele.com/administrator_guide_4_0/traffic_monitoring/store_monitoring_data_in_mysql.html are NOT affected.

Best regards and again sorry for this mess,
Diladele Dev Team.

About sichent

This entry was posted in Diladele, MySQL and tagged . Bookmark the permalink.