MySQL really needs an undo button...

[slycat]

In an attempt to be clever and update all future LondonFur meets on my Furmeets UK site with the new venue, I accidentally made an error with my SQL and managed to update all meets, past and future with the new LF meet venue... (I know, it really shouldn't have happened but my SQL is rusty and I did it fairly early in the morning).

I didn't make a backup before making the change so I've emailed my domain host in the vein hope that they have a backup they can restore.  If not, I will need to manually modify all of the meets historically and future meets to show the correct data.  As such, please can anyone reading this who has any info for meets which show as "lost data" or meets which show incorrect info let me know the correct details, it would be much appreciated.

Update: Looks like my domain host was able to restore it after all. It's back the way it was now. Now I've just gotta update all the LondonFur meets to the new venue again :P

Comments

[bungle-bear.livejournal.com]

I'll never understand SQL, so even though there was an error, you still made it further than me :-p


Although... I know it might sound rather cowboyish.. but if the hostorical meets have been and gone... why worry? I know it helps keep a decent record of everything, but I'd say ensuring up-and-coming meets are catalogued correctly would be the real concern :)

But that's just the thoughts of a bummer XD I'll let you get back to your working :-p

[kj-roo.livejournal.com]

That's where transactions are nice. :D You can make sure what ya did is right, then commit or rollback. :)

[slycat.livejournal.com]

Yeah, someone else mentioned this to me. I don't really know how it works tho.

[kj-roo.livejournal.com]

begin;
update blah blah
insert blah blah blah
commit;

You can do selects and stuff too. The data you see is the changed version. It doesn't actually go till you do the commit.
If something screws up, you do 'rollback;' instead of commit.

It works in postgresql, and oracle. MySQL can be made to do it, but it requires a certain table type or database type. (I don't remember exactly, but when I played with it, it was not the default.)

[sphelx.livejournal.com]

But surely you could just perform a query that would change location to BB&C/YOL where dates are greater than 2003(roughly?) and less than 2006?
I mean, I thought that was the whole point of SQL; that it could perform such queries and modifications?

Or am I missing something horribly huge from the equation?

[slycat.livejournal.com]

Yes, you were missing the fact that I wasn't really thinking about what I was doing :P

[mattlazycat.livejournal.com]

Next time, either do a SELECT with the same criteria as the UPDATE to make sure, or add "LIMIT 1" to the end the first time you do it, so it only changes one - if you fuck it up then, you only have one to fix :)

Come to think of it, if you're on TCH and using phpMyAdmin, you can do a backup of a table before you update it :)

[slycat.livejournal.com]

Yeah, I know I should have backed up... thinking back, I don't know what I was thinking with that query :P Anyway, lesson learnt, I won't be doing that again in a hurry :)