Flask-SQLAlchemyでMySQLを使っていてハマった事象について。 発生事象 Flaskアプリケーションを起動して長時間（８時間くらい？）経過後にブラウザでアクセスするとInternal Server Error。エラーログは下記の内容。 sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') Internal Server Error発生後、ブラウザで再読み込みするとエラーは解消される。 が、さらに長時間経過すると再び同様のエラーが発生。 環境 Python 3.9.5 MySQL 5.7.34 Flask 2.0.0 Flask-SQLAlchemy 2.4.4 PyMySQL 1.0.2 原因と対策 どうやら長時間経過するとコネクションプールが死んでしまう模様。 これを解消するにはDBアクセスの前にpingを飛ばしてやる、すなわち SELECT 1 のような特に何もしないSQLを投げてコネクションプールを復活させてやればＯＫ。 この方法はSQLAlchemyの機能として提供されており、デフォルトでは使用しない設定となっている。 create_engine の引数に pool_pre_ping=True を渡せとのことだったが、A Minimal Applicationをどう改修したらいいものやら、いろいろ試した結果以下の方法に落ち着いた。 flask_app.py（抜粋） app = Flask(__name__) app.config['SQLALCHEMY_ENGINE_OPTIONS'] = { 'pool_pre_ping': True } とりあえず長時間経過してもエラーは出なくなった。 わからないこと MySQL以外のDBMSでは発生しないのか？

はじめに SQLをチューニングする業務を初めて任されたので、その際に学んだことを復習がてらメモ。初心者なので間違っているところなどありましたらご指摘いただけるととてもありがたいです！ 前提 newRelic を使って種々のリソースの応答時間を監視している。そこでレスポンスに時間がかかっていたコントローラから、重いクエリを特定＆改善する、というタスクだった。 原因となるSQLの特定 まずここが思っていたより手間取った。AWSのログと照らし合わせながら、コンソールでクエリを叩いて同じ出力になっている場所を確かめる、という方法で探した。 ActiveRecord のexplainを使いながら、ログを確認 terminal AreasArticle.where(parent_id: 401450).explain AreasArticle Load (164.4ms) SELECT `areas_articles`.* FROM `areas_articles` WHERE `areas_articles`.`parent_id` = 401450 => EXPLAIN for: SELECT `areas_articles`.* FROM `areas_articles` WHERE `areas_articles`.`parent_id` = 401450 +----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | areas_articles | NULL | ALL | NULL | NULL | NULL | NULL | 388203 | 10.0 | Using where | +----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set (0.00 sec) こんな感じで出力される。 explain の読み方についてはこちらの記事が非常にわかりやすかったのでおすすめです 他に、rails のログも見ると良い。explain は複数テーブルの読み込みを一気に行ったとき（joinなど）に、読み込みにかかった時間を表示してくれなかった。（僕が気づいてないだけかも？） 根本的にSQLの知識が足りてなく、クエリそのものが何をしているかいまいち掴めてなかったのも特定しづらかった原因。 explain による分析 詳しいことは上記記事を見るのが一番わかりやすいが、要点だけまとめておく。 主に見る場所 select_type → type, table → key → extra みたいな順で見ていくのが良さそう。 select_type はサブクエリの有無などクエリのタイプに関するもの。まず初めにここを見る。SIMPLE以外になってる場合はなにかしら改善の余地がある可能性高い。。。？ tabel を見ると、どのテーブルを読み込んだかがわかる。無駄に読み込んでいるテーブルや、joinの必要がないテーブルなどがないか確認する。 ただし、joinが無駄かどうかなどは周辺のコードを読まないとわからないのでなかなか難しい。クエリそのものを変えるのはそこそこのコスパがかかる。 次に type を見る。ここはデータをとってくる方式を記している。ALL、INDEX が入ってたら要注意。index を貼ることで改善できる可能性がある。 インデックスに関しては貼るだけで速度改善になりうるのでコスパはいい。ただし、無闇矢鱈とインデックスを貼ると、データをcreateするときに重くなる＆オプティマイザが判断を誤って逆にクエリが遅くなる、といった弊害がありうるので、十分に吟味する、 一般に、中間テーブルなどよく検索に使われるものには貼っといて損がないことが多そう。 スロークエリの原因となるもの 種々の要因が絡まっていることもあり、特定が難しいが、調べた限りで出てきたものを簡単にまとめる 無駄なクエリの発行 これはシンプル。Rails のようにSQLをあまり意識しなくてよいフレームワークを使っている場合、無駄なクエリが生じている場合がある。今回生じていたものとして、includes の使用が挙げられる。 includes は、Railsがeager_loadとpre_load をよしなに振り分けてくれるメソッドだが、クエリによってはボトルネックになる。 以下記事がわかりやすかった。 他にも、必要のないjoinをしていないか？など観点は色々あるが、クエリそのものを書き換える変更は、当然ながら取得されるデータに違いがないかの確認が必要になるので、実装コストがかかる。一番に見直すのはincludes で良さそうか？ インデックスが貼られていない そのまま。調べればたくさん出てくるので割愛。 オプティマイザの誤判断 発行するクエリを判断しているのはDBのオプティマイザで、これはDBに蓄えられた統計情報から最適なクエリを判断している。ゆえに、統計情報の更新タイミング付近などでは変なクエリが発行されてしまうことがあるらしい。 DBの設定にもよるが、軽く時間を置いてからもう一度クエリを発行すると症状がよくなっていることなどもある。 本番環境とテスト環境でのDBのデータ数の違い 上に少し被るが、オプティマイザの判断はDBに入っているデータの数にも左右される。従って、できる限り本番に近いDBの状態でクエリの分析を行うのが望ましい。テスト環境に本番と同じデータが入っているか？を気にしておく。入っていないならば本番環境のクエリで実行する必要がある。 おわりに また新しい知識が増えたら追記するかもしれません。とりあえずチューニングは奥が深いんだなぁとわかりました。まずSQLの勉強しよう...

