Added an optional ability to keep a read-only shadow database, so that

bug queries can be run against it, so that these slow queries won't be
able to tie up the rest of the system.

8 files changed, 224 insertions, 8 deletions

diff --git a/CGI.pl b/CGI.pl
index 41099661b..621bf9111 100644
--- a/CGI.pl
+++ b/CGI.pl
@@ -727,7 +727,10 @@ name=PleaseMailAPassword>
         # This seems like as good as time as any to get rid of old
         # crufty junk in the logincookies table.  Get rid of any entry
         # that hasn't been used in a month.
-        SendSQL("delete from logincookies where to_days(now()) - to_days(lastused) > 30");
+        if ($::dbwritesallowed) {
+            SendSQL("DELETE FROM logincookies " .
+                    "WHERE TO_DAYS(NOW()) - TO_DAYS(lastused) > 30");
+        }
 
         
         PutFooter();
@@ -735,7 +738,10 @@ name=PleaseMailAPassword>
     }
 
     # Update the timestamp on our logincookie, so it'll keep on working.
-    SendSQL("update logincookies set lastused = null where cookie = $::COOKIE{'Bugzilla_logincookie'}");
+    if ($::dbwritesallowed) {
+        SendSQL("UPDATE logincookies SET lastused = null " .
+                "WHERE cookie = $::COOKIE{'Bugzilla_logincookie'}");
+    }
     return $::userid;
 }
 
