6 Replies Latest reply on Feb 13, 2009 11:42 AM by peterj

    Create table warning... Need help!

      Everyone:

      I recently moved JBoss AS 5.0.0.GA from Hypersonic to MySQL.
      I believe I have resolved all resulting issues, except for this
      rather irritating CREATE TABLE warning related to the
      QuartzTimerServiceFactory (which occurs when starting
      the server), shown below:


      14:27:24,500 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 VARCHAR(1) NOT NULL, REQUESTS_RECOVERY VARCHAR(1) NOT NULL, JOB_DATA BLOB NULL, PRIMARY KEY (JOB_NAME,JOB_GROUP) )


      In my own efforts, I have thus far determined that:

      1. The QRTZ_JOB_DETAILS table is properly created on the
      first bootup of JBoss AS. Once created, future startups
      present the above warning.

      2. It has been suggested in other blogs/forums that a possible
      resolution is to set CREATE_DB_ON_STARTUP = FALSE within
      the server/xxx/deploy/ejb3-timer-service.xml file, but this
      does not appear to have any effect.

      My own educated guess is that some JBoss AS code attempts
      to create the appropriate tables based upon the specs found
      in the ejb3-timer-service.xml file on each startup of the server,
      where the first startup creates the tables, and all future
      startup attempts fail (obviously) since the tables already
      exist (hence the warning). It would seem to me, however,
      that the server code would check to see if the tables
      already exist before attempting to create them, thereby
      avoiding unnecessary warnings. This leaves me with that
      aching feeling that I have not resolved an existing problem.

      After an exhausting search on the web, I am frustratingly
      out of ideas for the moment. Any help would be greatly
      appreciated!!!!

      Thanks in advance.
      ...

        • 1. Re: Create table warning... Need help!
          peterj
          • 2. Re: Create table warning... Need help!

            Dear PeterJ,

            Thanks for the prompt response and info. It is
            greatly appreciated.

            I reviewed the information provided at the URL.
            Currently, I am leveraging the suggestions in the
            "JBoss in Action" book, and have performed the
            prescribed alterations to the ejb3-timer-service.xml
            file.

            As for the additional comment about removing the
            ejb3-timer-service.xml file, I am not quick to apply
            this approach given the attention provided to this
            issue by the "JBoss in Action" book.

            Since changes to the ejb3-timer-service.xml file
            are a bit involved, it is possible that a flaw was
            introduced in the file (although my gut tells me that
            the root of the problem is probably elsewhere -- but
            guts can be wrong!). This hypothesis can be tested
            by someone providing to me a copy of the
            ejb3-timer-service.xml file for a working MySQL
            implementation of JBoss AS 5.0.0.GA. If my
            difficulty persists with such a provided file, then it
            is likely not the ejb3-timer-service.xml file.

            THEREFORE....

            I shall endeavor to hunt down a copy of the
            ejb3-timer-service.xml file for a JBoss AS 5.0.0.GA
            installation using MySQL. If you, or someone else,
            has such a copy, please be so kind as to forward
            it along.

            Thanks again.
            ...





            • 3. Re: Create table warning... Need help!

              In case anyone can glean any insights into
              my existing ejb3-timer-service.xml file, I
              provide a copy of it below. If anyone
              identifies a problem, please let me know!
              As a note, it has been altered to support
              MySQL with JBoss AS 5.0.0.GA.

              Thanks again in advance.


              <?xml version="1.0" encoding="UTF-8"?>
              <server>
               <!-- TODO: the ejb deployer must depend on this -->
              
               <!-- ================================================ -->
               <!-- Defines the Quartz configuration for -->
               <!-- the EJB3 Timer Service -->
               <!-- ================================================ -->
               <mbean code="org.jboss.ejb3.timerservice.quartz.jmx.EJB3TimerService"
               name="jboss.ejb:service=EJB3TimerService">
               <depends>jboss:service=Naming</depends>
               <depends>jboss:service=TransactionManager</depends>
               <depends optional-attribute-name="DataSource">
               jboss.jca:service=DataSourceBinding,name=DefaultDS
               </depends>
              
               <attribute name="Properties">
               org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreCMT
               org.quartz.jobStore.nonManagedTXDataSource=myDS
               org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.StdJDBCDelegate
               org.quartz.jobStore.tablePrefix=QRTZ_
               org.quartz.jobStore.dataSource=myDS
              
               # To get it to work with hypersonic
               # FIXME: this doesn't lock the row
               org.quartz.jobStore.selectWithLockSQL=SELECT * FROM QRTZ_LOCKS WHERE lock_name = ?
              
               # from quartz.properties
               org.quartz.scheduler.instanceName=JBossEJB3QuartzScheduler
               org.quartz.scheduler.rmi.export=false
               org.quartz.scheduler.rmi.proxy=false
               org.quartz.scheduler.wrapJobExecutionInUserTransaction=false
              
               org.quartz.threadPool.class=org.quartz.simpl.SimpleThreadPool
               org.quartz.threadPool.threadCount=10
               org.quartz.threadPool.threadPriority=5
               org.quartz.threadPool.threadsInheritContextClassLoaderOfInitializingThread=true
              
               org.quartz.jobStore.misfireThreshold=60000
               </attribute>
               <attribute name="SqlProperties">
               CREATE_DB_ON_STARTUP = TRUE
              
               CREATE_TABLE_JOB_DETAILS = \
               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 VARCHAR(1) NOT NULL, \
               REQUESTS_RECOVERY VARCHAR(1) NOT NULL, \
               JOB_DATA BLOB NULL, \
               PRIMARY KEY (JOB_NAME,JOB_GROUP) \
               )
               CREATE_TABLE_JOB_LISTENERS = \
               CREATE TABLE QRTZ_JOB_LISTENERS \
               ( \
               JOB_NAME VARCHAR(80) NOT NULL, \
               JOB_GROUP VARCHAR(80) NOT NULL, \
               JOB_LISTENER VARCHAR(80) NOT NULL, \
               PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER), \
               FOREIGN KEY (JOB_NAME,JOB_GROUP) \
               REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP) \
               )
               CREATE_TABLE_TRIGGERS = \
               CREATE TABLE QRTZ_TRIGGERS \
               ( \
               TRIGGER_NAME VARCHAR(80) NOT NULL, \
               TRIGGER_GROUP VARCHAR(80) NOT NULL, \
               JOB_NAME VARCHAR(80) NOT NULL, \
               JOB_GROUP VARCHAR(80) NOT NULL, \
               IS_VOLATILE VARCHAR(1) NOT NULL, \
               DESCRIPTION VARCHAR(120) NULL, \
               NEXT_FIRE_TIME BIGINT(13) NULL, \
               PREV_FIRE_TIME BIGINT(13) NULL, \
               TRIGGER_STATE VARCHAR(16) NOT NULL, \
               TRIGGER_TYPE VARCHAR(8) NOT NULL, \
               START_TIME BIGINT(13) NOT NULL, \
               END_TIME BIGINT(13) NULL, \
               CALENDAR_NAME VARCHAR(80) NULL, \
               MISFIRE_INSTR SMALLINT(2) NULL, \
               JOB_DATA BLOB NULL, \
               PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), \
               FOREIGN KEY (JOB_NAME,JOB_GROUP) \
               REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP) \
               )
               CREATE_TABLE_SIMPLE_TRIGGERS = \
               CREATE TABLE QRTZ_SIMPLE_TRIGGERS \
               ( \
               TRIGGER_NAME VARCHAR(80) NOT NULL, \
               TRIGGER_GROUP VARCHAR(80) NOT NULL, \
               REPEAT_COUNT BIGINT(7) NOT NULL, \
               REPEAT_INTERVAL BIGINT(12) NOT NULL, \
               TIMES_TRIGGERED BIGINT(7) NOT NULL, \
               PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), \
               FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) \
               REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) \
               )
               CREATE_TABLE_CRON_TRIGGERS = \
               CREATE TABLE QRTZ_CRON_TRIGGERS \
               ( \
               TRIGGER_NAME VARCHAR(80) NOT NULL, \
               TRIGGER_GROUP VARCHAR(80) NOT NULL, \
               CRON_EXPRESSION VARCHAR(80) NOT NULL, \
               TIME_ZONE_ID VARCHAR(80), \
               PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), \
               FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) \
               REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) \
               )
               CREATE_TABLE_BLOB_TRIGGERS = \
               CREATE TABLE QRTZ_BLOB_TRIGGERS \
               ( \
               TRIGGER_NAME VARCHAR(80) NOT NULL, \
               TRIGGER_GROUP VARCHAR(80) NOT NULL, \
               BLOB_DATA BLOB NULL, \
               PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), \
               FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) \
               REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) \
               )
               CREATE_TABLE_TRIGGER_LISTENERS = \
               CREATE TABLE QRTZ_TRIGGER_LISTENERS \
               ( \
               TRIGGER_NAME VARCHAR(80) NOT NULL, \
               TRIGGER_GROUP VARCHAR(80) NOT NULL, \
               TRIGGER_LISTENER VARCHAR(80) NOT NULL, \
               PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER), \
               FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) \
               REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) \
               )
               CREATE_TABLE_CALENDARS = \
               CREATE TABLE QRTZ_CALENDARS \
               ( \
               CALENDAR_NAME VARCHAR(80) NOT NULL, \
               CALENDAR BLOB NOT NULL, \
               PRIMARY KEY (CALENDAR_NAME) \
               )
               CREATE_TABLE_PAUSED_TRIGGER_GRPS = \
               CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS \
               ( \
               TRIGGER_GROUP VARCHAR(80) NOT NULL, \
               PRIMARY KEY (TRIGGER_GROUP) \
               )
               CREATE_TABLE_FIRED_TRIGGERS = \
               CREATE TABLE QRTZ_FIRED_TRIGGERS \
               ( \
               ENTRY_ID VARCHAR(95) NOT NULL, \
               TRIGGER_NAME VARCHAR(80) NOT NULL, \
               TRIGGER_GROUP VARCHAR(80) NOT NULL, \
               IS_VOLATILE VARCHAR(1) NOT NULL, \
               INSTANCE_NAME VARCHAR(80) NOT NULL, \
               FIRED_TIME BIGINT(13) NOT NULL, \
               STATE VARCHAR(16) NOT NULL, \
               JOB_NAME VARCHAR(80) NULL, \
               JOB_GROUP VARCHAR(80) NULL, \
               IS_STATEFUL VARCHAR(1) NULL, \
               REQUESTS_RECOVERY VARCHAR(1) NULL, \
               PRIMARY KEY (ENTRY_ID) \
               )
               CREATE_TABLE_SCHEDULER_STATE = \
               CREATE TABLE QRTZ_SCHEDULER_STATE \
               ( \
               INSTANCE_NAME VARCHAR(80) NOT NULL, \
               LAST_CHECKIN_TIME BIGINT(13) NOT NULL, \
               CHECKIN_INTERVAL BIGINT(13) NOT NULL, \
               RECOVERER VARCHAR(80) NULL, \
               PRIMARY KEY (INSTANCE_NAME) \
               )
               CREATE_TABLE_LOCKS = \
               CREATE TABLE QRTZ_LOCKS \
               ( \
               LOCK_NAME VARCHAR(40) NOT NULL, \
               PRIMARY KEY (LOCK_NAME) \
               )
               INSERT_TRIGGER_ACCESS = \
               INSERT INTO QRTZ_LOCKS values('TRIGGER_ACCESS')
               INSERT_JOB_ACCESS = \
               INSERT INTO QRTZ_LOCKS values('JOB_ACCESS')
               INSERT_CALENDAR_ACCESS = \
               INSERT INTO QRTZ_LOCKS values('CALENDAR_ACCESS')
               INSERT_STATE_ACCESS = \
               INSERT INTO QRTZ_LOCKS values('STATE_ACCESS')
               INSERT_MISFIRE_ACCESS = \
               INSERT INTO QRTZ_LOCKS values('MISFIRE_ACCESS')
               </attribute>
               </mbean>
              </server>
              


              • 4. Re: Create table warning... Need help!
                peterj

                Look at the source code for the book. For chapter 15, target 03 builds a configuration that uses MySQL or PostgreSQL as the replacement database. If you run that target, you can get the ejb3-timer-service.xml file from there.

                • 5. Re: Create table warning... Need help!

                  Dear PeterJ,

                  Thanks for the info. I downloaded the source for
                  JBoss In Action, and built the Ch 15 production
                  database (from which I discovered some
                  minor configuring in the ch00/build.resources
                  file - designating MySQL as the target DB, as
                  well as providing MySQL root/pasword info).

                  In any case, the Ant build was successful, so
                  I now have a second production environment
                  with which to compare when encountering
                  issues going forward - thanks.

                  Unfortunately, when I executed the production
                  server configuration, I encountered the same
                  warning (i.e., ran the production server once,
                  which created the appropriate DB tables and
                  had no warning; then ran the production server
                  again, upon which the CREATE_TABLE warning
                  occurred).

                  It appears that the production implementation
                  provided by the example source for the
                  JBoss in Action book has the same issue
                  (i.e., a CREATE_TABLE warning on all
                  executions following the initial execution).

                  Thanks again for your suggestions.
                  ...

                  • 6. Re: Create table warning... Need help!
                    peterj

                    That's what I get for not reading your post more carefully. The warning is expected and is not an issue. Apparently quartz blindly attempts to create the table each time it runs and when it discovers the table is there already it generates a warning.