error when I use JdbcSchedulerHistoryPlugin against Oracle Database

[GoogleCodeExporter]

What steps will reproduce the problem?
1. use JdbcSchedulerHistoryPlugin against Oracle Database 
2. There's exception when create a records  into qrtz_scheduler_history for 
event_time field
3.  This is the exception log.
[16:11:38][DEBUG](AbstractControllerServlet.java:98) - View name: 
/dashboard/list
[16:11:38][DEBUG](AbstractControllerServlet.java:113) - Forward: 
/WEB-INF/jsp/main/dashboard/list.jsp
[16:11:38][ERROR](SchedulerSignalerImpl.java:76) - Error notifying listeners of 
trigger misfire.
org.quartz.SchedulerException: TriggerListener 'MyJobHistoryPlugin' threw 
exception: Failed to execute DB connection act
ion. [See nested exception: myschedule.quartz.extra.QuartzRuntimeException: 
Failed to execute DB connection action.]
        at org.quartz.core.QuartzScheduler.notifyTriggerListenersMisfired(QuartzScheduler.java:1858)
        at org.quartz.core.SchedulerSignalerImpl.notifyTriggerListenersMisfired(SchedulerSignalerImpl.java:74)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.doUpdateOfMisfiredTrigger(JobStoreSupport.java:1017)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.recoverMisfiredJobs(JobStoreSupport.java:975)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.doRecoverMisfires(JobStoreSupport.java:3187)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport$MisfireHandler.manage(JobStoreSupport.java:3946)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport$MisfireHandler.run(JobStoreSupport.java:3967)
Caused by: myschedule.quartz.extra.QuartzRuntimeException: Failed to execute DB 
connection action.
        at myschedule.quartz.extra.JdbcSchedulerHistoryPlugin.withConn(JdbcSchedulerHistoryPlugin.java:189)
        at myschedule.quartz.extra.JdbcSchedulerHistoryPlugin.insertHistory(JdbcSchedulerHistoryPlugin.java:169)
        at myschedule.quartz.extra.JdbcSchedulerHistoryPlugin.access$900(JdbcSchedulerHistoryPlugin.java:95)
        at myschedule.quartz.extra.JdbcSchedulerHistoryPlugin$HistoryTriggerListener.triggerMisfired(JdbcSchedulerHistor
yPlugin.java:572)
        at org.quartz.core.QuartzScheduler.notifyTriggerListenersMisfired(QuartzScheduler.java:1856)
        ... 6 more
Caused by: java.sql.SQLException: Invalid column type
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
        at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9262)
        at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8843)
        at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9565)
        at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9548)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:365)
        at myschedule.quartz.extra.JdbcSchedulerHistoryPlugin$3.onConn(JdbcSchedulerHistoryPlugin.java:174)
        at myschedule.quartz.extra.JdbcSchedulerHistoryPlugin.withConn(JdbcSchedulerHistoryPlugin.java:187)

There are timestamp and date filed in Oracle. I don't know you have plan to 
extend this module to oracle as well.

Original issue reported on code.google.com by james.yo...@gmail.com on 8 Mar 2012 at 11:17

[GoogleCodeExporter]

Hi James. Sounds like you just need to convert the schema I have (MySQL) to 
oracle specific. I can try test one when I get time.

Original comment by saltnlight5 on 13 Mar 2012 at 4:30

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

I too get the same error.

org.quartz.SchedulerException: TriggerListener 'MyJobHistoryPlugin' threw 
exception: Failed to execute DB connection act

can you help me out with that ?

Original comment by ani...@gmail.com on 16 Mar 2012 at 5:51

[GoogleCodeExporter]

Can you guys show me your full quartz.properties that shows how you configure 
the JdbcSchedulerHistoryPlugin?

Original comment by saltnlight5 on 17 Mar 2012 at 4:08

[GoogleCodeExporter]

Hi,

attached is my quartz.properties.

The invalid column is due to the date/timespan field. 
to make it work with my oracle db, what i did was i imported the source code of 
JdbcSchedulerHistoryPlugin.java locally and edited the line 

