Mailing List Archive

I should add that my upgrade process was always to install a clean RT
(4.2.7) and then dump/import/upgrade the RT database first from 3.6.6 ->
3.8.17 and then from 3.8.17 -> 4.2.7 using:

./configure --with-db-database=rt_gsits --with-web-user=apache
--with-web-group=apache --with-rt-group=rt \
--with-db-type=Pg --enable-gd --enable-graphviz --prefix=/data/rt/admin

make testdeps

sbin/rt-setup-database --dba postgres --action upgrade

--
Brian McNally
--
RT Training - November 4-5 Los Angeles
http://bestpractical.com/training

On 09/25/2014 08:05 PM, Brian McNally wrote:
> rt3_admin=# SELECT main.* FROM Scrips main JOIN ObjectScrips
> ObjectScrips_1 ON ( ObjectScrips_1.Scrip = main.id ) JOIN
> ScripConditions ScripConditions_2 ON ( ScripConditions_2.id =
> main.ScripCondition ) WHERE (ObjectScrips_1.ObjectId = '3' OR
> ObjectScrips_1.ObjectId = '0') AND (ObjectScrips_1.Stage =
> 'TransactionCreate') AND (ScripConditions_2.ApplicableTransTypes LIKE
> '%Create%' OR ScripConditions_2.ApplicableTransTypes LIKE '%Any%') AND
> (main.Disabled = '0') GROUP BY main.id ORDER BY
> MIN(ObjectScrips_1.SortOrder) ASC
> rt3_admin-# ;
> ERROR: column "main.description" must appear in the GROUP BY clause or
> be used in an aggregate function
> LINE 1: SELECT main.* FROM Scrips main JOIN ObjectScrips ObjectScrip...

That statement works just fine for me on a clean RT 4.2.7 database with
Pg 9.3.5. I suspect something is odd with your Postgres database or
configuration -- this is likely relevant to your other thread as well.

> What's confusing is that I also upgraded another RT instance using the
> same procedure and I do not have this same issue (the SQL query above
> and ticket updates work fine).

Compare your Pg configurations.
- Alex
--
RT Training - November 4-5 Los Angeles
http://bestpractical.com/training

Note that the statement works fine for me on one RT 4.2.7 instance and
fails on the other. The difference in configuration is the original
database I was migrating from (a 3.6.6 RT instance). The DB upgrade
process for both was similar without any critical errors.

The only errors/warnings I saw during upgrade are listed below:

Processing 4.0.1
Now inserting database ACLs.
Now inserting data.
[1010] [Fri Sep 26 18:28:29 2014] [error]: Invalid right. Couldn't
canonicalize right 'AdminAllPersonalGroups'
(/data/tmp/rt-upgrade/rt-4.2.7/sbin/../lib/RT/ACE.pm:456)
[1010] [Fri Sep 26 18:28:29 2014] [error]: Invalid right. Couldn't
canonicalize right 'AdminOwnPersonalGroups'
(/data/tmp/rt-upgrade/rt-4.2.7/sbin/../lib/RT/ACE.pm:456)
[1010] [Fri Sep 26 18:28:29 2014] [error]: Invalid right. Couldn't
canonicalize right 'DelegateRights'
(/data/tmp/rt-upgrade/rt-4.2.7/sbin/../lib/RT/ACE.pm:456)
[1010] [Fri Sep 26 18:28:29 2014] [error]: Invalid right. Couldn't
canonicalize right 'AdminAllPersonalGroups'
(/data/tmp/rt-upgrade/rt-4.2.7/sbin/../lib/RT/ACE.pm:456)
[1010] [Fri Sep 26 18:28:29 2014] [error]: Invalid right. Couldn't
canonicalize right 'AdminOwnPersonalGroups'
(/data/tmp/rt-upgrade/rt-4.2.7/sbin/../lib/RT/ACE.pm:456)
[1010] [Fri Sep 26 18:28:30 2014] [error]: Invalid right. Couldn't
canonicalize right 'DelegateRights'
(/data/tmp/rt-upgrade/rt-4.2.7/sbin/../lib/RT/ACE.pm:456)
...
Processing 4.1.22
Now populating database schema.
Now inserting data.
[1010] [Fri Sep 26 18:29:42 2014] [info]: Going to delete all
SMIMEKeyNotAfter attributes (./etc/upgrade/4.1.22/content:61)
Processing 4.1.23
Now inserting database indexes.
[1010] [Fri Sep 26 18:29:48 2014] [warning]: Records in Users table had
non-unique values in Name column. Name has been changed for such
records, and now matches '%-dup-%' (etc/upgrade/4.1.23/indexes:81)

