AWS で Oracle Linux 8 を動かしたメモ

とある事情で AWS で Oracle Linux 8 を使ってみることになったのでそのメモ。

AMI

AMI は所有者を 131827586825 で検索するとずらずら出てきます。

参考:https://community.oracle.com/tech/apps-infra/discussion/4417739/launch-an-oracle-linux-instance-in-aws

aws ec2 describe-images \
  --executable-users all \
  --owners 131827586825 \
  --query 'Images[] | [].{ Name: Name, ImageId: ImageId } | sort_by(@, &Name)' \
  --output table
# -------------------------------------------------------------------
# |                         DescribeImages                          |
# +------------------------+----------------------------------------+
# |         ImageId        |                 Name                   |
# +------------------------+----------------------------------------+
# |  ami-352d1c34          |  OL5.11-i386-10-17-2014-ebs            |
# |  ami-3d2d1c3c          |  OL5.11-x86_64-10-17-2014-ebs          |
# |  ami-0a7a660b          |  OL6-i386-HVM-2015-1-20                |
# |  ami-ce5742cf          |  OL6-i386-PVM-2014-12-11               |
# |  ami-a6637da7          |  OL6-x86_64-HVM-2015-1-20              |
# |  ami-8c23308d          |  OL6-x86_64-PVM-2015-1-12              |
# |  ami-08021f6ff9bb5043e |  OL6.10-x86_64-HVM-2019-01-30          |
# |  ami-06aec29ea727b45c5 |  OL6.10-x86_64-PVM-2019-01-30          |
# |  ami-1011387e          |  OL6.7-i386-HVM-2015-12-04             |
# |  ami-8fefc6e1          |  OL6.7-i386-PVM-2015-12-06             |
# |  ami-3e133a50          |  OL6.7-x86_64-HVM-2015-12-04           |
# |  ami-6a1f3604          |  OL6.7-x86_64-PVM-2015-12-04           |
# |  ami-a022cec1          |  OL6.8-i386-HVM-2016-05-23             |
# |  ami-233cd042          |  OL6.8-i386-PVM-2016-05-23             |
# |  ami-e726ca86          |  OL6.8-x86_64-HVM-2016-05-23           |
# |  ami-6a3dd10b          |  OL6.8-x86_64-PVM-2016-05-24           |
# |  ami-9f8ca9f8          |  OL6.9-i386-HVM-2017-03-29             |
# |  ami-4882a72f          |  OL6.9-i386-PVM-2017-03-29             |
# |  ami-6982a70e          |  OL6.9-x86_64-HVM-2017-03-28           |
# |  ami-afc65dc9          |  OL6.9-x86_64-HVM-2018-01-10           |
# |  ami-c58faaa2          |  OL6.9-x86_64-PVM-2017-03-28           |
# |  ami-70c45f16          |  OL6.9-x86_64-PVM-2018-01-10           |
# |  ami-fce940fc          |  OL7.1-x86_64-HVM-2015-06-17           |
# |  ami-0ce8410c          |  OL7.1-x86_64-PVM-2015-04-06           |
# |  ami-65e0c80b          |  OL7.2-x86_64-HVM-2015-12-10           |
# |  ami-52e1c93c          |  OL7.2-x86_64-PVM-2015-12-10           |
# |  ami-de248ebf          |  OL7.3-x86_64-HVM-2016-11-09           |
# |  ami-1e11e778          |  OL7.4-x86_64-HVM-2017-08-07           |
# |  ami-3ad3485c          |  OL7.4-x86_64-HVM-2018-01-09           |
# |  ami-0918cd1ebaef69218 |  OL7.5-x86_64-HVM-2019-03-04           |
# |  ami-054e85339904efdef |  OL7.6-x86_64-HVM-2019-01-29           |
# |  ami-0950559ec2dfaacec |  OL7.7-x86_64-HVM-2020-02-13           |
# |  ami-0267b0d59b04d0de5 |  OL7.7-x86_64-HVM-2020-02-13-13-14     |
# |  ami-04e6facfaefdca6ec |  OL7.8-x86_64-HVM-2020-04-28           |
# |  ami-07174046d339a2557 |  OL7.9-x86_64-HVM-2020-12-07           |
# |  ami-0ef7c57e516f3dda7 |  OL8.1-x86_64-HVM-2019-12-11-15-50-57  |
# |  ami-0509f2707adadc9c5 |  OL8.1-x86_64-HVM-2020-01-15           |
# |  ami-05cf17097c9e7017f |  OL8.2-x86_64-HVM-2020-05-22           |
# |  ami-0d5bb29b78cc0af39 |  OL8.2-x86_64-HVM-2020-12-09           |
# |  ami-0ab635c88e06025fb |  OL8.3-x86_64-HVM-2020-12-10           |
# |  ami-0fbc34d0cb97e47b3 |  OL8.4-x86_64-HVM-2021-05-28           |
# |  ami-0afe0424c9fd49524 |  OL8.5-x86_64-HVM-2021-11-24           |
# +------------------------+----------------------------------------+

Oracle Linux 8.5 = OL8.5 なので ami-0afe0424c9fd49524 です。

os-release とか

インスタンス作成後に ssh ログインして色々見てみます。

cat /etc/redhat-release
# Red Hat Enterprise Linux release 8.5 (Ootpa)

cat /etc/oracle-release
# Oracle Linux Server release 8.5

cat /etc/os-release
# NAME="Oracle Linux Server"
# VERSION="8.5"
# ID="ol"
# ID_LIKE="fedora"
# VARIANT="Server"
# VARIANT_ID="server"
# VERSION_ID="8.5"
# PLATFORM_ID="platform:el8"
# PRETTY_NAME="Oracle Linux Server 8.5"
# ANSI_COLOR="0;31"
# CPE_NAME="cpe:/o:oracle:linux:8:5:server"
# HOME_URL="https://linux.oracle.com/"
# BUG_REPORT_URL="https://bugzilla.oracle.com/"
#
# ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8"
# ORACLE_BUGZILLA_PRODUCT_VERSION=8.5
# ORACLE_SUPPORT_PRODUCT="Oracle Linux"
# ORACLE_SUPPORT_PRODUCT_VERSION=8.5

ID_LIKErhel が無いですね、RHEL の ID_LIKE はおそらく fedora だけだろうので、それがそのままということなのでしょうけど。

なお、CentOS 8 なら rhel fedora だし AlmaLinux 8 なら rhel centos fedora です。

Kernel

uname -r
# 5.4.17-2136.300.7.el8uek.x86_64

カーネルのバージョンが RHEL8 と全然違います。いわゆる Unbreakable Enterprise Kernel(UEK) です。 Oracle Linux は UEK と RHEL 互換のカーネル(Red Hat Compatible Kernel(RHCK)) が利用できますが、デフォルトが UEK になっているようです。

そのままでもあまり問題無いかもしれませんが RHCK に変更します。見た感じ RHCK のカーネル自体がインストールされていないようなのでインストールします。

ls -l /boot/vmlinuz*
# -rwxr-xr-x. 1 root root 10348096 Oct  9 08:29 /boot/vmlinuz-5.4.17-2136.300.7.el8uek.x86_64

dnf install kernel
# いろいろ

ls -l /boot/vmlinuz*
# -rwxr-xr-x. 1 root root 10221104 Dec 22 02:32 /boot/vmlinuz-4.18.0-348.7.1.el8_5.x86_64
# -rwxr-xr-x. 1 root root 10348096 Oct  9 08:29 /boot/vmlinuz-5.4.17-2136.300.7.el8uek.x86_64

