4 Replies Latest reply on Feb 7, 2011 7:40 AM by gertv

    Configure failover using JDBC lock with postgres

    floriantp

      Hi,

       

      I got a question regaring the configuration of failover using JDBC locking mechanism with PostgreSQL.

       

      Unlikely there seems to be a problem acquiring the lock.

       

      The following errormessage is shown in the logfile:

       

      """

      WARNING: Failed to acquire database lock: org.postgresql.util.PSQLException: Method org.postgresql.jdbc4.Jdbc4PreparedStatement.setQueryTimeout(int) is not yet implemented.

      17:51:33,232 | WARN  | Thread-2         | DefaultJDBCLock                  | pache.karaf.main.DefaultJDBCLock  164 |  -  -  | Failed to acquire database lock: org.postgresql.util.PSQLException: Method org.postgresql.jdbc4.Jdbc4PreparedStatement.setQueryTimeout(int) is not yet implemented.

      """

       

      I got the following setup:

       

      • apache-servicemix-4.3.0-fuse-03-00

      • com.springsource.org.postgresql.jdbc4-8.3.604.jar

       

      Postgres version used: "PostgreSQL 8.3.11"

       

      I put the following lines to the "system.properties" file:

       

      """

      karaf.lock=true

      karaf.lock.class=org.apache.karaf.main.DefaultJDBCLock

      karaf.lock.level=50

      karaf.lock.delay=10

      karaf.lock.jdbc.url=jdbc:postgresql://weblin01.int/serviceMix

      karaf.lock.jdbc.driver=org.postgresql.Driver

      karaf.lock.jdbc.user=user

      karaf.lock.jdbc.password=password

      karaf.lock.jdbc.table=KARAF_LOCK

      karaf.lock.jdbc.clustername=karaf

      karaf.lock.jdbc.timeout=30

      """

       

      Maybe some of you got an idea what do to to solve the problem!?

       

      Best,

      Florian

        • 1. Re: Configure failover using JDBC lock with postgres
          floriantp

          Seems like I fixed this issue by changing the timeout property in the 'system.properties" to "karaf.lock.jdbc.timeout=0".

          • 2. Re: Configure failover using JDBC lock with postgres
            gertv

            L.S.,

             

             

            Could you try that again with the JDBC3 version of the driver?  I'm aware that the download page at http://jdbc.postgresql.org/download.html recommends using JDBC4 for JDK 1.6, but it also reports that "Support for JDBC4 methods is limited. The driver builds, but the several of the new methods are stubbed out."

             

            From the information I find online, you might just be hitting one of these stubbed out methods in the new implementation classes.

             

             

            Regards,

             

            Gert

            • 3. Re: Configure failover using JDBC lock with postgres
              floriantp

              Hmmm...

              I did some further tests with the following driver versions from  http://jdbc.postgresql.org/download.html:

               

              • postgresql-8.3-606.jdbc2.jar

              • postgresql-8.3-606.jdbc3.jar

              • postgresql-8.3-606.jdbc4.jar

              • postgresql-9.0-801.jdbc3.jar

              • postgresql-9.0-801.jdbc4.jar

               

              Unfortunatelly none of them worked with 'karaf.lock.jdbc.timeout' > 0.

               

              But in my testsystem the failover worked with 'karaf.lock.jdbc.timeout=0'.

               

              Do you think it's ok to run serviceMix with this configuration?

              Is there any reason to worry?

               

              Best,

              Florian

              • 4. Re: Configure failover using JDBC lock with postgres
                gertv

                L.S.,

                 

                 

                No, I don't think there's a real issue here.  By default, Karaf will wait for 1 second between attempts to allocate the lock.  Without the timeout on the JDBC query itself, it will probably just return straight away when it's unable to allocate the lock.  If you notice the database is being accessed to often, you can also set the 'karaf.lock.delay' property to increase the amount of time Karaf itself waits between attempts.

                 

                FWIW, I raised https://issues.apache.org/jira/browse/KARAF-447 to ensure we either document the PostgreSQL configuration requirements or provide a specific JDBC lock implementation class that can handle it for us.  If you fancy taking a stab at that yourself and need some help getting started, just let us know...

                 

                 

                Regards,

                 

                Gert