quartz driver delegate postgres throws exception

Chad Redman 10 hours ago
I'm getting an error with the new postgres library: org.postgresql.util.PSQLException: Bad value for type long : \x. Is that error familiar to anyone? It happens in the daemon when reading from grouper_QZ_TRIGGERS

Chris Hyzer 9 hours ago
ugh... guess we need to find a version that works?

Chris Hyzer 9 hours ago
i assume you dont have bad data in your schedules? and that it wasnt doing this with old version right? :slightly_smiling_face:

Carey Black 4 hours ago
Maybe this is related? https://shred.zone/cilla/page/299/string-lobs-on-postgresql-with-hibernate-36.html. ( I note it is about Hibernate 3.6 too. )
grouper_QZ_TRIGGERS has a column ‘job_data’ that in Mysql is a “longblob”.
Maybe that is the issue?
shred.zone
String LOBs on PostgreSQL with Hibernate 3.6
For String properties that may contain more than 255 characters, it is advised to add a @Lob annotat

Chris Hyzer 1 hour ago
might be related but quartz doesnt use hibernate

Chad Redman 1 hour ago
it's postgres-specific
org/postgresql/postgresql/42.2.19/postgresql-42.2.19.jar!/org/postgresql/jdbc/PgResultSet.class:381
public Blob getBlob(int i) throws SQLException

{ byte[] value = this.getRawValue(i); return value == null ? null : this.makeBlob(this.getLong(i)); }

Chris Hyzer 1 hour ago
that seems like a bug right?

Chad Redman 1 hour ago
the field is bytea, not oid, so it would seem so

Chris Hyzer 1 hour ago
https://github.com/pgjdbc/pgjdbc/issues/820

