今回は、データベースを選択するSQL文を紹介します！

1.データベースを選択するSQL文はこのように書きます

USE データベース名;

試しに、前回の記事↓で参照できたデータベースからデータベースを指定&SQL文の実行をしてみます！
https://qiita.com/yni_se/items/1915a25f34578aa19b12

USE pictweet4;

次のように表示されていれば、正しくデータベースの選択ができています！

mysql> USE pictweet;
Database changed
mysql> 

2.テーブルを確認するSQL文はこのように書きます

SHOW TABLES;

次のように表示されていれば、正しくテーブル一覧を表示できています！

mysql> SHOW TABLES;
Empty set (0.01 sec)

mysql> 

今回はここまで！次回はテーブル作成・構造確認方法を紹介します＾＾

異なるDockerコンテナに同一構造のテーブルを持つデータベースがあり、
両方動かさねば！テストで動かしたい！ということで上司にどうすれば？と確認したところ、
「ダンプレストア！んじゃやってみよう！」とのことでやってみた。

まずはダンプ

$ mysqldump -u root -p -h $IP -P $PORT DATABASE_NAME TABLE_NAME > dump.sql

いけー！と思ったらエラー。

実行結果

$ mysqldump: Couldn't execute 'SELECT COLUMN_NAME, hogehogefugafuga

SELECT COLUMN_NAMEを実行できない？的な？

ということらしくちょいハマりしたが、以下のオプションをつけることで解決。

$ --column-statistics=0

実際の使用例はこんな感じ。

$ mysqldump -u root -p -h $IP -P $PORT DATABASE_NAME TABLE_NAME --column-statistics=0 > dump.sql

パスワードの入力が求められるので入力する。

これでダンプ完了。

ダンプで生成したファイルについて

dump.sqlの中身はこんな感じ。

dump.sql

DROP TABLE IF EXISTS `TABLE_NAME`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `TABLE_NAME` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hoge` varchar(20) NOT NULL,
  hogehogefugafuga

DROP TABLE IF EXISTSで既存で同名のテーブルがあった場合はそれを削除してから、
CREATE TABLEで再度新しく同様のテーブルを作成するクエリが盛り込まれていますね。

dump.sqlには続きがあり、それが以下の処理です。

dump.sql

LOCK TABLES `TABLE_NAME` WRITE;
/*!40000 ALTER TABLE `TABLE_NAME` DISABLE KEYS */;
INSERT INTO `TABLE_NAME` VALUES (hogehogefugafuga

LOCK TABLESでテーブルをロックして、書き込みができないようにしているみたいです。

これは、ダンプを作成したあとに、別でDB（テーブル）への新規の書き込みがあった場合などに、
ダンプファイルとの整合性が取れなくなってしまうことを避けるためです。

その後INSERT INTO `TABLE_NAME`で値を挿入していく。

というファイル内容になっています。

次はレストア

ファイル内容がわかったところで、実際にdump.sqlを新テーブルへと流し込みましょう（レストア）。

（今回ファイルはローカル上で移動させてあります。）

mysql -u root -p -h $IP -P $PORT DATABASE_NAME < dump.sql

ダンプ時とレストア時で＜＞の向き先に注意です。

またまたパスワードの入力を求められるので入力して、レストア完了です。

やってみて

あとはmysqlにログインして確認するでもよし、
phpMyAdminにアクセスして確認するでもよし。

最初はコマンド打つのアレコレ分からないしなんとなく怖いしって
感じなんですが、、慣れると便利ですね。（万が一にも変なコマンド打たないようにせねば。。）

異なるDockerコンテナに同一構造のテーブルを持つデータベースがあり、
両方動かさねば！テストで動かしたい！ということで上司にどうすれば？と確認したところ、
「ダンプレストア！んじゃやってみよう！」とのことでやってみた。

まずはダンプ

$ mysqldump -u root -p -h $IP -P $PORT DATABASE_NAME TABLE_NAME > dump.sql

いけー！と思ったらエラー。

実行結果

$ mysqldump: Couldn't execute 'SELECT COLUMN_NAME, hogehogefugafuga

SELECT COLUMN_NAMEを実行できない？的な？

ということらしくちょいハマりしたが、以下のオプションをつけることで解決。

$ --column-statistics=0

実際の使用例はこんな感じ。

$ mysqldump -u root -p -h $IP -P $PORT DATABASE_NAME TABLE_NAME --column-statistics=0 > dump.sql

パスワードの入力が求められるので入力する。

これでダンプ完了。

ダンプで生成したファイルについて

dump.sqlの中身はこんな感じ。

dump.sql

DROP TABLE IF EXISTS `TABLE_NAME`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `TABLE_NAME` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hoge` varchar(20) NOT NULL,
  hogehogefugafuga

