[rt-users] Upgrade 3.6 -> 3.8; database corruption?

[Kevin Murphy]

Ruslan Zakirov wrote:
> Start from SELECT * FROM ACL WHERE RightName = 'OwnTicket'; Check
> objects in the list may be there are some relevant details.
>
>   
>> Kevin Murphy wrote:
>>     
>>> I just upgraded from RT 3.6.6 to 3.8.1 on Mac OS X Server 10.4.11, using
>>> PostgreSQL 8.3.5.
>>> Creating a ticket, I see a couple candidate owners who shouldn't be in
>>> the Owner drop-down menu, even including a non-privileged user.
>>>       


1) I'll need some hints.  I presume I follow into the groups (Group) and 
??? (AdminCC, Requestor,Cc) tables?  The query output (of my 3.6.7 
database) looks like this:

 id  | principaltype | principalid | rightname | objecttype | objectid | 
delegatedby | delegatedfrom
-----+---------------+-------------+-----------+------------+----------+-------------+---------------
   2 | Group         |          11 | OwnTicket | RT::System |        1 
|           0 |             0
 174 | Group         |        4562 | OwnTicket | RT::Queue  |       16 
|           0 |             0
 213 | Group         |        6585 | OwnTicket | RT::Queue  |       18 
|           0 |             0
 261 | Group         |          94 | OwnTicket | RT::Queue  |       13 
|           0 |             0
 236 | Group         |        7882 | OwnTicket | RT::Queue  |       19 
|           0 |             0
 284 | Group         |          23 | OwnTicket | RT::Queue  |       13 
|           0 |             0
 307 | Group         |        7949 | OwnTicket | RT::Queue  |        5 
|           0 |             0
 328 | Group         |          30 | OwnTicket | RT::Queue  |        6 
|           0 |             0
 352 | Group         |        8610 | OwnTicket | RT::Queue  |        9 
|           0 |             0
 375 | Group         |        8615 | OwnTicket | RT::Queue  |       20 
|           0 |             0
 394 | Group         |          30 | OwnTicket | RT::Queue  |        1 
|           0 |             0
 411 | Group         |          30 | OwnTicket | RT::Queue  |        5 
|           0 |             0
 423 | Group         |        9022 | OwnTicket | RT::Queue  |        3 
|           0 |             0
 447 | Group         |          48 | OwnTicket | RT::Queue  |       21 
|           0 |             0
 469 | Group         |        6136 | OwnTicket | RT::Queue  |       21 
|           0 |             0
 508 | Group         |       11105 | OwnTicket | RT::Queue  |       22 
|           0 |             0
 632 | AdminCc       |       11566 | OwnTicket | RT::Queue  |       25 
|           0 |             0
 645 | Group         |       11603 | OwnTicket | RT::Queue  |       25 
|           0 |             0
 695 | Group         |           3 | OwnTicket | RT::Queue  |       23 
|           0 |             0
 709 | Requestor     |       11551 | OwnTicket | RT::Queue  |       23 
|           0 |             0
 619 | Group         |       11603 | OwnTicket | RT::Queue  |       24 
|           0 |             0
 665 | Group         |       11548 | OwnTicket | RT::Queue  |       23 
|           0 |             0
 602 | Group         |        4557 | OwnTicket | RT::Queue  |       24 
|           0 |             0
 737 | Cc            |       11549 | OwnTicket | RT::Queue  |       23 
|           0 |             0
 755 | Group         |          23 | OwnTicket | RT::Queue  |       12 
|           0 |             0
 778 | Group         |          48 | OwnTicket | RT::Queue  |       12 
|           0 |             0
 795 | Group         |       11105 | OwnTicket | RT::Queue  |       24 
|           0 |             0
 814 | Group         |       11105 | OwnTicket | RT::Queue  |       26 
|           0 |             0
 837 | Group         |       10980 | OwnTicket | RT::Queue  |       13 
|           0 |             0
 860 | Group         |       13972 | OwnTicket | RT::Queue  |       27 
|           0 |             0
 886 | Group         |        4557 | OwnTicket | RT::Queue  |       27 
|           0 |             0
 904 | AdminCc       |       14380 | OwnTicket | RT::Queue  |       28 
|           0 |             0
 913 | Cc            |       14379 | OwnTicket | RT::Queue  |       28 
|           0 |             0
 927 | Group         |       12221 | OwnTicket | RT::Queue  |       23 
|           0 |             0
 943 | Group         |       15773 | OwnTicket | RT::Queue  |       29 
|           0 |             0

2) Should I take this off-line with Ruslan, because there's going to be 
a lot of back-and-forth and lengthy posts?

3) Out of curiosity and complete ignorance, I ran the rt-validator from 
3.8.2rc1 on my 3.6.7 database, and this is what I got:

Name "RT::DatabaseType" used only once: possible typo at 
sbin/rt-validator line 133.
Record #15423 in CachedGroupMembers references not existent record in 
CachedGroupMembers
    ImmediateParentId => '6585' => GroupId
    MemberId => '6329' => MemberId
    Via => '15420' => id

... and 109 more like the previous ...
   
Principal #22 is member of #6585 when #6585 is member of #6166,
but there is no cached GM record that 22 is member of #6166.

... and 219 more like the previous ...

[Wed Dec 17 15:59:28 2008] [warning]: DBD::Pg::st execute failed: 
ERROR:  syntax error at or near "WHERE"
LINE 4: WHERE
        ^ at sbin/rt-validator line 1060. (sbin/rt-validator:1060)
[Wed Dec 17 15:59:28 2008] [crit]: couldn't execute SELECT m.id, g.id, 
g.Instance
FROM
    Groups g JOIN Attachments m
WHERE
    g.Domain = ?
    AND g.Type = ?
    AND g.id = m.Creator

    Error: ERROR:  syntax error at or near "WHERE"
LINE 4: WHERE
        ^ at sbin/rt-validator line 1060. 
(/usr/local/src/rt-3.8.2rc1/sbin/../lib/RT.pm:377)
couldn't execute SELECT m.id, g.id, g.Instance
FROM
    Groups g JOIN Attachments m
WHERE
    g.Domain = ?
    AND g.Type = ?
    AND g.id = m.Creator

    Error: ERROR:  syntax error at or near "WHERE"
LINE 4: WHERE
        ^ at sbin/rt-validator line 1060.