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@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=<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@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