DROP TABLE IF EXISTSで既存で同名のテーブルがあった場合はそれを削除してから、
CREATE TABLEで再度新しく同様のテーブルを作成するクエリが盛り込まれていますね。

dump.sqlには続きがあり、それが以下の処理です。

dump.sql

LOCK TABLES `TABLE_NAME` WRITE;
/*!40000 ALTER TABLE `TABLE_NAME` DISABLE KEYS */;
INSERT INTO `TABLE_NAME` VALUES (hogehogefugafuga

LOCK TABLESでテーブルをロックして、書き込みができないようにしているみたいです。

これは、ダンプを作成したあとに、別でDB（テーブル）への新規の書き込みがあった場合などに、
ダンプファイルとの整合性が取れなくなってしまうことを避けるためです。

その後INSERT INTO `TABLE_NAME`で値を挿入していく。

まとめると、

1. テーブル削除
2. テーブル作成
3. レコード挿入（作成）

というファイル内容になっています。

次はレストア

ファイル内容がわかったところで、実際にdump.sqlを新テーブルへと流し込みましょう（レストア）。

（今回ファイルはローカル上で移動させてあります。）

mysql -u root -p -h $IP -P $PORT DATABASE_NAME < dump.sql

ダンプ時とレストア時で＜＞の向き先に注意です。

またまたパスワードの入力を求められるので入力して、レストア完了です。

あとはmysqlにログインして確認するでもよし、
phpMyAdminにアクセスして確認するでもよし。

やってみて

MySQLは自己開発でも副業でも触ってますし、CUIでの操作もアレコレやったことありますが、
mysqldumpコマンドは初めて使いました。

テーブル被りは削除とか、ロックかけて整合性の保持をするなど、
気の利いてるコマンドだなぁ、なんて思いました。

最初はコマンド打つのアレコレ分からないしなんとなく怖いしって
感じなんですが、、慣れると便利ですね。
（万が一にも変なコマンド打たないようにせねば。。）

今日、Laravelのサーバーでgit pullした後に、migrate:refreshをしようとしたらエラーが発生してしまいました。

原因は、migrationの名前を変更したことなのですが、さらに問題が発生しました。

phpMyAdminに入れねぇ。。

phpMyAdminがサーバー側のエラーで入れませんでした。

そこで考えました。

PHPでテーブル消すコード書けば良くね？

ということで、サーバーへのアクセス制限をしたのちに下のコードでテーブルを全て消しました。

<?php
$dbh = new PDO ('mysql:dbname=DB名;host=ホスト名;charset=utf8;','ユーザー名','パスワード');
$stmt = $dbh->query('SHOW TABLES');
while($re = $stmt->fetch(PDO::FETCH_ASSOC)){
  echo $re['Tables_in_DB名'].',';
  $dbh->query('DROP TABLE '.$re['Tables_in_DB名']);
}

こんな感じです。

2~3回実行して何も表示されなくなれば完璧です。

後は、php artisan migrateしましょう。

最後に

いや、これコマンドでのmysqlで済む話だったかも。。。

ログイン時、オプションを指定

mysql -u root -p --local-infile=1

データベースを選択

use hogedb

インポートするコマンド

LOAD DATA LOCAL INFILE './huga.csv' --インポートしたいcsvファイル
INTO TABLE hogetable --インポートするテーブル
FIELDS TERMINATED BY '\t' --インポートしたいcsvの区切り文字
LINES TERMINATED BY '\n';  --csvの行区切り指定

おまけ

インポート前に、格納済みのデータをクリアしたいときは

TRUNCATE TABLE hogetable;

別テーブルからデータを移行したいとき

insert into new_table (
    column1,
    column2,
    column3)
select
    column1,
    column2,
    column3,
from
    old_table;

せっかく開発したならデプロイして世間に公開したくなるのが世の常ですね（？）
AWSは個人開発で使うには複雑で、ランニングコストも馬鹿にならん。けど、管理もデプロイも簡単にしたいなーってなったらherokuでデプロイしてみましょう。

対象読者

・初めてherokuでデプロイする人
・herokuでMySQLを使いたい人
・楽にデプロイしたい人

参考記事

railsアプリをgithubとherokuにpush
【初心者向け】railsアプリをherokuを使って確実にデプロイする方法【決定版】
Herokuへのデプロイ方法【Heroku+Rails+MySQL】
Railsアプリでherokuを使うときのDBをMySQLに変更する

開発環境

Ruby 2.6.5
Rails 6.0.0
MySQL 14.14

herokuでデプロイ

デプロイのためのツールをインストール

$ brew install heroku-toolbelt
とやったものの、エラーが。

Error: No available formula with the name "heroku-toolbelt" 
==> Searching for a previously deleted formula (in the last month)...
Warning: homebrew/core is shallow clone. To get complete history run:
  git -C "$(brew --repo homebrew/core)" fetch --unshallow

Error: No previously deleted formula found.
==> Searching for similarly named formulae...
Error: No similarly named formulae found.
==> Searching taps...
==> Searching taps on GitHub...
Error: No formulae found in taps.
MasutomoMacBook-Pro:medipra user$ cd ~
MasutomoMacBook-Pro:~ user$ brew install heroku-toolbelt
Error: No available formula with the name "heroku-toolbelt" 
==> Searching for a previously deleted formula (in the last month)...
Warning: homebrew/core is shallow clone. To get complete history run:
  git -C "$(brew --repo homebrew/core)" fetch --unshallow

Error: No previously deleted formula found.
==> Searching for similarly named formulae...
Error: No similarly named formulae found.
==> Searching taps...
==> Searching taps on GitHub...
Error: No formulae found in taps.

で、色々調べてみると、今（2020/03/27現在）、toolbeltというのは使われていないらしい。。。
今はCLI(Command Line Interface)だそうです。
なので、herokuのページにあるSet Upから以下を実行。

herokuのセットアップに従って下記を実行(macの場合）
$ brew install heroku/brew/heroku

そしたら次のエラー

Error: The `brew link` step did not complete successfully
The formula built, but is not symlinked into /usr/local
Could not symlink bin/heroku
Target /usr/local/bin/heroku
already exists. You may want to remove it:
  rm '/usr/local/bin/heroku'  <=これを実行する

・・・略

To use the Heroku CLI's autocomplete --
  Via homebrew's shell completion:
    1) Follow homebrew's install instructions https://docs.brew.sh/Shell-Completion
        NOTE: For zsh, as the instructions mention, be sure compinit is autoloaded
              and called, either explicitly or via a framework like oh-my-zsh.
    2) Then run
      $ heroku autocomplete --refresh-cache　<=次にこれを実行

  OR

  Use our standalone setup:
    1) Run and follow the install steps:
      $ heroku autocomplete

