未分類 – igreks開発日記

GmailからエックスサーバへのSMTP接続がTLSエラーになる件

弊社のように、エックスサーバなど外部のメールアドレスをGmailで一括管理している方も多いはず。

ところが4月8日ころから、エックスサーバを介したメール送信が行えない事象が発生。その時点でエックスサーバで特に障害情報などは出ていませんでした。

送信後に以下のようなエラーが返ってきます。

メールの配信エラー
[名前] の機能を使用して、別のアドレスまたは別のエイリアスからこのメールを送信しようとしています。[名前] のアカウントの設定に誤りがあるか、設定が最新の状態ではありません。設定を確認して、もう一度送信してみてください。
詳細
応答:
TLS Negotiation failed, the certificate doesn't match the host.

メールの配信エラー

[名前] の機能を使用して、別のアドレスまたは別のエイリアスからこのメールを送信しようとしています。[名前] のアカウントの設定に誤りがあるか、設定が最新の状態ではありません。設定を確認して、もう一度送信してみてください。

詳細

応答:

TLS Negotiation failed, the certificate doesn't match the host.

証明書がマッチしないTLSネゴシエーションエラーのようです。Googleのコミュニティでも揉めてますね(笑)。

調べたら、早速詳しくまとめていただいている方がいました。

エックスサーバのSMTPサーバを調べると、証明書のドメイン名は「*.xserver.jp」(ワイルドカード)でしたので、各サーバに割り当てられているsvから始まるサーバ番号がわかればこのドメイン名が分かります。

例えば「sv1234」であれば、証明書のドメイン名「sv1234.xserver.jp」になります。

Gmailの設定で、SMTPサーバ名がこの「sv****.xserver.jp」でない場合は、この名前を指定してやれば解決です。

今見てみたら、エックスサーバのFAQにもそのように案内がありますね。（最近変わった？）

以上です。

CentOS7でのサービス起動・停止・自動起動設定など

サービス一覧と自動起動設定の確認

CentOS6まで

# chkconfig --list

1	# chkconfig --list

CentOS7から

# systemctl list-unit-files -t service

1	# systemctl list-unit-files -t service

enabled →自動起動有効
disabled →自動起動無効
static →単体で自動起動設定ができないサービス

個別に確認したい場合

CentOS6まで

# /etc/rc.d/init.d/"サービス名" status

1	# /etc/rc.d/init.d/"サービス名" status

CentOS7から

# systemctl status "サービス名(Unit名)"

1	# systemctl status "サービス名(Unit名)"

例：sshdの状態確認

# systemctl status sshd.service

1	# systemctl status sshd.service

“…/”Unit名”; eabled;” →自動起動有効
“…/”Unit名”; disabled;” →自動起動無効

Active: active (running) →サービス起動中
Active: inactive (dead) →サービス停止中

自動起動の設定

CentOS6まで

# chkconfig "サービス名" on/off

1	# chkconfig "サービス名" on/off

CentOS7から

# systemctl enable/disable "サービス名(Unit名)"

1	# systemctl enable/disable "サービス名(Unit名)"

例：sshdの自動起動設定

# systemctl enable sshd.service

1	# systemctl enable sshd.service

起動・停止・リスタート・リロード

CentOS6まで

# /etc/rc.d/init.d/"サービス名" "やりたいこと"

1	# /etc/rc.d/init.d/"サービス名" "やりたいこと"

CentOS7から

# systemctl "やりたいこと" "サービス名(Unit名)"

1	# systemctl "やりたいこと" "サービス名(Unit名)"

例：sshdを起動するとき

# systemctl start sshd.service

1	# systemctl start sshd.service

参考URL）
http://qiita.com/haisaihiroki/items/c38cb3c0a331db9b6f69

ひかり電話オフィスでビジネスフォン（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電話なのだから、言われてみれば当たり前のことなのだが、、、。

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

とても便利な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	佐藤　次郎	佐藤　息子

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

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

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

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

例えば下記のSQL。

SELECT	SQL_CALC_FOUND_ROWS
	`history`.*,
	`click`.total_clicks,
	ROUND(
		(`click`.total_clicks / `history`.users) * 100,
		 2
	) AS ctr 
FROM	`history` 
LEFT JOIN (
	SELECT
		`click`.history_id,
		SUM(click_count) AS total_clicks 
	FROM	`click`
	GROUP	BY `click`.history_id
)
ON	`history`.id = `click`.history_id
ORDER BY ctr DESC
LIMIT   0, 30

SELECT SQL_CALC_FOUND_ROWS

`history`.*,

`click`.total_clicks,

ROUND(

(`click`.total_clicks / `history`.users) * 100,

) AS ctr

FROM `history`

LEFT JOIN (

SELECT

`click`.history_id,

SUM(click_count) AS total_clicks

FROM `click`

GROUP BY `click`.history_id

)

ON `history`.id = `click`.history_id

ORDER BY ctr DESC

LIMIT 0, 30

この場合、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テーブルを結合してみる。

SELECT	SQL_CALC_FOUND_ROWS
	   `history`.*,
	   SUM(`click`.click_count) AS total_clicks,
	   ROUND(
              SUM(`click`.click_count) / `history`.users * 100,
              2
           ) AS ctr
FROM       `click`
RIGHT JOIN `history`
ON	   `click`.history_id = `history`.id
-- ここでwhereも使える --
GROUP BY   `history`.id
ORDER BY   SUM(`click`.click_count) / `history`.users * 100
LIMIT      0, 30

SELECT SQL_CALC_FOUND_ROWS

`history`.*,

SUM(`click`.click_count) AS total_clicks,

ROUND(

SUM(`click`.click_count) / `history`.users * 100,

) AS ctr

FROM `click`

RIGHT JOIN `history`

ON `click`.history_id = `history`.id

-- ここでwhereも使える --

GROUP BY `history`.id

ORDER BY SUM(`click`.click_count) / `history`.users * 100

LIMIT 0, 30

ポイントは、

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

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

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

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

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

参考サイト）

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

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