Update mozillians-stage database

VERIFIED FIXED

Status

Data & BI Services Team
DB: MySQL
--
critical
VERIFIED FIXED
4 years ago
4 years ago

People

(Reporter: giorgos, Assigned: cyborgshadow)

Tracking

Details

(Whiteboard: [data: export])

(Reporter)

Description

4 years ago
Please get a fresh copy of mozillians prod database, sanitize the data(*) and load the dump into mozillians_stage database

(*) Sanitize the data using the following sql commands.

--- 

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS api_apiapp;
DROP TABLE IF EXISTS celery_taskmeta;
DROP TABLE IF EXISTS celery_tasksetmeta;
DROP TABLE IF EXISTS django_admin_log;
DROP TABLE IF EXISTS django_session;
DROP TABLE IF EXISTS djcelery_crontabschedule;
DROP TABLE IF EXISTS djcelery_intervalschedule;
DROP TABLE IF EXISTS djcelery_periodictask;
DROP TABLE IF EXISTS djcelery_periodictasks;
DROP TABLE IF EXISTS djcelery_taskstate;
DROP TABLE IF EXISTS djcelery_workerstate;
DROP TABLE IF EXISTS schema_migration;
DROP TABLE IF EXISTS tastypie_apiaccess;
DROP TABLE IF EXISTS tastypie_apikey;
DROP TABLE IF EXISTS thumbnail_kvstore;
DROP TABLE IF EXISTS waffle_flag;
DROP TABLE IF EXISTS waffle_flag_groups;
DROP TABLE IF EXISTS waffle_flag_users;
DROP TABLE IF EXISTS waffle_sample;
DROP TABLE IF EXISTS waffle_switch;
UPDATE auth_user SET email = CONCAT(id, "@mozilla.com") WHERE is_staff=0 AND email LIKE "%@mozilla.com";
UPDATE auth_user SET email = CONCAT(id, "@example.com") WHERE is_staff=0 AND email NOT LIKE "%@mozilla.com";
SET FOREIGN_KEY_CHECKS=1;

---


Thanks!
(Reporter)

Comment 1

4 years ago
Getting this done today will be highly appreciated. We plan a push to production on Thu and we want a couple of days to test on stage. 

Sheeri, maybe you can work on this?
Flags: needinfo?(scabral)
(Assignee)

Comment 2

4 years ago
Hi Giorgos, 

Sheeri's out of the office at a conference until Thursday. I'm the current oncall DBA. I'll make sure this gets done for you today. In the future, for urgent requests like this, it helps to do 1 of 2 things (or both).

1.) Set the Importance on the bug to Critical (That means the oncall DBA be paged if nobody gets to it within 4 hours).

2.) Pop into #db on irc and ask. Another DBA might have free cycles to jump in and do it immediately.

Note that the oncall DBA can be identified via the topic in #db.
Flags: needinfo?(scabral)
(Assignee)

Comment 3

4 years ago
All done. Refreshed and loaded into stage. Sanitize complete.
Status: NEW → RESOLVED
Last Resolved: 4 years ago
Resolution: --- → FIXED
(Reporter)

Comment 4

4 years ago
Hey Brandon,

Thanks for the ultra fast response and the info. 

Question did you drop mozillians_stage or did you just push the sanitized data to it?
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
Do we need to update the daily dev/stage sanitization (that makes the files that get copied to mozillians-dev.allizom.org) with what's in comment 0?

For Brandon/Matt (DBAs on our side), the service is documented at https://mana.mozilla.org/wiki/pages/viewpage.action?pageId=40046283
This broke stage because it seems like all the tables listed to be dropped above are missing. Those tables needed to be removed from the database dump from prod, but not from the stage database itself. 

Is it possible to restore the old stage db and then import the sanitized dump to it rather than dropping it?
(In reply to Sheeri Cabral [:sheeri] Out of office until Thu 24 Jul from comment #5)
> Do we need to update the daily dev/stage sanitization (that makes the files
> that get copied to mozillians-dev.allizom.org) with what's in comment 0?
> 
> For Brandon/Matt (DBAs on our side), the service is documented at
> https://mana.mozilla.org/wiki/pages/viewpage.action?pageId=40046283

Nah those commands are just a manual copy paste of what the sanitization script does.
(Reporter)

Comment 8

4 years ago
Ideally we' like the old stage database to be restored (pre actions in comment #3) and then the sanitized dump to be pushed to it.
(Reporter)

Comment 9

4 years ago
Setting the importance to critical as this is blocking a release now. Please reply here or find me (giorgos) or sancus on IRC if you need extra info to move this forward.

Thanks
Severity: normal → critical
This is paging on call because it has been moved to critical. I'm going to take the bug until one of the DBA's can to stop it paging.
Assignee: server-ops-database → rwatson
(Assignee)

Comment 11

4 years ago
Sorry, I pushed the dumpfile into the database that would have fixed this before leaving yesterday evening. Unfortunately I had modified it a bit and made a typo so it failed. I corrected the typo and re-ran this morning.
Assignee: rwatson → bjohnson
Status: REOPENED → RESOLVED
Last Resolved: 4 years ago4 years ago
Resolution: --- → FIXED
(Reporter)

Comment 12

4 years ago
Looks good! Thanks :)
Bumping to verified per comment 12
Status: RESOLVED → VERIFIED
(Assignee)

Updated

4 years ago
Whiteboard: [ export ]
Whiteboard: [ export ] → [data: export]
Product: mozilla.org → Data & BI Services Team
You need to log in before you can comment on or make changes to this bug.