You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@dolphinscheduler.apache.org by zh...@apache.org on 2022/09/14 10:03:16 UTC
[dolphinscheduler] branch dev updated: [Fix][db] fix init&upgrade mysql-meta-schema bugs (#11887)
This is an automated email from the ASF dual-hosted git repository.
zhongjiajie pushed a commit to branch dev
in repository https://gitbox.apache.org/repos/asf/dolphinscheduler.git
The following commit(s) were added to refs/heads/dev by this push:
new e70c501f9a [Fix][db] fix init&upgrade mysql-meta-schema bugs (#11887)
e70c501f9a is described below
commit e70c501f9aafa5b456e01a897259a940d4a0a0ed
Author: wendongdi <49...@users.noreply.github.com>
AuthorDate: Wed Sep 14 18:03:07 2022 +0800
[Fix][db] fix init&upgrade mysql-meta-schema bugs (#11887)
---
.../2.0.3_schema/mysql/dolphinscheduler_ddl.sql | 36 ++++++++++++++++++++--
.../2.0.4_schema/mysql/dolphinscheduler_ddl.sql | 33 ++++++++++++++++++--
.../3.1.0_schema/mysql/dolphinscheduler_ddl.sql | 2 +-
.../3.1.1_schema/mysql/dolphinscheduler_ddl.sql | 34 ++++++++++++++++++--
4 files changed, 97 insertions(+), 8 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
diff --git a/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.1.0_schema/mysql/dolphinscheduler_ddl.sql b/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.1.0_schema/mysql/dolphinscheduler_ddl.sql
index ddf91f5238..0d05800e33 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.1.0_schema/mysql/dolphinscheduler_ddl.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.1.0_schema/mysql/dolphinscheduler_ddl.sql
@@ -340,7 +340,7 @@ drop PROCEDURE if EXISTS drop_t_ds_task_instance_key_foreign_key_instance_id;
delimiter d//
CREATE PROCEDURE drop_t_ds_task_instance_key_foreign_key_instance_id()
BEGIN
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME='t_ds_task_instance'
AND TABLE_SCHEMA=(SELECT DATABASE())
AND INDEX_NAME='foreign_key_instance_id')
diff --git a/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.1.1_schema/mysql/dolphinscheduler_ddl.sql b/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.1.1_schema/mysql/dolphinscheduler_ddl.sql
index 59e3540f97..e3fdefe870 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.1.1_schema/mysql/dolphinscheduler_ddl.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.1.1_schema/mysql/dolphinscheduler_ddl.sql
@@ -16,7 +16,37 @@
*/
-ALTER TABLE `t_ds_worker_group` ADD COLUMN `other_params_json` text DEFAULT NULL COMMENT 'other params json';
+drop procedure if exists add_column_safety;
+delimiter d//
+create procedure add_column_safety(target_table_name varchar(256), target_column varchar(256),
+ target_column_type varchar(256), sths_else varchar(256))
+begin
+ declare target_database varchar(256);
+ select database() into target_database;
+ IF EXISTS(SELECT *
+ FROM information_schema.COLUMNS
+ WHERE COLUMN_NAME = target_column
+ AND TABLE_NAME = target_table_name
+ )
+ THEN
+ set @statement =
+ concat('alter table ', target_table_name, ' change column ', target_column, ' ', target_column, ' ',
+ target_column_type, ' ',
+ sths_else);
+ PREPARE STMT_c FROM @statement;
+ EXECUTE STMT_c;
+ ELSE
+ set @statement =
+ concat('alter table ', target_table_name, ' add column ', target_column, ' ', target_column_type, ' ',
+ sths_else);
+ PREPARE STMT_a FROM @statement;
+ EXECUTE STMT_a;
+ END IF;
+end;
+d//
+delimiter ;
-ALTER TABLE `t_ds_process_instance` ADD COLUMN `state_history` text DEFAULT NULL COMMENT 'state history desc' AFTER `state`;
+call add_column_safety('t_ds_worker_group','other_params_json_test', 'text' , "DEFAULT NULL COMMENT 'other params json'");
+call add_column_safety('t_ds_process_instance','state_history', 'text' , "DEFAULT NULL COMMENT 'state history desc' AFTER `state`");
+drop procedure if exists add_column_safety;
\ No newline at end of file