Insane sql logging
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.
--
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-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
Re: Insane sql logging
Why not do this at database level?
e.g. using http://dev.mysql.com/doc/refman/5.1/en/query-log.html
On Tue, Jan 15, 2013 at 9:35 PM, Matteo Suppo wrote:
> 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.
>
> --
> 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-users@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
Re: Insane sql logging
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
> Why not do this at database level?
> e.g. using http://dev.mysql.com/doc/refman/5.1/en/query-log.html
>
> On Tue, Jan 15, 2013 at 9:35 PM, Matteo Suppo
> wrote:
> > 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.
> >
> > --
> > 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-users@googlegroups.com.
> > To unsubscribe from this group, send email to
> > django-users+unsubscr...@googlegroups.com.
> > For more options, visit this group at
> > http://groups.google.com/group/django-users?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-users@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
Re: Insane sql logging
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. -- 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/-/GxdmyaoQkQ0J. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
Re: Insane sql logging
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.
On Jan 15, 2013 10:26 AM, "Matteo Suppo" wrote:
> 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
>
>> Why not do this at database level?
>> e.g. using http://dev.mysql.com/doc/refman/5.1/en/query-log.html
>>
>> On Tue, Jan 15, 2013 at 9:35 PM, Matteo Suppo
>> wrote:
>> > 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.
>> >
>> > --
>> > 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-users@googlegroups.com.
>> > To unsubscribe from this group, send email to
>> > django-users+unsubscr...@googlegroups.com.
>> > For more options, visit this group at
>> > http://groups.google.com/group/django-users?hl=en.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django users" group.
>> To post to this group, send email to django-users@googlegroups.com.
>> To unsubscribe from this group, send email to
>> django-users+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/django-users?hl=en.
>>
>>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-users@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
