Igreks Inc. Developer – ページ 7

ひかり電話オフィスでビジネスフォン（SAXA HM700Std）

この度諸々の理由で、会社の電話をSAXAのビジネスフォンに変更することになった。

このSAXA HM700はひかり電話を直収できるタイプのものなので、終端装置（ONU）に介する信号変換機（OG）が必要ないらしい。

ただしひかり電話オフィス（タイプ）にすることで、今までのWifiカード付のNTTルータが使えなくなるので、Wifi飛ばす場合は別途Wifiルータが必要となる。（今回はTP-Link AC1200を導入）

一次側（ONUまで）の回線工事と疎通はもちろんNTTにやってもらい、ビジネスフォン主装置は、事前に外線着信設定や内線の設定を専門業者に済ませてもらい、基本現場でつなぐだけの状態で導入した。

で、ひかり電話のため、当然二次側もネットが疎通しないと電話が使えないし、インターネットも使えない。ついでに警備の通信も有線回線がメインだったりすると、当然メインが停止状態になる。（ちなみに警備はA社利用）

ちょっとした思い込みで3時間くらいはまってしまったので、今後使うかどうかわからないけど一応忘備録。

【事前準備】

ONU設置と回線工事完了後、

専門業者の説明書を参考に主装置をONUにつなぐ
専門業者の説明書を参考に電話機（今回導入したのはTD615）とFAXを主装置につなぐ
主装置の電源を入れる

【ネット疎通】

主装置から出ている（はずの）WAN側接続用のLANケーブルをPCに接続。
ブラウザを起動し、主装置（初期値：192.168.1.253）にアクセス。（主装置のIPアドレスは電話機のメニュー→その他→ネットワーク→ネットワーク情報の確認→主装置で確認可能）
主装置に工事担当者権限でログイン。（工場出荷時は、ユーザ名「in」、パスワード「tlipinst」）
上の方の「VoIP1+ルータ」をクリック
「Service Type」が指定されていなければ、「ひかり電話オフィスタイプ（フレッツ光ネクスト[NTT東／西]）」を選択して保存
左側の「設定メニュー」→「ルータ設定メニュー」→「WANインターフェイス」
いずれかのインターフェイス名（ここではPPPoE_1）を選択
各情報を入力
- セッション→「有効」
- 認証方式→「あり」
- 接続ユーザ名→「プロバイダから提供されているインターネット接続用のユーザID」
- 接続パスワード→「プロバイダから提供されているインターネット接続用のパスワード」
「設定」ボタンクリック。さらに左側メニューの一番下「設定データ保存」をクリックし主装置に反映。（この時点で電話は使えるようになっているはず。）

【Wifiルータ（もしくはハブなど）】

PCからLANケーブルを外し、別途準備したWifiルータのWANポートに接続。
既存の警備用のLANケーブル（あれば）とプリンタ用のLANケーブル（あれば）をWifiルータのLANポートに接続
PCをWifiルータのLANポートに接続
Wifiルータの電源を入れる
ブラウザを起動し、Wifiルータ（初期値：192.168.0.1）にアクセスしログイン（工場出荷時は、ユーザ名パスワード共に「admin」）
メニューから「クイックセットアップ」を選択し設定を行う。
- WANタイプ→「動的IP」→次へ
- MACクローン→「いいえ」→次へ
- ワイアレスデュアルバンド選択→「５GH,2.4GH」どちらもチェック→次へ
- ワイヤレス2.4GH、ワイヤレス5GH→「セキュリティ:WPA2-PSK」、それ以外は任意に指定（Wifiの接続パスワードはこの時確認しておく）→次へ
- 最後に内容を確認して「設定」をクリック。
DHCPを有効にするため、メニュー「DHCP」→「DHCP設定」
「有効」になっていなければ「有効」にチェックを入れ「保存」
メニュー「DHCP」→「DHCPクライアントリスト」でプリンタ（あれば）のIPアドレスを確認しておく。
管理画面からログアウト
PCとWifiルータを接続していたLANケーブルを外す。

【確認】

PCでインターネットが利用でき、警備用の回線がバックアップから回復していればOK（警備会社にちゃんと報告しましょう。）

ルーターのDHCP更新によりプリンタのIPアドレスが変更されていると思われるので、必要に応じてプリンタの接続設定を行う。（Wifiルータの管理画面で、プリンタのIPアドレス出てこない場合は、プリンタ本体で確かめる（プリンタ側でDHCP受付がOFFになっている可能性あり）。）

【総評】

ミソは【ネット疎通】の個所。

つまりこれをやらずに、いきなりWifiルータ側で一生懸命PPPoEを開通させようとして、何度もパスワードを確認したりして、ハマってしまった。

結局主装置側でルーティングが遮断されているのだから、その先のWifiルータ側で何やってもダメなわけ。（主装置自体がルータなのだから、主装置でPPPoE設定し、Wifiルータはただのブリッジでよい）。

IP電話なのだから、言われてみれば当たり前のことなのだが、、、。

説明書にその辺をちょっとだけでも書いておいて欲しかったなあ。

メール本文にピリオド（.）のみの行がある場合の対処

今更感がありますが、、。

SMTP通信上は、原則ピリオドのみの行（正確にはCRLF.CRLF）はメールの終わりを意味します。

本文にこれらが含まれていた場合、以降のデータが無視されてしまいますので、SMTPとPOPのルール上は、本文にピリオドのみの行があった場合には、送信時にピリオドをさらに1つつけ、受信時（POP処理時）には1つピリオドを落とします。

sendmailでは-iオプションで送信時に上記の処理を勝手にやってくれます。（逆に-iオプションを付けないとデフォルトでピリオドだけの行を終端と見なし以降は無視される、という仕様に驚き）

例

sendmail -i -f from@example.com -t to@example.com

1	sendmail -i -f from@example.com -t to@example.com

qmailやPostfixで、sendmailラッパを利用している場合でも、きちんと動作します。

参考サイト）

https://fumiyas.github.io/2014/12/13/sendmail.postfix-advent-calendar.html

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

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

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

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

$ sudo /usr/local/src/letsencrypt/certbot-auto renew                          

Upgrading certbot-auto 0.12.0 to 0.14.1...
Replacing certbot-auto...
Creating virtual environment...
Installing Python packages...
Had a problem while installing Python packages.

pip prints the following errors: 
=====================================================
Collecting argparse==1.4.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 11))
  Downloading argparse-1.4.0-py2.py3-none-any.whl
Collecting pycparser==2.14 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 17))
  Downloading pycparser-2.14.tar.gz (223kB)
Collecting cffi==1.4.2 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 21))
  Downloading cffi-1.4.2.tar.gz (365kB)
Collecting ConfigArgParse==0.10.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 38))
  Downloading ConfigArgParse-0.10.0.tar.gz
Collecting configobj==5.0.6 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 40))
  Downloading configobj-5.0.6.tar.gz
Collecting cryptography==1.5.3 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 42))
  Downloading cryptography-1.5.3.tar.gz (400kB)
Collecting enum34==1.1.2 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 65))
  Downloading enum34-1.1.2.tar.gz (46kB)
Collecting funcsigs==0.4 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 68))
  Downloading funcsigs-0.4-py2.py3-none-any.whl
Collecting idna==2.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 71))
  Downloading idna-2.0-py2.py3-none-any.whl (61kB)
Collecting ipaddress==1.0.16 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 74))
  Downloading ipaddress-1.0.16-py27-none-any.whl
Collecting linecache2==1.0.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 77))
  Downloading linecache2-1.0.0-py2.py3-none-any.whl
