Bug 435665 - Bugzilla data cleanup - bugs.reporter data cleanup
Bugzilla data cleanup - bugs.reporter data cleanup
Status: CLOSED CURRENTRELEASE
Product: Bugzilla
Classification: Community
Component: Bugzilla General (Show other bugs)
devel
All Linux
medium Severity medium (vote)
: ---
: ---
Assigned To: Tony Fu
:
Depends On:
Blocks: 431402
  Show dependency treegraph
 
Reported: 2008-03-03 01:14 EST by Tony Fu
Modified: 2013-06-24 00:20 EDT (History)
0 users

See Also:
Fixed In Version: 2.18
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2008-03-16 23:44:13 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
change missed reporter to bugzilla@redhat.com (2.44 KB, text/plain)
2008-03-06 01:21 EST, Tony Fu
dkl: review-
Details
change missed reporter to bugzilla@redhat.com (2.33 KB, text/plain)
2008-03-07 02:01 EST, Tony Fu
dkl: review+
Details

  None (edit)
Comment 1 David Lawrence 2008-03-04 11:08:12 EST
These are most likely due to a profile being removed and the reporter id in bugs
table points to non-existent profile.

mysql> SELECT bugs.bug_id FROM bugs LEFT JOIN profiles ON bugs.reporter =
profiles.userid WHERE profiles.userid IS NULL ORDER BY bugs.bug_id;

258 rows in set (0.66 sec)

Looking through a random set of bugs, it is near impossible to figure out who
the reporter was from bug history or comments. Lets just take the easy path on
this on and set to bugzilla@redhat.com for now.

Please attach SQL statements needed to convert reporter for review.

Thanks
Dave
Comment 2 Tony Fu 2008-03-06 01:19:38 EST
Dave,

Please attached script.


Thanks,
Tony
Comment 3 Tony Fu 2008-03-06 01:21:14 EST
Created attachment 297002 [details]
change missed reporter to bugzilla@redhat.com
Comment 4 David Lawrence 2008-03-06 14:50:16 EST
Comment on attachment 297002 [details]
change missed reporter to bugzilla@redhat.com

># bug_activity prepartion 1:
># get Admin User profiles.userid. Used in writing bug_activity
># get new reporter (bugzilla@redhat.com)'s userid
>my $login_name    = $dbh->quote('bugzilla@redhat.com');
>my $admin_user_id = $dbh->selectall_arrayref(
>    "SELECT userid 
>        FROM profiles 
>       WHERE login_name = $login_name"
>)->[0][0];
>my $new_reporter_user_id = $admin_user_id;
>

We don't need $new_reporter_user_id here since we already know it is
bugzilla@redhat.com. We can just put bugzilla@redhat.com in the
bugs_activity.added column directly.

># bug_activity prepartion 2:
># get RESOLUTION's fieldid in fielddefs table
>my $field_id = $dbh->selectall_arrayref(
>    "SELECT id 
>       FROM fielddefs 
>      WHERE name='reporter'"
>)->[0][0];
>

Change this to SELECT fieldid since we are running it against the 2.18
database. No need to ever run this against the 3.2 database as we hope to have
most of this data corruption cleaned up before we do the final migration to
3.2.

>#get bug_id of the bugs whose reporter is not in profiles table
>my $sth = $dbh->prepare(
>"SELECT bugs.bug_id, bugs.reporter FROM bugs LEFT JOIN profiles ON bugs.reporter =
>profiles.userid WHERE profiles.userid IS NULL ORDER BY bugs.bug_id");
>$sth->execute();
>

$old_reporter_user_id will always be an integer value for this SELECT.
Should we put the integer value into into bugs_activity.removed or should we
just put some value like 'UNKNOWN' instead. The integer is not really useful to
anyone anymore if the profile entry has been deleted.

>while ( my ($bug_id, $old_reporter_user_id) = $sth->fetchrow_array() ) {
>
>    #change reporter to user bugzilla@redhat.com's userid
>    $dbh->do(
>        "UPDATE bugs SET reporter='$new_reporter_user_id' WHERE bug_id=$bug_id" );
>
>    #update bugs_activity table
>    $dbh->do(
>        "INSERT INTO bugs_activity (who, 
>                                    bug_when, 
>                                    fieldid, 
>                                    added, 
>                                    removed,
>                                    bug_id) 
>              VALUES ( $admin_user_id, 
>                       NOW(), 
>                       $field_id, 
>                       '$new_reporter_user_id',

Just put 'bugzilla@redhat.com' directly here instead of $new_reporter_user_id. 

>                       '$old_reporter_user_id',

See comment above about using 'UNKNOWN' instead.


>                       $bug_id )"
>    );
>
>}
>

Thanks Tony
Comment 5 Tony Fu 2008-03-07 01:59:37 EST
Dave,

Thanks for the review.  I have updated the script and please see new attachment.


Thanks,
Tony
Comment 6 Tony Fu 2008-03-07 02:01:35 EST
Created attachment 297141 [details]
change missed reporter to bugzilla@redhat.com
Comment 7 David Lawrence 2008-03-10 15:03:58 EDT
Comment on attachment 297141 [details]
change missed reporter to bugzilla@redhat.com

Looks good Tony. I did a sanitcheck.pl foreign_keys check against bugs2
database on bz-db1-test. I ran the cleanup_reporter.pl script against bugs2. I
then re-ran the sanitycheck and observed that the errors with bugs.reporter
were removed. So we can push this update live this Thursday.

Dave
Comment 8 David Lawrence 2008-03-10 15:05:01 EDT
Please check in cleanup_reporter.pl to CVS as well under the redhat directory. I
already checked in cleanup_resolution.pl from last week.

Thanks
Dave
Comment 9 Tony Fu 2008-03-10 21:37:29 EDT
Dave,

Thanks for the review.  I have added cleanup_reporter.pl into cvs repository.


Thanks,
Tony
Comment 10 David Lawrence 2008-03-16 23:44:13 EDT
This had been executed now on live database.

Dave

Note You need to log in before you can comment on or make changes to this bug.