stage Socorro database updates for QA

VERIFIED FIXED

Status

mozilla.org Graveyard
Server Operations
--
critical
VERIFIED FIXED
8 years ago
3 years ago

People

(Reporter: ozten, Assigned: jabba)

Tracking

Details

Attachments

(3 attachments)

(Reporter)

Description

8 years ago
Please leave this ticket open, there will be several updates.

Please execute the following SQL and note how long it took to run, as well as it's output:

UPDATE reports_20100104 SET email = NULL;

SELECT product, version, signature, COUNT(id)
FROM reports_20100104
GROUP BY product, version, signature
ORDER BY COUNT(id) DESC
LIMIT 10;

SELECT min(date_processed), max(date_processed) FROM reports_20100104;
(Reporter)

Comment 1

8 years ago
The title says staging.. but I didn't mention in Comment#0.

This is for *STAGING* ONLY. Thanks.

Updated

8 years ago
Assignee: server-ops → jdow
(Assignee)

Comment 2

8 years ago
breakpad=# \timing
Timing is on.
breakpad=# UPDATE reports_20100104 SET email = NULL;
UPDATE 35897
Time: 3528.491 ms
breakpad=# SELECT product, version, signature, COUNT(id)
breakpad-# FROM reports_20100104
breakpad-# GROUP BY product, version, signature
breakpad-# ORDER BY COUNT(id) DESC
breakpad-# LIMIT 10;
   product   | version  |                         signature                         | count 
-------------+----------+-----------------------------------------------------------+-------
 Firefox     | 3.6b5    | UserCallWinProcCheckWow                                   |   341
 Firefox     | 3.6b5    | RtlpCoalesceFreeBlocks                                    |   166
 Firefox     | 3.6b5    | RtlpWaitForCriticalSection | RtlEnterCriticalSection      |   147
 Firefox     | 3.5.6    | UserCallWinProcCheckWow                                   |   142
 Firefox     | 3.5.6    | _PR_MD_SEND                                               |   122
 Firefox     | 4.0a1pre | @0x0 | NPSWF32.dll@0x1890d8                               |   107
 Thunderbird | 3.0      | EscapeFromSpaceLine(nsIOutputStream*, char*, char const*) |   104
 Firefox     | 3.5b4    | RtlpWaitForCriticalSection | RtlEnterCriticalSection      |   102
 Firefox     | 3.5.6    | RtlpWaitForCriticalSection | RtlEnterCriticalSection      |   102
 Firefox     | 3.6b5    | _PR_MD_SEND                                               |    96
(10 rows)

Time: 76.006 ms
breakpad=# SELECT min(date_processed), max(date_processed) FROM reports_20100104;
            min             |            max             
----------------------------+----------------------------
 2010-01-06 01:03:04.161551 | 2010-01-07 19:24:24.538063
(1 row)

Time: 26.264 ms
breakpad=#
(Reporter)

Comment 3

8 years ago
Stage has very little date, expanding the safe test data window:

UPDATE reports_20091228 SET email = NULL;
UPDATE reports_20100111 SET email = NULL;
UPDATE reports_20100118 SET email = NULL;
UPDATE reports_20100125 SET email = NULL;
UPDATE reports_20100201 SET email = NULL;
(Reporter)

Comment 4

8 years ago
Created attachment 482600 [details]
First run of fake data

Once Comment#3 is done, please run the attached SQL and pastebin the output.
(Assignee)

Comment 5

8 years ago
output from comment 3:
breakpad=# UPDATE reports_20091228 SET email = NULL;

UPDATE 99025
Time: 52901.868 ms
breakpad=# 
breakpad=# UPDATE reports_20100111 SET email = NULL;
ERROR:  relation "reports_20100111" does not exist
breakpad=# UPDATE reports_20100118 SET email = NULL;
UPDATE 35417
Time: 2969.579 ms
breakpad=# UPDATE reports_20100125 SET email = NULL;
UPDATE 31618
Time: 7141.110 ms
breakpad=# UPDATE reports_20100201 SET email = NULL;
UPDATE 61281
Time: 59129.480 ms
breakpad=#
(Reporter)

Comment 7

8 years ago
thanks.
Status: NEW → RESOLVED
Last Resolved: 8 years ago
Resolution: --- → FIXED
(Reporter)

Updated

8 years ago
Blocks: 588516
(Reporter)

Comment 8

8 years ago
Created attachment 482720 [details]
Second run of fake data

This adds a second crash signature and different data for a second QA test case.
(Reporter)

Comment 9

8 years ago
@jabba - in stage, please run attachment#482720 [details] Thanks!
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
(Assignee)

Comment 10

8 years ago
Done:

-bash-3.2$ psql breakpad < bug603660.sql 
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
-bash-3.2$
(Reporter)

Comment 11

8 years ago
Thanks, can you also run and pastebin the output of:

SELECT date_processed, email FROM reports WHERE 
date_processed >= '2010-01-01' AND 
date_processed < '2010-01-30' AND
signature = 'RtlpCoalesceFreeBlocks' AND
email IS NOT NULL and email != ''
ORDER BY email;
(Reporter)

Comment 12

8 years ago
As reported in IRC
1) log permission issues (Python can't create new logs when rolling
2) db objects have permission issues

please comment here when these are fixed, so QA knows when it's worth testing.
(Assignee)

Comment 13

8 years ago
I think the db stuff is straightened out. I'm not sure about the log permission issues. It is very intermittent and due to some python log rolling issue that I can't reproduce on prod.
(Reporter)

Comment 14

8 years ago
(In reply to comment #13)
How is it intermittent? I have access to stage and can reproduce in 100% of the time?
(Reporter)

Comment 15

8 years ago
Poke for running SQL in comment#11.
(Reporter)

Comment 16

8 years ago
After you run SQL in comment#11 then please run 

DELETE FROM email_campaigns_contacts;
DELETE FROM email_contacts;
DELETE FROM email_campaigns;

this will reset the stage data for QA.

Thanks
(Assignee)

Comment 17

8 years ago
sql from comment#11 has been run: http://jabba.pastebin.mozilla.org/816062

And sql in comment#16 has been run as well:
breakpad=> DELETE FROM email_campaigns_contacts;
DELETE 27
breakpad=> DELETE FROM email_contacts;
DELETE 27
breakpad=> DELETE FROM email_campaigns;
DELETE 1
breakpad=>
(Reporter)

Comment 18

8 years ago
This is ready to test. My 2nd batch of test data has some issue, I'll file a new bug to add another test case.

Thanks for your help @jabba.
Status: REOPENED → RESOLVED
Last Resolved: 8 years ago8 years ago
Resolution: --- → FIXED
(Reporter)

Comment 19

8 years ago
Re-opening bug to add third set of test data.
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
(Reporter)

Comment 20

8 years ago
Created attachment 483596 [details]
third set of SQL

Please run against stage db and pastebin results.
(Reporter)

Comment 21

8 years ago
http://jabba.pastebin.mozilla.org/818243
Thanks!
Status: REOPENED → RESOLVED
Last Resolved: 8 years ago8 years ago
Resolution: --- → FIXED
Verified FIXED; Vishal and I worked through the three datasets [1] today, and everything worked.

[1] https://docs.google.com/document/pub?id=1_Rhg1n3ac-rvMkjow_5nCJJv6up5oqineue7cmHcBpE&pli=1
Status: RESOLVED → VERIFIED
Product: mozilla.org → mozilla.org Graveyard
You need to log in before you can comment on or make changes to this bug.