またまたcerbot-auto renewでエラー

以前書いた記事とは異なる現象。

CRONで自動更新設定したはずなのに、なーんかまた更新案内メール来てるので、、。

どうやら今回は下記のエラーが発生している模様。

pipをアップグレードしろみたいなことが書いてあるが、、

最新版なんですけど。

 

調べた結果、cerbot-autoスクリプトのpycparserハッシュがおかしいとか。

※今回のcerbot-autoの更新では「pycparser==2.14」ではなく「pyparsing==2.1.8」となっている模様。

これで無事renewも通りました。

を忘れずに。

以下参考サイト)

Let’s Encryptが急に動かなくなった件(THESE PACKAGES DO NOT MATCH THE HASHES FROM THE REQUIREMENTS FILE.)

https://community.letsencrypt.org/t/certbot-auto-fails-while-setting-up-virtual-environment-complains-about-package-hashes/20529/24

zoneファイルへのDMARCレコード一括追加用シェルスクリプト

業務用スクリプト。

DNSサーバのどこかに置き、指定されたディレクトリ内の名前が「FQDN.zone」で終わるすべてのファイルの末尾に、DMARCポリシー用のTXTレコードを追加するスクリプト。

※ご利用の際は必ずテストを行ってください。
※改変すれば、その他のレコード追加時にも使えると思います。
※コマンドが通らないときはwhichして探してください。

 

cerbot-auto renew でpythonパッケージのインストールが進まない

letsencryptの証明書更新時に、「cerbot-auto renew」でpythonパッケージのインストールが進まない話。
→ 結果、待つしかない(笑)

導入編は、こちらを参照。

さてcronによる証明書の自動更新設定を行っていたはずだが、下記のような「期限が迫ってるよ~。早く証明書更新してね~。」的なメールが届く。
———————————————
Hello,

Your certificate (or certificates) for the names listed below will expire in
9 days (on 12 Mar 17 08:02 +0000). Please make sure to renew
your certificate before then, or visitors to your website will encounter errors.

hoge.jp

For any questions or support, please visit https://community.letsencrypt.org/.
Unfortunately, we can’t provide support by email.
(以下略)
———————————————

おかしーなーと思ってrootあてのメールを見ると、以下のメッセージが。
———————————————

FATAL: Amazon Linux support is very experimental at present…
if you would like to work on improving it, please ensure you have backups
and then run this script again with the –debug flag!

———————————————

debugフラグをつけてコマンドを実行せい!とのことなので、言われたとおりにやってみるが、、

———————————————
# /usr/local/src/letsencrypt/certbot-auto renew –debug
->
Bootstrapping dependencies via Amazon Linux…
yum is /usr/bin/yum
Loaded plugins: priorities, update-motd, upgrade-helper
amzn-main/latest | 2.1 kB 00:00
amzn-updates/latest | 2.3 kB 00:00
epel/x86_64/metalink | 6.0 kB 00:00
epel/x86_64 | 4.3 kB 00:00
epel/x86_64/updateinfo | 741 kB 00:00
epel/x86_64/primary_db | 5.9 MB 00:00
remi-safe | 2.9 kB 00:00
remi-safe/primary_db | 718 kB 00:04
991 packages excluded due to repository priority protections
Package gcc-4.8.3-3.20.amzn1.noarch already installed and latest version
Package augeas-libs-1.0.0-5.7.amzn1.x86_64 already installed and latest version
Package 1:openssl-1.0.1k-15.96.amzn1.x86_64 already installed and latest version
Package 1:openssl-devel-1.0.1k-15.96.amzn1.x86_64 already installed and latest version
Package libffi-devel-3.0.13-16.5.amzn1.x86_64 already installed and latest version
Package system-rpm-config-9.0.3-42.28.amzn1.noarch already installed and latest version
Package ca-certificates-2015.2.6-65.0.1.16.amzn1.noarch already installed and latest version
Package python27-2.7.12-2.120.amzn1.x86_64 already installed and latest version
Package python27-devel-2.7.12-2.120.amzn1.x86_64 already installed and latest version
Package python27-virtualenv-12.0.7-1.13.amzn1.noarch already installed and latest version
Package python27-tools-2.7.12-2.120.amzn1.x86_64 already installed and latest version
Package python27-pip-6.1.1-1.23.amzn1.noarch already installed and latest version
Nothing to do
Creating virtual environment…
Installing Python packages…
——————————————————————————-
この状態でうんともすんとも言わなくなった!
10分待とうが20分待とうが進まないので、再度コマンド発行して、一日放置してみるも、無操作によりsshクライアントとの接続が自動切断、、、