解決方法らしきこと書いてあったので以下を順に実行。

＄ rm '/usr/local/bin/heroku'
$$ heroku autocomplete --refresh-cache

そしたらherokuのログインが求められ
無事にログインできました(^O^)／

これで、herokuのコマンドが使えるようになります。

herokuでMysqlを使えるようにする

クレジットカード情報が必要なので、まだの人は予め登録しておきましょう（無料で使えますので）

Ignite-freeを選べば無料で使えるようです。

MySQLのURLを追加する

Settingsの二段目を確認しましょう。Config　Varsです。
ちゃんとClearDBが追加できていれば、上のだんCLEARDB_DATABASE_URLはすでに入っています。

ターミナル から以下を実行してください。
hogehogeの部分は、CLEARDB_DATABASE_URLのmysql://以下の部分をコピーして入れ替えます。

$ heroku config:add DATABASE_URL='mysql2:{hogehoge}'

heroku masterにアップ

masterにプッシュするときは以下のコマンド

$ git push heroku master

エラー①puma

こんなエラーが出ました

Your bundle is locked to puma (3.12.3), but that version could not be found in
remote:        any of the sources listed in your Gemfile. If you haven't changed sources, that
remote:        means the author of puma (3.12.3) has removed it. You'll need to update your
remote:        bundle to a version other than puma (3.12.3) that hasn't been removed in order
remote:        to install.

どうやらpumaがダメっぽいです。
そういえばgithubのsecurityで指摘されていたので、gemfileで以下のように追記してbundle install

gem 'puma', '~> 3.12.4'

エラー②URIの登録ミス

次はこちらでした。真っ赤な字で書かれています。

