华为idme编码集问题启动不了解决方案

华为idme编码集问题启动不了解决方案

1. mdm启动不了

该条执行失败。

解决方案:

DROP PROCEDURE IF EXISTS AddUniqueConstraintWhenTableExist;
DROP PROCEDURE IF EXISTS DropUniqueConstraintWhenExist;
DROP PROCEDURE IF EXISTS DropUniqueConstraint;

DELIMITER //
CREATE PROCEDURE AddUniqueConstraintWhenTableExist
( IN tableName varchar(255),
  IN indexName varchar(255),
  IN columnName varchar(255)
)
BEGIN
    IF exists(select 1 from information_schema.COLUMNS where TABLE_SCHEMA = DATABASE() and TABLE_NAME = tableName and COLUMN_NAME = columnName)
        AND NOT EXISTS(select 1 from information_schema.TABLE_CONSTRAINTS where TABLE_SCHEMA = DATABASE() and TABLE_NAME = tableName and CONSTRAINT_NAME = indexName)
    THEN
        set @psql = concat('alter table ',tableName,' add constraint ',indexName,' unique(`',columnName,'`)');
PREPARE stmt FROM @psql;
EXECUTE stmt;
DEALLOCATE prepare stmt;
END IF;
end //
delimiter ;

DELIMITER //
CREATE PROCEDURE DropUniqueConstraintWhenExist
( IN tableName varchar(255),
  IN indexName varchar(255)
)
BEGIN
    IF exists(select 1 from information_schema.TABLE_CONSTRAINTS where TABLE_SCHEMA = DATABASE() and TABLE_NAME = tableName and CONSTRAINT_NAME = indexName and CONSTRAINT_TYPE = 'UNIQUE') THEN
        set @psql = concat('alter table ',tableName,' drop index ',indexName);
PREPARE stmt FROM @psql;
EXECUTE stmt;
DEALLOCATE prepare stmt;
END IF;
end //
delimiter ;

DELIMITER //
CREATE PROCEDURE DropUniqueConstraint
( IN tableName varchar(255),
  IN indexName varchar(255),
  In columnName varchar(255)
)

BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE constraintName varchar(255);
    DECLARE uniqueCursor CURSOR FOR (select KEY_COLUMN_USAGE.constraint_name from information_schema.KEY_COLUMN_USAGE  where TABLE_SCHEMA = DATABASE() and TABLE_NAME = tableName and COLUMN_NAME = columnName and CONSTRAINT_NAME like 'UK_%');
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
call DropUniqueConstraintWhenExist(tableName,indexName);
open uniqueCursor;
REPEAT
FETCH uniqueCursor into constraintName;
call DropUniqueConstraintWhenExist(tableName,constraintName);
until done end repeat;
CLOSE uniqueCursor;
end //
delimiter ;

将出错的flyway条目删除，重启mdm微服务

2. qm, les启动不了

解决方案:

DROP PROCEDURE IF EXISTS fresh_layout_history_data;
DROP PROCEDURE IF EXISTS fresh_search_relation_index_entity_data;
DROP PROCEDURE IF EXISTS fresh_entity_location_attached_data;


CREATE DEFINER=`root`@`%` PROCEDURE `fresh_layout_history_data`(IN old_attr_name VARCHAR(128), IN new_attr_name VARCHAR(128), IN entity_name VARCHAR(64), IN holder_type VARCHAR(100))
BEGIN
    -- 布局表未初始化时不做处理
    IF (SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE `TABLE_NAME` = 'xdm_attributelayout' AND `TABLE_SCHEMA` = database()) > 0 THEN