環境 MacOS Big Sur 11.5.2 ruby 3.0.2 rails 6.1.4 Docker 20.10.8 MySQL 8.0.23 ディレクトリ　myapp 全て現時点(2021/09/06)での最新バージョンです。 1.用意するファイル ・Dockerfile ・Gemfile ・Gemfile.lock ・entrypoint.sh ・docker-compose.yml Dockerfile FROM ruby:3.0.2 RUN apt-get update -qq && apt-get install -y nodejs # yarnパッケージ管理ツールをインストール # https://classic.yarnpkg.com/en/docs/install/#debian-stable RUN curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add - RUN echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list RUN apt-get update && apt-get install yarn WORKDIR /myapp COPY Gemfile /myapp/Gemfile COPY Gemfile.lock /myapp/Gemfile.lock RUN bundle install COPY . /myapp # Add a script to be executed every time the container starts COPY entrypoint.sh /usr/bin/ RUN chmod +x /usr/bin/entrypoint.sh ENTRYPOINT ["entrypoint.sh"] EXPOSE 3000 # Start the main process. CMD ["rails", "server", "-b", "0.0.0.0"] ここでyarnをインストールしておくのがポイントです。しないと後でwebpackerがインストールできずエラーになります。 Gemfile source 'https://rubygems.org' gem 'rails', '~>6' Gemfile.lockは空のままで大丈夫です entrypoint.sh #!/bin/bash set -e # Remove a potentially pre-existing server.pid for Rails. rm -f /myapp/tmp/pids/server.pid # Then exec the container's main process (what's set as CMD in the Dockerfile). exec "$@" docker-compose.yml version: "3" services: db: image: mysql:8.0 command: mysqld --default-authentication-plugin=mysql_native_password environment: MYSQL_USER: user MYSQL_PASSWORD: password MYSQL_ROOT_PASSWORD: password ports: - 3306:3306 volumes: - ./tmp/db:/var/lib/mysql web: build: . command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'" environment: MYSQL_HOST: db volumes: - .:/myapp ports: - "3000:3000" depends_on: - db 2.実行 $ docker-compose run web rails new . --force --no-deps --database=mysql ↓ $ docker-compose build ↓ ここでdatabase.ymlを編集します config/database.yml default: &default adapter: mysql2 encoding: utf8mb4 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> username: root password: <%= ENV.fetch("MYSQL_ROOT_PASSWORD", "root") %> host: db development: <<: *default database: myapp_development test: <<: *default database: myapp_test production: <<: *default database: myapp_production username: myapp password: <%= ENV['MYAPP_DATABASE_PASSWORD'] %> パスワードなど設定した後データベースと接続します。 $ docker-compose run web rake db:create ↓ $ docker-compose up 終わったらlocalhost:3000とネットで検索すればこの画面が出てきます。 そしたら環境構築成功です！ おわりに 未経験でしかも初心者なのでかなり苦労しましたが必要な情報は以上だけでした 一人でも多くの方の役に立てば嬉しいです 【Mac】Docker+rails6+postgreSQLでの環境構築完全版 PostgreSQLバージョンも作ったのでよかったら参考にしてください！

