List:General Discussion« Previous MessageNext Message »
From:Ben Clewett Date:February 15 2005 8:25am
Subject:Re: Can I dissable transactions?
View as plain text  

Thanks for your idea.  Deleting data x rows at a time would certainly 
help, if AUTOCOMMIT=TRUE.  But I have no idea how :)  I have tried:

DELETE FROM very_large_table WHERE delete_flag=1 LIMIT 100000;

But the LIMIT is not understood (4.1.8).

Unfortunately my 'delete_flag' is not key.  Therefore repeated attempts 
at deleting small amounts of data is very slow.

The fastest way seem to be to dump the data, edit the file, and 
re-insert the data.

But you have given my my solution:

If I cannot disable transactions, I'll have to work with one of the keys 
and iterate through that key bit bit.  So thanks, I'll go off and give 
it a go...


Jeff Smelser wrote:
> On Monday 14 February 2005 03:52 am, Ben Clewett wrote:
>>I am having a lot of problems deleting a large amount of data.  Say 20GB
>>from a 40GB table.  I seem to get failure quite a lot (due NOT to mysql,
>>but bad hardware), then MySQL roles back the transaction, which takes as
>>many hours and starting the transaction.  I also get this a lot:
> There is a feature of DB2 that can do this.. Its really not always all its 
> cracked up to be..
> In this case, it would happily delete, if something goes wrong, your table is 
> now marked bad.. The other 20million rows are now gone.. Is that what you 
> want?
> What you need to do, is set up a simple script to delete 20,000 rows a time, 
> and commit, just keep doing it till its done.. This way you could do 20,000 
> rows, wait a bit, do it again. or whatever. If it fails, you only rollback 
> what it was doing during the transaction and you wont have to start all over.
> Jeff

Ben Clewett
Road Tech Computer System Ltd
+44(0)1923 460000
Can I dissable transactions?Ben Clewett14 Feb
  • Re: Can I dissable transactions?Jeff Smelser14 Feb
    • Re: Can I dissable transactions?Ben Clewett15 Feb
      • Re: Can I dissable transactions?Michael Stassen16 Feb