Collecting ordereddict==1.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 80))
  Downloading ordereddict-1.1.tar.gz
Collecting parsedatetime==2.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 82))
  Downloading parsedatetime-2.1-py2-none-any.whl
Collecting pbr==1.8.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 85))
  Downloading pbr-1.8.1-py2.py3-none-any.whl (89kB)
Collecting pyasn1==0.1.9 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 88))
  Downloading pyasn1-0.1.9-py2.py3-none-any.whl
Collecting pyOpenSSL==16.2.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 100))
  Downloading pyOpenSSL-16.2.0-py2.py3-none-any.whl (43kB)
Collecting pyparsing==2.1.8 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 103))
  Downloading pyparsing-2.1.8-py2.py3-none-any.whl (54kB)
Collecting pyRFC3339==1.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 112))
  Downloading pyRFC3339-1.0-py2.py3-none-any.whl
Collecting python-augeas==0.5.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 115))
  Downloading python-augeas-0.5.0.tar.gz (90kB)
Collecting pytz==2015.7 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 117))
  Downloading pytz-2015.7-py2.py3-none-any.whl (476kB)
Collecting requests==2.12.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 131))
  Downloading requests-2.12.1-py2.py3-none-any.whl (574kB)
Requirement already satisfied (use --upgrade to upgrade): six==1.10.0 in /root/.local/share/letsencrypt/lib/python2.7/site-packages (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 134))
Collecting traceback2==1.4.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 137))
  Downloading traceback2-1.4.0-py2.py3-none-any.whl
Collecting unittest2==1.1.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 140))
  Downloading unittest2-1.1.0-py2.py3-none-any.whl (96kB)
Collecting zope.component==4.2.2 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 143))
  Downloading zope.component-4.2.2.tar.gz (546kB)
Collecting zope.event==4.1.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 145))
  Downloading zope.event-4.1.0.tar.gz (476kB)
Collecting zope.interface==4.1.3 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 147))
  Downloading zope.interface-4.1.3.tar.gz (141kB)
Collecting mock==1.0.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 165))
  Downloading mock-1.0.1.zip (861kB)
Collecting letsencrypt==0.7.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 168))
  Downloading letsencrypt-0.7.0-py2-none-any.whl
Collecting acme==0.14.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 174))
  Downloading acme-0.14.1-py2.py3-none-any.whl (96kB)
Collecting certbot==0.14.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 177))
  Downloading certbot-0.14.1-py2.py3-none-any.whl (248kB)
Collecting certbot-apache==0.14.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 180))
  Downloading certbot_apache-0.14.1-py2.py3-none-any.whl (134kB)
Collecting certbot-nginx==0.14.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 183))
  Downloading certbot_nginx-0.14.1-py2.py3-none-any.whl (64kB)
Requirement already satisfied (use --upgrade to upgrade): setuptools>=11.3 in /root/.local/share/letsencrypt/lib/python2.7/site-packages (from cryptography==1.5.3->-r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 42))
Installing collected packages: argparse, pycparser, cffi, ConfigArgParse, configobj, idna, pyasn1, enum34, ipaddress, cryptography, funcsigs, linecache2, ordereddict, parsedatetime, pbr, pyOpenSSL, pyparsing, pytz, pyRFC3339, python-augeas, requests, traceback2, unittest2, zope.interface, zope.event, zope.component, mock, acme, certbot, letsencrypt, certbot-apache, certbot-nginx
  Running setup.py install for pycparser: started
    Running setup.py install for pycparser: finished with status 'done'
  Running setup.py install for cffi: started
    Running setup.py install for cffi: finished with status 'done'
  Running setup.py install for ConfigArgParse: started
    Running setup.py install for ConfigArgParse: finished with status 'done'
  Running setup.py install for configobj: started
    Running setup.py install for configobj: finished with status 'done'
  Running setup.py install for enum34: started
    Running setup.py install for enum34: finished with status 'done'
  Running setup.py install for cryptography: started
    Running setup.py install for cryptography: finished with status 'error'
    Complete output from command /root/.local/share/letsencrypt/bin/python2.7 -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-l6BZOB/cryptography/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /tmp/pip-n0qt5Y-record/install-record.txt --single-version-externally-managed --compile --install-headers /root/.local/share/letsencrypt/include/site/python2.7/cryptography:
    running install
    running build
    running build_py
    creating build
    creating build/lib.linux-x86_64-2.7
    creating build/lib.linux-x86_64-2.7/cryptography
    copying src/cryptography/__init__.py -> build/lib.linux-x86_64-2.7/cryptography
    copying src/cryptography/exceptions.py -> build/lib.linux-x86_64-2.7/cryptography
    copying src/cryptography/__about__.py -> build/lib.linux-x86_64-2.7/cryptography
    copying src/cryptography/fernet.py -> build/lib.linux-x86_64-2.7/cryptography
    copying src/cryptography/utils.py -> build/lib.linux-x86_64-2.7/cryptography
    creating build/lib.linux-x86_64-2.7/cryptography/x509
    copying src/cryptography/x509/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/x509
    copying src/cryptography/x509/base.py -> build/lib.linux-x86_64-2.7/cryptography/x509
    copying src/cryptography/x509/name.py -> build/lib.linux-x86_64-2.7/cryptography/x509
    copying src/cryptography/x509/extensions.py -> build/lib.linux-x86_64-2.7/cryptography/x509
    copying src/cryptography/x509/oid.py -> build/lib.linux-x86_64-2.7/cryptography/x509
    copying src/cryptography/x509/general_name.py -> build/lib.linux-x86_64-2.7/cryptography/x509
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat
    copying src/cryptography/hazmat/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings
    copying src/cryptography/hazmat/bindings/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives
    copying src/cryptography/hazmat/primitives/hmac.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives
    copying src/cryptography/hazmat/primitives/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives
    copying src/cryptography/hazmat/primitives/keywrap.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives
    copying src/cryptography/hazmat/primitives/padding.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives
    copying src/cryptography/hazmat/primitives/serialization.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives
    copying src/cryptography/hazmat/primitives/constant_time.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives
    copying src/cryptography/hazmat/primitives/hashes.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives
    copying src/cryptography/hazmat/primitives/cmac.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/backends
    copying src/cryptography/hazmat/backends/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends
    copying src/cryptography/hazmat/backends/multibackend.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends
    copying src/cryptography/hazmat/backends/interfaces.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/commoncrypto
    copying src/cryptography/hazmat/bindings/commoncrypto/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/commoncrypto
    copying src/cryptography/hazmat/bindings/commoncrypto/binding.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/commoncrypto
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/openssl
    copying src/cryptography/hazmat/bindings/openssl/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/openssl
    copying src/cryptography/hazmat/bindings/openssl/_conditional.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/openssl
    copying src/cryptography/hazmat/bindings/openssl/binding.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/openssl
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/interfaces
    copying src/cryptography/hazmat/primitives/interfaces/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/interfaces
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/twofactor
    copying src/cryptography/hazmat/primitives/twofactor/totp.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/twofactor
    copying src/cryptography/hazmat/primitives/twofactor/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/twofactor
    copying src/cryptography/hazmat/primitives/twofactor/hotp.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/twofactor
    copying src/cryptography/hazmat/primitives/twofactor/utils.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/twofactor
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric
    copying src/cryptography/hazmat/primitives/asymmetric/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric
    copying src/cryptography/hazmat/primitives/asymmetric/dsa.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric
    copying src/cryptography/hazmat/primitives/asymmetric/padding.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric
    copying src/cryptography/hazmat/primitives/asymmetric/ec.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric
    copying src/cryptography/hazmat/primitives/asymmetric/utils.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric
    copying src/cryptography/hazmat/primitives/asymmetric/rsa.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric
    copying src/cryptography/hazmat/primitives/asymmetric/dh.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/ciphers
    copying src/cryptography/hazmat/primitives/ciphers/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/ciphers
    copying src/cryptography/hazmat/primitives/ciphers/base.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/ciphers
    copying src/cryptography/hazmat/primitives/ciphers/algorithms.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/ciphers
    copying src/cryptography/hazmat/primitives/ciphers/modes.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/ciphers
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf
    copying src/cryptography/hazmat/primitives/kdf/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf
    copying src/cryptography/hazmat/primitives/kdf/hkdf.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf
    copying src/cryptography/hazmat/primitives/kdf/concatkdf.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf
    copying src/cryptography/hazmat/primitives/kdf/pbkdf2.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf
    copying src/cryptography/hazmat/primitives/kdf/kbkdf.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf
    copying src/cryptography/hazmat/primitives/kdf/x963kdf.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto
    copying src/cryptography/hazmat/backends/commoncrypto/hmac.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto
    copying src/cryptography/hazmat/backends/commoncrypto/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto
    copying src/cryptography/hazmat/backends/commoncrypto/backend.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto
    copying src/cryptography/hazmat/backends/commoncrypto/hashes.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto
    copying src/cryptography/hazmat/backends/commoncrypto/ciphers.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto
    creating build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/hmac.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/dsa.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/x509.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/backend.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/ec.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/hashes.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/cmac.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/ciphers.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/utils.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/rsa.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/decode_asn1.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    copying src/cryptography/hazmat/backends/openssl/encode_asn1.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl
    running egg_info
    writing requirements to src/cryptography.egg-info/requires.txt
    writing src/cryptography.egg-info/PKG-INFO
    writing top-level names to src/cryptography.egg-info/top_level.txt
    writing dependency_links to src/cryptography.egg-info/dependency_links.txt
    writing entry points to src/cryptography.egg-info/entry_points.txt
    warning: manifest_maker: standard file '-c' not found
    
    reading manifest file 'src/cryptography.egg-info/SOURCES.txt'
    reading manifest template 'MANIFEST.in'
    no previously-included directories found matching 'docs/_build'
    warning: no previously-included files matching '*' found under directory 'vectors'
    writing manifest file 'src/cryptography.egg-info/SOURCES.txt'
    running build_ext
    generating cffi module 'build/temp.linux-x86_64-2.7/_padding.c'
    creating build/temp.linux-x86_64-2.7
    generating cffi module 'build/temp.linux-x86_64-2.7/_constant_time.c'
    generating cffi module 'build/temp.linux-x86_64-2.7/_openssl.c'
    building '_openssl' extension
    creating build/temp.linux-x86_64-2.7/build
    creating build/temp.linux-x86_64-2.7/build/temp.linux-x86_64-2.7
    gcc -pthread -fno-strict-aliasing -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -I/usr/include/python2.7 -c build/temp.linux-x86_64-2.7/_openssl.c -o build/temp.linux-x86_64-2.7/build/temp.linux-x86_64-2.7/_openssl.o
    virtual memory exhausted: Cannot allocate memory
    error: command 'gcc' failed with exit status 1
    
    ----------------------------------------
