You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@dolphinscheduler.apache.org by ki...@apache.org on 2021/06/15 02:30:40 UTC
[dolphinscheduler] 01/12: [Fix-5581][SQL] Specific key was too long,
max key length is 767 bytes for varchar(256) in some mysql with
innodb_large_prefix=OFF (#5582)
This is an automated email from the ASF dual-hosted git repository.
kirs pushed a commit to branch json_split_two
in repository https://gitbox.apache.org/repos/asf/dolphinscheduler.git
commit 309deaefe8416f81314405ec6ef4cb25ffaed990
Author: Shiwen Cheng <ch...@gmail.com>
AuthorDate: Fri Jun 4 09:55:42 2021 +0800
[Fix-5581][SQL] Specific key was too long, max key length is 767 bytes for varchar(256) in some mysql with innodb_large_prefix=OFF (#5582)
---
sql/dolphinscheduler_mysql.sql | 12 +--
sql/dolphinscheduler_postgre.sql | 10 +-
.../1.3.6_schema/mysql/dolphinscheduler_ddl.sql | 2 +-
.../postgresql/dolphinscheduler_ddl.sql | 2 +-
.../1.3.7_schema/mysql/dolphinscheduler_ddl.sql | 119 +++++++++++++++++++++
.../1.3.7_schema/mysql/dolphinscheduler_dml.sql | 16 +++
.../postgresql/dolphinscheduler_ddl.sql | 106 ++++++++++++++++++
.../postgresql/dolphinscheduler_dml.sql | 16 +++
8 files changed, 270 insertions(+), 13 deletions(-)
diff --git a/sql/dolphinscheduler_mysql.sql b/sql/dolphinscheduler_mysql.sql
index e2866e0..5f2814c 100644
--- a/sql/dolphinscheduler_mysql.sql
+++ b/sql/dolphinscheduler_mysql.sql
@@ -345,7 +345,7 @@ DROP TABLE IF EXISTS `t_ds_datasource`;
CREATE TABLE `t_ds_datasource` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'key',
`name` varchar(64) NOT NULL COMMENT 'data source name',
- `note` varchar(256) DEFAULT NULL COMMENT 'description',
+ `note` varchar(255) DEFAULT NULL COMMENT 'description',
`type` tinyint(4) NOT NULL COMMENT 'data source type: 0:mysql,1:postgresql,2:hive,3:spark',
`user_id` int(11) NOT NULL COMMENT 'the creator id',
`connection_params` text NOT NULL COMMENT 'json connection params',
@@ -724,7 +724,7 @@ CREATE TABLE `t_ds_resources` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'key',
`alias` varchar(64) DEFAULT NULL COMMENT 'alias',
`file_name` varchar(64) DEFAULT NULL COMMENT 'file name',
- `description` varchar(256) DEFAULT NULL,
+ `description` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL COMMENT 'user id',
`type` tinyint(4) DEFAULT NULL COMMENT 'resource type,0:FILE，1:UDF',
`size` bigint(20) DEFAULT NULL COMMENT 'resource size',
@@ -751,14 +751,14 @@ CREATE TABLE `t_ds_schedules` (
`start_time` datetime NOT NULL COMMENT 'start time',
`end_time` datetime NOT NULL COMMENT 'end time',
`timezone_id` varchar(40) DEFAULT NULL COMMENT 'timezoneId',
- `crontab` varchar(256) NOT NULL COMMENT 'crontab description',
+ `crontab` varchar(255) NOT NULL COMMENT 'crontab description',
`failure_strategy` tinyint(4) NOT NULL COMMENT 'failure strategy. 0:end,1:continue',
`user_id` int(11) NOT NULL COMMENT 'user id',
`release_state` tinyint(4) NOT NULL COMMENT 'release state. 0:offline,1:online ',
`warning_type` tinyint(4) NOT NULL COMMENT 'Alarm type: 0 is not sent, 1 process is sent successfully, 2 process is sent failed, 3 process is sent successfully and all failures are sent',
`warning_group_id` int(11) DEFAULT NULL COMMENT 'alert group id',
`process_instance_priority` int(11) DEFAULT NULL COMMENT 'process instance priority：0 Highest,1 High,2 Medium,3 Low,4 Lowest',
- `worker_group` varchar(256) DEFAULT '' COMMENT 'worker group id',
+ `worker_group` varchar(64) DEFAULT '' COMMENT 'worker group id',
`create_time` datetime NOT NULL COMMENT 'create time',
`update_time` datetime NOT NULL COMMENT 'update time',
PRIMARY KEY (`id`)
@@ -832,7 +832,7 @@ DROP TABLE IF EXISTS `t_ds_tenant`;
CREATE TABLE `t_ds_tenant` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'key',
`tenant_code` varchar(64) DEFAULT NULL COMMENT 'tenant code',
- `description` varchar(256) DEFAULT NULL,
+ `description` varchar(255) DEFAULT NULL,
`queue_id` int(11) DEFAULT NULL COMMENT 'queue id',
`create_time` datetime DEFAULT NULL COMMENT 'create time',
`update_time` datetime DEFAULT NULL COMMENT 'update time',
@@ -897,7 +897,7 @@ CREATE TABLE `t_ds_user` (
DROP TABLE IF EXISTS `t_ds_worker_group`;
CREATE TABLE `t_ds_worker_group` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
- `name` varchar(256) NOT NULL COMMENT 'worker group name',
+ `name` varchar(255) NOT NULL COMMENT 'worker group name',
`addr_list` text NULL DEFAULT NULL COMMENT 'worker addr list. split by [,]',
`create_time` datetime NULL DEFAULT NULL COMMENT 'create time',
`update_time` datetime NULL DEFAULT NULL COMMENT 'update time',
diff --git a/sql/dolphinscheduler_postgre.sql b/sql/dolphinscheduler_postgre.sql
index f9299cd..3393938 100644
--- a/sql/dolphinscheduler_postgre.sql
+++ b/sql/dolphinscheduler_postgre.sql
@@ -243,7 +243,7 @@ DROP TABLE IF EXISTS t_ds_datasource;
CREATE TABLE t_ds_datasource (
id int NOT NULL ,
name varchar(64) NOT NULL ,
- note varchar(256) DEFAULT NULL ,
+ note varchar(255) DEFAULT NULL ,
type int NOT NULL ,
user_id int NOT NULL ,
connection_params text NOT NULL ,
@@ -590,7 +590,7 @@ CREATE TABLE t_ds_resources (
id int NOT NULL ,
alias varchar(64) DEFAULT NULL ,
file_name varchar(64) DEFAULT NULL ,
- description varchar(256) DEFAULT NULL ,
+ description varchar(255) DEFAULT NULL ,
user_id int DEFAULT NULL ,
type int DEFAULT NULL ,
size bigint DEFAULT NULL ,
@@ -615,7 +615,7 @@ CREATE TABLE t_ds_schedules (
start_time timestamp NOT NULL ,
end_time timestamp NOT NULL ,
timezone_id varchar(40) default NULL ,
- crontab varchar(256) NOT NULL ,
+ crontab varchar(255) NOT NULL ,
failure_strategy int NOT NULL ,
user_id int NOT NULL ,
release_state int NOT NULL ,
@@ -686,7 +686,7 @@ DROP TABLE IF EXISTS t_ds_tenant;
CREATE TABLE t_ds_tenant (
id int NOT NULL ,
tenant_code varchar(64) DEFAULT NULL ,
- description varchar(256) DEFAULT NULL ,
+ description varchar(255) DEFAULT NULL ,
queue_id int DEFAULT NULL ,
create_time timestamp DEFAULT NULL ,
update_time timestamp DEFAULT NULL ,
@@ -754,7 +754,7 @@ create index version_index on t_ds_version(version);
DROP TABLE IF EXISTS t_ds_worker_group;
CREATE TABLE t_ds_worker_group (
id bigint NOT NULL ,
- name varchar(256) NOT NULL ,
+ name varchar(255) NOT NULL ,
addr_list text DEFAULT NULL ,
create_time timestamp DEFAULT NULL ,
update_time timestamp DEFAULT NULL ,
diff --git a/sql/upgrade/1.3.6_schema/mysql/dolphinscheduler_ddl.sql b/sql/upgrade/1.3.6_schema/mysql/dolphinscheduler_ddl.sql
index f4df77a..b126163 100644
--- a/sql/upgrade/1.3.6_schema/mysql/dolphinscheduler_ddl.sql
+++ b/sql/upgrade/1.3.6_schema/mysql/dolphinscheduler_ddl.sql
@@ -28,7 +28,7 @@ BEGIN
AND COLUMN_NAME ='ip_list')
THEN
ALTER TABLE t_ds_worker_group CHANGE COLUMN `ip_list` `addr_list` text;
- ALTER TABLE t_ds_worker_group MODIFY COLUMN `name` varchar(256) NOT NULL;
+ ALTER TABLE t_ds_worker_group MODIFY COLUMN `name` varchar(255) NOT NULL;
ALTER TABLE t_ds_worker_group ADD UNIQUE KEY `name_unique` (`name`);
END IF;
END;
diff --git a/sql/upgrade/1.3.6_schema/postgresql/dolphinscheduler_ddl.sql b/sql/upgrade/1.3.6_schema/postgresql/dolphinscheduler_ddl.sql
index b9744c3..e6470fd 100644
--- a/sql/upgrade/1.3.6_schema/postgresql/dolphinscheduler_ddl.sql
+++ b/sql/upgrade/1.3.6_schema/postgresql/dolphinscheduler_ddl.sql
@@ -25,7 +25,7 @@ BEGIN
THEN
ALTER TABLE t_ds_worker_group RENAME ip_list TO addr_list;
ALTER TABLE t_ds_worker_group ALTER COLUMN addr_list type text;
- ALTER TABLE t_ds_worker_group ALTER COLUMN name type varchar(256), ALTER COLUMN name SET NOT NULL;
+ ALTER TABLE t_ds_worker_group ALTER COLUMN name type varchar(255), ALTER COLUMN name SET NOT NULL;
ALTER TABLE t_ds_worker_group ADD CONSTRAINT name_unique UNIQUE (name);
END IF;
END;
diff --git a/sql/upgrade/1.3.7_schema/mysql/dolphinscheduler_ddl.sql b/sql/upgrade/1.3.7_schema/mysql/dolphinscheduler_ddl.sql
new file mode 100644
index 0000000..e714bae
--- /dev/null
+++ b/sql/upgrade/1.3.7_schema/mysql/dolphinscheduler_ddl.sql
@@ -0,0 +1,119 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+*/
+
+SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
+
+-- uc_dolphin_T_t_ds_datasource_R_note
+drop PROCEDURE if EXISTS uc_dolphin_T_t_ds_datasource_R_note;
+delimiter d//
+CREATE PROCEDURE uc_dolphin_T_t_ds_datasource_R_note()
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
+ WHERE TABLE_NAME='t_ds_datasource'
+ AND TABLE_SCHEMA=(SELECT DATABASE())
+ AND COLUMN_NAME ='note')
+ THEN
+ ALTER TABLE t_ds_datasource MODIFY COLUMN `note` varchar(255) DEFAULT NULL COMMENT 'description';
+ END IF;
+END;
+
+d//
+
+delimiter ;
+CALL uc_dolphin_T_t_ds_datasource_R_note;
+DROP PROCEDURE uc_dolphin_T_t_ds_datasource_R_note;
+
+-- uc_dolphin_T_t_ds_resources_R_description
+drop PROCEDURE if EXISTS uc_dolphin_T_t_ds_resources_R_description;
+delimiter d//
+CREATE PROCEDURE uc_dolphin_T_t_ds_resources_R_description()
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
+ WHERE TABLE_NAME='t_ds_resources'
+ AND TABLE_SCHEMA=(SELECT DATABASE())
+ AND COLUMN_NAME ='description')
+ THEN
+ ALTER TABLE t_ds_resources MODIFY COLUMN `description` varchar(255) DEFAULT NULL;
+ END IF;
+END;
+
+d//
+
+delimiter ;
+CALL uc_dolphin_T_t_ds_resources_R_description;
+DROP PROCEDURE uc_dolphin_T_t_ds_resources_R_description;
+
+-- uc_dolphin_T_t_ds_schedules_R_crontab
+drop PROCEDURE if EXISTS uc_dolphin_T_t_ds_schedules_R_crontab;
+delimiter d//
+CREATE PROCEDURE uc_dolphin_T_t_ds_schedules_R_crontab()
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
+ WHERE TABLE_NAME='t_ds_schedules'
+ AND TABLE_SCHEMA=(SELECT DATABASE())
+ AND COLUMN_NAME ='crontab')
+ THEN
+ ALTER TABLE t_ds_schedules MODIFY COLUMN `crontab` varchar(255) NOT NULL COMMENT 'crontab description';
+ ALTER TABLE t_ds_schedules MODIFY COLUMN `worker_group` varchar(64) DEFAULT '' COMMENT 'worker group id';
+ END IF;
+END;
+
+d//
+
+delimiter ;
+CALL uc_dolphin_T_t_ds_schedules_R_crontab;
+DROP PROCEDURE uc_dolphin_T_t_ds_schedules_R_crontab;
+
+-- uc_dolphin_T_t_ds_tenant_R_description
+drop PROCEDURE if EXISTS uc_dolphin_T_t_ds_tenant_R_description;
+delimiter d//
+CREATE PROCEDURE uc_dolphin_T_t_ds_tenant_R_description()
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
+ WHERE TABLE_NAME='t_ds_tenant'
+ AND TABLE_SCHEMA=(SELECT DATABASE())
+ AND COLUMN_NAME ='description')
+ THEN
+ ALTER TABLE t_ds_tenant MODIFY COLUMN `description` varchar(255) DEFAULT NULL;
+ END IF;
+END;
+
+d//
+
+delimiter ;
+CALL uc_dolphin_T_t_ds_tenant_R_description;
+DROP PROCEDURE uc_dolphin_T_t_ds_tenant_R_description;
+
+-- uc_dolphin_T_t_ds_worker_group_R_name
+drop PROCEDURE if EXISTS uc_dolphin_T_t_ds_worker_group_R_name;
+delimiter d//
+CREATE PROCEDURE uc_dolphin_T_t_ds_worker_group_R_name()
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
+ WHERE TABLE_NAME='t_ds_worker_group'
+ AND TABLE_SCHEMA=(SELECT DATABASE())
+ AND COLUMN_NAME ='name')
+ THEN
+ ALTER TABLE t_ds_worker_group MODIFY COLUMN `name` varchar(255) NOT NULL COMMENT 'worker group name';
+ END IF;
+END;
+
+d//
+
+delimiter ;
+CALL uc_dolphin_T_t_ds_worker_group_R_name;
+DROP PROCEDURE uc_dolphin_T_t_ds_worker_group_R_name;
diff --git a/sql/upgrade/1.3.7_schema/mysql/dolphinscheduler_dml.sql b/sql/upgrade/1.3.7_schema/mysql/dolphinscheduler_dml.sql
new file mode 100644
index 0000000..38964cc
--- /dev/null
+++ b/sql/upgrade/1.3.7_schema/mysql/dolphinscheduler_dml.sql
@@ -0,0 +1,16 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+*/
\ No newline at end of file
diff --git a/sql/upgrade/1.3.7_schema/postgresql/dolphinscheduler_ddl.sql b/sql/upgrade/1.3.7_schema/postgresql/dolphinscheduler_ddl.sql
new file mode 100644
index 0000000..8b20466
--- /dev/null
+++ b/sql/upgrade/1.3.7_schema/postgresql/dolphinscheduler_ddl.sql
@@ -0,0 +1,106 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+*/
+
+-- uc_dolphin_T_t_ds_datasource_A_note
+delimiter d//
+CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_datasource_A_note() RETURNS void AS $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
+ WHERE TABLE_NAME='t_ds_datasource'
+ AND COLUMN_NAME ='note')
+ THEN
+ ALTER TABLE t_ds_datasource ALTER COLUMN note type varchar(255);
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+d//
+
+delimiter ;
+SELECT uc_dolphin_T_t_ds_datasource_A_note();
+DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_datasource_A_note();
+
+-- uc_dolphin_T_t_ds_resources_A_description
+delimiter d//
+CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_resources_A_description() RETURNS void AS $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
+ WHERE TABLE_NAME='t_ds_resources'
+ AND COLUMN_NAME ='description')
+ THEN
+ ALTER TABLE t_ds_resources ALTER COLUMN description type varchar(255);
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+d//
+
+delimiter ;
+SELECT uc_dolphin_T_t_ds_resources_A_description();
+DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_resources_A_description();
+
+-- uc_dolphin_T_t_ds_schedules_A_crontab
+delimiter d//
+CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_schedules_A_crontab() RETURNS void AS $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
+ WHERE TABLE_NAME='t_ds_schedules'
+ AND COLUMN_NAME ='crontab')
+ THEN
+ ALTER TABLE t_ds_schedules ALTER COLUMN crontab type varchar(255);
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+d//
+
+delimiter ;
+SELECT uc_dolphin_T_t_ds_schedules_A_crontab();
+DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_schedules_A_crontab();
+
+-- uc_dolphin_T_t_ds_tenant_A_description
+delimiter d//
+CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_tenant_A_description() RETURNS void AS $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
+ WHERE TABLE_NAME='t_ds_tenant'
+ AND COLUMN_NAME ='description')
+ THEN
+ ALTER TABLE t_ds_tenant ALTER COLUMN description type varchar(255);
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+d//
+
+delimiter ;
+SELECT uc_dolphin_T_t_ds_tenant_A_description();
+DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_tenant_A_description();
+
+-- uc_dolphin_T_t_ds_worker_group_A_name
+delimiter d//
+CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_worker_group_A_name() RETURNS void AS $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
+ WHERE TABLE_NAME='t_ds_worker_group'
+ AND COLUMN_NAME ='name')
+ THEN
+ ALTER TABLE t_ds_worker_group ALTER COLUMN name type varchar(255);
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+d//
+
+delimiter ;
+SELECT uc_dolphin_T_t_ds_worker_group_A_name();
+DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_worker_group_A_name();
diff --git a/sql/upgrade/1.3.7_schema/postgresql/dolphinscheduler_dml.sql b/sql/upgrade/1.3.7_schema/postgresql/dolphinscheduler_dml.sql
new file mode 100644
index 0000000..38964cc
--- /dev/null
+++ b/sql/upgrade/1.3.7_schema/postgresql/dolphinscheduler_dml.sql
@@ -0,0 +1,16 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+*/
\ No newline at end of file