List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 6 1999 6:20pm
Subject:Re: [BUG] MySQL bug on some update query.
View as plain text  
At 6:09 PM +0000 11/6/99, Oh Junseon wrote:
>I found infinite loop bug on mysql update query .
>Let's excute below query sequencely .
>
>
>Let's create a new table width index ;
>
>      CREATE TABLE test ( no int not null default 0 , INDEX idx_no(no) ) ;
>
>Inserting data ;
>
>      INSERT INTO test VALUES (1) ;
>      INSERT INTO test VALUES (2) ;
>      ..........
>      ..........
>      ..........
>      INSERT INTO test VALUES (30) ;
>
>Updating data for meet the bug(?) :) ;
>
>      UPDATE test SET no = no + 1 where no >= 2 ;
>      INSERT INTO test VALUES (2) ;
>
>      ( LOOP THIS TWO LINE FOR 10 TIMES.. )
>
>Let's meet the bug !! ;
>
>      UPDATE test SET no = no + 1 where no >= 40 ;
>
>Mysql doing infinite loop for updating this query.
>Did you meet the bug ..? :)


This is fixed in current MySQL 3.23 versions.  To avoid the problem
in earlier versions, you have to prevent MySQL from using the index
on the updated field.  You can do so like this:

UPDATE test SET no = no + 1 where no + 0 >= 40 ;

Adding +0 to the indexed column prevents use of the index.

-- 
Paul DuBois, paul@stripped
Thread
[BUG] MySQL bug on some update query.Oh Junseon6 Nov
  • Re: [BUG] MySQL bug on some update query.Paul DuBois6 Nov
  • Re: [BUG] MySQL bug on some update query.Bob Kline6 Nov
    • Re: [BUG] MySQL bug on some update query.Oh Junseon6 Nov
      • Re: [BUG] MySQL bug on some update query.Bob Kline6 Nov
    • Re: [BUG] MySQL bug on some update query.Paul DuBois6 Nov