110899 - support MySQL mirroring with read-only version of Bugzilla

Status: RESOLVED WORKSFORME

(Bugzilla :: Administration, task, P3)

Description

[Myk Melez [:myk] [@mykmelez]]

Recent versions of MySQL provide mirroring capabilities that allow us to store
an exact copy of our Bugzilla database on another server.  This is a better
solution than using a shadow database since it is more scalable, less prone to
bugginess, and does not require potentially large amounts of downtime every day
(at b.m.o. this downtime now exceeds 40 minutes).

Bugzilla should support the mirroring capabilities of MySQL by providing a
switch to turn an installation of Bugzilla into read-only mode so users can use
it to read data from the database but get taken to the master installation of
Bugzilla when making changes.

This *may* be as simple as adding the "urlbase" parameter to the form submission
URLs of forms that update data and configuring mirror installations with the
master's "urlbase".

Comment 1

[Matthew Tuck [:CodeMachine]]

Bugzilla typically does various writing even for no-change access.  For example,
logincookies.

These don't need to be backed up so could perhaps be placed in a
separate database.  Can we lock off only certain tables?

Comment 2

[Matthew Tuck [:CodeMachine]]

Sorry, forgot to mention the other-database option is suboptimal if we want to
add referential constraints later.

Comment 3

[Matthew Tuck [:CodeMachine]]

The above comment assumes you can't have inter-database referential constraints.

Comment 4

[Bradley Baetz (:bbaetz)]

"Bugzilla should support the mirroring capabilities of MySQL by providing a
switch to turn an installation of Bugzilla into read-only mode so users can use
it to read data from the database but get taken to the master installation of
Bugzilla when making changes."

You could make show_bug use the shadowdb, but then you'll have extra midairs,
and other bad/annoying things.

I'm not sure what you mean otherwise.

The rest of this is a dupe of bug 124589, which has a patch to allow the
shadowdb to be another db, possibly on a different host/port, to main db.

mattyt: we don't want to start joining across dbs:

a) Its not portable
b) it may avoid some optimisations, since stuff has to go through an interdb
protocol, rather than directly.
c) Its not worth it
d) Its not possible with replication, because they'd be on a different server
(you can't replicate to the same server)

Comment 5

[Myk Melez [:myk] [@mykmelez]]

Attachment: patch v1: does the job

Here's the patch I applied to a copy of the b.m.o Bugzilla installation to make
it read-only.  The patch adds a "writeurlbase" parameter to Bugzilla that gets
prepended to the value of the "action" parameter for forms whose submission
changes data.  This way you can set up a Bugzilla that provides read-only
access to Bugzilla, and when users submit forms to change things the form
submission goes to the main writable installation.

This patch assumes a separate database ("shard_db") for storing logincookies,
since the installation will have to deal with its own login cookies. 
Theoretically, if the servers were in the same domain, they could use the same
login cookies, but that's not the case for the installation I have set up (my
workstation, which is behind a firewall and can't have a *.mozilla.org domain).


In addition to this patch, I also configured Bugzilla to use a database user
that has only read access to the database.

Comment 6

[Bradley Baetz (:bbaetz)]

You can't do cross-db queries portably - only mysql supports them, basically.
This patch is a massive, massive hack which will break once I finish merging all
my db stuff.

Why can't all http pages be served from your mirror, with db writes using the
main db, and a local shadow? Just hack your copy to have show_bug use the
shadowdb, which is a one line patch to handle 40% of bmo's pages (buglist.cgi's
15% already uses the shadow)

Comment 7

[Dave Miller [:justdave]]

Bugzilla 2.20 feature set is now frozen as of 15 Sept 2004.  Anything flagged
enhancement that hasn't already landed is being pushed out.  If this bug is
otherwise ready to land, we'll handle it on a case-by-case basis, please set the
blocking2.20 flag to '?' if you think it qualifies.

Comment 8

[Dave Miller [:justdave]]

Reassigning bugs that I'm not actively working on to the default component owner
in order to try to make some sanity out of my personal buglist.  This doesn't
mean the bug isn't being dealt with, just that I'm not the one doing it.  If you
are dealing with this bug, please assign it to yourself.

Comment 9

[Frédéric Buclin]

With MySQL replication, this is now WFM. We already optimized Bugzilla to use slaves most of the time, except when you edit the DB, in which case we switch back to the master DB.