今回、新しいMySQLのデータベースをエクスポートして。古いMySQLにインポートする必要が生じました。
私は、MySQLに詳しくないので、Adminerを使いエクスポートしたものを、そのままインポートしようとしました。

すると、utf8mb4という文字セットを理解できませんという内容の英語のエラーメッセージが表示されました。

ネットで調べると、utf8mb4をUTF8に置き換えるという方法が複数見つかりました。
いくつか試しましたが、どれも成功しませんでした。

別のエラーが出てしまうのです。
ネットの記事を総合すると、数値のデータ項目がある場合は、文字セットや照合順序の置き換えをしてもダメなようです。
確かに、私の場合は、NULLデータのところでエラーになりました。

そのため、クライアント指定の期日に間に合わない事態になってしました。
悔しい気持ちもあり、もう意味がないけどネットで調べていました。

私自身のWordPressの引越しのために、Adminerの使い方を再勉強していた時に、データベースの照合順序の変更ができることを知りました。

画像の中のプルダウンメニューを選択することで、色々と照合順序を変更できることを知りました。

ということは、エクスポートしたいデータベースの照合手順を予めインポートするMySQLが理解できる照合順序に変えて置くことで、問題なくインポートできるように推測しています。

こちらの記事で、古いバージョンのMySQLをインストールする方法を知りました。
https://engineers.weddingpark.co.jp/?p=1284
テスト環境を作って、実証実験したいと思っています。

はまった時は、落ち着こう

落ち着いてネット検索すると、問題の解決策や解決するヒントを見つけることが出来ます。

WordPressを日本語環境で使う場合に、推奨される照合順序

こちらの記事が、とても勉強になりました。
https://note.mokeco.in/209/

WordPressを日本語環境で使う場合の照合順序は、utf8_general_ciもしくはutf8mb4_general_ciだそうです。

データベースの文字セットと照合順序を勉強する良い機会になりました。

1日

use DB;

DB::table('users')
  ->orderBy('created_at','desc')
  ->whereRaw('created_at > NOW() - INTERVAL 1 DAY')
  ->count()

30分

  ->whereRaw('created_at > NOW() - INTERVAL 30 MIN')

リファレンス

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

この記事が作成されている時点では、以下のリファレンスが用意されていたのですが、バージョンアップで参照できなくなるとおもうので、適宜環境にあったリファレンスを参照してください。

筆者のサーバー環境はMySQL 5.5だったのだけど、実行環境依存だとおもうので、MINとかが使えないバージョンなどもあったりするのだろうか。。。

はじめに

MySQLで絵文字の入ったレコードをinsertしようとしたところ、エラーが出たので、その時のことを書きます。

エラーを調べてみる。

色々調べてみると絵文字のバイト数の関係で、insertできなかったようです。

絵文字のバイト数を確認してみる。

普段Rubyをよく使うので、Rubyのファイルを作りバイト数を確認。

emoji.rb

puts "?".bytesize #絵文字のバイト数を確認
puts "顔".bytesize #日本語のバイト数を確認
puts "a".bytesize  #アルファベットのバイト数を確認。

実行。

$ ruby emoji.rb
4
3
1

絵文字は4バイトで、日本語は３バイト、アルファベットは1バイトのようです。

原因

元々の文字コードの設定がutf8だったようで、3バイト文字までしか認識できず、
絵文字を認識するには4バイト文字が認識できるutf8mb4が必要のようです。

utf8mb4とは

文字コードの一種で、MySQLでUTF-8で4バイト文字を扱う事が出来るとのこと。
utf8の設定だと3バイト文字までしか扱えません。

DBの文字コード変更

DBの文字コードをutf8mb4に変更します。(DB名には自分のDBの名前を入れてください。)

ALTER DATABASE DB名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

テーブルの文字コード変更

テーブルも。(テーブル名には自分のテーブルの名前を入れてください。)

ALTER TABLE テーブル名 DEFAULT CHARACTER SET utf8mb4;

/etc/my.confの設定

mysqlの設定ファイルにも文字コードの設定を書き加えます。
いったんmysqlからログアウトします。

vi /etc/my.cnf

# 下記を追記
[mysqld]
...
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

MySQLの再起動

mysql.server restart

終わりに

以上を行うと絵文字入りのレコードを追加できました。
絵文字入りのデータを扱う場合は文字コードに気をつけましょう。

ファイル関連

・ローカル環境からリモート環境へファイル（フォルダ）を転送する

Terminal

$ scp -r 送りたいファイル名 root@ホスト名:送りたいリモートのディレクトリ（Ex.)/home/www）