UPDATE xdm_attributelayout
SET attributeNameEn = new_attr_name
WHERE attributeNameEn = old_attr_name AND entityNameEn = entity_name AND extensionFlag = 0 AND attributeHolderType = holder_type;
UPDATE xdm_attributelayout
SET attributeNameEn = CONCAT(new_attr_name, '.Name') collate utf8mb4_general_ci
WHERE attributeNameEn = CONCAT(old_attr_name, '.Name') collate utf8mb4_general_ci AND entityNameEn = entity_name AND extensionFlag = 0 AND attributeHolderType = holder_type;
END IF;
END





CREATE DEFINER=`root`@`%` PROCEDURE `fresh_layout_history_data`(IN old_attr_name VARCHAR(128), IN new_attr_name VARCHAR(128), IN entity_name VARCHAR(64), IN holder_type VARCHAR(100))
BEGIN
    -- 布局表未初始化时不做处理
    IF (SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE `TABLE_NAME` = 'xdm_attributelayout' AND `TABLE_SCHEMA` = database()) > 0 THEN
UPDATE xdm_attributelayout
SET attributeNameEn = new_attr_name
WHERE attributeNameEn = old_attr_name AND entityNameEn = entity_name AND extensionFlag = 0 AND attributeHolderType = holder_type;
UPDATE xdm_attributelayout
SET attributeNameEn = CONCAT(new_attr_name, '.Name') collate utf8mb4_general_ci
WHERE attributeNameEn = CONCAT(old_attr_name, '.Name') collate utf8mb4_general_ci AND entityNameEn = entity_name AND extensionFlag = 0 AND attributeHolderType = holder_type;
END IF;
END



CREATE DEFINER=`root`@`%` PROCEDURE `fresh_search_relation_index_entity_data`(IN old_attr_name VARCHAR(128), IN new_attr_name VARCHAR(128), IN entity_name VARCHAR(64))
BEGIN
	-- 搜索服务表未初始化时不做处理
	IF (SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE `TABLE_NAME` = 'xdm_xdmsearchrelationindexentity' AND `TABLE_SCHEMA` = database()) > 0 THEN
-- 实体列名发生变化时刷新列名
UPDATE xdm_xdmsearchrelationindexentity
SET attrName = new_attr_name collate utf8mb4_general_ci, attrDesc = new_attr_name collate utf8mb4_general_ci
WHERE dmVersionNameEn = entity_name AND attrName = old_attr_name AND isExtendAttr = 0;
-- 实体列名发生变化时，刷新扩展模型的列名
UPDATE xdm_xdmsearchrelationindexentity
SET attrName = new_attr_name collate utf8mb4_general_ci, attrDesc = new_attr_name collate utf8mb4_general_ci
WHERE attrName = old_attr_name AND isExtendAttr = 0 AND (dmVersionNameEn, _tenantId) IN (SELECT child.nameEn, child._tenantId FROM xdm_typedefinition child LEFT JOIN xdm_typedefinition parent ON parent.ID = child._parentNodeId WHERE parent.nameEn = entity_name);
END IF;
END





CREATE DEFINER=`root`@`%` PROCEDURE `fresh_entity_location_attached_data`(IN old_attr_name VARCHAR(128), IN new_attr_name VARCHAR(128), IN entity_name VARCHAR(64))
BEGIN
IF (SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE `TABLE_NAME` = 'xdm_typedefinition' AND `TABLE_SCHEMA` = database()) > 0 THEN
-- 刷新当前实体的locationAttached，扩展模型不用刷，从父上获取
UPDATE xdm_typedefinition
SET locationAttached = new_attr_name collate utf8mb4_general_ci
WHERE nameEn = entity_name AND locationAttached = old_attr_name;
END IF;
END

测试三个存储过程是否正常:

CALL fresh_layout_history_data('indexType','IndexType','XDMSearchColumnEntity','SINGLE');
CALL fresh_search_relation_index_entity_data('dataInitRecord','DataInitRecord','XDMRuntimeDataInitDetail');
CALL fresh_entity_location_attached_data('descriptionEn','DescriptionEn','LifecycleState');

将出错的flyway条目删除，重启qm, les 微服务