Re: deleting a large number of rows - Paul Maunders - 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:	Paul Maunders (pa...@fubra.com)
Date:	06/09/2006 06:36:19 AM
List:	com.mysql.lists.cluster

Great, thanks for all your help guys.

Pekka > I have a tinytext field in this table, so I guess that was causing the problem.

FYI, I was using version 5.0.21 - will look forward to the new version.

Paul

Martin Skold wrote:

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:

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.

Paul Maunders

Operations Director Fubra Limited

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Fubra Ltd. Finally, the recipient should check this email and any attachments for the presence of viruses. Fubra Ltd accepts no liability for any damage caused by any virus transmitted by this email.