[GitHub] [dolphinscheduler] caishunfeng commented on a diff in pull request #11887: [Fix][dolphinscheduler-api] fix init&upgrade mysql-meta-schema bugs

[GitBox <gi...@apache.org>]

caishunfeng commented on code in PR #11887:
URL: https://github.com/apache/dolphinscheduler/pull/11887#discussion_r969105966


##########
dolphinscheduler-dao/src/main/resources/sql/upgrade/3.1.1_schema/mysql/dolphinscheduler_ddl.sql:
##########
@@ -16,7 +16,34 @@
 */
 
 
-ALTER TABLE `t_ds_worker_group` ADD COLUMN `other_params_json` text DEFAULT NULL COMMENT 'other params json';
 
-ALTER TABLE `t_ds_process_instance` ADD COLUMN `state_history` text DEFAULT NULL COMMENT 'state history desc' AFTER `state`;
+drop procedure if exists add_column_if_not_exists;
+delimiter d//
+create procedure add_column_if_not_exists(target_table_name varchar(256), target_column varchar(256),
+                                          add_statement 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,' drop column ',target_column);
+        PREPARE STMT FROM @statement;
+        EXECUTE STMT;
+    END IF;
+    set @statement = concat(add_statement);
+    PREPARE STMT FROM @statement;
+    EXECUTE STMT;
+end;
+d//
+delimiter ;
 
+-- ALTER TABLE `t_ds_worker_group` ADD COLUMN `other_params_json` text DEFAULT NULL COMMENT 'other params json';
+-- ALTER TABLE `t_ds_process_instance` ADD COLUMN `state_history` text DEFAULT NULL COMMENT 'state history desc' AFTER `state`;
+
+call add_column_if_not_exists('t_ds_worker_group','other_params_json',"ALTER TABLE `t_ds_worker_group` ADD COLUMN `other_params_json` text DEFAULT NULL COMMENT 'other params json'");
+call add_column_if_not_exists('t_ds_process_instance','state_history',"ALTER TABLE `t_ds_process_instance` ADD COLUMN `state_history` text DEFAULT NULL COMMENT 'state history desc' AFTER `state`");

Review Comment:
   Agree with jiajie



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org