grubby でデフォルトカーネルを切り替えてリブートします。

参考:https://community.oracle.com/tech/apps-infra/discussion/4467791/oracle-linux-how-to-change-default-kernel

grubby --default-kernel
# /boot/vmlinuz-5.4.17-2136.300.7.el8uek.x86_64

grubby --info=ALL | grep -E "^kernel|^index"
# index=0
# kernel="/boot/vmlinuz-5.4.17-2136.300.7.el8uek.x86_64"
# index=1
# kernel="/boot/vmlinuz-4.18.0-348.7.1.el8_5.x86_64"

grubby --set-default /boot/vmlinuz-4.18.0-348.7.1.el8_5.x86_64
# The default is /boot/loader/entries/ec2536de9a9cc53fbc785b744042fb86-4.18.0-348.7.1.el8_5.x86_64.conf with index 1 and kernel /boot/vmlinuz-4.18.0-348.7.1.el8_5.x86_64

grubby --default-kernel
# /boot/vmlinuz-4.18.0-348.7.1.el8_5.x86_64

reboot

カーネルが RHEL 互換になっていることを確認します。

uname -r
# 4.18.0-348.7.1.el8_5.x86_64

EPEL

EPEL は Oracle で専用のリポジトリを持っているらしく epel-release のパッケージ名が異なるようです。

dnf install -y oracle-epel-release-el8

PowerTools

CentOS 8 で EPEL の moreutils をインストールしようとすると PowerTools リポジトリにある perl-IPC-Run が必要になります。

PowerTools はデフォルトで無効なので有効にしてインストールする必要があるのですが、

dnf install --enablerepo=powertools moreutils

Oracle Linux 8 だとリポジトリ名が異なっており PowerTools は ol8_codeready_builder です。

dnf install --enablerepo=ol8_codeready_builder moreutils

他のリポジトリ名の全然違っているため、リポジトリ名を指定してなにかしているなら注意が必要です。

dnf repolist --enablerepo=\* | column -t -s $'\t'
# repo id               repo name
# ol8_UEKR6             Latest Unbreakable Enterprise Kernel Release 6 for Oracle Linux 8 (x86_64)
# ol8_UEKR6_RDMA        Oracle Linux 8 UEK6 RDMA (x86_64)
# ol8_addons            Oracle Linux 8 Addons (x86_64)
# ol8_appstream         Oracle Linux 8 Application Stream (x86_64)
# ol8_baseos_latest     Oracle Linux 8 BaseOS Latest (x86_64)
# ol8_codeready_builder Oracle Linux 8 CodeReady Builder (x86_64) - Unsupported
# ol8_developer_EPEL    Oracle Linux 8 EPEL Packages for Development (x86_64)
# ol8_distro_builder    Oracle Linux 8 Distro Builder (x86_64) - Unsupported
# ol8_kvm_appstream     Oracle Linux 8 KVM Application Stream (x86_64)
# ol8_u0_baseos_base    Oracle Linux 8 BaseOS GA (x86_64)
# ol8_u1_baseos_base    Oracle Linux 8.1 BaseOS (x86_64)
# ol8_u2_baseos_base    Oracle Linux 8.2 BaseOS (x86_64)
# ol8_u3_baseos_base    Oracle Linux 8.3 BaseOS (x86_64)
# ol8_u4_baseos_base    Oracle Linux 8.4 BaseOS (x86_64)
# ol8_u5_baseos_base    Oracle Linux 8.5 BaseOS (x86_64)

参考:https://github.com/oracle/centos2ol/blob/1424a2417aaa383b5ebf0b77b9147584c85c9695/centos2ol.sh#L454

firewalld と iptables

Oracle Linux 8 の素の AMI だと firewalld が有効でした。CentOS 8 の素の AMI だと firewalld は無効だったと思うので、何も知らないと「何故か ssh 以外で接続できない・・」となり数分~十数分ぐらいの時間を無駄にします。

セキュリティグループやネットワークACLで十分ならサーバの firewalld は止めても良いでしょう。

systemctl stop firewalld
systemctl disable firewalld

がしかし、これだけでは不十分。なぜか iptables-services がインストールされています。

rpm -q iptables-services
# iptables-services-1.8.4-20.0.1.el8.x86_64

しかも有効です。

systemctl is-enabled iptables
# enabled

がしかし、firewalld で Conflicts に指定されているため iptables サービスは起動しません。

systemctl show firewalld -p Conflicts
# Conflicts=ip6tables.service nftables.service ebtables.service ipset.service shutdown.target iptables.service

systemctl status iptables
# * iptables.service - IPv4 firewall with iptables
#    Loaded: loaded (/usr/lib/systemd/system/iptables.service; disabled; vendor preset: disabled)
#    Active: inactive (dead)

がしかし、前述のように firewalld を無効にしてリブートすると、次は iptables サービスが起動してきます。

systemctl disable firewalld
reboot
.
.
.
systemctl status iptables
# * iptables.service - IPv4 firewall with iptables
#    Loaded: loaded (/usr/lib/systemd/system/iptables.service; enabled; vendor preset: disabled)
#    Active: active (exited) since Tue 2022-01-18 13:20:10 GMT; 25min ago
#     :

ので、firewalld と iptables の両方を無効にする必要があります。

systemctl stop firewalld
systemctl disable firewalld
systemctl disable iptables

tar: command not found

素の AMI だと tar すら入っていませんでした。dnf install @base とかで 普通入ってそうだけど入っていないもの を確認しておくと良いかもしれません。

EC2 でインスタンスを停止せずにルートボリュームを置き換えてみるメモ

少し前に AWS EC2 でルートボリュームをインスタンスを停止せずに交換可能になったと発表がありました。

え、停止せずにって、無停止でってこと? そんな馬鹿な(馬鹿な)

ので、試してみました。

適当にインスタンスを作って、適当にファイルを書いて、

echo 'this is test 1' > test.txt

マネジメントコンソールからスナップショットを作成した後、↑のファイルを上書きします。

echo 'this is test 2' > test.txt

マネジメントコンソールからルートボリュームの置き換えを行います。インスタンス詳細のストレージタブにあります。

f:id:ngyuki:20210520220406p:plain

スナップショットのところにフォーカスすると候補が表示されるので、↑で作成したスナップショットを選択します。

f:id:ngyuki:20210520220419p:plain

置き換えタスクを作成すると、前画面のストレージのところに表示されます。

f:id:ngyuki:20210520220429p:plain

ここが「成功」になれば置き換え完了です。

f:id:ngyuki:20210520220440p:plain

インスタンスに ssh して確認してみると・・

cat test.txt
#=> this is test 1
uptime
#=> 18:34:23 up 0 min,  1 user,  load average: 0.04, 0.01, 0.00

まあ当たり前でした。OS 的には再起動しています。ただ、インスタンスを停止→開始する必要がないので、associate-public-ip-address で付与された PublicIP が変わることもなければ、エフェメラルボリュームのデータもそのまま残ります(普通に停止→開始すると PublicIP は変わるしエフェメラルボリュームのデータも消える)。

さいごに

ルートボリュームをスナップショットで置き換えた後、同じインスタンスで再度ルートボリュームの置き換えをしようとしても、同じスナップショットは選択の候補に現れないようです。

