将休眠生成器值从增量更改为本机

2024-05-08 • 问答

问题：我将1个服务器的1 db设置扩展为2个服务器的1 db设置。我运行Spring boot并作为ORM休眠。每个表当前都带有以下注释：

@Id
@GeneratedValue(generator = "increment")
@GenericGenerator(name = "increment",strategy = "increment")
@Column(name = "id")

ddl的示例可能是这样的：

create table drugs

(id                 bigint               not null
    primary key,....)

当运行2台服务器时，这会产生刷新错误，因为每个应用服务器将仅在初始化时检查当前索引，然后盲目增加ID。

我目前的做法： 为了避免这种情况，我希望DB设置密钥并使其休眠以实现“本机”策略，并为此向所有就绪的现有PRIMARY KEYS添加AUTO INCREMENT。

问题在于，对于每个表都有多个外键约束。所以我尝试了To add Auto increment while running SET FOREIGN KEY CHECK = 0

LOCK TABLES
drugs WRITE;
SET FOREIGN_KEY_CHECKS = 0;
ALTER table drugs DROP PRIMARY KEY;
ALTER TABLE drugs MODIFY id bigint PRIMARY KEY AUTO_INCREMENT NOT NULL ;

SET FOREIGN_KEY_CHECKS = 1;
UNLOCK TABLES ;

但是得到错误

（错误号：150“外键约束格式不正确”）

我愿意接受其他想法来解决此问题或解决问题的方法：）

解决了该问题。我完成了5个步骤：

为给定的主键保存每个外键约束
消除上述限制
删除主键
应用主键和auto_increment
再次应用每个外键

CREATE OR REPLACE PROCEDURE alter_primary_key(P_SCHEMA VARCHAR(64),P_TABLE VARCHAR(64),P_COLUMN VARCHAR(64)) MODIFIES SQL DATA
BEGIN
    DECLARE V_CONSTRAINT_SCHEMA VARCHAR(64);
    DECLARE V_TABLE_NAME VARCHAR(64);
    DECLARE V_CONSTRAINT_NAME VARCHAR(64);
    DECLARE V_COLUMN_NAME VARCHAR(64);
    DECLARE V_REFERENCED_TABLE_SCHEMA VARCHAR(64);
    DECLARE V_REFERENCED_TABLE_NAME VARCHAR(64);
    DECLARE V_REFERENCED_COLUMN_NAME VARCHAR(64);
    DECLARE C CURSOR FOR SELECT CONSTRAINT_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME,REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM foreign_keys;

    CREATE OR REPLACE TEMPORARY TABLE magi.foreign_keys (CONSTRAINT_SCHEMA VARCHAR(64),TABLE_NAME VARCHAR(64),CONSTRAINT_NAME VARCHAR(64),COLUMN_NAME VARCHAR(64),REFERENCED_TABLE_SCHEMA VARCHAR(64),REFERENCED_TABLE_NAME VARCHAR(64),REFERENCED_COLUMN_NAME VARCHAR(64));
    INSERT INTO magi.foreign_keys (CONSTRAINT_SCHEMA,REFERENCED_COLUMN_NAME)
    SELECT CONSTRAINT_SCHEMA,REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE
    WHERE REFERENCED_TABLE_SCHEMA = P_SCHEMA AND REFERENCED_TABLE_NAME = P_TABLE AND REFERENCED_COLUMN_NAME = P_COLUMN;


    OPEN C;
    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        read_loop: LOOP
            FETCH C INTO V_CONSTRAINT_SCHEMA,V_TABLE_NAME,V_CONSTRAINT_NAME,V_COLUMN_NAME,V_REFERENCED_TABLE_SCHEMA,V_REFERENCED_TABLE_NAME,V_REFERENCED_COLUMN_NAME;
            IF done THEN
                LEAVE read_loop;
            END IF;
            SET @STMT = CONCAT('ALTER TABLE ',V_CONSTRAINT_SCHEMA,'.',' DROP FOREIGN KEY ',V_CONSTRAINT_NAME);
            PREPARE STMT FROM @STMT;
            EXECUTE STMT;
        END LOOP;
    END;
    CLOSE C;


    SET @STMT = CONCAT('ALTER TABLE ',P_SCHEMA,P_TABLE,' DROP PRIMARY KEY');
    PREPARE STMT FROM @STMT;
    EXECUTE STMT;

#     SET @STMT = CONCAT('ALTER TABLE ',' MODIFY COLUMN ',P_COLUMN,' bigint AUTO_INCREMENT NOT NULL PRIMARY KEY');
    SET @STMT = CONCAT('ALTER TABLE ',' bigint AUTO_INCREMENT PRIMARY KEY');
    PREPARE STMT FROM @STMT;
    EXECUTE STMT;


    OPEN C;
        BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        read_loop2: LOOP
            FETCH C INTO V_CONSTRAINT_SCHEMA,V_REFERENCED_COLUMN_NAME;
            IF done THEN
                LEAVE read_loop2;
            END IF;
            SET @STMT = CONCAT('ALTER TABLE ',' ADD CONSTRAINT ',' FOREIGN KEY (',') REFERENCES ',' (',V_REFERENCED_COLUMN_NAME,')');
            PREPARE STMT FROM @STMT;
            EXECUTE STMT;
        END LOOP;
    END;
    CLOSE C;

END

CALL alter_primary_key('mydatabase','mytable','id');

将休眠生成器值从增量更改为本机

youbaio 回答：将休眠生成器值从增量更改为本机

大家都在问