Activities

November 2012
M T W T F S S
« Oct   Dec »
 1234
567891011
12131415161718
19202122232425
2627282930  

MySQL : You can’t use locks with log tables

I got “You can’t use locks with log tables” error when I’m trying to delete contents from the slow_log table which is always be opened on MySQL Engine in a MySQL 5.5 server. My idea is take a table dump and move the data to another remote DB server for later analysis.

I also get the similar errors while I’m dumping only the data from this “slow_log table”

My purpose is to delete all the records from “slow-log” table which a MySQL_built-in table where slow sql queries are being logged.

Solution : Use “truncate” command to delete the table. truncate command destroy the table and recreated it automatically.

[root@SQL04 temp]# mysql -e 'use mysql; TRUNCATE TABLE  slow_log;'

here is the script which will move the slow query log data from MySQL production server to another remote MySQL server.

 mysqldump --lock-tables=0  --no-create-info  mysql slow_log -r /sqldata/temp/slow_log.sql
mysql -h 192.168.10.15  -udbauser -p'mypass' -e 'use  slow_log; source /sqldata/temp/slow_log.sql;'
echo -n > /sqldata/temp/slow_log.sql
mysql -e 'use mysql; TRUNCATE TABLE  slow_log;'

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>