XXL-JOB 适配 GAUSSDB, 报 ERROR: column “job_data“ is of type blob but expression is of type bytea

原创已于 2025-02-26 15:40:59 修改 · 478 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#gaussdb #java

于 2025-02-25 14:58:15 首次发布

调用任务时抛异常：

xxl-job-admin [com.xxl.job.admin.service.impl.XxlJobServiceImpl]-[http-nio-8080-exec-10]-[triggerJob]-[290]-[ERROR] Couldn't store trigger 'DEFAULT.MT_63hwu012jht6' for '35.546' job:[*******] ERROR: column "job_data" is of type blob but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.
  Position: 323
  Where: referenced column: job_data
org.quartz.JobPersistenceException: Couldn't store trigger 'DEFAULT.MT_63hwu012jht6' for '35.546' job:[**********] ERROR: column "job_data" is of type blob but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.
  Position: 323
  Where: referenced column: job_data [See nested exception: com.huawei.gaussdb.jdbc.util.PSQLException: [**********] ERROR: column "job_data" is of type blob but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.
  Position: 323
  Where: referenced column: job_data]
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeTrigger(JobStoreSupport.java:1228)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport$4.executeVoid(JobStoreSupport.java:1164)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport$VoidTransactionCallback.execute(JobStoreSupport.java:3765)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport$VoidTransactionCallback.execute(JobStoreSupport.java:3763)
        at org.quartz.impl.jdbcjobstore.JobStoreCMT.executeInLock(JobStoreCMT.java:245)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeTrigger(JobStoreSupport.java:1160)
        at org.quartz.core.QuartzScheduler.triggerJob(QuartzScheduler.java:1156)
        at org.quartz.impl.StdScheduler.triggerJob(StdScheduler.java:341)
        at com.xxl.job.admin.core.schedule.XxlJobDynamicScheduler.triggerJob(XxlJobDynamicScheduler.java:358)
        at com.xxl.job.admin.service.impl.XxlJobServiceImpl.triggerJob(XxlJobServiceImpl.java:287)
        at com.xxl.job.admin.controller.JobInfoController.triggerJob(JobInfoController.java:102)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:515)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:583)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:212)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:156)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:181)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:156)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:181)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:156)
        at com.xxl.job.admin.filter.xss.XSSFilter.doFilter(XSSFilter.java:36)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:181)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:156)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:679)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:617)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:934)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1690)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
        at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
        at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
        at java.lang.Thread.run(Thread.java:748)
Caused by: com.huawei.gaussdb.jdbc.util.PSQLException: [*******] ERROR: column "job_data" is of type blob but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.
  Position: 323
  Where: referenced column: job_data
        at com.huawei.gaussdb.jdbc.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:3315)
        at com.huawei.gaussdb.jdbc.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:3002)
        at com.huawei.gaussdb.jdbc.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:446)
        at com.huawei.gaussdb.jdbc.jdbc.PgStatement.runQueryExecutor(PgStatement.java:657)
        at com.huawei.gaussdb.jdbc.jdbc.PgStatement.executeInternal(PgStatement.java:613)
        at com.huawei.gaussdb.jdbc.jdbc.PgStatement.execute(PgStatement.java:445)
        at com.huawei.gaussdb.jdbc.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:165)
        at com.huawei.gaussdb.jdbc.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:127)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:384)
        at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.insertTrigger(StdJDBCDelegate.java:1093)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeTrigger(JobStoreSupport.java:1225)
        ... 56 more

Quartz 默认使用 org.quartz.impl.jdbcjobstore.StdJDBCDelegate 来处理 SQL 语句，报错的是blob