MySQLにAP専用ユーザーとデータベース、テーブルを作成する サーバー側のMySQLにAP用のDBとパスワードを設定する(以後DB1) AP側のDB設定をDB1に変更する。 sudo mysql MySQL 5.7（またそれ以降のバージョン）を実行しているUbuntuシステムでは、rootMySQLユーザーはデフォルトによりパスワードではなくauth_socketプラグインを使用して認証するように設定されています。【中略】 root MySQLユーザーにアクセスできるようにするには sudo権限でmysqlを呼び出す必要があります。 (参考)https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04-ja MySQLのAP専用ユーザーにグローバル権限を付与する CREATE USER 'AP専用ユーザー'@'%' IDENTIFIED WITH mysql_native_password BY '任意のパスワード'; GRANT ALL ON 特定のDB名.* TO 'AP専用ユーザー'@'%'; GRANT ALL ON 特定のDB名.* TO 'AP専用ユーザー'@'%'; 部分 説明 備考 GRANT 権限を与える ALL グローバル権限 ON 特定のDB名.* 特定のDBの全ての権限 ON db_name.* 構文 'AP専用ユーザー'@'%'; AP専用ユーザーに対して 【ポイント】 ・「特定のDB名」はこれから作るDB名を指定してOK。 ・ 先にGRANTしないと権限がないので、DB等の作成ができない。

MySQLにAP専用ユーザーとデータベース、テーブルを作成する 【本忘備録目的】 SpringBootで作成したjavaWebAPをサーバー側にデプロイしたのち、そのjavaWebAPと連携するDBを構築する。 環境 OS Linux ubuntu20.04 Web_S apache App_S tomcat DB_S MySQL ポイント サーバー側のMySQLにAP用のユーザーやDB,パスワード等を設定する。 AP側のDBに関するプロパティファイルの内容(DBユーザー名とパスワード)をサーバー側の内容と合ううように変更する。 sudo mysql MySQL 5.7（またそれ以降のバージョン）を実行しているUbuntuシステムでは、rootMySQLユーザーはデフォルトによりパスワードではなくauth_socketプラグインを使用して認証するように設定されています。【中略】 root MySQLユーザーにアクセスできるようにするには sudo権限でmysqlを呼び出す必要があります。 (参考)https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04-ja MySQLにAP専用ユーザー作成 CREATE USER 'AP専用ユーザー'@'%' IDENTIFIED WITH mysql_native_password BY '任意のパスワード'; @'%'はホスト名を表す。 以下詳細 https://qiita.com/YuichiTanaka007/items/f31f94ace4bac0aaf990#%E3%83%9B%E3%82%B9%E3%83%88%E5%90%8D MySQLにAP専用ユーザーにグローバル権限を付与する 専用ユーザーでDBやテーブルの作成等ができるようにグローバル権限を付与する GRANT ALL ON 特定のDB名.* TO 'AP専用ユーザー'@'%'; GRANT ALL ON 特定のDB名.* TO 'AP専用ユーザー'@'%'; 部分 説明 備考 GRANT 権限を与える ALL グローバル権限 ON 特定のDB名.* 特定のDBの全ての権限 ON db_name.* 構文 'AP専用ユーザー'@'%'; AP専用ユーザーに対して 【ポイント】 ・「特定のDB名」はこれから作るDB名を指定してOK。 ・ 先にGRANTしないと権限がないので、DB等の作成ができない。

