4 Replies Latest reply: Jul 17, 2014 8:43 AM by Jigar Shah RSS

    Periodic Recovery Exception, usually on startup.

    Jigar Shah Newbie

      05:42:39,701 WARN  [com.arjuna.ats.jta] (Periodic Recovery) ARJUNA016027: Local XARecoveryModule.xaRecovery got XA exception XAException.XAER_RMERR: org.postgresql.xa.PGXAException: Error rolling back prepared transaction

              at org.postgresql.xa.PGXAConnection.rollback(PGXAConnection.java:420)

              at org.jboss.jca.adapters.jdbc.xa.XAManagedConnection.rollback(XAManagedConnection.java:346)

              at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.handleOrphan(XARecoveryModule.java:755) [jbossjts-jacorb-4.17.15.Final-redhat-4.jar:4.17.15.Final-redhat-4]

              at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.xaRecoverySecondPass(XARecoveryModule.java:661) [jbossjts-jacorb-4.17.15.Final-redhat-4.jar:4.17.15.Final-redhat-4]

              at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.bottomUpRecovery(XARecoveryModule.java:431) [jbossjts-jacorb-4.17.15.Final-redhat-4.jar:4.17.15.Final-redhat-4]

              at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkSecondPass(XARecoveryModule.java:212) [jbossjts-jacorb-4.17.15.Final-redhat-4.jar:4.17.15.Final-redhat-4]

              at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:789) [jbossjts-jacorb-4.17.15.Final-redhat-4.jar:4.17.15.Final-redhat-4]

              at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:371) [jbossjts-jacorb-4.17.15.Final-redhat-4.jar:4.17.15.Final-redhat-4]

      Caused by: org.postgresql.util.PSQLException: ERROR: permission denied to finish prepared transaction

        Hint: Must be superuser or the user that prepared the transaction.

              at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

              at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

              at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

              at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)

              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)

              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331)

              at org.postgresql.xa.PGXAConnection.rollback(PGXAConnection.java:406)

              ... 7 more

       

       

      Getting above exception sometimes on start-up. in Periodic Recovery thread.

       

       

      1. max_prepared_transactions and max_connections are same in postgresql.conf.

      2. Recovery tag provided:

                               <recovery>

                                  <recover-credential>

                                      <user-name>a</user-name>

                                      <password>b</password>

                                  </recover-credential>

                              </recovery>

       

      Any ideas what is should look at ?

        • 1. Re: Periodic Recovery Exception, usually on startup.
          Michael Musgrove Master

          Your datasource config does not have the appropriate permissions for ending the transaction. You could validate this by logging into the database using the credentials you have configured in your datasource and manually roll back the transaction, something similar to:

           

              bash-4.2$ psql

              postgres=# select * from pg_prepared_xacts;

              ...

              rollback prepared  'the entry in the gid column.';

           

          If you can find a set of credentials where this manual rollback succeeds then use those in your datasource configuration.

          • 2. Re: Periodic Recovery Exception, usually on startup.
            Jigar Shah Newbie

            CONSOLE:

             

            reg=> 'select * from pg_prepared_xacts';

             

            transaction |                                               gid                                                |           prepared            |    owner     |   database  
            -------------+--------------------------------------------------------------------------------------------------+-------------------------------+--------------+--------------
                27361558 | 131077_AAAAAAAAAAAAAP//wKiWzJ0AwrFTsrrwAAAqwjE=_AAAAAAAAAAAAAP//wKiWzJ0AwrFTsrrwAAAqywAAAAAAAAAA | 2014-07-01 09:48:35.441639-04 | reg | reg

             

            reg=> ROLLBACK PREPARED '131077_AAAAAAAAAAAAAP//wKiWzJ0AwrFTsrrwAAAqwjE=_AAAAAAAAAAAAAP//wKiWzJ0AwrFTsrrwAAAqywAAAAAAAAAA';
            ROLLBACK PREPARED


            reg=> select * from pg_prepared_xacts;
            transaction | gid | prepared | owner | database
            -------------+-----+----------+-------+----------
            (0 rows)

             

             

            JBoss XA configuration:

             

                                <xa-datasource jndi-name="java:/jdbc/RegDS" pool-name="RegDS" enabled="true">
                                    <xa-datasource-property name="serverName">
                                        localhost
                                    </xa-datasource-property>
                                    <xa-datasource-property name="databaseName">
                                        reg
                                    </xa-datasource-property>
                                    <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
                                    <driver>postgresql-9.3-1101.jdbc41.jar</driver>
                                    <xa-pool>
                                        <max-pool-size>50</max-pool-size>
                                        <pad-xid>false</pad-xid>
                                        <wrap-xa-resource>false</wrap-xa-resource>
                                    </xa-pool>
                                    <security>
                                        <user-name>reg</user-name>
                                        <password>reg</password>
                                    </security>
                                    <recovery>
                                        <recover-credential>
                                            <user-name>reg</user-name>
                                            <password>reg</password>
                                        </recover-credential>
                                    </recovery>
                                    <validation>
                                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
                                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
                                    </validation>
                                    <statement>
                                        <share-prepared-statements>false</share-prepared-statements>
                                    </statement>
                                </xa-datasource>

             

            'reg' user has permissions to roll-back prepared transaction. I was able to rollback prepared transaction with 'reg' user from psql console.

             

            Same credentials are provided in <security/> and <recovery/> configuration in <xa-datasource />.

             

            How do we know that which connection pool is used by Periodic recovery thread triggered by JBoss every 2 min ? I also have more xa data-sources configured in same server.

             

            Is it possible Periodic Recovery is using different credentials, of different XA data-source, and hence, its getting this error. Or is there some configuration mistake.

            • 3. Re: Periodic Recovery Exception, usually on startup.
              Michael Musgrove Master

              Jigar Shah wrote:

               

               

              'reg' user has permissions to roll-back prepared transaction. I was able to rollback prepared transaction with 'reg' user from psql console.

               

              Same credentials are provided in <security/> and <recovery/> configuration in <xa-datasource />.

               

              How do we know that which connection pool is used by Periodic recovery thread triggered by JBoss every 2 min ? I also have more xa data-sources configured in same server.

               

              Is it possible Periodic Recovery is using different credentials, of different XA data-source, and hence, its getting this error. Or is there some configuration mistake.

              Do you have multiple datasources for the same db but with different credentials. If so then Periodic Recovery may end up using one with the "wrong credentials". If this is your config then disable recovery on all datasources (using the <no-recover> tag)  except the one configured with the reg user.

              • 4. Re: Periodic Recovery Exception, usually on startup.
                Jigar Shah Newbie

                Yes found the problem, Recovery Thread is picking up wrong connection (credentials), or i may say its trying to ROLLBACK transactions found by

                'SELECT gid FROM pg_prepared_xacts;', irrespective of owner. So it fails when it tries to roll-back transaction different user.

                instead it should be

                'SELECT gid FROM pg_prepared_xacts,where owner = current_user ?'

                 

                Found it from:

                http://www.postgresql.org/message-id/CAJZ63LON9=EAzyyyhuUCTTp85AcxKGGMiXA9MpLb1gGZj0vs+w@mail.gmail.com


                it may be a fix in postgres driver, as mentioned.