How to flush data from mysql.slow_log table in mysql?

Question

Hi i am working on MySQL version 5.5, can somebody please help me to clear/flush data from mysql.slow_log tables in mysql ?

Keloid

See this – Eggplant

Answer 1

If you are on linux

> mysql -uroot -p
> enter your password
> use mysql;
> delete from slow_log;

It will give you an error that you can't lock log tables. Work around is, run the following queries:

SET GLOBAL slow_log= 'OFF';
RENAME TABLE slow_log TO general_log_temp;
DELETE FROM `general_log_temp`;
RENAME TABLE general_log_temp TO slow_log ;
SET GLOBAL slow_log = 'ON';

Taken from "DELETE old rows from Mysql General Log Table"

Update:

You can truncate the table like TRUNCATE mysql.slow_log as mentioned by Renato Liibke

Barrows

when deleting records from slow_log getting error "Error Code:1556.You can't use locks with log tables." – Keloid

got another error DELETE FROM mysql.general_log_temp Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences – Keloid

unknown system variable "slow_log"? – Jacobine

@LennartRolland that's a table. select mysql database using use mysql and there is a table with name, you can verify it by using this query show tables like '%slow_log%' after selecting the mysql database – Barrows

Answer 2

You can avoid locking problems without disabling the slow log by using the built-in rotate function:

CALL mysql.rds_rotate_slow_log;
DELETE FROM mysql.slow_log_backup;

This will swap the 'slow_log' and 'slow_log_backup' tables, moving all of the data you want to clear to the non-locked 'slow_log_backup' table, which will happily take the DELETE FROM for data purging purposes.

You can also just invoke rotation twice:

CALL mysql.rds_rotate_slow_log;
CALL mysql.rds_rotate_slow_log;

Migraine

Quite straightforward and useful approach, dude. I just didn't figure out why you got no up votes until now. Thanks a lot! – Indecisive

+1 Exactly what I was looking for! would the question be tagged with RDS you would get lot's of upvotes. – Crosspollinate

This is beautiful! Thanks for sharing this little gem, I had no idea RDS had their own stored procedures. – Cribb

mysql> CALL rds_rotate_slow_log; Then the following error shows: ERROR 1305 (42000): PROCEDURE mysql.rds_rotate_slow_log does not exist; MySQL version 14.14 Distrib 5.7.20 – Thresathresh

Answer 3

TRUNCATE mysql.slow_log

From Mysql Documentation:

TRUNCATE TABLE is a valid operation on a log table. It can be used to expire log entries.

Circumgyration

shouldn't this answer be on the top? – Permutation

Answer 4

For me this works:

SET GLOBAL slow_query_log= 'OFF';
RENAME TABLE slow_log TO general_log_temp;
DELETE FROM `general_log_temp`;
RENAME TABLE general_log_temp TO slow_log ;
SET GLOBAL slow_query_log = 'ON';

Cocainism