Multi-AZ な ELB 下の Shibboleth IdP on EC2

AWS で ELB とその下の EC2 を共に Multi-AZ にしている。
そんな環境で Shibboleth IdP を立ててるんだけど、どうやら ELB の IP アドレスが
DNS に 2 つ(Multi-AZ分)登録されていてラウンドロビンしてしまっている。

すると、Shibboleth IdP は以下のエラーを吐き認証が通らない。

Client sent a cookie from address 192.168.1.15 but the cookie was issued to address 192.168.2.17 

ELB がソース IP を自身のものに書き換えてしまうのは知っていたが、これが Multi-AZ
だとアクセスする毎に変わってしまう。DNS のラウンドロビンのせいで。

tomcat の server.xml の Engine タグ内に以下の設定を入れて回避。

      <Valve
          className="org.apache.catalina.valves.RemoteIpValve"
          internalProxies="192\.168\.\d+\.\d+"
          remoteIpHeader="x-forwarded-for"
          proxiesHeader="x-forwarded-by"
          protocolHeader="x-forwarded-proto" />

Apache の mod_remoteip で回避する方法もあるかも知れんが、今回は Cent6 であり、
Apache2.2 な世界なので標準で mod_remoteip が入っていないので tomcat で回避す
ることにした。

MySQL で sequence 作ってみたが... (再チャレンジ)

先日 MySQL の sequence をちょっと作ってみたが、あれからちょっと進化させてトランザクション外でも大丈夫なようにしてみた(SELECT FOR UPDATE はやめて、ワンライン update + last_insert_id() で済ますようにした)。

ただ、トランザクション内に nextval() や setval() を入れてしまうと、他のセッションで同一 sequence を nextval(), setval() しようとしたときにロックかかってしまう状態になる。

うーーんこのあたりなんとかならんか。トランザクション内でも特定のテーブルもしくは行だけはロックがかからないようにしたいのだが。

-- ### sequence テーブル定義 ###
CREATE TABLE sequence (
    sequence_name VARCHAR(50) NOT NULL,
    current_value BIGINT,
    increment BIGINT NOT NULL DEFAULT 1,
    start_value BIGINT,
    min_value BIGINT,
    max_value BIGINT,
    is_cycle BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (sequence_name)
);

-- ### sequence テーブルデフォルト定義 ###
DELIMITER //
CREATE TRIGGER sequence_default BEFORE INSERT ON sequence
FOR EACH ROW
BEGIN
    IF NEW.increment > 0 THEN
        IF NEW.max_value IS NULL THEN
            SET NEW.max_value = POWER(2, 63) - 1;
        END IF;
        IF NEW.min_value IS NULL THEN
            SET NEW.min_value = 1;
        END IF;
        IF NEW.start_value IS NULL THEN
            SET NEW.start_value = NEW.min_value;
        END IF;
    ELSE
        IF NEW.max_value IS NULL THEN
            SET NEW.max_value = -1;
        END IF;
        IF NEW.min_value IS NULL THEN
            SET NEW.min_value = POWER(-2, 63) - 1;
        END IF;
        IF NEW.start_value IS NULL THEN
            SET NEW.start_value = NEW.max_value;
        END IF;
    END IF;
END
//
-- ### シーケンス関数内部利用のテンポラリテーブル定義 ###
DELIMITER //
CREATE PROCEDURE _create_tmp_sequence ()
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_sequence (
        tmp_sequence_name VARCHAR(50) KEY,
        tmp_current_value BIGINT
    );
END
//
DELIMITER ;
-- ### currval() ###
DELIMITER //
CREATE FUNCTION currval (seq VARCHAR(50))
     RETURNS BIGINT
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     SQL SECURITY DEFINER
     COMMENT ''
BEGIN
     DECLARE mes varchar(128);
     DECLARE my_currval INT;
     CALL _create_tmp_sequence();
     SELECT tmp_current_value
     INTO my_currval
     FROM tmp_sequence
     WHERE tmp_sequence_name = seq;
 
     IF my_currval IS NULL THEN
         IF (SELECT 1 FROM sequence WHERE sequence_name = seq) THEN
             -- 一度も nextval() or setval() してない
             SET mes = concat('currval of sequence "',
                               seq,
                              '" is not yet defined in this session');
         ELSE
             -- そもそも sequence がない
             SET mes = concat('sequence "', seq, '" does not exist');
         END IF;
         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
     END IF;
     RETURN my_currval;
