DELETE old rows from the common mysql log table (MyISAM not CSV)

Question

I wanted to delete old rows from mysql.general_log table, but ran into this error:

 #1556 - You can't use locks with log tables. 

This is the query that I executed:

 DELETE FROM `general_log` WHERE `event_time` < "2014-01-25 14:05" 

Answer 1

You can rename the table, perform the cleanup as necessary, and then return the table name again.

Example:

 SET GLOBAL general_log = 'OFF'; RENAME TABLE general_log TO general_log_temp; DELETE FROM `general_log_temp` WHERE `event_time` < DATE(NOW()); RENAME TABLE general_log_temp TO general_log; SET GLOBAL general_log = 'ON';