Chris Bare wrote:
>
> I observed this problem on 3.22.14b-gamma, so I just downloaded
> 3.22.23b and I still see the same problem.
>
> The following sequence of commands appear to send mysql into an
> infinite loop. Is this update statement invalid in some way? It
> seems to work most of the time, but some times it goes into a loop.
> When I shut it down and restart, the value for layer is very large.
> Is there another way to do that that won't cause an infinite loop?
>
> create table cut2 (
> cut int(11) unsigned NOT NULL,
> layer int(11) unsigned NOT NULL DEFAULT 1,
> production_id int(11) unsigned NOT NULL,
> index (production_id, cut, layer),
> index (cut)
> );
>
> insert into cut2 (production_id, cut, layer) values (1, 1, 1);
> insert into cut2 (production_id, cut, layer) values (1, 2, 1);
> insert into cut2 (production_id, cut, layer) values (1, 2, 2);
> insert into cut2 (production_id, cut, layer) values (1, 2, 3);
> insert into cut2 (production_id, cut, layer) values (1, 2, 4);
> insert into cut2 (production_id, cut, layer) values (1, 2, 5);
> insert into cut2 (production_id, cut, layer) values (1, 3, 1);
> insert into cut2 (production_id, cut, layer) values (1, 4, 1);
> insert into cut2 (production_id, cut, layer) values (1, 4, 2);
> insert into cut2 (production_id, cut, layer) values (1, 4, 3);
> insert into cut2 (production_id, cut, layer) values (1, 5, 1);
> insert into cut2 (production_id, cut, layer) values (1, 6, 1);
> insert into cut2 (production_id, cut, layer) values (1, 7, 1);
> insert into cut2 (production_id, cut, layer) values (1, 8, 1);
> insert into cut2 (production_id, cut, layer) values (1, 9, 1);
>
> update cut2 set layer = layer + 1 where production_id = 1 and cut = 4
> and layer >= 3;
>
> --
> Chris Bare Metro Link Incorporated
Hi Chris
You update a column, which is in the index used to solve the where part of your update.
This has the effect, that the updated row will be sorted to another place in the index.
So this updated row will be found a second time, be updated, new sorted into the index,
found again, .....
I would break the use of that index with:
UPDATE
cut2
SET
layer = layer + 1
WHERE
production_id + 0 = 1 /* <-- now no index used! */
AND cut = 4
AND layer >= 3;
Tschau
Christian