ただ、置き換えるスナップショットを指定しなければルートボリュームの元となったスナップショットで置き換えられるため、直前に置き換えたスナップショットでもう一度置き換えたい場合は単にスナップショットを未指定で置き換えを実行すれば OK です(トライアンドエラーで試行錯誤するときにありそう)。

おそらく、置き換える対象のルートボリュームから作成されたスナップショットしか候補に表示されないようになっているのだと思います。置き換えるとルートボリュームが新しく作成されるので、以前のルートボリュームから作成されていたスナップショットはすべて候補にあがらなくなるのではないかと。

  • ルートボリューム A からスナップショット S を作成する
  • スナップショット S で ルートボリューム A を置き換える
    • 新たにボリューム B が作られて置き換えられる
  • B の置き換えでは S は B のスナップショットではないので候補にあがらない
    • ただし B のベースは S なのでスナップショットが未指定なら S で置き換えられる

なお、これはマネジメントコンソール上で選択の候補にあげられるかどうかだけのものなので、 スナップショットのIDを直接入力すれば候補に無いスナップショットでも置き換え可能だし、 AWS CLI でやる分にはぜんぜん関係ありません。

aws --profile hj-tky ec2 create-replace-root-volume-task \
  --instance-id i-04ff3fb484044124b --snapshot-id snap-0386ee904da7163a8
#=> {
#=>     "ReplaceRootVolumeTask": {
#=>         "ReplaceRootVolumeTaskId": "replacevol-0edbfcd8a8e82d2c2",
#=>         "InstanceId": "i-04ff3fb484044124b",
#=>         "TaskState": "pending",
#=>         "StartTime": "2021-05-17T18:52:29Z",
#=>         "Tags": []
#=>     }
#=> }

これ知らずにいると、なぜか置き換えできない・・とか思って嵌りそうです。

AWS Lambda で PHP のカスタムランタイムのコンテナイメージを作ってみるメモ

もう結構前のことですが、AWS Lambda に zip ではなくコンテナイメージでデプロイ出来るようになったとのことです。

ので試してみました。残骸はこちら

コンテナイメージは AWS から提供されている Amazon Linux2 ベースのものをカスタマイズすると簡単に作成できます。

あるいは Lambda Runtime Interface Clients を使えば自前の debian や alpine ベースのイメージでも実装できます。例えば NodeJS だと次の npm モジュールを使って実装できます。

ただ、AWS 提供のコンテナイメージも、Lambda Runtime Interface Clients も、PHP の実装はありません。PHPer なので PHP で試したいので、PHP 用のカスタムランタイムのコンテナイメージを作成しました。

PHP 用のカスタムランタイムのコンテナイメージ

カスタムランタイムは次の API を使用して実装します。

PHP 用に次のスクリプトを bootstrap というファイル名で作成しまいた。

<?php
require __DIR__ . '/../vendor/autoload.php';

new class () {
    private string $baseUrl;

    public function __construct()
    {
        $runtimeApi = getenv('AWS_LAMBDA_RUNTIME_API');
        if (strlen($runtimeApi) == 0) {
            throw new LogicException('Missing Runtime API Server configuration.');
        }

        $this->baseUrl = "http://$runtimeApi/2018-06-01";

        // CMD で渡されるコマンドライン引数からハンドラ名を得る
        $argv = $_SERVER['argv'];
        if (count($argv) < 2) {
            throw new LogicException('No handler specified.');
        }

        $appRoot = getcwd();
        $handlerName = $argv[1];

        // ハンドラ名をファイルとして require 戻り値をクロージャーとして得る
        $function = require "$appRoot/$handlerName";

        do {
            list ($invocationId, $payload) = $this->getNextRequest();
            try {
                // クロージャーを実行
                $response = $function($payload);
                $this->sendResponse($invocationId, $response);
            } catch (Throwable $ex) {
                $this->handleFailure($invocationId, $ex);
            }
        } while (true);
    }

    private function getNextRequest(): array
    {
        $url = "$this->baseUrl/runtime/invocation/next";
        $client = new GuzzleHttp\Client();
        $response = $client->get($url);
        $invocationId = $response->getHeaderLine('lambda-runtime-aws-request-id');
        $payload = json_decode($response->getBody(), true);
        return [$invocationId, $payload];
    }

    private function sendResponse(string $invocationId, $response): void
    {
        $url = "$this->baseUrl/runtime/invocation/$invocationId/response";
        $payload = json_encode($response, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $client = new GuzzleHttp\Client();
        $client->post($url, [
            'headers' => [
                'Content-Type' => 'application/json',
            ],
            'body' => $payload,
        ]);
    }

    private function handleFailure(string $invocationId, Throwable $exception): void
    {
        $url = "$this->baseUrl/runtime/invocation/$invocationId/error";
        $data = [
            'errorType' => get_class($exception),
            'errorMessage' => $exception->getMessage(),
        ];
        $payload = json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $client = new GuzzleHttp\Client();
        $client->post($url, [
            'headers' => [
                'Content-Type' => 'application/json',
            ],
            'body' => $payload,
        ]);
    }
};

コマンドラインで指定されたファイルを require してそのファイルが返したクロージャーを実行しています。require するファイルは次の要領で作成します。

<?php
return function ($payload) {

    // なにかする

    return $result;
};

イメージのための Dockerfile は次のような内容です。ENTRYPOINT で前述の bootstrap を実行し、CMD で require するファイル名を指定します。

FROM php:alpine

COPY --from=composer /usr/bin/composer /usr/bin/composer

COPY composer.* /home/app/
WORKDIR /home/app/
RUN composer install --prefer-dist --no-dev --no-progress -o -a

COPY bin/      /home/app/bin/
COPY handlers/ /home/app/handlers/

ENTRYPOINT [ "/home/app/bin/bootstrap" ]
RUN chmod +x /home/app/bin/bootstrap

CMD [ "handlers/index.php" ]

なお Lambda の定義時に ENTRYPOINT や CMD はオーバーライドできます。例えば次のように Terraform テンプレートで指定できます(この例では Dockerfile で指定してるとおりなので意味ないですが)。

resource "aws_lambda_function" "func" {
  function_name = "${var.tag}-func"
  role          = aws_iam_role.lambda.arn
  timeout       = 10
  memory_size   = 128
  package_type  = "Image"
  image_uri     = "${aws_ecr_repository.php.repository_url}:${var.docker_tag}"
  image_config {
    command           = ["handlers/index.php"]
    entry_point       = ["/home/app/bin/bootstrap"]
    working_directory = "/home/app/"
  }
}

なので Web アプリケーション用に作ったコンテナイメージに Lambda 用の bootstrap を仕込んでおき、デフォルトの ENTRYPOINT と CMD は Web アプリケーション用、Lambda の定義時には ENTRYPOINT や CMD を差し替える、といったことが可能です(Web アプリケーション用と Lambda 用に別のイメージを作る必要は無い)。

Lambda Runtime Interface Emulator でローカル実行

Lambda Runtime Interface Emulator でローカル環境でのテスト実行が可能です。

Lambda Runtime Interface Emulator は aws-lambda-rie というワンバイナリの実行可能ファイルです。あらかじめイメージに Lambda Runtime Interface Emulator を仕込んでおいても良いし、あるいは実行時にホストから差し込んでも OK です。

# ホストから aws-lambda-rie を差し込んで実行する例
wget https://github.com/aws/aws-lambda-runtime-interface-emulator/releases/latest/download/aws-lambda-rie
chmod +x ./aws-lambda-rie
docker build -t ore-no-image .
docker run --rm -p 9000:8080 \
    -v "$PWD/aws-lambda-rie:/aws-lambda-rie:ro" \
    --entrypoint /aws-lambda-rie ore-no-image \
    bin/bootstrap handlers/index.php

次のように Lambda が実行できます。

curl -XPOST "http://localhost:9000/2015-03-31/functions/function/invocations" -d '{"hello":"php"}'
#=> {"statusCode":200,"headers":{"Content-Type":"text/plain","x-php-version":"8.0.6"},"body":{"payload":{"hello":"php"}}}

さいごに

試しに PHP でやってみましたが、あえて AWS Lambda で PHP を動かすことはまず無いと思います。 (Lambda Runtime Interface Clients の PHP 実装も出来て Packagist で公開されれば話は別かもしれないですが)

Lambda 以外でも使用しているイメージを Lambda に流用したい、というケースが多いと思うので(例えば ECS Service で実行する Web アプリケーションのイメージを Lambda でも使いたい、とか)、 AWS 提供のイメージをカスタマイズするよりは、Docker Hub のオフィシャルのイメージから作成した独自のイメージに Lambda Runtime Interface Clients を入れる、という形で、構築が容易で素早く開始できる ECS Run Task のような感覚で使えそうです。

ParaTest で TEST_TOKEN を使って DB が絡むテストを並列実行する

だいぶ以前に Qiita に ParaTest で PHPUnit を並列実行する記事を書いていたのですが、

よく考えたら別に Docker なんて必要なくて、ひとつの MySQL インスタンスに複数のデータベースを作ればいいだけでした。なんとなく Docker を使ってみたかっただけじゃないかな、この時期。

また ParaTest の TEST_TOKEN を使えばそもそも変なハックしなくても普通に使用するデータベース切り替えられます。ParaTest の README にそのまんま書かれています。

https://github.com/paratestphp/paratest#test-token

<?php
if (getenv('TEST_TOKEN') !== false) {  // Using paratest
    $dbname = 'testdb_' . getenv('TEST_TOKEN');
} else {
    $dbname = 'testdb';
}

TEST_TOKEN が追加されたのが下記のリリースなので、記事を書いた当初はまだ TEST_TOKEN は実装されてなかったようです。

さっそく試してみました。使用したコード類は https://github.com/ngyuki-sandbox/php-paratest-with-db にあります。

普通にテストを実行すると10秒ぐらいかかります。

$ vendor/bin/phpunit
PHPUnit 9.5.4 by Sebastian Bergmann and contributors.

..........                                                        10 / 10 (100%)

Time: 00:10.232, Memory: 6.00 MB

OK (10 tests, 10 assertions)

普通に ParaTest を実行するとテストケースごとにデータベースのフィクスチャをざっくざく入れてるとめちゃめちゃ競合します。

$ vendor/bin/paratest -p 5
Running phpunit in 5 processes with /work/vendor/phpunit/phpunit/phpunit

Configuration read from /work/phpunit.xml.dist

EE.EE.EEEE                                                        10 / 10 (100%)

Time: 00:02.230, Memory: 6.00 MB

There were 8 errors:

1) Test\Sample0Test::test
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 't.PRIMARY'

