[Logo] Terracotta Discussion Forums
  [Search] Search   [Recent Topics] Recent Topics   [Members]  Member Listing   [Groups] Back to home page 
[Register] Register /  [Login] Login 
[Expert]
JobStoreTX : Unknown column 'TRIGGER_TYPE' in 'field list'  XML
Forum Index -> Quartz
Author Message
[Post New]05/18/2011 03:37:43
    Subject: JobStoreTX : Unknown column 'TRIGGER_TYPE' in 'field list'
[Up]
Jeh

journeyman

Joined: 05/18/2011 02:52:11
Messages: 10
Offline
Hi !

I got an issue while starting my quartz (2.0.0) webapp on Tomcat 6.
Quartz couldn't store a job beacause of an unknown column TRIGGER_TYPE.
Actually, there's no TRIGGER_TYPE column in database.

Could you please help me to solve this issue ?

Here is the job config :

Code:
 <schedule>
         <!-- JOBS -->
 	<job>
             <name>TraitBdd</name>
             <group>QRTZ</group>
             <description>I</description>
             <job-class>com.afone.testclusterquartz.job.TraitBdd</job-class>
         </job>
 		
 		<!-- TRIGGERS -->
          <trigger>
             <cron>
                 <name>TraitBdd_Trigger</name>
                 <group>T_QRTZ</group>
                 <job-name>TraitBdd</job-name>
                 <job-group>QRTZ</job-group>
                 <cron-expression>* * * * * ?</cron-expression>
             </cron>
         </trigger>
     </schedule>


the quartz config :
Code:
 #============================================================================
 # Configure Main Scheduler Properties
 #============================================================================
 
 org.quartz.scheduler.instanceName = QrtzClusteredScheduler
 org.quartz.scheduler.instanceId = AUTO
 
 #============================================================================
 # Configure ThreadPool
 #============================================================================
 
 org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
 org.quartz.threadPool.threadCount = 2
 org.quartz.threadPool.threadPriority = 5
 
 #===================================================
 # Configure the Job Initialization Plugin
 #===================================================
 
 org.quartz.plugin.jobInitializer.class = org.quartz.plugins.xml.XMLSchedulingDataProcessorPlugin
 org.quartz.plugin.jobInitializer.fileNames = jobs.xml
 org.quartz.plugin.jobInitializer.failOnFileNotFound = true
 org.quartz.plugin.jobInitializer.scanInterval = 0
 org.quartz.plugin.jobInitializer.wrapInUserTransaction = false
 
 #============================================================================
 # Configure JobStore
 #============================================================================
 
 org.quartz.jobStore.misfireThreshold = 30000
 
 org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
 #org.quartz.jobStore.class = org.quartz.simpl.RAMJobStore
 org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
 org.quartz.jobStore.useProperties = false
 org.quartz.jobStore.dataSource = myDS
 org.quartz.jobStore.tablePrefix = QRTZ_
 org.quartz.jobStore.isClustered = true
 org.quartz.jobStore.clusterCheckinInterval = 500
 
 #============================================================================
 # Configure Datasources
 #============================================================================
 
 org.quartz.dataSource.myDS.driver = com.mysql.jdbc.Driver
 org.quartz.dataSource.myDS.URL = jdbc:mysql://IP:3306/QRTZ2_TEST
 org.quartz.dataSource.myDS.user = login
 org.quartz.dataSource.myDS.password = passwd
 org.quartz.dataSource.myDS.maxConnections = 5
 org.quartz.dataSource.myDS.validationQuery=select 0 from dual

and the exception thrown :