字段的设置方式

    public int insertTrigger(Connection conn, OperableTrigger trigger, String state,
            JobDetail jobDetail) throws SQLException, IOException {

        ByteArrayOutputStream baos = null;
        if(trigger.getJobDataMap().size() > 0) {
            baos = serializeJobData(trigger.getJobDataMap());
        }
        
        PreparedStatement ps = null;

        int insertResult = 0;

        try {
            ps = conn.prepareStatement(rtp(INSERT_TRIGGER));
            ps.setString(1, trigger.getKey().getName());
            ps.setString(2, trigger.getKey().getGroup());
            ps.setString(3, trigger.getJobKey().getName());
            ps.setString(4, trigger.getJobKey().getGroup());
            ps.setString(5, trigger.getDescription());
            if(trigger.getNextFireTime() != null)
                ps.setBigDecimal(6, new BigDecimal(String.valueOf(trigger
                        .getNextFireTime().getTime())));
            else
                ps.setBigDecimal(6, null);
            long prevFireTime = -1;
            if (trigger.getPreviousFireTime() != null) {
                prevFireTime = trigger.getPreviousFireTime().getTime();
            }
            ps.setBigDecimal(7, new BigDecimal(String.valueOf(prevFireTime)));
            ps.setString(8, state);
            
            TriggerPersistenceDelegate tDel = findTriggerPersistenceDelegate(trigger);
            
            String type = TTYPE_BLOB;
            if(tDel != null)
                type = tDel.getHandledTriggerTypeDiscriminator();
            ps.setString(9, type);
            
            ps.setBigDecimal(10, new BigDecimal(String.valueOf(trigger
                    .getStartTime().getTime())));
            long endTime = 0;
            if (trigger.getEndTime() != null) {
                endTime = trigger.getEndTime().getTime();
            }
            ps.setBigDecimal(11, new BigDecimal(String.valueOf(endTime)));
            ps.setString(12, trigger.getCalendarName());
            ps.setInt(13, trigger.getMisfireInstruction());
            setBytes(ps, 14, baos);
            ps.setInt(15, trigger.getPriority());
            
            insertResult = ps.executeUpdate();
            
            if(tDel == null)
                insertBlobTrigger(conn, trigger);
            else
                tDel.insertExtendedTriggerProperties(conn, trigger, state, jobDetail);
            
        } finally {
            closeStatement(ps);
        }

        return insertResult;
    }

就是这一句

 setBytes(ps, 14, baos);

处理方式和 Oracle 的类似，我们就从 OracleDelegate 继承个类来实现

package org.quartz.impl.jdbcjobstore.oracle.gaussdb;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.quartz.impl.jdbcjobstore.oracle.OracleDelegate;

public class GaussdbOracleDelegate extends OracleDelegate {

	@Override
	protected Object getObjectFromBlob(ResultSet rs, String colName)
			throws ClassNotFoundException, IOException, SQLException {
		Object obj = null;
		final InputStream binaryInput = rs.getBinaryStream(colName);
		if (binaryInput != null) {
			// 将二进制流转换为字节数组
			// byte[] bytes = binaryStream.readAllBytes(); // jdk9
			final byte[] bytes = toByteArray(binaryInput); // jdk8

			if (bytes.length < 1) {
				return null;
			}

			// 创建 ByteArrayInputStream
			final ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
			final ObjectInputStream in = new ObjectInputStream(byteArrayInputStream);
			try {
				obj = in.readObject();
			} finally {
				in.close();
			}
		}

		return obj;
	}

	@Override
	protected Blob writeDataToBlob(ResultSet rs, int column, byte[] data) throws SQLException {
		final Blob blob = rs.getBlob(column);

		if (blob == null) {
			throw new SQLException("Driver's Blob representation is null!");
		}
//		logger.info(blob.getClass().getName());

		if (blob instanceof com.huawei.gaussdb.jdbc.core.types.PGBlob) { // is it an gaussdb blob?
			((com.huawei.gaussdb.jdbc.core.types.PGBlob) blob).setBytes(1, data);
			return blob;
		} else {
			throw new SQLException(
					"Driver's Blob representation is of an unsupported type: " + blob.getClass().getName());
		}
	}

	/**
	 * 将 InputStream 转换为字节数组.
	 *
	 * @param inputStream the input stream
	 * @return the byte[]
	 * @throws IOException Signals that an I/O exception has occurred.
	 */
	private static byte[] toByteArray(InputStream inputStream) throws IOException {
		final ByteArrayOutputStream buffer = new ByteArrayOutputStream();
		int nRead;
		final byte[] data = new byte[1024];
		while ((nRead = inputStream.read(data, 0, data.length)) != -1) {
			buffer.write(data, 0, nRead);
		}
		buffer.flush();
		return buffer.toByteArray();
	}
}

修改 quartz.properties，增加配置项

org.quartz.jobStore.driverDelegateClass: org.quartz.impl.jdbcjobstore.oracle.gaussdb.GaussdbOracleDelegate