5 messages in com.mysql.lists.clusterRe: deleting a large number of rows
FromSent OnAttachments
Paul Maunders08 Jun 2006 09:15 
Ross McFarland08 Jun 2006 10:38 
pek...@mysql.com08 Jun 2006 11:15 
Martin Skold09 Jun 2006 00:22 
Paul Maunders09 Jun 2006 06:36 
Subject:Re: deleting a large number of rows
From:Martin Skold (Mart@mysql.com)
Date:06/09/2006 12:22:38 AM
List:com.mysql.lists.cluster

Hi!

In next release (with fix for Bug #18864) TRUNCATE TABLE will be several magnitudes faster and the lock timeout problem should be gone.

BR -- Martin pek@mysql.com wrote:

Hi

I've just created a table with about 100,000 rows in it. I want to empty this table, but when I do it with TRUNCATE I get an error:

mysql> TRUNCATE TABLE `user_ourproperty`;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Probably your table has blob attributes. See bug#19201. The bug is fixed in these (upcoming?) versions:

4.1.20 5.0.22 5.1.11

mysql> DELETE FROM user_ourproperty WHERE 1 LIMIT 1000000;

ERROR 1297 (HY000): Got temporary error 233 'Out of operation records

This is normal, each table row and each blob attribute part (2k) needs on operation record when doing _transactional_ truncate ie. DELETE. Only way is to increase MaxNoOfConcurrentOperations.