・ファイルの所有者権限の確認など

Console

$ ls -l 確認したいファイル名

・ディレクトリにあるすべてのファイルの権限を確認したいとき

Console

確認したいディレクトリに移動ののち
$ ll

・ファイルの所有者権限の設定など

Console

$ chown -R 所有者:グループ所有者 ファイル名

-Rを付与することで、ディレクトリ内全体の所有者の設定を変更する

・viでファイルを開いた際に、文字化けしていて、日本語が表示されないとき

Console

:set enc=utf-8

・viでログを確認するときに最新のエラーログを確認したいとき

Console

:$

・Apacheエラーのログを確認する

Console

$ cd /
$ vi /var/log/httpd/error_log

MySQLに関するコマンド

・MySQLでユーザーを作成する際などに、求められるパスワードのセキュリティレベルの設定に関するコマンド

Console

-> global validate_password_policy="LOW or MEDIUM or HIGH"

・MySQLでパスワードのセキュリティレベル一覧を表示するコマンド

Console

-> show global variables like '%validates%';

・MySQLのステータス確認

Console

$ systemctl status mysqld.service

・MySQL　ログ

Console

$ cd /
$ cat /var/log/mysql/mysqld.log

・MySQLのユーザー一覧

Console

-> SELECT user, host from mysql.user

・MySQLのユーザー作成

Console

-> create user 'ユーザー名'@'ホスト名' identified by 'パスワード';

・MySQLのユーザーの削除

Console

-> drop user 'ユーザー名'@'ホスト名';

・特定のユーザーにすべての権限を付与

Console

-> grant all privileges on データベース名.* to 'ユーザー名'@'ホスト名';

・MySQLで行った変更の反映

Console

-> FLUSH PRIVILEGES;

問題

bundle installをすると

Installing mysql2 0.5.2 with native extensions
Gem::Ext::BuildError: ERROR: Failed to build gem native extension.
（略）
An error occurred while installing mysql2 (0.5.2), and Bundler cannot continue.
Make sure that gem install mysql2 -v '0.5.2' --source 'https://rubygems.org/' succeeds before bundling.

と出る

指示通り
gem install mysql2 -v '0.5.2' --source 'https://rubygems.org/'
を走らせても解決しない

解決策

bundle configを実行すると

build.mysql2
Set for your local app (/Users/ユーザー名/アプリケーション名/.bundle/config): "--with-cppflags=-I/usr/local/opt/openssl@1.1/include"
と出る

そこで
bundle config --local build.mysql2 "--with-ldflags=-L/usr/local/opt/openssl@1.1/lib"
を実行した

bundle configで確認すると
build.mysql2
Set for your local app (/Users/GO/source_code/clubru/.bundle/config): "--with-ldflags=-L/usr/local/opt/openssl@1.1/lib"
となっていた

再度bundle installを実行すると無事mysqlをインストールできた

調べて出てきた結果と違ったこと

自分の場合は
bundle config --local build.mysql2 "--with-ldflags=-L/usr/local/opt/openssl@1.1/lib"
で解決したが、調べて出てきた解決策は
bundle config --local build.mysql2 "--with-ldflags=-L/usr/local/opt/openssl@1.1/lib --with-cppflags=-I/usr/local/opt/openssl@1.1/include"
や
bundle config --local build.mysql2 "--with-cppflags=-I/usr/local/opt/openssl@1.1/include"
datta

やりたいこと

ActiveRecordを与えられた配列順でorderしたい。
ids配列にユーザのidが格納されていて、このidの格納順に従って、Userモデルに対してorderしたい。

ids = [6,2,24,5,3,7]

問題

Rails5.1までは以下の書き方で配列ids順にuserをorderすることができました。

User.where(id: ids).order(['field(id, ?)', ids])

しかし、Rails5.2以降（Rails 6も）ではこのような書き方をすると
エラーが発生します。

DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "FIELD(id, ?)". Non-attribute arguments will be disallowed in Rails 6.1. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql().

Rails5.2以降は、SQLインジェクションを防ぐためにorderの引数に属性以外の文字列を取れなくなりました。今回でいうところのfieldが属性以外の文字列に当たってしまうと考えられます。

解決方法

Arel.sql()で囲めば良いので以下の書き方に変更します。

User.where(id: ids).order([Arel.sql('field(id, ?)'), ids])

また、機械的にArel.sql()で囲むのではなく、引数が安全な値であることを確認した上でArel.sql()を使いましょう。
https://qiita.com/jnchito/items/5f2f00c93c0ba68e4d31

