I’m seeing lots of log entries of the form:
RT: DBD::Pg::st execute failed: ERROR: invalid input syntax for integer:
"" at /usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 410.
(/usr/local/rt/lib/RT.pm:250)
RT 3.2.1, PostgreSQL 7.4
Keith
I’m seeing lots of log entries of the form:
RT: DBD::Pg::st execute failed: ERROR: invalid input syntax for
integer:
“” at /usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line
410.
(/usr/local/rt/lib/RT.pm:250)
What SQL query is RT generating that does this?
What SQL query is RT generating that does this?
RT::Handle=HASH(0x93b3504) couldn’t execute the query ‘SELECT * FROM
Principals WHERE LOWER(PrincipalType) = ? AND (ObjectId IS NULL OR ObjectId
= ‘’)’ at /usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line
417. (/usr/local/rt/lib/RT.pm:250)
RTFM is also installed (just in case it helps to know that).
I’m seeing lots of log entries of the form:
RT: DBD::Pg::st execute failed: ERROR: invalid input syntax for
integer: “” at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 410.
(/usr/local/rt/lib/RT.pm:250)RT 3.2.1, PostgreSQL 7.4
Seeing them too on RT 3.0.11, psql 7.4
What SQL query is RT generating that does this?
I think it happens when the Basics of a ticket are modified, and the
Time Worked / Time Left fields are not initialised to 0.
-- Niels.
What SQL query is RT generating that does this?
I’m also seeing errors - possibly unrelated - when trying to manually
create a ticket with the “New ticket in” button:
System error
error: DBD::Pg::st execute failed: no connection to the server at
/usr/lib/perl5/site_perl/5.8.3/Apache/Session/Store/DBI.pm line 67.
context: …
63:
64: $self->{update_sth}->bind_param(1, $session->{serialized});
65: $self->{update_sth}->bind_param(2,
$session->{data}->{_session_id}); 66:
67: $self->{update_sth}->execute;
68:
69: $self->{update_sth}->finish;
70: }
71:
…
code stack:
/usr/lib/perl5/site_perl/5.8.3/Apache/Session/Store/DBI.pm:67
/usr/lib/perl5/site_perl/5.8.3/Apache/Session.pm:523
/usr/lib/perl5/site_perl/5.8.3/Apache/Session.pm:477
/usr/lib/perl5/site_perl/5.8.3/HTML/Mason/Request.pm:1078
What SQL query is RT generating that does this?
RT::Handle=HASH(0x93b3504) couldn’t execute the query ‘SELECT * FROM
Principals WHERE LOWER(PrincipalType) = ? AND (ObjectId IS NULL OR
ObjectId
= ‘’)’ at /usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm
line
417. (/usr/local/rt/lib/RT.pm:250)
And what are you doing to cause that? It looks familiar. And a bit
like something I was pretty sure we’d fixed. Perhaps it’s time for
3.2.2.
-j
And what are you doing to cause that? It looks familiar. And a bit
like something I was pretty sure we’d fixed. Perhaps it’s time for
3.2.2.
Unfortunately I can’t say. This is on a new test system, and a few test
users are trying RT out. I saw this when I examined the logs (in response
to one test user complaining that RT had mailed him multiple times about
one call for no apparent reason). I can keep an eye on the log while
continuing testing and supply more information as I find it.
I’m also seeing errors - possibly unrelated - when trying to manually
create a ticket with the “New ticket in” button:
If it helps, I have the entries from the logfile available, which include
“DBD::Pg::st execute failed: FATAL: invalid frontend message type 34 at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm”
Full log details on request.
RT 3.2.1 / Perl 5.8.3-5 / DBIx::SearchBuilder 1.10 / Postgres 7.4.1
I’ve noticed SQL errors appearing in syslog during most updating
operations; although everything appears to work.
I also get the invalid input syntax for integer error.
Sep 8 12:21:21 RT: DBD::Pg::st execute failed: ERROR: invalid input
syntax for integer: “” at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 478.
(/opt/rt3/lib/RT.pm:250)
Sep 8 12:21:21 RT: RT::Handle=HASH(0x99031f4) couldn’t execute the
query 'UPDATE Queues SET InitialPriority=? WHERE id=? ’ at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 492.
(/opt/rt3/lib/RT.pm:250)
Sep 8 12:21:21 RT: DBD::Pg::st execute failed: ERROR: invalid input
syntax for integer: “” at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 478.
(/opt/rt3/lib/RT.pm:250)
Sep 8 12:21:21 RT: RT::Handle=HASH(0x99031f4) couldn’t execute the
query 'UPDATE Queues SET FinalPriority=? WHERE id=? ’ at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 492.
(/opt/rt3/lib/RT.pm:250)
The queue in question has only the name, description and “requests
should be due in” fields set. After creation the "Priority starts at"
and “Priority moves forward” fields are set to zero, presumably the
default.
I’ve looked through the mailing list archives and STW but can’t seem to
find any resolution for this issue nor am I familiar enough with the RT
and related code, or Perl, to look into this.
Matt
RT 3.2.2 / DBIx::SearchBuilder 1.15 (latest from CPAN) / Postgres 7.4.1
Example message:
Sep 8 12:21:21 RT: DBD::Pg::st execute failed: ERROR: invalid input
syntax for integer: “”
Has the invalid value (“”) for integer fields on postgres issue been
resolved in 3.2 series (or in a CVS version)?
We still encounter these messages occasionally followed by a failed ACL
query; seemingly less often with the newer SearchBuilder. The messages
do not seem to affect functionality but are concerning nonetheless.
The last time I saw such an error in RT itself was when creating a
queue, it occurred if the priority or other integer fields of the Queue
were left as the defaults.
I’ve just installed the rt3-statistics package from contrib and have
encountered more such messages, this time they seem to stop the stats
pages from being displayed.
Log below.
Like the previous poster I can find on this topic I’ve looked through
the mailing list archives and STW but can’t seem to find any resolution
for this issue nor am I familiar enough with the RT and related code yet
to look into this.
Regards,
Matt Jennings
Log:
Dec 6 13:57:01 myserver RT: Trying to check RT::Queue rights for an
unspecified RT::Queue at /opt/rt3/lib/RT/Principal_Overlay.pm line 376
^IRT::Principal::HasRight(‘RT::Principal=HASH
(0x9e9daa8)’,‘Object’,‘RT::Queu
e=HASH(0x9ed8760)’,‘Right’,‘SeeQueue’) called at
/opt/rt3/lib/RT/User_Overlay.pm line 1476
^IRT::User::HasRight(‘RT::User=HASH(0x9f09de0)’,‘Object’,‘RT::Queue=HASH
(0x9
ed8760)’,‘Right’,‘SeeQueue’) called at /opt/rt3/lib/RT/CurrentUser.pm
line
338
^IRT::CurrentUser::HasRight(‘RT::CurrentUser=HASH
(0x9ec5344)’,‘Object’,‘RT::
Queue=HASH(0x9ed8760)’,‘Right’,‘SeeQueue’) called at
/opt/rt3/lib/RT/Queue_Overlay.pm line 1066
^IRT::Queue::HasRight(‘RT::Queue=HASH
(0x9ed8760)’,‘Principal’,‘RT::CurrentUs
er=HASH(0x9ec5344)’,‘Right’,‘SeeQueue’) called at
/opt/rt3/lib/RT/Queue_Overlay.pm line 1032
^IRT::Queue::CurrentUserHasRight(‘RT::Queue=HASH(0x9ed8760)’,‘SeeQueue’)
called at /opt/rt3/local/html/Statistics/CallsMultiQueue/index.html line
21
^IHTML::Mason::Commands::ANON() called at
/usr/lib/perl5/site_perl/5.8.3/HTML/Mason/Compon
Dec 6 13:57:01 myserver RT: Trying to check RT::Queue rights for an
unspecified RT::Queue (/opt/rt3/lib/RT/Principal_Overlay.pm:377)
Dec 6 13:57:01 myserver RT: DBD::Pg::st execute failed: ERROR: invalid
input
syntax for integer: “”
at /usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 475.
(/opt/rt3/lib/RT.pm:269)
Dec 6 13:57:01 myserver RT: RT::Handle=HASH(0x9909944) couldn’t execute
the
query ‘SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers
WHERE
(ACL.RightName = ‘SuperUser’ OR ACL.RightName = ‘SeeQueue’) AND
Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
Principals.id = Groups.id AND Principals.id =
CachedGroupMembers.GroupId
AND CachedGroupMembers.MemberId = ‘24’ AND ( ACL.ObjectType =
‘RT::System’
OR (ACL.ObjectType = ‘RT::Queue’ AND ACL.ObjectId = ‘’)) AND ( (
ACL.PrincipalId = Principals.id AND ACL.PrincipalType = ‘Group’ AND
(Groups.Domain = ‘SystemInternal’ OR Groups.Domain = ‘UserDefined’ OR
Groups.Domain = ‘ACLEquivalence’ OR Groups.Domain = ‘Personal’)) )
LIMIT 1’
at /usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 489.
(/opt/rt3/lib/RT.pm:269)
Dec 6 13:57:01 myserver RT: DBD::Pg::st execute failed: ERROR: invalid
input
syntax for integer: “” at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 475.
(/opt/rt3/lib/RT.pm:269)
Dec 6 13:57:01 myserver RT: RT::Handle=HASH(0x9909944) couldn’t execute
the
query ‘SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers
WHERE
(ACL.RightName = ‘SuperUser’ OR ACL.RightName = ‘SeeQueue’) AND
Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
Principals.id = Groups.id AND Principals.id =
CachedGroupMembers.GroupId
AND CachedGroupMembers.MemberId = ‘24’ AND ( ACL.ObjectType =
‘RT::System’
OR (ACL.ObjectType = ‘RT::Queue’ AND ACL.ObjectId = ‘’)) AND (
((Groups.Domain = ‘RT::Queue-Role’ AND Groups.Instance = 0) ) AND
Groups.Type = ACL.PrincipalType AND Groups.Id = Principals.id AND
Principals.PrincipalType = ‘Group’) LIMIT 1’ at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 489.
(/opt/rt3/lib/RT.pm:269)
Has the invalid value (“”) for integer fields on postgres issue been
resolved in 3.2 series (or in a CVS version)?
I know the behaviour has been improved. I expect to get 3.2.3RC1 out
“soon” and I’d love to hear if that resolves the issue for it.
The last time I saw such an error in RT itself was when creating a
queue, it occurred if the priority or other integer fields of the Queue
were left as the defaults.
That’s the sort of thing we like to see in bug reports. Thanks!
I’ve just installed the rt3-statistics package from contrib and have
encountered more such messages, this time they seem to stop the stats
pages from being displayed.
The stats package doesn’t go through the RT API for everything if I
recall correctly. That means that it doesn’t take advantage of any
bulletproofing we do against such probelms. MySQL (And possibly earlier
versions of Pg) would automatically convert an integer field of “” to
“0”. Current versions of postgres are very strict about the SQL standard
and throw errors to get you to fix your mistakes, rather than
interpreting what you mean and just running with it.
The failed acl check does fail to deny, so this isn’t a security issue
per se, but one that I would like to fix if 3.2.3 doesn’t already.
Jesse
Has the invalid value (“”) for integer fields on postgres issue been
resolved in 3.2 series (or in a CVS version)?
Jesse, thanks for your response.
I know the behaviour has been improved. I expect to get 3.2.3RC1 out
“soon” and I’d love to hear if that resolves the issue for it.
I’m a software developer (not much perl experience though…). I should
be able to thoroughly bug-test future releases in both test and
production environments.
MySQL (And possibly earlier versions of Pg) would automatically convert
an integer field of “” to “0”. Current versions of postgres are very
strict about the SQL standard and throw errors to get you to fix your
mistakes, rather than interpreting what you mean and just running with
it.
Yes I found this as the cause pretty quickly from some googling. I
support postgres for doing this despite breaking existing code… none of
mine luckily 
The failed acl check does fail to deny, so this isn’t a security issue
per se, but one that I would like to fix if 3.2.3 doesn’t already.
You mean doesn’t fail?
From observation it doesn’t seem to interfere with our use of the
system, however I was worried that once we start restricting access per
queue, class of user etc. it may begin to cause access problems.
Matt