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 <[email protected]>
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