Command "/root/.local/share/letsencrypt/bin/python2.7 -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-l6BZOB/cryptography/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /tmp/pip-n0qt5Y-record/install-record.txt --single-version-externally-managed --compile --install-headers /root/.local/share/letsencrypt/include/site/python2.7/cryptography" failed with error code 1 in /tmp/pip-build-l6BZOB/cryptography
You are using pip version 8.0.3, however version 9.0.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
=====================================================

Certbot has problem setting up the virtual environment.

Based on your pip output, the problem can likely be fixed by 
increasing the available memory.

Consult https://certbot.eff.org/docs/install.html#problems-with-python-virtual-environment
for possible solutions.
You may also find some support resources at https://certbot.eff.org/support/ .

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

$ sudo /usr/local/src/letsencrypt/certbot-auto renew

Upgrading certbot-auto 0.12.0 to 0.14.1...

Replacing certbot-auto...

Creating virtual environment...

Installing Python packages...

Had a problem while installing Python packages.

pip prints the following errors:

=====================================================

Collecting argparse==1.4.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 11))

Downloading argparse-1.4.0-py2.py3-none-any.whl

Collecting pycparser==2.14 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 17))

Downloading pycparser-2.14.tar.gz (223kB)

Collecting cffi==1.4.2 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 21))

Downloading cffi-1.4.2.tar.gz (365kB)

Collecting ConfigArgParse==0.10.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 38))

Downloading ConfigArgParse-0.10.0.tar.gz

Collecting configobj==5.0.6 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 40))

Downloading configobj-5.0.6.tar.gz

Collecting cryptography==1.5.3 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 42))

Downloading cryptography-1.5.3.tar.gz (400kB)

Collecting enum34==1.1.2 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 65))

Downloading enum34-1.1.2.tar.gz (46kB)

Collecting funcsigs==0.4 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 68))

Downloading funcsigs-0.4-py2.py3-none-any.whl

Collecting idna==2.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 71))

Downloading idna-2.0-py2.py3-none-any.whl (61kB)

Collecting ipaddress==1.0.16 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 74))

Downloading ipaddress-1.0.16-py27-none-any.whl

Collecting linecache2==1.0.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 77))

Downloading linecache2-1.0.0-py2.py3-none-any.whl

Collecting ordereddict==1.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 80))

Downloading ordereddict-1.1.tar.gz

Collecting parsedatetime==2.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 82))

Downloading parsedatetime-2.1-py2-none-any.whl

Collecting pbr==1.8.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 85))

Downloading pbr-1.8.1-py2.py3-none-any.whl (89kB)

Collecting pyasn1==0.1.9 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 88))

Downloading pyasn1-0.1.9-py2.py3-none-any.whl

Collecting pyOpenSSL==16.2.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 100))

Downloading pyOpenSSL-16.2.0-py2.py3-none-any.whl (43kB)

Collecting pyparsing==2.1.8 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 103))

Downloading pyparsing-2.1.8-py2.py3-none-any.whl (54kB)

Collecting pyRFC3339==1.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 112))

Downloading pyRFC3339-1.0-py2.py3-none-any.whl

Collecting python-augeas==0.5.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 115))

Downloading python-augeas-0.5.0.tar.gz (90kB)

Collecting pytz==2015.7 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 117))

Downloading pytz-2015.7-py2.py3-none-any.whl (476kB)

Collecting requests==2.12.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 131))

Downloading requests-2.12.1-py2.py3-none-any.whl (574kB)

Requirement already satisfied (use --upgrade to upgrade): six==1.10.0 in /root/.local/share/letsencrypt/lib/python2.7/site-packages (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 134))

Collecting traceback2==1.4.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 137))

Downloading traceback2-1.4.0-py2.py3-none-any.whl

Collecting unittest2==1.1.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 140))

Downloading unittest2-1.1.0-py2.py3-none-any.whl (96kB)

Collecting zope.component==4.2.2 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 143))

Downloading zope.component-4.2.2.tar.gz (546kB)

Collecting zope.event==4.1.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 145))

Downloading zope.event-4.1.0.tar.gz (476kB)

Collecting zope.interface==4.1.3 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 147))

Downloading zope.interface-4.1.3.tar.gz (141kB)

Collecting mock==1.0.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 165))

Downloading mock-1.0.1.zip (861kB)