...snip...

FAILURES!
Tests: 10, Assertions: 2, Errors: 8.

接続するデータベース名について、環境変数 TEST_TOKEN をデータベース名のサフィックスに追加します。

<?php
$host     = getenv('MYSQL_HOST');
$port     = getenv('MYSQL_PORT');
$username = getenv('MYSQL_USER');
$password = getenv('MYSQL_PASSWORD');
$dbname   = getenv('MYSQL_DATABASE');
$charset  = 'utf8mb4';

$token = getenv('TEST_TOKEN');
if ($token !== false) {
    $dbname .= $token;
}

TEST_TOKEN には ParaTest での実行時に並列実行されるプロセスごとに1から始まる連番が設定されます。

並列数の分だけデータベースを作成します。マイグレーション適用済のデータベースからダンプ→リストアが手っ取り早です。

# マイグレーションを実行 ... 使用するツールに応じて適切なコマンドに置き換え
cat database/*.sql | mysql -v "$MYSQL_DATABASE"

# データベースをダンプ
mysqldump -h "$MYSQL_HOST" "$MYSQL_DATABASE" -r dump.sql

# 連番サフィックスのデータベースへの権限を付与
mysql -v -e "grant all on \`${MYSQL_DATABASE}%\`.* to $MYSQL_USER@'%'"

# 連番サフィックスのデータベースを作成
seq 5 | xargs -P0 -i mysql -v -e 'create database if not exists test{}'

# 連番サフィックスのデータベースへダンプを流し込み
seq 5 | xargs -P0 -i mysql -e 'source dump.sql' 'test{}'

# テストを実行
vendor/bin/paratest -p 5

10秒かかっていたテストが2秒で終わるようになりました。

Running phpunit in 5 processes with /work/vendor/phpunit/phpunit/phpunit

Configuration read from /work/phpunit.xml.dist

..........                                                        10 / 10 (100%)

Time: 00:02.216, Memory: 6.00 MB

OK (10 tests, 10 assertions)

さいごに

かなり極端な例なので、実際の効果の程は実行環境や並列数によりけりです。手元の実案件で試してみたところ、DBを使うテストが4並列で半分ぐらいの時間で終わるようになりました。

AlmaLinux を KickStart とか CentOS からのインプレースとかで入れてみる

CentOS 8 の代替になるかもしれない AlmaLinux が GA リリースとのことなので、とりあえずインストールしてみました。

KiskStart でインストール

KVM with libvirt へ virt-install で KiskStart でインストールしてみます。 手順は CentOS 8 とまったく同じで、ISO やリポジトリの URL が異なるのみです。

イメージのための論理ボリュームを作成します。

lvcreate vg0 -L 5G  -n vm.almalinux-8

イメージをダウンロードしておきます。 ここでは AlmaLinux-8.3-x86_64-boot.iso をダウンロードしていますが AlmaLinux-8.3-x86_64-minimal.iso とかのほうがインストールは早くなります。

cd /iso
wget http://ftp.iij.ad.jp/pub/linux/almalinux/8.3/isos/x86_64/AlmaLinux-8.3-x86_64-boot.iso

キックスタートファイル ks.cfg を作成します。AlmaLinux-8.3-x86_64-boot.iso なので cdrom ではなく url を指定する必要があります。

#version=RHEL8

cmdline
url --url=http://ftp.iij.ad.jp/pub/linux/almalinux/8.3/BaseOS/x86_64/os/

lang en_US.UTF-8
keyboard --vckeymap=jp --xlayouts=jp
timezone Asia/Tokyo --isUtc

network --activate --device=link --noipv6 --bootproto=dhcp

zerombr
clearpart --all --initlabel
bootloader --location=mbr --boot-drive=vda
part / --label=root --grow --asprimary --ondisk=vda

rootpw --plaintext password
skipx
selinux --disabled
firewall --disabled
firstboot --disabled

services --enabled=chronyd --disabled=kdump,auditd

poweroff

%packages
@^minimal-environment
%end

%post --log=/root/ks-post.log
set -eux

# authorized_keys
mkdir -p /root/.ssh
curl -fsSL https://github.com/ngyuki.keys > /root/.ssh/authorized_keys
chown -R root: /root/.ssh
chmod 700 /root/.ssh
chmod 600 /root/.ssh/authorized_keys

%end

virt-install で OS をインストールします。

virt-install \
  --name almalinux-8 \
  --hvm \
  --virt-type kvm \
  --ram 2048 \
  --vcpus 1 \
  --arch x86_64 \
  --os-type linux \
  --os-variant rhel8.2 \
  --boot hd \
  --disk /dev/vg0/vm.almalinux-8 \
  --network network=default \
  --graphics none \
  --serial pty \
  --console pty \
  --location /iso/AlmaLinux-8.3-x86_64-boot.iso,kernel=isolinux/vmlinuz,initrd=isolinux/initrd.img \
  --initrd-inject ks.cfg \
  --extra-args "inst.ks=file:/ks.cfg inst.stage2=cdrom: console=ttyS0 net.ifnames=0 biosdevname=0" \
  --noreboot

ゲストを起動してコンソールに接続します。

virsh start almalinux-8
virsh console almalinux-8

root でログインして /etc/*-release を見てみます。

ll /etc/*-release
#=> -rw-r--r--. 1 root root 37 Mar 25 02:36 /etc/almalinux-release
#=> lrwxrwxrwx. 1 root root 17 Mar 25 02:36 /etc/centos-release -> almalinux-release
#=> lrwxrwxrwx. 1 root root 21 Mar 25 02:36 /etc/os-release -> ../usr/lib/os-release
#=> lrwxrwxrwx. 1 root root 17 Mar 25 02:36 /etc/redhat-release -> almalinux-release
#=> lrwxrwxrwx. 1 root root 17 Mar 25 02:36 /etc/system-release -> almalinux-release

cat /etc/almalinux-release
#=> AlmaLinux release 8.3 (Purple Manul)

AlmaLinux 8.3 がインストールできました。

CentOS からインプレースでマイグレーション

AlmaLinux のサイトに CentOS からインプレースでマイグレーションする方法が載っていたので試してみます。

とりあえずマイグレーション元の CentOS を作ります。

virt-builder centos-8.2 \
  --output /var/lib/libvirt/images/almalinux-from-centos.img \
  --arch x86_64 \
  --hostname almalinux-from-centos \
  --root-password password:password \
  --timezone Asia/Tokyo \
  --selinux-relabel

virt-install \
  --name almalinux-from-centos \
  --hvm \
  --virt-type kvm \
  --ram 2048 \
  --vcpus 1 \
  --arch x86_64 \
  --os-type linux \
  --os-variant centos8 \
  --boot hd \
  --disk path=/var/lib/libvirt/images/almalinux-from-centos.img \
  --network network=default \
  --graphics none \
  --serial pty \
  --console pty \
  --import

今の時点の virt-builder だと CentOS 8.2 までしか入れられなかったので root でログインして dnf update します。

dnf update -y
cat /etc/centos-release
#=> CentOS Linux release 8.3.2011

reboot

再起動後、再びログインして下記の手順の通りスクリプトをダウンロードして実行します。

curl -O https://raw.githubusercontent.com/AlmaLinux/almalinux-deploy/master/almalinux-deploy.sh
sudo bash almalinux-deploy.sh

次のようなメッセージがダラーっと流れてきます(端折っているところは dnf の出力)

Check root privileges                                                 OK
Check Secure Boot disabled                                            OK
Check centos-8.x86_64 is supported                                    OK
Download RPM-GPG-KEY-AlmaLinux                                        OK
Import RPM-GPG-KEY-AlmaLinux to RPM DB                                OK
Download almalinux-release package                                    OK
Verify almalinux-release package                                      OK
Remove centos-linux-release package                                   OK
Remove centos-gpg-keys package                                        OK
Remove centos-linux-repos package                                     OK
...snip...
Install almalinux-release package                                     OK
...snip...
Run dnf distro-sync -y                                                OK

うーん? almalinux-deploy.sh を見た感じ、distro-sync の後で grub2-mkconfig を実行して Migration to AlmaLinux is completed と表示して終わるはずなんですけど、なぜかここで終了しています。

sudo bash -x almalinux-deploy.sh で実行してみたところ、どうやら下記でコケていたようです。

restore_issue() {
    for file in /etc/issue /etc/issue.net; do
        [ -f "${file}.bak" ] && mv ${file}.bak ${file}
    done
}

issue も登録されていました(issue の issue ですね)。

この書き方だと [ -f "${file}.bak" ] && mv ${file}.bak ${file} が関数の最後の実行になるので /etc/issue.net.bak が存在しなければ関数の終了コードが非0になります。そしてスクリプトの先頭で set -euo pipefail と書かれているのでスクリプトは終了します。

distro-sync の前に対になる backup_issue が実行されていますが・・

backup_issue() {
    for file in $(rpm -Vf /etc/issue | cut -d' ' -f4); do
        if [[ ${file} =~ "/etc/issue" ]]; then
            cp "${file}" "${file}.bak"
        fi
    done
}

要するに /etc/issue を含むパッケージ(centos-release)に含まれるファイルのうち /etc/issue というパターンを含むファイルがもし変更されていれば distro-sync の前後で退避→復元する、という処理のようなのですが、そもそも /etc/issue/etc/issue.net が変更されていなければ /etc/issue.bak などが作成されることもないのでコケそうです。

あらかじめ /etc/issue/etc/issue.net を適当な内容に変更しておけば OK? です。

echo . >> /etc/issue
echo . >> /etc/issue.net
sudo bash -v almalinux-deploy.sh

または、この後の処理は grub2-mkconfig だけなので、これだけ手動でやっておけば大丈夫だと思います。

grub2-mkconfig -o /boot/grub2/grub.cfg
#=> Generating grub configuration file ...
#=> done

cat /etc/redhat-release
#=> AlmaLinux release 8.3 (Purple Manul)

sudo grubby --info DEFAULT | grep AlmaLinux
#=> title="AlmaLinux (4.18.0-240.22.1.el8_3.x86_64) 8.3 (Purple Manul)"

CodeBuild/CodeDeploy/CodePipeline で ECS にデプロイする素振り

CodeBuild/CodeDeploy/CodePipeline を使って ECS にデプロイを素振りしました。

残骸はこちら

デプロイの流れ

デプロイの基本的な流れは次のとおりです。

  1. VCS からソースコードを取得
  2. CodeBuild でイメージをビルドして ECR にプッシュ
    • このときイメージのURIを含むファイルをアーティファクトとして後段ステージに渡す
    • imagedefinitions.json とか imageDetail.json とか
  3. 新しいイメージの ECS タスク定義を作成し、ECS サービスを更新してデプロイ

ローリングアップデートであれば ECS 単体でできるので CodeDeploy は必要ありません。

ECS アクションプロバイダへは入力アーティファクトとして次のようなファイルを imagedefinitions.json というファイル名で提供する必要があります。この内容を元に既存のタスク定義のイメージの部分を書き換えたタスク定義が新たなリビジョンに登録され、そのタスク定義で ECS サービスが更新され、ローリングアップデートが実行されます。

// imagedefinitions.json
[{"name":"コンテナ名","imageUri":"イメージのURI"}]

CodeDeploy を絡めれば ELB のターゲットグループを複数使った Blue-Green Deployment が可能です。CodePipeline のアクションプロバイダに CodeDeploy というのがありますが、それではなく CodeDeployToECS というアクションプロバイダを使います。

CodeDeployToECS には appspec.yml taskdef.json imageDetail.json の3つのファイルを提供する必要があります。

  • imageDetail.json
    • イメージのURIが記述されたファイル
    • CodeBuild でイメージをビルド時にアーティファクトとして出力します
    • taskdef.json のプレースホルダがこのファイルで指定されたURIに書き換えられます
  • taskdef.json
    • ECS のタスク定義の内容、マネジメントコンソールでタスク定義の JSON をコピペできます
    • あらかじめリポジトリに含めておきます
    • <IMAGE1_NAME>imageDetail.json を元に置換されます
  • appspec.yml
    • CodeDeploy によるデプロイの設定
    • あらかじめリポジトリに含めておきます
    • <TASK_DEFINITION>taskdef.json を元に作成されたタスク定義に置換されます

taskdef.json をリポジトリに入れておく必要があるのがすごく微妙です。タスク定義はあらかじめ Terraform で作っておくので、これだと Terraform と taskdef.json とでタスク定義が2重に存在することになってしまいます。

taskdef.json はリポジトリには含めず、CodeBuild で既存のタスク定義からイメージの部分だけ <IMAGE1_NAME> に書き換えて生成しても良いかも。

aws ecs describe-task-definition --task-definition $ECS_TASK_DEFINITION_ARN --query taskDefinition |
  jq '.containerDefinitions[0].image="<IMAGE1_NAME>"' > taskdef.json

これならプレースホルダ使わずに、このタイミングで実際のイメージのURIに書き換えたものを作成するほうが手っ取り早い気もする・・・

CodeDeploy を使わない ECS アクションプロバイダはこれに相当することをやっていると思うんですけど(taskdef.json が必要ないので)、CodeDeployToECS アクションプロバイダだとなぜ taskdef.json が必要なんでしょうかね、謎。

CodePipeline のサービスロールの IAM ポリシー

CodePipeline のデプロイのアクションでサービスロールのポリシーが足りなくて失敗しても次のようなエラーメッセージしかダッシュボードに表示されず、なにが足りないのかさっぱりわかりません。

The provided role does not have sufficient permissions to access ECS

ユーザーガイドを見てもよくわからない。。。マネジメントコンソールでぽちぽちやる分には自動でサービスロールが作られたり必要なポリシーが更新されていくようですけど、Terraform であらかじめ作っておくとなると難しい。。。

CloudTrail のログを漁った感じ ECS 関係だと次のポリシーが必要なようです。

{
  "Action": [
    "ecs:DescribeServices",
    "ecs:DescribeTaskDefinition",
    "ecs:RegisterTaskDefinition",
    "ecs:UpdateService"
  ],
  "Effect": "Allow",
  "Resource": "*"
},
{
  "Action": "iam:PassRole",
  "Effect": "Allow",
  "Resource": <ECSExecutionRole>,
  "Condition": {
    "StringEqualsIfExists": {
      "iam:PassedToService": [
        "ecs-tasks.amazonaws.com"
      ]
    }
  }
}

CloudTrail でAPIを書き込みだけログるようにしていたために ecs:DescribeServices などが必要なことに気づかず結構な時間を浪費しました。

データベースマイグレーション

デプロイの前にデータベースのマイグレーションを入れたいときはどうするのが良いのかな?

CodeDeploy によって作られたタスク定義を使って CodeBuild で ECS Run Task を実行する? いやいやそれだとデプロイが終わった後になるので遅すぎますね。

デプロイステージの前に CodeBuild をもう一段追加して、新たに作成されたイメージで docker run するのが簡単そう。

イメージをビルドする CodeBuild の buildspec.yml でイメージのURIをエクスポートします。

# buildspec.yml
version: 0.2
env:
  exported-variables:
    - IMAGE_URI
phases:
  pre_build:
    commands:
      - IMAGE_TAG=$CODEBUILD_RESOLVED_SOURCE_VERSION
      - IMAGE_URI=$REPOSITORY_URI:$IMAGE_TAG

  # ...snip... #

マイグレーションの buildspec.yml は別名 migration.buildspec.yml で作成します。

# migration.buildspec.yml
version: 0.2
phases:
  pre_build:
    commands:
      - $(aws ecr get-login --no-include-email)
  build:
    commands:
      - docker run --rm $IMAGE_URI <マイグレーションコマンド>

CodeBuild の定義で migration.buildspec.yml を参照させます。

resource "aws_codebuild_project" "migration" {
  // ...snip... //

  source {
    type      = "CODEPIPELINE"
    buildspec = "migration.buildspec.yml"
  }

  // ...snip... //
}

CodePipeline で環境変数をビルド間で受け渡すように指定します。

resource "aws_codepipeline" "pipeline" {
  // ...snip... //

  stage {
    name = "Build"

    action {
      name      = "Build"
      namespace = "BuildExport" // エクスポートする変数の名前空間
      category  = "Build"
      owner     = "AWS"
      provider  = "CodeBuild"
      version   = "1"

      input_artifacts  = ["SourceArtifact"]
      output_artifacts = ["BuildArtifact"]

      configuration = {
        ProjectName = aws_codebuild_project.build.name
      }
    }
  }

  stage {
    name = "Migration"

    action {
      name     = "Migration"
      category = "Build"
      owner    = "AWS"
      provider = "CodeBuild"
      version  = "1"

      input_artifacts = ["SourceArtifact"]

      configuration = {
        ProjectName = aws_codebuild_project.migration.name
        EnvironmentVariables = jsonencode([
          {
            name  = "IMAGE_URI"
            value = "#{BuildExport.IMAGE_URI}" // エクスポートされた変数を参照
          }
        ])
      }
    }
  }

  // ...snip... //
}

ただこの方法、CodeBuild からデータベースに接続するために VPC 内で実行する必要があります。VPC 内の CodeBuild には PublicIP が付与できないので、プライベートサブネットから NAT Gateway なり PrivateLink なりで CodeCommit やら ECR やらにアクセスできるようにする必要があります。

ECS サービスをプライベートサブネットに置くなら CodeCommit はともかく ECR へは同じようにアクセスする必要があるので、CodeBuild を ECS サービスと同じサブネットで実行すれば問題はないですね。

定期バッチ

いわゆる定期バッチのために Cloudwatch Event で ECS Task を定期実行していたとして、デプロイ時は Cloudwatch Event を新しいイメージのタスク定義で実行するように更新する必要がありますが、これはどう更新するのが良いのかな?

CodeDeploy を使うなら appspec.yml の AfterAllowTraffic で Lambda を呼び出して Cloudwatch Event を更新する、とかでできるでしょうか。

あるいは定期バッチは常に最新のタスク定義または Docker イメージを使うように構成しておいて、Cloudwatch Event を更新せずに済ませるとか。ロールバックしたときに困りそうだし、どのイメージで動いてるかわかりにくくなるので微妙ですね。

そもそも定期バッチのタスク定義はアプリケーションサーバのタスク定義と同じものは使わなだろうので、デプロイステージでアプリケーションサーバをデプロイするアクションとは別に、定期バッチのためのタスク定義を作成&Cloudwatch Event を更新する処理を CodeBuild で実行すればいいですね。

CodeBuild の buildspec.yml は次のような感じ。ECS_TASK_DEFINITION_ARNCWE_RULR_NAME はあらかじめ CodeBuild の環境変数で定義しておきます。IMAGE_URI は前述のマイグレーションの場合と同様に CodePipeline 間で受け渡す必要があります。

version: 0.2
phases:
  build:
    commands:
      - |
        aws ecs describe-task-definition \
          --task-definition "$ECS_TASK_DEFINITION_ARN" |
          jq --arg IMAGE_URI "$IMAGE_URI" '.taskDefinition | {
            family: .family,
            executionRoleArn: .executionRoleArn,
            networkMode: .networkMode,
            containerDefinitions: .containerDefinitions,
            requiresCompatibilities: .requiresCompatibilities,
            cpu: .cpu,
            memory: .memory
          } | .containerDefinitions[].image = $IMAGE_URI' > taskdef.json
      - aws ecs register-task-definition
          --cli-input-json file://taskdef.json
          --query taskDefinition > registered.json
      - |
        aws events list-targets-by-rule --rule "$CWE_RULR_NAME" |
          jq --slurpfile task registered.json '
            .Targets |
              .[].EcsParameters.TaskDefinitionArn = $task[0].taskDefinitionArn
          ' > targets.json
      - aws events put-targets
          --rule "$CWE_RULR_NAME"
          --targets file://targets.json

うーんこれは・・AWS CLI ではなく Terraform でデプロイすればいいんじゃないかという気がしてきます。

なお、aws ecs register-task-definitionaws events put-targets のために、CodeBuild のサービスロールには ECS タスク定義の Execution Role や CloudWatch Event Rule の IAM Role に対する iam:PassRole が必要です。

{
  "Action": "iam:PassRole"
  "Effect": "Allow"
  "Resource": [
    aws_iam_role.ecs_execution.arn,
    aws_iam_role.schedule.arn
  ]
}

さいごに

軽く触ってみた感じ、CodePipeline の ECS プロバイダや CodeDeployToECS プロバイダを使えば Rolling Update や Blue-Green Deployment が簡単にできるのは便利だと思う反面、単にビルドしたイメージを使うタスク定義を新たに登録して ECS や CodeDeploy を呼んでるだけなので、それならデプロイも CodeBuild で Terraform や AWS CLI で実行するのでよいかも・・という気もしました。

また、CI/CD のパイプラインの定義が、CodeBuild/CodeDeploy/CodePipeline そのものを作成するための Terraform のテンプレートと、CodePipeline の実行中に利用される buildspec.yml appspec.yml とに分かれるため、どっちに何があるかわかりにくく感じます(この環境変数は Terraform テンプレート? いや buildspec.yml だったかな? みたいな)。

また、Terraform のテンプレートを修正したときは CI/CD のパイプラインとは別に terraform apply が必要になるため、ちょっと修正してプッシュしたらすぐ実行、みたいな手軽さもありません。 terraform apply するための CodePipeline も作って CodePipeline を 2 段重ねにすればできるかもしれないですけど・・複雑。

普段 Gitlab CI を使っているので、ECS のデプロイも Gitlab CI で docker build -> docker pubh -> terrafrm apply みたいなフローでも良いかも。

SQL で範囲が記録されたテーブルから任意の範囲が隙間なくすべて埋まっているか得る

次のようなテーブルがあったとします。1 レコードが a~b の範囲を示しています。終端の端点は含みません。例えば 3~6 なら 3,4,5 を含む範囲です。

create table t (
    id int not null primary key auto_increment,
    a int not null,
    b int not null
);
insert into t values (null,  3,  6);
insert into t values (null, 10, 14);
insert into t values (null, 13, 17);
insert into t values (null, 16, 20);
insert into t values (null, 25, 28);
insert into t values (null, 28, 31);
insert into t values (null, 31, 34);
insert into t values (null, 34, 36);
insert into t values (null, 40, 60);
insert into t values (null, 44, 56);

このテーブルから、指定した任意の範囲 @a~@b の区間に隙間があるかを調べます。例えば↑のデータの場合、10~20 の区間は全部埋まっています、20~30 の区間には隙間があります。

案:ビットフィールド

範囲内の各ポイントに1ビットを割り当てます。次のようにです。

0         1
1        10
2       100
3      1000
4     10000
5    100000
6   1000000
7  10000000
8 100000000

範囲はその中のすべてのポイントを論理和したものになります。例えば 3~6 の範囲は 111000 = 56 です。

テーブルのすべてのレコードの範囲を論理和すれば埋まっている範囲のビットフィールドが得られます。そのビットを反転し、目的の範囲と論理積して、非ゼロとなるビットが隙間です。ので、結果が非ゼロかどうかで隙間があるかどうか判断できます。

3~6 のような範囲の値からビットフィールドへは次の式で変換できます。

set @a=3, @b=6;
select (-1 << @a) & ~(-1 << @b) as x;
/*
+------+
| x    |
+------+
|   56 |
+------+
*/

