Prepared statement already exists

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();” 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@db:/home/backup>
| /usr/bin/pg_dump -Ft rt3 > /home/backup/psqldbs/rt3-pre-rt4-upgrade.tar
| Create new RT4 database: postgres@db:~> createdb rt4
| Restore RT4 DB from RT3 dump: postgres@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= --enable-gpg --with-web-user=wwwrun
| --with-web-group=www --with-db-type=Pg --with-db-host=db
–with-db-rt-pass=
| make testdeps and make fixdeps (repeated)
| make upgrade
| Add a line for rt_user access to rt4 to
| postgres@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 :wink:

Thanks,
– Matthias

One addition:

  • perl -MDBI -le ‘print $DBI::VERSION’ => 1.609 on both systems.

DBD::Pg is at 2.18.1, which seems to be the most recent released
version according to DBD::Pg - PostgreSQL database driver for the DBI module - metacpan.org

– Matthias

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:

DBD::Pg - PostgreSQL database driver for the DBI module - metacpan.org Wed, Feb 1, 2012 at 17:50, Matthias Leisi matthias@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();” 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@db:/home/backup>
| /usr/bin/pg_dump -Ft rt3 > /home/backup/psqldbs/rt3-pre-rt4-upgrade.tar
| Create new RT4 database: postgres@db:~> createdb rt4
| Restore RT4 DB from RT3 dump: postgres@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= --enable-gpg --with-web-user=wwwrun
| --with-web-group=www --with-db-type=Pg --with-db-host=db
–with-db-rt-pass=
| make testdeps and make fixdeps (repeated)
| make upgrade
| Add a line for rt_user access to rt4 to
| postgres@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 :wink:

Thanks,
– Matthias

RT Training Sessions (http://bestpractical.com/services/training.html)

  • Boston March 5 & 6, 2012

Best regards, Ruslan.

(I just realized that I originally sent my response to Ruslan only. My bad.)

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

Admin/Tools/Configuration.html says:

| DB 1.26
| DBD::Pg 2.18.1
/usr/lib/perl5/site_perl/5.12.1/x86_64-linux-thread-multi/DBD/Pg.pm
| DBD::Sponge 12.010002
/usr/lib/perl5/vendor_perl/5.12.1/x86_64-linux-thread-multi/DBD/Sponge.pm
| DBI 1.609
/usr/lib/perl5/vendor_perl/5.12.1/x86_64-linux-thread-multi/DBI.pm

loads. Try downgrading DBD::Pg.

I’m a bit uneasy about messing around with Perl modules, so I’ll try
the next one first:

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)

Is there a safe way to add that option within RT? I see that
lib/RT/Handle.pm has sub Connect which get’s passed %args which
supposedly is prepared in sub BuildDSN in the same file? Is there a
conceptually better alternative to this:

(in sub BuildDSN, after %args has been set):

if ( $db_type eq ‘Pg’ ) {
$args{‘pg_server_prepare’ = 1;
}

– Matthias