Collecting letsencrypt==0.7.0 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 168))

Downloading letsencrypt-0.7.0-py2-none-any.whl

Collecting acme==0.14.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 174))

Downloading acme-0.14.1-py2.py3-none-any.whl (96kB)

Collecting certbot==0.14.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 177))

Downloading certbot-0.14.1-py2.py3-none-any.whl (248kB)

Collecting certbot-apache==0.14.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 180))

Downloading certbot_apache-0.14.1-py2.py3-none-any.whl (134kB)

Collecting certbot-nginx==0.14.1 (from -r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 183))

Downloading certbot_nginx-0.14.1-py2.py3-none-any.whl (64kB)

Requirement already satisfied (use --upgrade to upgrade): setuptools>=11.3 in /root/.local/share/letsencrypt/lib/python2.7/site-packages (from cryptography==1.5.3->-r /tmp/tmp.sSOf1HDXvB/letsencrypt-auto-requirements.txt (line 42))

Installing collected packages: argparse, pycparser, cffi, ConfigArgParse, configobj, idna, pyasn1, enum34, ipaddress, cryptography, funcsigs, linecache2, ordereddict, parsedatetime, pbr, pyOpenSSL, pyparsing, pytz, pyRFC3339, python-augeas, requests, traceback2, unittest2, zope.interface, zope.event, zope.component, mock, acme, certbot, letsencrypt, certbot-apache, certbot-nginx

Running setup.py install for pycparser: started

Running setup.py install for pycparser: finished with status 'done'

Running setup.py install for cffi: started

Running setup.py install for cffi: finished with status 'done'

Running setup.py install for ConfigArgParse: started

Running setup.py install for ConfigArgParse: finished with status 'done'

Running setup.py install for configobj: started

Running setup.py install for configobj: finished with status 'done'

Running setup.py install for enum34: started

Running setup.py install for enum34: finished with status 'done'

Running setup.py install for cryptography: started

Running setup.py install for cryptography: finished with status 'error'

Complete output from command /root/.local/share/letsencrypt/bin/python2.7 -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-l6BZOB/cryptography/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /tmp/pip-n0qt5Y-record/install-record.txt --single-version-externally-managed --compile --install-headers /root/.local/share/letsencrypt/include/site/python2.7/cryptography:

running install

running build

running build_py

creating build

creating build/lib.linux-x86_64-2.7

creating build/lib.linux-x86_64-2.7/cryptography

copying src/cryptography/__init__.py -> build/lib.linux-x86_64-2.7/cryptography

copying src/cryptography/exceptions.py -> build/lib.linux-x86_64-2.7/cryptography

copying src/cryptography/__about__.py -> build/lib.linux-x86_64-2.7/cryptography

copying src/cryptography/fernet.py -> build/lib.linux-x86_64-2.7/cryptography

copying src/cryptography/utils.py -> build/lib.linux-x86_64-2.7/cryptography

creating build/lib.linux-x86_64-2.7/cryptography/x509

copying src/cryptography/x509/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/x509

copying src/cryptography/x509/base.py -> build/lib.linux-x86_64-2.7/cryptography/x509

copying src/cryptography/x509/name.py -> build/lib.linux-x86_64-2.7/cryptography/x509

copying src/cryptography/x509/extensions.py -> build/lib.linux-x86_64-2.7/cryptography/x509

copying src/cryptography/x509/oid.py -> build/lib.linux-x86_64-2.7/cryptography/x509

copying src/cryptography/x509/general_name.py -> build/lib.linux-x86_64-2.7/cryptography/x509

creating build/lib.linux-x86_64-2.7/cryptography/hazmat

copying src/cryptography/hazmat/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings

copying src/cryptography/hazmat/bindings/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives

copying src/cryptography/hazmat/primitives/hmac.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives

copying src/cryptography/hazmat/primitives/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives

copying src/cryptography/hazmat/primitives/keywrap.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives

copying src/cryptography/hazmat/primitives/padding.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives

copying src/cryptography/hazmat/primitives/serialization.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives

copying src/cryptography/hazmat/primitives/constant_time.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives

copying src/cryptography/hazmat/primitives/hashes.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives

copying src/cryptography/hazmat/primitives/cmac.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/backends

copying src/cryptography/hazmat/backends/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends

copying src/cryptography/hazmat/backends/multibackend.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends

copying src/cryptography/hazmat/backends/interfaces.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/commoncrypto

copying src/cryptography/hazmat/bindings/commoncrypto/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/commoncrypto

copying src/cryptography/hazmat/bindings/commoncrypto/binding.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/commoncrypto

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/openssl

copying src/cryptography/hazmat/bindings/openssl/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/openssl

copying src/cryptography/hazmat/bindings/openssl/_conditional.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/openssl

copying src/cryptography/hazmat/bindings/openssl/binding.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/bindings/openssl

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/interfaces

copying src/cryptography/hazmat/primitives/interfaces/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/interfaces

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/twofactor

copying src/cryptography/hazmat/primitives/twofactor/totp.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/twofactor

copying src/cryptography/hazmat/primitives/twofactor/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/twofactor

copying src/cryptography/hazmat/primitives/twofactor/hotp.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/twofactor

copying src/cryptography/hazmat/primitives/twofactor/utils.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/twofactor

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric

copying src/cryptography/hazmat/primitives/asymmetric/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric

copying src/cryptography/hazmat/primitives/asymmetric/dsa.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric

copying src/cryptography/hazmat/primitives/asymmetric/padding.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric

copying src/cryptography/hazmat/primitives/asymmetric/ec.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric

copying src/cryptography/hazmat/primitives/asymmetric/utils.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric

copying src/cryptography/hazmat/primitives/asymmetric/rsa.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric

copying src/cryptography/hazmat/primitives/asymmetric/dh.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/asymmetric

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/ciphers

copying src/cryptography/hazmat/primitives/ciphers/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/ciphers

copying src/cryptography/hazmat/primitives/ciphers/base.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/ciphers

copying src/cryptography/hazmat/primitives/ciphers/algorithms.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/ciphers

copying src/cryptography/hazmat/primitives/ciphers/modes.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/ciphers

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf

copying src/cryptography/hazmat/primitives/kdf/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf

copying src/cryptography/hazmat/primitives/kdf/hkdf.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf

copying src/cryptography/hazmat/primitives/kdf/concatkdf.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf

copying src/cryptography/hazmat/primitives/kdf/pbkdf2.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf

copying src/cryptography/hazmat/primitives/kdf/kbkdf.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf

copying src/cryptography/hazmat/primitives/kdf/x963kdf.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/primitives/kdf

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto

copying src/cryptography/hazmat/backends/commoncrypto/hmac.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto

copying src/cryptography/hazmat/backends/commoncrypto/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto

copying src/cryptography/hazmat/backends/commoncrypto/backend.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto

copying src/cryptography/hazmat/backends/commoncrypto/hashes.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto

copying src/cryptography/hazmat/backends/commoncrypto/ciphers.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/commoncrypto

creating build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/hmac.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/__init__.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/dsa.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/x509.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/backend.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/ec.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/hashes.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/cmac.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/ciphers.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/utils.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/rsa.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/decode_asn1.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

copying src/cryptography/hazmat/backends/openssl/encode_asn1.py -> build/lib.linux-x86_64-2.7/cryptography/hazmat/backends/openssl

running egg_info

writing requirements to src/cryptography.egg-info/requires.txt

writing src/cryptography.egg-info/PKG-INFO

writing top-level names to src/cryptography.egg-info/top_level.txt