Note that my other thread (about message encoding) is dealing with the
RT 4.2.7 instance that does not suffer from this problem. At the moment
the production instance that suffers from this problem is still version
3.6.6 because this issue is a non-starter.

--
Brian McNally

On 09/26/2014 01:02 PM, Alex Vandiver wrote:
> On 09/25/2014 08:05 PM, Brian McNally wrote:
>> rt3_admin=# SELECT main.* FROM Scrips main JOIN ObjectScrips
>> ObjectScrips_1 ON ( ObjectScrips_1.Scrip = main.id ) JOIN
>> ScripConditions ScripConditions_2 ON ( ScripConditions_2.id =
>> main.ScripCondition ) WHERE (ObjectScrips_1.ObjectId = '3' OR
>> ObjectScrips_1.ObjectId = '0') AND (ObjectScrips_1.Stage =
>> 'TransactionCreate') AND (ScripConditions_2.ApplicableTransTypes LIKE
>> '%Create%' OR ScripConditions_2.ApplicableTransTypes LIKE '%Any%') AND
>> (main.Disabled = '0') GROUP BY main.id ORDER BY
>> MIN(ObjectScrips_1.SortOrder) ASC
>> rt3_admin-# ;
>> ERROR: column "main.description" must appear in the GROUP BY clause or
>> be used in an aggregate function
>> LINE 1: SELECT main.* FROM Scrips main JOIN ObjectScrips ObjectScrip...
>
> That statement works just fine for me on a clean RT 4.2.7 database with
> Pg 9.3.5. I suspect something is odd with your Postgres database or
> configuration -- this is likely relevant to your other thread as well.
>
>> What's confusing is that I also upgraded another RT instance using the
>> same procedure and I do not have this same issue (the SQL query above
>> and ticket updates work fine).
>
> Compare your Pg configurations.
> - Alex
>
--
RT Training - November 4-5 Los Angeles
http://bestpractical.com/training

On 09/26/2014 04:19 PM, Brian McNally wrote:
> Note that the statement works fine for me on one RT 4.2.7 instance and
> fails on the other. The difference in configuration is the original
> database I was migrating from (a 3.6.6 RT instance). The DB upgrade
> process for both was similar without any critical errors.

As the SQL statement runs successfully in one place, and not in the
other, I'm asking you to compare:

1. Database schemas for the two instances
2. Postgres configurations for the two instances

Note that this appears to be a failure of "GROUP BY id" to properly note
that id is a unique index, and as such is sufficient -- rather than
having to list all columns. The logic to support this was originally
introduced in PostgreSQL 9.1, and DBIx::SearchBuilder 1.66 now takes
advantage of it if possible. If that "GROUP BY" doesn't work for you,
it implies that:
1. You Scrips table somehow doesn't have "id" as a unique index
2. You're not actually running PostgreSQL 9.1 or higher (check client
vs server libraries?)
3. Your PostgreSQL has somehow disabled this optimization

That is what I mean by "check your postgres configuration."
- Alex
--
RT Training - November 4-5 Los Angeles
http://bestpractical.com/training

1. In terms of tables/sequences that are different: the problematic
instance has this, where the functional one does not.

rt3_admin=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------------------+----------+----------------
public | objectcustomfieldvalues_id_s | sequence | rt3_admin_user

2. Should all objects in the database be owned by the RT database user?
I ask because in my functional copy most things are and a few things are
owned by postgres whereas in my non-functional instance most things are
owned by postgres. The owner of both databases is the RT database user.

3. In terms of indexes, the non-functional instance's database has many
fewer indexes. For example

rt3_admin=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+---------------------+-------+----------+--------------------
public | articles_pkey | index | postgres | articles
public | cachedgroupmembers1 | index | postgres | cachedgroupmembers
public | cachedgroupmembers2 | index | postgres | cachedgroupmembers
public | cachedgroupmembers3 | index | postgres | cachedgroupmembers
public | classes_pkey | index | postgres | classes
public | contentindex_idx | index | postgres | attachments
public | groupmembers1 | index | postgres | groupmembers
public | groups1 | index | postgres | groups
public | groups2 | index | postgres | groups
public | groups3 | index | postgres | groups
public | objectclasses_pkey | index | postgres | objectclasses
public | objectscrips1 | index | postgres | objectscrips
public | objectscrips_pkey | index | postgres | objectscrips
public | objecttopics_pkey | index | postgres | objecttopics
public | queues1 | index | postgres | queues
public | topics_pkey | index | postgres | topics
public | users1 | index | postgres | users
(17 rows)

