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 for now. Please attach SQL statements needed to convert reporter for review. Thanks Dave
Dave, Please attached script. Thanks, Tony
Created attachment 297002 [details] change missed reporter to bugzilla
Comment on attachment 297002 [details] change missed reporter to bugzilla ># bug_activity prepartion 1: ># get Admin User profiles.userid. Used in writing bug_activity ># get new reporter (bugzilla)'s userid >my $login_name = $dbh->quote('bugzilla'); >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. We can just put bugzilla 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'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' directly here instead of $new_reporter_user_id. > '$old_reporter_user_id', See comment above about using 'UNKNOWN' instead. > $bug_id )" > ); > >} > Thanks Tony
Dave, Thanks for the review. I have updated the script and please see new attachment. Thanks, Tony
Created attachment 297141 [details] change missed reporter to bugzilla
Comment on attachment 297141 [details] change missed reporter to bugzilla 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
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
Dave, Thanks for the review. I have added cleanup_reporter.pl into cvs repository. Thanks, Tony
This had been executed now on live database. Dave