writing dependency_links to src/cryptography.egg-info/dependency_links.txt

writing entry points to src/cryptography.egg-info/entry_points.txt

warning: manifest_maker: standard file '-c' not found

reading manifest file 'src/cryptography.egg-info/SOURCES.txt'

reading manifest template 'MANIFEST.in'

no previously-included directories found matching 'docs/_build'

warning: no previously-included files matching '*' found under directory 'vectors'

writing manifest file 'src/cryptography.egg-info/SOURCES.txt'

running build_ext

generating cffi module 'build/temp.linux-x86_64-2.7/_padding.c'

creating build/temp.linux-x86_64-2.7

generating cffi module 'build/temp.linux-x86_64-2.7/_constant_time.c'

generating cffi module 'build/temp.linux-x86_64-2.7/_openssl.c'

building '_openssl' extension

creating build/temp.linux-x86_64-2.7/build

creating build/temp.linux-x86_64-2.7/build/temp.linux-x86_64-2.7

gcc -pthread -fno-strict-aliasing -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -I/usr/include/python2.7 -c build/temp.linux-x86_64-2.7/_openssl.c -o build/temp.linux-x86_64-2.7/build/temp.linux-x86_64-2.7/_openssl.o

virtual memory exhausted: Cannot allocate memory

error: command 'gcc' failed with exit status 1

----------------------------------------

Command "/root/.local/share/letsencrypt/bin/python2.7 -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-l6BZOB/cryptography/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /tmp/pip-n0qt5Y-record/install-record.txt --single-version-externally-managed --compile --install-headers /root/.local/share/letsencrypt/include/site/python2.7/cryptography" failed with error code 1 in /tmp/pip-build-l6BZOB/cryptography

You are using pip version 8.0.3, however version 9.0.1 is available.

You should consider upgrading via the 'pip install --upgrade pip' command.

=====================================================

Certbot has problem setting up the virtual environment.

Based on your pip output, the problem can likely be fixed by

increasing the available memory.

Consult https://certbot.eff.org/docs/install.html#problems-with-python-virtual-environment

for possible solutions.

You may also find some support resources at https://certbot.eff.org/support/ .

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

$ sudo pip install --upgrade pip
Requirement already up-to-date: pip in /usr/lib/python2.7/dist-packages

1 2	$ sudo pip install --upgrade pip Requirement already up-to-date: pip in /usr/lib/python2.7/dist-packages

最新版なんですけど。

$ pip -V
pip 9.0.1 from /usr/lib/python2.7/dist-packages (python 2.7)

1 2	$ pip -V pip 9.0.1 from /usr/lib/python2.7/dist-packages (python 2.7)

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

$ sudo vi /usr/local/src/letsencrypt/certbot-auto
----------------
pyparsing==2.1.8 \
    --hash=sha256:2f0f5ceb14eccd5aef809d6382e87df22ca1da583c79f6db01675ce7d7f49c18 \
    --hash=sha256:03a4869b9f3493807ee1f1cb405e6d576a1a2ca4d81a982677c0c1ad6177c56b \
    --hash=sha256:ab09aee814c0241ff0c503cff30018219fe1fc14501d89f406f4664a0ec9fbcd \
    --hash=sha256:6e9a7f052f8e26bcf749e4033e3115b6dc7e3c85aafcb794b9a88c9d9ef13c97 \
    --hash=sha256:9f463a6bcc4eeb6c08f1ed84439b17818e2085937c0dee0d7674ac127c67c12b \
    --hash=sha256:3626b4d81cfb300dad57f52f2f791caaf7b06c09b368c0aa7b868e53a5775424 \
    --hash=sha256:367b90cc877b46af56d4580cd0ae278062903f02b8204ab631f5a2c0f50adfd0 \
 -  --hash=sha256:9f1ea360086cd68681e7f4ca8f1f38df47bf81942a0d76a9673c2d23eff35b13
 +  --hash=sha256:9f1ea360086cd68681e7f4ca8f1f38df47bf81942a0d76a9673c2d23eff35b13 \
 +  --no-binary pycparser
----------------

$ sudo vi /usr/local/src/letsencrypt/certbot-auto

----------------

pyparsing==2.1.8 \

--hash=sha256:2f0f5ceb14eccd5aef809d6382e87df22ca1da583c79f6db01675ce7d7f49c18 \

--hash=sha256:03a4869b9f3493807ee1f1cb405e6d576a1a2ca4d81a982677c0c1ad6177c56b \

--hash=sha256:ab09aee814c0241ff0c503cff30018219fe1fc14501d89f406f4664a0ec9fbcd \

--hash=sha256:6e9a7f052f8e26bcf749e4033e3115b6dc7e3c85aafcb794b9a88c9d9ef13c97 \

--hash=sha256:9f463a6bcc4eeb6c08f1ed84439b17818e2085937c0dee0d7674ac127c67c12b \

--hash=sha256:3626b4d81cfb300dad57f52f2f791caaf7b06c09b368c0aa7b868e53a5775424 \

--hash=sha256:367b90cc877b46af56d4580cd0ae278062903f02b8204ab631f5a2c0f50adfd0 \

- --hash=sha256:9f1ea360086cd68681e7f4ca8f1f38df47bf81942a0d76a9673c2d23eff35b13

+ --hash=sha256:9f1ea360086cd68681e7f4ca8f1f38df47bf81942a0d76a9673c2d23eff35b13 \

+ --no-binary pycparser

----------------

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

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

$ sudo service httpd reload

1	$ sudo service httpd reload

を忘れずに。

以下参考サイト）

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して探してください。

#!/bin/bash

# zoneファイルがある場所
PATH="/var/named/zone"
#PATH="./zone_test" # テスト用

