結論 InnoDBのテーブル トランザクションを開始する SELECT ... FOR UPDATEをする 新規追加もしくは更新のクエリを実行する トランザクションを終了する 概要 １件だけの登録、１件のだけの更新時の処理用 同時登録、同時更新によりデータの整合性が取れなくなる事を防ぐ事が目的 行ロックなのかテーブルロックになってしまうかなどは、考えていない サンプル用テーブル idとnoだけのテーブル。InnoDBである事。 CREATE TABLE test ( id int NOT NULL AUTO_INCREMENT, no int NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB; -- １件入れておく INSERT INTO test (no) VALUES (1); 新規作成 仕様はnoは重複してはならない（ユニークインデックスを貼れば済むが）とします。なので挿入するnoが存在していない事をチェックしてから挿入します。「伝票番号などのnoが重複してはならないという仕様ならユニークインデックス張っておいたら？」と思いますか？思いますよね？私も思うんです。でも世の中にはユニークインデックス使用禁止というシステムが存在するので、存在する事を覚えておいてください。 // 伝票番号など 例)100番 $no = 100; $pdo = new \PDO(略); try { $pdo->beginTransaction(); // ロックを取得する // FOR UPDATEで他のセッションがロックを取得している場合、解除されるまで待つ // FOR UPDATEの後ろにNOWAITを付けている場合、解除されるまで待たずに例外がスローされる $stmt = $pdo->query("SELECT COUNT(*) as cnt FROM test WHERE no = {$no} FOR UPDATE"); // $stmt = $pdo->query("SELECT COUNT(*) as cnt FROM test WHERE no = {$no} FOR UPDATE NOWAIT"); // ３秒以内に別ブラウザで叩いて確認する。FOR UPDATE を付けてないと二重登録されてしまう sleep(3); $r = $stmt->fetch(\PDO::FETCH_OBJ); if (0 < $r->cnt) { echo "<h1>no({$no})のデータが既に存在しています。</h1>"; exit; } // 新規追加 $pdo->exec("INSERT INTO test (no) VALUES ({$no})"); $pdo->commit(); echo '<h1>正常に登録されました</h1>'; } catch (\Exception $e) { $pdo->rollBack(); echo '<h1>エラーが発生しました</h1>'; echo $e->getMessage(); // NOWAITでロックが取得できなかった時の例外メッセージ // SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction } 更新処理 なんらかの処理が行われたらnoがインクリメントされる処理があるとします。 もし完全に同時に２回行われた時、１回分のインクリメントしかされない事がないようにロックを取得します。 $pdo = new \PDO(略); try { $pdo->beginTransaction(); // idが1のデータのnoをインクリメントするため現在の値を取得 $stmt = $pdo->query('SELECT no FROM test WHERE id = 1 FOR UPDATE'); // $stmt = $pdo->query('SELECT no FROM test WHERE id = 1 FOR UPDATE NOWAIT'); // ３秒以内に別ブラウザで叩いて確認する。FOR UPDATEが入っていないと２回のインクリメントのはずが１回分しか増えない sleep(3); // 値をインクリメントする更新処理 $r = $stmt->fetch(\PDO::FETCH_OBJ); $new_no = $r->no + 1; $pdo->exec("UPDATE test SET no = {$new_no} WHERE id = 1"); $pdo->commit(); echo '<h1>正常に更新されました</h1>'; echo "{$r->no}から{$new_no}へ更新"; } catch (\Exception $e) { $pdo->rollBack(); echo '<h1>エラーが発生しました</h1>'; echo $e->getMessage(); }

無料でサーバーを立てられるherokuでDjangoを使ったアプリケーションを構築しようとしたところ、たくさんのトラップに引っかかったので、備忘録としてまとめることにしました。 0. 前置き 目的 herokuにDjangoアプリケーションを立てたい リポジトリはGitHubに置いておきたい Djangoで使うDBはMySQLがいい 今回は以上の目的を達成するための流れを一通り説明します。 前提知識 ターミナルの基本操作ができること pythonの環境を整えていること djangoの基本的な使い方を知っていること ローカル環境 環境 項目 OS macOS BigSur 11.5.2 Python 3.9.7, venv(環境管理), pip(パッケージ管理) Django 3.2.7 heroku CLI heroku/7.59.0 darwin-x64 node-v12.21.0 その他 プロジェクト名は[heroku-pj]または[heroku_pj]と記載しています。各自の設定に置き換えてください herokuコマンドに必須の-aオプションは省略しているので、各自で補完してください 1. Pythonの設定 まずはローカルの環境を整えます。 1-1. 仮想環境(venv)の用意 ※Pythonの環境整備は宗派が分かれるので参考程度で。 zsh % python -m venv .venv % source .venv/bin/activate # 警告がしつこく出るのでupgradeする % pip install -U pip 1-2. 必要なライブラリのインストール 必要なライブラリは以下の通りです。(バージョンは執筆当時の最新版です) requirements.txt asgiref==3.4.1 dj-database-url==0.5.0 Django==3.2.7 django-heroku==0.3.1 gunicorn==20.1.0 mysql==0.0.3 mysqlclient==2.0.3 psycopg2==2.9.1 pytz==2021.1 sqlparse==0.4.1 whitenoise==5.3.0 psycopg2のインストールがうまくいかない場合は以下のページを参考にしてください https://dev.classmethod.jp/articles/mac-psycopg2-install/ 1-3. djangoプロジェクトの作成 今回はプロジェクトのルートをgit管理したいので、作ります。 アプリケーションは今回は作らなくても良いです。 zsh % django-admin startproject [heroku_pj] % cd [heroku_pj] 1-4. 各種ファイルの作成 .gitignore 必要なものがあれば適宜追記してください。 .gitignore __pycache__ .DS_Store local.py db.sqlite3 requirements.txt 以下のコマンドで生成します(上記のrequirements.txtをコピペしても良いですが、最新版にしておくことをお勧めします) zsh % pip freeze > requirements.txt Procfile herokuのビルドに必要です。 Procfile web: gunicorn heroku_pj.wsgi --log-file - runtime.txt heroku上のPythonバージョン指定に必要です。 バージョンはこちらのページで最新の対応状況を確認してください。 runtime.txt python-3.9.7 1-5. settings.pyの分離 開発環境(ローカル)と本番環境(heroku上)ではデータベース等の設定が異なりますので、ファイルを分離しておいた方が良いです。 本記事では分離することを前提としてコードを書いているので、分離しない人は1-5, 1-6をスキップしてください。 1-5-1. フォルダ構造 [heroku_pj]-ルート [heroku_pj] settings __init__.py base.py local.py production.py __init__.py asgi.py urls.py wsgi.py manage.py (以下省略) 1-5-2. base.py デフォルトのsettings.pyから以下の項目を除きます。 DEBUG, ALLOWED_HOSTS, SECRET_KEY, DATABASES SECRET_KEYは中身をコピー&メモしておくこと(あとで使います) また、以下の項目を変更しておきます(差分のある項目のみ表示) [heroku_pj]/settings/base.py # settingsの階層が1つ深くなるのでさらに親ディレクトリを取る BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))) # whitenoiseのミドルウェアをこの位置に追加する MIDDLEWARE = [ 'django.middleware.security.SecurityMiddleware', 'whitenoise.middleware.WhiteNoiseMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', (省略) ] LANGUAGE_CODE = 'ja-jp' TIME_ZONE = 'Asia/Tokyo' # 以下はwhitenoiseとcollectstaticに関連して必要な設定 STATIC_ROOT = os.path.join(BASE_DIR, 'staticfiles') STATICFILES_STORAGE = 'whitenoise.storage.CompressedManifestStaticFilesStorage' 参考: whitenoiseとは 1-5-3. local.py BASE_DIRは base.pyで定義しているので明示する必要はありませんが、気持ち悪い人は書き加えてください。 [heroku_pj]/settings/local.py import os from .base import * DEBUG = True # ここは各自でよしなにしてください ALLOWED_HOSTS = ['127.0.0.1',] SECRET_KEY = '[元のSECRET_KEYをそのままコピペ]' #settings.pyからそのままコピー DATABASES = { 'default': { 'ENGINE': 'django.db.backends.sqlite3', 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'), } } 1-5-4. production.py BASE_DIRについては同上。 また、多くの値を環境変数から取得していますが、その設定については後述します。 [heroku_pj]/settings/production.py import os import django_heroku import dj_database_url from .base import * DEBUG = False # ['*']でもいい ALLOWED_HOSTS = ['127.0.0.1', '.herokuapp.com'] # 秘密鍵はファイルに書かない SECRET_KEY = os.environ['SECRET_KEY'] DATABASES = { 'default': { # localと異なり、mysqlのエンジンを用いる 'ENGINE': 'django.db.backends.mysql', 'OPTIONS': { # これを設定しないとMySQLのWARNINGが出る 'init_command': "SET sql_mode='STRICT_TRANS_TABLES'", }, # 以下はあとで値を設定する 'NAME': os.environ['DB_NAME'], 'USER': os.environ['DB_USERNAME'], 'PASSWORD': os.environ['DB_PASSWORD'], 'HOST': os.environ['DB_HOSTNAME'], 'PORT': os.environ['DB_PORT'], } } # 補足参照 db_from_env = dj_database_url.config(conn_max_age=600, ssl_require=False) DATABASES['default'].update(db_from_env) django_heroku.settings(locals(), databases=False) MySQLを使わず(heroku標準の)PostgreSQLを使用したい場合、DATABASES['default']['ENGINE']の値は以下に変更になります 'django.db.backends.postgresql_psycopg2' dj_database_url.config()において、ssl_require=Falseを指定しないとdjango_heroku.settings()でエラーが出ます これはdjango_herokuがsslをサポートしていないのに、読み込み時に勝手にssl設定を読み込もうとすることが原因のようです 参考: Django Heroku, server does not support SSL, but SSL was required 1-6. manage.py/wsgi.pyの書き換え 開発環境と本番環境で適切なsettingsを読み込むために必要です。 1-6-1. manage.py 書き換え前(抜粋、main関数の最初) manage.py os.environ.setdefault("DJANGO_SETTINGS_MODULE", "[heroku_pj].settings") 書き換え後 manage.py os.environ.setdefault("DJANGO_SETTINGS_MODULE", "[heroku_pj].settings.local") 1-6-2. [heroku_pj]/wsgi.py 書き換え前(抜粋) manage.py os.environ.setdefault("DJANGO_SETTINGS_MODULE", "[heroku_pj].settings") 書き換え後 manage.py os.environ.setdefault("DJANGO_SETTINGS_MODULE", "[heroku_pj].settings.production") ここまで長くなったので続きは別記事へ。

apacheの再起動 $ sudo systemctl restart httpd.service mysql port番号 $ mysql> show variables like 'port'; バージョン確認 yum info httpd mysql --version mysqladmin version mysql> select version(); mysql> status *Linux2にLUMPインストールチュートリアル

環境（利用するソフトウェア） MySQL (RDBMS：リレーショナルデータベース管理システム) Apache (Webサーバー) php (プログラム言語) SQLとは データを作るときの「create table ･･･」やデータを挿入するときの「insert intto ･･･」などデータベースに対して処理する内容を指定するコマンドをクエリという。このクエリを書くときの規則が、SQLという言語です。 作成編 SQLコマンド データベースの作成 'create database データベース名;' > コマンドの最後には、「;」(デミリタ)をつける テーブルの作成 'create database テーブル名 (カラム名 データ型, カラム名　データ型, カラム名 データ型 ･･･);' 例）'create database users (id INT AUTO_INCREMENT PRIMATY KEY, name VARCHAR(25), email VARCHAR(50), password VARCHAR(50));' > idカラムに、データ型(INT), AUTO_INCREMENT(連続番号1,2,3,4,5,･･･を宣言), PRIMATY KEY(主キーを設定) > nameカラムに、データ型(VARCHAR(25))を指定。VARCHARは、文字データの保管。(25)は、文字数制限をしていて２５文字までの文字が入るようになる。passwordカラムも同様。 テーブルのデータの作成 'insert into テーブル名 values(データ, データ, データ ･･･);' 例）'insert into users(name, email, password) values('yyy', 'yyy@example.com', 'whywhywhy');' > usersテーブルのnameに'yyy', emailに'yyy@example.com', password に'whywhywhy'というデータを作成している。 複数データの作成 'insert into users(name,email,password) values('yyy','yyy@example.com','whywhywhy'),('zzz','zzz@example.com','zzzzzzzzzz'),('xxx','xxx@example.com','xxxxxxxxxxx');' > usersテーブルに３つのレコード作成している。作成したいデータを「,」で繋げる。･･･,(),(),･･･ インデックスの作成 インデックスとは、テーブルの索引のこと。膨大なデータから必要なレコードの情報を調べるときに使う。 'create index インデックス名 ON テーブル名(カラム名)' 例）'create index my_ind on users(name)' > usersテーブルのnameカラムに、'my_ind'というインデックスを設定している。 その他 作成したデータベースの確認 'show databases;' 使うデータベースを指定する 'use データベース名;' 現在しようしているデータベースの表示 'select database();' 内部結合（複数のテーブルを結合して表示する） テーブル構成 > usersテーブル(id, name, email, password) > articlesテーブル(id, title, body, created_at, updated_at, user_id) 今回は、articlesテーブルの title と created_at, usersテーブルの name を結合して表示する。 'select カラム名 from テーブル１ join 結合するテーブル２ on テーブル１のカラム=テーブル２のカラム;' 'select articles.title, articles.created_at, users.name from articles join users on articles.user_id=user.id;'

はじめに herokuにデプロイしたアプリのメンテナンスでmysqlにログインしようと思ったが heroku環境久しぶりだったので、 次回以降この記事をもとに作業するための個人的なメモです。 herokuのアプリ環境を確認する $heroku config -a アプリケーション名 これでアプリのDB情報(ホスト名・DBユーザー名・パスワードが取得できる。)や環境変数などが確認できる。 CLEARDB_DATABASE_URL: ほげほげ DB_HOSTNAME: ほげほげ DB_NAME: ほげほげ DB_PASSWORD: ほげほげ DB_USERNAME: ほげほげ これがわかればいつもどおりmyslqにログインできる $mysql -h [DB_HOSTNAME] -u [DB_USERNAME] -p #データベースの一覧確認 $show databases; #使用するデータベース選択 $use データベース名 #テーブルの構造確認 $desc テーブル名; #create ddl取得 $show create table テーブル名;

環境 Ruby on rails 6.1.4 ruby 2.6.5 capistrano mariaDB 5.5.68 経緯 AWSのEC2を使った環境構築をしています。 capistranoを使い自動デプロイを完了しています。 ローカル環境ではSequel Proを使っていたので、ローカルで保存した情報はすぐ確認することができました。 しかし、本番環境のデータベースの中身を確認する方法がわからず。 素人丸出しの「mariaDB　中身　確認」だとかで検索しましたが出てくるのは「select * from モデル名で中身見れますよ〜」ばかりでした。（初心者すぎて調べ方が分かっていない） そもそもDBにたどり着く方法がわからず悩んでいたのですが、なんとか辿り着けたので備忘録を兼ねて記事に残そうと思います。 コード ターミナル内 まずEC2へログインします #ホームディレクトリに移動 xxxx@yyyynoAir ~ % cd #sshディレクトリに移動 xxxx@yyyynoAir ~ % cd .ssh/ #EC2へログイン（ssh -i ダウンロードした鍵の名前.pem ec2-user@作成したEC2インスタンスに紐付けたElastic IP） xxxx@yyyynoAir .ssh %　ssh -i ００００xxxx.pem ec2-user@12.345.67.890 #ログインできればEC2のロゴ？が出現します __| __|_ ) _| ( / Amazon Linux 2 AMI ___|\___|___| ログイン後 #currentディレクトリに移動 [ec2-user@ip-123-45-67-8 ~]$cd /var/www/アプリケーション名/current #mysqlへアクセスするコマンドを入力 [ec2-user@ip-123-45-67-8 current]mysql -u root -p アプリケーション名_production #パスワードを求められるので入力（見た目上は何も表示されませんが入力されているので完了後enterを押す） Enter password: #DBの中に入っているので、select文を使って検索する MariaDB [アプリケーション名_production]> select * from stores \G; 流れとしては上記の通りです。 mysqlのパスワードがわからない方はEC2内のホームディレクトリ[ec2-user@ip-123-45-67-8 ~]へ戻ってもらい「env」と入力すればパスワードが確認できるはずです。 また、select文の\Gはターミナル上で表示されるデータが見やすくなる（抽象的ですいません）ので使っています。 以上です。 ローカルでのデータはすぐ目視で確認できていたので、いざ本番環境のデータを見るとなったときに「どこ見に行けばいいの？」と思ったので作成しました。 内容に不備がございましたらご指摘いただけますと幸いです。

1. 背景 前回の記事続きになります。Azureの基本的な構成を作ってみました。 Azureで基本的な構成を構築してみる①（前提、ネットワーク・VM作成） Azureで基本的な構成を構築してみる②（ALB、DNS作成） Azureで基本的な構成を構築してみる③（MySQL作成, VMからのSSL接続）　←今ここ 2. アーキテクチャ図 今回はDBとしてMySQLを作成し、可能な限りVMからセキュアな接続設定をしていきます。 4. 構築手順 4.9 DatabaseforMySQL作成 AWSでいうRDSforMySQLとなる、DatabaseforMySQLを作成します。 Azureポータルで「Azure Database for MySQL」に移動 → 「+作成」 ・デプロイオプションとして、今回は 「単一サーバ」を使いました。 【基本】 ・リソースグループ：VM,AGW等と同じグループ選択 ・サーバ名：任意 ・データソース：なし ・場所：東日本 ・バージョン：8 ・コンピューティンングストレージ：デフォルトで汎用プラン選択されてますが、今回高いメモリスペックが必要ないのと、コストを抑えたかったのでBasicプランを選びました。（汎用プランだと最低でも¥2万以上/月します、、） 　「サーバの構成」→Basicプラン選択し、必要スペックに応じてvCore・ストレージ・ストレージの自動拡張等選択します。 DBのユーザ、パスワードも適当に設定します。 →作成 最低スペックでも¥4,000/月程度かかります。。 4.10 DBへの接続設定 前提 お気づきかと思いますが、上記DB作成時にDBの所属サブネットグループ等指定するところがありませんでした。 AzureではAWSのようにプライベートサブネットにRDSを所属させることができず、デフォルトではMySQLへの接続にはインターネットを経由しなくてはいけません。 そしてさらに、BasicプランのDBforMySQLだと他Azureサービスからプライベートな接続ができないため、VMのパブリックIPによりインターネット経由でMySQLにアクセスすることしかできません…。 （汎用 or メモリ最適化プランだと「プライベートエンドポイント」というものを作成することでプライベートIPによるセキュアな接続が可能です。） そこで今回のBasicプランMySQLでは、 1. MySQL側のFirewall機能でVMのパブリックIPのみ開放 2. SSL接続を強制化して通信 により、インターネット経由であっても可能な限りセキュアにVMから接続させることにしました。 手順 上記で作成したDBforMySQLに移動 →左側のタブで「接続のセキュリティ」に移動 ・「Azure サービスへのアクセスを許可」を「いいえ」 ・「ファイアウォール規則名」で開始IP、終了IPにVMの静的パブリックIPを設定 ・「SSL接続を強制」を「有効」 →保存 これでVMのパブリックIPからのみ、SSLでMySQLに接続が可能になります。 4.11 VMからのSSL接続設定 上記の設定で、VMのIPからの接続は開放されましたが、VMでSSL接続の準備をする必要があります。 具体的には、VMにSSL証明書をインポートします。 SSL証明書インポート ・https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem からSSL証明書をローカルのMac等にダウンロード ・Pem形式に変換 $ openssl x509 -inform DER -in BaltimoreCyberTrustRoot.crt -out MyServerCACert.pem $ less MyServerCACert.pem 　　　　　→pemの中身見てコピーしておく ・VMにSSH接続 $ ssh -i <鍵のパス> azureuser@<VMのパブリックIP> ・任意のディレクトリに移動し、証明書を複製 $ sudo touch MyServerCACert.pem $ sudo vi MyServerCACert.pem →lessで見た中身をペースト これでSSL証明書をVMにインポートできました。 接続確認 mysqlクライアントでSSL接続できるか確認してみます。 まず、mysqlコマンドをインストールする必要がありました。 mysql8のリポジトリ追加 $ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm リポジトリ追加できたか確認 $ ls -l /etc/yum.repos.d -rw-r--r-- 1 root root 1050 Oct 4 2019 mysql-community-source.repo -rw-r--r-- 1 root root 995 Oct 4 2019 mysql-community.repo デフォルトのmysqlモジュール無効化 $ sudo yum module disable mysql mysqlクライアントインストール $ sudo yum install mysql-community-client これでmysqlコマンド使えるようになりました。ssl強制で接続してみます。 $ mysql -h <DBforMySQLのサーバ名> -u <MySQLユーザ名>@<サーバ名> -p --ssl-mode=REQUIRED –ssl-ca=<pemのパス> （例：$ mysql -h mydbformysql.mysql.database.azure.com -u myuser@mydbformysql -p --ssl-mode=REQUIRED –ssl-ca=MyServerCACert.pem） 　→パスワード入力 mysqlのプロンプトに変わると接続成功です。 以下のように、ssl無効で接続しようとすると失敗することも確認します。 $ mysql -h mydbformysql.mysql.database.azure.com -u myuser@mydbformysql -p –ssl-mode=DISABLED ERROR 9002 (28000): SSL connection is required. Please specify SSL options and retry. 補足ですが、MySQL8.0では--ssl-mode指定しなくても、デフォルトでSSL接続を試みるようです。（参考） 実際に以下コマンドでも接続できました。 $ mysql -h mydbformysql.mysql.database.azure.com -u myuser@mydbformysql -p でも、このコマンドは証明書（MyServerCACert.pem）指定しなくても接続できています。。理由は調べたけどよく分からない…。ご存知の方コメントいただけると幸いです。 アプリケーションでの接続 このMyServerCACert.pemを使って、使用するアプリケーションに応じて、DBへのSSL設定をしましょう。 まとめ 以上、3記事に渡って簡単な構成を作ってみました。 感想としては、同じサービスでもグレードによって使えないオプションや他サービスと連携できないことがあり予算がそれなりにないと不便なことが多かったです。しかもグレードを上げると急激に値段が高い。。（ApplicationGatewayのStandard v2、DBforMySQLの汎用プランとか） 小規模構成や個人運用にAzureは向いてないのかな、と思いました。 今後のTodo サイトのSSL化についてコストを抑えて構築できればと思ってます。 ApplicationGatewayのStandard v2プランだとKeyVaultと連携してSSL証明書の自動更新できそうですが、v1プランだとKeyVaultとの連携がサポートされていないみたい。ここクリアできるんだろうか。。