public void schedulerStarted() {
            Object[] params = new Object[] {
                localIp,
                localHost,
                schedulerNameAndId,
                "SchedulerListener",
                "schedulerStarted",
                //new Date(),
                new Timestamp(Calendar.getInstance().getTime().getTime()),
                null,
                null,
                null,
                null,
                null
            };
            insertHistory(insertSql, params);

I commented out the //new date() (this is the java.util.date) and not 
java.sql.date thus I commented that and replaced it with timespan as shown 
below.

I did this in each of the methods where insertHistory was called.

Can you recommend me any other solution or a perminant fix 

thanks for the quick reply.

Original comment by ani...@gmail.com on 17 Mar 2012 at 5:22

Attachments:

• quartz.properties

[GoogleCodeExporter]

Okay, I see the problem now. The Oracle JDBC doesn't like or play with default 
values when binding SQL parameters as we as MYSQL. So it error out when we try 
to bind it.

I've committed a fix that make JdbcSchedulerHistoryPlugin more friendly. Can 
you guys grap the latest myschedule-2.4.2-SNAPSHOT.war from the download and 
retry this?

BTW, the Oracle schema should be like this (use TIMESTAMP!)

CREATE TABLE "QRTZ_SCHEDULER_HISTORY"
(
"HOST_IP" VARCHAR2(120 BYTE) NOT NULL,
"HOST_NAME" VARCHAR2(200 BYTE) NOT NULL,
"SCHEDULER_NAME" VARCHAR2(250 BYTE) NOT NULL,
"EVENT_TYPE" VARCHAR2(250 BYTE) NOT NULL,
"EVENT_NAME" VARCHAR2(250 BYTE) NOT NULL,
"EVENT_TIME" TIMESTAMP NOT NULL,
"INFO1" VARCHAR2(250 BYTE),
"INFO2" VARCHAR2(250 BYTE),
"INFO3" VARCHAR2(250 BYTE),
"INFO4" VARCHAR2(250 BYTE),
"INFO5" VARCHAR2(250 BYTE)
);
CREATE INDEX QRTZ_SCHEDULER_HISTORY_INDEX on QRTZ_SCHEDULER_HISTORY(HOST_IP, 
HOST_NAME, EVENT_TYPE,EVENT_NAME,EVENT_TIME);

Original comment by saltnlight5 on 17 Mar 2012 at 6:09

[GoogleCodeExporter]

Hi

thanks for the reply, I will try this out today and revert. A quick question.. 
I am actually not using the war file as my functionality. I am just using the 
plugin feature to store history data. Thus I hope if I just extract the war and 
add the myschedule-quartz-extra-2.4.2-SNAPSHOT.jar file to my project and run, 
that should still test your changes right ?

correct me if I am wrong.

thanks again for the response. 

Original comment by ani...@gmail.com on 17 Mar 2012 at 7:00

[GoogleCodeExporter]

That's correct Anil.

Original comment by saltnlight5 on 17 Mar 2012 at 7:12

[GoogleCodeExporter]

Hi,

Sorry for the delayed reply. I did tested it with the jar as mentioned above 
and it works fine except for an error which is continuously there in the logs. 

This error was also present in the previous version of the jar, I feel it is 
not logging a certain type of event. 

I feel, this has references to the core quartz file.. will it be possible to 
fix this ?

I have attached the complete trace with debugs here in the attachment.

thanks !

Original comment by ani...@gmail.com on 18 Mar 2012 at 5:46

Attachments:

• logs.txt

[GoogleCodeExporter]

Hello,

Please ignore my previous comment. I had not removed the previous version of 
the jar and thus I was getting the error as listed above. 

Now when I remove the previous versions of the jar, I get the following error.
With this fix, my application does not even start. the scheduler starts and 
immediately shutsdown. 

Is there a way to fix this ?

Original comment by ani...@gmail.com on 18 Mar 2012 at 6:00

[GoogleCodeExporter]

Original comment by ani...@gmail.com on 18 Mar 2012 at 6:01

Attachments:

• log2.txt

[GoogleCodeExporter]

Hum... seems like your Oracle driver didn't return values when calling 
PreparedStatement#getMetaData(), which used to inspect the column types for 
conversion. It would seems odd Oracle won't return data on that.

I have tested this against a OracleXE 11g and it works fine. Maybe you need 
upgrade jdbc driver?

Original comment by saltnlight5 on 18 Mar 2012 at 11:08

[GoogleCodeExporter]

yes this fixes it.

thanks !

Original comment by ani...@gmail.com on 19 Mar 2012 at 2:33

[GoogleCodeExporter]

fixed in Release-2.4.2

Original comment by saltnlight5 on 21 Mar 2012 at 3:36

• Changed state: Fixed
• Added labels: Release-2.4.2