環境 ・macOS Big Sur バーション11.5.2 ・シェル zsh ・Ruby 3.0.2 ・rails 6.1.4 ・My SQL 8.0.23 ・Docker 20.10.8 Docker file Dockerfile FROM ruby:3.0.2 RUN apt-get update -qq && apt-get install -y nodejs # yarnパッケージ管理ツールをインストール # https://classic.yarnpkg.com/en/docs/install/#debian-stable RUN curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add - RUN echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list RUN apt-get update && apt-get install yarn WORKDIR /myapp COPY Gemfile /myapp/Gemfile COPY Gemfile.lock /myapp/Gemfile.lock RUN bundle install COPY . /myapp # Add a script to be executed every time the container starts COPY entrypoint.sh /usr/bin/ RUN chmod +x /usr/bin/entrypoint.sh ENTRYPOINT ["entrypoint.sh"] EXPOSE 3000 # Start the main process. CMD ["rails", "server", "-b", "0.0.0.0"] docker-compose.yml docker-compose.yml version: "3" services: db: image: mysql:8.0.26 command: mysqld --default-authentication-plugin=mysql_native_password environment: MYSQL_USER: user MYSQL_PASSWORD: password MYSQL_ROOT_PASSWORD: password ports: - 3306:3306 volumes: - ./tmp/db:/var/lib/mysql web: build: . command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'" environment: MYSQL_HOST: db volumes: - .:/myapp ports: - "3000:3000" depends_on: - db エラー内容 $ docker compose up ↓ Starting mysql_db_1 ... error ERROR: for mysql_db_1 Cannot start service db: Ports are not available: listen tcp 0.0.0.0:3306: bind: address already in use ERROR: for db Cannot start service db: Ports are not available: listen tcp 0.0.0.0:3306: bind: address already in use ERROR: Encountered errors while bringing up the project. 【Docker】Rails+MySQLの環境をDockerで構築中「ポートの衝突」に遭遇してから解決するまで こちらの記事を参考に２つの解決策を試してみるも解決できず、、、 エラーにはaddress already in useとあるので他にaddressが使われていないか調べていきます。 $ lsof -i:3306 ↓ COMMAND PID USER ... mysqld 117 SAKAE ... 出てきました！これを消せばいいわけです！ 消すコマンドはこちら $ kill 117 それでもうまくいかない場合 探すとき $ sudo lsof -i:3306 or $ sudo lsof -i -P | grep "LISTEN" 消したい時 $ sudo kill <killしたいportのPID> それでもダメなら docker-compose.yaml ports: - "3306:3306" ↓ ports: - "3307:3306" このようにポート番号を変えることで解決できる場合もあります。

