Search the database
Search forum topics
Search members
Search for trades
diablo2.io is supported by ads
diablo2.io is supported by ads
14 replies   1271 views
1

I fucked the database and had to restore from backup, sorry!

No data yet

2

Description

Hey guys,

So I had to roll back the entire database to the state it was at 00:00 UTC on the 24th (earlier this morning). This was because I put a query into the DB without looking properly and overwrote the imagic field of every single entry ever made to '0'.
The winning query... :/ Note the lack of a 'WHERE' clause which meant the query applied to the whole database.
The winning query... :/ Note the lack of a 'WHERE' clause which meant the query applied to the whole database.
Really sorry to those of you who put in contributions today but I'm afraid they're lost forever and would need re-doing if you're still keen. Also means that any items that had been processed by me today are now unprocessed. Same goes for any forum posts , signups, or pretty much any interaction at all. Personally lost about 6 hours of work today, I think Storm had hundreds of contribs approved...

Would be nice if phpMyAdmin warned me that I was about to overwrite 4900+ rows of data, or had an undo button. But nope, hard reality is that what's queried stays queried.

Again, apologies for the inconvenience guys.
Description by Teebling
5

Can be used to make Runewords:

7
User avatar

Teebling 6859Admin

Europe PC
Hey guys,

So I had to roll back the entire database to the state it was at 00:00 UTC on the 24th (earlier this morning). This was because I put a query into the DB without looking properly and overwrote the imagic field of every single entry ever made to '0'.
The winning query... :/ Note the lack of a 'WHERE' clause which meant the query applied to the whole database.
The winning query... :/ Note the lack of a 'WHERE' clause which meant the query applied to the whole database.
Really sorry to those of you who put in contributions today but I'm afraid they're lost forever and would need re-doing if you're still keen. Also means that any items that had been processed by me today are now unprocessed. Same goes for any forum posts , signups, or pretty much any interaction at all. Personally lost about 6 hours of work today, I think Storm had hundreds of contribs approved...

Would be nice if phpMyAdmin warned me that I was about to overwrite 4900+ rows of data, or had an undo button. But nope, hard reality is that what's queried stays queried.

Again, apologies for the inconvenience guys.

7
I was wondering what just happened to my two table posts. I'm sad now. =(
7
User avatar

Stormlash 356Moderator

Paladin Europe PC
:feelsbadman:

Image
7
OP
User avatar

Teebling 6859Admin

Europe PC
Krythic wrote: 3 years ago
I was wondering what just happened to my two table posts. I'm sad now. =(
Really sorry dude omg.. I'd salvage them if I could but they're like, gone gone.

7
Teebling wrote: 3 years ago
Krythic wrote: 3 years ago
I was wondering what just happened to my two table posts. I'm sad now. =(
Really sorry dude omg.. I'd salvage them if I could but they're like, gone gone.
You're good, man. Feces happens. Thankfully, the tables were generated via code, so it won't take me more than a few minutes to make them again. Just have to find time to do it.
7
User avatar

Stormlash 356Moderator

Paladin Europe PC
Here's to my 1st diablo2.io Hardcore Death!

Guess it's time to rebuild the Skills entries to it's former glory once again!

This is why you play Hardcore! :D
Not many things can bring you down when you are used to lose everything you worked so hard for and be forced to start again from scratch.

This is just a bump in the road :)

Image
7
OP
User avatar

Teebling 6859Admin

Europe PC
Storm <3

I've updated the automatic DB backup cron to once every 6 hours now just in case this happens again.

7
Stormlash wrote: 3 years ago
Here's to my 1st diablo2.io Hardcore Death!

Guess it's time to rebuild the Skills entries to it's former glory once again!

This is why you play Hardcore! :D
Not many things can bring you down when you are used to lose everything you worked so hard for and be forced to start again from scratch.

This is just a bump in the road :)
Yeah, just consider it an unexpected season reset. Lmao.
7
@Teebling I recommend taking a bit of extra time to wrap any sql statement like that in a transaction with a rollback. I've been burned once too many times to write ANY statement on the live database without first putting it in a begin; rollback; transaction.

I usually do the following:

Code: Select all

begin;

-- UPDATE or DELETE statement here
-- SELECT statement here that shows me the change I intended to make

rollback;
Once I'm happy with the change, I'll change the rollback; to a commit;.
7
OP
User avatar

Teebling 6859Admin

Europe PC
walker34j wrote: 3 years ago
@Teebling I recommend taking a bit of extra time to wrap any sql statement like that in a transaction with a rollback. I've been burned once too many times to write ANY statement on the live database without first putting it in a begin; rollback; transaction.

I usually do the following:

Code: Select all

begin;

-- UPDATE or DELETE statement here
-- SELECT statement here that shows me the change I intended to make

rollback;
Once I'm happy with the change, I'll change the rollback; to a commit;.
Wow, thanks for this tip!

7
Onosecond.
I'll just leave it here...
7
Stormlash wrote: 3 years ago
This is just a bump in the road :)
Yes, you live and you learn! You have something great going here Teebling, this bump will only make it better in the long run!
7
sql will bite everyones ass eventually. Thanks for letting us know.

Gorpyr#2305
7
As a sql database admin, can relate. Transaction log backups have saved me more than I'd like to admit
7
This is the kind of mistake most SQL users have to make once, but good on you for having backups!
9

Advertisment

Hide ads
999

Greetings stranger!

You don't appear to be logged in...

No matches
 

 

 

 

Value:
Hide ads forever by supporting the site with a donation.

Greetings adblocker...

Warriv asks that you consider disabling your adblocker when using diablo2.io

Ad revenue helps keep the servers going and supports me, the site's creator :)

A one-time donation hides all ads, forever:
Make a donation