Code:
 Error scheduling jobs: Couldn't store trigger 'T_QRTZ.TraitBdd_Trigger' for 'QRTZ.TraitBdd' job:Unknown column 'TRIGGER_TYPE' in 'field list'
 org.quartz.JobPersistenceException: Couldn't store trigger 'T_QRTZ.TraitBdd_Trigger' for 'QRTZ.TraitBdd' job:Unknown column 'TRIGGER_TYPE' in 'field list' [See nested exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'TRIGGER_TYPE' in 'field list']
         at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeTrigger(JobStoreSupport.java:1167)
         at org.quartz.impl.jdbcjobstore.JobStoreSupport$2.execute(JobStoreSupport.java:1007)
         at org.quartz.impl.jdbcjobstore.JobStoreSupport$45.execute(JobStoreSupport.java:3654)
         at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3726)
         at org.quartz.impl.jdbcjobstore.JobStoreTX.executeInLock(JobStoreTX.java:90)
         at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInLock(JobStoreSupport.java:3650)
         at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeJobAndTrigger(JobStoreSupport.java:1002)
         at org.quartz.core.QuartzScheduler.scheduleJob(QuartzScheduler.java:816)
         at org.quartz.impl.StdScheduler.scheduleJob(StdScheduler.java:250)
         at org.quartz.xml.XMLSchedulingDataProcessor.scheduleJobs(XMLSchedulingDataProcessor.java:1119)
         at org.quartz.xml.XMLSchedulingDataProcessor.processFileAndScheduleJobs(XMLSchedulingDataProcessor.java:892)
         at org.quartz.plugins.xml.XMLSchedulingDataProcessorPlugin.processFile(XMLSchedulingDataProcessorPlugin.java:327)
         at org.quartz.plugins.xml.XMLSchedulingDataProcessorPlugin.start(XMLSchedulingDataProcessorPlugin.java:255)
         at org.quartz.plugins.SchedulerPluginWithUserTransactionSupport.start(SchedulerPluginWithUserTransactionSupport.java:144)
         at org.quartz.core.QuartzScheduler.startPlugins(QuartzScheduler.java:2292)
         at org.quartz.core.QuartzScheduler.start(QuartzScheduler.java:508)
         at org.quartz.impl.StdScheduler.start(StdScheduler.java:143)
         at org.quartz.ee.servlet.QuartzInitializerListener.contextInitialized(QuartzInitializerListener.java:202)
         at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4205)
         at org.apache.catalina.core.StandardContext.start(StandardContext.java:4704)
         at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:799)
         at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:779)
         at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:601)
         at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:943)
         at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:778)
         at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:504)
         at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1315)
         at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:324)
         at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:142)
         at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1061)
         at org.apache.catalina.core.StandardHost.start(StandardHost.java:840)
         at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
         at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:463)
         at org.apache.catalina.core.StandardService.start(StandardService.java:525)
         at org.apache.catalina.core.StandardServer.start(StandardServer.java:754)
         at org.apache.catalina.startup.Catalina.start(Catalina.java:595)
         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
         at java.lang.reflect.Method.invoke(Method.java:597)
         at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
         at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)


[Post New]05/18/2011 07:39:14
    Subject: Re:JobStoreTX : Unknown column 'TRIGGER_TYPE' in 'field list'
[Up]
loriente

journeyman

Joined: 05/10/2011 12:10:29
Messages: 37
Offline
@jeh

I think either the SQL wasn't run appropriately or you run some older version SQL.

I checked the 2.0.1 SQL provided with the distribution and the TRIGGER_TYPE is in there. I also checked 1.8.4, which I use, and SQL is there as well.

Make sure you run the SQL that is provided with official full Quartz distribution.

Here is SQL shipped with Quartz 2.0.1 for MySQL:

