[rt-users] Prepared statement already exists...

[Ruslan Zakirov]

Hi,

>From what you have posted I suspect that problem is in DBI/DBD::Pg
modules. When you check version of the modules, double check with RT's
UI to make sure what you get in command line interface matches what RT
loads. Try downgrading DBD::Pg.

As workaround you can try to disable server side preparing of statements:

http://search.cpan.org/~turnstep/DBD-Pg-2.18.1/Pg.pm#pg_server_prepare_(integer)

On Wed, Feb 1, 2012 at 17:50, Matthias Leisi <matthias at leisi.net> wrote:
> Hello,
>
> I got some support debugging my "prepared statement already exists"
> issue with RT 4.0.4 on #rt, but I believe it is more efficient on the
> mailing list. I'm not fully sure whether this is an issue in RT, in
> Postgresql or DBI, or something completely different, but I'll try to
> start at RT.
>
> We recently upgraded to RT 4.0.4 (using Postgresql 8.4 as in the
> previous RT 3.8.7, see upgrade steps below). Since then, we have the
> "prepared statement already exists" error; this results in RT being
> inaccessible. We currently see no other option than to restart the DB
> server (or issue "select * from pg_terminate_backend(<serverpid>);" as
> super-user within Postgres).
>
> The error condition pops up about once a day, after a couple of hours
> of usage. The usage is minimal (usually, only a handful of
> transactions on a single-digit tickets are executed each day). On the
> RT side, the error usually looks like
>
> | [Mon Jan 30 22:58:00 2012] [warning]: DBD::Pg::st execute failed:
> ERROR:  prepared statement
> |    "dbdpg_p27347_5" already exists at
> /usr/lib/perl5/site_perl/5.12.1/Apache/Session/Store/DBI.pm
> |    line 44. (/usr/lib/perl5/site_perl/5.12.1/Apache/Session/Store/DBI.pm:44)
>
> On the Postfix side:
>
> | 2012-01-31 00:18:31 CET rt4 rt_user 4f270fbe.633d ERROR:  prepared
> statement "dbdpg_p27347_5" already exists
>
> "27347" is always equal to the PID of the httpd parent process on the
> webserver, which obviously is used as an identifier for the prepared
> statement, together with a seemingly increasing counter.
>
> I have also seen another error messages mixed in with "already exists":
>
> | 2012-01-29 22:46:09 CET rt4 rt_user 4f25be1e.304 ERROR:  bind
> message supplies 2 parameters,
> |   but prepared statement "dbdpg_p27347_5" requires 1
>
> And on the Apache side:
>
> | [Sun Jan 29 21:46:09 2012] [warning]: DBD::Pg::st execute failed:
> ERROR:  bind message supplies
> |   2 parameters, but prepared statement "dbdpg_p27347_5" requires 1 at
> |   usr/lib/perl5/site_perl/5.12.1/DBIx/SearchBuilder/Handle.pm line 509.
>
> If the error condition pops up, the mail gateway is also affected
> (with the same error message).
>
> Things I tried (partially based on suggestions on #rt), unfortunately
> without success:
>
> * Upgrade DBI-related stuff (done on both machines, as non-RT stuff
> could potentially profit on both ends)
> * Upgrade Postgresql to latest supported release
> * "SetHandler perl-script" instead of "SetHandler modperl"
>
> Some setup information:
>
> The webserver / RT installation is on one physical box (together with
> other web apps), the database sits on a different physical box
> (sharing a Postgres instance with other databases). This backend
> access runs over IPv6 (if this makes a difference).
>
> * openSUSE 11.3 (x86_64) on both systems.
> * perl -MDBI -le 'print $DBI::VERSION' => 1.609 on both systems.
> * perl --version => 5.12.1 on both systems
> * psql (server, clients, lib etc) is on version 8.4.7 on both systems.
>
> RT_SiteConfig is set to "Set($WebExternalAuth , 1);"., RT4 lives in
> subdirectory /rt4, Basic Auth happens on the root directory.
> Everything runs over https. There is only a small number of users who
> access RT at all, interaction with external users happens via incoming
> and outgoing email.
>
> Upgrade steps from 3.8.7 to 4.0.4:
>
> | Backup RT3 database: postgres at db:/home/backup>
> |   /usr/bin/pg_dump -Ft rt3 > /home/backup/psqldbs/rt3-pre-rt4-upgrade.tar
> | Create new RT4 database: postgres at db:~> createdb rt4
> | Restore RT4 DB from RT3 dump: postgres at db:~> pg_restore
> |    --dbname=rt4 --verbose /home/backup/psqldbs/rt3-pre-rt4-upgrade.tar
> | wget http://download.bestpractical.com/pub/rt/release/rt-4.0.4.tar.gz
> | tar xvf rt-4.0.4.tar.gz
> | cd rt-4.0.4/
> | ./configure prefix=<local path> --enable-gpg --with-web-user=wwwrun
> |    --with-web-group=www --with-db-type=Pg --with-db-host=db
> --with-db-rt-pass=<password>
> | make testdeps and make fixdeps (repeated)
> | make upgrade
> | Add a line for rt_user access to rt4 to
> |    postgres at db:/var/lib/pgsql/data/pg_hba.conf
> | make upgrade-database
> | Enter RT version you're upgrading from: 3.8.7
>
> I don't want to spam the (public) group with the output of
> Admin/Tools/Configuration.html, but I can put it somewhere if it would
> help diagnosing the issue.
>
> I'm honestly at a loss on how to identify the root cause of this. Any
> hints appreciated, and I'm more than happy to provide more
> config/setup information or perform additional tests. Since the
> problem can not be directly reproduced, but only observed over about
> 24 hours, it is difficult to judge whether a change actually improved
> things, or was irrelevant, but I'll do my best ;)
>
> Thanks,
> -- Matthias
> --------
> RT Training Sessions (http://bestpractical.com/services/training.html)
> * Boston  March 5 & 6, 2012



-- 
Best regards, Ruslan.