I simplified the query and narrowed the problem down to the use of
SUBSTRING_INDEX:
UPDATE `param_str`
SET `value` =
SUBSTRING_INDEX(
`value`,
';',
1
) + 1
WHERE
`id`=0 AND
`name`='prf_hits'
Table structure:
CREATE TABLE `param_str` (
`id` int(11) NOT NULL default '0',
`name` varchar(32) NOT NULL default '',
`value` varchar(255) default NULL,
`match` varchar(255) default NULL,
`match2` varchar(255) default NULL,
`weight` tinyint(4) default NULL,
KEY `id-name-value-match-match2`
(`id`,`name`,`value`(20),`match`(20),`match2`(20))
) TYPE=InnoDB;
Starting with value = 1
It often returns and affected rows count of the next bigger power of ten
minus the current value( i.e. 5 -> affected rows: 95; 102 -> 898; etc.)
Sometimes running the query repeatedly without another action in between
only 1 affected row is reported and the value is indeed only updated once.
Filed as http://bugs.mysql.com/8942
Regards, Jigal.
----- Original Message -----
From: "Jigal van Hemert" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Thursday, March 03, 2005 11:18 AM
Subject: Strange internal loop causing multiple updates on one record!?
> Tested on MySQL version 4.0.23, 4.1.8 and 4.1.10
> OS: RedHat Fedora Core 2 / RedHat 8
> Table type: InnoDB or MyISAM
>
> UPDATE `param_str`
> SET `value` =
> CONCAT_WS(
> ';',
> (
> SUBSTRING_INDEX(
> COALESCE(`value`,''),
> ';',
> 1
> ) + 1
> ),
> CONCAT_WS(
> ',',
> SUBSTRING_INDEX(
> REPLACE(
> SUBSTRING(
> COALESCE(`value`,'') FROM
> LOCATE(
> ';',
> COALESCE(`value`,'')
> ) + 1
> ),
> CONCAT(
> ',' ,
> '113',
> ','
> ),
> ','
> ),
> ',' ,
> -(9 -1)
> ),
> '113'
> )
> ) WHERE
> `id`=6 AND
> `name`='prf_hits'
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