vs.

rt_gsits=# \di
List of relations
Schema | Name | Type | Owner |
Table
--------+------------------------------+-------+---------------+-------------------------
public | acl1 | index | rt_gsits_user | acl
public | acl_pkey | index | rt_gsits_user | acl
public | articles_pkey | index | postgres | articles
public | attachments1 | index | rt_gsits_user |
attachments
public | attachments2 | index | rt_gsits_user |
attachments
public | attachments3 | index | rt_gsits_user |
attachments
public | attachments_pkey | index | rt_gsits_user |
attachments
public | attributes1 | index | rt_gsits_user | attributes
public | attributes2 | index | rt_gsits_user | attributes
public | attributes_pkey | index | rt_gsits_user | attributes
public | cachedgroupmembers1 | index | rt_gsits_user |
cachedgroupmembers
public | cachedgroupmembers4 | index | rt_gsits_user |
cachedgroupmembers
public | cachedgroupmembers_pkey | index | rt_gsits_user |
cachedgroupmembers
public | classes_pkey | index | postgres | classes
public | contentindex_idx | index | rt_gsits_user |
attachments
public | customfields_pkey | index | rt_gsits_user |
customfields
public | customfieldvalues_pkey | index | rt_gsits_user |
customfieldvalues
public | disgroumem | index | rt_gsits_user |
cachedgroupmembers
public | groupmembers1 | index | rt_gsits_user |
groupmembers
public | groupmembers_pkey | index | rt_gsits_user |
groupmembers
public | groups1 | index | rt_gsits_user | groups
public | groups2 | index | rt_gsits_user | groups
public | groups3 | index | rt_gsits_user | groups
public | groups_pkey | index | rt_gsits_user | groups
public | links1 | index | rt_gsits_user | links
public | links_pkey | index | rt_gsits_user | links
public | objectclasses_pkey | index | postgres |
objectclasses
public | objectcustomfields_pkey | index | rt_gsits_user |
objectcustomfields
public | objectcustomfieldvalues1 | index | rt_gsits_user |
objectcustomfieldvalues
public | objectcustomfieldvalues2 | index | rt_gsits_user |
objectcustomfieldvalues
public | objectscrips1 | index | postgres |
objectscrips
public | objectscrips_pkey | index | postgres |
objectscrips
public | objecttopics_pkey | index | postgres |
objecttopics
public | principals2 | index | rt_gsits_user | principals
public | principals_pkey | index | rt_gsits_user | principals
public | queues1 | index | rt_gsits_user | queues
public | queues_pkey | index | rt_gsits_user | queues
public | scripactions_pkey | index | rt_gsits_user |
scripactions
public | scripconditions_pkey | index | rt_gsits_user |
scripconditions
public | scrips_pkey | index | rt_gsits_user | scrips
public | sessions_pkey | index | rt_gsits_user | sessions
public | templates_pkey | index | rt_gsits_user | templates
public | ticketcustomfieldvalues_pkey | index | rt_gsits_user |
objectcustomfieldvalues
public | tickets1 | index | rt_gsits_user | tickets
public | tickets2 | index | rt_gsits_user | tickets
public | tickets3 | index | rt_gsits_user | tickets
public | tickets6 | index | rt_gsits_user | tickets
public | tickets7 | index | rt_gsits_user | tickets
public | tickets8 | index | rt_gsits_user | tickets
public | tickets_pkey | index | rt_gsits_user | tickets
public | topics_pkey | index | postgres | topics
public | transactions1 | index | rt_gsits_user |
transactions
public | transactions2 | index | rt_gsits_user |
transactions
public | transactions_pkey | index | rt_gsits_user |
transactions
public | users1 | index | rt_gsits_user | users
public | users4 | index | rt_gsits_user | users
public | users_pkey | index | rt_gsits_user | users
(57 rows)

Both databases are running in the same postgres instance, so that should
clarify that there isn't a difference in the actual service's
configuration that would be causing problems.