Code:
#
 # Quartz seems to work best with the driver mm.mysql-2.0.7-bin.jar
 #
 # PLEASE consider using mysql with innodb tables to avoid locking issues
 #
 # In your Quartz properties file, you'll need to set 
 # org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
 #
 
 DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;
 DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;
 DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;
 DROP TABLE IF EXISTS QRTZ_LOCKS;
 DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS;
 DROP TABLE IF EXISTS QRTZ_SIMPROP_TRIGGERS;
 DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS;
 DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS;
 DROP TABLE IF EXISTS QRTZ_TRIGGERS;
 DROP TABLE IF EXISTS QRTZ_JOB_DETAILS;
 DROP TABLE IF EXISTS QRTZ_CALENDARS;
 
 
 CREATE TABLE QRTZ_JOB_DETAILS
   (
     SCHED_NAME VARCHAR(120) NOT NULL,
     JOB_NAME  VARCHAR(200) NOT NULL,
     JOB_GROUP VARCHAR(200) NOT NULL,
     DESCRIPTION VARCHAR(250) NULL,
     JOB_CLASS_NAME   VARCHAR(250) NOT NULL,
     IS_DURABLE VARCHAR(1) NOT NULL,
     IS_NONCONCURRENT VARCHAR(1) NOT NULL,
     IS_UPDATE_DATA VARCHAR(1) NOT NULL,
     REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
     JOB_DATA BLOB NULL,
     PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
 );
 
 CREATE TABLE QRTZ_TRIGGERS
   (
     SCHED_NAME VARCHAR(120) NOT NULL,
     TRIGGER_NAME VARCHAR(200) NOT NULL,
     TRIGGER_GROUP VARCHAR(200) NOT NULL,
     JOB_NAME  VARCHAR(200) NOT NULL,
     JOB_GROUP VARCHAR(200) NOT NULL,
     DESCRIPTION VARCHAR(250) NULL,
     NEXT_FIRE_TIME BIGINT(13) NULL,
     PREV_FIRE_TIME BIGINT(13) NULL,
     PRIORITY INTEGER 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(200) NULL,
     MISFIRE_INSTR SMALLINT(2) NULL,
     JOB_DATA BLOB NULL,
     PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
     FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
         REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
 );
 
 CREATE TABLE QRTZ_SIMPLE_TRIGGERS
   (
     SCHED_NAME VARCHAR(120) NOT NULL,
     TRIGGER_NAME VARCHAR(200) NOT NULL,
     TRIGGER_GROUP VARCHAR(200) NOT NULL,
     REPEAT_COUNT BIGINT(7) NOT NULL,
     REPEAT_INTERVAL BIGINT(12) NOT NULL,
     TIMES_TRIGGERED BIGINT(10) NOT NULL,
     PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
     FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
         REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
 );
 
 CREATE TABLE QRTZ_CRON_TRIGGERS
   (
     SCHED_NAME VARCHAR(120) NOT NULL,
     TRIGGER_NAME VARCHAR(200) NOT NULL,
     TRIGGER_GROUP VARCHAR(200) NOT NULL,
     CRON_EXPRESSION VARCHAR(200) NOT NULL,
     TIME_ZONE_ID VARCHAR(80),
     PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
     FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
         REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
 );
 
 CREATE TABLE QRTZ_SIMPROP_TRIGGERS
   (          
     SCHED_NAME VARCHAR(120) NOT NULL,
     TRIGGER_NAME VARCHAR(200) NOT NULL,
     TRIGGER_GROUP VARCHAR(200) NOT NULL,
     STR_PROP_1 VARCHAR(512) NULL,
     STR_PROP_2 VARCHAR(512) NULL,
     STR_PROP_3 VARCHAR(512) NULL,
     INT_PROP_1 INT NULL,
     INT_PROP_2 INT NULL,
     LONG_PROP_1 BIGINT NULL,
     LONG_PROP_2 BIGINT NULL,
     DEC_PROP_1 NUMERIC(13,4) NULL,
     DEC_PROP_2 NUMERIC(13,4) NULL,
     BOOL_PROP_1 VARCHAR(1) NULL,
     BOOL_PROP_2 VARCHAR(1) NULL,
     PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
     FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) 
     REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
 );
 
 CREATE TABLE QRTZ_BLOB_TRIGGERS
   (
     SCHED_NAME VARCHAR(120) NOT NULL,
     TRIGGER_NAME VARCHAR(200) NOT NULL,
     TRIGGER_GROUP VARCHAR(200) NOT NULL,
     BLOB_DATA BLOB NULL,
     PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
     FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
         REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
 );
 
 CREATE TABLE QRTZ_CALENDARS
   (
     SCHED_NAME VARCHAR(120) NOT NULL,
     CALENDAR_NAME  VARCHAR(200) NOT NULL,
     CALENDAR BLOB NOT NULL,
     PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
 );
 
 CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS
   (
     SCHED_NAME VARCHAR(120) NOT NULL,
     TRIGGER_GROUP  VARCHAR(200) NOT NULL, 
     PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
 );
 
 CREATE TABLE QRTZ_FIRED_TRIGGERS
   (
     SCHED_NAME VARCHAR(120) NOT NULL,
     ENTRY_ID VARCHAR(95) NOT NULL,
     TRIGGER_NAME VARCHAR(200) NOT NULL,
     TRIGGER_GROUP VARCHAR(200) NOT NULL,
     INSTANCE_NAME VARCHAR(200) NOT NULL,
     FIRED_TIME BIGINT(13) NOT NULL,
     PRIORITY INTEGER NOT NULL,
     STATE VARCHAR(16) NOT NULL,
     JOB_NAME VARCHAR(200) NULL,
     JOB_GROUP VARCHAR(200) NULL,
     IS_NONCONCURRENT VARCHAR(1) NULL,
     REQUESTS_RECOVERY VARCHAR(1) NULL,
     PRIMARY KEY (SCHED_NAME,ENTRY_ID)
 );
 
 CREATE TABLE QRTZ_SCHEDULER_STATE
   (
     SCHED_NAME VARCHAR(120) NOT NULL,
     INSTANCE_NAME VARCHAR(200) NOT NULL,
     LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
     CHECKIN_INTERVAL BIGINT(13) NOT NULL,
     PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
 );
 
 CREATE TABLE QRTZ_LOCKS
   (
     SCHED_NAME VARCHAR(120) NOT NULL,
     LOCK_NAME  VARCHAR(40) NOT NULL, 
     PRIMARY KEY (SCHED_NAME,LOCK_NAME)
 );
 
 
 commit;


nicolas.loriente
[Post New]05/18/2011 09:03:46
    Subject: Re:JobStoreTX : Unknown column 'TRIGGER_TYPE' in 'field list'
[Up]
Jeh

journeyman

Joined: 05/18/2011 02:52:11
Messages: 10
Offline
the table creation has failed for this table.
With a source of the sql script, it's now ok.
[Post New]07/21/2011 00:05:19
    Subject: JobStoreTX : Unknown column 'TRIGGER_TYPE' in 'field list'
[Up]
gyan10

ophanim

Joined: 06/28/2011 23:15:25
Messages: 700
Offline
This is an old thread and no update from a long time.Please let us know if you are still facing same issue.
Thanks
Gyan Awasthi
Terracotta -A Software AG company
 
Forum Index -> Quartz
Go to:   
Powered by JForum 2.1.7 © JForum Team