Getting "Invalid column name 'SCHED_TIME'" Error After Upgrading TIBCO JasperReports® Server from version 7.1 to 7.5

Problem:

Our customer has reported that after upgrading to TIBCO JasperReports® Server 7.5.1 from 7.1.3, they are getting the following error when starting the server:
Table of Contents 
 
ERROR ErrorLogger,quartzScheduler_QuartzSchedulerThread:2407 - An error occurred while scanning for the next triggers to fire.[]
org.quartz.JobPersistenceException: Couldn't acquire next trigger: [TibcoSoftware][SQLServer JDBC Driver][SQLServer]Invalid column name 'SCHED_TIME'.
at org.quartz.impl.jdbcjobstore.JobStoreSupport$41.execute(JobStoreSupport.java:2805) ~[quartz-2.3.2.jar:?]
at tibcosoftware.jdbc.sqlserverbase.ddcw.b(Unknown Source) ~[TIsqlserver-5.1.4.000242.jar:?]
at tibcosoftware.jdbc.sqlserverbase.ddcw.a(Unknown Source) ~[TIsqlserver-5.1.4.000242.jar:?]
at tibcosoftware.jdbc.sqlserver.tds.ddr.v(Unknown Source) ~[TIsqlserver-5.1.4.000242.jar:?]
at tibcosoftware.jdbc.sqlserver.tds.ddr.a(Unknown Source) ~[TIsqlserver-5.1.4.000242.jar:?]
at org.quartz.impl.jdbcjobstore.JobStoreSupport$41.execute(JobStoreSupport.java:2803) ~[quartz-2.3.2.jar:?]
at tibcosoftware.jdbc.sqlserverbase.dde7.e(Unknown Source) ~[TIsqlserver-5.1.4.000242.jar:?]
at tibcosoftware.jdbc.sqlserverbase.ddd2.a(Unknown Source) ~[TIsqlserver-5.1.4.000242.jar:?]
at tibcosoftware.jdbc.sqlserverbase.dde7.z(Unknown Source) ~[TIsqlserver-5.1.4.000242.jar:?]
at tibcosoftware.jdbc.sqlserver.tds.ddr.c(Unknown Source) ~[TIsqlserver-5.1.4.000242.jar:?]
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) ~[commons-dbcp-1.4.jar:1.4]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:2906) ~[quartz-2.3.2.jar:?]
... 5 more
at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.insertFiredTrigger(StdJDBCDelegate.java:2666) ~[quartz-2.3.2.jar:?]

This customer uses MS SQL server to host their JasperReports® Server repository database.

Cause:

This customer had used js-upgrade-samedb script to run the upgrade process. According to TIBCO JasperReports® Server Upgrade Guide Software Release 7.5 document, the upgrade procedure using the js-upgrade-samedb shell script is for upgrading from 7.2 to 7.5 only. This script does not add the new SCHED_TIME column into QRTZ_FIRED_TRIGGERS table in JasperReports® Server 7.5 because JasperReports® Server 7.2 should have included this new column in its deployment. Instead, customers should use the js-upgrade-newdb shell script for their upgrading from 7.1 to 7.5.

Solution:

The customers in this situation do not need to redo their upgrade. They can simply run "upgrade-sqlserver-7.1.0-7.2.0-pro.sql" SQL script from their JasperReports® Server 7.5 deployment \buildomatic\install_resources\sql\sqlserver folder to add the column to your JasperReports® Server 7.5.0 repository DB. The SQL content is:

ALTER TABLE QRTZ_FIRED_TRIGGERS ADD SCHED_TIME BIGINT;
UPDATE QRTZ_FIRED_TRIGGERS SET SCHED_TIME = FIRED_TIME;
ALTER TABLE QRTZ_FIRED_TRIGGERS ALTER COLUMN SCHED_TIME BIGINT NOT NULL;

In addition, this user should wipe out the legacy dashboard from the 7.1 repository content since it is no longer supported by JasperReports® Server starting in version 7.2.0.

References:

  • TIBCO JasperReports® Server Upgrade Guide Software Release 7.5, CHAPTER 3 UPGRADING FROM 7.2 TO 7.5
  • TIBCO JasperReports® Server Upgrade Guide Software Release 7.5, CHAPTER 4 UPGRADING FROM 6.4 - 7.1 TO 7.5
  • The "upgrade-......-7.1.0-7.2.0-pro.sql" script for other databases can be located under JasperReports® Server 7.5 deployment \buildomatic\install_resources\sql\{database} folder.

TTC-20200706-1875273

Comments

Submitted by friederike.buchner on March 21, 2022 - 4:14am

Hello, what is the equivalent SQL statement for oracle databases?

Am I correct with my assumption, that I could fix the issue on an oracle DB with

alter table QRTZ_FIRED_TRIGGERS add SCHED_TIME NUMBER(19) NOT NULL

Thanks!

Feedback
randomness