This is an automated email from the ASF dual-hosted git repository.
caishunfeng pushed a commit to branch 3.1.0-prepare
in repository https://gitbox.apache.org/repos/asf/dolphinscheduler.git
The following commit(s) were added to refs/heads/3.1.0-prepare by this push:
new 0f6cc4fe33 [Fix][db] fix init&upgrade mysql-meta-schema bugs (#11887)
(#11935)
0f6cc4fe33 is described below
commit 0f6cc4fe33d3e94521a35a070a1aabda4168eb71
Author: caishunfeng <[email protected]>
AuthorDate: Wed Sep 14 18:49:49 2022 +0800
[Fix][db] fix init&upgrade mysql-meta-schema bugs (#11887) (#11935)
Co-authored-by: wendongdi <[email protected]>
---
.../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
