Getting Error while deleting mysql.general_log table

Question

5, getting error while executing below query,

delete from mysql.general_log

Error getting after executing above query,

Error Code: 1556
You can't use locks with log tables.

can somebody please help me to resolve above error.

Answer 1

To clear all Data from the Table you can use

TRUNCATE mysql.general_log

Answer 2

I think you can first rename the table and then try to delete it and then revert back ie., change the table name back to the original one.

The second alternative solution is this:

You may need to disable “–lock-tables” option on your dump statement ie –lock-tables=0 . What my assumption is when we use “–lock-tables” option, the current table which is being backed up will be operating on “read only” mode to avoid further write operations during the time. The cached query which containing “write’ operation will be executed later once after the table dump has been done. Pls note that skipping this option is not recommended in production mode.

I made this changes because of since the Amazon RDS does allow only remote access even though I set daily backup and retention period in RDS web interface properly. This custom backup is taken for my surety even though AWS RDS handle it better.

$mysqldump –all-database –lock-tables=0 -uusername -h hostname -p’password’ -B | bzip2 > /backup/db/domain-00-00.2012.bz2