Bug #17456 Error message trying to delete records from general_log or slow_log
Submitted: 16 Feb 2006 13:57 Modified: 3 May 2006 7:42
Reporter: Markus Popp Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Logging Severity:S4 (Feature request)
Version:5.1.6 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[16 Feb 2006 13:57] Markus Popp
Description:
Trying to delete records from mysql.general_log or mysql.slow_log, I get the error message:

ERROR 1532 (HY000): You can't write-lock a log table. Only read access is possible.

How to repeat:
mysql> use mysql
Database changed
mysql> DELETE FROM general_log
    ->   WHERE command_type != 'Connect'
    ->   AND event_time > DATE_SUB(now(), INTERVAL 1 DAY);
ERROR 1532 (HY000): You can't write-lock a log table. Only read access is possible.
mysql> DELETE FROM slow_log
    ->   WHERE start_time > DATE_SUB(now(), INTERVAL 1 DAY);
ERROR 1532 (HY000): You can't write-lock a log table. Only read access is possible.

Suggested fix:
It should be possible to delete records from both log tables.
[16 Feb 2006 15:08] Miguel Solorzano
Thank you for the bug report.
[20 Feb 2006 11:14] Konstantin Osipov
Markus: you can't delete some records, leaving others intact. TRUNCATE should work fine.
[21 Feb 2006 13:14] Markus Popp
That's the current situation, but this feature would be a lot more useful if it would be possible to delete records from the log tables.
[21 Feb 2006 13:37] Petr Chardin
Markus: This is a limitation, imposed by the CSV engine implementation
details. The question whether we will releive this is still open. What
applications do you see for delete over the log tables? Do you think that
UPDATE is also useful?
[21 Feb 2006 14:03] Markus Popp
Log tables can grow extremely large over time and in some occasions it might be interesting to only keep some of the information in the log tables (e.g. who logged in) and delete the rest for example by the new event scheduling feature (that would make the combination of these two features extremely powerful).

However, I know that there are workarounds for this. If it's rather difficult to allow deleting records from the log tables, we could also modify this bug report to make it a feature request, maybe for MySQL 5.2?!
[21 Feb 2006 17:40] Petr Chardin
Marcus, just leave the bugreport as is. The problem is noticed.
But I'm afraid I cannot give a version estimation at the moment.
You'll get a message from bugsdb, as soon as I have more info
on this issue. Thank you for your time!
[28 Aug 2009 9:17] Jason Luo
How about it? I need to use this feature in my program.

If can not delete from general_log while enable general_log, I think we should allow the deletion while without using the general_log.
[25 Feb 2010 16:19] Michael Miller
> What applications do you see for delete over the log tables? Do you think that
UPDATE is also useful?

In my case I am seeing queries showing up in the slow log that are being caused by the mysql dump we do for our backups.  Due to the table size these are queries I just have to live with being slow.  So it is useful to be able to remove these queries so that I can do analysis on the queries I can actually do something about.