List:General Discussion« Previous MessageNext Message »
From:Rafał Radecki Date:May 14 2012 9:48am
Subject:MySQL 5.1.59 - slow_log purge problem.
View as plain text  
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.

Thread
MySQL 5.1.59 - slow_log purge problem.Rafał Radecki14 May