On Wed, 2008-12-03 at 10:44 -0500, John A. Sullivan III wrote:
> On Tue, 2008-12-02 at 13:00 -0500, John A. Sullivan III wrote:
> > On Wed, 2008-11-19 at 11:31 -0500, John A. Sullivan III wrote:
> > > On Fri, 2008-11-14 at 08:04 -0500, John A. Sullivan III wrote:
> > > > Hello, all. I've hit a problem right away while trying to use OpenCA
> > > > 1.0.2 running on CentOS 5.2 with PostgreSQL 8.1.11. I installed
> > > > separate online and offline systems, i.e., CA/node and then RA/node/Pub.
> > > > When retrieving the list of new CSRs under RA Operations, I receive the
> > > > following error:
> > > >
> > > > Database failed with errorcode 20032. SELECT_FAILED (error 20016:
> > > > EXECUTE_FAILED (error 11111: Do not commit if the database or the module
> > > > itself fails.))
> > > >
> > > > The entry in var/openca/log/stderr.log makes it look like a bug. Here
> > > > is the error:
> > > >
> > > > DBD::Pg::st execute failed: ERROR: column "request.req_key" must appear
> > > > in the GROUP BY clause or be used in an aggregate function
> > > > DBD::Pg::st execute failed: ERROR: current transaction is aborted,
> > > > commands ignored until end of transaction block
> > > >
> > > > The installation, initialization, and configuration import went fine. I
> > > > then used the pub interface to create a browser request using server
> > > > side key generation. After submitting the request, I went to the RA,
> > > > successfully logged in using LDAP password authentication, chose RA
> > > > Operations, Certificate Requests, New. I left all fields set to All and
> > > > clicked Search. I then receive the above errors. I also tried
> > > > searching with all fields explicitly set and received the same results.
> > > >
> > > > If I manually connect to the database as the openca user and query the
> > > > request table, I can see the request in the database.
> > > >
> > > > I should mention this also happens whenever I select from the RA
> > > > interface Information / CRLS / and either Valid or Expired. Here is the
> > > > entry from the postgresql log:
> > > >
> > > > ERROR: column "crl.last_update" must appear in the GROUP BY clause or
> > > > be used in an aggregate function
> > > > ERROR: current transaction is aborted, commands ignored until end of
> > > > transaction block
> > > >
> > > > Hmm . . . furthermore, if I search for the CSR (Information / Search /
> > > > Requests), I am able to retrieve the request.
> > > >
> > > > Argh!!! I get the same thing on the CA with the following actions:
> > > >
> > > > CA Operations / Certificate Requests / all options
> > > > CA Operations / Renewal Requests / New
> > > > Information / Certificate Requests / all options
> > > > Information / CRLS / all options
> > > >
> > > > Am I doing something wrong? Is there a fix? This has us stopped in our
> > > > tracks for now. Thanks - John
> > >
> > > Sorry to bump this but it has us stymied in our attempt to move 1.0.2
> > > into production. Alas, I am really not a developer and especially do
> > > not know perl to troubleshoot this myself.
> > >
> > > It smells like a problem we hit on the ISCS open source network security
> > > management project (http://iscs.sourceforge.net) where we had developed
> > > on mysql, ported to postgresql and found almost this exact error.
> > > Postgresql was much more exact about the groupby syntax. I don't recall
> > > exactly what it was - if it needed the primary key or if it needed the
> > > groupby columns to also appear in the select list. I think it was the
> > > latter.
> > >
> > > Please let me know if there is anything I can do to assist in finding
> > > and resolving the problem. Thanks - John
> > Sorry to be a pain - one last bump. Otherwise, I'll try to dig into the
> > code myself even though I don't know perl and could not find the query
> > on my first code scan. If I can't find it and fix it, we'll have to do
> > something else for our PKI. Any pointers on where to look for the
> > query? Thanks - John
>
> <snip>
> This thread explains the likely cause of the bug:
> http://archives.postgresql.org/pgsql-general/2004-02/msg01191.php
> What I need is help finding where the query is fashioned so it can be
> corrected. Any pointers? Thanks - John
<snip>
I tracked down the call to OpenCA::DBI::searchItems. When I turned on
debugging in OpenCA::DBI, this showed up in stderr.log:
DEBUG: OpenCA::DBI->searchItems: query: select count(*) from request
where (status like ? ) order by req_key
DEBUG: OpenCA::DBI->Entering set_error ...
DEBUG: OpenCA::DBI->errno: gettext is defined
DEBUG: OpenCA::DBI->errno: new errorcode is 0
DEBUG: OpenCA::DBI->doQuery: entering function
DEBUG: OpenCA::DBI->doQuery: query: select count(*) from request where
(status like ? ) order by req_key
DEBUG: OpenCA::DBI->doQuery: bind_values: NEW
DEBUG: OpenCA::DBI->doQuery: bind_values: NEW
DEBUG: OpenCA::DBI->doQuery: prepare statement
DEBUG: OpenCA::DBI->doQuery: execute statement
DBD::Pg::st execute failed: ERROR: column "request.req_key" must appear
in the GROUP BY clause or be used in an aggregate function
DEBUG: OpenCA::DBI->doQuery: execute failed (leaving function)
Sure enough, executing the command directly in psql fails:
niagarara=> select count(*) from request where (status like 'NEW' )
order by req_key;
ERROR: column "request.req_key" must appear in the GROUP BY clause or
be used in an aggregate function
I first thought it did not like MODE="COUNT(*)" with the order by even
though it is using an aggregate function of COUNT() so I tried changing
COUNT to use the primary key but it did not like that either:
niagarara=> select count(req_key) from request where (status like
'NEW' ) order by req_key;
ERROR: column "request.req_key" must appear in the GROUP BY clause or
be used in an aggregate function
However, if MODE="COUNT(*)", why do we even need an order by? That seems
to be the problem because simply removing it works:
niagarara=> select count(*) from request where (status like 'NEW' );
count
-------
4
(1 row)
The order by additions occur at lines 2384 and 2553.
I made the following change at line 2384:
if ( $mode ne "count(*)" ) {
## order by key to support correct listings
$query.= " order by ".
$OpenCA::DBI::SQL->{VARIABLE}->{$arguments {TABLE}."_ORDERBY"}[0];
}
Line 2553 does not seem to use the COUNT() function.
This seems to have fixed all the problems cited above. However, I don't
have a clue about what I am doing so this may have broken something
else. Can anyone confirm that this is a correct solution or propose a
better one? Thanks - John
--
John A. Sullivan III
Open Source Development Corporation
+1 207-985-7880
[EMAIL PROTECTED]
http://www.spiritualoutreach.com
Making Christianity intelligible to secular society
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
OpenCA-Devel mailing list
OpenCA-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openca-devel