モチベーション 「スッキリわかるSQL入門」という書籍を進めていて、参照整合性の崩壊を引き起こすデータ操作が4パターンあるとのことを知り、外部キー周りの理解がなんとなくだったので実際に手を動かして検証してみようと思いました。 目的 この記事では、参照整合性の崩壊を引き起こすデータ操作を行った際に実際にどのような挙動になり、どのようにすれば防げるのかをRails+MySQLで検証します。 参照整合性とは 外部キーが指し示す先にきちんと行が存在してリレーションシップが成立していること。 参照整合性の崩壊を引き起こすデータ操作 「スッキリわかるSQL入門」という書籍で参照整合性の崩壊を引き起こすデータ操作として次の4つのパターンがあるとのことでした。 この4つのパターンを実際に試していこうと思います。 「ほかの行から参照されている」行を削除してしまう 「ほかの行から参照されている」行の主キーを変更してしまう 「存在しない行を参照する」行を追加してしまう 「存在しない行を参照する」行に更新してしまう 今回登場するテーブル 書籍を参考に「家計簿テーブル」と「費目テーブル」が登場します。 ER図 参照整合性が崩壊している例 例えば、以下の家計簿テーブルのid=2のレコードは、費目テーブルのid=2を参照しますが費目テーブルにはid=2のレコードが存在していないため参照整合性が取れていない状態になっています。 さっそく、検証してみる 何も意識せずにテーブルを作成する 今回は参照整合性の検証のため、インデックスやNOT NULL制約など何も考えずにテーブルを作成していきます。 # 家計簿テーブル作成 rails g model household_account_book date:date expense_item_id:integer memo:string income:integer expenditure:integer # 費目テーブル作成 rails g model expense_item name:string # マイグレーション実行 rails db:migrate 作成されたスキーマ(DB情報が反映されたもの) # 家計簿テーブル create_table "expense_items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t| t.string "name" t.datetime "created_at", null: false t.datetime "updated_at", null: false end # 費目テーブル create_table "household_account_books", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t| t.date "date" t.integer "expense_item_id" t.string "memo" t.integer "income" t.integer "expenditure" t.datetime "created_at", null: false t.datetime "updated_at", null: false end データを用意する 費目テーブルのデータを作成します。 ExpenseItem.create!(name: "食費") ExpenseItem.create!(name: "外食費") ExpenseItem.create!(name: "交際費") ExpenseItem.create!(name: "書籍代") ExpenseItem.create!(name: "水道代") ExpenseItem.create!(name: "電気代") ExpenseItem.create!(name: "ガス代") ExpenseItem.create!(name: "携帯代") ExpenseItem.create!(name: "インターネット料金") ExpenseItem.create!(name: "定期代") ExpenseItem.create!(name: "病院代") ExpenseItem.create!(name: "被服費") ExpenseItem.create!(name: "日用品費") ExpenseItem.create!(name: "住宅費") ExpenseItem.create!(name: "給与") 家計簿テーブルのデータを作成します。 内容は適当に入力していきます。 HouseholdAccountBook.create!(date: Date.today, expense_item_id: 1, memo: "食費", income: nil, expenditure: 1000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 2, memo: "外食費", income: nil, expenditure: 2000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 3, memo: "交際費", income: nil, expenditure: 3000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 4, memo: "書籍代", income: nil, expenditure: 4000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 5, memo: "水道代", income: nil, expenditure: 5000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 6, memo: "電気代", income: nil, expenditure: 6000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 7, memo: "ガス代", income: nil, expenditure: 7000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 8, memo: "携帯代", income: nil, expenditure: 8000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 9, memo: "インターネット料金", income: nil, expenditure: 9000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 10, memo: "定期代", income: nil, expenditure: 10000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 11, memo: "病院代", income: nil, expenditure: 11000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 12, memo: "被服費", income: nil, expenditure: 12000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 13, memo: "日用品費", income: nil, expenditure: 13000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 14, memo: "住宅費", income: nil, expenditure: 14000) HouseholdAccountBook.create!(date: Date.today, expense_item_id: 15, memo: "給与", income: 1000000, expenditure: nil) 家計簿テーブルの状態。 費目テーブルの状態。 検証 1. 「ほかの行から参照されている」行を削除する 家計簿テーブルから参照されている費目テーブルの行を削除してみます。 → 削除できました irb(main):039:0> ExpenseItem.find_by(name: "食費").destroy ExpenseItem Load (0.4ms) SELECT `expense_items`.* FROM `expense_items` WHERE `expense_items`.`name` = '食費' LIMIT 1 (0.2ms) BEGIN ExpenseItem Destroy (0.3ms) DELETE FROM `expense_items` WHERE `expense_items`.`id` = 1 (2.6ms) COMMIT => #<ExpenseItem id: 1, name: "食費", created_at: "2021-09-05 13:30:52", updated_at: "2021-09-05 13:30:52"> 2. 「ほかの行から参照されている」行の主キーを変更する 家計簿テーブルから参照されている費目テーブルの行の主キーを変更してみます。 → 変更できました。 irb(main):040:0> expense_item = ExpenseItem.find_by(name: "外食費") ExpenseItem Load (0.4ms) SELECT `expense_items`.* FROM `expense_items` WHERE `expense_items`.`name` = '外食費' LIMIT 1 => #<ExpenseItem id: 2, name: "外食費", created_at: "2021-09-05 13:30:52", updated_at: "2021-09-05 13:30:52"> irb(main):041:0> expense_item.id = 99 => 99 irb(main):042:0> expense_item.save! (0.3ms) BEGIN ExpenseItem Update (0.3ms) UPDATE `expense_items` SET `id` = 99, `updated_at` = '2021-09-05 13:55:48' WHERE `expense_items`.`id` = 2 (4.6ms) COMMIT => true 3. 「存在しない行を参照する」行を追加する 家計簿テーブルに費目テーブルに存在しない行を参照する行を追加してみます。 → 追加できました。 irb(main):043:0> HouseholdAccountBook.create!(date: Date.today, expense_item_id: 999, memo: "存在しない費目", income: nil, expenditure: 999) (0.2ms) BEGIN HouseholdAccountBook Create (0.3ms) INSERT INTO `household_account_books` (`date`, `expense_item_id`, `memo`, `expenditure`, `created_at`, `updated_at`) VALUES ('2021-09-05', 999, '存在しない費目', 999, '2021-09-05 14:07:16', '2021-09-05 14:07:16') (2.9ms) COMMIT => #<HouseholdAccountBook id: 16, date: "2021-09-05", expense_item_id: 999, memo: "存在しない費目", income: nil, expenditure: 999, created_at: "2021-09-05 14:07:16", updated_at: "2021-09-05 14:07:16"> 4. 「存在しない行を参照する」行に更新する 家計簿テーブルで費目テーブルに存在する行を参照している行を存在しない行を参照するように更新してみます。 → 更新できました。 irb(main):045:0> household_account_book = HouseholdAccountBook.find_by(memo: "外食費") HouseholdAccountBook Load (0.3ms) SELECT `household_account_books`.* FROM `household_account_books` WHERE `household_account_books`.`memo` = '外食費' LIMIT 1 => #<HouseholdAccountBook id: 2, date: "2021-09-05", expense_item_id: 2, memo: "外食費", income: nil, expenditure: 2000, created_at: "2021-09-05 13:40:41", updated_at: "2021-09-05 13:40:41"> irb(main):046:0> household_account_book.expense_item_id = 998 => 998 irb(main):047:0> household_account_book.save! (0.3ms) BEGIN HouseholdAccountBook Update (0.3ms) UPDATE `household_account_books` SET `expense_item_id` = 998, `updated_at` = '2021-09-05 14:10:33' WHERE `household_account_books`.`id` = 2 (2.6ms) COMMIT => true 検証結果 当たり前ですが、何も制約等を設定していないため、はこのように参照整合性の崩壊を引き起こすことができる状態になってしまっていることがわかりました。 外部キー制約を指定を行ってテーブルを作成する 参照整合性の崩壊は、絶対に避けなければなりません。 そういったミスを防ぐために、外部キー制約(FOREIGN KEY制約)があります。 外部キー制約を指定することで、先ほど検証した参照整合性の崩壊を引き起こすデータ操作を行った際に強制的に処理を中断するようになります。 実際に検証してみます。 外部キー制約を指定する # 家計簿テーブルの費目idカラムに外部キー制約を指定 rails g migration AddExpenseItemIdToHouseholdAccountBook expense_item:references # 生成されたマイグレーションファイル class AddExpenseItemIdToHouseholdAccountBook < ActiveRecord::Migration[5.2] def change add_reference :household_account_books, :expense_item, foreign_key: true end end # データをリセットして、マイグレーションをやり直す rails db:migrate:reset これだとエラーになったため、最終的に下記のようにマイグレーションファイルを変更して再実行するとうまくいきました。 (rails db:rollbackもうまくいきました。) class AddExpenseItemIdToHouseholdAccountBook < ActiveRecord::Migration[5.2] def up change_column :household_account_books, :expense_item_id, :bigint add_foreign_key :household_account_books, :expense_items, column: :expense_item_id end def down remove_foreign_key :household_account_books, column: :expense_item_id change_column :household_account_books, :expense_item_id, :integer end end スキーマファイルでも外部キー制約が指定されています。 ActiveRecord::Schema.define(version: 2021_09_05_143824) do create_table "expense_items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t| t.string "name" t.datetime "created_at", null: false t.datetime "updated_at", null: false end create_table "household_account_books", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t| t.date "date" t.bigint "expense_item_id" t.string "memo" t.integer "income" t.integer "expenditure" t.datetime "created_at", null: false t.datetime "updated_at", null: false t.index ["expense_item_id"], name: "fk_rails_619f5a0007" end add_foreign_key "household_account_books", "expense_items" end データを用意する 「何も意識せずにテーブルを作成する」と同じようにデータを用意。 検証 1. 「ほかの行から参照されている」行を削除する 家計簿テーブルから参照されている費目テーブルの行を削除してみます。 → 削除できなくなりました！ irb(main):031:0> ExpenseItem.find_by(name: "食費").destroy ExpenseItem Load (0.3ms) SELECT `expense_items`.* FROM `expense_items` WHERE `expense_items`.`name` = '食費' LIMIT 1 (0.2ms) BEGIN ExpenseItem Destroy (0.5ms) DELETE FROM `expense_items` WHERE `expense_items`.`id` = 1 (2.4ms) ROLLBACK Traceback (most recent call last): 1: from (irb):31 ActiveRecord::StatementInvalid (Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails (`app_development`.`household_account_books`, CONSTRAINT `fk_rails_619f5a0007` FOREIGN KEY (`expense_item_id`) REFERENCES `expense_items` (`id`)): DELETE FROM `expense_items` WHERE `expense_items`.`id` = 1) 2. 「ほかの行から参照されている」行の主キーを変更する 家計簿テーブルから参照されている費目テーブルの行の主キーを変更してみます。 → 変更できなくなりました！ irb(main):032:0> expense_item = ExpenseItem.find_by(name: "外食費") ExpenseItem Load (0.3ms) SELECT `expense_items`.* FROM `expense_items` WHERE `expense_items`.`name` = '外食費' LIMIT 1 => #<ExpenseItem id: 2, name: "外食費", created_at: "2021-09-05 15:16:46", updated_at: "2021-09-05 15:16:46"> irb(main):033:0> expense_item.id = 99 => 99 irb(main):034:0> expense_item.save! (0.3ms) BEGIN ExpenseItem Update (0.7ms) UPDATE `expense_items` SET `id` = 99, `updated_at` = '2021-09-05 15:20:28' WHERE `expense_items`.`id` = 2 (3.4ms) ROLLBACK Traceback (most recent call last): 1: from (irb):34 ActiveRecord::StatementInvalid (Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails (`app_development`.`household_account_books`, CONSTRAINT `fk_rails_619f5a0007` FOREIGN KEY (`expense_item_id`) REFERENCES `expense_items` (`id`)): UPDATE `expense_items` SET `id` = 99, `updated_at` = '2021-09-05 15:20:28' WHERE `expense_items`.`id` = 2) 3. 「存在しない行を参照する」行を追加する 家計簿テーブルに費目テーブルに存在しない行を参照する行を追加してみます。 → 追加できなくなりました！ irb(main):035:0> HouseholdAccountBook.create!(date: Date.today, expense_item_id: 999, memo: "存在しない費目", income: nil, expenditure: 999) (0.2ms) BEGIN HouseholdAccountBook Create (0.6ms) INSERT INTO `household_account_books` (`date`, `expense_item_id`, `memo`, `expenditure`, `created_at`, `updated_at`) VALUES ('2021-09-05', 999, '存在しない費目', 999, '2021-09-05 15:21:26', '2021-09-05 15:21:26') (3.1ms) ROLLBACK Traceback (most recent call last): 2: from (irb):35 1: from (irb):35:in `rescue in irb_binding' ActiveRecord::InvalidForeignKey (Mysql2::Error: Cannot add or update a child row: a foreign key constraint fails (`app_development`.`household_account_books`, CONSTRAINT `fk_rails_619f5a0007` FOREIGN KEY (`expense_item_id`) REFERENCES `expense_items` (`id`)): INSERT INTO `household_account_books` (`date`, `expense_item_id`, `memo`, `expenditure`, `created_at`, `updated_at`) VALUES ('2021-09-05', 999, '存在しない費目', 999, '2021-09-05 15:21:26', '2021-09-05 15:21:26')) 4. 「存在しない行を参照する」行に更新する 家計簿テーブルで費目テーブルに存在する行を参照している行を存在しない行を参照するように更新してみます。 → 更新できなくなりました！ irb(main):036:0> household_account_book = HouseholdAccountBook.find_by(memo: "外食費") HouseholdAccountBook Load (0.3ms) SELECT `household_account_books`.* FROM `household_account_books` WHERE `household_account_books`.`memo` = '外食費' LIMIT 1 => #<HouseholdAccountBook id: 2, date: "2021-09-05", expense_item_id: 2, memo: "外食費", income: nil, expenditure: 2000, created_at: "2021-09-05 15:16:54", updated_at: "2021-09-05 15:16:54"> irb(main):037:0> household_account_book.expense_item_id = 998 => 998 irb(main):038:0> household_account_book.save! (0.3ms) BEGIN HouseholdAccountBook Update (0.6ms) UPDATE `household_account_books` SET `expense_item_id` = 998, `updated_at` = '2021-09-05 15:22:19' WHERE `household_account_books`.`id` = 2 (3.4ms) ROLLBACK Traceback (most recent call last): 1: from (irb):38 ActiveRecord::InvalidForeignKey (Mysql2::Error: Cannot add or update a child row: a foreign key constraint fails (`app_development`.`household_account_books`, CONSTRAINT `fk_rails_619f5a0007` FOREIGN KEY (`expense_item_id`) REFERENCES `expense_items` (`id`)): UPDATE `household_account_books` SET `expense_item_id` = 998, `updated_at` = '2021-09-05 15:22:19' WHERE `household_account_books`.`id` = 2) 検証結果 外部キー制約を追加したことにより、ROLLBACKを行い例外が発生するようになりました。 まとめ 外部キーを持つテーブルのカラムには、外部キー制約を指定して参照整合性の崩壊を引き起こすデータ操作ができないようにしよう！