You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@dolphinscheduler.apache.org by ke...@apache.org on 2022/09/14 10:50:32 UTC
[dolphinscheduler] branch 3.0.1-prepare updated: [Cherry-pick 3.0.1][Fix][db] fix init&upgrade mysql-meta-schema bugs #11887 (#11933)
This is an automated email from the ASF dual-hosted git repository.
kerwin pushed a commit to branch 3.0.1-prepare
in repository https://gitbox.apache.org/repos/asf/dolphinscheduler.git
The following commit(s) were added to refs/heads/3.0.1-prepare by this push:
new ba9605f576 [Cherry-pick 3.0.1][Fix][db] fix init&upgrade mysql-meta-schema bugs #11887 (#11933)
ba9605f576 is described below
commit ba9605f5762832f254c092e1c4a34e590c657993
Author: caishunfeng <ca...@gmail.com>
AuthorDate: Wed Sep 14 18:50:27 2022 +0800
[Cherry-pick 3.0.1][Fix][db] fix init&upgrade mysql-meta-schema bugs #11887 (#11933)
---
.../2.0.3_schema/mysql/dolphinscheduler_ddl.sql | 36 ++++++++++++++++++++--
.../2.0.4_schema/mysql/dolphinscheduler_ddl.sql | 33 ++++++++++++++++++--
2 files changed, 64 insertions(+), 5 deletions(-)
diff --git a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.3_schema/mysql/dolphinscheduler_ddl.sql b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.3_schema/mysql/dolphinscheduler_ddl.sql
index a95858d936..ebacd2f98a 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.3_schema/mysql/dolphinscheduler_ddl.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.3_schema/mysql/dolphinscheduler_ddl.sql
@@ -15,7 +15,37 @@
* limitations under the License.
*/
+drop procedure if exists re_add_index;
+delimiter d//
+create procedure re_add_index(target_table_name varchar(256),
+ target_index_type varchar(8), target_index_name varchar(256),
+ target_columns varchar(512), using_str varchar(256))
+begin
+ declare target_database varchar(256);
+ select database() into target_database;
+ IF EXISTS(SELECT *
+ FROM information_schema.statistics
+ WHERE table_schema = target_database
+ AND table_name = target_table_name
+ AND index_name = target_index_name) THEN
+ set @statement = concat('drop index ', target_index_name, ' on ', target_table_name);
+ PREPARE STMT FROM @statement;
+ EXECUTE STMT;
+ END IF;
+ set @statement =
+ concat('alter table ', target_table_name, ' add ', target_index_type, ' ', target_index_name,
+ '(', target_columns,
+ ') ', using_str);
+ PREPARE STMT FROM @statement;
+ EXECUTE STMT;
+end;
+d//
+delimiter ;
+
ALTER TABLE `t_ds_task_instance` MODIFY COLUMN `task_params` longtext COMMENT 'job custom parameters' AFTER `app_link`;
-ALTER TABLE `t_ds_process_task_relation` ADD KEY `idx_code` (`project_code`, `process_definition_code`) USING BTREE;
-ALTER TABLE `t_ds_process_task_relation_log` ADD KEY `idx_process_code_version` (`process_definition_code`,`process_definition_version`) USING BTREE;
-ALTER TABLE `t_ds_task_definition_log` ADD INDEX `idx_code_version` (`code`,`version`) USING BTREE;
\ No newline at end of file
+
+call re_add_index('t_ds_process_task_relation','KEY','idx_code', '`project_code`, `process_definition_code`', 'USING BTREE');
+call re_add_index('t_ds_process_task_relation_log','KEY','idx_process_code_version','`process_definition_code`,`process_definition_version`', 'USING BTREE');
+call re_add_index('t_ds_task_definition_log','INDEX','idx_code_version','`code`,`version`', 'USING BTREE');
+
+drop procedure if exists re_add_index;
\ No newline at end of file
diff --git a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.4_schema/mysql/dolphinscheduler_ddl.sql b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.4_schema/mysql/dolphinscheduler_ddl.sql
index 996e009dc2..e059538669 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.4_schema/mysql/dolphinscheduler_ddl.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.4_schema/mysql/dolphinscheduler_ddl.sql
@@ -15,5 +15,34 @@
* limitations under the License.
*/
-ALTER TABLE `t_ds_task_instance` ADD INDEX `idx_code_version` (`task_code`, `task_definition_version`) USING BTREE;
-ALTER TABLE `t_ds_task_definition_log` ADD INDEX `idx_project_code` (`project_code`) USING BTREE;
\ No newline at end of file
+drop procedure if exists re_add_index;
+delimiter d//
+create procedure re_add_index(target_table_name varchar(256),
+ target_index_type varchar(8), target_index_name varchar(256),
+ target_columns varchar(512), using_str varchar(256))
+begin
+ declare target_database varchar(256);
+select database() into target_database;
+IF EXISTS(SELECT *
+ FROM information_schema.statistics
+ WHERE table_schema = target_database
+ AND table_name = target_table_name
+ AND index_name = target_index_name) THEN
+ set @statement = concat('drop index ', target_index_name, ' on ', target_table_name);
+PREPARE STMT FROM @statement;
+EXECUTE STMT;
+END IF;
+ set @statement =
+ concat('alter table ', target_table_name, ' add ', target_index_type, ' ', target_index_name,
+ '(', target_columns,
+ ') ', using_str);
+PREPARE STMT FROM @statement;
+EXECUTE STMT;
+end;
+d//
+delimiter ;
+
+call re_add_index('t_ds_task_instance','INDEX','idx_code_version','`task_code`, `task_definition_version`', 'USING BTREE');
+call re_add_index('t_ds_task_definition_log','INDEX','idx_project_code','`project_code`', 'USING BTREE');
+
+drop procedure if exists re_add_index;
\ No newline at end of file