Hi, The update command only attempts to update non-key values for a wide range of tokens, which is why I can't fathom why it's failing. The key fields are uid and token, but we are only trying to update spam_hits, innocent_hits, and last_hit.
Very odd. On Sat, 2003-10-18 at 03:38, Joakim Ryden wrote: > Hey Matt - > > 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 > > > > > > > > > > > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]