I finally got around to upgrading RT 3.6.7 to 3.8.8 today. Everything went smoothly except for one minor hiccup. The database schema upgrade for 3.8.3 failed with the following:
[Wed Oct 27 13:06:01 2010] [crit]: DBD::Pg::st execute failed: ERROR: relation “groupmembers1” already exists at /usr/local/lib/perl5/site_perl/5.10.1/RT/Handle.pm line 506. (/usr/local/lib/perl5/site_perl/5.10.1/RT.pm:384)
DBD::Pg::st execute failed: ERROR: relation “groupmembers1” already exists at /usr/local/lib/perl5/site_perl/5.10.1/RT/Handle.pm line 506.
I’m not sure if I manually added an index of that name at some point, but the index I had under that name prior was
CREATE INDEX groupmembers1 ON groupmembers USING btree (groupid);
However, the rt 3.8.3 upgrade wants to create
CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers(GroupId, MemberId);
Not a big deal, but there is non-unique data in that table already. For now I just made that a non-unique index, but I’d rather bring my schema inline with the “official” one.
Any recommendations on how to fix? It is hard to trace the schema relations since there are not FK’s defined, so I’m not sure how to update the group id references or if I need to. Also, is this table supposed to be really huge? I have over 700k tuples in it!
Thanks!
Any recommendations on how to fix? It is hard to trace the schema relations since there are not FK’s defined, so I’m not sure how to update the group id references or if I need to. Also, is this table supposed to be really huge? I have over 700k tuples in it!
In awesome form, I’ll reply to myself 
Looks like there are two “groups” that are duplicate groupid+member id. It should be plenty easy to clean that up. What other table refers to the groupmembers.id field? I can just update those, delete the dupes, and have a nice unique index.
Basically I did this:
select groupid,memberid,count() from groupmembers group by groupid,memberid having count() > 1;
It gave me this:
groupid | memberid | count
466380 | 134922 | 2
386385 | 134922 | 2
Any recommendations on how to fix? It is hard to trace the schema
relations since there are not FK’s defined, so I’m not sure how to
update the group id references or if I need to. Also, is this
table supposed to be really huge? I have over 700k tuples in it!
Size depends on your installation size.
You may be interested in sbin/rt-validator’s output
-kevin
You may be interested in sbin/rt-validator’s output
Wow. 5.5 hours later it deleted a fair number of dangling data rows.
However, my two duplicated groupmembers rows are still there.
What trouble will it cause to not be a unique index on those columns?
You may be interested in sbin/rt-validator’s output
Wow. 5.5 hours later it deleted a fair number of dangling data rows.
However, my two duplicated groupmembers rows are still there.
What trouble will it cause to not be a unique index on those columns?
validator doesn’t check uniqueness of group members records. So at
this momement you have validated data and I think the right way to go
is to delete duplicated group members records and run validator once
again so it will cleanup cached group members records. As far as I can
see you should delete both records in GM to properly clean CGM table,
then add back members into groups using either UI or API.
Best regards, Ruslan.