Set-up regular spreadfirefox.authstage db refresh

RESOLVED FIXED

Status

mozilla.org Graveyard
Server Operations
--
major
RESOLVED FIXED
10 years ago
3 years ago

People

(Reporter: Alex Polvi, Assigned: fox2mike)

Tracking

Details

(Reporter)

Description

10 years ago
Could we please get the database on spreadfirefox.authstage refreshed with current production on a daily basis?

Right now I think we have to do it by hand...

Comment 1

10 years ago
I  think it might be  better to pull the production database to stage following a commit to subversion and prior to testing the changes to scripts / database schema on stage. 

If the database on stage is scheduled to be refreshed with what is currently on production on a daily basis it is possible that the following could happen

1. a commit happens to subversion with changes for scripts and database schema
2. these changes are operational on stage
3. the production database is then copied over from stage
4. problems arise as result of new scripts and a previous database schema  and the site unexpectedly breaks

Paul

Comment 2

10 years ago
I'm somewhat wary about doing any sort of "big" operation like this (import the database) whenever you do a commit.  But perhaps we could set something up so that there's a dummy file, and whenever that file is changed, we pull in the latest database?

I assume that we currently have an auto-checkout script on the stage server.  So we could add a cron that runs every few minutes that says "if this file is newer than we last saw, then re-import the database."  That way it's still automatic, but you get control over when it happens and we don't do it on every checkin.

Thoughts?
Assignee: server-ops → mark
(Reporter)

Comment 3

10 years ago
Sounds like a good plan to me.

I'm not sure if we have an auto db import script now....

Comment 4

10 years ago
Great idea Mark :-)

Comment 5

10 years ago
Okay then, please tell me what file you want to key this on?

Also, what is involved in the actual refresh process?  Take the latest production SQL and copy it over to staging?  That's easy and straightforward, are there any gotchas?
Status: NEW → ASSIGNED
(Reporter)

Comment 6

10 years ago
the only gotcha is to make sure to dump the binary data correctly. Oremj, can you provide the details of that? 

Comment 7

10 years ago
oremj - is that the --hex-blob or whatever you added to the dump script?  Can I assume that with that in place, it's a simple matter of "cat foo.sql | mysql" to import this dump?

Comment 8

10 years ago
It was removing --hex-blog from the mysqldump command.

Comment 9

10 years ago
I still need to know what file to key this on.  Just give me an svn path to something that you will update whenever you want to update the database.  Thanks.

Comment 10

10 years ago
Closing since I haven't gotten any info in about a month.  If you decide what file to key this off of, or come up with another way of doing this, please reopen.  Thanks.
Status: ASSIGNED → RESOLVED
Last Resolved: 10 years ago
Resolution: --- → INCOMPLETE
(Reporter)

Comment 11

10 years ago
Sorry. 

Have we done this anywhere in the past that we can use as a best practice? If not, key off of trunk/db-update/flag (which does not exist, yet)

Thanks.
Status: RESOLVED → REOPENED
Resolution: INCOMPLETE → ---
(Reporter)

Comment 12

10 years ago
thanks to buchanae, the flag is in r16327

Comment 13

10 years ago
Amazing, wait a month for a response and nothing, close the bug and get one in 36 minutes.  :p

To my knowledge there is no existing system that lets developers choose when to update staging.  Given you don't have access to the staging servers, we have traditionally gone with 'updates every night' or 'updates when you ask IT.'

Since you want something in the middle, 'let us choose when to update but not rely on IT', then we have to come up with a new way of doing it.  This seems to be the easiest way, IMO.  Open to suggestions though.  :)

..

I've gone ahead and set this up.  Every 5 minutes, it checks the latest revision on that file.  If it's greater than the last check, it initiates the import process.  Note that a full dump and import takes about 10 minutes, and the cron runs every 5 minutes, so it'll take 15-20 minutes for you to see the results of your commit.

During the course of testing, I ran the import.  I realized afterwards that this might not be a good idea if there's stuff going on on authstage that would be bad to import a DB over.  Sorry if I've mucked anything up.
Status: REOPENED → RESOLVED
Last Resolved: 10 years ago10 years ago
Resolution: --- → FIXED
Does anyone know where this was set up?

Comment 15

9 years ago
I think that it's setup on khan or dracula.

