MySQL Bugs: #17456: Error message trying to delete records from general_log or slow

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

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.

Thank you for the bug report.

Markus: you can't delete some records, leaving others intact. TRUNCATE should work fine.

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.

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?

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?!

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!

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.

> 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.