On 8/25/08, Ann Harrison wrote: > > >> Brian and I had a short off-list discussion that touched on the > >> IGNORE qualifier and I suggested that Drizzle would be better > >> without it. In MySQL, IGNORE can qualify a number of different > >> statements - INSERT, UPDATE, DELETE, REPLACE, CREATE, ALTER ...
Then Jeremy Zawodny said: I've primarily seen people use IGNORE in cases when they: (1) were lazy or (2) didn't have a good mechanism for detecting existing rows and doing updates on them (old code from when ON DUPLICATE KEY UPDATE didn't exist, for example) And then Baron said: > The thing about IGNORE that makes it different from other > ignore-bad-data things is that you have to explicitly say you want to > ignore duplicates. (Except for LOAD DATA LOCAL INFILE, and maybe the > protocol hacking is already eliminating the need for that?) > > It's really quite useful when you're cleaning up crappy data, which I > have done a lot of. > > Or when you're restarting something that failed partway through. > Ditto, done a lot of that. > > I don't see the harm in it, since it's not one of those on-by-default > things; rather I see it as a useful extension like ON DUPLICATE KEY > UPDATE, or some of the other non-relational extensions I proposed in > an earlier thread. I agree with Baron's first point -- and as far as I know there's not really an automated way to change INSERT IGNORE INTO to INSERT...ON DUPLICATE KEY UPDATE -- say, if you're replaying binary logs and are being lazy (as Jeremy Z. suggests). I know we've had the problem where we've had to deal with replication being out of sync, but we don't know where it got out of sync, so we replay part of the binary logs to try to get it back....only INSERT IGNORE will not insert if a foreign key error occurs, etc. However, all of Baron's points after the first one are an extension of "being lazy" -- if something failed partway through, the non-lazy way is to start it from the failed statement. Granted, if MySQL handled transactions properly (even on MyISAM tables) this wouldn't even *be* an issue. I think almost all the problems I've had have been due to replication falling out of sync; I've had the most problems with clients that use circular replication (ugh). I don't see it as *harmful* to keep as a user, however, if it's bloating the code, I have no problem getting rid of it. MySQL INSERT IGNORE INTO is just another way MySQL poops on data integrity (and allows users to!). There are ways around it, though (for instance, to really "ignore" errors, instead of running shell> mysql < file.sql run mysql> source file.sql this doesn't ignore errors, if a statement has an error it prints the error on commandline, then goes to the next statement.) That being said, better error logging would be the solution -- not an extension to LOAD, but an on-the-fly configurable error file where you can say "I care about only these error numbers" or "I care about all error numbers except these" -- ie, inclusive or exclusive, with the option for "all", "none", "errors", "errors + warnings", etc. And by "better" error logging, I mean don't just throw the error into the error log (or table) but also put the command, [EMAIL PROTECTED], datetime, etc. that was running at the time of the error. -Sheeri
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
