Re: deleting a large number of rows - pek...@mysql.com - com.mysql.lists.cluster

5 messages in com.mysql.lists.clusterRe: deleting a large number of rows

From	Sent On	Attachments
Paul Maunders	08 Jun 2006 09:15
Ross McFarland	08 Jun 2006 10:38
pek...@mysql.com	08 Jun 2006 11:15
Martin Skold	09 Jun 2006 00:22
Paul Maunders	09 Jun 2006 06:36

Subject:	Re: deleting a large number of rows
From:	pek...@mysql.com (pek...@mysql.com)
Date:	06/08/2006 11:15:01 AM
List:	com.mysql.lists.cluster

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.