guaiguai
#820 error in PgResultSet#getBlob(int)
It seems org.postgresql.jdbc.PgResultSet#getBlob(int) is wrong in version group: 'org.postgresql', name: 'postgresql', version: '42.1.1'
IDE shows me its code as:
public Blob getBlob(int i) throws SQLException {
checkResultSet;
if (wasNullFlag) {
return null;
Show more
Comments
5
<https://github.com/pgjdbc/pgjdbc|pgjdbc/pgjdbc>pgjdbc/pgjdbc | May 10th, 2017 | Added by GitHub

Chad Redman 1 hour ago
the column is defined as bytea, maybe it needs to be oid?
commit f738246ec7a13a804f426be2813856763c048710
Author: Chris Hyzer <mchyzer@isc.upenn.edu>
Date: Wed Dec 16 16:28:04 2020 -0500
GRP-3058: add provisioning error code to tables
+CREATE TABLE grouper_QZ_JOB_DETAILS
+(
+ sched_name VARCHAR(120) NOT NULL,
+ job_name VARCHAR(200) NOT NULL,
+ job_group VARCHAR(200) NOT NULL,
+ description VARCHAR(250),
+ job_class_name VARCHAR(250) NOT NULL,
+ is_durable BOOLEAN NOT NULL,
+ is_nonconcurrent BOOLEAN NOT NULL,
+ is_update_data BOOLEAN NOT NULL,
+ requests_recovery BOOLEAN NOT NULL,
+ job_data BYTEA,
+ PRIMARY KEY (sched_name, job_name, job_group)
+);

Chad Redman 1 hour ago
It's quartz code that assumes it's a blob. Was it quartz that generated the ddl?

Chad Redman 1 hour ago
never mind, it's been there since 2.5.40. So the obvious change is postgres version (edited)

Chris Hyzer 43 minutes ago
do you have a full stack on that one?

Chad Redman 41 minutes ago
org.postgresql.jdbc.PgResultSet.getBlob(PgResultSet.java:424)
org.postgresql.jdbc.PgResultSet.getBlob(PgResultSet.java:414)
com.mchange.v2.c3p0.impl.NewProxyResultSet.getBlob(NewProxyResultSet.java:4709)
org.quartz.impl.jdbcjobstore.StdJDBCDelegate.getObjectFromBlob(StdJDBCDelegate.java:3190)
org.quartz.impl.jdbcjobstore.StdJDBCDelegate.selectTrigger(StdJDBCDelegate.java:1780)
org.quartz.impl.jdbcjobstore.StdJDBCDelegate.selectTriggersForJob(StdJDBCDelegate.java:1704)
org.quartz.impl.jdbcjobstore.JobStoreSupport.getTriggersForJob(JobStoreSupport.java:2187)
org.quartz.impl.jdbcjobstore.JobStoreSupport$29.execute(JobStoreSupport.java:2176)
org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3864)
org.quartz.impl.jdbcjobstore.JobStoreTX.executeInLock(JobStoreTX.java:93)
org.quartz.impl.jdbcjobstore.JobStoreSupport.executeWithoutLock(JobStoreSupport.java:3800)
org.quartz.impl.jdbcjobstore.JobStoreSupport.getTriggersForJob(JobStoreSupport.java:2173)
org.quartz.core.QuartzScheduler.getTriggersOfJob(QuartzScheduler.java:1451)
org.quartz.impl.StdScheduler.getTriggersOfJob(StdScheduler.java:461)
edu.internet2.middleware.grouper.grouperUi.beans.api.GuiDaemonJob.<init>(GuiDaemonJob.java:220)
edu.internet2.middleware.grouper.grouperUi.serviceLogic.UiV2Admin.daemonJobsHelper(UiV2Admin.java:409)
edu.internet2.middleware.grouper.grouperUi.serviceLogic.UiV2Admin.daemonJobs(UiV2Admin.java:214)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Chris Hyzer 39 minutes ago
should be this right?
public @Nullable Blob getBlob(int i) throws SQLException {
byte[] value = getRawValue;
if (value == null)

{ return null; }

int col = i - 1;
int oid = fields[col].getOID();
return makeBlob(oid);
}

Chris Hyzer 39 minutes ago
Why is it StdJDBCDelegate??????

Chris Hyzer 38 minutes ago
public static String convertUrlToQuartzDriverDelegateClassIfNeeded(String connectionUrl, String driverClassName) {
//default some of the stuff
if (StringUtils.isBlank(driverClassName)) {
if (GrouperDdlUtils.isHsql(connectionUrl))

{ driverClassName = "org.quartz.impl.jdbcjobstore.HSQLDBDelegate"; }

else if (GrouperDdlUtils.isMysql(connectionUrl))

{ driverClassName = "org.quartz.impl.jdbcjobstore.StdJDBCDelegate"; }

else if (GrouperDdlUtils.isOracle(connectionUrl))

{ driverClassName = "org.quartz.impl.jdbcjobstore.oracle.OracleDelegate"; }

else if (GrouperDdlUtils.isPostgres(connectionUrl))

{ driverClassName = "org.quartz.impl.jdbcjobstore.PostgreSQLDelegate"; }

else if (GrouperDdlUtils.isSQLServer(connectionUrl))

{ driverClassName = "org.quartz.impl.jdbcjobstore.MSSQLDelegate"; }

else {
//if this is blank we will figure it out later
if (!StringUtils.isBlank(connectionUrl))

{ String error = "Cannot determine the quartz driver class from database URL: " + connectionUrl; System.err.println(error); LOG.error(error); return null; }

}
}
return driverClassName;
}

Chris Hyzer 36 minutes ago
can you try adding this to grouper-loader.properties?
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Chris Hyzer 34 minutes ago
I dont see where this method is called: GrouperDdlUtils.convertUrlToQuartzDriverDelegateClassIfNeeded()

Chad Redman 33 minutes ago
good catch, that class does override getObjectFromBlob()

Chad Redman 29 minutes ago
yes that does fix it

Chris Hyzer 21 minutes ago
ok let me commit something in a minute, after i make a pull request to postgres :slightly_smiling_face:

Chad Redman 18 minutes ago
The stdJDBCDelegate could be because postgres is wrapped in c3p0

Chris Hyzer 14 minutes ago
worst untested pull request ever :slightly_smiling_face:
https://github.com/pgjdbc/pgjdbc/pull/2094

mchyzermchyzer
#2094 error in PgResultSet#getBlob(int) #820
All Submissions:
☐ Have you followed the guidelines in our Contributing document? No
☐ Have you checked to ensure there aren't other open <../../pulls|Pull Requests> for the same update/change? No
New Feature Submissions:
☐ Does your submission pass tests? Dont know
☐ Does ./gradlew autostyleCheck checkstyleAll pass ? Dont know
☐ Have you added your new test classes to an existing test suite in alphabetical order? NA
Changes to Existing Features:
☐ Does this break existing behaviour? If so please explain. Dont know
☐ Have you added an explanation of what your changes do and why you'd like us to include them? Your current code causes exception, dont know how it is possible to be correct. Convert… Show more
<https://github.com/pgjdbc/pgjdbc|pgjdbc/pgjdbc>pgjdbc/pgjdbc | Today at 10:47 AM | Added by GitHub