翌週、気を取り直してもう一度試すと、、、え?通った!
——————————————————————————-

Installation succeeded.
Saving debug log to /var/log/letsencrypt/letsencrypt.log

——————————————————————————-
Processing /etc/letsencrypt/renewal/hoge.jp.conf
——————————————————————————-
Cert is due for renewal, auto-renewing…
Renewing an existing certificate
Performing the following challenges:
tls-sni-01 challenge for hoge.jp
Waiting for verification…
Cleaning up challenges
Generating key (2048 bits): /etc/letsencrypt/keys/0001_key-certbot.pem
Creating CSR: /etc/letsencrypt/csr/0001_csr-certbot.pem

——————————————————————————-
new certificate deployed with reload of apache server; fullchain is
/etc/letsencrypt/live/hoge.jp/fullchain.pem
——————————————————————————-

Congratulations, all renewals succeeded. The following certs have been renewed:
/etc/letsencrypt/live/hoge.jp/fullchain.pem (success)
——————————————————————————-

最初はpipのミラーサイトがダメになったのかな?とも思っていろいろ調べたが、結局通信状態の問題もしくは一時的にミラーが落ちてたっぽい。
とりあえず待つしかなかったってことね。

参考サイト)

 

Amazon LinuxでletsEncryptを導入

今回使用したamazon linuxのバージョンは以下。
——————————-
# cat /etc/system-release
-> Amazon Linux AMI release 2016.09
——————————-
WEBサーバはapache2.4。なおWEBサーバの設定の詳細は割愛します。

githubからletsencryptインストール
——————————-
# cd /usr/local/src
# git clone https://github.com/letsencrypt/letsencrypt
# cd letsencrypt
# sudo ./letsencrypt-auto –help –debug
——————————-

※使用するドメインで外部から80番にアクセスできないと認証できないので、あらかじめファイアウォールの解放と、httpd.confの設定を行っておきます。

証明書作成
——————————-
# ./letsencrypt-auto certonly –standalone -d ドメイン名
——————————-

画面の案内に沿って進める。
完了後、サーバ証明書が作成されているか確認
——————————-
# ls -lat /etc/letsencrypt/live/
——————————-

作成した証明書を、ssl.confに指定
——————————-
# vi /etc/httpd/conf.d/ssl.conf
->
:
SSLCertificateFile /etc/letsencrypt/live/ドメイン名/fullchain.pem
:
SSLCertificateKeyFile /etc/letsencrypt/live/ドメイン名/privkey.pem
:
——————————-
※fullchain.pemを指定した場合は中間証明書(SSLCertificateChainFile)の指定は不要。

WEBサーバリロード
——————————-
# service httpd reload
——————————-

ブラウザなどでhttpsでアクセスして証明書が効いているかチェック

更新用コマンド
——————————-
# /usr/local/src/letsencrypt/certbot-auto renew
——————————-

自動更新設定
——————————-
# vim /etc/crontab
——————————-
->
# letsencrypt renew #####
5 4 * * * root /usr/local/src/letsencrypt/certbot-auto renew && /etc/init.d/httpd reload
#########################
——————————-
※ちなみにcerbot-autoが使えるのはpython2.7以降。それ以前の場合は「/usr/local/src/letsencrypt/letsencrypt-auto」を使う

 

参考リンク)

とても便利なGROUP_CONCAT()

今まであまり使う機会がなく知らなかったのだが、グループ化したカラム以外のカラム値をつなげて返してくれるこの関数はとても便利。
しかもDISTINCTも使えるのが最高に良い。(DISTINCTはUNIONでしか使えないと思っていた。。。)

