SQL error from Pg on 4.0.21

RT Users
1

Hi, everybody,

I’ve got a 4.0.21 system (recently upgraded from 4.0.8) Database is
Postgresql 8.4.20. I’m seeing intermittent ticket creation errors after
the update, and when I go to the log, I see SQL errors which are
reproducible at the console (assuming causation without proof):

rt4=> SELECT main.* FROM CustomFields main JOIN ObjectCustomFields
ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField = main.id )
WHERE (ObjectCustomFields_1.ObjectId = ‘5’ OR
ObjectCustomFields_1.ObjectId = ‘0’) AND (main.Disabled = ‘0’) AND
(main.LookupType = ‘RT::Queue-RT::Ticket-RT::Transaction’) GROUP BY
main.id ORDER BY MIN(ObjectCustomFields_1.SortOrder) ASC
rt4-> ;
ERROR: column “main.name” must appear in the GROUP BY clause or be used
in an aggregate function
LINE 1: SELECT main.* FROM CustomFields main JOIN ObjectCustomFields…
^
Could somebody give me a pointer to where this might be generated? Or
if anybody knows of a fix that’s be great too.

These variants are valid syntax, but I’m not up on the code enough to
know what it’s looking for or exactly where to tweak it:

rt4=> SELECT main.id FROM CustomFields main JOIN ObjectCustomFields
ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField = main.id )
WHERE (ObjectCustomFields_1.ObjectId = ‘5’ OR
ObjectCustomFields_1.ObjectId = ‘0’) AND (main.Disabled = ‘0’) AND
(main.LookupType = ‘RT::Queue-RT::Ticket-RT::Transaction’) GROUP BY
main.id ORDER BY MIN(ObjectCustomFields_1.SortOrder) ASC;
id
(0 rows)

rt4=> SELECT main.* FROM CustomFields main JOIN ObjectCustomFields
ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField = main.id )
WHERE (ObjectCustomFields_1.ObjectId = ‘5’ OR
ObjectCustomFields_1.ObjectId = ‘0’) AND (main.Disabled = ‘0’) AND
(main.LookupType = ‘RT::Queue-RT::Ticket-RT::Transaction’) GROUP BY
main.id,main.name,main.type,main.description,main.sortorder,main.creator,main.created,main.lastupdatedby,main.lastupdated,main.disabled,main.lookuptype,main.repeated,main.pattern,main.maxvalues,main.basedon,main.rendertype,main.valuesclass
ORDER BY MIN(ObjectCustomFields_1.SortOrder) ASC
;
id | name | type | description | sortorder | creator | created |
lastupdatedby | lastupdated | disabled | lookuptype | repeated |
pattern | maxvalues | basedon | rendertype | valuesclass
(0 rows)

Thanks,
-Bill

Bill McGonigle, Owner
BFC Computing, LLC
http://bfccomputing.com/
Telephone: +1.855.SW.LIBRE
Email, IM, VOIP: bill@bfccomputing.com
VCard: http://bfccomputing.com/vcard/bill.vcf
Social networks: bill_mcgonigle/bill.mcgonigle

2

I’ve got a 4.0.21 system (recently upgraded from 4.0.8) Database is
Postgresql 8.4.20. I’m seeing intermittent ticket creation errors after
the update, and when I go to the log, I see SQL errors which are
reproducible at the console (assuming causation without proof):

What version of DBIx::SearchBuilder? You can check via:

perl -MDBIx::SearchBuilder\ 1000

  • Alex
3

What version of DBIx::SearchBuilder?

$ perl -MDBIx::SearchBuilder\ 1000
DBIx::SearchBuilder version 1000 required–this is only version 1.65.
BEGIN failed–compilation aborted.

I did see this backtrace, but I’m not sure at which point the query is built (looks like DBIx::SearchBuilder::_DoSearch gets a perl object and builds the SQL?)

    DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0x7f238c35c538)', 'SELECT main.* FROM CustomFields main JOIN ObjectCustomFields ...') called at /usr/share/perl5/vendor_perl/DBIx/SearchBuilder.pm line 239
    DBIx::SearchBuilder::_DoSearch('RT::CustomFields=HASH(0x7f238cba87d8)') called at /usr/share/perl5/vendor_perl/RT/SearchBuilder.pm line 347
    RT::SearchBuilder::_DoSearch('RT::CustomFields=HASH(0x7f238cba87d8)') called at /usr/share/perl5/vendor_perl/DBIx/SearchBuilder.pm line 507
    DBIx::SearchBuilder::Next('RT::CustomFields=HASH(0x7f238cba87d8)') called at /usr/share/perl5/vendor_perl/RT/CustomFields.pm line 383
    RT::CustomFields::Next('RT::CustomFields=HASH(0x7f238cba87d8)') called at /usr/share/rt/html/Elements/ValidateCustomFields line 51
    HTML::Mason::Commands::__ANON__('CustomFields', 'RT::CustomFields=HASH(0x7f238cba87d8)', 'ARGSRef', 'HASH(0x7f238cb89bd8)')

