自分で作成したページ・ウィジェット・ブロックに思うように商品が表示されない。
ならば、どのようなSQLが流れていて、何が引っかかって表示されないのか調べてみよう、という活動です。
1.環境整備
(1)SSH接続用ターミナルソフト
Tera TermでもPuttyでも、お好きなものをどうぞ。
ではあるのですが、AWS内bitnamiで構築した環境のMy SQLに接続する、という観点で。
Puttyでは、Host Nameにユーザ名@xxxを記載しますが、bitnamiで構築した場合は、
ubuntu@xx.us-east-2.compute.amazonaws.com
というようになります。
そして、Connection＞SSH＞Auth でPrivate keyにppkファイルを登録します。
この接続状態をそのまま保存しておいて、次回からはSaved Sessionから呼び出すだけなので、現在は基本的にはPuttyを使用しています。
Tera Termでは、ホスト欄にxx.us-east-2.compute.amazonaws.comを入力しOK、ユーザ名にubuntu、「RSA/DSA/ECDSA/ED25519鍵を使う」で、ppkファイルではなくpemファイルを指定する、という形になり、ホスト名以外は次回に覚えてくれていないので、ちょっと面倒だなと思っています。ターミナル上での見た目はTeraの方がきれいなので好きなのですが。
(2)WinSCP
ファイルのアップ・ダウンロード用にはこちら。
SFTPで、ホスト名はxx.us-east-2.compute.amazonaws.com、ユーザ名はubuntu、設定＞SSH＞認証の秘密鍵にppkファイルを指定します。
これも、保存しておけば、次回以降の接続はリストから選択するだけなので、よいです。
(3)A5 : SQL Mk-2
これが本日のポイント。
https://a5m2.mmatsubara.com/
DB接続ツールは色々ありますけど、個人的にはこれが最強だと思っています。
特に、最強なのがSQL整形機能。今回のように、ログからSQLを取り出して解析しようと思う時に、Ctrl-Q一発でSQLを見やすくしてくれるのは、非常に助かるので、普段の会社の仕事でも使っております。
これの接続は、ちょっとばかり難しいです。トンネリングでの接続になります。
A5 : SQL の接続を作成する際にはMySQL/MariaDBを選択できます。その中の設定で、SSH2トンネルの指定ができます。
「SSH2トンネル」タブのSSH2ホスト名をxx.us-east-2.compute.amazonaws.com、ユーザーIDをubuntuにして、秘密鍵ファイルにpemファイルを指定します。
その上で、「基本」タブのサーバー名にはSSH2サーバーから見たサーバー名を指定するので、初期の状態ならlocalhostを指定します。Magento用に作成したユーザーID、パスワードを指定しますが、
grant select on *.* to home;
が必要だったように思います（記録し忘れている）。
ということで、この3点セットがそろうと、割と自由にDBもいじれるかと思います。
2.MySQLのクエリトレース
普段はSQL Serverが主戦場なので、トレース取得はProfilerを便利に使用させていただいています。
ではMySQLはどうかということで、こちらを参考にさせていただきました。
http://blog.szmake.net/archives/496
my.cnfはどこにあるかというと、これはbitnami特有で、/opt/bitnami/mysql/ にあります。
また、my.confを編集した後、MySQLをリスタートするのですが、これもbitnami特有で
sudo /opt/bitnami/ctlscript.sh restart mysql
となります。
bitnamiでは、/opt/bitnami 配下に色々なものが存在する、ということを認識しておくだけでもいろいろはかどるかと思います。
3.Magentoで実行されるSQL
で、トレースを取ってみた結果です。
例えば、
SELECT `mg_store_website`.* FROM `mg_store_website`
↓
website_id code name sort_order default_group_id is_default
みたいなのが取れることが分かるだけでも、今後の理解には役立ちそうです。
今回のポイントは、下に書いたSQLでした（A5 : SQL ではこのように整形してくれます。これがよいのです）。
まず、全部LEFT JOINにして、
stock_status_index.stock_status = 1
をコメントアウトすると検索対象になるので、これを何とかする必要があることが分かります。
また、コメントアウトしても、mg_catalog_category_product_index_store1がINNER JOINのままだと、2件しかヒットしないので、visibilityあたりが怪しそうだと考えます。
そうしてみてみると、商品の設定で、在庫状況が在庫切れになっています。
実は、これは在庫ありにしたつもりだったのですが、Advanced Inventoryの子画面で、個数を入力して在庫ありにしないと反映されません。
そもそも、ピザ屋さんのピザなんて在庫管理しない商品にしておくべきで、その辺を属性セットとして登録しておくべきな訳ですが、それはまだ試していません。
また、Visibilityという項目で表示設定を行います。
ここで。「カタログ,検索」を指定しないと表示されません。
これの使い方として、例えば、商品タイプとして、Configurable Productを指定した時に、基本商品は一覧に出すけど、サイズ別の商品設定は一覧には出さない、という場合には、サイズ別の商品は非表示にしておくことでそのような動作になります。
こんな感じで、SQLを見ながら商品設定をしていくと表示されるようになるのですが、最後の砦として、システム＞キャッシュ管理 で更新することにより表示された、ということもありましたので、油断は禁物です。
こんな感じで、商品を表示するめどは立ってきました。
SELECT DISTINCT
`e`.*
, `cat_index`.`position` AS `cat_index_position`
, `price_index`.`price`
, `price_index`.`tax_class_id`
, `price_index`.`final_price`
, IF (
price_index.tier_price IS NOT NULL
, LEAST(price_index.min_price, price_index.tier_price)
, price_index.min_price
) AS `minimal_price`
, `price_index`.`min_price`
, `price_index`.`max_price`
, `price_index`.`tier_price`
, `stock_status_index`.`stock_status` AS `is_salable`
FROM
`mg_catalog_product_entity` AS `e`
left JOIN `mg_catalog_category_product_index_store1` AS `cat_index`
ON cat_index.product_id = e.entity_id
AND cat_index.store_id = ‘1’
AND cat_index.visibility IN (2, 4)
AND cat_index.category_id = ‘2’
left JOIN `mg_catalog_product_index_price` AS `price_index`
ON price_index.entity_id = e.entity_id
AND price_index.website_id = ‘1’
AND price_index.customer_group_id = 0
left JOIN `mg_cataloginventory_stock_status` AS `stock_status_index`
ON e.entity_id = stock_status_index.product_id
AND stock_status_index.website_id = 0
AND stock_status_index.stock_id = 1
WHERE
(((IFNULL(`attribute_set_id`, 0) = ’16’)))
AND (stock_status_index.stock_status = 1)
LIMIT
10
