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