レコードごとにビットフィールドを計算します。

select *, lpad(bin(
    (-1 << a) & ~(-1 << b)
), 64, '0') as x from t;
/*
+----+----+----+------------------------------------------------------------------+
| id | a  | b  | x                                                                |
+----+----+----+------------------------------------------------------------------+
|  1 |  3 |  6 | 0000000000000000000000000000000000000000000000000000000000111000 |
|  2 | 10 | 14 | 0000000000000000000000000000000000000000000000000011110000000000 |
|  3 | 13 | 17 | 0000000000000000000000000000000000000000000000011110000000000000 |
|  4 | 16 | 20 | 0000000000000000000000000000000000000000000011110000000000000000 |
|  5 | 25 | 28 | 0000000000000000000000000000000000001110000000000000000000000000 |
|  6 | 28 | 31 | 0000000000000000000000000000000001110000000000000000000000000000 |
|  7 | 31 | 34 | 0000000000000000000000000000001110000000000000000000000000000000 |
|  8 | 34 | 36 | 0000000000000000000000000000110000000000000000000000000000000000 |
|  9 | 40 | 60 | 0000111111111111111111110000000000000000000000000000000000000000 |
| 10 | 44 | 56 | 0000000011111111111100000000000000000000000000000000000000000000 |
+----+----+----+------------------------------------------------------------------+
*/

