On Sat, Dec 06, 2003 at 10:43:25AM -0800, Vintage Computer Festival wrote:
> On Fri, 5 Dec 2003, Patrick Rigney wrote:
>
> > > <RANT FRUSTRATION=HIGH>
> > > Who's the asshole that decided an UPDATE command in SQL without a WHERE
> > > clause defaults to ALL?
> > > </RANT>
> >
> > Ouch. Same for DELETE by the way.
>
> I know, which made it easy to zap the table so I could reload it :)
>
> I mean, I knew that this is the behavior of the UPDATE and DELETE
> commands, but when you're not paying attention you can easily hork up your
> database. TOO easily! It's just stupid!
The old problem with powerful tools: you can get all kinds of stuff
done, but if you are not careful, bad things are going to happen.
Yes, once upon time this one came to bite me in the ass. When one
directly messes with UPDATE on the grant tables (because the wannabe
database in questions didn't support doing it the right way, and no, it
also didn't support transactions) to change a users DB password,
forgeting the WHERE clause is a good way to get lots of attention
from coworkers ;-)
> > This may be a good opportunity to pick up the thread about MySQL and other
> > databases. :-)
> >
> > One of the things that mission-critical-appropriate databases provide, IMHO,
> > is a running transaction log or similar mechanism that facilitates not only
> > transaction management and rollback, but also backup and recovery up to a
> > point in time. I'm pretty sure MySQL doesn't offer this. Does anyone know
> > if PostgreSQL does?
>
> How about just modifying the command to require an ALL clause to delete or
> update all, rather than that being the default behavior? You'd think this
> would be the case. WHO is the jackass that designed this?
The guys designing the SQL92/99 standards?
> Fortunately, MySQL tables are easy to backup. Just copy the files in the
> data directory. It would be nice to have rollback features though.
Simple: wrap your critical operations in a transaction. Start the
transaction, do your changes, check them. If everything is ok, commit
else rollback. Ok, with MySQL you might have to use a special table type
to get support for transactions, but thats just one of the warts one has
to accept for not using a real RDBMS ;-)
Regards,
Alex.
--
"Opportunity is missed by most people because it is dressed in overalls and
looks like work." -- Thomas A. Edison
Received on Sun Dec 07 2003 - 10:00:57 GMT