END
//
DELIMITER ;
-- ### setval() ###
DELIMITER //
CREATE FUNCTION setval (seq VARCHAR(50), val INTEGER)
    RETURNS BIGINT
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE my_max_value BIGINT;
    DECLARE my_sequence_name varchar(50);
    DECLARE mes VARCHAR(128);
    CALL _create_tmp_sequence();

    SELECT sequence_name, max_value
    INTO my_sequence_name, my_max_value
    FROM sequence
    WHERE sequence_name = seq;
    IF my_sequence_name IS NULL THEN
        SET mes = concat('sequence "', seq, '" does not exist');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    END IF;
    IF val > my_max_value THEN
        SET mes = concat('setval: over maximum value of sequence ',
                         seq,
                         '(', my_max_value,')');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    END IF;
    UPDATE sequence
    SET current_value = last_insert_id(val)
    WHERE sequence_name = seq;
    REPLACE INTO tmp_sequence VALUES (seq, last_insert_id());
    RETURN last_insert_id();
 END
//
DELIMITER ;
DELIMITER //
CREATE FUNCTION nextval (seq VARCHAR(50))
    RETURNS BIGINT
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
 BEGIN
    DECLARE mes VARCHAR(128);
    DECLARE my_next_value BIGINT;
    DECLARE my_max_value BIGINT;
    CALL _create_tmp_sequence();

    UPDATE sequence
    SET current_value =
        last_insert_id(
             CASE WHEN (current_value is NULL) OR
                       (is_cycle && (current_value > (max_value - increment)))
                      THEN start_value
                  ELSE current_value + increment
             END)
    WHERE sequence_name = seq;
    IF (SELECT ROW_COUNT()) != 1 THEN
        SET mes = concat('sequence "', seq, '" does not exist');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    END IF;
    SELECT last_insert_id(), max_value
    INTO my_next_value, my_max_value
    FROM sequence
    WHERE sequence_name = seq;
    IF my_next_value > my_max_value THEN
        SET mes = concat('nextval: reached maximum value of sequence ',
                         seq,
                         '(', my_max_value,')');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    END IF;
    REPLACE INTO tmp_sequence VALUES (seq, my_next_value);
    RETURN my_next_value;
 END
//
DELIMITER ;

-- ## sequence 定義 (create sequence みたいなもん)
-- デフォルト(値は PostgreSQL チック)で良い場合
INSERT INTO sequence (sequence_name) VALUES ('myseq1');
-- 2 ずつ increment の場合
INSERT INTO sequence (sequence_name, incremetn) VALUES ('myseq2', 2);
-- 1 ずつ increment(デフォルト) かつ start を 10 から, 最大値 9999, 最小値 10
INSERT INTO sequence (sequence_name, start_value, min_value, max_value)
  VALUES ('myseq3', 10, 10, 9999);

MySQL で sequence 作ってみたが... イマイチ...

※ 2015/02/08 追記: 以下に例示した sequence のエミュレーションは全然ダメ。使わないでください、って誰も使うわけないかw
※ 2015/02/08 の日記にちょっと進化させたやつを載せてますが、まだ少し難点あり。

MySQL で、複数の同時接続を考慮した sequence が必要だったので以下 URL を参考に作ってみた(like  PostgreSQL)。

参考: MySQL で シーケンス 機能実現, Emulating nextval() function to get sequence in MySQL

が、どうしても外側でトランザクションを併用しなければならないというダサい実装になってしまう。

つーのも MySQL の SELECT FOR UPDATE はトランザクション内でしかロックが効かない
(InnoDBでしか確認してないが)、というのと、Function 内でトランザクションが使えない
ということから、今回作ったやつだと以下のような感じで nextval() を実行しなければな
らない...

begin; select nextval('myseq'); commit;

うーーん、あまりにもダサい気がする。一応ソースを載せておくのでどなたかご指摘を
いただけますと助かります(電車の中でうんこ座りで書いたのでひどいコード... という
言い訳をしておこうw)。

(上記参考のjlakeさんのコードの一部を使わせてもらってますm(__)m)

-- ### sequence テーブル定義 ###
CREATE TABLE sequence (
    sequence_name VARCHAR(50) NOT NULL,
    current_value BIGINT,
    increment BIGINT NOT NULL DEFAULT 1,
    start_value BIGINT,
    min_value BIGINT,
    max_value BIGINT,
    is_cycle BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (sequence_name)
);

-- ### sequence テーブルデフォルト定義 ###
DELIMITER //
CREATE TRIGGER sequence_default BEFORE INSERT ON sequence
FOR EACH ROW
BEGIN
    IF NEW.increment > 0 THEN
        IF NEW.max_value IS NULL THEN
            SET NEW.max_value = POWER(2, 63) - 1;
        END IF;
        IF NEW.min_value IS NULL THEN
            SET NEW.min_value = 1;
        END IF;
        IF NEW.start_value IS NULL THEN
            SET NEW.start_value = NEW.min_value;
        END IF;
    ELSE
        IF NEW.max_value IS NULL THEN
            SET NEW.max_value = -1;
        END IF;
        IF NEW.min_value IS NULL THEN
            SET NEW.min_value = POWER(-2, 63) - 1;
        END IF;
        IF NEW.start_value IS NULL THEN
            SET NEW.start_value = NEW.max_value;
        END IF;
    END IF;