diff --git a/buglist.cgi b/buglist.cgi
index 297d32255..be55f22b6 100755
--- a/buglist.cgi
+++ b/buglist.cgi
@@ -49,7 +49,9 @@ sub sillyness {
 
 my $serverpush = 0;
 
-ConnectToDatabase();
+my $useshadow = Param("queryagainstshadowdb");
+
+ConnectToDatabase($useshadow);
 
 # print "Content-type: text/plain\n\n";    # Handy for debugging.
 # $::FORM{'debug'} = 1;
diff --git a/checksetup.pl b/checksetup.pl
index 16f2a9c19..7b5658ad6 100755
--- a/checksetup.pl
+++ b/checksetup.pl
@@ -751,6 +751,15 @@ $table{keyworddefs} =
      unique(name)';
 
 
+$table{shadowlog} =
+    'id int not null auto_increment primary key,
+     ts timestamp,
+     reflected tinyint not null,
+     command mediumtext not null,
+
+     index(reflected)';
+
+
 
 ###########################################################################
 # Create tables
diff --git a/defparams.pl b/defparams.pl
index c5cf400e0..5f8d1ccc2 100644
--- a/defparams.pl
+++ b/defparams.pl
@@ -83,7 +83,23 @@ sub check_numeric {
     return "";
 }
     
-
+sub check_shadowdb {
+    my ($value) = (@_);
+    $value = trim($value);
+    if ($value eq "") {
+        return "";
+    }
+    SendSQL("SHOW DATABASES");
+    while (MoreSQLData()) {
+        my $n = FetchOneColumn();
+        if (lc($n) eq lc($value)) {
+            return "The $n database already exists.  If that's really the name you want to use for the backup, please CAREFULLY make the existing database go away somehow, and then try again.";
+        }
+    }
+    SendSQL("CREATE DATABASE $value");
+    SendSQL("INSERT INTO shadowlog (command) VALUES ('SYNCUP')", 1);
+    return "";
+}
 
 @::param_list = ();
 
@@ -142,6 +158,18 @@ DefParam("usequip",
 	"b",
 	1);
 
+DefParam("shadowdb",
+         "If non-empty, then this is the name of another database in which Bugzilla will keep a shadow read-only copy of everything.  This is done so that long slow read-only operations can be used against this db, and not lock up things for everyone else.  Turning on this parameter will create the given database; be careful not to use the name of an existing database with useful data in it!",
+         "t",
+         "",
+         \&check_shadowdb);
+
+DefParam("queryagainstshadowdb",
+         "If this is on, and the shadowdb is set, then queries will happen against the shadow database.",
+         "b",
+         0);
+         
+
 DefParam("usedespot",
          "If this is on, then we are using the Despot system to control our database of users.  Bugzilla won't ever write into the user database, it will let the Despot code maintain that.  And Bugzilla will send the user over to Despot URLs if they need to change their password.  Also, in that case, Bugzilla will treat the passwords stored in the database as being crypt'd, not plaintext.",
          "b",
diff --git a/doeditparams.cgi b/doeditparams.cgi
index 7e0a4f193..d37bb042a 100755
--- a/doeditparams.cgi
+++ b/doeditparams.cgi
@@ -72,6 +72,9 @@ foreach my $i (@::param_list) {
 WriteParams();
 
 unlink "data/versioncache";
+print "<PRE>";
+system("./syncshadowdb -v");
+print "</PRE>";
 
 print "OK, done.<p>\n";
 print "<a href=editparams.cgi>Edit the params some more.</a><p>\n";
diff --git a/globals.pl b/globals.pl
index 820749769..00e1cfd25 100644
--- a/globals.pl
+++ b/globals.pl
@@ -74,10 +74,17 @@ $::dontchange = "--do_not_change--";
 $::chooseone = "--Choose_one:--";
 $::defaultqueryname = "(Default query)";
 $::unconfirmedstate = "UNCONFIRMED";
+$::dbwritesallowed = 1;
 
 sub ConnectToDatabase {
+    my ($useshadow) = (@_);
     if (!defined $::db) {
-	$::db = Mysql->Connect($db_host, $db_name, $db_user, $db_pass)
+        my $name = $db_name;
+        if ($useshadow) {
+            $name = Param("shadowdb");
+            $::dbwritesallowed = 0;
+        }
+	$::db = Mysql->Connect($db_host, $name, $db_user, $db_pass)
             || die "Can't connect to database server.";
     }
 }
@@ -100,11 +107,31 @@ sub SqlLog {
 
 
 sub SendSQL {
-    my ($str) = (@_);
+    my ($str, $dontshadow) = (@_);
+    my $iswrite =  ($str =~ /^(INSERT|REPLACE|UPDATE|DELETE)/i);
+    if ($iswrite && !$::dbwritesallowed) {
+        die "Evil code attempted to write stuff to the shadow database.";
+    }
+    if ($str =~ /^LOCK TABLES/ && $str !~ /shadowlog/) {
+        $str =~ s/^LOCK TABLES/LOCK TABLES shadowlog WRITE, /;
+    }
     SqlLog($str);
     $::currentquery = $::db->query($str)
 	|| die "$str: $::db_errstr";
     SqlLog("Done");
+    if (!$dontshadow && $iswrite && Param("shadowdb")) {
+        my $q = SqlQuote($str);
+        my $insertid;
+        if ($str =~ /^(INSERT|REPLACE)/i) {
+            SendSQL("SELECT LAST_INSERT_ID()");
+            $insertid = FetchOneColumn();
+        }
+        SendSQL("INSERT INTO shadowlog (command) VALUES ($q)", 1);
+        if ($insertid) {
+            SendSQL("SET LAST_INSERT_ID = $insertid");
+        }
+        system("./syncshadowdb &");
+    }
 }
 
 sub MoreSQLData {
diff --git a/process_bug.cgi b/process_bug.cgi
index e432837e2..047016b04 100755
--- a/process_bug.cgi
+++ b/process_bug.cgi
@@ -613,8 +613,8 @@ delete $::FORM{'resolution'};   # Make sure we don't test the resolution
 #
 foreach my $id (@idlist) {
     my %dependencychanged;
-    my $write = "LOW_PRIORITY WRITE"; # Might want to make a param to control
-                                      # whether we do LOW_PRIORITY ...
+    my $write = "WRITE";        # Might want to make a param to control
+                                # whether we do LOW_PRIORITY ...
     SendSQL("LOCK TABLES bugs $write, bugs_activity $write, cc $write, " .
             "profiles $write, dependencies $write, votes $write, " .
             "keywords $write, longdescs $write, fielddefs $write, " .
diff --git a/syncshadowdb b/syncshadowdb
new file mode 100755
index 000000000..6c6479e19
--- /dev/null
+++ b/syncshadowdb
@@ -0,0 +1,141 @@
+#!/usr/bonsaitools/bin/perl -w
+# -*- Mode: perl; indent-tabs-mode: nil -*-
+#
+# The contents of this file are subject to the Mozilla Public
+# License Version 1.1 (the "License"); you may not use this file
+# except in compliance with the License. You may obtain a copy of
+# the License at http://www.mozilla.org/MPL/
+#
+# Software distributed under the License is distributed on an "AS
+# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
+# implied. See the License for the specific language governing
+# rights and limitations under the License.
+#
+# The Original Code is the Bugzilla Bug Tracking System.
+#
+# The Initial Developer of the Original Code is Netscape Communications
+# Corporation. Portions created by Netscape are
+# Copyright (C) 1998 Netscape Communications Corporation. All
+# Rights Reserved.
+#
+# Contributor(s): Terry Weissman <terry@mozilla.org>
+#                 David Gardiner <david.gardiner@unisa.edu.au>
+
+use diagnostics;
+use strict;
+
+require "globals.pl";
+
+# Shut up misguided -w warnings about "used only once".  "use vars" just
+# doesn't work for me.
+
+sub sillyness {
+    my $zz;
+    $zz = $::dbwritesallowed;
+}
+
+my $verbose = 0;
+if ($ARGV[0] eq '-v') {
+    $verbose = 1;
+}
+$| = 1;
+
+sub Verbose ($) {
+    my ($str) = (@_);
+    if ($verbose) {
+        print $str, "\n";
+    }
+}
+    
+
+
+my $db_name = "bugs";
+require "localconfig";
+
+if (!Param("shadowdb")) {
+    Verbose("We don't have shadow databases turned on; no syncing performed.");
+    exit;
+}
+
+ConnectToDatabase(1);
+$::dbwritesallowed = 1;
+
+SendSQL("SELECT GET_LOCK('synclock', 1)");
+if (!FetchOneColumn()) {
+    Verbose("Couldn't get the lock to do the shadow database syncing.");
+    exit;
+}
+
+my $shadowtable = "$db_name.shadowlog";
+
+SendSQL("SELECT id FROM $shadowtable " .
+        "WHERE reflected = 0 AND command = 'SYNCUP'");
+
+if (FetchOneColumn()) {
+    Verbose("Syncing up the shadow database by copying entire database in.");
+    my @tables;
+    SendSQL("SHOW TABLES");
+    while (MoreSQLData()) {
+        my $table = FetchOneColumn();
+        push(@tables, $table);
+    }
+    foreach my $table (@tables) {
+        Verbose("Dropping old shadow table $table");
+        SendSQL("DROP TABLE $table");
+    }
+    # Carefully lock the whole real database for reading, except for the
+    # shadowlog table, which we lock for writing.  Then dump everything
+    # into the shadowdb database.  Then mark everything in the shadowlog
+    # as reflected.  Only then unlock everything.  This sequence causes
+    # us to be sure not to miss anything or get something twice.
+    SendSQL("USE $db_name");
+    SendSQL("SHOW TABLES");
+    @tables = ();
+    my $query = "LOCK TABLES shadowlog WRITE";
+    while (MoreSQLData()) {
+        my $table = FetchOneColumn();
+        if ($table ne "shadowlog") {
+            $query .= ", $table READ";
+            push(@tables, $table);
+        }
+    }
+    Verbose("Locking entire database");
+    SendSQL($query);
+    my $tablelist = join(' ', @tables);
+    Verbose("Doing the actual sync (can take a real long time!)");
+    my $extra = "";
+    if ($verbose) {
+        $extra = "-v";
+    }
+    open(MYSQL, "mysqldump -l -e $db_name $tablelist | mysql $extra " .
+         Param("shadowdb") . "|") || die "Couldn't do db copy";
+    my $count = 0;
+    while (<MYSQL>) {
+        print ".";
+        $count++;
+        if ($count % 70 == 0) {
+            print "\n";
+        }
+    }
+    close(MYSQL);
+    Verbose("");
+    
+    
+    SendSQL("UPDATE shadowlog SET reflected = 1 WHERE reflected = 0", 1);
+    SendSQL("UNLOCK TABLES");
+    Verbose("OK, done.");
+}
+
+while (1) {
+    SendSQL("SELECT id, command FROM $shadowtable WHERE reflected = 0 " .
+            "ORDER BY id LIMIT 1");
+    my ($id, $command) = (FetchSQLData());
+    if (!$id) {
+        last;
+    }
+    Verbose("Executing command in shadow db: $command");
+    SendSQL($command, 1);
+    SendSQL("UPDATE $shadowtable SET reflected = 1 WHERE id = $id", 1);
+}
+
+SendSQL("SELECT RELEASE_LOCK('synclock')");