Admin ticket assignment depending on date

RT Users
21

A simple solution would be to mysqldump the database on the server,
and rsync the data over to your laptop, and then make an import on
your local setup.

Yes, that would do it. However, that would lead to all of the data
being brought over the network (and imported) each time, while using
replication would bring this down to just deltas. Yes, with
sufficiently infrequent deltas the update log may actually be larger
than the end result, but that is not the environment I’m interested in
and not typical of a ticketing system with thousands of old (hopefully
resolved) tickets laying around in the database.

No, rsync is intelligent, you should look into it. It can sync through
ssh, or what you prefer.

But you have a point, all data is imported each time. This is bad if
you want write-access in your read-only client db. Maybe you can diff
the data on the server between exports, and use the intelligent SQL
output from mysqldump to recreate the database on the “client”.

Btw, this is the output from rsync when I sync mail:

wrote 58862 bytes read 90700 bytes 3021.45 bytes/sec
total size is 72043419 speedup is 481.70

patrik_wallstrom->foodfight->pawal@blipp.com->+46-709580442

22

To make things worse, we are not always in one place. We travel, we
lose Internet connectivity (typically in hotel rooms, airports,
etc). At the same time such (disconnected) occasions are often the
best to get things off your personal list of things to do.

So what I would like to do is run RT on top of a replicated MySQL
db with slave replicas in our laptops. I fully realize that I

The RIPE NCC Operation’s ticket system runs on replicated mysql servers
for redundancy and general paranoia reasons. I have considered directing
read operations to one server, and write operations to the master, but
haven’t gotten around to that as yet.

Your main limitation with running replication from your master to your
laptop-slaves (the only way to do this, obviously over a secured
transport) is to ensure that each slave has enough disk to keep the entire
database. This is not a serious problem in today’s high-disk-capacity
world.

With disconnected operations, how do you create a new ticket? Have my
userid in the name of it? I don’t know, myself, I haven’t put that much
brainpower into it.

I’ll say ‘trivial in concept, not so trivial in implementation’.

The main problem that you face is keeping the same numbering system. Once
you realise that you don’t need to, you’ve got a lot more options open.

Elsewhere in the thread, we’re using database replication. Cool, lets
continue to do so.

Lets assume that example.com has an rt system, with its numbering system
looking like ‘[example.com #321]’. Lets assume that the database behind
this runs on ‘master.example.com’ in the ‘rt’ database. (master:rt).

Lets also assume that master:rt is replicated to every slave (laptop) that
example.com has, as slaveX.example.com:rt . Easy-peasy so far.

Now, if we give each slave their own database and numbering system, eg
‘slaveX:slaveX’ and ‘[slaveX.example.com #123]’, we can get updates sent
back to the master server by having the master database server be a slave
for each seperate slaveX database. The Links table can provide the
linkage between ‘[example.com #321]’ and ‘[slaveX.example.com #123]’.

A little bit of magic on the master (also on the customised RT install on
each slave) keeps everything in sync between the various slaveX databases
and the main RT databases.

( I have a feeling that this is one of those things where I can see the
solution clearly, but don’t have a suitable X-dimensional drawing
instrument to put the solution down on )

                         Bruce Campbell                            RIPE
               Systems/Network Engineer                             NCC
             www.ripe.net - PGP562C8B1B                      Operations
23

No, rsync is intelligent, you should look into it. It can sync through
ssh, or what you prefer.

Databases are slightly different creatures, and generally don’t take
kindly to having their raw files changed without their knowledge. Please
don’t consider using rsync on active database files.

                         Bruce Campbell                            RIPE
               Systems/Network Engineer                             NCC
             www.ripe.net - PGP562C8B1B                      Operations
24

No, rsync is intelligent, you should look into it. It can sync through
ssh, or what you prefer.

Databases are slightly different creatures, and generally don’t take
kindly to having their raw files changed without their knowledge. Please
don’t consider using rsync on active database files.

My suggestion was regarding the replication of a mysqldump file with
pure SQL-statements used for importing to the slave database. You can
mysqldump an active database.

patrik_wallstrom->foodfight->pawal@blipp.com->+46-709580442

25

-----Original Message-----
From: Bruce Campbell [mailto:bruce_campbell@ripe.net]
Sent: Tuesday, April 16, 2002 7:31 PM
To: rt-users@lists.fsck.com
Subject: Re: [rt-users] RT to go? Dependency graphs?

The RIPE NCC Operation’s ticket system runs on replicated
mysql servers
for redundancy and general paranoia reasons. I have
considered directing
read operations to one server, and write operations to the master, but
haven’t gotten around to that as yet.

Good to hear replicated MySQL servers are being used already.

To return to Johan Ihren’s initial question: consider
that we want to deploy an RT slave-server which is used
strictly in read-only mode (searching and viewing tickets).

Would it be possible to run a read-only
RT slave-server as follows?
→ halt RT on the master server
→ install MySQL on the slave server and
configure 1-way replication of the
rt database from the master to the
slave server
→ install RT on the slave server,
and subsequently modify the DB_RT_USER
user in MySQL so that he only has
read-access to the RT tables
→ re(start) RT on the master server
and on the slave server

This surely won’t win a prize for
elegance, but will it work at all?
The idea of DB_RT_USER only having
read-access to MySQL tables would
enforce data-integrity (all updates
are forced to happen on the master
server). The question is if
the RT on the slave server will work
at all? (assuming users there only search
and view tickets).

Thanks,

Patrick

26

-----Original Message-----
From: Bruce Campbell [mailto:bruce_campbell@ripe.net]

The RIPE NCC Operation’s ticket system runs on replicated
mysql servers
for redundancy and general paranoia reasons. I have
considered directing
read operations to one server, and write operations to the master, but
haven’t gotten around to that as yet.

Good to hear replicated MySQL servers are being used already.

To return to Johan Ihren’s initial question: consider
that we want to deploy an RT slave-server which is used
strictly in read-only mode (searching and viewing tickets).

Would it be possible to run a read-only
RT slave-server as follows?

Ok, what I’ve done to test this out is:

Set up mysql replication of the rt database
(already done, long time ago ;) )

Allowed rt_user to SELECT and modify the rt database on the master.

Allowed rt_user to SELECT only on the slave.

Copied config.pm, rt and rt-q to a temp directory.

Edited tmp/config.pm to point to the slave database host

Edited tmp/rt and tmp/rt-q to point to tmp/config.pm

So, I now have custom versions of the rt and rt-q scripts (rt-q is my
in-house reqq-like script) which will use the slave database server in
read-only mode.

tmp/rt-q works as expected (its only doing selects after-all)

tmp/rt displaying tickets works as expected.

Trying to add a comment to a test ticket on the slave database fails
silently (ugh).

This surely won’t win a prize for
elegance, but will it work at all?

Yes.

The idea of DB_RT_USER only having
read-access to MySQL tables would
enforce data-integrity (all updates
are forced to happen on the master
server). The question is if
the RT on the slave server will work
at all? (assuming users there only search
and view tickets).

Yes.

                         Bruce Campbell                            RIPE
               Systems/Network Engineer                             NCC
             www.ripe.net - PGP562C8B1B                      Operations
27

Thanks a lot!