INNER JOIN で中間テーブルを経由してデータ取得するとき、DISTINCT をつけても重複が削除できなくてハマった話です

データ

Users ユーザー情報

id	name
1	Bob
2	Mary

Tags タグ

id	name
1	Engineer
2	Leader

Tag_Users ユーザー情報とタグの中間テーブル

user_id	tag_id
1	1
2	1
1	2

BobはEngineer
MaryもEngineer
BobはLeader というデータとします

最初に書いたSQL

Engineer または Leader のユーザーを取得する必要があり、以下のようなSQL文を書きました

SELECT * FROM users INNER JOIN tag_users ON users.id = tag_users.user_id WHERE (tag_users.tags_id = 1 or tag_users.tags_id = 2);

表示された結果

users.id	users.name	tag_users.user_id	tag_users.tag_id
1	Bob	1	1
1	Bob	1	2
2	Mary	2	1

Bobが2人いますね

重複を削除しようと思って書いたSQL

SELECT DISTINCT * FROM users INNER JOIN tag_users ON users.id = tag_users.user_id WHERE (tag_users.tags_id = 1 or tag_users.tags_id = 2);

表示された結果

users.id	users.name	tag_users.user_id	tag_users.tag_id
1	Bob	1	1
1	Bob	1	2
2	Mary	2	1

Bobが重複しているのでDISTINCTを追加したけどBobは減らなかった

本当に必要だったSQL

SELECT DISTINCT users.* FROM users INNER JOIN tag_users ON users.id = tag_users.user_id WHERE (tag_users.tags_id = 1 or tag_users.tags_id = 2);

表示された結果

users.id	users.name
1	Bob
2	Mary

OK

結論

取得する範囲をきちんと指定する

エラーの忘備録

エラー内容

rails db:migrateをした時に、以下のエラー文が出ました。（Mysqlのエラー）

Mysql2::Error: Specified key was too long; max key length is 767 bytes

指定したキーが長すぎるというエラーです。
テーブルに色々カラムを追加すると、キーの制限である767バイトを超えてしまうため
このエラーが起こってしまいます。

原因

config/database.yml

  default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  socket: /tmp/mysql.sock

encoding: utf8mb4という記述のためエラーが出ます。

解決方法

encoding: utf8に書き換えます。

config/database.yml

  default: &default
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  socket: /tmp/mysql.sock

utf8mb4とutf8の違いはいまいちわかっていないのですが、utf8mb4は1〜4バイト、utf8は1〜3バイトの文字が格納できるようです。そのためutf8だと、絵文字などは保存できませんが、代わりに容量が大きくなるのではないかな？と推測しました。

すでにrails db:createでデータベースを作成してしまっている場合は、

% rails db:drop
% rails db:create

か

% rails db:reset

で、もう一度データベースを作り直してあげる必要があります。

おわりに

もし間違えている点などございましたら、ご指摘いただけましたら幸いです。
読んでいただきありがとうございました！

やりたいこと

docker-compose最初の起動時にDB複数作りたいです。

docker-compose.ymlにて、environmentでYSQL_DATABASEにDBを指定するとそのDBを初回起動時に作成してくれますが、複数DBやダンプしたデータの流し込みには対応していません。

MySQL(Docker Official Images)では/docker-entrypoint-initdb.dというディレクトリに入っているSQLやシェルスクリプトが初回起動時に実行されるようになっているので、そこに色々入れて実行してもらおうと思います。
MYSQL_DATABASEで作成したDBに対してしか実行出来ないので、作成した別のDBをUSEします。

〜やろうとしていることの流れ〜
1. DB1作る
2. DB1にダンプしたSQL流し込み
3. DB2作る
4. DB2をUSEする
5. DB2ダンプしたSQL流し込み

docker-entrypoint-initdb.dについて

このディレクトリに入っている.sh、.sql、.sql.gzいずれか拡張子を持つファイルがアルファベット順に実行されます。
それ以外の拡張子のファイルは無視されます。

構成

ディレクトリ

├── docker-compose.yml
└── mysql
    ├── Dockerfile
    ├── dbdata
    └── initdb
        ├── 001.ｓｑｌ
        ├── 002.ｓｈ
        └── 003.sql

docker-compose.yml

docker-compose.yml

services:
  mysqldata:
    image: busybox
    volumes:
      - ./mysql/dbdata:/var/lib/mysql
  mysql:
    build: ./mysql
    volumes_from:
      - mysqldata
    ports:
      - 4306:3306
    environment:
      - MYSQL_ROOT_PASSWORD=password
      - MYSQL_DATABASE=db_1
    volumes:
      - ./mysql/initdb:/docker-entrypoint-initdb.d

• MYSQL_DATABASEでdb_1を作成
• docker-entrypoint-initdb.dを./mysql/initdbにマウントします

dockerfile

dockerfile

FROM mysql:5.7

イメージ指定するだけ。

001.sql

db_1に流し込むダンプしたSQL。

002.sh

