Graham Ashton wrote:
>
> I've got a relatively small table like this;
>
> mysql> SELECT * FROM table LIMIT 3;
> +------+------+------+---------+
> | col1 | col2 | col3 | counter |
> +------+------+------+---------+
> | 1 | 0 | 1 | 0 |
> | 1 | 1 | 1 | 0 |
> | 1 | 2 | 1 | 0 |
> +------+------+------+---------+
>
> The key is defined by
>
> UNIQUE (col1, col2, col3, counter)
>
> All the columns are defined as INT.
>
> What I want to do is to update the contents of the "counter" column
> based on the values of the other columns.
>
> I've been trying the following
>
> UPDATE table
> SET counter = counter + 1
> WHERE col1 = 1 AND col2 = 1 AND col3 = 1
>
> but whenever I try it the mysqld CPU load goes to 100%, and it doesn't
> appear to come back. If I kill mysqld then things return to normal, but
> it appears as though my UPDATE statement went into an indefinite loop
> (the value in the "counter" column is enormous). It appears to be the
> "counter = counter + 1" statement that is causing the problem.
>
> This works fine, however;
>
> UPDATE table
> SET counter = counter + 1
> WHERE col2 = 1
>
> It appears as though I don't understand how the UPDATE statement
> actually does it's stuff. Can anybody point out which tree I ought to be
> barking up? In the meantime I'm going to revert to SELECTing the value
> of the counter I'm interested in, adding 1 to it myself, and then doing
> a simple UPDATE.
>
> Any pointers would be much appreciated.
>
> P.S. I'm using 3.21.33 on Debian 2.1.
>
> --
> Graham
Hi Graham
Your problem comes from the fact, that you update a column which is part of a KEY which is
used in the WHERE clause.
To make this work, you have to stop using that key.
Try this:
UPDATE
table
SET
counter = counter + 1
WHERE
col1 + 0 = 1
AND col2 = 1
AND col3 = 1
Tschau
Christian
