2
# -*- Mode: perl; indent-tabs-mode: nil -*-
4
# The contents of this file are subject to the Mozilla Public
5
# License Version 1.1 (the "License"); you may not use this file
6
# except in compliance with the License. You may obtain a copy of
7
# the License at http://www.mozilla.org/MPL/
9
# Software distributed under the License is distributed on an "AS
10
# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
11
# implied. See the License for the specific language governing
12
# rights and limitations under the License.
14
# The Original Code is the Bugzilla Bug Tracking System.
16
# The Initial Developer of the Original Code is Netscape Communications
17
# Corporation. Portions created by Netscape are
18
# Copyright (C) 1998 Netscape Communications Corporation. All
21
# Contributor(s): Terry Weissman <terry@mozilla.org>
22
# Matthew Tuck <matty@chariot.net.au>
23
# Max Kanat-Alexander <mkanat@bugzilla.org>
24
# Marc Schumann <wurblzap@gmail.com>
25
# Frédéric Buclin <LpSolit@gmail.com>
32
use Bugzilla::Constants;
37
###########################################################################
39
###########################################################################
48
Status("<font color=\"red\">$str</font>");
53
return "<a href=\"show_bug.cgi?id=$id\">$id</a>";
57
# Parameter is a list of bug ids.
59
# Return is a string containing a list of all the bugids, as hrefs,
60
# followed by a link to them all as a buglist
64
# Historically, GetBugLink() wasn't used here. I'm guessing this
65
# was because it didn't exist or is too performance heavy, or just
67
my @bug_links = map(BugLink($_), @bugs);
69
return join(', ',@bug_links) . " <a href=\"buglist.cgi?bug_id=" .
70
join(',',@bugs) . "\">(as buglist)</a>";
73
###########################################################################
75
###########################################################################
77
Bugzilla->login(LOGIN_REQUIRED);
79
my $cgi = Bugzilla->cgi;
80
my $dbh = Bugzilla->dbh;
81
my $template = Bugzilla->template;
82
my $user = Bugzilla->user;
84
# Make sure the user is authorized to access sanitycheck.cgi.
85
# As this script can now alter the group_control_map table, we no longer
86
# let users with editbugs privs run it anymore.
87
$user->in_group("editcomponents")
88
|| ($user->in_group('editkeywords') && defined $cgi->param('rebuildkeywordcache'))
89
|| ThrowUserError("auth_failure", {group => "editcomponents",
91
object => "sanity_check"});
97
$template->put_header("Sanity Check");
99
###########################################################################
100
# Users with 'editkeywords' privs only can only check keywords.
101
###########################################################################
102
unless ($user->in_group('editcomponents')) {
103
check_votes_or_keywords('keywords');
104
Status("Sanity check completed.");
105
$template->put_footer();
109
###########################################################################
111
###########################################################################
113
if (defined $cgi->param('rebuildvotecache')) {
114
Status("OK, now rebuilding vote cache.");
115
$dbh->bz_lock_tables('bugs WRITE', 'votes READ');
116
$dbh->do(q{UPDATE bugs SET votes = 0});
117
my $sth_update = $dbh->prepare(q{UPDATE bugs
120
my $sth = $dbh->prepare(q{SELECT bug_id, SUM(vote_count)
121
FROM votes }. $dbh->sql_group_by('bug_id'));
123
while (my ($id, $v) = $sth->fetchrow_array) {
124
$sth_update->execute($v, $id);
126
$dbh->bz_unlock_tables();
127
Status("Vote cache has been rebuilt.");
130
###########################################################################
131
# Create missing group_control_map entries
132
###########################################################################
134
if (defined $cgi->param('createmissinggroupcontrolmapentries')) {
135
Status(qq{OK, now creating <code>SHOWN</code> member control entries
136
for product/group combinations lacking one.});
138
my $na = CONTROLMAPNA;
139
my $shown = CONTROLMAPSHOWN;
140
my $insertsth = $dbh->prepare(
141
qq{INSERT INTO group_control_map (
142
group_id, product_id, entry,
143
membercontrol, othercontrol, canedit
149
my $updatesth = $dbh->prepare(qq{UPDATE group_control_map
150
SET membercontrol = $shown
152
AND product_id = ?});
155
# Find all group/product combinations used for bugs but not set up
156
# correctly in group_control_map
157
my $invalid_combinations = $dbh->selectall_arrayref(
158
qq{ SELECT bugs.product_id,
164
INNER JOIN bug_group_map AS bgm
165
ON bugs.bug_id = bgm.bug_id
167
ON bgm.group_id = groups.id
169
ON bugs.product_id = products.id
170
LEFT JOIN group_control_map AS gcm
171
ON bugs.product_id = gcm.product_id
172
AND bgm.group_id = gcm.group_id
173
WHERE COALESCE(gcm.membercontrol, $na) = $na
174
} . $dbh->sql_group_by('bugs.product_id, bgm.group_id',
175
'gcm.membercontrol, groups.name, products.name'));
177
foreach (@$invalid_combinations) {
178
my ($product_id, $group_id, $currentmembercontrol,
179
$group_name, $product_name) = @$_;
182
if (defined($currentmembercontrol)) {
183
Status(qq{Updating <code>NA/<em>xxx</em></code> group control
184
setting for group <em>$group_name</em> to
185
<code>SHOWN/<em>xxx</em></code> in product
186
<em>$product_name</em>.});
187
$updatesth->execute($group_id, $product_id);
190
Status(qq{Generating <code>SHOWN/NA</code> group control setting
191
for group <em>$group_name</em> in product
192
<em>$product_name</em>.});
193
$insertsth->execute($group_id, $product_id);
197
Status("Repaired $counter defective group control settings.");
200
###########################################################################
201
# Fix missing creation date
202
###########################################################################
204
if (defined $cgi->param('repair_creation_date')) {
205
Status("OK, now fixing missing bug creation dates");
207
my $bug_ids = $dbh->selectcol_arrayref('SELECT bug_id FROM bugs
208
WHERE creation_ts IS NULL');
210
my $sth_UpdateDate = $dbh->prepare('UPDATE bugs SET creation_ts = ?
213
# All bugs have an entry in the 'longdescs' table when they are created,
214
# even if 'commentoncreate' is turned off.
215
my $sth_getDate = $dbh->prepare('SELECT MIN(bug_when) FROM longdescs
218
foreach my $bugid (@$bug_ids) {
219
$sth_getDate->execute($bugid);
220
my $date = $sth_getDate->fetchrow_array;
221
$sth_UpdateDate->execute($date, $bugid);
223
Status(scalar(@$bug_ids) . " bugs have been fixed.");
226
###########################################################################
228
###########################################################################
230
if (defined $cgi->param('rescanallBugMail')) {
231
require Bugzilla::BugMail;
233
Status("OK, now attempting to send unsent mail");
234
my $time = $dbh->sql_interval(30, 'MINUTE');
236
my $list = $dbh->selectcol_arrayref(qq{
239
WHERE (lastdiffed IS NULL
240
OR lastdiffed < delta_ts)
241
AND delta_ts < now() - $time
244
Status(scalar(@$list) . ' bugs found with possibly unsent mail.');
247
# We cannot simply look at the bugs_activity table to find who did the
248
# last change in a given bug, as e.g. adding a comment doesn't add any
249
# entry to this table. And some other changes may be private
250
# (such as time-related changes or private attachments or comments)
251
# and so choosing this user as being the last one having done a change
252
# for the bug may be problematic. So the best we can do at this point
253
# is to choose the currently logged in user for email notification.
254
$vars->{'changer'} = Bugzilla->user->login;
256
foreach my $bugid (@$list) {
257
Bugzilla::BugMail::Send($bugid, $vars);
260
if (scalar(@$list) > 0) {
261
Status("Unsent mail has been sent.");
264
$template->put_footer();
268
###########################################################################
269
# Remove all references to deleted bugs
270
###########################################################################
272
if (defined $cgi->param('remove_invalid_bug_references')) {
273
Status("OK, now removing all references to deleted bugs.");
275
$dbh->bz_lock_tables('attachments WRITE', 'bug_group_map WRITE',
276
'bugs_activity WRITE', 'cc WRITE',
277
'dependencies WRITE', 'duplicates WRITE',
278
'flags WRITE', 'keywords WRITE',
279
'longdescs WRITE', 'votes WRITE', 'bugs READ');
281
foreach my $pair ('attachments/', 'bug_group_map/', 'bugs_activity/', 'cc/',
282
'dependencies/blocked', 'dependencies/dependson',
283
'duplicates/dupe', 'duplicates/dupe_of',
284
'flags/', 'keywords/', 'longdescs/', 'votes/') {
286
my ($table, $field) = split('/', $pair);
290
$dbh->selectcol_arrayref("SELECT $table.$field FROM $table
291
LEFT JOIN bugs ON $table.$field = bugs.bug_id
292
WHERE bugs.bug_id IS NULL");
294
if (scalar(@$bug_ids)) {
295
$dbh->do("DELETE FROM $table WHERE $field IN (" . join(',', @$bug_ids) . ")");
299
$dbh->bz_unlock_tables();
300
Status("All references to deleted bugs have been removed.");
303
###########################################################################
304
# Remove all references to deleted attachments
305
###########################################################################
307
if (defined $cgi->param('remove_invalid_attach_references')) {
308
Status("OK, now removing all references to deleted attachments.");
310
$dbh->bz_lock_tables('attachments WRITE', 'attach_data WRITE');
313
$dbh->selectcol_arrayref('SELECT attach_data.id
315
LEFT JOIN attachments
316
ON attachments.attach_id = attach_data.id
317
WHERE attachments.attach_id IS NULL');
319
if (scalar(@$attach_ids)) {
320
$dbh->do('DELETE FROM attach_data WHERE id IN (' .
321
join(',', @$attach_ids) . ')');
324
$dbh->bz_unlock_tables();
325
Status("All references to deleted attachments have been removed.");
328
print "OK, now running sanity checks.<p>\n";
330
###########################################################################
331
# Perform referential (cross) checks
332
###########################################################################
334
# This checks that a simple foreign key has a valid primary key value. NULL
335
# references are acceptable and cause no problem.
337
# The first parameter is the primary key table name.
338
# The second parameter is the primary key field name.
339
# Each successive parameter represents a foreign key, it must be a list
340
# reference, where the list has:
341
# the first value is the foreign key table name.
342
# the second value is the foreign key field name.
343
# the third value is optional and represents a field on the foreign key
344
# table to display when the check fails.
345
# the fourth value is optional and is a list reference to values that
346
# are excluded from checking.
348
# FIXME: The excluded values parameter should go away - the QA contact
349
# fields should use NULL instead - see bug #109474.
350
# The same goes for series; no bug for that yet.
353
my $table = shift @_;
354
my $field = shift @_;
355
my $dbh = Bugzilla->dbh;
357
Status("Checking references to $table.$field");
361
my ($refertable, $referfield, $keyname, $exceptions) = @$ref;
364
my %exceptions = map { $_ => 1 } @$exceptions;
366
Status("... from $refertable.$referfield");
368
my $query = qq{SELECT DISTINCT $refertable.$referfield} .
369
($keyname ? qq{, $refertable.$keyname } : q{}) .
372
ON $refertable.$referfield = $table.$field
373
WHERE $table.$field IS NULL
374
AND $refertable.$referfield IS NOT NULL};
376
my $sth = $dbh->prepare($query);
379
my $has_bad_references = 0;
381
while (my ($value, $key) = $sth->fetchrow_array) {
382
next if $exceptions{$value};
383
my $alert = "Bad value "$value" found in $refertable.$referfield";
385
if ($keyname eq 'bug_id') {
386
$alert .= ' (bug ' . BugLink($key) . ')';
388
$alert .= " ($keyname == '$key')";
392
$has_bad_references = 1;
394
# References to non existent bugs can be safely removed, bug 288461
395
if ($table eq 'bugs' && $has_bad_references) {
396
print qq{<a href="sanitycheck.cgi?remove_invalid_bug_references=1">
397
Remove invalid references to non existent bugs.</a><p>\n};
399
# References to non existent attachments can be safely removed.
400
if ($table eq 'attachments' && $has_bad_references) {
401
print qq{<a href="sanitycheck.cgi?remove_invalid_attach_references=1">
402
Remove invalid references to non existent attachments.</a><p>\n};
407
CrossCheck('classifications', 'id',
408
['products', 'classification_id']);
410
CrossCheck("keyworddefs", "id",
411
["keywords", "keywordid"]);
413
CrossCheck("fielddefs", "id",
414
["bugs_activity", "fieldid"],
415
['profiles_activity', 'fieldid']);
417
CrossCheck("flagtypes", "id",
418
["flags", "type_id"]);
420
CrossCheck("bugs", "bug_id",
421
["bugs_activity", "bug_id"],
422
["bug_group_map", "bug_id"],
423
["attachments", "bug_id"],
425
["longdescs", "bug_id"],
426
["dependencies", "blocked"],
427
["dependencies", "dependson"],
430
["keywords", "bug_id"],
431
["duplicates", "dupe_of", "dupe"],
432
["duplicates", "dupe", "dupe_of"]);
434
CrossCheck("groups", "id",
435
["bug_group_map", "group_id"],
436
['category_group_map', 'group_id'],
437
["group_group_map", "grantor_id"],
438
["group_group_map", "member_id"],
439
["group_control_map", "group_id"],
440
["namedquery_group_map", "group_id"],
441
["user_group_map", "group_id"]);
443
CrossCheck("namedqueries", "id",
444
["namedqueries_link_in_footer", "namedquery_id"],
445
["namedquery_group_map", "namedquery_id"],
448
CrossCheck("profiles", "userid",
449
['profiles_activity', 'userid'],
450
['profiles_activity', 'who'],
451
['email_setting', 'user_id'],
452
['profile_setting', 'user_id'],
453
["bugs", "reporter", "bug_id"],
454
["bugs", "assigned_to", "bug_id"],
455
["bugs", "qa_contact", "bug_id"],
456
["attachments", "submitter_id", "bug_id"],
457
['flags', 'setter_id', 'bug_id'],
458
['flags', 'requestee_id', 'bug_id'],
459
["bugs_activity", "who", "bug_id"],
460
["cc", "who", "bug_id"],
462
["votes", "who", "bug_id"],
463
["longdescs", "who", "bug_id"],
464
["logincookies", "userid"],
465
["namedqueries", "userid"],
466
["namedqueries_link_in_footer", "user_id"],
467
['series', 'creator', 'series_id'],
468
["watch", "watcher"],
469
["watch", "watched"],
470
['whine_events', 'owner_userid'],
471
["tokens", "userid"],
472
["user_group_map", "user_id"],
473
["components", "initialowner", "name"],
474
["components", "initialqacontact", "name"],
475
["component_cc", "user_id"]);
477
CrossCheck("products", "id",
478
["bugs", "product_id", "bug_id"],
479
["components", "product_id", "name"],
480
["milestones", "product_id", "value"],
481
["versions", "product_id", "value"],
482
["group_control_map", "product_id"],
483
["flaginclusions", "product_id", "type_id"],
484
["flagexclusions", "product_id", "type_id"]);
486
CrossCheck("components", "id",
487
["component_cc", "component_id"]);
489
# Check the former enum types -mkanat@bugzilla.org
490
CrossCheck("bug_status", "value",
491
["bugs", "bug_status", "bug_id"]);
493
CrossCheck("resolution", "value",
494
["bugs", "resolution", "bug_id"]);
496
CrossCheck("bug_severity", "value",
497
["bugs", "bug_severity", "bug_id"]);
499
CrossCheck("op_sys", "value",
500
["bugs", "op_sys", "bug_id"]);
502
CrossCheck("priority", "value",
503
["bugs", "priority", "bug_id"]);
505
CrossCheck("rep_platform", "value",
506
["bugs", "rep_platform", "bug_id"]);
508
CrossCheck('series', 'series_id',
509
['series_data', 'series_id']);
511
CrossCheck('series_categories', 'id',
512
['series', 'category']);
514
CrossCheck('whine_events', 'id',
515
['whine_queries', 'eventid'],
516
['whine_schedules', 'eventid']);
518
CrossCheck('attachments', 'attach_id',
519
['attach_data', 'id']);
521
###########################################################################
522
# Perform double field referential (cross) checks
523
###########################################################################
525
# This checks that a compound two-field foreign key has a valid primary key
526
# value. NULL references are acceptable and cause no problem.
528
# The first parameter is the primary key table name.
529
# The second parameter is the primary key first field name.
530
# The third parameter is the primary key second field name.
531
# Each successive parameter represents a foreign key, it must be a list
532
# reference, where the list has:
533
# the first value is the foreign key table name
534
# the second value is the foreign key first field name.
535
# the third value is the foreign key second field name.
536
# the fourth value is optional and represents a field on the foreign key
537
# table to display when the check fails
539
sub DoubleCrossCheck {
540
my $table = shift @_;
541
my $field1 = shift @_;
542
my $field2 = shift @_;
543
my $dbh = Bugzilla->dbh;
545
Status("Checking references to $table.$field1 / $table.$field2");
549
my ($refertable, $referfield1, $referfield2, $keyname) = @$ref;
551
Status("... from $refertable.$referfield1 / $refertable.$referfield2");
553
my $d_cross_check = $dbh->selectall_arrayref(qq{
554
SELECT DISTINCT $refertable.$referfield1,
555
$refertable.$referfield2 } .
556
($keyname ? qq{, $refertable.$keyname } : q{}) .
559
ON $refertable.$referfield1 = $table.$field1
560
AND $refertable.$referfield2 = $table.$field2
561
WHERE $table.$field1 IS NULL
562
AND $table.$field2 IS NULL
563
AND $refertable.$referfield1 IS NOT NULL
564
AND $refertable.$referfield2 IS NOT NULL});
566
foreach my $check (@$d_cross_check) {
567
my ($value1, $value2, $key) = @$check;
568
my $alert = "Bad values "$value1", "$value2" found in " .
569
"$refertable.$referfield1 / $refertable.$referfield2";
571
if ($keyname eq 'bug_id') {
572
$alert .= ' (bug ' . BugLink($key) . ')';
575
$alert .= " ($keyname == '$key')";
583
DoubleCrossCheck('attachments', 'bug_id', 'attach_id',
584
['flags', 'bug_id', 'attach_id'],
585
['bugs_activity', 'bug_id', 'attach_id']);
587
DoubleCrossCheck("components", "product_id", "id",
588
["bugs", "product_id", "component_id", "bug_id"],
589
['flagexclusions', 'product_id', 'component_id'],
590
['flaginclusions', 'product_id', 'component_id']);
592
DoubleCrossCheck("versions", "product_id", "value",
593
["bugs", "product_id", "version", "bug_id"]);
595
DoubleCrossCheck("milestones", "product_id", "value",
596
["bugs", "product_id", "target_milestone", "bug_id"],
597
["products", "id", "defaultmilestone", "name"]);
599
###########################################################################
600
# Perform login checks
601
###########################################################################
603
Status("Checking profile logins");
605
my $sth = $dbh->prepare(q{SELECT userid, login_name FROM profiles});
608
while (my ($id, $email) = $sth->fetchrow_array) {
609
validate_email_syntax($email)
610
|| Alert "Bad profile email address, id=$id, <$email>.";
613
###########################################################################
614
# Perform vote/keyword cache checks
615
###########################################################################
617
sub AlertBadVoteCache {
619
Alert("Bad vote cache for bug " . BugLink($id));
622
check_votes_or_keywords();
624
sub check_votes_or_keywords {
625
my $check = shift || 'all';
627
my $dbh = Bugzilla->dbh;
628
my $sth = $dbh->prepare(q{SELECT bug_id, votes, keywords
630
WHERE votes != 0 OR keywords != ''});
636
while (my ($id, $v, $k) = $sth->fetchrow_array) {
645
# If we only want to check keywords, skip checks about votes.
646
_check_votes(\%votes) unless ($check eq 'keywords');
647
# If we only want to check votes, skip checks about keywords.
648
_check_keywords(\%keyword) unless ($check eq 'votes');
654
Status("Checking cached vote counts");
655
my $dbh = Bugzilla->dbh;
656
my $sth = $dbh->prepare(q{SELECT bug_id, SUM(vote_count)
658
$dbh->sql_group_by('bug_id'));
661
my $offer_votecache_rebuild = 0;
663
while (my ($id, $v) = $sth->fetchrow_array) {
665
Alert("Bad vote sum for bug $id");
667
if (!defined $votes->{$id} || $votes->{$id} != $v) {
668
AlertBadVoteCache($id);
669
$offer_votecache_rebuild = 1;
671
delete $votes->{$id};
674
foreach my $id (keys %$votes) {
675
AlertBadVoteCache($id);
676
$offer_votecache_rebuild = 1;
679
if ($offer_votecache_rebuild) {
680
print qq{<a href="sanitycheck.cgi?rebuildvotecache=1">Click here to rebuild the vote cache</a><p>\n};
684
sub _check_keywords {
687
Status("Checking keywords table");
688
my $dbh = Bugzilla->dbh;
689
my $cgi = Bugzilla->cgi;
692
my $keywords = $dbh->selectall_arrayref(q{SELECT id, name
695
foreach (@$keywords) {
696
my ($id, $name) = @$_;
697
if ($keywordids{$id}) {
698
Alert("Duplicate entry in keyworddefs for id $id");
700
$keywordids{$id} = 1;
701
if ($name =~ /[\s,]/) {
702
Alert("Bogus name in keyworddefs for id $id");
706
my $sth = $dbh->prepare(q{SELECT bug_id, keywordid
708
ORDER BY bug_id, keywordid});
712
while (my ($id, $k) = $sth->fetchrow_array) {
713
if (!$keywordids{$k}) {
714
Alert("Bogus keywordids $k found in keywords table");
716
if (defined $lastid && $id eq $lastid && $k eq $lastk) {
717
Alert("Duplicate keyword ids found in bug " . BugLink($id));
723
Status("Checking cached keywords");
725
if (defined $cgi->param('rebuildkeywordcache')) {
726
$dbh->bz_lock_tables('bugs write', 'keywords read', 'keyworddefs read');
729
my $query = q{SELECT keywords.bug_id, keyworddefs.name
731
INNER JOIN keyworddefs
732
ON keyworddefs.id = keywords.keywordid
734
ON keywords.bug_id = bugs.bug_id
735
ORDER BY keywords.bug_id, keyworddefs.name};
737
$sth = $dbh->prepare($query);
744
my ($b, $k) = $sth->fetchrow_array;
745
if (!defined $b || $b != $lastb) {
747
$realk{$lastb} = join(', ', @list);
759
foreach my $b (keys(%$keyword)) {
760
if (!exists $realk{$b} || $realk{$b} ne $keyword->{$b}) {
764
foreach my $b (keys(%realk)) {
765
if (!exists $keyword->{$b}) {
770
@badbugs = sort {$a <=> $b} @badbugs;
771
Alert(scalar(@badbugs) . " bug(s) found with incorrect keyword cache: " .
772
BugListLinks(@badbugs));
774
my $sth_update = $dbh->prepare(q{UPDATE bugs
778
if (defined $cgi->param('rebuildkeywordcache')) {
779
Status("OK, now fixing keyword cache.");
780
foreach my $b (@badbugs) {
782
if (exists($realk{$b})) {
785
$sth_update->execute($k, $b);
787
Status("Keyword cache fixed.");
789
print qq{<a href="sanitycheck.cgi?rebuildkeywordcache=1">Click here to rebuild the keyword cache</a><p>\n};
793
if (defined $cgi->param('rebuildkeywordcache')) {
794
$dbh->bz_unlock_tables();
798
###########################################################################
799
# Check for flags being in incorrect products and components
800
###########################################################################
802
Status('Checking for flags being in the wrong product/component');
804
my $invalid_flags = $dbh->selectall_arrayref(
805
'SELECT DISTINCT flags.id, flags.bug_id, flags.attach_id
808
ON flags.bug_id = bugs.bug_id
809
LEFT JOIN flaginclusions AS i
810
ON flags.type_id = i.type_id
811
AND (bugs.product_id = i.product_id OR i.product_id IS NULL)
812
AND (bugs.component_id = i.component_id OR i.component_id IS NULL)
813
WHERE i.type_id IS NULL');
815
my @invalid_flags = @$invalid_flags;
817
$invalid_flags = $dbh->selectall_arrayref(
818
'SELECT DISTINCT flags.id, flags.bug_id, flags.attach_id
821
ON flags.bug_id = bugs.bug_id
822
INNER JOIN flagexclusions AS e
823
ON flags.type_id = e.type_id
824
WHERE (bugs.product_id = e.product_id OR e.product_id IS NULL)
825
AND (bugs.component_id = e.component_id OR e.component_id IS NULL)');
827
push(@invalid_flags, @$invalid_flags);
829
if (scalar(@invalid_flags)) {
830
if ($cgi->param('remove_invalid_flags')) {
831
Status("OK, now deleting invalid flags.");
832
my @flag_ids = map {$_->[0]} @invalid_flags;
833
$dbh->bz_lock_tables('flags WRITE');
834
# Silently delete these flags, with no notification to requesters/setters.
835
$dbh->do('DELETE FROM flags WHERE id IN (' . join(',', @flag_ids) .')');
836
$dbh->bz_unlock_tables();
837
Status("Invalid flags deleted.");
840
foreach my $flag (@$invalid_flags) {
841
my ($flag_id, $bug_id, $attach_id) = @$flag;
842
Alert("Invalid flag $flag_id for " .
843
($attach_id ? "attachment $attach_id in bug " : "bug ") . BugLink($bug_id));
845
print qq{<a href="sanitycheck.cgi?remove_invalid_flags=1">Click
846
here to delete invalid flags</a><p>\n};
850
###########################################################################
852
###########################################################################
855
my ($middlesql, $errortext, $repairparam, $repairtext) = @_;
856
my $dbh = Bugzilla->dbh;
858
my $badbugs = $dbh->selectcol_arrayref(qq{SELECT DISTINCT bugs.bug_id
860
ORDER BY bugs.bug_id});
862
if (scalar(@$badbugs)) {
863
Alert("$errortext: " . BugListLinks(@$badbugs));
865
$repairtext ||= 'Repair these bugs';
866
print qq{<a href="sanitycheck.cgi?$repairparam=1">$repairtext</a>.},
872
Status("Checking for bugs with no creation date (which makes them invisible)");
874
BugCheck("bugs WHERE creation_ts IS NULL", "Bugs with no creation date",
875
"repair_creation_date", "Repair missing creation date for these bugs");
877
Status("Checking resolution/duplicates");
879
BugCheck("bugs INNER JOIN duplicates ON bugs.bug_id = duplicates.dupe " .
880
"WHERE bugs.resolution != 'DUPLICATE'",
881
"Bug(s) found on duplicates table that are not marked duplicate");
883
BugCheck("bugs LEFT JOIN duplicates ON bugs.bug_id = duplicates.dupe WHERE " .
884
"bugs.resolution = 'DUPLICATE' AND " .
885
"duplicates.dupe IS NULL",
886
"Bug(s) found marked resolved duplicate and not on duplicates table");
888
Status("Checking statuses/resolutions");
890
my @open_states = map($dbh->quote($_), BUG_STATE_OPEN);
891
my $open_states = join(', ', @open_states);
893
BugCheck("bugs WHERE bug_status IN ($open_states) AND resolution != ''",
894
"Bugs with open status and a resolution");
895
BugCheck("bugs WHERE bug_status NOT IN ($open_states) AND resolution = ''",
896
"Bugs with non-open status and no resolution");
898
Status("Checking statuses/everconfirmed");
900
BugCheck("bugs WHERE bug_status = 'UNCONFIRMED' AND everconfirmed = 1",
901
"Bugs that are UNCONFIRMED but have everconfirmed set");
902
# The below list of resolutions is hard-coded because we don't know if future
903
# resolutions will be confirmed, unconfirmed or maybeconfirmed. I suspect
904
# they will be maybeconfirmed, e.g. ASLEEP and REMIND. This hardcoding should
905
# disappear when we have customized statuses.
906
BugCheck("bugs WHERE bug_status IN ('NEW', 'ASSIGNED', 'REOPENED') AND everconfirmed = 0",
907
"Bugs with confirmed status but don't have everconfirmed set");
909
Status("Checking votes/everconfirmed");
911
BugCheck("bugs INNER JOIN products ON bugs.product_id = products.id " .
912
"WHERE everconfirmed = 0 AND votestoconfirm <= votes",
913
"Bugs that have enough votes to be confirmed but haven't been");
915
###########################################################################
917
###########################################################################
919
# Checks for values that are invalid OR
920
# not among the 9 valid combinations
921
Status("Checking for bad values in group_control_map");
922
my $groups = join(", ", (CONTROLMAPNA, CONTROLMAPSHOWN, CONTROLMAPDEFAULT,
923
CONTROLMAPMANDATORY));
925
SELECT COUNT(product_id)
926
FROM group_control_map
927
WHERE membercontrol NOT IN( $groups )
928
OR othercontrol NOT IN( $groups )
929
OR ((membercontrol != othercontrol)
930
AND (membercontrol != } . CONTROLMAPSHOWN . q{)
931
AND ((membercontrol != } . CONTROLMAPDEFAULT . q{)
932
OR (othercontrol = } . CONTROLMAPSHOWN . q{)))};
934
my $c = $dbh->selectrow_array($query);
936
Alert("Found $c bad group_control_map entries");
939
Status("Checking for bugs with groups violating their product's group controls");
941
INNER JOIN bug_group_map
942
ON bugs.bug_id = bug_group_map.bug_id
943
LEFT JOIN group_control_map
944
ON bugs.product_id = group_control_map.product_id
945
AND bug_group_map.group_id = group_control_map.group_id
946
WHERE ((group_control_map.membercontrol = " . CONTROLMAPNA . ")
947
OR (group_control_map.membercontrol IS NULL))",
948
'Have groups not permitted for their products',
949
'createmissinggroupcontrolmapentries',
950
'Permit the missing groups for the affected products
951
(set member control to <code>SHOWN</code>)');
954
INNER JOIN group_control_map
955
ON bugs.product_id = group_control_map.product_id
957
ON group_control_map.group_id = groups.id
958
LEFT JOIN bug_group_map
959
ON bugs.bug_id = bug_group_map.bug_id
960
AND group_control_map.group_id = bug_group_map.group_id
961
WHERE group_control_map.membercontrol = " . CONTROLMAPMANDATORY . "
962
AND bug_group_map.group_id IS NULL
963
AND groups.isactive != 0",
964
"Are missing groups required for their products");
967
###########################################################################
969
###########################################################################
971
Status("Checking for unsent mail");
973
my $time = $dbh->sql_interval(30, 'MINUTE');
974
my $badbugs = $dbh->selectcol_arrayref(qq{
977
WHERE (lastdiffed IS NULL OR lastdiffed < delta_ts)
978
AND delta_ts < now() - $time
982
if (scalar(@$badbugs > 0)) {
983
Alert("Bugs that have changes but no mail sent for at least half an hour: " .
984
BugListLinks(@$badbugs));
986
print qq{<a href="sanitycheck.cgi?rescanallBugMail=1">Send these mails</a>.<p>\n};
989
###########################################################################
991
###########################################################################
993
Status("Sanity check completed.");
994
$template->put_footer();