remote:  !     Could not detect rake tasks
remote:  !     ensure you can run `$ bundle exec rake -P` against your app
remote:  !     and using the production group of your Gemfile.
remote:  !     rake aborted!
remote:  !     URI::InvalidURIError: bad URI(is not URI?): mysql2:{//b4...略...}

指示に従って bundle exec rake -P を実行するも、なんも変わらず。
で、よく見たらURIのエラーっぽい
よーくみると、mysql2:の後に余計な{}が入っている！
なので、一旦削除して、再度登録し直し。

で、めでたくデプロイできました。

Githubとの連携も可能

ご存知githubでバージョン管理しているなら、デプロイはgithubのmasterにマージするだけで完了させることもできます。楽すぎてやばいですね。
各自のダッシュボードから画像のページにいけると思うので、Githubと連携させたら完了です。なお、Githubと連携後も $ git push heroku master は使えます。

herokuでmigrate

masterにプッシュできたら、おなじみのマイグレーションを忘れずに実行しましょう。

$ heroku run rake db:migrate

僕の場合はseedファイルを用意しているのでそれもやっておきます

$ heroku run rake db:seed

うまくいけば、ザーーーーーッとデータが入る様子がみられます。

migrate時のエラー

rails aborted!
NoMethodError: undefined method `standard_name_cont_all

「どのファイルのstandard_name_cont_allだよぉ」って探したら、
modelのproduct.rbのコードが原因でした。
コメントアウトで通しておきました。

てか、このmodel、あまり意味なく残しておいたコードでした。余計なコードはエラーの元になるので消しておくのがよろしいですね。

その他メモ

clearDBは無料だと3,600クエリ/時間

seedファイルでデータを流し込んだ時のエラー

rails aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: User 'hogehogehoge' has exceeded the 'max_questions' resource (current value: 3600)

公式Q＆Aに回答がありました。

無料だと1時間当たり3,600クエリで、有料にすると18,000クエリまで増えるんだとか。必要ならアップグレード必須ですね。

また、容量は以下のようになってます。
Ignite : 5MB (free)
Punch : 1GB (9.99/month)
Drift : 5GB ($49.99/month)

ちなみに、csvファイルでデータを入れるとき、1行のデータを入れるのに３回クエリが飛ぶようです。（画像の赤枠で1行分（３クエリ）です）
大量のデータをseedで入れるときはこのへんも気を配る必要があります。

独自ドメイン、SSLが必要なら有料化が必要

無料の範囲だとドメインはhogehoge.herokuapp.comとなっていて、ちょっとダサめ。
また、今はSSL化が必須なので、サービスとして稼働させるならこちらもやっておきましょう。
$7/monthからできます。

ということで、以上です。
役に立ったらコメント残してもらえるとモチベーションになります。

laravel 5.７を使用したアプリ開発時に「php artisan migrate」を行ったところ次のようなエラーメッセージが出た。

SQLSTATE[HY000]: General error: 3780 Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'posts_user_id_foreign' are incompatible.

user_idがどうこう言っていたのでmigrationファイルを確認したが異常は見つからず…。

その時の自分が書いたコードが以下の通り

create_users_table.php

public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            省略
        });
    }

create_posts_table.php

public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            中略
            $table->integer('user_id')->unsigned();
            $table->foreign('user_id')
                  ->references('id')->on('users')
                  ->onDelete('cascade');
        });
    }

しばらくGoogleで検索しているとこのような下記のような記事が見つかった

Laravel 外部キー制約エラー General error: 3780 Referencing column

記事によるとどうやら外部キー制約によるエラーであることが判明。

記事の通り確認すると問題の箇所は
「create_posts_table」のほうではなく「create_users_table」の方にあった。

$table->bigIncrements('id');

と書かれていたのを

$table->Increments('id');

へと修正し、再度migrateを実行したところうまく行った！解決！！

どうやら自分が参照していたのはlaravel５.８以降のバージョンの記事であったらしくそれより以前のバージョンを使用していたためうまくかなかったらしいとのこと。

laravelにはバージョン別に様々な記事や参考書などがあるが自身が使っているバージョンの把握も大切だと学んだ。

ログイン状態からログアウトするためにクッキーを用いて実装しているのですが
右も左も分からなくなってしまいました。

ログイン画面までは実装できています。

ログアウト画面に関しても、自分なりにググって、調べてコードを打ち込みましたが全然ダメです;(

どなたか詳しい方、御指導、御鞭撻頂けないでしょうか。

session_start();

if( !empty($_GET['btn_logout']) ) {
    unset($_SESSION['admin_login']);
}
if( !empty($_POST['btn_submit']) ) {
if( !empty($_POST['admin_password']) && $_POST['admin_password'] === PASSWORD ) {
        $_SESSION['admin_login'] = true;
    } else {
        $error_message[] = 'ログインに失敗しました。';
    }
}

setcookie("name", "test");
echo $_COOKIE["name"];
setcookie("name", "test",time()+60*60*24);
//setcookie("name" , "" , time()-100 );

執筆背景

ローカル環境で作成中のWebアプリを立ち上げようと、

$ rails s

を入力し、ブラウザからWebアプリにアクセスしたところ、突然

Completed 500 Internal Server Error in 7ms (ActiveRecord: 0.3ms | Allocations: 5953)

という謎のエラーが発生。
直訳では、内部サーバーエラー500。

とりあえず、パソコンを再起動し、再びアプリを再起動すると、

Mysql2 :: Error :: ConnectionError

の文字が表示された。

よく分からないがとりあえずmysql2で何か問題があると考えられる。

※なお、今回のrailsのバージョンは6.0.2.2です。
また、Yay! You’re on Rails!のページ(以下画像)にはアクセスできました。

結論

このエラーは、
クライアント側とMySQLサーバー側で設定されている最大バケット値(これはmax_allowed_packetという変数で指定される)よりも転送時のバケット数の方が高い場合

に起こるエラーです。

今回行った作業内容として、

・ MySQLの公式ドキュメントに掲載されている方法
(mysqld_safeドキュメントの最初の行にulimit -d 256000を追加したのちmysqlを再起動)

・ MySQLをアンインストールしたのち、再インストール

・ データベースを削除したのち、再作成

などを試したが、直らなかった。

そのため、
アプリのバックアップを作成しアプリを一から作り直したのち、バックアップを反映させてアプリを再起動
したところ直った。

(2020/04/01:追記)
後日、ファイルのアップロード機能の動作確認をしていたところ、アップロードボタンを押した後に
TypeError (no implicit conversion of nil into String)
というエラーが発生した(以下のリンク参照)。
https://qiita.com/hasegawa-naoto/items/895dc96bb5aed93d769a

こちらのエラーの頭行に
Completed 500 Internal Server Error
と同様のエラーが示されていたため、エラー対処の結果、
アップローダーのfilenameメソッドが正しく機能していなかったこと
が本記事のエラー原因ではないかと考えている。

エラーが出る直前で行なっていたこと

ファイルのアップロード機能を実装しており、ファイルをアップロードしてファイルを削除して再びアップロードして…みたいなことの動作チェックをしていた。

ここから連想されるに、mysqlサーバーに負荷がかかりすぎてしまったのかな…？という1つの推測が浮かび上がった。

作業内容

以下では、実際の作業内容を示していきます。

MySQLの公式ドキュメントに掲載されている方法を試す

Mysql2 :: Error :: ConnectionErrorで調査したところ、公式ドキュメントを発見。
https://dev.mysql.com/doc/refman/5.6/ja/packet-too-large.html

どうやら、

クライアント側とMySQLサーバー側で設定されている最大バケット値(これはmax_allowed_packetという変数で指定される)よりもアップロード時のバケット数の方が高い場合

に接続が失われるようになっているらしい。

対処法として、mysqld_safeドキュメントファイルの最初の行にulimit -d 256000を追加したのち、MySQLを再起動してみてください

と書いてあったため、指示通りに試してみる。

sudo find / -name mysqld_safe #mysqld_safeファイルがどこにあるか検索
/usr/local/bin/mysqld_safe

sudo vim /usr/local/bin/mysqld_safe #mysqld_safeファイルを開く

mysqld_safe

ulimit -d 256000  #追加
#!/bin/sh
# Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB
…以下略

:wq!コマンドで保存しようとすると、Read Only File(読み込み専用ファイル)と表示され、保存できないため、以下のコマンドを打つことで保存した。

:w !sudo tee % #このコマンドを入力する

W12: Warning: File "mysqld_safe" has changed and the buffer was changed in Vim a
s well
See ":help W12" for more info.
[O]K, (L)oad File:o #OKを選択する
"mysqld_safe" [readonly] 1040L, 29923C
:q! #上書き保存を入力する

MySQLを再起動。

$ mysql.server restart
Shutting down MySQL
. SUCCESS! 
Starting MySQL
. SUCCESS! 

その後、railsを再起動し接続を試みるも直らない(笑)

MySQLの再インストールを試す

仕方がないので、MySQLをアンインストールする方針で進めることにする。

$ brew uninstall mysql
Uninstalling /usr/local/Cellar/mysql/8.0.19... (294 files, 291.4MB)

$ brew install mysql

$ rails db:migrate

$ rails s

しかし、直らず。

データベースの再作成を試す

仕方がないので、データも大したことなかったということもありデータベースそのものを削除することにした。

$ mysql -u user_name -p
$ enter password

mysql > drop database database_name;
query OK,1 row affected

mysql> create database database_name;
query OK,1 row affected

しかし、結局直らなかった…。

アプリを再作成

他のアプリでは起動することができ、MySQLも正常に動作したので、一旦、このアプリのバックアップを作成しておき、アプリを作り直すことにした。

$ rails new app_name

この後、コピーしておいたアプリの情報を新規で作成したアプリに反映させたのち、

$ rails s

直った…。

まとめ

今回は、簡易なアプリかつローカル環境下だったから良かったものの大規模なアプリ開発かつ本番環境下の場合、このエラーが出たときはかなり苦渋の選択を強いられそう。

調査した感じだとこのエラーは割とrailsで開発してる中では多いみたいだが、
どの記事をみても原因が不明瞭なことが多く修復方法も(アンインストールなどの)強行突破な方法が多い。

ただ、公式ドキュメントによれば、

クライアント側とMySQLサーバー側で設定されている最大バケット値(これはmax_allowed_packetという変数で指定される)よりも転送時のバケット数の方が高い場合

に起こるエラーであることは確かなので、転送時のバケット数に気を配りながら動作チェックをするなどを気構えておく必要があるのかなと思う。

(2020/04/01:追記)
結論でも述べたが、このエラーの原因は
アップローダーのfilenameメソッドが正しく機能していなかったこと
だと考えており、正しく機能しなかった理由は
fileが添付されていないにも関わらずcarrierwave.rbのfile_nameメソッドを呼び出していたため
です。
そのため、file_nameメソッドの中身の後ろに
if original_filename.present?
をつけてファイルが添付されているときのみメソッドを呼び出すようにすることで解決すると考えています。

詳細は以下の記事からお願いします。
https://qiita.com/hasegawa-naoto/items/895dc96bb5aed93d769a

Catalina で mysql クライント使おうと思った時に設定したことのメモ

インストール

$ brew install mysql

my.cnf

homebrewでインストールした場合、my.cnf の場所は /usr/local/etc/my.cnf っぽい。

/etc/my.cnf とかにはないので注意。

/usr/local/etc/my.cnf

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

初期値はこんな感じ。

日本語使うなら下記の記述を追記

[mysql]
default-character-set = utf8

リモートサーバー接続するときの注意

ポートフォワーディングでリモートサーバーのDBに接続しようとした際に、下記のようなErrorが表示された。

$ mysql -h 127.0.0.1
ERROR 2026 (HY000): SSL connection error: error:141A318A:SSL routines:tls_process_ske_dhe:dh key too small

SSL接続を強制されてなければ、--ssl-mode=DISABLED をつければOK。

$ mysql --ssl-mode=DISABLED -h 127.0.0.1

接続先のサーバー古すぎるからかも。

app.phpに接続情報を明記

2つの「datebase_aのcommentsテーブル」と「datebase_bのusersテーブル」が存在するとします。(usersテーブルとcommentsテーブルを別のデータベースで管理するのはあくまで例です)
1つのusersに複数のcommentsが紐づきます。

config/app.phpに接続情報を明記しましょう。

app.php

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',
        'username' => 'user_a', 
        'password' => 'password',
        'database' => 'database_a',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'log' => false,
    ],
    'database_b' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',
        'username' => 'user_b', 
        'password' => 'password',
        'database' => 'database_b',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'log' => false,
    ],
],

モデルに定義

defaultConnectionName()を使いましょう。

UsersTable.php

namespace App\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

class EstatesTable extends Table
{
    public static function defaultConnectionName(){
        return 'database_b';
    }

    public function initialize(array $config)
    {
        parent::initialize($config);
        $this->setTable('users');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');
        $this->addBehavior('Timestamp');
    }
}

別々のデータベースのテーブルをjoin

UsersController.php

$users = $this->Users->find()
    ->join([
        'table' => 'datebase_a.comments',
        'alias' => 'C',
        'type' => 'LEFT',
        'conditions' => 'C.user_id = Users.id',
    ])
    ->toArray();

参考:公式サイト

config/app.phpに接続情報を明記しましょう。

app.php

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',
        'username' => 'user_a', 
        'password' => 'password',
        'database' => 'database_a',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'log' => false,
    ],
    'database_b' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',
        'username' => 'user_b', 
        'password' => 'password',
        'database' => 'database_b',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'log' => false,
    ],
],

これでおしまい。

別記事でCakePHP3で別々のデータベースのテーブルをjoinする方法を紹介していますので、気になる方はこちらの記事をご覧ください。

データベースの接続時は問題なかった

データベースはMySQLを使用しており、データベース名「datebase-b」(仮)のようにしていました。CakePHPを使用しており、接続時は問題ありませんでした。

app.php

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',
        'username' => 'user_a', 
        'password' => 'password',
        'database' => 'database_a',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'log' => false,
    ],
    'test' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',
        'username' => 'user_b', 
        'password' => 'password',
        'database' => 'database-b',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'log' => false,
    ],
],

SQL文でエラーになった

今回はポータルサイトであったため、複数のデータベースが存在します。「datebase_aのusersテーブル」と「datebase-bのcommentsテーブル」をjoinする際にSQL文でエラーが発生しました。(usersテーブルとcommentsテーブルを別のデータベースで管理するのはあくまで例です)
下記はCakePHPです。

test.php

$users = $this->Users->find()
    ->join([
        'table' => 'datebase-b.comments',
        'alias' => 'C',
        'type' => 'LEFT',
        'conditions' => 'C.user_id = Users.id',
    ])
    ->toArray();

これでsyntax errorになりました。

ちなみにCakePHPを使った複数の接続方法を知りたい方はこちらの記事をご覧ください。

解決方法はエスケープ

ハイフン(-)が悪さをしていたみたいです。SQL文でデータベース名をエスケープ(``)したら動きました。

test.php

$users = $this->Users->find()
    ->join([
        'table' => "`portal-oem`.comments",
        'alias' => 'C',
        'type' => 'LEFT',
        'conditions' => 'C.user_id = Users.id',
    ])
    ->toArray();

データベース名はよく使うと思うので、定数にしておきましょう。

const.php

define("B", '`database-b`');

test.php

$users = $this->Users->find()
    ->join([
        'table' => B.".comments",
        'alias' => 'C',
        'type' => 'LEFT',
        'conditions' => 'C.user_id = Users.id',
    ])
    ->toArray();

データベース名で記号を使うときは、有効か一度確認した方が良いですね。

参考

• 公式サイト
• phpMyAdminのSQLに付加される「’」に似た記号「`」は何？
• 特殊文字の指定
• MYSQL バッククオートについて

MySQLエラー発生

PCを再起動した後rails sでアプリケーションサーバを起動ようとしたところMySQLエラーが発生しました。

Mysql2::Error::ConnectionError
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

エラーを見ると「/tmp/mysql.sockを通じてローカルのMySQLサーバーに接続できませんでした。」と書いてあります。

mysql.sockが無い！？
sockというのは、ファイルシステムソケットのことで、ローカルシステム内で通信を行う際に、このsockファイルを仲介役にしてMySQLとRuby/Rails間での通信を行っています。
このsockファイルはMySQLが起動した時に生成され、MySQLが停止すると削除されるファイルです。

MySQLエラー解決に必要なこと

MySQLエラーには色々な原因があるので、今起きている問題は何なのか要素を切り分けて考えることが必要です。

１．MySQLサーバーが起動しているか
２．起動に必要な権限があるか
３．ソケットのパスが正しく通っているか
４．ソケットの生成場所が正しく設定されているか
５．MySQLサーバーが複数起動していないか

この記事では１と２の解決方法について解説します。

1.MySQLサーバーを起動させる

PCを再起動したときにMySQLが停止してmysql.sockが削除されてしまっています。
なのでMySQLを起動させてsockファイルを生成しましょう。

# MySQLを起動
$ mysql.server start

mysql.server startでダメなら

原因不明ですがMySQLを起動する権限が現在のユーザーでなくなっていることがあるようです。
この場合でもsudoコマンドを使ってroot権限でMySQLを起動させることができます。

$ sudo mysql.server start

Starting MySQL
.. SUCCESS!
と表示されればOKです。

２．必要な権限を与える

権限の確認

１．MySQLのあるディレクトリまで、cdコマンドで移動します。

$ cd /usr/local/var

２．pwdコマンドで今居るディレクトリを表示して正しく移動できたか確認します。

$ pwd
# /usr/local/varと表示されればOKです。

３．ディレクトリの状態を確認します。

$ ls -l

usr/local/varの配下で起動中のプロセスが表示されます。

total 0
drwxrwxr-x   4 yamazaki  admin   128  8  7  2019 homebrew
drwxr-xr-x   3 yamazaki  admin    96 11  8 23:22 log
drwxr-xr-x  13 _mysql    _mysql  416  3 19 06:40 mysql
drwx------  25 yamazaki  admin   800  3  5 01:02 postgres
drwx------  24 yamazaki  admin   768 10 30 00:34 postgresql@10

mysqlの所だけユーザー名ではなく_mysqlとなっています。

4.chownコマンドでディレクトリの権限を変更させます。

$ sudo chown -R [ユーザー名] mysql

5.[ユーザー名]の部分に任意のユーザー名を入れます。

$ sudo chown -R yamazaki mysql

6.変更を確認します。

$ ls -l

total 0
drwxrwxr-x   4 yamazaki  admin   128  8  7  2019 homebrew
drwxr-xr-x   3 yamazaki  admin    96 11  8 23:22 log
drwxr-xr-x  12 yamazaki  _mysql  384  3 21 17:45 mysql
drwx------  25 yamazaki  admin   800  3  5 01:02 postgres
drwx------  24 yamazaki  admin   768 10 30 00:34 postgresql@10

権限がユーザーに変更されました。
この状態でMySQLを起動してみましょう。

＄　mysql.server start

無事起動できればOKです。

MySQLのAES_ENCRYPTで暗号化を試す機会があり、暗号化後のデータ長を試算したときのメモ。

環境

• MySQL5.6

想定ケース

• aes-256-cbc
• AES_ENCRYPT関数で暗号化した値はHEXで16進数へ変換
• 初期化ベクトルはRANDOM_BYTES(16)で生成し、HEXで16進数へ変換して暗号化した値に付加
• 文字コードはutf8

計算

MySQLの公式リファレンスマニュアルによると、暗号化後の長さは以下の式で計算できるとある。

16 * (trunc(string_length / 16) + 1)

今回は暗号化したバイナリの値をHEXで16進数へ変換するので2倍し、
さらに同じく16進数へ変換した初期化ベクトルを付与するので16*2を加算する。

16 * (trunc(string_length / 16) + 1) * 2 + 16 * 2

string_lengthは、文字数 * 1文字のbyte数で計算。

検証

平文「あいうえおかきくけこさしすせそ12345678901234567890」を暗号化。
期待値：16 * (trunc((15 * 3 + 20) / 16) + 1) * 2 + 16 * 2 = 192

mysql> SET @iv=RANDOM_BYTES(16);
Query OK, 0 rows affected (0.00 sec)

mysql> select CHAR_LENGTH(CONCAT(HEX(@iv), HEX(AES_ENCRYPT('あいうえおかきくけこさしすせそ12345678901234567890', SHA2('password',512), @iv)))) as length;
+--------+
| length |
+--------+
|    192 |
+--------+
1 row in set (0.00 sec)

参考

MySQL 5.6 リファレンスマニュアル - 12.13 暗号化関数と圧縮関数 - AES_ENCRYPT

先日、ステージング環境がAmazon EC2からGCEへ移行されました。その際、データベースの移行をしたので手順を書いていきます。

EC2にssh接続

$ ssh -i 使用する鍵のパス ユーザ名@接続先のIPアドレス

例)
$ ssh -i ~/.ssh/hoge.pem ec2-user@xx.xx.xx.xx

