This is an automated email from the ASF dual-hosted git repository.
dahn pushed a commit to branch 4.18
in repository https://gitbox.apache.org/repos/asf/cloudstack.git
The following commit(s) were added to refs/heads/4.18 by this push:
new 0cbe77024a8 Fix foreign key constraints and the mysql procedure that
is used (#7381)
0cbe77024a8 is described below
commit 0cbe77024a82cebf8734ed2a619eaa2ee8b3a602
Author: Harikrishna <[email protected]>
AuthorDate: Thu Jul 6 12:13:49 2023 +0530
Fix foreign key constraints and the mysql procedure that is used (#7381)
---
.../cloud/upgrade/dao/DatabaseAccessObject.java | 11 ++++++++
.../java/com/cloud/upgrade/dao/DbUpgradeUtils.java | 5 +++-
.../com/cloud/upgrade/dao/Upgrade41800to41810.java | 29 ++++++++++++++++++++++
.../resources/META-INF/db/schema-41720to41800.sql | 14 -----------
4 files changed, 44 insertions(+), 15 deletions(-)
diff --git
a/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
b/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
index 5d0edddb02e..21d5a205a09 100644
---
a/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
+++
b/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
@@ -26,6 +26,17 @@ public class DatabaseAccessObject {
private static Logger s_logger =
Logger.getLogger(DatabaseAccessObject.class);
+ public void addForeignKey(Connection conn, String tableName, String
tableColumn, String foreignTableName, String foreignColumnName) {
+ String addForeignKeyStmt = String.format("ALTER TABLE `cloud`.`%s` ADD
CONSTRAINT `fk_%s__%s` FOREIGN KEY `fk_%s__%s`(`%s`) REFERENCES `%s`(`%s`)",
tableName, tableName, tableColumn, tableName, tableColumn, tableColumn,
foreignTableName, foreignColumnName);
+ try(PreparedStatement pstmt =
conn.prepareStatement(addForeignKeyStmt);)
+ {
+ pstmt.executeUpdate();
+ s_logger.debug(String.format("Foreign key is added successfully
from the table %s", tableName));
+ } catch (SQLException e) {
+ s_logger.error("Ignored SQL Exception when trying to add foreign
key on table " + tableName + " exception: " + e.getMessage());
+ }
+ }
+
public void dropKey(Connection conn, String tableName, String key, boolean
isForeignKey)
{
String alter_sql_str;
diff --git
a/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
b/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
index 38ca5c9c272..02dad6250dc 100644
--- a/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
+++ b/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
@@ -23,7 +23,10 @@ public class DbUpgradeUtils {
private static DatabaseAccessObject dao = new DatabaseAccessObject();
- public static void dropKeysIfExist(Connection conn, String tableName,
List<String> keys, boolean isForeignKey) {
+ public static void addForeignKey(Connection conn, String tableName, String
tableColumn, String foreignTableName, String foreignColumnName) {
+ dao.addForeignKey(conn, tableName, tableColumn, foreignTableName,
foreignColumnName);
+ }
+ public static void dropKeysIfExist(Connection conn, String tableName,
List<String> keys, boolean isForeignKey) {
for (String key : keys) {
dao.dropKey(conn, tableName, key, isForeignKey);
}
diff --git
a/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41800to41810.java
b/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41800to41810.java
index c4fbeb73eb2..ab493da8cd8 100644
--- a/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41800to41810.java
+++ b/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41800to41810.java
@@ -22,6 +22,8 @@ import org.apache.log4j.Logger;
import java.io.InputStream;
import java.sql.Connection;
+import java.util.ArrayList;
+import java.util.List;
public class Upgrade41800to41810 implements DbUpgrade,
DbUpgradeSystemVmTemplate {
final static Logger LOG = Logger.getLogger(Upgrade41800to41810.class);
@@ -55,6 +57,7 @@ public class Upgrade41800to41810 implements DbUpgrade,
DbUpgradeSystemVmTemplate
@Override
public void performDataMigration(Connection conn) {
+ fixForeignKeyNames(conn);
}
@Override
@@ -82,4 +85,30 @@ public class Upgrade41800to41810 implements DbUpgrade,
DbUpgradeSystemVmTemplate
throw new CloudRuntimeException("Failed to find / register
SystemVM template(s)");
}
}
+
+ private void fixForeignKeyNames(Connection conn) {
+ //Alter foreign key name for user_vm table from fk_user_data_id to
fk_user_vm__user_data_id (if exists)
+ List<String> keys = new ArrayList<String>();
+ keys.add("fk_user_data_id");
+ keys.add("fk_user_vm__user_data_id");
+ DbUpgradeUtils.dropKeysIfExist(conn, "cloud.user_vm", keys, true);
+ DbUpgradeUtils.dropKeysIfExist(conn, "cloud.user_vm", keys, false);
+ DbUpgradeUtils.addForeignKey(conn, "user_vm", "user_data_id",
"user_data", "id");
+
+ //Alter foreign key name for vm_template table from fk_user_data_id to
fk_vm_template__user_data_id (if exists)
+ keys = new ArrayList<>();
+ keys.add("fk_user_data_id");
+ keys.add("fk_vm_template__user_data_id");
+ DbUpgradeUtils.dropKeysIfExist(conn, "cloud.vm_template", keys, true);
+ DbUpgradeUtils.dropKeysIfExist(conn, "cloud.vm_template", keys, false);
+ DbUpgradeUtils.addForeignKey(conn, "vm_template", "user_data_id",
"user_data", "id");
+
+ //Alter foreign key name for volumes table from fk_passphrase_id to
fk_volumes__passphrase_id (if exists)
+ keys = new ArrayList<>();
+ keys.add("fk_passphrase_id");
+ keys.add("fk_volumes__passphrase_id");
+ DbUpgradeUtils.dropKeysIfExist(conn, "cloud.volumes", keys, true);
+ DbUpgradeUtils.dropKeysIfExist(conn, "cloud.volumes", keys, false);
+ DbUpgradeUtils.addForeignKey(conn, "volumes",
"passphrase_id","passphrase", "id");
+ }
}
diff --git
a/engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql
b/engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql
index b8eee33cad7..2af6723c134 100644
--- a/engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql
+++ b/engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql
@@ -224,17 +224,6 @@ CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` (
BEGIN
DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl
= CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
-
--- Add foreign key procedure to link volumes to passphrase table
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY`;
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY` (
- IN in_table_name VARCHAR(200),
- IN in_foreign_table_name VARCHAR(200),
- IN in_foreign_column_name VARCHAR(200)
-)
-BEGIN
- DECLARE CONTINUE HANDLER FOR 1005,1826 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' ADD CONSTRAINT '); SET
@ddl = CONCAT(@ddl, 'fk_', in_foreign_table_name, '_', in_foreign_column_name);
SET @ddl = CONCAT(@ddl, ' FOREIGN KEY (', in_foreign_table_name, '_',
in_foreign_column_name, ')'); SET @ddl = CONCAT(@ddl, ' REFERENCES ',
in_foreign_table_name, '(', in_foreign_column_name, ')'); PREPARE stmt FROM
@ddl; EXECUTE stmt; DEALLOCATE PREPARE [...]
-
-- Add passphrase table
CREATE TABLE IF NOT EXISTS `cloud`.`passphrase` (
`id` bigint unsigned NOT NULL auto_increment,
@@ -244,7 +233,6 @@ CREATE TABLE IF NOT EXISTS `cloud`.`passphrase` (
-- Add passphrase column to volumes table
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.volumes', 'passphrase_id', 'bigint
unsigned DEFAULT NULL COMMENT "encryption passphrase id" ');
-CALL `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY`('cloud.volumes', 'passphrase', 'id');
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.volumes', 'encrypt_format',
'varchar(64) DEFAULT NULL COMMENT "encryption format" ');
-- Add encrypt column to disk_offering
@@ -653,11 +641,9 @@ CREATE TABLE IF NOT EXISTS `cloud`.`user_data` (
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.user_vm', 'user_data_id', 'bigint
unsigned DEFAULT NULL COMMENT "id of the user data" AFTER `user_data`');
-CALL `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY`('cloud.user_vm', 'user_data', 'id');
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.user_vm', 'user_data_details',
'mediumtext DEFAULT NULL COMMENT "value of the comma-separated list of
parameters" AFTER `user_data_id`');
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.vm_template', 'user_data_id',
'bigint unsigned DEFAULT NULL COMMENT "id of the user data"');
-CALL `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY`('cloud.vm_template', 'user_data',
'id');
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.vm_template',
'user_data_link_policy', 'varchar(255) DEFAULT NULL COMMENT "user data link
policy with template"');
-- Added userdata details to template
