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
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;
`id` links to a table with the accounts whose string parameters are stored
in this table.
The object of the query is to update a field that contains a hit counter and
the last 9 (that is maximum 9) unique ID's that caused the hits. Proposed
format was: <hits>;,<id1>,<id2>,...
I tried to fix an bug when an existing id starts with the new id (e.g. 11305
is in the list and a new hit is from id 113).
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'
I started out with the value "1;,11305" for the first hit by ID 11305 (which
is inserted by an INSERT query).
The query above resulted in 'affected rows: 9' and the value of the single
(!) row was "10;,11305,113,113"; the double 113 was caused by the bug I
mentioned before, but I'm now focussing on the counter part of the value.
I repeated the query and now it returned 'affected rows: 90' and the value
in the row was "100;,11305,113,113".
Various tests with different hit counter values show that the update is
repeated until the next power of 10 is reached:
1 -> affected: 9 -> 10
12 -> affected: 88 -> 100
413 -> affected: 578 -> 1000
Since the WHERE clause clearly identifies a single row in our table, I'm
beginning to think of a bug...
Any ideas or suggestions?
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
