1.升级背景
因项目需要使用数据质量模块功能，可以为数仓提供良好的数据质量监控功能。故要对已有2.0版本升级到3.0版本以上，此次选择测试了3.0.1 和 3.1.1 两个版本，对进行同数据等任务调度暂停等操作测试，最后选择3.0.1 版本
原因：
3.1.1 在测试sql任务时 ，同时启动上百sql 任务时，会出现sql 任务报错，导致大量任务无法正常运行，询问社区大佬，这是DS本身bug导致，虽然此现象在3.0.1也有出现，不过出现几率较小。
DS3.0.1以上版本zookeeper的依赖版本进行了更新，查看驱动版本是3.8版本。我们生产不打算升级zk,故选择使用3.0.1版本。
此版本测试还是比较稳定的，功能比较完善，满足我们使用需求。
此次升级已经验证可行性，已在生产环境验证上线，对已有的问题，并给出了合理的解决方便，故写此篇文章，供各位同学参考。
2.升级的方案
选定方案：
采用数据库表同步的方式进行任务迁移，前期3.0 版本 和 2.0 版本同时运行，任务再验证没问题后，再逐步停止2.0版本。
原因：直接使用官网提供的升级脚本，无法正常运行，有较多问题，目前我们改造后，升级的数据库信息没问题，运行时数据信息有损坏，导致较多问题，所以为安全稳定，不直接使用官网提方案。
3.升级准备
1.首先对已有数据库进行备份，此项非常重要,
备份原始DS库：
mysqldump -h ip -P 3306-u 用户 -p 密码 数据库名 >/opt/new_dolphinscheduler.sql
恢复到新库：
mysql -u 用户 –p 密码 数据库名 < 备份文件.sql
2.对已有备份表进行表结构变更
####此脚本是官网脚本改的，添加了字段/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/SET sql_mode=(SELECTREPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));-- uc_dolphin_T_t_ds_alert_R_signdropPROCEDUREifEXISTS uc_dolphin_T_t_ds_alert_R_sign;delimiter d//CREATEPROCEDURE uc_dolphin_T_t_ds_alert_R_sign()BEGINIFNOTEXISTS(SELECT1FROM information_schema.COLUMNSWHERE TABLE_NAME='t_ds_alert'AND TABLE_SCHEMA=(SELECTDATABASE())AND COLUMN_NAME='sign')THENALTERTABLE`t_ds_alert`ADDCOLUMN`sign`char(40)NOTNULLDEFAULT''COMMENT'sign=sha1(content)'after`id`;ALTERTABLE`t_ds_alert`ADDINDEX`idx_sign`(`sign`)USINGBTREE;ENDIF;END;
d//delimiter;CALL uc_dolphin_T_t_ds_alert_R_sign;DROPPROCEDURE uc_dolphin_T_t_ds_alert_R_sign;-- add unique key to t_ds_relation_project_userdropPROCEDUREifEXISTS add_t_ds_relation_project_user_uk_uniq_uid_pid;delimiter d//CREATEPROCEDURE add_t_ds_relation_project_user_uk_uniq_uid_pid()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_relation_project_user'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='uniq_uid_pid')THENALTERTABLE t_ds_relation_project_user ADDUNIQUEKEY uniq_uid_pid(user_id, project_id);ENDIF;END;
d//delimiter;# CALL add_t_ds_relation_project_user_uk_uniq_uid_pid;DROPPROCEDURE add_t_ds_relation_project_user_uk_uniq_uid_pid;-- drop t_ds_relation_project_user key user_id_indexdropPROCEDUREifEXISTS drop_t_ds_relation_project_user_key_user_id_index;delimiter d//CREATEPROCEDURE drop_t_ds_relation_project_user_key_user_id_index()BEGINIFEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_relation_project_user'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='user_id_index')THENALTERTABLE`t_ds_relation_project_user`DROPKEY`user_id_index`;ENDIF;END;
d//delimiter;CALL drop_t_ds_relation_project_user_key_user_id_index;DROPPROCEDURE drop_t_ds_relation_project_user_key_user_id_index;-- add unique key to t_ds_projectdropPROCEDUREifEXISTS add_t_ds_project_uk_unique_name;delimiter d//CREATEPROCEDURE add_t_ds_project_uk_unique_name()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_project'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='unique_name')THENALTERTABLE t_ds_project ADDUNIQUEKEY unique_name(name);ENDIF;END;
d//delimiter;CALL add_t_ds_project_uk_unique_name;DROPPROCEDURE add_t_ds_project_uk_unique_name;dropPROCEDUREifEXISTS add_t_ds_project_uk_unique_code;delimiter d//CREATEPROCEDURE add_t_ds_project_uk_unique_code()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_project'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='unique_code')THENALTERTABLE t_ds_project ADDUNIQUEKEY unique_code(code);ENDIF;END;
d//delimiter;CALL add_t_ds_project_uk_unique_code;DROPPROCEDURE add_t_ds_project_uk_unique_code;-- add unique key to t_ds_queuedropPROCEDUREifEXISTS add_t_ds_queue_uk_unique_queue_name;delimiter d//CREATEPROCEDURE add_t_ds_queue_uk_unique_queue_name()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_queue'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='unique_queue_name')THENALTERTABLE t_ds_queue ADDUNIQUEKEY unique_queue_name(queue_name);ENDIF;END;
d//delimiter;CALL add_t_ds_queue_uk_unique_queue_name;DROPPROCEDURE add_t_ds_queue_uk_unique_queue_name;-- add unique key to t_ds_udfsdropPROCEDUREifEXISTS add_t_ds_udfs_uk_unique_func_name;delimiter d//CREATEPROCEDURE add_t_ds_udfs_uk_unique_func_name()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_udfs'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='unique_func_name')THENALTERTABLE t_ds_udfs ADDUNIQUEKEY unique_func_name(func_name);ENDIF;END;
d//delimiter;CALL add_t_ds_udfs_uk_unique_func_name;DROPPROCEDURE add_t_ds_udfs_uk_unique_func_name;-- add unique key to t_ds_tenantdropPROCEDUREifEXISTS add_t_ds_tenant_uk_unique_tenant_code;delimiter d//CREATEPROCEDURE add_t_ds_tenant_uk_unique_tenant_code()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_tenant'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='unique_tenant_code')THENALTERTABLE t_ds_tenant ADDUNIQUEKEY unique_tenant_code(tenant_code);ENDIF;END;
d//delimiter;CALL add_t_ds_tenant_uk_unique_tenant_code;DROPPROCEDURE add_t_ds_tenant_uk_unique_tenant_code;-- ALTER TABLE `t_ds_task_instance` ADD INDEX `idx_code_version` (`task_code`, `task_definition_version`) USING BTREE;dropPROCEDUREifEXISTS add_t_ds_task_instance_uk_idx_code_version;delimiter d//CREATEPROCEDURE add_t_ds_task_instance_uk_idx_code_version()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_task_instance'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='idx_code_version')THENALTERTABLE`t_ds_task_instance`ADDINDEX`idx_code_version`(`task_code`,`task_definition_version`)USINGBTREE;ENDIF;END;
d//delimiter;CALL add_t_ds_task_instance_uk_idx_code_version;DROPPROCEDURE add_t_ds_task_instance_uk_idx_code_version;-- ALTER TABLE `t_ds_task_instance` MODIFY COLUMN `task_params` longtext COMMENT 'job custom parameters' AFTER `app_link`;dropPROCEDUREifEXISTS modify_t_ds_task_instance_col_task_params;delimiter d//CREATEPROCEDURE modify_t_ds_task_instance_col_task_params()BEGINIFEXISTS(SELECT1FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='t_ds_task_instance'AND TABLE_SCHEMA=(SELECTDATABASE())AND COLUMN_NAME ='task_params')THENALTERTABLE`t_ds_task_instance`MODIFYCOLUMN`task_params`longtextCOMMENT'job custom parameters'AFTER`app_link`;ENDIF;END;
d//delimiter;CALL modify_t_ds_task_instance_col_task_params;DROPPROCEDURE modify_t_ds_task_instance_col_task_params;-- ALTER TABLE `t_ds_task_instance` ADD COLUMN `task_group_id` int(11) DEFAULT NULL COMMENT 'task group id';dropPROCEDUREifEXISTS add_t_ds_task_instance_col_task_group_id;delimiter d//CREATEPROCEDURE add_t_ds_task_instance_col_task_group_id()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='t_ds_task_instance'AND TABLE_SCHEMA=(SELECTDATABASE())AND COLUMN_NAME ='task_group_id')THENALTERTABLE`t_ds_task_instance`ADDCOLUMN`task_group_id`int(11)DEFAULTNULLCOMMENT'task group id'after`var_pool`;ENDIF;END;
d//delimiter;CALL add_t_ds_task_instance_col_task_group_id;DROPPROCEDURE add_t_ds_task_instance_col_task_group_id;-- ALTER TABLE `t_ds_process_task_relation` ADD KEY `idx_code` (`project_code`, `process_definition_code`) USING BTREE;dropPROCEDUREifEXISTS add_t_ds_process_task_relation_key_idx_code;delimiter d//CREATEPROCEDURE add_t_ds_process_task_relation_key_idx_code()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_process_task_relation'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='idx_code')THENALTERTABLE`t_ds_process_task_relation`ADDKEY`idx_code`(`project_code`,`process_definition_code`)USINGBTREE;ENDIF;END;
d//delimiter;CALL add_t_ds_process_task_relation_key_idx_code;DROPPROCEDURE add_t_ds_process_task_relation_key_idx_code;-- ALTER TABLE `t_ds_process_task_relation` ADD KEY `idx_pre_task_code_version` (`pre_task_code`,`pre_task_version`);dropPROCEDUREifEXISTS add_t_ds_process_task_relation_key_idx_pre_task_code_version;delimiter d//CREATEPROCEDURE add_t_ds_process_task_relation_key_idx_pre_task_code_version()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_process_task_relation'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='idx_pre_task_code_version')THENALTERTABLE`t_ds_process_task_relation`ADDKEY`idx_pre_task_code_version`(`pre_task_code`,`pre_task_version`);ENDIF;END;
d//delimiter;CALL add_t_ds_process_task_relation_key_idx_pre_task_code_version;DROPPROCEDURE add_t_ds_process_task_relation_key_idx_pre_task_code_version;-- ALTER TABLE `t_ds_process_task_relation` ADD KEY `idx_post_task_code_version` (`post_task_code`,`post_task_version`);dropPROCEDUREifEXISTS add_t_ds_process_task_relation_key_idx_post_task_code_version;delimiter d//CREATEPROCEDURE add_t_ds_process_task_relation_key_idx_post_task_code_version()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_process_task_relation'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='idx_post_task_code_version')THENALTERTABLE`t_ds_process_task_relation`ADDKEY`idx_post_task_code_version`(`post_task_code`,`post_task_version`);ENDIF;END;
d//delimiter;CALL add_t_ds_process_task_relation_key_idx_post_task_code_version;DROPPROCEDURE add_t_ds_process_task_relation_key_idx_post_task_code_version;-- ALTER TABLE `t_ds_process_task_relation_log` ADD KEY `idx_process_code_version` (`process_definition_code`,`process_definition_version`) USING BTREE;dropPROCEDUREifEXISTS add_t_ds_process_task_relation_key_idx_process_code_version;delimiter d//CREATEPROCEDURE add_t_ds_process_task_relation_key_idx_process_code_version()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_process_task_relation_log'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='idx_process_code_version')THENALTERTABLE`t_ds_process_task_relation_log`ADDKEY`idx_process_code_version`(`process_definition_code`,`process_definition_version`)USINGBTREE;ENDIF;END;
d//delimiter;CALL add_t_ds_process_task_relation_key_idx_process_code_version;DROPPROCEDURE add_t_ds_process_task_relation_key_idx_process_code_version;-- ALTER TABLE `t_ds_task_definition_log` ADD INDEX `idx_project_code` (`project_code`) USING BTREE;dropPROCEDUREifEXISTS add_t_ds_task_definition_log_key_idx_process_code_version;delimiter d//CREATEPROCEDURE add_t_ds_task_definition_log_key_idx_process_code_version()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_task_definition_log'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='idx_project_code')THENALTERTABLE`t_ds_task_definition_log`ADDINDEX`idx_project_code`(`project_code`)USINGBTREE;ENDIF;END;
d//delimiter;CALL add_t_ds_task_definition_log_key_idx_process_code_version;DROPPROCEDURE add_t_ds_task_definition_log_key_idx_process_code_version;-- ALTER TABLE `t_ds_task_definition_log` ADD INDEX `idx_code_version` (`code`,`version`) USING BTREE;dropPROCEDUREifEXISTS add_t_ds_task_definition_log_key_idx_code_version;delimiter d//CREATEPROCEDURE add_t_ds_task_definition_log_key_idx_code_version()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_task_definition_log'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='idx_code_version')THENALTERTABLE`t_ds_task_definition_log`ADDINDEX`idx_code_version`(`code`,`version`)USINGBTREE;ENDIF;END;
d//delimiter;CALL add_t_ds_task_definition_log_key_idx_code_version;DROPPROCEDURE add_t_ds_task_definition_log_key_idx_code_version;-- alter table t_ds_task_definition_log add `task_group_id` int(11) DEFAULT NULL COMMENT 'task group id' AFTER `resource_ids`;dropPROCEDUREifEXISTS add_t_ds_task_definition_log_col_task_group_id;delimiter d//CREATEPROCEDURE add_t_ds_task_definition_log_col_task_group_id()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='t_ds_task_definition_log'AND TABLE_SCHEMA=(SELECTDATABASE())AND COLUMN_NAME='task_group_id')THENaltertable t_ds_task_definition_log add`task_group_id`int(11)DEFAULTNULLCOMMENT'task group id'AFTER`resource_ids`;ENDIF;END;
d//delimiter;CALL add_t_ds_task_definition_log_col_task_group_id;DROPPROCEDURE add_t_ds_task_definition_log_col_task_group_id;-- alter table t_ds_task_definition_log add `task_group_id` int(11) DEFAULT NULL COMMENT 'task group id' AFTER `resource_ids`;dropPROCEDUREifEXISTS add_t_ds_task_definition_col_task_group_id;delimiter d//CREATEPROCEDURE add_t_ds_task_definition_col_task_group_id()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='t_ds_task_definition'AND TABLE_SCHEMA=(SELECTDATABASE())AND COLUMN_NAME='task_group_id')THENaltertable t_ds_task_definition add`task_group_id`intDEFAULTNULLCOMMENT'task group id';ENDIF;END;
d//delimiter;CALL add_t_ds_task_definition_col_task_group_id;DROPPROCEDURE add_t_ds_task_definition_col_task_group_id;-- alter table t_ds_task_definition_log add `task_group_priority` int(11) DEFAULT NULL COMMENT 'task group id' AFTER `task_group_id`;dropPROCEDUREifEXISTS add_t_ds_task_definition_log_col_task_group_priority;delimiter d//CREATEPROCEDURE add_t_ds_task_definition_log_col_task_group_priority()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='t_ds_task_definition_log'AND TABLE_SCHEMA=(SELECTDATABASE())AND COLUMN_NAME='task_group_priority')THENaltertable t_ds_task_definition_log add`task_group_priority`tinyintDEFAULT'0'COMMENT'task group priority'AFTER`task_group_id`;ENDIF;END;
d//delimiter;CALL add_t_ds_task_definition_log_col_task_group_priority;DROPPROCEDURE add_t_ds_task_definition_log_col_task_group_priority;-- alter table t_ds_task_definition add `task_group_priority` int(11) DEFAULT '0' COMMENT 'task group id' AFTER `task_group_id`;dropPROCEDUREifEXISTS add_t_ds_task_definition_col_task_group_priority;delimiter d//CREATEPROCEDURE add_t_ds_task_definition_col_task_group_priority()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='t_ds_task_definition'AND TABLE_SCHEMA=(SELECTDATABASE())AND COLUMN_NAME='task_group_priority')THENaltertable t_ds_task_definition add`task_group_priority`tinyintDEFAULT'0'COMMENT'task group priority'AFTER`task_group_id`;ENDIF;END;
d//delimiter;CALL add_t_ds_task_definition_col_task_group_priority;DROPPROCEDURE add_t_ds_task_definition_col_task_group_priority;-- ALTER TABLE `t_ds_user` ADD COLUMN `time_zone` varchar(32) DEFAULT NULL COMMENT 'time zone';dropPROCEDUREifEXISTS add_t_ds_user_col_time_zone;delimiter d//CREATEPROCEDURE add_t_ds_user_col_time_zone()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='t_ds_user'AND TABLE_SCHEMA=(SELECTDATABASE())AND COLUMN_NAME='time_zone')THENALTERTABLE`t_ds_user`ADDCOLUMN`time_zone`varchar(32)DEFAULTNULLCOMMENT'time zone';ENDIF;END;
d//delimiter;CALL add_t_ds_user_col_time_zone;DROPPROCEDURE add_t_ds_user_col_time_zone;-- ALTER TABLE `t_ds_alert` ADD COLUMN `warning_type` tinyint(4) DEFAULT '2' COMMENT '1 process is successfully, 2 process/task is failed';dropPROCEDUREifEXISTS add_t_ds_alert_col_warning_type;delimiter d//CREATEPROCEDURE add_t_ds_alert_col_warning_type()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='t_ds_alert'AND TABLE_SCHEMA=(SELECTDATABASE())AND COLUMN_NAME='warning_type')THENALTERTABLE`t_ds_alert`ADDCOLUMN`warning_type`tinyint(4)DEFAULT'2'COMMENT'1 process is successfully, 2 process/task is failed';ENDIF;END;
d//delimiter;CALL add_t_ds_alert_col_warning_type;DROPPROCEDURE add_t_ds_alert_col_warning_type;-- ALTER TABLE `t_ds_alert` ADD INDEX `idx_status` (`alert_status`) USING BTREE;dropPROCEDUREifEXISTS add_t_ds_alert_idx_idx_status;delimiter d//CREATEPROCEDURE add_t_ds_alert_idx_idx_status()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_NAME='t_ds_alert'AND TABLE_SCHEMA=(SELECTDATABASE())AND INDEX_NAME='idx_status')THENALTERTABLE`t_ds_alert`ADDINDEX`idx_status`(`alert_status`)USINGBTREE;ENDIF;END;
d//delimiter;CALL add_t_ds_alert_idx_idx_status;DROPPROCEDURE add_t_ds_alert_idx_idx_status;-- ALTER TABLE `t_ds_alert` ADD COLUMN `project_code` bigint DEFAULT NULL COMMENT 'project_code';-- ALTER TABLE `t_ds_alert` ADD COLUMN `process_definition_code` bigint DEFAULT NULL COMMENT 'process_definition_code';-- ALTER TABLE `t_ds_alert` ADD COLUMN `process_instance_id` int DEFAULT NULL COMMENT 'process_instance_id';-- ALTER TABLE `t_ds_alert` ADD COLUMN `alert_type` int DEFAULT NULL COMMENT 'alert_type';dropPROCEDUREifEXISTS add_t_ds_alert_col_project_code;delimiter d//CREATEPROCEDURE add_t_ds_alert_col_project_code()BEGINIFNOTEXISTS(SELECT1FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='t_ds_alert'AND TABLE_SCHEMA=(SELECTDATABASE())AND COLUMN_NAME='project_code')THENALTERTABLE`t_ds_alert`ADDCOLUMN`project_code`bigintDEFAULTNULLCOMMENT'project_code';ALTERTABLE`t_ds_alert`ADDCOLUMN`process_definition_code`bigintDEFAULTNULLCOMMENT'process_definition_code';ALTERTABLE`t_ds_alert`ADDCOLUMN`process_instance_id`intDEFAULTNULLCOMMENT'process_instance_id';ALTERTABLE`t_ds_alert`ADDCOLUMN`alert_type`intDEFAULTNULLCOMMENT'alert_type';ENDIF;END;
d//delimiter;# CALL add_t_ds_alert_col_project_code;DROPPROCEDURE add_t_ds_alert_col_project_code;-- t_ds_task_instancedropPROCEDUREifEXISTS alter_t_ds_task_instance_col_log_path;delimiter d//CREATEPROCEDURE alter_t_ds_task_instance_col_log_path()BEGINIFEXISTS(SELECT1FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='t_ds_task_instance'AND TABLE_SCHEMA=(SELECTDATABASE())AND COLUMN_NAME='log_path')THENALTERTABLE`t_ds_task_instance`MODIFYCOLUMN`log_path`longtextDEFAULTNULLCOMMENT'task log path';ENDIF;END;
d//delimiter;CALL alter_t_ds_task_instance_col_log_path;DROPPROCEDURE alter_t_ds_task_instance_col_log_path;---- Table structure for table `t_ds_dq_comparison_type`--DROPTABLEIFEXISTS`t_ds_dq_comparison_type`;CREATETABLE`t_ds_dq_comparison_type`(`id`int(11)NOTNULLAUTO_INCREMENT,`type`varchar(100)NOTNULL,`execute_sql`textDEFAULTNULL,`output_table`varchar(100)DEFAULTNULL,`name`varchar(100)DEFAULTNULL,`create_time`datetimeDEFAULTNULL,`update_time`datetimeDEFAULTNULL,`is_inner_source`tinyint(1)DEFAULT'0',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;---- Table structure for table t_ds_dq_execute_result--DROPTABLEIFEXISTS`t_ds_dq_execute_result`;CREATETABLE`t_ds_dq_execute_result`(`id`int(11)NOTNULLAUTO_INCREMENT,`process_definition_id`int(11)DEFAULTNULL,`process_instance_id`int(11)DEFAULTNULL,`task_instance_id`int(11)DEFAULTNULL,`rule_type`int(11)DEFAULTNULL,`rule_name`varchar(255)DEFAULTNULL,`statistics_value`doubleDEFAULTNULL,`comparison_value`doubleDEFAULTNULL,`check_type`int(11)DEFAULTNULL,`threshold`doubleDEFAULTNULL,`operator`int(11)DEFAULTNULL,`failure_strategy`int(11)DEFAULTNULL,`state`int(11)DEFAULTNULL,`user_id`int(11)DEFAULTNULL,`comparison_type`int(11)DEFAULTNULL,`error_output_path`textDEFAULTNULL,`create_time`datetimeDEFAULTNULL,`update_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;---- Table structure for table t_ds_dq_rule--DROPTABLEIFEXISTS`t_ds_dq_rule`;CREATETABLE`t_ds_dq_rule`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(100)DEFAULTNULL,`type`int(11)DEFAULTNULL,`user_id`int(11)DEFAULTNULL,`create_time`datetimeDEFAULTNULL,`update_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;---- Table structure for table t_ds_dq_rule_execute_sql--DROPTABLEIFEXISTS`t_ds_dq_rule_execute_sql`;CREATETABLE`t_ds_dq_rule_execute_sql`(`id`int(11)NOTNULLAUTO_INCREMENT,`index`int(11)DEFAULTNULL,`sql`textDEFAULTNULL,`table_alias`varchar(255)DEFAULTNULL,`type`int(11)DEFAULTNULL,`is_error_output_sql`tinyint(1)DEFAULT'0',`create_time`datetimeDEFAULTNULL,`update_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;---- Table structure for table t_ds_dq_rule_input_entry--DROPTABLEIFEXISTS`t_ds_dq_rule_input_entry`;CREATETABLE`t_ds_dq_rule_input_entry`(`id`int(11)NOTNULLAUTO_INCREMENT,`field`varchar(255)DEFAULTNULL,`type`varchar(255)DEFAULTNULL,`title`varchar(255)DEFAULTNULL,`value`varchar(255)DEFAULTNULL,`options`textDEFAULTNULL,`placeholder`varchar(255)DEFAULTNULL,`option_source_type`int(11)DEFAULTNULL,`value_type`int(11)DEFAULTNULL,`input_type`int(11)DEFAULTNULL,`is_show`tinyint(1)DEFAULT'1',`can_edit`tinyint(1)DEFAULT'1',`is_emit`tinyint(1)DEFAULT'0',`is_validate`tinyint(1)DEFAULT'1',`create_time`datetimeDEFAULTNULL,`update_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;---- Table structure for table t_ds_dq_task_statistics_value--DROPTABLEIFEXISTS`t_ds_dq_task_statistics_value`;CREATETABLE`t_ds_dq_task_statistics_value`(`id`int(11)NOTNULLAUTO_INCREMENT,`process_definition_id`int(11)DEFAULTNULL,`task_instance_id`int(11)DEFAULTNULL,`rule_id`int(11)NOTNULL,`unique_code`varchar(255)NULL,`statistics_name`varchar(255)NULL,`statistics_value`doubleNULL,`data_time`datetimeDEFAULTNULL,`create_time`datetimeDEFAULTNULL,`update_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;---- Table structure for table t_ds_relation_rule_execute_sql--DROPTABLEIFEXISTS`t_ds_relation_rule_execute_sql`;CREATETABLE`t_ds_relation_rule_execute_sql`(`id`int(11)NOTNULLAUTO_INCREMENT,`rule_id`int(11)DEFAULTNULL,`execute_sql_id`int(11)DEFAULTNULL,`create_time`datetimeNULL,`update_time`datetimeNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;---- Table structure for table t_ds_relation_rule_input_entry--DROPTABLEIFEXISTS`t_ds_relation_rule_input_entry`;CREATETABLE`t_ds_relation_rule_input_entry`(`id`int(11)NOTNULLAUTO_INCREMENT,`rule_id`int(11)DEFAULTNULL,`rule_input_entry_id`int(11)DEFAULTNULL,`values_map`textDEFAULTNULL,`index`int(11)DEFAULTNULL,`create_time`datetimeDEFAULTNULL,`update_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;-- ------------------------------ Table structure for t_ds_k8s-- ----------------------------DROPTABLEIFEXISTS`t_ds_k8s`;CREATETABLE`t_ds_k8s`(`id`int(11)NOTNULLAUTO_INCREMENT,`k8s_name`varchar(100)DEFAULTNULL,`k8s_config`textDEFAULTNULL,`create_time`datetimeDEFAULTNULLCOMMENT'create time',`update_time`datetimeDEFAULTNULLCOMMENT'update time',PRIMARYKEY(`id`))ENGINE=INNODBAUTO_INCREMENT=1DEFAULTCHARSET= utf8;-- ------------------------------ Table structure for t_ds_k8s_namespace-- ----------------------------DROPTABLEIFEXISTS`t_ds_k8s_namespace`;CREATETABLE`t_ds_k8s_namespace`(`id`int(11)NOTNULLAUTO_INCREMENT,`limits_memory`int(11)DEFAULTNULL,`namespace`varchar(100)DEFAULTNULL,`online_job_num`int(11)DEFAULTNULL,`user_id`int(11)DEFAULTNULL,`pod_replicas`int(11)DEFAULTNULL,`pod_request_cpu`decimal(14,3)DEFAULTNULL,`pod_request_memory`int(11)DEFAULTNULL,`limits_cpu`decimal(14,3)DEFAULTNULL,`k8s`varchar(100)DEFAULTNULL,`create_time`datetimeDEFAULTNULLCOMMENT'create time',`update_time`datetimeDEFAULTNULLCOMMENT'update time',PRIMARYKEY(`id`),UNIQUEKEY`k8s_namespace_unique`(`namespace`,`k8s`))ENGINE=INNODBAUTO_INCREMENT=1DEFAULTCHARSET= utf8;-- ------------------------------ Table structure for t_ds_relation_namespace_user-- ----------------------------DROPTABLEIFEXISTS`t_ds_relation_namespace_user`;CREATETABLE`t_ds_relation_namespace_user`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'key',`user_id`int(11)NOTNULLCOMMENT'user id',`namespace_id`int(11)DEFAULTNULLCOMMENT'namespace id',`perm`int(11)DEFAULT'1'COMMENT'limits of authority',`create_time`datetimeDEFAULTNULLCOMMENT'create time',`update_time`datetimeDEFAULTNULLCOMMENT'update time',PRIMARYKEY(`id`),UNIQUEKEY`namespace_user_unique`(`user_id`,`namespace_id`))ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET= utf8;-- ------------------------------ Table structure for t_ds_alert_send_status-- ----------------------------DROPTABLEIFEXISTS t_ds_alert_send_status;CREATETABLE t_ds_alert_send_status (`id`int(11)NOTNULLAUTO_INCREMENT,`alert_id`int(11)NOTNULL,`alert_plugin_instance_id`int(11)NOTNULL,`send_status`tinyint(4)DEFAULT'0',`log`text,`create_time`datetimeDEFAULTNULLCOMMENT'create time',PRIMARYKEY(`id`),UNIQUEKEY`alert_send_status_unique`(`alert_id`,`alert_plugin_instance_id`))ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;-- ------------------------------ Table structure for t_ds_audit_log-- ----------------------------DROPTABLEIFEXISTS`t_ds_audit_log`;CREATETABLE`t_ds_audit_log`(`id`bigint(11)NOTNULLAUTO_INCREMENTCOMMENT'key',`user_id`int(11)NOTNULLCOMMENT'user id',`resource_type`int(11)NOTNULLCOMMENT'resource type',`operation`int(11)NOTNULLCOMMENT'operation',`time`datetimeDEFAULTCURRENT_TIMESTAMPCOMMENT'create time',`resource_id`int(11)NULLDEFAULTNULLCOMMENT'resource id',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;-- ------------------------------ Table structure for t_ds_task_group-- ----------------------------DROPTABLEIFEXISTS`t_ds_task_group`;CREATETABLE`t_ds_task_group`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'key',`name`varchar(100)DEFAULTNULLCOMMENT'task_group name',`description`varchar(200)DEFAULTNULL,`group_size`int(11)NOTNULLCOMMENT'group size',`use_size`int(11)DEFAULT'0'COMMENT'used size',`user_id`int(11)DEFAULTNULLCOMMENT'creator id',`project_code`bigint(20)DEFAULT0COMMENT'project code',`status`tinyint(4)DEFAULT'1'COMMENT'0 not available, 1 available',`create_time`timestampNULLDEFAULTCURRENT_TIMESTAMP,`update_time`timestampNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(`id`))ENGINE=INNODBAUTO_INCREMENT=1DEFAULTCHARSET= utf8;-- ------------------------------ Table structure for t_ds_task_group_queue-- ----------------------------DROPTABLEIFEXISTS`t_ds_task_group_queue`;CREATETABLE`t_ds_task_group_queue`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'key',`task_id`int(11)DEFAULTNULLCOMMENT'taskintanceid',`task_name`varchar(100)DEFAULTNULLCOMMENT'TaskInstance name',`group_id`int(11)DEFAULTNULLCOMMENT'taskGroup id',`process_id`int(11)DEFAULTNULLCOMMENT'processInstace id',`priority`int(8)DEFAULT'0'COMMENT'priority',`status`tinyint(4)DEFAULT'-1'COMMENT'-1: waiting 1: running 2: finished',`force_start`tinyint(4)DEFAULT'0'COMMENT'is force start 0 NO ,1 YES',`in_queue`tinyint(4)DEFAULT'0'COMMENT'ready to get the queue by other task finish 0 NO ,1 YES',`create_time`timestampNULLDEFAULTCURRENT_TIMESTAMP,`update_time`timestampNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(`id`))ENGINE=INNODBAUTO_INCREMENT=1DEFAULTCHARSET= utf8;ALTERTABLE t_ds_process_instance ADD`next_process_instance_id`int(11)DEFAULT'0'COMMENT'serial queue next processInstanceId';ALTERTABLE t_ds_process_instance ADD`restart_time`datetimeDEFAULTNULLCOMMENT'process instance restart time';ALTERTABLE t_ds_process_definition ADD`execution_type`tinyint(4)DEFAULT'0'COMMENT'execution_type 0:parallel,1:serial wait,2:serial discard,3:serial priority';ALTERTABLE t_ds_process_definition_log ADD`execution_type`tinyint(4)DEFAULT'0'COMMENT'execution_type 0:parallel,1:serial wait,2:serial discard,3:serial priority';
4.安装3.0.1 DS
具体安装参考官网，创建新库，这里不做介绍。
introduction (apache.org)
5.把DS2.0数据导入数据库
需要重点注意：
次脚本导入时：
1.定时调度 状态全设置为下线状态 定时表 中 t_ds_schedules release_state 全设置为0了，为了防止和原调度冲突，测试完成后需要手动上线即可。
2.worker 分组组信息需要重新设置，和原先保持一致即可。
3.告警实例管理需要重新添加，然后在告警组管理中重新绑定。
#项目表truncatetable t_ds_project;insertinto t_ds_project
select id,
name,
code,
description,
user_id,
flag,
create_time,
update_time
from dolphin201.t_ds_project;#任务过程定义####唯一dagtruncatetable t_ds_process_definition;insertinto t_ds_process_definition
select id,
code,
name,
version,
description,
project_code,
release_state,
user_id,
global_params,
flag,
locations,
warning_group_id,
timeout,
tenant_id,
execution_type,
create_time,
update_time
from dolphin201.t_ds_process_definition;#任务过程定义日志truncatetable t_ds_process_definition_log;insertinto t_ds_process_definition_log
select t1.id,
t1.code,
t1.name,
t1.version,
t1.description,
t1.project_code,
t1.release_state,
t1.user_id,
t1.global_params,
t1.flag,
t1.locations,
t1.warning_group_id,
t1.timeout,
t1.tenant_id,
t1.execution_type,
t1.operator,
t1.operate_time,
t1.create_time,
t1.update_time
from dolphin201.t_ds_process_definition_log t1
innerjoin(select code, project_code,max(version) version
from dolphin201.t_ds_process_definition_log
groupby project_code, code) t2
on t1.code = t2.code and t1.project_code = t2.project_code and t1.version = t2.version
;###进行任务相关性表truncatetable t_ds_process_task_relation;insertinto t_ds_process_task_relation
select id,
name,
project_code,
process_definition_code,
process_definition_version,
pre_task_code,
pre_task_version,
post_task_code,
post_task_version,
condition_type,
condition_params,
create_time,
update_time
from dolphin201.t_ds_process_task_relation;###进行任务相关性表日志truncatetable t_ds_process_task_relation_log;insertinto t_ds_process_task_relation_log
select t3.id,
t3.name,
t3.project_code,
t3.process_definition_code,
t3.process_definition_version,
t3.pre_task_code,
t3.pre_task_version,
t3.post_task_code,
t3.post_task_version,
t3.condition_type,
t3.condition_params,
t3.operator,
t3.operate_time,
t3.create_time,
t3.update_time # from t_ds_process_task_relation;from dolphin201.t_ds_process_task_relation_log t3
innerjoin(select project_code,
process_definition_code,
post_task_code,max(process_definition_version) process_definition_version,max(post_task_version)AS post_task_version
from dolphin201.t_ds_process_task_relation_log
groupby project_code, process_definition_code, post_task_code) t4
on t3.project_code = t4.project_code
and t3.process_definition_code = t4.process_definition_code
and t3.post_task_code = t4.post_task_code
and t3.process_definition_version = t4.process_definition_version
and t3.post_task_version = t4.post_task_version
;###任务定义truncatetable t_ds_task_definition;insertinto t_ds_task_definition
select id,
code,
name,
version,
description,
project_code,
user_id,
task_type,
task_params,
flag,
task_priority,
worker_group,
environment_code,
fail_retry_times,
fail_retry_interval,
timeout_flag,
timeout_notify_strategy,
timeout,
delay_time,
resource_ids,
task_group_id,
task_group_priority,
create_time,
update_time
from dolphin201.t_ds_task_definition;###任务定义日志truncatetable t_ds_task_definition_log;insertinto t_ds_task_definition_log
select t5.id,
t5.code,
t5.name,
t5.version,
t5.description,
t5.project_code,
t5.user_id,
t5.task_type,
t5.task_params,
t5.flag,
t5.task_priority,
t5.worker_group,
t5.environment_code,
t5.fail_retry_times,
t5.fail_retry_interval,
t5.timeout_flag,
t5.timeout_notify_strategy,
t5.timeout,
t5.delay_time,
t5.resource_ids,
t5.operator,
t5.task_group_id,
t5.task_group_priority,
t5.operate_time,
t5.create_time,
t5.update_time
from dolphin201.t_ds_task_definition_log t5
;###定时计划truncatetable t_ds_schedules;insertinto t_ds_schedules
select id,
process_definition_code,
start_time,
end_time,
timezone_id,
crontab,
failure_strategy,
user_id,'0'AS release_state,
warning_type,
warning_group_id,
process_instance_priority,
worker_group,
environment_code,
create_time,
update_time
from dolphin201.t_ds_schedules;###告警组truncatetable t_ds_alertgroup;insertinto t_ds_alertgroup
select id,
alert_instance_ids,
create_user_id,
group_name,
description,
create_time,
update_time
from dolphin201.t_ds_alertgroup;## ###告警组信息 ，这张表信息有冲突故不导入，后续自己配置下# truncate table t_ds_alert_plugin_instance;# insert into t_ds_alert_plugin_instance# select id, plugin_define_id, plugin_instance_params, create_time, update_time, instance_name# from dolphin201.t_ds_alert_plugin_instance;###用户truncatetable t_ds_user;insertinto t_ds_user
select id,
user_name,
user_password,
user_type,
email,
phone,
tenant_id,
create_time,
update_time,
queue,
state,
time_zone
from dolphin201.t_ds_user;###租户truncatetable t_ds_tenant;insertinto t_ds_tenant
select id,
tenant_code,
description,
queue_id,
create_time,
update_time
from dolphin201.t_ds_tenant;####资源中心truncatetable t_ds_resources;insertinto t_ds_resources
select id,
alias,
file_name,
description,
user_id,type,
size,
create_time,
update_time,
pid,
full_name,
is_directory
from dolphin201.t_ds_resources;####数据源truncatetable t_ds_datasource;insertinto t_ds_datasource
select id,
name,
note,type,
user_id,
connection_params,
create_time,
update_time
from dolphin201.t_ds_datasource;###环境情况truncatetable t_ds_environment;insertinto t_ds_environment
select id,
code,
name,
config,
description,
operator,
create_time,
update_time
from dolphin201.t_ds_environment;
6.遇到问题和解决方案
1.任务无法打开
如直接使用官网任务脚本会报次错误，这保存可以直接看日志，是由于字段缺少导致的，上面ddl语句中已经添加相关字段语句。
2.会出现少量任务无法保存问题，主要原因主键冲突造成的
最简单解决方案：
重新复制一个任务，在新任务中进行更改保存
3.用户授权的任务需要进行重新授权。
本文转载自: https://blog.csdn.net/m0_46571744/article/details/128175021
版权归原作者 缤纷的世界 所有， 如有侵权，请联系我们删除。
版权归原作者 缤纷的世界 所有， 如有侵权，请联系我们删除。