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

[Matthias Leisi]

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