[GitHub] [dolphinscheduler] caishunfeng commented on a change in pull request #8595: [Bug] [dolphinscheduler-sql] create index if not exists #8594

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

caishunfeng commented on a change in pull request #8595:
URL: https://github.com/apache/dolphinscheduler/pull/8595#discussion_r827554814



##########
File path: dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/mysql/dolphinscheduler_ddl.sql
##########
@@ -15,15 +15,42 @@
  * limitations under the License.
 */
 
-ALTER TABLE `t_ds_task_instance` ADD INDEX `idx_code_version` (`task_code`, `task_definition_version`) USING BTREE;
+/************************************
+ * Procedure
+ ************************************/
+delimiter d//
+
+DROP PROCEDURE IF EXISTS create_index_if_not_exists d//
+CREATE PROCEDURE create_index_if_not_exists (
+    IN tableName varchar(128),
+    IN indexName varchar(128),
+    IN indexColumns varchar(128)
+)
+BEGIN
+    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND table_name = tableName AND index_name = indexName)
+    THEN
+        SET @sqlstmt = CONCAT('ALTER TABLE `', tableName , '` ADD KEY `', indexName, '` (', indexColumns, ') USING BTREE');
+        PREPARE stmt FROM @sqlstmt;
+        EXECUTE stmt;
+        DEALLOCATE PREPARE stmt;
+    END IF;
+END;
+
+d//
+delimiter ;
+
+/************************************
+ * DDL
+ ************************************/
+call create_index_if_not_exists('t_ds_task_instance', 'idx_code_version', 'task_code, task_definition_version');
 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;

Review comment:
       Will there be the same problem?




-- 
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