Mailing List Archive: Request Tracker: Devel

piping up about having proper referential integrity in the DB...

Index | Next | Previous | View Threaded

vivek at khera

Oct 19, 2009, 12:53 PM

Post #1 of 4 (384 views)
Permalink

piping up about having proper referential integrity in the DB...

For the last few weeks, one of my support staff was not getting
watcher notifications for one of our queue groups because he was
neither in nor out of the required group. I believe having RI in the
DB would have prevented this.

The details:

We have groups called FooWatchers for all people who should be
watchers for a given set of queues related to Foo. One of my staff
went on vacation, so we removed him from the group. It seemed to have
worked fine, as he was no longer getting those messages. He was also
removed from another group at the same time.

Upon his return, one of the groups added him back just fine. The
other, however, reported that the user was already in the group. The
groups membership page does not list him as a member, and indeed his
name is in the "add users" list so we can add him.

I turned on statement logging in postgres, and tracing the queries RT
made, discovered that his ID was still listed as a member of the group
in the GroupMembers table.

Issuing the query 'DELETE FROM GroupMembers WHERE GroupId = 325782 and
MemberId=146360' then resubmitting the group membership form instantly
worked to add the membership back.

So once again, I really really implore you to add proper foreign key
referential integrity checks into the DB itself, to avoid the DB from
getting munged like this.

Versions: FreeBSD 6.3, Postgres 8.3.7, RT 3.6.7, Perl 5.8.9 (man I
have a lot of upgrading to do on this box...)
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

jesse at bestpractical

Oct 20, 2009, 12:18 PM

Post #2 of 4 (343 views)
Permalink

Re: piping up about having proper referential integrity in the DB... [In reply to]

Vick,

> I turned on statement logging in postgres, and tracing the queries RT
> made, discovered that his ID was still listed as a member of the group
> in the GroupMembers table.

But not in the CachedGroupMembers table? That seems surprising since all
changes to those two tables should happen in lockstep inside a
transaction that gets rolled back on failure.

> Issuing the query 'DELETE FROM GroupMembers WHERE GroupId = 325782 and
> MemberId=146360' then resubmitting the group membership form instantly
> worked to add the membership back.
>
>
> So once again, I really really implore you to add proper foreign key
> referential integrity checks into the DB itself, to avoid the DB from
> getting munged like this.

Send a patch. I'll drop it into our testing infrastructure. The last
time this came up, however, someone told me that to do this "properly",
I would have to either recast the entire schema to use features that only
work on a single database or create a Transactions table for each other
table in RT, create an Attributes table for each other table in RT,
create an ObjectCustomFields table for each table in RT and create an
ACL table for each table in RT. Neither of those solutions was
particularly appealing. A good partial solution that doesn't break RT
would be great, though.

Best,
Jesse
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

ruslan.zakirov at gmail

Oct 20, 2009, 2:09 PM

Post #3 of 4 (339 views)
Permalink

Re: piping up about having proper referential integrity in the DB... [In reply to]

On Tue, Oct 20, 2009 at 11:18 PM, Jesse Vincent <jesse[at]bestpractical.com> wrote:
> Vick,
>
>> I turned on statement logging in postgres, and tracing the queries RT
>> made, discovered that his ID was still listed as a member of the group
>> in the GroupMembers table.
>
> But not in the CachedGroupMembers table? That seems surprising since all
> changes to those two tables should happen in lockstep inside a
> transaction that gets rolled back on failure.

This is possible if rt-validator from RT 3.8.1 is used with --resolve.
In 3.8.2 this has been fixed and rt-validator can resurrect CGM table
from scratch.

--
Best regards, Ruslan.
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

vivek at khera

Oct 21, 2009, 9:01 AM

Post #4 of 4 (330 views)
Permalink

Re: piping up about having proper referential integrity in the DB... [In reply to]

On Tue, Oct 20, 2009 at 3:18 PM, Jesse Vincent <jesse[at]bestpractical.com> wrote:
>> I turned on statement logging in postgres, and tracing the queries RT
>> made, discovered that his ID was still listed as a member of the group
>> in the GroupMembers table.
>
> But not in the CachedGroupMembers table? That seems surprising since all
> changes to those two tables should happen in lockstep inside a
> transaction that gets rolled back on failure.

This confused me as well. Not having been through the schema of RT in
years (last time I dug thru it was for RT 3.4 when I came up with some
better indexing for postgres) I didn't know where else to look. But
whatever the page was using to check membership was not finding it...
I'll assume that was CachedGroupMembers.

I also recall the last time this was discussed. From what I remember,
there were issues with certain values being used to represent "no
relation" state, rather than using NULL. I also remember something
about needing trigger functions to enforce certain relationships, and
that was not viable for MySQL at the time. But that's all vague...

Perhaps I'll dig into it again once I get updated to RT 3.8. Our
server is slated for a major overhaul, so it will happen sometime
soon...
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact lists@gossamer-threads.com