--
Brian McNally
System Administrator, Genome Sciences
(206) 543-7363

On 09/26/2014 01:37 PM, Alex Vandiver wrote:
> On 09/26/2014 04:19 PM, Brian McNally wrote:
>> Note that the statement works fine for me on one RT 4.2.7 instance and
>> fails on the other. The difference in configuration is the original
>> database I was migrating from (a 3.6.6 RT instance). The DB upgrade
>> process for both was similar without any critical errors.
>
> As the SQL statement runs successfully in one place, and not in the
> other, I'm asking you to compare:
>
> 1. Database schemas for the two instances
> 2. Postgres configurations for the two instances
>
> Note that this appears to be a failure of "GROUP BY id" to properly note
> that id is a unique index, and as such is sufficient -- rather than
> having to list all columns. The logic to support this was originally
> introduced in PostgreSQL 9.1, and DBIx::SearchBuilder 1.66 now takes
> advantage of it if possible. If that "GROUP BY" doesn't work for you,
> it implies that:
> 1. You Scrips table somehow doesn't have "id" as a unique index
> 2. You're not actually running PostgreSQL 9.1 or higher (check client
> vs server libraries?)
> 3. Your PostgreSQL has somehow disabled this optimization
>
> That is what I mean by "check your postgres configuration."
> - Alex
>
--
RT Training - November 4-5 Los Angeles
http://bestpractical.com/training

On 09/26/2014 04:50 PM, Brian McNally wrote:
> 1. In terms of tables/sequences that are different: the problematic
> instance has this, where the functional one does not.
>
> rt3_admin=# \d
> List of relations
> Schema | Name | Type | Owner
> --------+------------------------------+----------+----------------
> public | objectcustomfieldvalues_id_s | sequence | rt3_admin_user

Worrisome, yes, but not the root of your problems; see below.

> 2. Should all objects in the database be owned by the RT database user?
> I ask because in my functional copy most things are and a few things are
> owned by postgres whereas in my non-functional instance most things are
> owned by postgres. The owner of both databases is the RT database user.

The ownership isn't significant -- RT runs "GRANT ... ON ... TO rt_user"
after adding/removing tables.

> 3. In terms of indexes, the non-functional instance's database has many
> fewer indexes. For example
>
> rt3_admin=# \di
> List of relations
> Schema | Name | Type | Owner | Table
> --------+---------------------+-------+----------+--------------------
> public | articles_pkey | index | postgres | articles
> public | cachedgroupmembers1 | index | postgres | cachedgroupmembers
> public | cachedgroupmembers2 | index | postgres | cachedgroupmembers
> public | cachedgroupmembers3 | index | postgres | cachedgroupmembers
> public | classes_pkey | index | postgres | classes
> public | contentindex_idx | index | postgres | attachments
> public | groupmembers1 | index | postgres | groupmembers
> public | groups1 | index | postgres | groups
> public | groups2 | index | postgres | groups
> public | groups3 | index | postgres | groups
> public | objectclasses_pkey | index | postgres | objectclasses
> public | objectscrips1 | index | postgres | objectscrips
> public | objectscrips_pkey | index | postgres | objectscrips
> public | objecttopics_pkey | index | postgres | objecttopics
> public | queues1 | index | postgres | queues
> public | topics_pkey | index | postgres | topics
> public | users1 | index | postgres | users
> (17 rows)

...there isn't even a primary key index on tickets there. I have no
idea how your database got into this state (I've never seen such
before), but that would indeed be your problem.

- Alex
--
RT Training - November 4-5 Los Angeles
http://bestpractical.com/training

Well that's a bummer, but it does explain why this instance has been so
slow for 6+ years. Do you think it's viable to try and make the indexes
for this database at this point, or is that a waste of time to
investigate? The other option is that I just start fresh and use the old
system (or a new read-only system) as a reference.

Thanks for your help.

--
Brian McNally

> ...there isn't even a primary key index on tickets there. I have no
> idea how your database got into this state (I've never seen such
> before), but that would indeed be your problem.
--
RT Training - November 4-5 Los Angeles
http://bestpractical.com/training

Update: I think I've been able to fix this issue by re-creating my
indexes from a preexisting database that has the right indexes.

pg_restore -d db_without_indexes --section=post-data
pgdump_other_db_with_indexes.pg

Thanks for your help Alex.

--
Brian McNally
--
RT Training - November 4-5 Los Angeles
http://bestpractical.com/training