Upgrade 3.6 -> 3.8; something wacky with users?

RT Users
1

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.

I followed the instructions (but see next paragraph), as far as I know
(I’ve done many RT upgrades over the years), and I didn’t see any
errors. The installation of CPAN dependencies went fine, the make
upgrade went fine, there were no errors when I ran the database upgrade
script. I read the UPGRADING file but didn’t see any actionable items
for me. I don’t see anything unusual in the PostgreSQL, system, or
Apache logs.

The only unusual occurrence was that I initially ran 'sudo make upgrade’
on top of the existing RT directory, but I then decided to move that
directory out of the way and run ‘sudo make upgrade’ again.

RT 3.8 looks great, but hopefully I don’t have a corrupted database. I
do have a backup of the database, of course.

I’d like some advice on how to proceed.

Thanks for any tips,
Kevin Murphy

2

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.

Yikes! I repeated the upgrade from scratch with a fresh restore of the
pre-upgrade database, with identical results. Is it possible that the
database upgrade scripts corrupted something?

Prior to the upgrade, I did a pg_dumpall, and I used that dump to
rebuild the database.

I then followed the upgrade instructions. These database updates were
applied:

  • 3.7.1
  • 3.7.3
  • 3.7.10
  • 3.7.15
  • 3.7.19
  • 3.7.81
  • 3.7.82
  • 3.7.85
  • 3.7.86
  • 3.7.87
  • 3.8.0
  • 3.8.1

-Kevin Murphy

3

Kevin,

The only database changes in this list are 3.7.3/schema.Pg
which just increases the size of a variable length field to
64k from 255. I do not see how that could have the effect
that you are observing.

KenOn Tue, Dec 16, 2008 at 04:04:08PM -0500, Kevin Murphy wrote:

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.

Yikes! I repeated the upgrade from scratch with a fresh restore of the
pre-upgrade database, with identical results. Is it possible that the
database upgrade scripts corrupted something?

Prior to the upgrade, I did a pg_dumpall, and I used that dump to
rebuild the database.

I then followed the upgrade instructions. These database updates were
applied:

  • 3.7.1
  • 3.7.3
  • 3.7.10
  • 3.7.15
  • 3.7.19
  • 3.7.81
  • 3.7.82
  • 3.7.85
  • 3.7.86
  • 3.7.87
  • 3.8.0
  • 3.8.1

-Kevin Murphy

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

4

Start from SELECT * FROM ACL WHERE RightName = ‘OwnTicket’; Check
objects in the list may be there are some relevant details.

I don’t think it’s DB, but it may be either bug or fix of a bug that
uncovered something.On Wed, Dec 17, 2008 at 12:04 AM, Kevin Murphy murphy@genome.chop.edu wrote:

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.

Yikes! I repeated the upgrade from scratch with a fresh restore of the
pre-upgrade database, with identical results. Is it possible that the
database upgrade scripts corrupted something?

Prior to the upgrade, I did a pg_dumpall, and I used that dump to
rebuild the database.

I then followed the upgrade instructions. These database updates were
applied:

  • 3.7.1
  • 3.7.3
  • 3.7.10
  • 3.7.15
  • 3.7.19
  • 3.7.81
  • 3.7.82
  • 3.7.85
  • 3.7.86
  • 3.7.87
  • 3.8.0
  • 3.8.1

-Kevin Murphy

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

5

Kenneth Marshall wrote:

The only database changes in this list are 3.7.3/schema.Pg
which just increases the size of a variable length field to
64k from 255. I do not see how that could have the effect
that you are observing.

I don’t know either, but two updates from 3.6.6 to 3.8.1 resulted in
identical problems - users appearing incorrectly in Owner menus, users
able to see queues they shouldn’t.

Happily, upgrading to 3.6.7 worked. I just went through the exact same
upgrade procedure a third time, but this time from 3.6.6 to 3.6.7 (the
only difference being the manual checking for database upgrade
scripts). This time the resulting RT instance behaves normally.

Because of my 3.8 lust, I’m willing to spend a couple hours
troubleshooting per the advice of Ruslan and others on the list.

-Kevin Murphy

6

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

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

  2. 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.

7

Hi Ruslan,

I don’t know if you saw the end of my previous message in this thread,
so here it is again (rt-validator 3.8.2rc1 output). There are many
CachedGroupMembers problems. Should I attempt to allow rt-validator to
fix them (if it can?) See also the seemingly unrelated SQL error.

As mentioned previously, my RT database works fine with RT 3.6.X but
produces the weird rights errors with 3.8.X. This database has been in
use since 2003. There have been many RT and PostgreSQL upgrades since
then, but there has never been a database problem, to my knowledge.

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.*

8

Hi,
I upgraded my RT from version 3.2.2 to 3.8.1 and noticed the same issue (I see candidate owners who shouldn’t be in the Owner drop-down menu).
I am not sure what is causing this, but I do not have this problem in RT 3.2.2
I did this on opensuse 11; mysql 5; perl 5.10; apache 2

My next step is to apply one update at a time and see if the data corrouption happens.

ThanksFrom: Kevin Murphy murphy@genome.chop.edu
Subject: Re: [rt-users] Upgrade 3.6 → 3.8; database corruption? (was: something wacky with users)
To: rt-users@lists.bestpractical.com
Date: Tuesday, December 16, 2008, 3:04 PM

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.

Yikes! I repeated the upgrade from scratch with a fresh restore of the
pre-upgrade database, with identical results. Is it possible that the
database upgrade scripts corrupted something?

Prior to the upgrade, I did a pg_dumpall, and I used that dump to
rebuild the database.

I then followed the upgrade instructions. These database updates were
applied:

  • 3.7.1
  • 3.7.3
  • 3.7.10
  • 3.7.15
  • 3.7.19
  • 3.7.81
  • 3.7.82
  • 3.7.85
  • 3.7.86
  • 3.7.87
  • 3.8.0
  • 3.8.1

-Kevin Murphy

http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com