i=0
j=0
for zone_file in `\/usr/bin/find ${PATH} -maxdepth 1 -name '*.zone'`; do
    echo "file path -> ${zone_file}"

    # get hostname
    base=${zone_file#${PATH}/}
    echo "base -> ${base}"
    host=${base%.zone}
    echo "host -> ${host}"
    echo "${host} Start ----------"

    ret=`\/bin/grep "v=DMARC1;" "$zone_file"`

    if [ "$ret" ]; then
       # "v=DMARC1;"がすでに書いてあったらスキップ
       echo 'dmarc record is already exist.'
       i=$((++i))
    else
       # backupしたければ#を外す
       #ext=`/bin/date "+%Y%m%d"`
       #/bin/cp $zone_file "$zone_file.$ext"

       # add dmarc record
       echo 'Adding dmarc record...'
       echo "_dmarc IN TXT \"v=DMARC1; p=none\"" >> $zone_file
       j=$((++j))
    fi
    echo 'Complete add record.'
    echo "${host} End ----------"
done

total=$(($i+$j))
echo "Files nothing to do = $i"
echo "Files added record = $j"
echo "total = $total"

exit

#!/bin/bash

# zoneファイルがある場所

PATH="/var/named/zone"

#PATH="./zone_test" # テスト用

i=0

j=0

for zone_file in `\/usr/bin/find ${PATH} -maxdepth 1 -name '*.zone'`; do

echo "file path -> ${zone_file}"

# get hostname

base=${zone_file#${PATH}/}

echo "base -> ${base}"

host=${base%.zone}

echo "host -> ${host}"

echo "${host} Start ----------"

ret=`\/bin/grep "v=DMARC1;" "$zone_file"`

if [ "$ret" ]; then

# "v=DMARC1;"がすでに書いてあったらスキップ

echo 'dmarc record is already exist.'

i=$((++i))

else

# backupしたければ#を外す

#ext=`/bin/date "+%Y%m%d"`

#/bin/cp $zone_file "$zone_file.$ext"

# add dmarc record

echo 'Adding dmarc record...'

echo "_dmarc IN TXT \"v=DMARC1; p=none\"" >> $zone_file

j=$((++j))

echo 'Complete add record.'

echo "${host} End ----------"

done

total=$(($i+$j))

echo "Files nothing to do = $i"

echo "Files added record = $j"

echo "total = $total"

exit

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」を使う

参考リンク）

http://weblabo.oscasierra.net/letsencrypt-2/

とても便利なGROUP_CONCAT（）

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

使用例
tb_family

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）をグループ化して表示

SELECT `tb_family`.id AS family_id 
GROUP_CONCAT(
 `tb_parent`.parent_name
 ORDER BY `tb_parent`.id ASC
 SEPARATOR ',' -- カンマ区切り
 ) AS all_parent 
FROM `tb_family` 
LEFT JOIN `tb_parent` -- parentテーブル結合 
ON `tb\_parent`.family_id = `tb\_family`.id 
GROUP BY `tb\_family`.id -- 家庭IDでグループ化

SELECT `tb_family`.id AS family_id

GROUP_CONCAT(

`tb_parent`.parent_name

ORDER BY `tb_parent`.id ASC

SEPARATOR ',' -- カンマ区切り

) AS all_parent

FROM `tb_family`

LEFT JOIN `tb_parent` -- parentテーブル結合

ON `tb\_parent`.family_id = `tb\_family`.id

GROUP BY `tb\_family`.id -- 家庭IDでグループ化

結果

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

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

SELECT `tb_family`.id AS family_id 
GROUP_CONCAT(
 `tb_parent`.parent_name
 ORDER BY `tb_parent`.id ASC
 SEPARATOR ',' -- カンマ区切り
 ) AS all_parent, 
GROUP_CONCAT(
 `tb_student`.student_name
 ORDER BY `tb_student`.id ASC
 SEPARATOR ',' -- カンマ区切り
 ) AS all_student
FROM `tb_family` 
LEFT JOIN `tb_parent` -- parentテーブル結合 
ON `tb\_parent`.family_id = `tb\_family`.id 
LEFT JOIN `tb_student` -- studentテーブル結合 
ON `tb\_student`.family_id = `tb\_family`.id 
GROUP BY `tb\_family`.id -- 家庭IDでグループ化

SELECT `tb_family`.id AS family_id

GROUP_CONCAT(

`tb_parent`.parent_name

ORDER BY `tb_parent`.id ASC

SEPARATOR ',' -- カンマ区切り

) AS all_parent,

GROUP_CONCAT(

`tb_student`.student_name

ORDER BY `tb_student`.id ASC

SEPARATOR ',' -- カンマ区切り

) AS all_student

FROM `tb_family`

LEFT JOIN `tb_parent` -- parentテーブル結合

ON `tb\_parent`.family_id = `tb\_family`.id

LEFT JOIN `tb_student` -- studentテーブル結合

ON `tb\_student`.family_id = `tb\_family`.id

GROUP BY `tb\_family`.id -- 家庭IDでグループ化

結果

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

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

SELECT `tb_family`.id AS family_id 
GROUP_CONCAT(
 DISTINCT(`tb_parent`.parent_name) -- 重複削除
 ORDER BY `tb_parent`.id ASC
 SEPARATOR ','
 ) AS all_parent, 
GROUP_CONCAT(
 DISTINCT(`tb_student`.student_name) -- 重複削除
 ORDER BY `tb_student`.id ASC
 SEPARATOR ','
 ) AS all_student
FROM `tb_family` 
LEFT JOIN `tb_parent`
ON `tb\_parent`.family_id = `tb\_family`.id 
LEFT JOIN `tb_student`
ON `tb\_student`.family_id = `tb\_family`.id 
GROUP BY `tb\_family`.id

SELECT `tb_family`.id AS family_id

GROUP_CONCAT(

DISTINCT(`tb_parent`.parent_name) -- 重複削除

ORDER BY `tb_parent`.id ASC

SEPARATOR ','

) AS all_parent,

GROUP_CONCAT(

DISTINCT(`tb_student`.student_name) -- 重複削除

ORDER BY `tb_student`.id ASC

SEPARATOR ','

) AS all_student

FROM `tb_family`

LEFT JOIN `tb_parent`

ON `tb\_parent`.family_id = `tb\_family`.id

LEFT JOIN `tb_student`

ON `tb\_student`.family_id = `tb\_family`.id

GROUP BY `tb\_family`.id

結果

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

Linuxコマンドのワンライナー集

全文検索

grep -rnw path -e "search_string"

1	grep -rnw path -e "search_string"

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

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

grep -rnw ./hoge -e "fuga"

1	grep -rnw ./hoge -e "fuga"

指定のディレクトリ以下を再帰的にパーミッション変更

find path -type d -exec chmod permission \{\} \;

1	find path -type d -exec chmod permission \{\} \;

使用例：hogeディレクトリ以下の、すべてのディレクトリのパーミッションを755に変更

find ./hoge -type d -exec chmod 755 \{\} \;

1	find ./hoge -type d -exec chmod 755 \{\} \;

指定のディレクトリ以下の特定の名前のファイルを再帰的に削除

find path -name filename -exec rm -fr {} \;

1	find path -name filename -exec rm -fr {} \;

使用例：hogeディレクトリ以下の、desktop.iniファイルを全て削除

find hoge -name desctop.ini -exec rm -fr {} \;

1	find hoge -name desctop.ini -exec rm -fr {} \;

使用例：hogeディレクトリ以下の、拡張子が「.ini」のファイルを全て削除

find hoge -name *.ini -exec rm -fr {} \;

1	find hoge -name *.ini -exec rm -fr {} \;

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

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

　　：
# DB接続、SQL組み立て処理
　　：
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, $value); # 第三引数に値のデータタイプも指定可能
　　：
# 後処理
　　：

　　：

# DB接続、SQL組み立て処理

　　：

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, $value); # 第三引数に値のデータタイプも指定可能

　　：

# 後処理

　　：

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

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, "あ, い, う");

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, "あ, い, う");

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

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, "あ");

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, "あ");

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

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

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

単純に書くと、

my $sql = "SELECT * FROM table_name WHERE column_name IN (?,?,?)";
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, "あ");
$sth->bind_param(2, "い");
$sth->bind_param(3, "う");

my $sql = "SELECT * FROM table_name WHERE column_name IN (?,?,?)";

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, "あ");

$sth->bind_param(2, "い");

$sth->bind_param(3, "う");

てな感じ。

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

・DB処理用ファイル（database.pl）

package database;
　　：
# DB接続処理
　　：
sub db_exec {
  my($sql, $bind) = @_;
  our $sth; my $exec;
  $sth = $db::dbh->prepare($sql) || die $db::dbh->errstr();
  if($bind){
    my $el = 1;
    map {$sth->bind_param($el,$$_{value},$$_{type}); $el++;} @$bind;
  }
  $exec = $sth->execute() || die $sth->errstr();
}

package database;

　　：

# DB接続処理

　　：

sub db_exec {

my($sql, $bind) = @_;

our $sth; my $exec;

$sth = $db::dbh->prepare($sql) || die $db::dbh->errstr();

if($bind){

my $el = 1;

map {$sth->bind_param($el,$$_{value},$$_{type}); $el++;} @$bind;

}

$exec = $sth->execute() || die $sth->errstr();

}

・呼び出し元