DBをdumpする

ssh接続ができたら

[ec2-user@ip-xxx ~]$ mysqldump -u ユーザー名 -p -h DBホスト DB名 > 保存したいファイル名

例）
[ec2-user@ip-xxx ~]$ mysqldump -u hoge -p -h hoge.rds.amazonaws.com  hoge_staging > hoge_staging.dump

そしてパスワードを聞かれるのでパスワードを入力すればdumpファイルがEC2上に作成されます。ここまでできたらEC2からはログアウトしてください。
ユーザー名等は自分のDB設定ファイルを参照してください。Railsならdatabase.ymlです。

EC2上に作成したdumpファイルをローカルへコピー

$ scp -i 使用する鍵のパス ユーザ名＠接続先のIPアドレス:EC2上のコピー元のファイルのパス ローカルのコピー先のパス

例）
$ scp -i ~/.ssh/hoge.pem ec2-user@xx.xx.xx.xx:/var/www/foo/bar/hoge_staging.dump /Users/mishikun/workspace/

これで、EC2上で作成したdumpファイルを手元のPCのworkspace配下まで持ってくることができました。次は移行先のGCEにこのファイルを持っていきます。

移行先ににdumpファイルを送信

sftpを使って送信します。

$ sftp -i 使用する鍵のパス ユーザ名@接続先のIPアドレス

sftpができたらputコマンドでローカルにあるdumpファイルをGCEに送ってあげましょう。場所は適当です。私はホームディレクトリに送信しました。

GCEにssh接続

GCEにssh接続します。コマンドは上記と同様なので割愛させていただきます。

移行先DBにdumpをインポート

[xxx@xxx ~]$ mysql -u ユーザ名 -p -h DBホスト dumpをインポートしたいDB名 < dumpファイルのパス

例) 
[xxx@xxx ~]$ mysql -u hoge -p -h xx.xx.xx.xx hoge_staging < ~/hoge_staging.dump

これでDBの以降は完了です✌️
もし間違い等があればご指摘のほどよろしくお願いします。

参考

MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム
scpコマンド チートシート - Qiita