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
main()
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
purge()
File "/opt/qlproxy/var/console/import_all.py", line 70, in purge
events.delete()
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/models/query.py", line 468, in delete
collector.delete()
File "/usr/lib/python2.7/site-packages/Django-1.6.7-py2.7.egg/django/db/models/deletion.py", line 260, in delete
qs._raw_delete(using=self.using)
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
self.get_compiler(using).execute_sql(None)
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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s