563525 - Migrate process_type data script

Status: RESOLVED FIXED

(Socorro :: General, task)

Description

[Austin King [:ozten]]

Adding the process_type column to our reports tables will leave existing records with a NULL value.

For Plugins Crashes, we must update this value to 'plugin'.

Comment 1

[Austin King [:ozten]]

From a data perspective, I think it would be good to store Browser Crashes as process_type = 'browser', even though the crash metadata doesn't have a ProcessType property.

Comment 2

[Austin King [:ozten]]

UPDATE reports SET process_type = 'plugin'
WHERE id IN (
  SELECT reports.id FROM reports 
  JOIN  plugins_reports ON plugins_reports.report_id = reports.id
  WHERE
  reports.date_processed BETWEEN TIMESTAMP '2010-04-20 8:49:36' - CAST('1 weeks' AS INTERVAL) AND TIMESTAMP '2010-04-20 08:49:36' AND
  plugins_reports.date_processed BETWEEN TIMESTAMP '2010-04-20 8:49:36' - CAST('1 weeks' AS INTERVAL) AND TIMESTAMP '2010-04-20 08:49:36' AND
  process_type IS NULL
  AND hangid is not NULL
  AND plugins_reports.plugin_id IS NOT NULL);

Comment 3

[Austin King [:ozten]]

For dm-breakpad-stagedb the SQL in comment #2 update 1 week of data before 4-20, which was two crash reports:

SELECT * FROM reports
WHERE id IN (111218357, 111218359);

We'll need to roll this out across all the partitions.

Comment 4

[Austin King [:ozten]]

(In reply to comment #2)
Hmmm, but in my SQL. The snippet below was un-necessary since we want to migrate all plugin crashes.

AND hangid is not NULL

Comment 5

[Austin King [:ozten]]

UPDATE reports SET process_type = 'plugin'
WHERE id IN (
  SELECT reports.id FROM reports 
  JOIN  plugins_reports ON plugins_reports.report_id = reports.id
  WHERE
    reports.date_processed BETWEEN TIMESTAMP '2010-04-20 8:49:36' - CAST('1 weeks' AS INTERVAL) AND TIMESTAMP '2010-04-20 08:49:36' AND
    plugins_reports.date_processed BETWEEN TIMESTAMP '2010-04-20 8:49:36' - CAST('1 weeks' AS INTERVAL) AND TIMESTAMP '2010-04-20 08:49:36' AND
    process_type IS NULL AND
    plugins_reports.plugin_id IS NOT NULL
);

Okay a week's worth affected 1887 rows. That looks better.

Comment 6

[Austin King [:ozten]]

@lars is this something that would fit well with schema.py or should we write a one off python script?

Comment 7

[K Lars Lohn [:lars] [:klohn]]

since there is no-one following in our steps on this one, and a single sql statement addresses the issue, I would just have it as an sql statement that gets run once by IT.  Just document the instructions on the upgrade page on the wiki for 1.6.3

Comment 8

[Austin King [:ozten]]

Consulted with jberkus, going with an update per partition:

UPDATE reports_20100412 set process_type = 'plugin'
FROM plugins_reports_20100412
WHERE reports_20100412.process_type IS NULL AND 
      plugins_reports_20100412.report_id = reports_20100412.id;

Comment 9

[Austin King [:ozten]]

Attachment: Quick and Dirty migration script

This script would be checked into socorro/database/migrations

It is a one off script to populate the process_type column of the reports table.

IT instructions are to copy it into the scripts folder, run it, and delete the copy.

Comment 10

[Austin King [:ozten]]

Took 24 minutes against db-breakpad-stagedb.

Filed Bug#564336.

Comment 11

[Laura Thomson :laura]

Austin, did you check it in?

Comment 12

[Austin King [:ozten]]

(In reply to comment #11)
yes, socorro/database/migrations/migrateProcessType.py