requare database.pl;
　　：
my @in = (あ, い, う);
&database::db_exec("
  SELECT * FROM table_name
  WHERE column IN (". join(',', map{'?'} @in ).") 
  ",
  [
    ( map {+{ value => $_ }} @in )
    # さらにプレースホルダがある場合はここに
    ,{ value => 'abc' }
    # のように書く
  ]
  #↑ +{...}はハッシュのリファレンスを示す
);
　　：

requare database.pl;

　　：

my @in = (あ, い, う);

&database::db_exec("

SELECT * FROM table_name

WHERE column IN (". join(',', map{'?'} @in ).")

[

( map {+{ value => $_ }} @in )

# さらにプレースホルダがある場合はここに

,{ value => 'abc' }

# のように書く

]

#↑ +{...}はハッシュのリファレンスを示す

);

　　：

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

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

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

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

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

xE3x81x8C

となります。

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

xE3x81x8B + xE3x82x99　（「か」＋「 ○゛」）

でもOKなのです。

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

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

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

$str = "がぎぐげござじずぜぞだぢづでど・・・";

# Hiragana
$str =~ s/\xE3\x81\x8B\xE3\x82\x99/\xE3\x81\x8C/g;	# か+○゛=> が
$str =~ s/\xE3\x81\x8D\xE3\x82\x99/\xE3\x81\x8E/g;	# き+○゛=> ぎ
$str =~ s/\xE3\x81\x8F\xE3\x82\x99/\xE3\x81\x90/g;	# く+○゛=> ぐ
$str =~ s/\xE3\x81\x91\xE3\x82\x99/\xE3\x81\x92/g;	# け+○゛=> げ
$str =~ s/\xE3\x81\x93\xE3\x82\x99/\xE3\x81\x94/g;	# こ+○゛=> ご
$str =~ s/\xE3\x81\x95\xE3\x82\x99/\xE3\x81\x96/g;	# さ+○゛=> ざ
$str =~ s/\xE3\x81\x97\xE3\x82\x99/\xE3\x81\x98/g;	# し+○゛=> じ
$str =~ s/\xE3\x81\x99\xE3\x82\x99/\xE3\x81\x9A/g;	# す+○゛=> ず
$str =~ s/\xE3\x81\x9B\xE3\x82\x99/\xE3\x81\x9C/g;	# せ+○゛=> ぜ
$str =~ s/\xE3\x81\x9D\xE3\x82\x99/\xE3\x81\x9E/g;	# そ+○゛=> ぞ
$str =~ s/\xE3\x81\x9F\xE3\x82\x99/\xE3\x81\xA0/g;	# た+○゛=> だ
$str =~ s/\xE3\x81\xA1\xE3\x82\x99/\xE3\x81\xA2/g;	# ち+○゛=> ぢ
$str =~ s/\xE3\x81\xA4\xE3\x82\x99/\xE3\x81\xA5/g;	# つ+○゛=> づ
$str =~ s/\xE3\x81\xA6\xE3\x82\x99/\xE3\x81\xA7/g;	# て+○゛=> で
$str =~ s/\xE3\x81\xA8\xE3\x82\x99/\xE3\x81\xA9/g;	# と+○゛=> ど
$str =~ s/\xE3\x81\xAF\xE3\x82\x99/\xE3\x81\xB0/g;	# は+○゛=> ば
$str =~ s/\xE3\x81\xAF\xE3\x82\x9A/\xE3\x81\xB1/g;	# は+○゜=> ぱ
$str =~ s/\xE3\x81\xB2\xE3\x82\x99/\xE3\x81\xB3/g;	# ひ+○゛=> び
$str =~ s/\xE3\x81\xB2\xE3\x82\x9A/\xE3\x81\xB4/g;	# ひ+○゜=> ぴ
$str =~ s/\xE3\x81\xB5\xE3\x82\x99/\xE3\x81\xB6/g;	# ふ+○゛=> ぶ
$str =~ s/\xE3\x81\xB5\xE3\x82\x9A/\xE3\x81\xB7/g;	# ふ+○゜=> ぷ
$str =~ s/\xE3\x81\xB8\xE3\x82\x99/\xE3\x81\xB9/g;	# へ+○゛=> べ
$str =~ s/\xE3\x81\xB8\xE3\x82\x9A/\xE3\x81\xBA/g;	# へ+○゜=> ぺ
$str =~ s/\xE3\x81\xBB\xE3\x82\x99/\xE3\x81\xBC/g;	# ほ+○゛=> ぼ
$str =~ s/\xE3\x81\xBB\xE3\x82\x9A/\xE3\x81\xBD/g;	# ほ+○゜=> ぽ

# Katakana
$str =~ s/\xE3\x82\xAB\xE3\x82\x99/\xE3\x82\xAC/g;	# カ+○゛=> ガ
$str =~ s/\xE3\x82\xAD\xE3\x82\x99/\xE3\x82\xAE/g;	# キ+○゛=> ギ
$str =~ s/\xE3\x82\xAF\xE3\x82\x99/\xE3\x82\xB0/g;	# ク+○゛=> グ
$str =~ s/\xE3\x82\xB1\xE3\x82\x99/\xE3\x82\xB2/g;	# ケ+○゛=> ゲ
$str =~ s/\xE3\x82\xB3\xE3\x82\x99/\xE3\x82\xB4/g;	# コ+○゛=> ゴ
$str =~ s/\xE3\x82\xB5\xE3\x82\x99/\xE3\x82\xB6/g;	# サ+○゛=> ザ
$str =~ s/\xE3\x82\xB7\xE3\x82\x99/\xE3\x82\xB8/g;	# シ+○゛=> ジ
$str =~ s/\xE3\x82\xB9\xE3\x82\x99/\xE3\x82\xBA/g;	# ス+○゛=> ズ
$str =~ s/\xE3\x82\xBB\xE3\x82\x99/\xE3\x82\xBC/g;	# セ+○゛=> ゼ
$str =~ s/\xE3\x82\xBD\xE3\x82\x99/\xE3\x82\xBE/g;	# ソ+○゛=> ゾ
$str =~ s/\xE3\x82\xBF\xE3\x82\x99/\xE3\x83\x80/g;	# タ+○゛=> ダ
$str =~ s/\xE3\x83\x81\xE3\x82\x99/\xE3\x83\x82/g;	# チ+○゛=> ヂ
$str =~ s/\xE3\x83\x84\xE3\x82\x99/\xE3\x83\x85/g;	# ツ+○゛=> ヅ
$str =~ s/\xE3\x83\x86\xE3\x82\x99/\xE3\x83\x87/g;	# テ+○゛=> デ
$str =~ s/\xE3\x83\x88\xE3\x82\x99/\xE3\x83\x89/g;	# ト+○゛=> ド
$str =~ s/\xE3\x83\x8F\xE3\x82\x99/\xE3\x83\x90/g;	# ハ+○゛=> バ
$str =~ s/\xE3\x83\x8F\xE3\x82\x9A/\xE3\x83\x91/g;	# ハ+○゜=> パ
$str =~ s/\xE3\x83\x92\xE3\x82\x99/\xE3\x83\x93/g;	# ヒ+○゛=> ビ
$str =~ s/\xE3\x83\x92\xE3\x82\x9A/\xE3\x83\x94/g;	# ヒ+○゜=> ピ
$str =~ s/\xE3\x83\x95\xE3\x82\x99/\xE3\x83\x96/g;	# フ+○゛=> ブ
$str =~ s/\xE3\x83\x95\xE3\x82\x9A/\xE3\x83\x97/g;	# フ+○゜=> プ
$str =~ s/\xE3\x83\x98\xE3\x82\x99/\xE3\x83\x99/g;	# ヘ+○゛=> ベ
$str =~ s/\xE3\x83\x98\xE3\x82\x9A/\xE3\x83\x9A/g;	# ヘ+○゜=> ペ
$str =~ s/\xE3\x83\x9B\xE3\x82\x99/\xE3\x83\x9C/g;	# ホ+○゛=> ボ
$str =~ s/\xE3\x83\x9B\xE3\x82\x9A/\xE3\x83\x9D/g;	# ホ+○゜=> ポ