ちなみに、この書き方どうするんだっけ？って思ってググっても出てこない時は、
Railsプロジェクトのテストコードを見ると解決することが多い。

今回の件に関してはRails6のテストコードで以下の書き方をされている。

https://github.com/rails/rails/blob/d684ca232d13abf518e184b1a8123f5438463074/activerecord/test/cases/relations_test.rb#L460-L469

  def test_finding_with_sanitized_order
    query = Tag.order([Arel.sql("field(id, ?)"), [1, 3, 2]]).to_sql
    assert_match(/field\(id, 1,3,2\)/, query)

    query = Tag.order([Arel.sql("field(id, ?)"), []]).to_sql
    assert_match(/field\(id, NULL\)/, query)

    query = Tag.order([Arel.sql("field(id, ?)"), nil]).to_sql
    assert_match(/field\(id, NULL\)/, query)
  end

Mysqlのレプリケーションステータスの確認コマンド

SHOW SLAVE STATUS によって返されるフィールドについて説明は参考に貼ってあるリファレンスを見る。

$ mysql -h {{ host }} -u{{ user }} -p -p -e 'SHOW SLAVE STATUS\G'

Enter password:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: XXX.XXX.XXX.XXX
                  Master_User: sample-repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000001
          Read_Master_Log_Pos: 14900000
               Relay_Log_File: relaylog.012729
                Relay_Log_Pos: 1000
        Relay_Master_Log_File: mysqld-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 14900000
              Relay_Log_Space: 4500
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 3e11fa47-71ca-11e1-9e33-c80aa9429562
             Master_Info_File: /var/mysqld.2/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 10
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
            Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
                Auto_Position: 1

参考

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.5.35 SHOW SLAVE STATUS 構文

マウントしたフォルダがPermission Denied、そんなはずはない！
777付与もしたけど動かない！そんなあなたに。

原因はSELinux。

chcon -Rt svirt_sandbox_file_t [マウント対象フォルダのパス]

これでディレクトリ単位で許可することができる。
再度 dokcer-compose upすれば

動きます

前置き

運営しているサービスがかなりレガシーなので、負の遺産が多いです。

入社した頃からサイトの速度が遅いなと感じていながら、日々の業務を追われているうちに後回しになっていました。

それから半年たったある日、SEOに注力する方針となり上司からサービスのサイトの表示速度が遅いので、本日中に何とかしてほしいとのことでようやく時間を割いて対応することになりました。

環境

• PHP 5.2
• Breeze framework
• MariaDB 5.5.62

速度改善で要因特定にやったこと

• Chromeのデバックツールで表示速度を確認
• バックエンドかフロントエンドか要因を切り分け
  • バックエンドに時間がかかっていることが判明
• PHPのソースコード内に速度を図るコードを埋め込み
  • DBの処理に時間がかかっていることが判明

速度改善にやったことまとめ

1. （効果：◎）DB：EXPLAINで実行計画を行い、INDEXを設定
2. （効果：△）PHP：ロジックを修正（不要な処理を削除）
3. （効果：◎）DB：EXPLAINで実行計画を行い、INDEXを設定
4. （効果：△）PHP：ロジックを修正（取得系の処理改善）
5. （効果：◎）DB：クエリキャッシュを有効にする

DBの速度改善が効果絶大だったので、対応したことを後記していきます。

DB：EXPLAINで実行計画を行い、INDEXを設定

FYI. MySQLのIndexをはるコツ | Qiita

-- インデックス削除
ALTER TABLE example_table_1 DROP INDEX user_id(user_id);

-- インデックス追加
ALTER TABLE example_table_2 ADD INDEX index_user_id(user_id);

DB：クエリキャッシュを有効にする

DBを再起動せずに反映する方法

SET GLOBAL query_cache_size=64*1024*1024;
SET GLOBAL query_cache_limit=8*1024*1024;
SET GLOBAL query_cache_type=1;
FLUSH QUERY CACHE;

実施結果

サイトの表示速度がなんと「5800ms」から「350ms」になりました。
およそ16倍の速度改善をすることが出来ました。

……クエリキャッシュの威力半端ない！！

最後に

ちょっとした設定一つでこんなにもサイトの表示速度が変わるのは感動しました。
むしろ今まで放置だったのも良くないですが…（苦笑）
いま、動いているリソースで改善できることはまだまだありますね！

とはいえ、ミドルウェアのバージョンがそもそも古かったり、フレームワークが古かったり、サーバーのスペックが適切でなかったり……
根本的な要因もあるので、引き続き地道に改善していきつつ思い切った決断をする必要もありそうですね。