002.sh

#!/bin/sh

echo "CREATE DATABASE IF NOT EXISTS \`db_2\` ;" | "${mysql[@]}"

• db_2を作成
• ${mysql[@]} = 引数付きのmysqlコマンドを展開しています
  • docker-entrypoint.shで中身が確認出来ます

003.sql

003.sql

USE db_2;
/*以下db_2に流し込むダンプデータ*/

USEコマンドはファイルをまたぐと無効になるので、ダンプデータの先頭に必要です。

$ cat header.sql 003.sql > 003_new.sql
// コマンドで合成するなら

おわり

他にもdb_2へのダンプするSQLファイルをtxt形式など(自動で実行されない形式)で設置しシェル上で流し込みを実行するやり方や、csvファイルで設置して流し込みするやり方があるようです。
個人的にはシェル上ででDBの作成だけをするやり方が楽が好きですが、

• DBが増えるとファイルが増えて複雑になる
• .sql.gz形式の時は先頭にUSEを挿入出来ない

ので使い所は限られるかと思います。

環境

この記事ではmacOS Catalina10.15.6にインストールしたRuby 2.6.5を使っています。

前提

３つのモデルを作成し、以下の状況となっています。

User / ユーザー
Report / レポート
Comment / ユーザーとレポートの関連付けテーブル

コメントがついているレポートの削除を行う際に、以下のエラーが発生しました。

エラー発生

ActiveRecord::InvalidForeignKey in ReportsController#destroy
外部キーエラー

何が原因なのか、その解決策を考えます。

原因

レポートの削除を行う際に、コメントテーブルのユーザーとレポートのidの値がなくなることが原因と判断。（外部キー制約のため）
ユーザーとレポートのidの関連付けがなくなったものは、コメントも削除できるようにしたい。

解決策

dependent: :destroyをUserモデルとReportモデルに記述する。
この記述により、親モデル（Report)を削除する際に、その親モデルに紐づく「子モデル(Comment)」も一緒に削除できる」ようになります。

検証

app/models/user.rb

class User < ApplicationRecord
  has_many :reports
  has_many :comments, dependent: :destroy
end

app/models/report.rb

class Report < ApplicationRecord
  belongs_to :user
  has_many :comments, dependent: :destroy
end

無事解決しました！
以上です。
同じような悩みや壁にぶつかっている方のお役に立てれば幸いです！

参考

下記を参考にしたので、より深く内容を理解したい方は、見てみてください！
https://qiita.com/Ushinji/items/650fa295a3054d2fe582
https://qiita.com/ITmanbow/items/2170ccaceafd5d401df8
https://qiita.com/Tsh-43879562/items/fbc968453a7063776637

初めに

GCPをつかっていてコネクションプールの話に行き着きました。
「最大コネクション数が100を超えました」というエラーが発生。
なぜコネクション数が多くなってしまうのかを調べ、見つけた対処方法となります。

環境

クラウド：GCP
WEBサーバーをVue/CloudRun
APIサーバーをGolang/CloudRun
DBサーバーをMySQL/CloudSQL

原因

APIを叩くたびにAPIサーバーとDBサーバー間で新しいコネクションができているから

と判明した。

つまり、コネクションの使い回しができていなかった。

対策

database.sqlの以下のメソッドを使用し、コネクションプールの設定を行う。
また、ORMでもコネクションプールできるらしい。

メソッド名	説明
func (db *DB) SetMaxOpenConns(n int)	接続の最大数を設定。 nに0以下の値を設定で、接続数無制限。
func (db *DB) SetMaxIdleConns(n int)	コネクションプールの最大接続数を設定。
func (db *DB) SetConnMaxLifetime(d time.Duration)	接続の再利用が可能な時間を設定。dに0以下の値を設定で、ずっと再利用可能。

以下のようにして実装。

DBconnection

func NewLocalDBConnection() error {
    /* ===== connect datebase ===== */
    // user
    user := os.Getenv("MYSQL_USER") 
    // password
    password := os.Getenv("MYSQL_PASSWORD") 
    // connection database
    database := os.Getenv("MYSQL_DATABASE") 
    // connection host
    host := "localhost" 
    // connection port
    port := "3306" 

    var err error
    DB, err = setupDB("mysql", fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true", user, password, host, port, database))
    if err != nil {
        return fmt.Errorf("sql.Open: %v", err)
    }

    return err
}

//this function is a function for connection pooling
func setupDB(Driver string, dsn string) (*sql.DB, error) {
    db, err := sql.Open(Driver, dsn)
    if err != nil {
        return nil, err
    }
    //コネクションプールの最大接続数を設定。
    db.SetMaxIdleConns(100)
    //接続の最大数を設定。 nに0以下の値を設定で、接続数は無制限。
    db.SetMaxOpenConns(100)
    //接続の再利用が可能な時間を設定。dに0以下の値を設定で、ずっと再利用可能。
    db.SetConnMaxLifetime(100 * time.Second)

    return db, err
}