使用例
tb_family

id
1
2
3

tb_parent

id parent_name family_id
1 山田 太郎 1
2 鈴木 花子 2
3 佐藤 次郎 3
4 山田 よしこ 1

tb_student

id student_name family_id
1 山田 息子 1
2 鈴木 息子 2
3 佐藤 息子 3
4 鈴木 娘 2

同じ家庭の保護者(parent)をグループ化して表示

結果

family_id all_parent
1 山田 太郎,山田 よしこ
2 鈴木 花子
3 佐藤 次郎

同じ家庭の保護者(parent)と学生(student)をグループ化して表示

結果

family_id all_parent all_student
1 山田 太郎,山田 よしこ 山田 息子,山田 息子
2 鈴木 花子,鈴木 花子 鈴木 娘,鈴木 息子
3 佐藤 次郎 佐藤 息子

このままだと保護者と学生の列数分ダブってしまうので、、、
同じ家庭の保護者(parent)と学生(student)をグループ化し、重複値は1つにまとめて表示

結果

family_id all_parent all_student
1 山田 太郎,山田 よしこ 山田 息子
2 鈴木 花子 鈴木 娘,鈴木 息子
3 佐藤 次郎 佐藤 息子

Linuxの全文検索ワンライナー

で、指定したパス以下の全文検索が実行され、結果には対象のファイルと行番号が表示される。

使用例:hogeディレクトリ以下の、内容に「fuga」という文字列を含んだファイルを検索

 

WHERE句のINでプレースホルダを使う際の注意点

DBIモジュールでは、プレースホルダを使う場合、基本的な記述は下記のようになる。

んで、SQL分にWHERE句を含んでいる場合で、条件をINで複数指定している場合、

でいけると思いきや、値の最初の要素(カンマで区切った一番最初の値=「あ」)のみプレースホルダに代入されて処理されてしまう。つまり、

と同じ結果となってしまう。

どうやらカンマで連結した文字列を渡しても、配列要素とみなされるようだ。

なので、条件の数分だけ、プレースホルダを作り指定してやる必要がある。

単純に書くと、

てな感じ。

現実的にはINの条件数は動的になると思うので、うちでは下記のような感じで処理している。

・DB処理用ファイル(database.pl)

・呼び出し元

PHPでもフレームワークによってここの処理が違ったりするので注意が必要。

 

濁点(゛)と半濁点(゜)を含む日本語のメール文字化け対応

JIS(≒ISO-2022-JP)で定義されていない文字コードは、UTF8から変換しても、もちろん正常に表示されません。

しかし日本語のUTF8には、実は、濁点と半濁点をふくむ文字に2パターンの表示方法があります。

たとえば「が」というひらがなですが、普通は、

xE3x81x8C

となります。

しかし、UTF8の決まりでは、

xE3x81x8B + xE3x82x99 (「か」+「 ○゛」)

でもOKなのです。

 

うちでは日本語文字コードの変換にUnicode::Japaneseを使ってます。非常に多くの変換パターンが実現できるので便利なのですが、上記の後者の場合(「か」+「 ○゛」の場合)に、UTF8→JISなどの変換に失敗します。

失敗というか、厳密にはxE3x82x99( ○゛)とxE3x82x9A( ○゜)が未定義状態のようで、「が」は「が」のように変換されてしまいます。

エンコーダを変えればいいのかもしれませんが、やはりバグが怖いので、下記のようにJISへの変換前にコードを統一することで対応してます。

 

ご自由にコピペどうぞ。

 

 

参考サイト)

http://d.hatena.ne.jp/kamosawa/20151015

http://www.seiai.ed.jp/sys/text/java/utf8table.html

http://webmastertool.jp/other/utf.html

http://www.asahi-net.or.jp/~ax2s-kmtn/ref/unicode/u3040.html

非効率なサブクエリを改善する

サブクエリを使った検索において、検索件数が数百~数千程度であれば、マシンの力で何とかなることがあるが、この検索結果が数万単位の膨大な数となる場合、動作が極端に遅くなる場合がある。

