Logging enabled
I enabled logging using:
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
All executed queries was logging to mysql.general_log
table. It is fine.
Attempt to clear the table
Then the table became large and I wanted to delete all records from the table. I executed:
DELETE FROM general_log
But this caused to an error that says I cannot lock log tables. So, I dropped the table after disabling logging:
SET GLOBAL general_log = 'OFF';
DROP TABLE general_log;
I hope that enabling logging again will create the table, but I couldn't enable it. When I execute this:
SET GLOBAL general_log = 'ON';
It gives this error:
Table 'mysql.general_log' doesn't exist
Questions
- How to create
mysql.general_log
again? - How to clear
mysql.general_log
safely and in a painless way?
Recreate:
Clear table:
Ok, having first hand experience, this is what worked for me, if your table gets corrupted by any reason whatsoever, works with MySQL 5.6.11
For 1.:
For 2.:
MySQL 5.7.18
I have dropped the
mysql.general_log
table too, but the other answers did not help me.Then, I noticed errors about the table structure cause it's a bit different on
MySQL 5.7.18
, so I have changed some field and now it works.In case anybody needs it:
This is the updated version, the 2012 answers do not work anymore: