CDH集群初始化oozie失败表结构不存在

文章目录

1. 背景
2. 初始化数据库
- 2.1 生成表结构
- 2.2 初始化数据库
3. CDH管理页面始化 oozie 服务

1. 背景

安装CDH 6.3.2 版本时初始化集群服务过程中出现oozie server启动失败的情况，第一次创建集群成功，第二次失败了，分析日志信息

SERVER[k8s-node1] E0103: Could not load service classes, Cannot create PoolableConnectionFactory (Table 'oozie.VALIDATE_CONN' doesn't exist)
org.apache.oozie.service.ServiceException: E0103: Could not load service classes, Cannot create PoolableConnectionFactory (Table 'oozie.VALIDATE_CONN' doesn't exist)
	at org.apache.oozie.service.Services.loadServices(Services.java:309)
	at org.apache.oozie.service.Services.init(Services.java:212)
	at org.apache.oozie.server.guice.ServicesProvider.get(ServicesProvider.java:31)
	at org.apache.oozie.server.guice.ServicesProvider.get(ServicesProvider.java:25)
	at com.google.inject.internal.BoundProviderFactory.get(BoundProviderFactory.java:55)
	at com.google.inject.internal.ProviderToInternalFactoryAdapter$1.call(ProviderToInternalFactoryAdapter.java:46)
	at com.google.inject.internal.InjectorImpl.callInContext(InjectorImpl.java:1031)
	at com.google.inject.internal.ProviderToInternalFactoryAdapter.get(ProviderToInternalFactoryAdapter.java:40)
	at com.google.inject.Scopes$1$1.get(Scopes.java:65)
	at com.google.inject.internal.InternalFactoryToProviderAdapter.get(InternalFactoryToProviderAdapter.java:40)
	at com.google.inject.internal.SingleParameterInjector.inject(SingleParameterInjector.java:38)
	at com.google.inject.internal.SingleParameterInjector.getAll(SingleParameterInjector.java:62)
	at com.google.inject.internal.ConstructorInjector.construct(ConstructorInjector.java:84)
	at com.google.inject.internal.ConstructorBindingImpl$Factory.get(ConstructorBindingImpl.java:254)
	at com.google.inject.internal.BoundProviderFactory.get(BoundProviderFactory.java:53)
	at com.google.inject.internal.ProviderToInternalFactoryAdapter$1.call(ProviderToInternalFactoryAdapter.java:46)
	at com.google.inject.internal.InjectorImpl.callInContext(InjectorImpl.java:1031)
	at com.google.inject.internal.ProviderToInternalFactoryAdapter.get(ProviderToInternalFactoryAdapter.java:40)
	at com.google.inject.Scopes$1$1.get(Scopes.java:65)
	at com.google.inject.internal.InternalFactoryToProviderAdapter.get(InternalFactoryToProviderAdapter.java:40)
	at com.google.inject.internal.SingleParameterInjector.inject(SingleParameterInjector.java:38)
	at com.google.inject.internal.SingleParameterInjector.getAll(SingleParameterInjector.java:62)
	at com.google.inject.internal.ConstructorInjector.construct(ConstructorInjector.java:84)
	at com.google.inject.internal.ConstructorBindingImpl$Factory.get(ConstructorBindingImpl.java:254)
	at com.google.inject.internal.InjectorImpl$4$1.call(InjectorImpl.java:978)
	at com.google.inject.internal.InjectorImpl.callInContext(InjectorImpl.java:1024)
	at com.google.inject.internal.InjectorImpl$4.get(InjectorImpl.java:974)
	at com.google.inject.internal.InjectorImpl.getInstance(InjectorImpl.java:1013)
	at org.apache.oozie.server.EmbeddedOozieServer.main(EmbeddedOozieServer.java:255)
Caused by: <openjpa-2.4.2-r422266:1777108 fatal general error> org.apache.openjpa.persistence.PersistenceException: Cannot create PoolableConnectionFactory (Table 'oozie.VALIDATE_CONN' doesn't exist)
	at org.apache.openjpa.jdbc.sql.DBDictionaryFactory.newDBDictionary(DBDictionaryFactory.java:108)
	at org.apache.openjpa.jdbc.conf.JDBCConfigurationImpl.getDBDictionaryInstance(JDBCConfigurationImpl.java:604)
	at org.apache.openjpa.jdbc.meta.MappingRepository.endConfiguration(MappingRepository.java:1520)
	at org.apache.openjpa.lib.conf.Configurations.configureInstance(Configurations.java:535)
	at org.apache.openjpa.lib.conf.Configurations.configureInstance(Configurations.java:460)
	at org.apache.openjpa.lib.conf.PluginValue.instantiate(PluginValue.java:121)
	at org.apache.openjpa.conf.MetaDataRepositoryValue.instantiate(MetaDataRepositoryValue.java:68)
	at org.apache.openjpa.lib.conf.ObjectValue.instantiate(ObjectValue.java:83)
	at org.apache.openjpa.conf.OpenJPAConfigurationImpl.newMetaDataRepositoryInstance(OpenJPAConfigurationImpl.java:973)
	at org.apache.openjpa.conf.OpenJPAConfigurationImpl.getMetaDataRepositoryInstance(OpenJPAConfigurationImpl.java:964)
	at org.apache.openjpa.kernel.AbstractBrokerFactory.makeReadOnly(AbstractBrokerFactory.java:642)
	at org.apache.openjpa.kernel.AbstractBrokerFactory.newBroker(AbstractBrokerFactory.java:202)
	at org.apache.openjpa.kernel.DelegatingBrokerFactory.newBroker(DelegatingBrokerFactory.java:154)
	at org.apache.openjpa.persistence.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:226)
	at org.apache.openjpa.persistence.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:153)
	at org.apache.openjpa.persistence.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:59)
	at org.apache.oozie.service.JPAService.getEntityManager(JPAService.java:633)
	at org.apache.oozie.service.JPAService.init(JPAService.java:232)
	at org.apache.oozie.service.Services.setServiceInternal(Services.java:386)
	at org.apache.oozie.service.Services.setService(Services.java:372)
	at org.apache.oozie.service.Services.loadServices(Services.java:304)
	... 28 more
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Table 'oozie.VALIDATE_CONN' doesn't exist)
	at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
	at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
	at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
	at org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:127)
	at org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:92)
	at org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
	at org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:321)
	at org.apache.openjpa.jdbc.sql.DBDictionaryFactory.newDBDictionary(DBDictionaryFactory.java:92)
	... 48 more
Caused by: java.sql.SQLSyntaxErrorException: Table 'oozie.VALIDATE_CONN' doesn't exist
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1936)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
	at org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:658)
	at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1558)
	at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:15

分析日志内容，发现因为表不存在导致oozie服务启动失败。于是准备手工创建数据库表结构，初始化数据库，然后再来启动oozie服务。文章来源地址https://uudwc.com/A/XkBGy

2. 初始化数据库

2.1 生成表结构

生成 oozie.sql 数据库初始化文件

cd /opt/cloudera/parcels/CDH-6.3.2-1.cdh6.3.2.p0.1605554/lib/oozie
mkdir conf
cp /opt/cloudera/parcels/CDH-6.3.2-1.cdh6.3.2.p0.1605554/etc/oozie/conf.dist/oozie-site.xml ./conf/
bin/ooziedb.sh create -sqlfile oozie.sql -run

修改 oozie.sql 文件，默认生成的是 Oracle 数据库语法的SQL，如果目标数据库是MySQL，需要做一些语法替换。

BLOB 替换成 LONGTEXT
CLOB 替换成 LONGTEXT
GENERATED BY DEFAULT AS IDENTITY 替换成 AUTO_INCREMENT

CREATE TABLE BUNDLE_ACTIONS
    (bundle_action_id VARCHAR(255) NOT NULL, bundle_id VARCHAR(255), coord_id VARCHAR(255), coord_name VARCHAR(255), critical INTEGER, last_modified_time TIMESTAMP, pending INTEGER, status VARCHAR(255), PRIMARY KEY (bundle_action_id));
CREATE TABLE BUNDLE_JOBS
    (id VARCHAR(255) NOT NULL, app_name VARCHAR(255), app_path VARCHAR(255), conf BLOB, created_time TIMESTAMP, end_time TIMESTAMP, external_id VARCHAR(255), group_name VARCHAR(255), job_xml BLOB, kickoff_time TIMESTAMP, last_modified_time TIMESTAMP, orig_job_xml BLOB, pause_time TIMESTAMP, pending INTEGER, start_time TIMESTAMP, status VARCHAR(255), suspended_time TIMESTAMP, time_out INTEGER, time_unit VARCHAR(255), user_name VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE COORD_ACTIONS
    (id VARCHAR(255) NOT NULL, action_number INTEGER, action_xml BLOB, console_url VARCHAR(255), created_conf BLOB, created_time TIMESTAMP, error_code VARCHAR(255), error_message VARCHAR(255), external_id VARCHAR(255), external_status VARCHAR(255), job_id VARCHAR(255), last_modified_time TIMESTAMP, missing_dependencies BLOB, nominal_time TIMESTAMP, pending INTEGER, push_missing_dependencies BLOB, rerun_time TIMESTAMP, run_conf BLOB, sla_xml BLOB, status VARCHAR(255), time_out INTEGER, tracker_uri VARCHAR(255), job_type VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE COORD_JOBS
    (id VARCHAR(255) NOT NULL, app_name VARCHAR(255), app_namespace VARCHAR(255), app_path VARCHAR(255), bundle_id VARCHAR(255), concurrency INTEGER, conf BLOB, created_time TIMESTAMP, done_materialization INTEGER, end_time TIMESTAMP, execution VARCHAR(255), external_id VARCHAR(255), frequency VARCHAR(255), group_name VARCHAR(255), job_xml BLOB, last_action_number INTEGER, last_action TIMESTAMP, last_modified_time TIMESTAMP, mat_throttling INTEGER, next_matd_time TIMESTAMP, orig_job_xml BLOB, pause_time TIMESTAMP, pending INTEGER, sla_xml BLOB, start_time TIMESTAMP, status VARCHAR(255), suspended_time TIMESTAMP, time_out INTEGER, time_unit VARCHAR(255), time_zone VARCHAR(255), user_name VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE OPENJPA_SEQUENCE_TABLE (ID SMALLINT NOT NULL, SEQUENCE_VALUE BIGINT, PRIMARY KEY (ID));
CREATE TABLE SLA_EVENTS
    (event_id BIGINT NOT NULL AUTO_INCREMENT, alert_contact VARCHAR(255), alert_frequency VARCHAR(255), alert_percentage VARCHAR(255), app_name VARCHAR(255), dev_contact VARCHAR(255), group_name VARCHAR(255), job_data longtext, notification_msg longtext, parent_client_id VARCHAR(255), parent_sla_id VARCHAR(255), qa_contact VARCHAR(255), se_contact VARCHAR(255), sla_id VARCHAR(255), upstream_apps longtext, user_name VARCHAR(255), bean_type VARCHAR(31), app_type VARCHAR(255), event_type VARCHAR(255), expected_end TIMESTAMP, expected_start TIMESTAMP, job_status VARCHAR(255), status_timestamp TIMESTAMP, PRIMARY KEY (event_id));
CREATE TABLE SLA_REGISTRATION
    (job_id VARCHAR(255) NOT NULL, app_name VARCHAR(255), app_type VARCHAR(255), created_time TIMESTAMP, expected_duration BIGINT, expected_end TIMESTAMP, expected_start TIMESTAMP, job_data VARCHAR(255), nominal_time TIMESTAMP, notification_msg VARCHAR(255), parent_id VARCHAR(255), sla_config VARCHAR(255), upstream_apps VARCHAR(255), user_name VARCHAR(255), PRIMARY KEY (job_id));
CREATE TABLE SLA_SUMMARY
    (job_id VARCHAR(255) NOT NULL, actual_duration BIGINT, actual_end TIMESTAMP, actual_start TIMESTAMP, app_name VARCHAR(255), app_type VARCHAR(255), created_time TIMESTAMP, event_processed SMALLINT, event_status VARCHAR(255), expected_duration BIGINT, expected_end TIMESTAMP, expected_start TIMESTAMP, job_status VARCHAR(255), last_modified TIMESTAMP, nominal_time TIMESTAMP, parent_id VARCHAR(255), sla_status VARCHAR(255), user_name VARCHAR(255), PRIMARY KEY (job_id));
CREATE TABLE VALIDATE_CONN
    (id BIGINT NOT NULL,
    dummy INTEGER, PRIMARY KEY (id));
CREATE TABLE WF_ACTIONS
    (id VARCHAR(255) NOT NULL, conf longtext, console_url VARCHAR(255), created_time TIMESTAMP, cred VARCHAR(255), data longtext, end_time TIMESTAMP, error_code VARCHAR(255), error_message VARCHAR(500), execution_path VARCHAR(1024), external_child_ids longtext, external_id VARCHAR(255), external_status VARCHAR(255), last_check_time TIMESTAMP, log_token VARCHAR(255), name VARCHAR(255), pending INTEGER, pending_age TIMESTAMP, retries INTEGER, signal_value VARCHAR(255), sla_xml longtext, start_time TIMESTAMP, stats longtext, status VARCHAR(255), tracker_uri VARCHAR(255), transition VARCHAR(255), type VARCHAR(255), user_retry_count INTEGER, user_retry_interval INTEGER, user_retry_max INTEGER, wf_id VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE WF_JOBS
    (id VARCHAR(255) NOT NULL, app_name VARCHAR(255), app_path VARCHAR(255), conf longtext, created_time TIMESTAMP, end_time TIMESTAMP, external_id VARCHAR(255), group_name VARCHAR(255), last_modified_time TIMESTAMP, log_token VARCHAR(255), parent_id VARCHAR(255), proto_action_conf longtext, run INTEGER, sla_xml longtext, start_time TIMESTAMP, status VARCHAR(255), user_name VARCHAR(255), wf_instance longtext, PRIMARY KEY (id));
CREATE INDEX I_BNDLTNS_BUNDLE_ID ON BUNDLE_ACTIONS (bundle_id);
CREATE INDEX I_BNDLJBS_CREATED_TIME ON BUNDLE_JOBS (created_time);
CREATE INDEX I_BNDLJBS_END_TIME ON BUNDLE_JOBS (end_time);
CREATE INDEX I_BNDLJBS_LAST_MODIFIED_TIME ON BUNDLE_JOBS (last_modified_time);
CREATE INDEX I_BNDLJBS_STATUS ON BUNDLE_JOBS (status);
CREATE INDEX I_BNDLJBS_SUSPENDED_TIME ON BUNDLE_JOBS (suspended_time);
CREATE INDEX I_BNDLJBS_USER_NAME ON BUNDLE_JOBS (user_name);
CREATE INDEX I_CRD_TNS_CREATED_TIME ON COORD_ACTIONS (created_time);
CREATE INDEX I_CRD_TNS_EXTERNAL_ID ON COORD_ACTIONS (external_id);
CREATE INDEX I_CRD_TNS_JOB_ID ON COORD_ACTIONS (job_id);
CREATE INDEX I_CRD_TNS_LAST_MODIFIED_TIME ON COORD_ACTIONS (last_modified_time);
CREATE INDEX I_CRD_TNS_NOMINAL_TIME ON COORD_ACTIONS (nominal_time);
CREATE INDEX I_CRD_TNS_RERUN_TIME ON COORD_ACTIONS (rerun_time);
CREATE INDEX I_CRD_TNS_STATUS ON COORD_ACTIONS (status);
CREATE INDEX I_CRD_JBS_BUNDLE_ID ON COORD_JOBS (bundle_id);
CREATE INDEX I_CRD_JBS_CREATED_TIME ON COORD_JOBS (created_time);
CREATE INDEX I_CRD_JBS_END_TIME ON COORD_JOBS (end_time);
CREATE INDEX I_CRD_JBS_LAST_MODIFIED_TIME ON COORD_JOBS (last_modified_time);
CREATE INDEX I_CRD_JBS_NEXT_MATD_TIME ON COORD_JOBS (next_matd_time);
CREATE INDEX I_CRD_JBS_STATUS ON COORD_JOBS (status);
CREATE INDEX I_CRD_JBS_SUSPENDED_TIME ON COORD_JOBS (suspended_time);
CREATE INDEX I_CRD_JBS_USER_NAME ON COORD_JOBS (user_name);
CREATE INDEX I_SL_VNTS_DTYPE ON SLA_EVENTS (bean_type);
CREATE INDEX I_SL_RRTN_NOMINAL_TIME ON SLA_REGISTRATION (nominal_time);
CREATE INDEX I_SL_SMRY_PARENT_ID ON SLA_SUMMARY (parent_id);
CREATE INDEX I_WF_CTNS_PENDING_AGE ON WF_ACTIONS (pending_age);
CREATE INDEX I_WF_CTNS_STATUS ON WF_ACTIONS (status);
CREATE INDEX I_WF_CTNS_WF_ID ON WF_ACTIONS (wf_id);
CREATE INDEX I_WF_JOBS_CREATED_TIME ON WF_JOBS (created_time);
CREATE INDEX I_WF_JOBS_END_TIME ON WF_JOBS (end_time);
CREATE INDEX I_WF_JOBS_EXTERNAL_ID ON WF_JOBS (external_id);
CREATE INDEX I_WF_JOBS_LAST_MODIFIED_TIME ON WF_JOBS (last_modified_time);
CREATE INDEX I_WF_JOBS_PARENT_ID ON WF_JOBS (parent_id);
CREATE INDEX I_WF_JOBS_STATUS ON WF_JOBS (status);
CREATE INDEX I_WF_JOBS_USER_NAME ON WF_JOBS (user_name);

CREATE INDEX I_WF_JOBS_STATUS_CREATED_TIME ON WF_JOBS (status, created_time);
CREATE INDEX I_COORD_ACTIONS_JOB_ID_STATUS ON COORD_ACTIONS (job_id, status);
CREATE INDEX I_COORD_JOBS_STATUS_CREATED_TIME ON COORD_JOBS (status, created_time);
CREATE INDEX I_COORD_JOBS_STATUS_LAST_MODIFIED_TIME ON COORD_JOBS (status, last_modified_time);
CREATE INDEX I_COORD_JOBS_PENDING_DONE_MATERIALIZATION_LAST_MODIFIED_TIME ON COORD_JOBS (pending, done_materialization, last_modified_time);
CREATE INDEX I_COORD_JOBS_PENDING_LAST_MODIFIED_TIME ON COORD_JOBS (pending, last_modified_time);
CREATE INDEX I_BUNLDE_JOBS_STATUS_CREATED_TIME ON BUNDLE_JOBS (status, created_time);
CREATE INDEX I_BUNLDE_JOBS_STATUS_LAST_MODIFIED_TIME ON BUNDLE_JOBS (status, last_modified_time);
CREATE INDEX I_BUNLDE_ACTIONS_PENDING_LAST_MODIFIED_TIME ON BUNDLE_ACTIONS (pending, last_modified_time);

create table OOZIE_SYS (name varchar(100), data varchar(100));
insert into OOZIE_SYS (name, data) values ('db.version', '4');
insert into OOZIE_SYS (name, data) values ('oozie.version', '5.1.0-cdh6.3.2');

2.2 初始化数据库

mysql -h <数据库IP>  -uroot -p  oozie < ./oozie.sql

# 如果还未创建 oozie 数据库，请先创建 oozie 数据库
# create database oozie default character set utf8 collate utf8_general_ci;

3. CDH管理页面始化 oozie 服务

找到集群中的Oozie服务
启动完成后的效果