Insane sql logging

[Matteo Suppo]

Sometimes people ask for strange features, like "I want to log every database query except select".

There will be drawbacks, of course: it will be slower, for example, but they won't care.

It happened to us, and we had to ship this insanity:

import logging

from logging.handlers import RotatingFileHandler

from django.db.backends import BaseDatabaseWrapper

from django.db.models.signals import pre_save, post_save, pre_delete, post_delete

from django.dispatch import receiver

from datetime import datetime

from django.conf import settings

def patch_cursor(self):

    """ Monkey Patch BaseDatabaseWrapper to always use the debug cursor """

    self.validate_thread_sharing()

    return self.make_debug_cursor(self._cursor())

BaseDatabaseWrapper.cursor = patch_cursor

@receiver(pre_delete)

@receiver(pre_save)

def member_pre_save(sender, **kwargs):

    l = logging.getLogger('django.db.backends')

    l.setLevel(logging.DEBUG)

    if len(l.handlers) <= 0:

        handler = RotatingFileHandler(settings.BACKUP_FILENAME,

                                      maxBytes=settings.BACKUP_MAXBYTES)

        l.addHandler(handler)

        l.debug(datetime.now())

@receiver(post_delete)

@receiver(post_save)

def member_post_save(sender, **kwargs):

    l = logging.getLogger('django.db.backends')

    l.removeHandler(l.handlers[0])

Of course now they told us they want to log the IP of the machine who triggered the query, so we'll have to use a different approach. Sigh.

[Addy Yeow]

Why not do this at database level?
e.g. using http://dev.mysql.com/doc/refman/5.1/en/query-log.html

> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/django-users/-/voMGlGJ3UqgJ.
> To post to this group, send email to django...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.

[Matteo Suppo]

Good question. We decided not to do it at database level because we didn't want to write every select on the file, but only the update/insert/delete.

Probably it was better that way, though, and clean the file later.

Matteo Suppo - Social Media Badass

"C'è sempre un altro modo"

"Ho l'80% di probabilità di fare qualsiasi cosa"

2013/1/15 Addy Yeow <aye...@gmail.com>

[Sam Solomon]

The word "Insane" caught my eye and lo and behold it was relevant to what we've done. We actually do this for security reasons and it does help with debugging sometimes (we actually log all queries, not just INSERT/UPDATE/DELETE).

We also store fk to user (with a pre_delete hook to unattach users and save the old user's name/email address to a text field should someone delete the user), request path, method, referrer, ip and timestamp. 

From a high level we basically extended the database wrapper to store the queries as it would if you have DEBUG=True set and then use middleware to store it to the database.

For performance reasons all of the queries are dumped and sent to a backup server every hour and after successful transfer, the dumped records are deleted from the production server. If performance becomes an issue (which it may soon) we will likely look into caching the queries on the production server and then creating a cron job to move the entries from cache onto the backup server periodically so users don't have to wait for a database write.

If you need help you can email me off list or there is a lot of interest, I may be able to open source some or all of it when I have time.

[Trevor Joynson]

We just parse MySQL binary logs as they only contain modification queries. You can dump the real SQL query out petty easily. I can send you the base of our dump script if you want.