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

    Create table warning... Need help!

    Jeff Greimann Newbie

      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.
      ...

        • 2. Re: Create table warning... Need help!
          Jeff Greimann Newbie

          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!
            Jeff Greimann Newbie

            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!
              Peter Johnson Master

              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!
                Jeff Greimann Newbie

                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!
                  Peter Johnson Master

                  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.