END
//
DELIMITER ;

-- ### シーケンス関数内部利用のテンポラリテーブル定義 ###
DELIMITER //
CREATE PROCEDURE _create_tmp_sequence ()
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_sequence (
        tmp_sequence_name VARCHAR(50) KEY,
        tmp_current_value INT
    );
END
//
DELIMITER ;

-- ### currval() ###
DELIMITER //
CREATE FUNCTION currval (seq VARCHAR(50))
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     SQL SECURITY DEFINER
     COMMENT ''
BEGIN
     DECLARE mes varchar(128);
     DECLARE my_currval INT;
     CALL _create_tmp_sequence();
     SELECT tmp_current_value
     INTO my_currval
     FROM tmp_sequence
     WHERE tmp_sequence_name = seq;

     IF my_currval IS NULL THEN
         IF (SELECT 1 FROM sequence WHERE sequence_name = seq) THEN
             -- 一度も nextval() or setval() してない
             SET mes = concat('currval of sequence "',
                               seq,
                              '" is not yet defined in this session');
         ELSE
             -- そもそも sequence がない
             SET mes = concat('sequence "', seq, '" does not exist');
         END IF;
         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
     END IF;
     RETURN my_currval;
END
//
DELIMITER ;

-- ### setval() ###
DELIMITER //
CREATE FUNCTION setval (seq VARCHAR(50), val INTEGER)
    RETURNS INTEGER
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE dummy INT;
    DECLARE mes VARCHAR(128);
    CALL _create_tmp_sequence();

    IF (SELECT 1 FROM sequence WHERE sequence_name = seq FOR UPDATE) THEN
        -- ロックして current_value の設定
        UPDATE sequence
        SET current_value = val
        WHERE sequence_name = seq;
    ELSE
        -- そもそも sequence がない
        SET mes = concat('sequence "', seq, '" does not exist');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    END IF;

    REPLACE INTO tmp_sequence VALUES (seq, val);
    RETURN val;
 END
//
DELIMITER ;

-- ### nextval() ###

DELIMITER //
CREATE FUNCTION nextval (seq VARCHAR(50))
    RETURNS INT
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
 BEGIN
    DECLARE my_sequence_name VARCHAR(50);
    DECLARE my_current_value BIGINT;
    DECLARE my_next_value BIGINT;
    DECLARE my_increment BIGINT;
    DECLARE my_min_value BIGINT;
    DECLARE my_max_value BIGINT;
    DECLARE my_start_value BIGINT;
    DECLARE my_cycle BOOLEAN;
    DECLARE mes VARCHAR(128);
    CALL _create_tmp_sequence();
    SELECT sequence_name, current_value, increment,
         min_value, max_value, is_cycle, start_value
    INTO my_sequence_name, my_current_value, my_increment,
         my_min_value, my_max_value, my_cycle, my_start_value
    FROM sequence
    WHERE sequence_name = seq
    FOR UPDATE;

    IF my_sequence_name IS NULL THEN
        -- そもそも sequence がない。
        SET mes = concat('sequence "', seq, '" does not exist');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    ELSEIF my_current_value IS NULL THEN
        -- sequence はあるが、まだ一度も使ったことがない -> start_value を設定
        SET my_next_value = my_start_value;
    ELSE
        -- increment 分加算
        SET my_next_value = my_current_value + my_increment;
    END IF;
    IF my_next_value > my_max_value THEN
        IF my_cycle THEN
            -- max_value を超えてしまったら min_value に戻す
            SET my_next_value = my_min_value;
        ELSE
            SET mes = concat('nextval: reached maximum value of sequence ',
                             seq,
                             '(', my_max_value,')');
   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
        END IF;
    END IF;
    UPDATE sequence
    SET current_value = my_next_value
    WHERE sequence_name = seq;
    REPLACE INTO tmp_sequence VALUES (seq, my_next_value);
    RETURN my_next_value;
 END
//
DELIMITER ;
-- デフォルト(値は PostgreSQL チック)で良い場合
INSERT INTO sequence (sequence_name) VALUES ('myseq1');
-- 2 ずつ increment の場合
INSERT INTO sequence (sequence_name, incremetn) VALUES ('myseq2', 2);
-- 1 ずつ increment(デフォルト) かつ start を 10 から, 最大値 9999, 最小値 10
INSERT INTO sequence (sequence_name, start_value, min_value, max_value)
  VALUES ('myseq3', 10, 10, 9999);

MySQL コネクション確認

show full processlist;