例として、historyテーブルにメールの履歴、clickテーブルにメールの履歴番号をhistory_idとしたクリックURL、およびそのクリック回数が入っていて、レコードは複数あるとする。

historyテーブルにクリック数のデータはないので、clickテーブルを結合して、履歴データと、その履歴ごとのクリック数およびクリック率(CTR)の一覧を表示したい。またCTRの大きさで一覧をソートしたい。

例えば下記のSQL。

この場合、clickテーブルのレコードが膨大になってしまうと、10~15行目のサブクエリ内のSELECTが全数検索になるため、結合後のテーブルが巨大となり、極端に動作が重くなる。history_idにINDEXを貼っていても、数万単位の量になってくると、同様だ。

いくら最後(18行目)にLIMIT句でレコード数を制限しても、内側のサブクエリから処理されるので、結合時点ではテーブルは巨大になってしまう。

「サブクエリ内でLIMIT使えばいいのでは?」と思うが、1つの履歴番号に紐付くclickテーブルのレコードが必ず存在するとは限らないし、存在したとしてもレコード数は一定ではないためこの時点でLIMITは確定できない。

つまり、「GROUP BY history.id」(15行目)したレコード数が、30になるとは限らない。仮にサブクエリ内で同じく「LIMIT 0,30」とやったとしたら、結合時にデータに不整合が生じる可能性がある。

またCTRは、historyテーブルに存在する読者数がわからないと出せないので、結合後にORDER BYするしかなく、サブクエリ内ではCTRによるORDER BYができない。この理由もありLIMITは使えない。

そこで発想の転換をして、clickテーブルにhistoryテーブルを結合してみる。

ポイントは、

  • clickテーブルを基本として、クリック数の合計とCTRの計算をする。
  • historyテーブルをRIGHT JOINして、historyテーブルのレコード数に、最終的な結果レコード数をあわせるようにする。
  • ORDER BY の対象を「click.history_id」ではなく「history.id」とし、履歴番号に紐付くclickテーブルのレコード数がない時も、対応できるようにする。

こうすることで、サブクエリを使わずに、LIMITをうまく使って膨大なクリックデータを高速に処理できるようになった。

当然ページ送りも、このLIMITの値を変更するだけで対応できる。

 

経験上、どうしてもサブクエリを使わなければならないってことはほとんどないんだよなあ、、、(経験不足なのか?)。

大体の場合、上記のように結合の方法を工夫するか、テンポラリテーブルを使って処理できる場合が多いと思う。(個人の感想です)

 

参考サイト)

http://nullnote.com/programs/mysql/join/

SQLでサブクエリを上手に使う6パターン

Postfixで特定のクライアントからのアクセスをブロックするテスト

※本記事は過去に公開した記事の復刻記事です※

特定のホストからのSMTP要求を拒否するための設定。実務的には、主にメールが拒否された際に行いたい処理の確認などに使う。

 

■main.cfを編集

————————————————

# vi /etc/postfix.main.cf

(以下を追記)


smtpd_client_restrictions = check_client_access regexp:/etc/postfix/clients_test

————————————————

※正規表現を使わない場合は「regexp:」でなく、「hash:」や「cidr:」でも可。ただし「postmap /etc/postfix/clients_test」してdbファイルを作り必要あり。

 

■client_testファイル作成

————————————————

vi /etc/postfix/client_test

(以下の形式で記載)

/接続元/ 応答コード [バナーテキスト]

例:

/123.123.123.123/ REJECT

/test.smtp.hoge.jp/ REJECT

/123.123.123.123/ 550 This is test block.(Don’t send anymore.)

/123.123.123.123/ 421 This is test block.(Send one more later.)

————————————————

※書式の詳細はこちらを参照。

 

■Postfix 再起動

————————————————

# service postfix restart

————————————————

 

参考サイト)

http://www.postfix-jp.info/trans-2.1/jhtml/access.5.html

http://d.hatena.ne.jp/hidde/20081104/1225775861

http://kisekiit.seesaa.net/article/226142927.html

http://blog.negabaro.com/4496.html