Menu
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"
702
To force MySQL to start using new log files, flush the logs. Log flushing occurs when you execute a FLUSH LOGS statement or a mysqladmin flush-logs, mysqladmin refresh, mysqldump --flush-logs, or mysqldump --master-data command.
If you wanted to delete a number of rows within a range, you can use the AND operator with the BETWEEN operator. DELETE FROM table_name WHERE column_name BETWEEN value 1 AND value 2; Another way to delete multiple rows is to use the IN operator.
To permanently remove a table, enter the following statement within the MySQL shell: DROP TABLE table1; Replace table1 with the name of the table you want to delete. The output confirms that the table has been removed.
The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.
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';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