集計関数 BIT_OR ですべてのレコードの論理和を求めます。

select lpad(bin(bit_or(
    (-1 << a) & ~(-1 << b)
)), 64, '0') as x from t;
/*
+------------------------------------------------------------------+
| x                                                                |
+------------------------------------------------------------------+
| 0000111111111111111111110000111111111110000011111111110000111000 |
+------------------------------------------------------------------+
*/

↑の結果の補数と目的の範囲とを論理積します。

set @a=10, @b=30;
select lpad(bin(
    ~bit_or(
        (-1 << a) & ~(-1 << b)
    )
    &
    (
        (-1 << @a) & ~(-1 << @b)
    )
), 64, '0') as x from t;
/*
+------------------------------------------------------------------+
| x                                                                |
+------------------------------------------------------------------+
| 0000000000000000000000000000000000000001111100000000000000000000 |
+------------------------------------------------------------------+
*/

最後の結果は、20~25 が隙間、ということを意味しています。

この方法は後述の他の方法と比べるとテーブルを 1 回しか走査しないため実行計画もシンプルでパフォーマンスも良いです。ただし値の範囲が 0~63 までしか扱えません。それを超える値だと複数に分割するなどが必要となるため、値の取りうる範囲が大きくなると現実的ではなくなります。例えば1日の中で10分単位で指定できる予約サイト(ありがち)とかだと 24*60/10 = 144 なのでオーバーします。30分単位なら 24*60/30 = 48 なのでカバーできます。この種のシステムの要件はなるべく30分単位にしたいところです(違

案:連続する範囲の左端と右端を計算

隣接する範囲や重なる範囲を結合し、隣接や重なりの無い範囲の組に変換することを考えてみます。

次の条件で他の範囲と隣接や重なっていない左端が得られます。

select t.a
from t inner join t as s
group by t.a
having sum(t.a > s.a and t.a <= s.b) = 0
order by t.a;
/*
+----+
| a  |
+----+
|  3 |
| 10 |
| 25 |
| 40 |
+----+
*/

同様に右端も次のように得られます。

select t.b
from t inner join t as s
group by t.b
having sum(t.b < s.b and t.b >= s.a) = 0
order by t.b;
/*
+----+
| b  |
+----+
|  6 |
| 20 |
| 36 |
| 60 |
+----+
*/

この2つの結果を横につなげれば、隣接や重なりの無い範囲の組が得られます。

select a, min(b) as b from (
  select t.a
  from t inner join t as s
  group by t.a
  having sum(t.a > s.a and t.a <= s.b) = 0
) aa join (
  select t.b
  from t inner join t as s
  group by t.b
  having sum(t.b < s.b and t.b >= s.a) = 0
) bb
where a <= b
group by a;
/*
+----+------+
| a  | b    |
+----+------+
|  3 |    6 |
| 10 |   20 |
| 25 |   36 |
| 40 |   60 |
+----+------+
*/

これらの範囲の組の中に目的の範囲 @a~@b を完全に含むものがあるなら、隙間はないと判断できます。

set @a=10,@b=20;
select a, min(b) as b from (
  select t.a
  from t inner join t as s
  group by t.a
  having sum(t.a > s.a and t.a <= s.b) = 0
) aa join (
  select t.b
  from t inner join t as s
  group by t.b
  having sum(t.b < s.b and t.b >= s.a) = 0
) bb
where a <= b
group by a
having a <= @a and b >= @b;
/*
+----+------+
| a  | b    |
+----+------+
| 10 |   20 |
+----+------+
*/

LEFT JOIN でも似たようなことができます。

select a, min(b) as b from (
  select t.a
  from t left join t as s on t.a > s.a and t.a <= s.b
  where s.a is null
) aa join (
  select t.b
  from t left join t as s on t.b < s.b and t.b >= s.a
  where s.b is null
) bb
where a <= b
group by a;
/*
+----+------+
| a  | b    |
+----+------+
|  3 |    6 |
| 10 |   20 |
| 25 |   36 |
| 40 |   60 |
+----+------+
*/

案:すべての範囲が隣接または重なるか計算

目的の範囲 @a~@b と重なるすべてのレコードについて、下記が成り立つならその範囲に隙間はありません。

  • 左端 a が他のレコードと隣接または含まれる、または、@a より左
  • 右端 b が他のレコードと隣接または含まれる、または、@b より右

テーブルを自己結合し、すべての組み合わせから条件を判定します。aa や bb が 1 となる組み合わせが条件を満たしています。

set @a=10,@b=20;
select *,
  (t.a > s.a and t.a <= s.b or t.a <= @a) as aa,
  (t.b < s.b and t.b >= s.a or t.b >= @b) as bb
from t join t as s
where t.a < @b and t.b > @a
  and s.a < @b and s.b > @a
order by t.a, t.b, s.a, s.b;
/*
+----+----+----+----+----+----+------+------+
| id | a  | b  | id | a  | b  | aa   | bb   |
+----+----+----+----+----+----+------+------+
|  2 | 10 | 14 |  2 | 10 | 14 |    1 |    0 |
|  2 | 10 | 14 |  3 | 13 | 17 |    1 |    1 |
|  2 | 10 | 14 |  4 | 16 | 20 |    1 |    0 |
|  3 | 13 | 17 |  2 | 10 | 14 |    1 |    0 |
|  3 | 13 | 17 |  3 | 13 | 17 |    0 |    0 |
|  3 | 13 | 17 |  4 | 16 | 20 |    0 |    1 |
|  4 | 16 | 20 |  2 | 10 | 14 |    0 |    1 |
|  4 | 16 | 20 |  3 | 13 | 17 |    1 |    1 |
|  4 | 16 | 20 |  4 | 16 | 20 |    0 |    1 |
+----+----+----+----+----+----+------+------+
*/

すべての t.id について aa が 1 である組み合わせと bb が 1 である組み合わせがそれぞれ 1 つ以上存在するかを判定します。count(distinct) ですべての id の数と、左端と右端がそれぞれ条件を満たす id の数が一致するかどうかで判定します。

set @a=10,@b=20;
select
  count(distinct t.id) as cnt_id,
  count(distinct if (t.a > s.a and t.a <= s.b or t.a <= @a, t.id, null)) as cnt_aa,
  count(distinct if (t.b < s.b and t.b >= s.a or t.b >= @b, t.id, null)) as cnt_bb
from t join t as s
where t.a < @b and t.b > @a
  and s.a < @b and s.b > @a
having cnt_id = cnt_aa and cnt_id = cnt_bb and cnt_id > 0;
/*
+--------+--------+--------+
| cnt_id | cnt_aa | cnt_bb |
+--------+--------+--------+
|      3 |      3 |      3 |
+--------+--------+--------+
*/

テストします。1 1 1 のような結果が表示されている行が隙間の無い範囲です。

cat <<'EOS'>z.sql
select
  count(distinct t.id) as cnt_id,
  count(distinct if (t.a > s.a and t.a <= s.b or t.a <= @a, t.id, null)) as cnt_aa,
  count(distinct if (t.b < s.b and t.b >= s.a or t.b >= @b, t.id, null)) as cnt_bb
from t join t as s
where t.a < @b and t.b > @a
  and s.a < @b and s.b > @a
having cnt_id = cnt_aa and cnt_id = cnt_bb and cnt_id > 0
EOS

while read -r x; do
  echo -n "$x "
  echo $({ echo "$x"; cat z.sql; } | mysql test -N)
done <<'EOS'
  set @a =  3, @b =  6;
  set @a =  2, @b =  6;
  set @a =  3, @b =  7;
  set @a = 10, @b = 20;
  set @a =  9, @b = 20;
  set @a = 10, @b = 21;
  set @a = 25, @b = 36;
  set @a = 24, @b = 36;
  set @a = 25, @b = 37;
  set @a = 40, @b = 60;
  set @a = 39, @b = 60;
  set @a = 40, @b = 61;
EOS
#=> set @a =  3, @b =  6; 1 1 1
#=> set @a =  2, @b =  6;
#=> set @a =  3, @b =  7;
#=> set @a = 10, @b = 20; 3 3 3
#=> set @a =  9, @b = 20;
#=> set @a = 10, @b = 21;
#=> set @a = 25, @b = 36; 4 4 4
#=> set @a = 24, @b = 36;
#=> set @a = 25, @b = 37;
#=> set @a = 40, @b = 60; 2 2 2
#=> set @a = 39, @b = 60;
#=> set @a = 40, @b = 61;

さいごに

なるべくぱっと見でなにやってるかわからない SQL は書きたくないものです。