I've enabled slow query logging to the mysql.slow_log table, like this:
log_output = TABLE
general_log = 0
slow_query_log = 1
long_query_time = 5
I'm getting queries in the mysql.slow_log table now, which is awesome. But my question is
this: how does one "prune" that log file, without doing a "truncate table slow_log"? I
want to just delete records that were added more than 30 days ago only. Is that
possible? I see that the table is a CSV file by default, and I've switched it to a
MyISAM table but that did not help; I still get the following error when tying to prune
mysql> delete from slow_log where start_time < date_sub(now(), interval 30 day);
ERROR 1556 (HY000): You can't use locks with log tables.
Is it possible to print this table this way?
Also, a suggestion: it would be more helpful (I think) if the user_host column was broken
up into two separate columns, "user" and "host", and if those values would match exactly
the data in the mysql.user table so that the two tables are easier to correlate.
Baskin School of Engineering
UC Santa Cruz
|• Pruning the slow_log Table||Tim Gustafson||10 Mar|