thanks for sharing some thoughts!
Yes, you're right about not changing the values of the columns with the UNIQUE index ( which kind of makes it even stranger of course :)
The output of show index and check table is here:
http://f1mail.forumone.com/mysql/
Check table looks ok as far as I can tell but show index I'm not sure what it's supposed to look like so I couldn't say if that looks kosher - whatcha think?
I'll have to get back to you on the IN() list - it's possible that it could grow to more than a million characters but I don't think it's there yet.
Thanks again!
--Jo
Matt W said the following on 10/18/03 00:07:
Hi Jo,
Well, that UPDATE query doesn't look like it's *changing* the value of the columns with the UNIQUE index. Are you sure there's no unique index on the columns that ARE being updated? e.g. is "id_token_data_01" key 1? (See if it's the first row returned by SHOW INDEX FROM dspam_token_data.)
Otherwise, maybe the table is corrupted? What does CHECK TABLE dspam_token_data say?
Finally, you said the IN () list is "very long" -- how long is that? More than a million characters?
Matt
----- Original Message ----- From: "Joakim Ryden" Sent: Friday, October 17, 2003 10:48 PM Subject: Failed updates
Hey guys -
I'm running a piece of software that is making database queries but
failing on
some of them and I'm trying to figure out why so I can fix it. The
table
structure for the table where the query fails looks like:
---
create table dspam_token_data ( uid smallint unsigned, token bigint unsigned, spam_hits int, innocent_hits int, last_hit date ) type=MyISAM;
create unique index id_token_data_01 on dspam_token_data(uid,token);
---
Then a typical query that fails looks like this:
Oct 17 15:22:29 fozzie dspam[1060]: query error: Duplicate entry '504-9261611235441802528' for key 1: update dspam_token_data set
last_hit =
current_date(), spam_hits = spam_hits + 1, innocent_hits =
innocent_hits - 0
where uid = 504 and token
in(4969289191363969024,5157322189832328960,6543336333207863017,118599581 19427079737,11593811100697187954,183694448946905616,6792121364254852560, 14691708723743604479,15592576291946632902,11395421438537018704,102680013 71065726376,720842269804708066,9738852911221666131,12131027321663899802, 7302142392335234946,14242238172346424282,5157322200926387920,13719443997 178333207,4870160322091024384,12799248752630108105,4751188209648361746,4 541344963299485594,14603129171059807301,4971015824631398400,797151803627 9903186,12747031424870854835,9487319413386095040,13913447836044115646,17 451437979588156723,9927623417453173202,13662488450238762265,159975508045 31139111,5056328520700002304,4773032218118574080,1648593925062866834,370 9266648058441096,9872040046217224573,15078128421449940735)
(Note that the "in()" is truncated here by me because it's very
long...)
Can anyone tell me what is happening?? Why would the update fail? The
table
already has the unique constraint so does this mean that the above
query
would modify rows in such a way that two rows or more would become
identical?
Many thanks!
--Jo
!DSPAM:3f90e6ca231201542861994!
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]