Hi all.
I write a script to delete rows from slow_log older than 2 weeks.
#!/bin/bash
if [ $# -ne 1 ]; then
echo "Usage: $0 mysql_config_file"
exit 1
fi
SELECTQUERY="select * from slow_log where start_time <
DATE_ADD(NOW(),INTERVAL - 2 WEEK)"
DELETEQUERY="delete from slow_log where start_time <
DATE_ADD(NOW(),INTERVAL - 2 WEEK)"
CONFIG_FILE="$1"
HOSTNAME="$(hostname | awk -F'.' '{print $1}')"
INSTANCENAME="$(grep datadir $CONFIG_FILE | awk -F'/' '{print $4}'|uniq)"
LOG_FILE="/var/log/${INSTANCENAME}-${HOSTNAME}-slowlog-clean.log"
echo "***" >> $LOG_FILE
echo "$(date +'%Y-%m-%d %H:%M:%S %Z') Started cleaning..." >> $LOG_FILE
echo "Before there are $(mysql --defaults-file=${CONFIG_FILE} mysql -e
\"${SELECTQUERY}\" | wc -l) entries older than 2 weeks" >> $LOG_FILE
mysql --defaults-file=${CONFIG_FILE} mysql -e \"$DELETEQUERY\" > /dev/null
2>&1
echo "After there are $(mysql --defaults-file=${CONFIG_FILE} mysql -e
\"${SELECTQUERY}\" | wc -l) entries older than 2 weeks" >> $LOG_FILE
echo "$(date +'%Y-%m-%d %H:%M:%S %Z') Stopped cleaning..." >> $LOG_FILE
echo "***" >> $LOG_FILE
When I issue the delete statement I get:
mysql --defaults-file=/etc/my.cnf mysql -e "delete from slow_log where
start_time < DATE_ADD(NOW(),INTERVAL - 2 WEEK)"
ERROR 1556 (HY000) at line 1: You can't use locks with log tables.
mysql --defaults-file=/etc/my.cnf mysql -e "select * from slow_log where
start_time < DATE_ADD(NOW(),INTERVAL - 4 WEEK) limit 1"
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+
| start_time | user_host | query_time | lock_time
| rows_sent | rows_examined | db | last_insert_id | insert_id | server_id |
sql_text |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+
| 2011-10-09 23:06:17 | root[root] @ localhost [] | 00:00:06 | 00:00:00
| 1 | 0 | | 0 | 0 | 72
| select sleep(6) |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+
Above select works fine.
How can I resolve the error? What is the proper way to clean slow_log?
Best regards,
Rafal Radecki.
