Spring引导石英:jdbc表总是初始化的

我们在春季启动应用程序中使用石英。为了存储作业信息，我使用了jdbc存储。在application.properties文件中，我有以下内容：

spring.quartz.job-store-type=jdbc
spring.quartz.jdbc.initialize-schema=always

但每次重新启动后，表都会被初始化，所有存储的信息都会丢失。如果我将spring.quartz.jdbc.initialize-schema更改为never，则在创建表时会出现异常。

我不想在重新启动后删除石英表数据。你能帮我吗？

谨致问候，Stephan

好吧，根据文档：

默认情况下，使用Quartz库提供的标准脚本。这些脚本被删除现有表，每次重新启动时删除所有触发器。它也是可以通过设置spring.quartz.jdbc.schema属性。

https://docs.spring.io/spring-boot/docs/2.1.9.RELEASE/reference/html/boot-features-quartz.html

你能做什么：

修改quartz用于创建表的脚本，删除drop table语句并使用create table if not exists...：https://github.com/quartz-scheduler/quartz/tree/master/quartz-core/src/main/resources/org/quartz/impl/jdbcjobstore
将语句放入src/main/resources目录中某个位置的脚本中(假设您使用的是maven(
将spring.quartz.jdbc.schema设置为指向在#2 中创建的文件

由于您可以使用任何数据库，因此脚本将是特定于数据库的。

或

另一种选择是使用flyway/likibase，并使用上述脚本作为这些管理系统的更改(这是我个人会做的(

脚本-

例如，mysql在这里(您希望删除开头的drop表语句，并按照上面的步骤使用自定义init脚本(：

#
# Quartz seems to work best with the driver mm.mysql-2.0.7-bin.jar
#
# PLEASE consider using mysql with innodb tables to avoid locking issues
#
# In your Quartz properties file, you'll need to set
# org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
#
DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;
DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;
DROP TABLE IF EXISTS QRTZ_LOCKS;
DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_SIMPROP_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_JOB_DETAILS;
DROP TABLE IF EXISTS QRTZ_CALENDARS;

CREATE TABLE QRTZ_JOB_DETAILS
(
SCHED_NAME VARCHAR(120) NOT NULL,
JOB_NAME  VARCHAR(200) NOT NULL,
JOB_GROUP VARCHAR(200) NOT NULL,
DESCRIPTION VARCHAR(250) NULL,
JOB_CLASS_NAME   VARCHAR(250) NOT NULL,
IS_DURABLE VARCHAR(1) NOT NULL,
IS_NONCONCURRENT VARCHAR(1) NOT NULL,
IS_UPDATE_DATA VARCHAR(1) NOT NULL,
REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
JOB_DATA BLOB NULL,
PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
);
CREATE TABLE QRTZ_TRIGGERS
(
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
JOB_NAME  VARCHAR(200) NOT NULL,
JOB_GROUP VARCHAR(200) NOT NULL,
DESCRIPTION VARCHAR(250) NULL,
NEXT_FIRE_TIME BIGINT(13) NULL,
PREV_FIRE_TIME BIGINT(13) NULL,
PRIORITY INTEGER NULL,
TRIGGER_STATE VARCHAR(16) NOT NULL,
TRIGGER_TYPE VARCHAR(8) NOT NULL,
START_TIME BIGINT(13) NOT NULL,
END_TIME BIGINT(13) NULL,
CALENDAR_NAME VARCHAR(200) NULL,
MISFIRE_INSTR SMALLINT(2) NULL,
JOB_DATA BLOB NULL,
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
);
CREATE TABLE QRTZ_SIMPLE_TRIGGERS
(
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
REPEAT_COUNT BIGINT(7) NOT NULL,
REPEAT_INTERVAL BIGINT(12) NOT NULL,
TIMES_TRIGGERED BIGINT(10) NOT NULL,
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE QRTZ_CRON_TRIGGERS
(
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
CRON_EXPRESSION VARCHAR(200) NOT NULL,
TIME_ZONE_ID VARCHAR(80),
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE QRTZ_SIMPROP_TRIGGERS
(
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
STR_PROP_1 VARCHAR(512) NULL,
STR_PROP_2 VARCHAR(512) NULL,
STR_PROP_3 VARCHAR(512) NULL,
INT_PROP_1 INT NULL,
INT_PROP_2 INT NULL,
LONG_PROP_1 BIGINT NULL,
LONG_PROP_2 BIGINT NULL,
DEC_PROP_1 NUMERIC(13,4) NULL,
DEC_PROP_2 NUMERIC(13,4) NULL,
BOOL_PROP_1 VARCHAR(1) NULL,
BOOL_PROP_2 VARCHAR(1) NULL,
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE QRTZ_BLOB_TRIGGERS
(
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
BLOB_DATA BLOB NULL,
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE QRTZ_CALENDARS
(
SCHED_NAME VARCHAR(120) NOT NULL,
CALENDAR_NAME  VARCHAR(200) NOT NULL,
CALENDAR BLOB NOT NULL,
PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
);
CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS
(
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_GROUP  VARCHAR(200) NOT NULL,
PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
);
CREATE TABLE QRTZ_FIRED_TRIGGERS
(
SCHED_NAME VARCHAR(120) NOT NULL,
ENTRY_ID VARCHAR(95) NOT NULL,
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
INSTANCE_NAME VARCHAR(200) NOT NULL,
FIRED_TIME BIGINT(13) NOT NULL,
SCHED_TIME BIGINT(13) NOT NULL,
PRIORITY INTEGER NOT NULL,
STATE VARCHAR(16) NOT NULL,
JOB_NAME VARCHAR(200) NULL,
JOB_GROUP VARCHAR(200) NULL,
IS_NONCONCURRENT VARCHAR(1) NULL,
REQUESTS_RECOVERY VARCHAR(1) NULL,
PRIMARY KEY (SCHED_NAME,ENTRY_ID)
);
CREATE TABLE QRTZ_SCHEDULER_STATE
(
SCHED_NAME VARCHAR(120) NOT NULL,
INSTANCE_NAME VARCHAR(200) NOT NULL,
LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
CHECKIN_INTERVAL BIGINT(13) NOT NULL,
PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
);
CREATE TABLE QRTZ_LOCKS
(
SCHED_NAME VARCHAR(120) NOT NULL,
LOCK_NAME  VARCHAR(40) NOT NULL,
PRIMARY KEY (SCHED_NAME,LOCK_NAME)
);

commit;

我为Spring引导执行了以下步骤以使其完美运行。

我手动创建了名为"；石英"；。然后通过从中复制正确的脚本手动创建所有表https://github.com/quartz-scheduler/quartz/tree/master/quartz-core/src/main/resources/org/quartz/impl/jdbcjobstore

然后我在spring-boot属性文件中有以下属性：

spring.quartz.jdbc.initialize-schema=never
spring.quartz.properties.org.quartz.jobStore.tablePrefix=quartz.QRTZ_

我设置的所有其他属性如下：

#==============================================================
# QUARTZ Configuration
#==============================================================
spring.quartz.job-store-type=jdbc
spring.quartz.jdbc.initialize-schema=never
spring.quartz.properties.org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
spring.quartz.properties.org.quartz.scheduler.instanceId=AUTO
spring.quartz.properties.org.quartz.threadPool.class=org.quartz.simpl.SimpleThreadPool
spring.quartz.properties.org.quartz.threadPool.threadCount=25
spring.quartz.properties.org.quartz.threadPool.threadPriority=5
spring.quartz.properties.org.quartz.threadPool.threadsInheritContextClassLoaderOfInitializingThread=true
spring.quartz.properties.org.quartz.jobStore.misfireThreshold=60000
spring.quartz.properties.org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreTX
spring.quartz.properties.org.quartz.jobStore.useProperties=false
spring.quartz.properties.org.quartz.jobStore.tablePrefix=quartz.QRTZ_
spring.quartz.properties.org.quartz.jobStore.isClustered=true
spring.quartz.properties.org.quartz.jobStore.clusterCheckinInterval=5000

我也这么做了。然而，我使用了解决方案1(，因为我希望所有东西都能自动创建。我在资源quartz/schema.sql中创建了一个文件，并设置了属性spring.quartz.jdbc.schema: classpath:quartz/schema.sql。

作为参考，以下是我为PostgreSQL 9:编写的脚本


CREATE TABLE IF NOT EXISTS QRTZ_JOB_DETAILS
(
SCHED_NAME        VARCHAR(120) NOT NULL,
JOB_NAME          VARCHAR(200) NOT NULL,
JOB_GROUP         VARCHAR(200) NOT NULL,
DESCRIPTION       VARCHAR(250) NULL,
JOB_CLASS_NAME    VARCHAR(250) NOT NULL,
IS_DURABLE        BOOL         NOT NULL,
IS_NONCONCURRENT  BOOL         NOT NULL,
IS_UPDATE_DATA    BOOL         NOT NULL,
REQUESTS_RECOVERY BOOL         NOT NULL,
JOB_DATA          BYTEA        NULL,
PRIMARY KEY (SCHED_NAME, JOB_NAME, JOB_GROUP)
);
CREATE TABLE IF NOT EXISTS QRTZ_TRIGGERS
(
SCHED_NAME     VARCHAR(120) NOT NULL,
TRIGGER_NAME   VARCHAR(200) NOT NULL,
TRIGGER_GROUP  VARCHAR(200) NOT NULL,
JOB_NAME       VARCHAR(200) NOT NULL,
JOB_GROUP      VARCHAR(200) NOT NULL,
DESCRIPTION    VARCHAR(250) NULL,
NEXT_FIRE_TIME BIGINT       NULL,
PREV_FIRE_TIME BIGINT       NULL,
PRIORITY       INTEGER      NULL,
TRIGGER_STATE  VARCHAR(16)  NOT NULL,
TRIGGER_TYPE   VARCHAR(8)   NOT NULL,
START_TIME     BIGINT       NOT NULL,
END_TIME       BIGINT       NULL,
CALENDAR_NAME  VARCHAR(200) NULL,
MISFIRE_INSTR  SMALLINT     NULL,
JOB_DATA       BYTEA        NULL,
PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
FOREIGN KEY (SCHED_NAME, JOB_NAME, JOB_GROUP)
REFERENCES QRTZ_JOB_DETAILS (SCHED_NAME, JOB_NAME, JOB_GROUP)
);
CREATE TABLE IF NOT EXISTS QRTZ_SIMPLE_TRIGGERS
(
SCHED_NAME      VARCHAR(120) NOT NULL,
TRIGGER_NAME    VARCHAR(200) NOT NULL,
TRIGGER_GROUP   VARCHAR(200) NOT NULL,
REPEAT_COUNT    BIGINT       NOT NULL,
REPEAT_INTERVAL BIGINT       NOT NULL,
TIMES_TRIGGERED BIGINT       NOT NULL,
PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
);
CREATE TABLE IF NOT EXISTS QRTZ_CRON_TRIGGERS
(
SCHED_NAME      VARCHAR(120) NOT NULL,
TRIGGER_NAME    VARCHAR(200) NOT NULL,
TRIGGER_GROUP   VARCHAR(200) NOT NULL,
CRON_EXPRESSION VARCHAR(120) NOT NULL,
TIME_ZONE_ID    VARCHAR(80),
PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
);
CREATE TABLE IF NOT EXISTS QRTZ_SIMPROP_TRIGGERS
(
SCHED_NAME    VARCHAR(120)   NOT NULL,
TRIGGER_NAME  VARCHAR(200)   NOT NULL,
TRIGGER_GROUP VARCHAR(200)   NOT NULL,
STR_PROP_1    VARCHAR(512)   NULL,
STR_PROP_2    VARCHAR(512)   NULL,
STR_PROP_3    VARCHAR(512)   NULL,
INT_PROP_1    INT            NULL,
INT_PROP_2    INT            NULL,
LONG_PROP_1   BIGINT         NULL,
LONG_PROP_2   BIGINT         NULL,
DEC_PROP_1    NUMERIC(13, 4) NULL,
DEC_PROP_2    NUMERIC(13, 4) NULL,
BOOL_PROP_1   BOOL           NULL,
BOOL_PROP_2   BOOL           NULL,
PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
);
CREATE TABLE IF NOT EXISTS QRTZ_BLOB_TRIGGERS
(
SCHED_NAME    VARCHAR(120) NOT NULL,
TRIGGER_NAME  VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
BLOB_DATA     BYTEA        NULL,
PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
);
CREATE TABLE IF NOT EXISTS QRTZ_CALENDARS
(
SCHED_NAME    VARCHAR(120) NOT NULL,
CALENDAR_NAME VARCHAR(200) NOT NULL,
CALENDAR      BYTEA        NOT NULL,
PRIMARY KEY (SCHED_NAME, CALENDAR_NAME)
);

CREATE TABLE IF NOT EXISTS QRTZ_PAUSED_TRIGGER_GRPS
(
SCHED_NAME    VARCHAR(120) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
PRIMARY KEY (SCHED_NAME, TRIGGER_GROUP)
);
CREATE TABLE IF NOT EXISTS QRTZ_FIRED_TRIGGERS
(
SCHED_NAME        VARCHAR(120) NOT NULL,
ENTRY_ID          VARCHAR(95)  NOT NULL,
TRIGGER_NAME      VARCHAR(200) NOT NULL,
TRIGGER_GROUP     VARCHAR(200) NOT NULL,
INSTANCE_NAME     VARCHAR(200) NOT NULL,
FIRED_TIME        BIGINT       NOT NULL,
SCHED_TIME        BIGINT       NOT NULL,
PRIORITY          INTEGER      NOT NULL,
STATE             VARCHAR(16)  NOT NULL,
JOB_NAME          VARCHAR(200) NULL,
JOB_GROUP         VARCHAR(200) NULL,
IS_NONCONCURRENT  BOOL         NULL,
REQUESTS_RECOVERY BOOL         NULL,
PRIMARY KEY (SCHED_NAME, ENTRY_ID)
);
CREATE TABLE IF NOT EXISTS QRTZ_SCHEDULER_STATE
(
SCHED_NAME        VARCHAR(120) NOT NULL,
INSTANCE_NAME     VARCHAR(200) NOT NULL,
LAST_CHECKIN_TIME BIGINT       NOT NULL,
CHECKIN_INTERVAL  BIGINT       NOT NULL,
PRIMARY KEY (SCHED_NAME, INSTANCE_NAME)
);
CREATE TABLE IF NOT EXISTS QRTZ_LOCKS
(
SCHED_NAME VARCHAR(120) NOT NULL,
LOCK_NAME  VARCHAR(40)  NOT NULL,
PRIMARY KEY (SCHED_NAME, LOCK_NAME)
);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_J_REQ_RECOVERY
ON QRTZ_JOB_DETAILS (SCHED_NAME, REQUESTS_RECOVERY);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_J_GRP
ON QRTZ_JOB_DETAILS (SCHED_NAME, JOB_GROUP);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_J
ON QRTZ_TRIGGERS (SCHED_NAME, JOB_NAME, JOB_GROUP);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_JG
ON QRTZ_TRIGGERS (SCHED_NAME, JOB_GROUP);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_C
ON QRTZ_TRIGGERS (SCHED_NAME, CALENDAR_NAME);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_G
ON QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_GROUP);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_STATE
ON QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_STATE);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_N_STATE
ON QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_STATE);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_N_G_STATE
ON QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_GROUP, TRIGGER_STATE);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_NEXT_FIRE_TIME
ON QRTZ_TRIGGERS (SCHED_NAME, NEXT_FIRE_TIME);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_NFT_ST
ON QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_STATE, NEXT_FIRE_TIME);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_NFT_MISFIRE
ON QRTZ_TRIGGERS (SCHED_NAME, MISFIRE_INSTR, NEXT_FIRE_TIME);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_NFT_ST_MISFIRE
ON QRTZ_TRIGGERS (SCHED_NAME, MISFIRE_INSTR, NEXT_FIRE_TIME, TRIGGER_STATE);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_T_NFT_ST_MISFIRE_GRP
ON QRTZ_TRIGGERS (SCHED_NAME, MISFIRE_INSTR, NEXT_FIRE_TIME, TRIGGER_GROUP, TRIGGER_STATE);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_FT_TRIG_INST_NAME
ON QRTZ_FIRED_TRIGGERS (SCHED_NAME, INSTANCE_NAME);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_FT_INST_JOB_REQ_RCVRY
ON QRTZ_FIRED_TRIGGERS (SCHED_NAME, INSTANCE_NAME, REQUESTS_RECOVERY);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_FT_J_G
ON QRTZ_FIRED_TRIGGERS (SCHED_NAME, JOB_NAME, JOB_GROUP);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_FT_JG
ON QRTZ_FIRED_TRIGGERS (SCHED_NAME, JOB_GROUP);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_FT_T_G
ON QRTZ_FIRED_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);
CREATE INDEX IF NOT EXISTS IDX_QRTZ_FT_TG
ON QRTZ_FIRED_TRIGGERS (SCHED_NAME, TRIGGER_GROUP);

从application.properties&您的Quartz表数据不会在每次重新启动时被删除。spring.quartz.jdbc.initialize schema=始终

相关内容

最新更新

热门标签：