Pentaho BI Server 5 – Missing data in jobId quartzjobkey.error_0002

My Pentaho scheduler was showing no data when I looked at it in the browser, and when I checked the logs I got an error that was along the lines of:

Missing data in jobId quartzjobkey.error_0002

This seemed to be caused by a JOB_NAME in the quartz hsql database that stores the scheduled job metadata. Specifically, bad job name was:

admin PentahoSystemVersionCheck 1428946692234

The other jobs all had names like:

mike.baranski:GeneratedContentCleaner:1393966687322

Looked to me like the whitespace in the job name was causing problems. I did not need the version checker, so I just opted to delete the job and hope that my job listing came back. There are several steps to accomplish this.

First, the Pentaho server uses a JNDI context definition to access the Quartz database. This definition is in ./tomcat/webapps/pentaho/META-INF/context.xml, and the relevant bit is:

 <Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
                factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
                maxWait="10000" username="pentaho_user" password="********"
                driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"
                validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>

mike@pentaho:~/biserver-ce$ java -jar tomcat/lib/hsqldb-1.8.0.jar --inlineRc URL=jdbc:hsqldb:hsql://localhost/quartz,USER=pentaho_user
pentaho_user's password: password
JDBC Connection established to a HSQL Database Engine v. 1.8.0 database as 'PENTAHO_USER'.
SqlTool v. 1.55.                        (SqlFile processor v. 1.135)
Distribution is permitted under the terms of the HSQLDB license.
(c) 2004-2005 Blaine Simpson and the HSQLDB Development Group.
 
    \q    to Quit.
    \?    lists Special Commands.
    :?    lists Buffer/Editing commands.
    *?    lists PL commands (including alias commands).
 
SPECIAL Commands begin with '\' and execute when you hit ENTER.
BUFFER Commands begin with ':' and execute when you hit ENTER.
COMMENTS begin with '/*' and end with the very next '*/'.
PROCEDURAL LANGUAGE commands begin with '*' and end when you hit ENTER.
All other lines comprise SQL Statements.
  SQL Statements are terminated by either a blank line (which moves the
  statement into the buffer without executing) or a line ending with ';'
  (which executes the statement).
  SQL Statements may begin with '/PLVARNAME' and/or contain *{PLVARNAME}s.
 
sql>

Now we are able to run commands on the database. Do note that hsql does not commit automatically, so I had to run the following to make this go away. Note the first statement to make sure I get the item I want to delete (and only the one I want to delete) before I remove records:

SELECT * FROM QRTZ5_SIMPLE_TRIGGERS WHERE TRIGGER_NAME like 'admin%'
DELETE FROM QRTZ5_SIMPLE_TRIGGERS WHERE TRIGGER_NAME like 'admin%'
DELETE FROM QRTZ5_TRIGGERS WHERE TRIGGER_NAME like 'admin%'
DELETE from QRTZ5_JOB_DETAILS WHERE JOB_NAME like 'admin%'
COMMIT

SELECT * FROM QRTZ5_SIMPLE_TRIGGERS WHERE TRIGGER_NAME like 'admin%'
DELETE FROM QRTZ5_SIMPLE_TRIGGERS WHERE TRIGGER_NAME like 'admin%'
DELETE FROM QRTZ5_TRIGGERS WHERE TRIGGER_NAME like 'admin%'
DELETE from QRTZ5_JOB_DETAILS WHERE JOB_NAME like 'admin%'
COMMIT

Now the offending job is gone and the UI will load the jobs that are there.