$str = "がぎぐげござじずぜぞだぢづでど・・・";

# Hiragana

$str =~ s/\xE3\x81\x8B\xE3\x82\x99/\xE3\x81\x8C/g; # か+○゛=> が

$str =~ s/\xE3\x81\x8D\xE3\x82\x99/\xE3\x81\x8E/g; # き+○゛=> ぎ

$str =~ s/\xE3\x81\x8F\xE3\x82\x99/\xE3\x81\x90/g; # く+○゛=> ぐ

$str =~ s/\xE3\x81\x91\xE3\x82\x99/\xE3\x81\x92/g; # け+○゛=> げ

$str =~ s/\xE3\x81\x93\xE3\x82\x99/\xE3\x81\x94/g; # こ+○゛=> ご

$str =~ s/\xE3\x81\x95\xE3\x82\x99/\xE3\x81\x96/g; # さ+○゛=> ざ

$str =~ s/\xE3\x81\x97\xE3\x82\x99/\xE3\x81\x98/g; # し+○゛=> じ

$str =~ s/\xE3\x81\x99\xE3\x82\x99/\xE3\x81\x9A/g; # す+○゛=> ず

$str =~ s/\xE3\x81\x9B\xE3\x82\x99/\xE3\x81\x9C/g; # せ+○゛=> ぜ

$str =~ s/\xE3\x81\x9D\xE3\x82\x99/\xE3\x81\x9E/g; # そ+○゛=> ぞ

$str =~ s/\xE3\x81\x9F\xE3\x82\x99/\xE3\x81\xA0/g; # た+○゛=> だ

$str =~ s/\xE3\x81\xA1\xE3\x82\x99/\xE3\x81\xA2/g; # ち+○゛=> ぢ

$str =~ s/\xE3\x81\xA4\xE3\x82\x99/\xE3\x81\xA5/g; # つ+○゛=> づ

$str =~ s/\xE3\x81\xA6\xE3\x82\x99/\xE3\x81\xA7/g; # て+○゛=> で

$str =~ s/\xE3\x81\xA8\xE3\x82\x99/\xE3\x81\xA9/g; # と+○゛=> ど

$str =~ s/\xE3\x81\xAF\xE3\x82\x99/\xE3\x81\xB0/g; # は+○゛=> ば

$str =~ s/\xE3\x81\xAF\xE3\x82\x9A/\xE3\x81\xB1/g; # は+○゜=> ぱ

$str =~ s/\xE3\x81\xB2\xE3\x82\x99/\xE3\x81\xB3/g; # ひ+○゛=> び

$str =~ s/\xE3\x81\xB2\xE3\x82\x9A/\xE3\x81\xB4/g; # ひ+○゜=> ぴ

$str =~ s/\xE3\x81\xB5\xE3\x82\x99/\xE3\x81\xB6/g; # ふ+○゛=> ぶ

$str =~ s/\xE3\x81\xB5\xE3\x82\x9A/\xE3\x81\xB7/g; # ふ+○゜=> ぷ

$str =~ s/\xE3\x81\xB8\xE3\x82\x99/\xE3\x81\xB9/g; # へ+○゛=> べ

$str =~ s/\xE3\x81\xB8\xE3\x82\x9A/\xE3\x81\xBA/g; # へ+○゜=> ぺ

$str =~ s/\xE3\x81\xBB\xE3\x82\x99/\xE3\x81\xBC/g; # ほ+○゛=> ぼ

$str =~ s/\xE3\x81\xBB\xE3\x82\x9A/\xE3\x81\xBD/g; # ほ+○゜=> ぽ

# Katakana

$str =~ s/\xE3\x82\xAB\xE3\x82\x99/\xE3\x82\xAC/g; # カ+○゛=> ガ

$str =~ s/\xE3\x82\xAD\xE3\x82\x99/\xE3\x82\xAE/g; # キ+○゛=> ギ

$str =~ s/\xE3\x82\xAF\xE3\x82\x99/\xE3\x82\xB0/g; # ク+○゛=> グ

$str =~ s/\xE3\x82\xB1\xE3\x82\x99/\xE3\x82\xB2/g; # ケ+○゛=> ゲ

$str =~ s/\xE3\x82\xB3\xE3\x82\x99/\xE3\x82\xB4/g; # コ+○゛=> ゴ

$str =~ s/\xE3\x82\xB5\xE3\x82\x99/\xE3\x82\xB6/g; # サ+○゛=> ザ

$str =~ s/\xE3\x82\xB7\xE3\x82\x99/\xE3\x82\xB8/g; # シ+○゛=> ジ

$str =~ s/\xE3\x82\xB9\xE3\x82\x99/\xE3\x82\xBA/g; # ス+○゛=> ズ

$str =~ s/\xE3\x82\xBB\xE3\x82\x99/\xE3\x82\xBC/g; # セ+○゛=> ゼ

$str =~ s/\xE3\x82\xBD\xE3\x82\x99/\xE3\x82\xBE/g; # ソ+○゛=> ゾ

$str =~ s/\xE3\x82\xBF\xE3\x82\x99/\xE3\x83\x80/g; # タ+○゛=> ダ

$str =~ s/\xE3\x83\x81\xE3\x82\x99/\xE3\x83\x82/g; # チ+○゛=> ヂ

$str =~ s/\xE3\x83\x84\xE3\x82\x99/\xE3\x83\x85/g; # ツ+○゛=> ヅ

$str =~ s/\xE3\x83\x86\xE3\x82\x99/\xE3\x83\x87/g; # テ+○゛=> デ

$str =~ s/\xE3\x83\x88\xE3\x82\x99/\xE3\x83\x89/g; # ト+○゛=> ド

$str =~ s/\xE3\x83\x8F\xE3\x82\x99/\xE3\x83\x90/g; # ハ+○゛=> バ

$str =~ s/\xE3\x83\x8F\xE3\x82\x9A/\xE3\x83\x91/g; # ハ+○゜=> パ

$str =~ s/\xE3\x83\x92\xE3\x82\x99/\xE3\x83\x93/g; # ヒ+○゛=> ビ

$str =~ s/\xE3\x83\x92\xE3\x82\x9A/\xE3\x83\x94/g; # ヒ+○゜=> ピ

$str =~ s/\xE3\x83\x95\xE3\x82\x99/\xE3\x83\x96/g; # フ+○゛=> ブ

$str =~ s/\xE3\x83\x95\xE3\x82\x9A/\xE3\x83\x97/g; # フ+○゜=> プ

$str =~ s/\xE3\x83\x98\xE3\x82\x99/\xE3\x83\x99/g; # ヘ+○゛=> ベ

$str =~ s/\xE3\x83\x98\xE3\x82\x9A/\xE3\x83\x9A/g; # ヘ+○゜=> ペ

$str =~ s/\xE3\x83\x9B\xE3\x82\x99/\xE3\x83\x9C/g; # ホ+○゛=> ボ

$str =~ s/\xE3\x83\x9B\xE3\x82\x9A/\xE3\x83\x9D/g; # ホ+○゜=> ポ

ご自由にコピペどうぞ。

参考サイト）

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