-Bill

Bill McGonigle, Owner
BFC Computing, LLC
http://bfccomputing.com/
Telephone: +1.855.SW.LIBRE
Email, IM, VOIP: bill@bfccomputing.com
VCard: http://bfccomputing.com/vcard/bill.vcf
Social networks: bill_mcgonigle/bill.mcgonigle

4

I did see this backtrace, but I’m not sure at which point the query is built (looks like DBIx::SearchBuilder::_DoSearch gets a perl object and builds the SQL?)

Maybe I see what’s going on - I’ve got some mysql code paths being called. I wonder if I messed up the config.

I have:

Set($DatabaseType , ‘Pg’);

And it is mostly working, so I gotta figure it’s finding the right database (using the right DBD, etc.). So … odd.

-Bill

[26226] [Tue Jul 22 16:52:53 2014] [warning]: DBD::Pg::st execute failed: ERROR: function last_insert_id() does not exist
LINE 1: SELECT LAST_INSERT_ID()
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts. at /usr/share/perl5/vendor_perl/DBIx/SearchBuilder/Handle.pm line 589. (/usr/share/perl5/vendor_perl/DBIx/SearchBuilder/Handle.pm:589)
[26226] [Tue Jul 22 16:52:53 2014] [warning]: RT::Handle=HASH(0x7f238c35c538) couldn’t execute the query ‘SELECT LAST_INSERT_ID()’ at /usr/share/perl5/vendor_perl/DBIx/SearchBuilder/Handle.pm line 602.
DBIx::SearchBuilder::Handle::SimpleQuery(‘RT::Handle=HASH(0x7f238c35c538)’, ‘SELECT LAST_INSERT_ID()’) called at /usr/share/perl5/vendor_perl/DBIx/SearchBuilder/Handle.pm line 634
DBIx::SearchBuilder::Handle::FetchResult(‘RT::Handle=HASH(0x7f238c35c538)’, ‘SELECT LAST_INSERT_ID()’) called at /usr/share/perl5/vendor_perl/DBIx/SearchBuilder/Handle/mysql.pm line 44
DBIx::SearchBuilder::Handle::mysql::Insert(‘RT::Handle=HASH(0x7f238c35c538)’, ‘Tickets’, ‘Subject’, ‘openstack for Boston’, ‘Status’, ‘new’, ‘Queue’, 5, ‘Creator’, …) called at /usr/share/perl5/vendor_perl/DBIx/SearchBuilder/Record.pm line 1320
DBIx::SearchBuilder::Record::Create(‘RT::Ticket=HASH(0x7f238cb99360)’, ‘Subject’, ‘openstack for Boston’, ‘Status’, ‘new’, ‘Queue’, 5, ‘Creator’, 22, …) called at /usr/share/perl5/vendor_perl/RT/Record.pm line 316

Bill McGonigle, Owner
BFC Computing, LLC
http://bfccomputing.com/
Telephone: +1.855.SW.LIBRE
Email, IM, VOIP: bill@bfccomputing.com
VCard: http://bfccomputing.com/vcard/bill.vcf
Social networks: bill_mcgonigle/bill.mcgonigle

5

Maybe I see what’s going on - I’ve got some mysql code paths being
called. I wonder if I messed up the config.

I have:

Set($DatabaseType , ‘Pg’);

And it is mostly working, so I gotta figure it’s finding the right
database (using the right DBD, etc.). So … odd.

I see from 1121601 – Review Request: rt - request tracker that
you were upgrading between a version of 4.0.8 that you packaged yourself
to a 4.0.21 package that is currently in testing. You may do well to
install a clean 4.0.21 from scratch and compare the two directory trees
and configurations.

  • Alex
6

I see from https://bugzilla.redhat.com/show_bug.cgi?id=1121601#c6 that
you were upgrading between a version of 4.0.8 that you packaged yourself
to a 4.0.21 package that is currently in testing. You may do well to
install a clean 4.0.21 from scratch and compare the two directory trees
and configurations.

They’re in completely separate installation directories (/usr/share/rt4
for 4.0.8 and /usr/share/rt for 4.0.21), but it’s a good point - I
haven’t verified that Ralf’s latest SPEC doesn’t break Pg support somehow.

I rolled back to 4.0.8 for now and Pg is working as expected.

-Bill

Bill McGonigle, Owner
BFC Computing, LLC
http://bfccomputing.com/
Telephone: +1.855.SW.LIBRE
Email, IM, VOIP: bill@bfccomputing.com
VCard: http://bfccomputing.com/vcard/bill.vcf
Social networks: bill_mcgonigle/bill.mcgonigle