RT 4.2.7: SQL error on ticket traffic

Brian_McNally · September 26, 2014, 12:05am

Hi,

I’m running RT 4.2.7 (having recently upgraded from 3.6.6) and am
running in to issues on ticket creation, correspondence, or comments
(that is, everything). I’ve attached log entries that show the errors.
My issue sounds very similar to this problem:

https://bugzilla.redhat.com/show_bug.cgi?id=1121601#c6
https://bugzilla.redhat.com/show_bug.cgi?id=1121601#c7

After lots of frustration I tried upgrading from 3.6.6 to 4.0.8 based on
the above user’s experience and I discovered that I do not have the same
SQL errors on ticket traffic. I’m using Postgres 9.3.5,
DBIx::SearchBuilder 1.66, DBD::Pg 3.4.1, RHEL 6.5.

I’ve narrowed down the failure to a particular SQL query:

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…

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

Brian McNally

rt-admin.txt (26.7 KB)

Brian_McNally · September 26, 2014, 12:13am

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

Alex_Vandiver · September 26, 2014, 8:02pm

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

Brian_McNally · September 26, 2014, 8:19pm

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 McNallyOn 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

Alex_Vandiver · September 26, 2014, 8:37pm

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:

Database schemas for the two instances
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:

You Scrips table somehow doesn’t have “id” as a unique index
You’re not actually running PostgreSQL 9.1 or higher (check client
vs server libraries?)
Your PostgreSQL has somehow disabled this optimization

That is what I mean by “check your postgres configuration.”

Alex

Brian_McNally · September 26, 2014, 8:50pm

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

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.
In terms of indexes, the non-functional instance’s database has many
fewer indexes. For example

vs.

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-7363On 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:

Database schemas for the two instances

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:

You Scrips table somehow doesn’t have “id” as a unique index

You’re not actually running PostgreSQL 9.1 or higher (check client
vs server libraries?)

Your PostgreSQL has somehow disabled this optimization

That is what I mean by “check your postgres configuration.”

Alex

Alex_Vandiver · September 26, 2014, 9:04pm

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.

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.

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

Brian_McNally · September 26, 2014, 10:18pm

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

Brian_McNally · September 29, 2014, 7:34pm

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