16

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

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

This is the query I ran:

DELETE FROM `general_log` WHERE `event_time` < "2014-01-25 14:05"
Buttle Butkus
  • 9,206
  • 13
  • 79
  • 120
  • 1
    There are a couple of [bugs filed](http://bugs.mysql.com/bug.php?id=30487) against this. It seems to be a persistent cosmetic error. Did your query actually work? If so, ignore the message. –  Jan 25 '14 at 22:50
  • I have the same issue, but when I try the accepted solution I get: #1017 - Can't find file: '.\XXX@002XXX\general_log.frm' , any ideas on how I can fix this? – shelbypereira Apr 22 '16 at 08:23
  • I'm not sure but are you using a table or a file for the error output? See here: https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html – Buttle Butkus Apr 22 '16 at 08:31
  • 1
    I had to hunt around a bit there were a couple of additional issues, the Rename table in techhero was failing due to permissions. I also found that I had a very old general_log table, which was no longer working. I updated it using : http://stackoverflow.com/questions/12247063/mysql-i-dropped-general-log-table and everything works now. – shelbypereira Apr 22 '16 at 12:21

1 Answers1

32

You can rename the table, perform the cleaning as needed, then revert 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';
Community
  • 1
  • 1
techhero
  • 1,074
  • 11
  • 11
  • 1
    The above worked for me but only after I turned-off logging as per the docs: `SET GLOBAL general_log = 'OFF';` – Sam Azer Dec 09 '14 at 19:26
  • 6
    you may want to select the right db also ,start with `use mysql` then do what's in the answer – zizoujab Sep 28 '16 at 11:09