There's a cron that runs every few minutes that checks to see if the revision of a certain file has changed.  If so, then it will go ahead and import the latest nightly backup (which is what makes me think it's on khan or dracula, since the backups are stored there).
Found it just now while looking for something else.
mradm02:/root/bin/sfx-update/*
Could someone check if this is still set up properly?

I tried updating the timestamp, but I haven't seen the stage DB update yet.

http://viewvc.svn.mozilla.org/vc/projects/spreadfirefox.com/trunk/db-update/

Thanks.
Assignee: mark → server-ops
Severity: minor → major
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
(Assignee)

Updated

8 years ago
Assignee: server-ops → shyam
QA Contact: justin → mrz
(Assignee)

Comment 18

8 years ago
Seems to be running :

[root@mradm02 sfx-update]# cat sfx-last-version 
49860
(Assignee)

Comment 19

8 years ago
The scripts and the cron job seem alright. I'm running the script by hand now to verify that it's working.
(Assignee)

Comment 20

8 years ago
FYI,

Aug 24 09:06:01 mradm02 crond[11675]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 09:11:01 mradm02 crond[12593]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 09:16:01 mradm02 crond[13716]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 09:21:01 mradm02 crond[14661]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 09:26:01 mradm02 crond[15451]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 09:31:01 mradm02 crond[16697]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 09:36:01 mradm02 crond[17579]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 09:41:01 mradm02 crond[18500]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 09:46:01 mradm02 crond[19610]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 09:51:01 mradm02 crond[20494]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 09:56:01 mradm02 crond[21309]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 10:01:01 mradm02 crond[22657]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 10:06:01 mradm02 crond[23693]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 10:11:01 mradm02 crond[24606]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 10:16:01 mradm02 crond[25752]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 10:21:01 mradm02 crond[26687]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 10:26:01 mradm02 crond[27507]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
Aug 24 10:31:01 mradm02 crond[28817]: (root) CMD (/root/bin/sfx-update/update-sfx.sh)
(Assignee)

Comment 21

8 years ago
And when run by hand :

[root@mradm02 sfx-update]# ./force-update-sfx.sh 
Dumping up to date copy of SFX...
   dumping...
   copying...
   importing to stage...
   cleanup...
   done.

So, this seems to be working fine. The cron job runs every 5 mins and well, takes 10-15 mins to run...so I think ideally, you shouldn't be running this for more twice an hour, to give it time to complete correctly.
(Assignee)

Comment 22

8 years ago
Looks good, closing.

Alex, feel free to reopen if it doesn't work.
Status: REOPENED → RESOLVED
Last Resolved: 10 years ago8 years ago
Resolution: --- → FIXED
I tried again today, and I haven't seen the database update,

http://viewvc.svn.mozilla.org/vc/projects/spreadfirefox.com/trunk/db-update/

thanks
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
Running from the CLI returns instantly:

[root@mradm02 cron.d]# cd  /root/bin/sfx-update/             
[root@mradm02 sfx-update]# ./update-sfx.sh 
[root@mradm02 sfx-update]# 

If run the commands within, both CURVER and LASTVER match:

[root@mradm02 sfx-update]# svn log http://svn.mozilla.org/projects/spreadfirefox.com/trunk/db-update/flag 2>/dev/null | grep "^r" | head -1 | awk '{ print $1 }' | sed 's/^r//'
50247
[root@mradm02 sfx-update]# cat /root/bin/sfx-update/sfx-last-version
50247

So the whole if[] block is skipped.

Appears working then?
(Assignee)

Comment 25

8 years ago
mrz,

When the flag is updated, while the update-sfx.sh runs, for some reason, even when the numbers don't match, it doesn't seem to be calling the force-update-sfx.sh script. Or somehow failing in between.

I was thinking we should just integrate the two (since we know that update-sfx.sh is being called by cron without any issues).
(Assignee)

Comment 26

8 years ago
I've intgrated the scripts, re-open if this doesn't work the next time, and I'll poke at it again.
Status: REOPENED → RESOLVED
Last Resolved: 8 years ago8 years ago
Resolution: --- → FIXED
Tried this today, it's been a few hours and I don't see the stage has synced with production.

http://viewvc.svn.mozilla.org/vc/projects/spreadfirefox.com/trunk/db-update/
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
(Assignee)

Comment 28

8 years ago
I refactored this a little bit, and when I was done the cron automatically ran..and it seemed to work. 

We can test this again once before we close this...probably when you're up in the morning.
Thanks

ok, checked in a test update to the flag file,

Sending        db-update/flag
Transmitting file data .
Committed revision 53459.
(Assignee)

Comment 30

8 years ago
Alex,

I fixed a max_allowed_packet issue and added a lockfile to make sure we don't have more than one instance of this running at a time.

This cron currently runs every 5 mins, do we want that frequency? The run by hand (twice) took about 30 mins to finish, I'm going to run it by cron once and then can you please do a check for me in the morning?
it worked!

many thanks for all your help on this.
Status: REOPENED → RESOLVED
Last Resolved: 8 years ago8 years ago
Resolution: --- → FIXED
Product: mozilla.org → mozilla.org Graveyard
You need to log in before you can comment on or make changes to this bug.