4 Replies Latest reply on May 30, 2007 12:21 PM by Peter Johnson

    Setting DefaultDS to use Postgresql in JBoss AS 5.0.0 Beta2

    fatbatman Novice


      How do I set the DefaultDS to use postgresql in Jboss AS 5.0.0 Beta 2?

      regards

      James

        • 1. Re: Setting DefaultDS to use Postgresql in JBoss AS 5.0.0 Be
          Peter Johnson Master

          Do this:

          1) Copy docs/examples/jca/postgres-ds.xml to server/xxx/deploy

          2) Edit the postgres-ds.xml to contain information for your database.

          3) Add a < metadata > entry to postgres-ds.xml (you need this to get some of the services to work correctly):

          <datasources>
           <local-tx-datasource>
           . . .
           <metadata>
           <type-mapping>PostgreSQL 8.0</type-mapping>
           </metadata>
           </local-tx-datasource>
          </datasources>


          4) Delete the server/xxx/deploy/hsqldb-ds.xml file

          5) Start the server. Any required tables are created automatically.

          • 2. Re: Setting DefaultDS to use Postgresql in JBoss AS 5.0.0 Be
            Peter Johnson Master

            Looks like I made a few assumptions in my post. Here are the missing steps:

            1a) Copy the PostgreSQL JDBC driver JAR file to the server/xxx/lib directory.

            1b) Add a user with a password. and create a database in PostgreSQL. (Use this info for step 2)

            In step 2, also change the JNDI name in the postgres-ds.xml file to DefaultDS.

            By the way, you can rename postgres-ds.xml to anything you like, as long as it ends with -ds.xml. I usually use the JNDI name, that is, defaultds-ds.xml.

            • 3. Re: Setting DefaultDS to use Postgresql in JBoss AS 5.0.0 Be
              fatbatman Novice

              Thanks for your response, sorry I should have been more specific in my initial question.
              I'd done what you suggested above but I'm getting problems. These seem to stem from the fact that "qrtz_locks" table does not exist.

              ............
              21:46:03,515 INFO [RARDeployment] Required license terms exist, view vfsfile:/D:/workspace/jboss-5.0.0.Beta2/server/default/deploy/jboss-local-jdbc.r
              ar/META-INF/ra.xml
              21:46:03,750 INFO [WrapperDataSourceService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=DefaultDS' to JNDI name 'java:DefaultD
              S'
              21:46:06,218 WARN [QuartzTimerServiceFactory] sql failed: CREATE TABLE qrtz_job_details(JOB_NAME VARCHAR(80) NOT NULL, JOB_GROUP VARCHAR(80) NOT NULL
              , DESCRIPTION VARCHAR(120) NULL, JOB_CLASS_NAME VARCHAR(128) NOT NULL, IS_DURABLE VARCHAR(1) NOT NULL, IS_VOLATILE VARCHAR(1) NOT NULL, IS_STATEFUL VA
              RCHAR(1) NOT NULL, REQUESTS_RECOVERY VARCHAR(1) NOT NULL, JOB_DATA BINARY NULL, PRIMARY KEY (JOB_NAME,JOB_GROUP))
              21:46:06,656 INFO [SimpleThreadPool] Job execution threads will use class loader of thread: main
              21:46:06,750 INFO [QuartzScheduler] Quartz Scheduler v.1.5.2 created.
              21:46:06,750 INFO [JobStoreCMT] Using db table-based data access locking (synchronization).
              21:46:06,781 ERROR [AbstractKernelController] Error installing to Start: name=jboss.ejb:service=EJB3TimerService state=Create mode=Manual requiredStat
              e=Installed
              org.quartz.SchedulerConfigException: Failure occured during job recovery. [See nested exception: org.quartz.impl.jdbcjobstore.LockException: Failure o
              btaining db row lock: ERROR: relation "qrtz_locks" does not exist [See nested exception: org.postgresql.util.PSQLException: ERROR: relation "qrtz_lock
              s" does not exist]]
               at org.quartz.impl.jdbcjobstore.JobStoreSupport.initialize(JobStoreSupport.java:493)
               at org.quartz.impl.jdbcjobstore.JobStoreCMT.initialize(JobStoreCMT.java:144)
               at org.quartz.impl.StdSchedulerFactory.instantiate(StdSchedulerFactory.java:1010)
               at org.quartz.impl.StdSchedulerFactory.getScheduler(StdSchedulerFactory.java:1152)
               at org.jboss.ejb3.timerservice.quartz.QuartzTimerServiceFactory.start(QuartzTimerServiceFactory.java:257)
               at org.jboss.ejb3.timerservice.quartz.jmx.EJB3TimerService.startService(EJB3TimerService.java:105)
               at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
               at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
               at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
              ...........
              


              • 4. Re: Setting DefaultDS to use Postgresql in JBoss AS 5.0.0 Be
                Peter Johnson Master

                Yet some more steps. These replace the Hypersonic-specific messaging configuration files with their PostgreSQL equivalents:

                4a) Download the binary files for JBoss Messaging. Make sure you get the correct version (1.2.0.sp1).

                4b) Delete the file server/xxx/deploy/messaging/hsqldb-persistence-service.xml

                4c) Copy the file examples/config/mysql-persistence-service.xml from the JBoss Messaging binary download to the server/xxx/deply/messaging directory

                However, this doesn't fix the problem with Quartz. Looking at http://www-128.ibm.com/developerworks/java/library/j-quartz/, it would appear that you have the manually create the database tables before Quartz can use a database as a job store. But what bugs me is that in the quartz.properties file, the org.quartz.jobStore.class property is set to org.quartz.simpl.RAMJobStore, which means that it should not be using the database.