20191203のMySQLに関する記事は11件です。

Lambda x MySQLでデータを更新してもクエリ結果が変わらない(キャッシュされているような振る舞いをする)

問題

表題の通りだが、Lambda x MySQL(RDS)で、テーブルのデータを変更しても、Lambda上でPythonからDBに対するSELECT結果が変わらないという問題が起きた。その場しのぎとしてLambdaのコードを変更すると、データ変更が反映されるためそれで運用をしのいでいる環境だった(または、少し時間を空けていた)。問題の解消法がわかったので載せておく。ただし原因はわかっていない。
(補足:RDSはサーバレスの部品ではないので、一般的にはDynamoDBを推奨)

解消方法

StackOverflowで同様の現象で困っている人がいて、回答の通り、Connection確立時に autocommit=True とすることで問題を解消できた。または各SELECT後にcommitしても解消できるかと思う。

stackoverflow: why is an aws lambda python call to a mysql rds being cached

以下はpymysqlというライブラリの例

query.py
# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit=True)  # これを追加

考察というか疑問

問題を回避できたのは良いが、今回の問題がLambda起因なのかMySQL起因なのかすらよくわかっていない。
クエリ後にConnectionが切れているのは確認したが、コネクション再開時に依然としてトランザクションが続いているということはありえるのだろうか。

もしそうだとすると、Select後にCommitすることで、他のトランザクションの変更が反映されるということであれば理解できる。
(少なくともMySQL(innoDB)のデフォルトの分離性レベルは「REPEATABLE-READ」で、innoDBであればこの分離性レベルであってもファントムリードも起きないほど分離性レベルは高い)

pymysqlのgithub issuesでは以下のように書いてあった。

Autocommit off can mean you are seeing a shadowed copy of the data in which case you will not see outside changes until you rollback/commit the transaction

オートコミットがオフであることが意味することは、データのシャドウドコピーを見ることであり、その状況では、コミット/ロールバックしない限り外部の変更を見ることができないだろう、と。

どなたか、何が起きているのか教えてください:bow:

参考

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

API等で取得した情報をMySQLのデータベースに格納するときに、2バイト文字が文字化けしてしまったときに確認すること

はじめに

取得した2バイト文字が文字化けしてしまい、色々確認して解消したというメモに近いものです。

環境

  • MySQL
  • Java

JavaのHttp Clientを利用してAPIを取得したデータを、
JDBCドライバーを利用してMySQLに接続し、DBに入れようとしています。

そのいち

テーブルを作成した際に、お決まりの呪文を唱えているか。
お決まりの呪文は、以下の通りです。

set character_set_client = utf8;
set character_set_connection = utf8;

CREATE TABLE `TABLE_NAME` (

()

) DEFAULT CHARSET=utf8';

ポイントはSQLを流す前に呪文を唱えることですが、
心配性な方は、CREATE TABLE文の終わりでも呪文を唱えましょう。

そのに

HttpClientでリクエストをビルドする際、呪文を唱えているか

HttpClient client = HttpClient.newBuilder().version(Version.HTTP_2).build();
Builder requestBuilder = HttpRequest.newBuilder().uri(uri).setHeader("Content-type","application/json; charset=UTF-8");
HttpRequest request = requestBuilder.build();

setHeaderの後ろあたりの、charset=UTF-8 が、呪文です。

そのさん

JDBCドライバーの設定情報で、呪文を唱えているか

dbconnection.properties
driver.class.name=com.mysql.jdbc.Driver
uri=jdbc:mysql://mysql.test.com/mydb?useUnicode=true&characterEncoding=utf8

上記のmysql.test.comが接続先で、mydbがデータベース名です。
その後の?useUnicode=true&characterEncoding=utf8 に注目してください。
呪文を唱えています。

さいごに

私の場合は、そのさん で解決しました。
似たようなことを、まったく同じ環境で実装したことがあるのですが、その時は文字化けしなかったです・・・

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MySQLで大量のシンプルなクエリを高速化する

どんなに軽いクエリでも、たとえばWebサーバーとMySQLの間のRTTが5msあって20クエリを実行したらRTTだけで100msかかってしまいます。

たくさんのデータをinsertするときは bulk insert (VALUES の後に複数の行を書くクエリ) を使うテクニックは有名です。しかしこのテクニックは次のような場合に使えません。

  • 複数のテーブルに1行ずつINSERTしたい
  • 複数のUPDATEやSELECTをまとめたい

たとえば弊社のある案件で次のような場面がありました。

  • 新規ユーザー作成時に大量のテーブルにINSERTしたい
  • ログイン時に大量のテーブルにSELECTしたい

こういった場面を高速化するために multiple statements と multiple result sets を利用しました。

Multiple Statements

複数のクエリを ; で区切って一発(Pythonレベルで言えば1つの Cursor.execute() 呼び出し、プロトコルレベルで言えば1つのCOM_QUERYパケット)で送ることができます。複数のINSERT文を ; で繋ぐことで、大量のINSERT文やUPDATE文を高速化することができます。

RTTを削減できるだけでなくTCPのパケット数も削減できるので、MySQLサーバー側のクエリを受信する部分の負荷の削減も期待できます。

ただし、この ; を使ってクエリを連結する仕組みはSQLインジェクションでもよく悪用されています。そのため MySQL protocol ではハンドシェイク時に multiple statements を無効化できるようになっています。MySQLクライアントのライブラリによっては multiple statements を利用するためにオプションが必要かもしれません。

例えば Python の mysqlclient はデフォルトで multiple statements が利用できます。(fork元の昔からあるライブラリとの後方互換性のため...)
一方 Go の github.com/go-sql-driver/mysqlmultiStatements=true を指定する必要があります。

もう一つの注意点として、 MySQL の (PREPARE 文ではなくプロトコルレベルの) prepared statement を使う場合は、 placeholder を利用できません。自前でエスケープしてSQL文字列を組み立ててからクエリを投げるか、 prepared statement を使わないように設定する必要があります。

Multiple Result sets

複数のSELECT文をまとめたい場合は、複数のクエリを投げるだけでなくその結果を受け取る必要もあります。そのためには mutltiple result sets を使います。

複数の Result Set が返されたとき、Python では Cursor.nextset() を使って次の Result Set を受け取ることができます。

import MySQLdb

con = MySQLdb.connect(host="127.0.0.1", port=3306, user="test", password="test")
cur = con.cursor()

q = """\
select 1;
select 2;
select 3;
select 4;
select 5;
"""

cur.execute(q)

while True:
    print(cur.fetchall())
    if not cur.nextset():
        break

Go の場合は database/sqlRows.NextResultSet() を使って同じように複数の Result Set を受け取ることができます。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

Laravelで弐寺のクリアランプマネージャーをつくる

Laravel #2 Advent Calendar 20195日目のの記事です。

概要・動機

beatmania IIDXというゲームが好きです(唐突)

既存のクリアランプマネージャもあるにはあるが、機能が多くて僕には使いこなせません。
(低難度とか普段やらんしフォルダ分けもいらん)

身の丈にあったツールを使いたいので、クリアランプマネージャー兼地力推定サービスを自分用に車輪の再開発します。

前提として、自分はMVCが何ぞやということはゆるふわ理解している程度で、フルスタックのwebフレームワークは初心者です。

できたアプリはこんなかんじ↓
スクリーンショット 2019-12-03 15.30.42.png
(開発期間:2日)

クリアできそうな楽曲も教えてくれます!!(目玉機能)

github

https://github.com/dhaiibfiukkiu/estimator

MVCそれぞれの概要

モデル

正直ココが全てを決める気がする。

アプリの動作としては、
1.indexページに全楽曲の表とクリアした難易度のラジオボタン,submitボタン
2.ログイン後、submitでそのuserとクリアした楽曲を紐付けたclearsデータを挿入。
3.clearsデータに基づいてラジオボタンをchecked。また、地力の推定も行う。

といったことを想定しているので、userテーブル、musicsテーブル、clearsテーブルがいるかなと思った。

本来userとmusicsは多対多の関係であるので、clearsがうまく中間テーブルとして作用してくれる。
つまり、clearsの複合主キーがu_idm_idであるから、新たに主キーidを作る必要は無いと感じた。

このせいで後に苦労する羽目になるのだが(後述)
空白の ERD.png

musicsテーブルのeからfcまではそれぞれのクリア難易度(地力値)です。

値はこちらを参考にさせていただきました。

単純にページをコピペすると、それぞれの楽曲は改行(\n)で、それぞれの要素はタブ文字(\t)で区切られていたので、こちらをlaravelのシーディングにうまく合う形に加工します。

musicTableSeeder.php
<?php                                                                                                                                   

    use Illuminate\Database\Seeder;
    use App\Music;

    class musicTableSeeder extends Seeder
    {
       /**
        * Run the database seeds.
        *
        * @return void
        */
       public function run()
       {

           $param=[
               'name' => '#MAGiCVLGiRL_TRVP_B3VTZ',
               'e' => '-2.914433',
               'n' => '-2.038145',
               'h' => '-1.965019',
               'exh' => '3.882106',
               'fc' => '8.346122',
           ];
           $musics = new Music;
           $musics->fill($param)->save();
     //(以下略)

目標はこの形です。そのために以下のPythonスクリプトを書きました。

makelist.py
input='''(ここに楽曲のリスト)
'''
input=input.split('\n')

for line in input:
    tmp=line.split('\t')
    if tmp[1]=='Infinity':
        continue
    name=tmp[0].replace("'",r"\'")
    e=tmp[1]
    n=tmp[2]
    h=tmp[3]
    exh=tmp[4]
    fc=tmp[5]
    print('''
        $param=[
            'name' => '{}',
            'e' => '{}',
            'n' => '{}',
            'h' => '{}',
            'exh' => '{}',
            'fc' => '{}',
        ];
        $musics = new Music;
        $musics->fill($param)->save();
    '''.format(name,e,n,h,exh,fc))

あとはこのスクリプトの出力をmusicのseederスクリプトにぶち込んでphp artisan db:seedしてやるだけです。(勿論テーブルのマイグレーションをした後にです)

あと、appディレクトリ以下のMusic,Clearモデルには以下のように記述しておきます。

Music.php
<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Music extends Model
{
    protected $table = 'musics';

    protected $guarded = [
        'm_id',
        'name',
        'e',
        'n',
        'h',
        'exh',
        'fc'
    ];
}
Clear.php
<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Clear extends Model
{
    protected $table = 'clears';
    protected $primaryKey = 'id';

    //仕方ないのでidをサロゲート(代理)キーに

    protected function rules(){
        return [
            'id' => 'integer',
            'm_id' => 'integer',
            'u_id' => 'integer',
            'info' => 'integer'
        ];
    }

    protected $fillable=[
        'id',
        'm_id',
        'u_id',
        'info'
    ];
}

ここに各要素のバリデーションのルールやらレコードの更新設定とかをするわけですね。

トラブル

Clear.phpの中に//仕方ないのでidをサロゲートキーにというコメントがあります。
実は、はじめにClearモデルを作成した際はidカラムは存在せず、m_idとu_idの複合主キーだけでした。

しかし、いざレコードをsaveメソッドで保存しようとすると、エラー。

どうやら複合主キーを使うとsaveメソッドが使えなくなるらしい。なんやそれ。

まあ、メソッドをオーバーライドすればできないこともないらしい。メンドクセー

参考

https://qiita.com/wrbss/items/7245103a5fef88cbdde9
https://github.com/laravel/framework/issues/5517

仕方がないので代理キーとしてidを追加しました。

ビュー

laravelでの開発と銘打っているし見た目にはそこまで拘りません。

resources/viewsフォルダにlayoutsフォルダ、estimatorフォルダを作成し、
layoutsにはおおもとのレイアウト、estimatorではそれを継承した具体的なビューを管理します。

/layouts/estimator.blade.php
<html>
    <head>
        <title>@yield('title')</title>
        <style>
body {font-size:16pt; color:#708090; margin: 5px;}

h1.title{
 position: relative;
  padding: 0.2em 0.5em;
  background: -webkit-linear-gradient(to right, rgb(255, 124, 111), #ffc994);
  background: linear-gradient(to right, rgb(255, 124, 111), #ffc994);
  color: white;
  font-weight: lighter;
  box-shadow: 0 0 4px rgba(0, 0, 0, 0.56);
}
#logout{font-size:20pt; text-align:right; color:#f6f6f6;
    margin:-20px 0px -30px 0px; letter-spacing:-4pt;}
ul{font-size:12pt;}
td{font-size:16pt;}
hr{margin:25px 100px; border-top: 1px dashed #ddd;}
.menutitle{font-size:14pt; font-weight:bold; margin: 0px;}
.content{margin:10px;}
.footer{text-align:right; font-size:10pt; margin:10px;
    border-bottom:solid 1px #ccc; color:#ccc;}
.blink {
  animation: blinkAnimeA 0.1s infinite alternate;
}
@keyframes blinkAnimeA{
   0% { background: #4dffff }
  95% { background: #ffff1a }
 100% { background: #ffff1a }
}
.highlighted{
color: #0000ff;
text-decoration: underline;
}
.blinkchr {
  animation: blinkAnimeB 0.6s infinite alternate;
}
@keyframes blinkAnimeB{
   0% { color: #ff0000 }
  97% { color: rgba(255, 255, 255, 0.99) }
 100% { color: rgba(255, 255, 255, 0.99) }
}
.fixed_btn
{
  font-size: 1.5em;
  width: 30%;
  height: 10%;
  position: fixed;
  bottom: 10px;
  right: 10px;
  padding: 6px 40px;

  display: inline-block;
  padding: 0.5em 1em;
  text-decoration: none;
  background: #668ad8;/*ボタン色*/
  color: #FFF;
  border-bottom: solid 4px #627295;
  border-radius: 3px;
}
.fixed_btn:active{
  /*ボタンを押したとき*/
  -webkit-transform: translateY(4px);
  transform: translateY(4px);/*下に動く*/
  border-bottom: none;/*線を消す*/
}
        </style>
    </head>
    <body>
        <h1 class='title'>@yield('title')</h1>
        <h2>@yield('loginfo')</h2>
        <hr size="1">
        <div class="content">
            @yield('content')
        </div>
        <div class="footer">
            @yield('footer')
        </div>
    </body>
</html>

ここにはめ込むビューが以下

estimator/index.blade.php
@extends('layouts.estimator')
@section('title','地力Estimator')
@section('loginfo')
    @php//@parent
    @endphp
@if(Auth::check())
<p>DJ NAME:{{$user->name}}</p>
<p id='logout'><a href='/logout'>ログアウト</a></p>
@else
    <p>ログインしていません(<a href='/login'>ログイン</a>|<a href='/register'>登録</a>)</p>
@endif
@endsection

@section('content')
<form action='/' method='post'>
<center>
<button type='submit' class='fixed_btn'>record</button>
<!--推定値-->
<h1 class='suiteichi'>
@if($jiriki==-100&&!Auth::check())
推定値を取得するためにはログインしてください
@elseif($jiriki==-100&&Auth::check())
推定値を取得するためには、チェックボックスにチェックを入れたあとに、右下のボタンを押下してください
@else
推定地力:{{$jiriki}}
@endif
</h1>
<table border = '2'>
<tr><th>曲名</th><th>NO PLAY</th><th>EASY</th><th>NORMAL</th><th>HARD</th><th>EX-HARD</th><th>FULLCOMBO</th></tr>
{{csrf_field()}}
<!--<input type='submit' value='Estimate'>-->
@foreach($musics as $item)

@if(!isset($clears))
@php
$check=-1
@endphp
@else
@php
$check=$clears->where('m_id',$item->m_id)->first()->info
@endphp
@endif

    <tr>
        <!--<td>{{$item->name}}</td>-->

        <td
        @if($check==1)
        bgcolor='#98fb98'
        @elseif($check==2)
        bgcolor='#87cefa'
        @elseif($check==3)
        bgcolor='#ff6347'
        @elseif($check==4)
        bgcolor='#ffff00'
        @elseif($check==5)
        class='blink'
        @endif
        >{{$item->name}}</td>

        <td bgcolor='#a9a9a9'>
        <label><input type="radio" name="{{$item->m_id}}" value=0
        checked
        >NULL</input></label>
        </td>

        <td bgcolor='#98fb98'>
        <label
        @if($item->e <= $jiriki)
        class=highlighted
        @endif
        ><input type="radio" name="{{$item->m_id}}" value=1
        @if($check==1)
        checked
        @endif
        >{{round($item->e,7)}}</input></label>
        </td>

        <td bgcolor='#87cefa'>
        <label
        @if($item->n <= $jiriki)
        class=highlighted
        @endif
        ><input type="radio" name="{{$item->m_id}}" value=2
        @if($check==2)
        checked
        @endif
        >{{round($item->n,7)}}</input></label>
        </td></label>

        <td bgcolor='#ff6347'>
        <label
        @if($item->h <= $jiriki)
        class=highlighted
        @endif
        ><input type="radio" name="{{$item->m_id}}" value=3
        @if($check==3)
        checked
        @endif
        >{{round($item->h,7)}}</input></label>
        </td>

        <td bgcolor='#ffff00'>
        <label
        @if($item->exh <= $jiriki)
        class=highlighted
        @endif
        ><input type="radio" name="{{$item->m_id}}" value=4
        @if($check==4)
        checked
        @endif
        >{{round($item->exh,7)}}</input></label>
        </td>

        <td class='blink'>
        <label
        @if($item->fc <= $jiriki)
        class=highlighted
        @endif
        ><input type="radio" name="{{$item->m_id}}" value=5
        @if($check==5)
        checked
        @endif
        >{{round($item->fc,7)}}</input></label>
        </td>

    </tr>
@endforeach
</table>
</center>
</form>
@endsection

@section('footer')
copyright 2019 Okada Hibiki
@endsection

今回は、主要なページが一つだけだったこともあり、ロジックをビュー側に少し任せすぎてしまったかなというのが反省点。

コントローラ

estimatorController.php
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Music;
use App\Clear;
use Illuminate\Support\Facades\Auth;

class estimatorController extends Controller
{
    public function index(Request $request){
        $user = Auth::user();
        $musics = Music::all();

        if(Auth::check()){
            $clears = Clear::where('u_id',$user->id)->get();///getをかかないとダメ!!!
            if(Clear::where('m_id',1)->where('u_id',$user->id)->count()==0){
                foreach($musics as $music){
                    $clear = new Clear;
                    $clear->m_id = $music->m_id;
                    $clear->u_id = $user->id;
                    $clear->info = 0;
                    $clear->save();
                }
            }
        }
        else{
            $clears=null;
        }

        if($request->method()=='POST'){
            if(Auth::check()){
                foreach($musics as $music){
                    $clear = Clear::where('m_id',$music->m_id)->where('u_id',$user->id)->first();
                    $m_id = $music->m_id;
                    $clear->info = (int)$request->$m_id;
                    //$clear->timestamps = false;
                    $clear->save();
                }
            }
            else{
                //
            }
        }

        if(Auth::check()){
            $clears = Clear::where('u_id',$user->id)->get();
            $jiriki = array();
            foreach($clears as $clear){
                switch($clear->info){
                case 0:
                    break;
                case 1:
                    array_push($jiriki,$musics->where('m_id',$clear->m_id)->first()->e);
                    break;
                case 2:
                    array_push($jiriki,$musics->where('m_id',$clear->m_id)->first()->n);
                    break;
                case 3:
                    array_push($jiriki,$musics->where('m_id',$clear->m_id)->first()->h);
                    break;
                case 4:
                    array_push($jiriki,$musics->where('m_id',$clear->m_id)->first()->exh);
                    break;
                case 5:
                    array_push($jiriki,$musics->where('m_id',$clear->m_id)->first()->fc);
                    break;
                }
            }
        }
//ここに推定値の処理
        if(!isset($jiriki)){$jiriki=-100;}
        elseif(count($jiriki)==0){$jiriki=-100;}
        else{
            rsort($jiriki);
            $num=ceil(count($jiriki)*0.3);
            $sum=0;
            for($i=0;$i<$num;$i++){
                $sum+=$jiriki[$i];
            }
            $sum=$sum/$num;
            $jiriki=$sum;
        }

        $param = ['musics' => $musics,'user' => $user,'clears' => $clears,'jiriki' => $jiriki];
        return view('estimator.index',$param);
    }
    public function logout(){
        Auth::logout();
        return redirect()->action('estimatorController@index');
    }
}

注意点としては、モデルの利用の際にuseしなきゃいけないことくらいか。

処理の概要としては、ログインがあるかをチェックして、しているのであればデータベースをチェック。そのユーザのクリア情報があれば取得し、なければNO PLAYとしたレコードをclearsテーブルに挿入。

POSTメソッドでのアクセスであれば、それを元にクリア情報を更新。

さらに、最新のクリア情報から自力を計算します。

この地力の計算が困ったもので、それらしい確率分布を適用しても、地力低めなEASYクリアとかが増えると地力が下がる、という現象がどうしても起こってしまう。

ので、クリアした楽曲の地力上位3割の平均値を推定地力としています。こっちのほうがシンプルでいいし、さっきみたいな不具合も幾らか少なくなるので良い。

Auth

Laravelには標準で認証機能があります。
これがものすごく便利で、php artisan ui vue --authするだけでログイン機能が実装できます。
Laravelのバージョン5ではphp artisan make:authで(読んだ本はこっちだった)
6系ではそれが使えない点に注意してください。

今回のアプリケーションでは、トップページの閲覧にログインは必須ではないが、データの保存や推定値の表示にはログインが必須であるような実装にしました。

そして、標準のAuthを利用すると、/register,/login,/homeといったpathが用意されます。

初期状態だと、/registerで登録、/loginでログイン後、/homeにリダイレクトされる仕様だったのですが、これを/にリダイレクトするよう変更します。

この処理はAuth内のコントローラに記述されています。
/app/Http/Controllers/Auth/以下のRegisterController.php,LoginController.php内の
protected $redirectTo = '/home';
という記述を
protected $redirectTo = '/';
に変更すればOK。

ルーティング

routes/web.php
<?php

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', 'estimatorController@index');
Route::post('/','estimatorController@index');
Route::get('/logout','estimatorController@logout');

Auth::routes();

Route::get('/home', 'HomeController@index')->name('home');

ルーティングとはいえどメインは/で動作するestimatorControllerだけなのでシンプルですね。

おわりに

フルスタックのフレームワークを使ったのは初めてですが、簡単に自分にとって実用的なアプリが作れました。

正直今回のアプリならSPAと相性が良さそうなので、気が向いたらそれもつくってみようかな(たぶんやらん)

参考文献

David Skler(2017)初めてのPHP
掌田津耶乃(2017)PHPフレームワーク Laravel入門

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

ハッカソンの開催情報を自動でお知らせするBotをGithub Actionsに移行して運用費が0円になりました

新着のハッカソン・ゲームジャム・アイディアソン・開発合宿の情報を自動的にお知らせしているBotがあります。
よかったらフォローしてください!!

このBotの頭の中身についてはこちらの記事にて紹介しました。

最新のハッカソンの開催情報を自動で集めて、お知らせするBotを作ったので頭の中を紹介

また、具体的なソースコードも公開していますのでこちらを参照してください。

hackathon_portal

今回はこのHackathonPortalをこれまで、AWS Lightsail (Ubuntu)サーバーにて稼働していたものを Github Actions に完全に移行したので、その内容について紹介します。

Github Actionsの本来の使い方について

本来の Github Actions の正しい使い方はCI/CDがメインで主に以下のような開発支援として用いられます。

  • 自動的にデプロイ
  • 自動テストの実行
  • issueやプルリクのお掃除

など

今回は Github Actionsschedule機能 にのみ着目して活用しました。
また Github Actions の本来の使い方については こちら などにまとめています。

Github Actionsにて自動的にデプロイする環境作成(Webサイト編)

そもそもなぜ、Github Actionsに移行したのか?

  • Github Actions で稼働させる間は月額費用が0円になるため
    • Github Actions の費用はPublicリポジトリならいくら使っても0円です。(privateは無料枠を超えたら費用がかかります。詳しくはこちら)
    • 元々,HackathonPortalはPublicリポジトリで稼働しています。
  • 下記の仕様の関係上、 schedule機能 さえあれば、要件を満たせるので Github Actions の使用に適していたため

仕様

HackathonPortalの仕様を簡単にまとめると以下になります

  • 1日に1回イベント公開されたイベントの情報を集める → cron を活用
  • 集めた情報をデータベースに保存する
  • 保存したデータベースの情報を整理して、Twitterなどに発信する

基本的に定期的に Batch が実行されればいいので Github Actionsschedule機能 を使えば実現できます。

実現させるにあたって苦労したこと

データベース

Github Actions ではMySQLなどのミドルウェアを使用することはできますが、 Github Actions では毎回実行されるたびにデータが空っぽになります。
そのため Github Actions のみで完結させようとした場合、これまでためたデータをどこかに保持するためておく仕組みが必要です。
外部のデータベースを活用するなど考えましたがいずれの場合も

  • 結局、月額費用がかかる
  • 無料のデータベースは容量が少ない
  • 何かと制約が多い

ということで結局これまでためたデータを全てGitの管理下に置いてGithubに保管してもらうことで対応しました。

具体的には以下のフローを Github Actions の中で行なっています

  1. MySQL稼働
  2. MySQLの中にデータを挿入する
  3. データを集める → 発信する
  4. mysqldump してデータを全て SQLファイル で出力する
  5. git push で Githubにアップロード

こうすることで Github Actions (Github) のみで完結する仕組みを構築しました。

実際に設定している内容については こちら を参照

データ容量におけるGithub上の仕様

Github上では 100MB を超えるデータをpushすることができません。
(詳しくは こちら)

1ファイル 100MB を超えるデータを保存する場合は Githubでは Git LFS を使用する必要があります。
しかし、 Git LFS を使用してのデータ管理は 月間1GB を超えると月額費用が発生してしまします。

今回、月額費用0円での運用を実現したかったので、mysqldump で抽出するSQLを 1ファイル100MB未満 になるように分割してpushするようにしました。そのために以下のようなコマンドを Batch の中で実行しています。

mysqldump データベース名 テーブル名 -u ユーザー名 -pパスワード --no-create-info -c --order-by-primary --skip-extended-insert --skip-add-locks --skip-comments --compact > SQLファイル.sql

上記のように mysqldump をするときに

  • --no-create-info でデータのみを取得し
  • --skip-extended-insert で一行ずつINSERT文を抽出
  • --skip-add-locks --skip-comments --compact でコメントなどの不要なものを全て排除

したSQLファイルを出力します。そして以下のように出力したSQLファイルを split コマンドを使い分割しています。

split -l 10000 -d --additional-suffix=.sql fulldump.sql テーブル名/

上記の場合10000行ごとに分割しています。これにより1ファイル 大体 100MB 以下になるように分割しています。

split コマンドの --additional-suffix オプションはLinuxでは使用可能ですが、Mac OS では使用することができません。Mac OS--additional-suffix オプションを使用する場合は gsplitbrew install gsplit にてインストールして gsplit コマンドにすることで使用することができます。

実行されるブランチをmaster以外に切り替える

基本的に Github Actionsschedule機能default ブランチ(最初は master ブランチ)の内容が実行されます。
今回データを蓄積するブランチは master ブランチ以外で行いたかったです。そのため、Github Actions が実行された瞬間にブランチを切り替えるには Github Actionsが実行される yml にて使用されている、actions/checkout (uses: actions/checkout@v1 の部分) で、with:とともにブランチを一緒に指定することで実行できました。(ref: ブランチ名)

以下にその部分を記述します。

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v1
      with:
        ref: crawled-data

Github Actions内からGithubへpushする

Github Actions の中からGithubへのpushをそのまま行おうとするとエラーとなってしまいます。
今回、pushを可能にするために Personal access tokens を発行し、User Name(Githubのアカウント名) とともに以下のように設定します。

git remote set-url origin "https://${User_Name}:${Personal_Access_Tokens}@github.com/TakuKobayashi/hackathon_portal.git"

この状態で

git push origin ブランチ名

を行うことで、Github Actions の中からGithubへのpushを実現しています。

Personal access tokens の発行の仕方はこちらを参照してください。

コマンドライン用の個人アクセストークンを作成する

課題

Github Actionsで稼働しているマシンスペックの問題

Github Actionsで稼働しているマシンのストレージ容量は 14GB です。

【参考】

mysqldump したSQLファイルの容量が 14GB を超えるようなデータ量となるとき、別の方法を考える必要があります。
(現状 1GB も超えていないのでまだ大丈夫)

最後に

ハッカソン・ゲームジャム・アイディアソン・開発合宿への参加に興味がある皆さんは是非フォローしてください!!
よろしくお願いします。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MACBOOK PROでMAMPを利用したDATABASE作成でつまづいた話

備忘録として書き残すので大した内容ではありませんがもし同じように悩む人の役に立てばこれ幸い也。

勉強書「スラスラわかるPHP 志田仁美 著」

経緯

PHPの勉強でホームページ制作。コンテンツ内に掲示板を作成することにした。当方MACBOOK PRO でMAMPを使って環境構築し順調に設計していたがMySQLで作成したデータベースが読み込めない。ぜんぜん読み込めない。why?

スクリーンショット 2019-12-03 14.50.28.png

原因

phpの記入ミスやMySQLのデータベースに正しくログインできていなかったのでは?とうろたえて数日経過。
なんてことはない。
自身のPCにMySQLをインストールしてデータベースを使用していただけだった。
MAMPにはローカル開発環境を立ち上げるために必要なソフトウェア(Apache、MySQL、PHP)がパッケージ化されているんだから余計なことしなくてよかったのだ。

解決法

備忘録なので簡潔に

  1. MAMPを起動してターミナルを起動する
  2. ターミナルからMAMPの作業ディレクトリへ移動
$ cd /Applications/MAMP/Library/bin/

このコマンドで作業ディレクトリへ

3.MAMPの作業ディレクトリからMySQLへログイン

通常のMySQLへのログインと同じ

$./mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

4.データベースを作成する

こちらも通常どうりに使用する。
今回は簡易的な掲示板作るための最低限のデータベースとテーブルを作成した。

データベース作成

mysql> create database (データベース名);
Query OK, 1 row affected (0.07 sec)

データベース選択

mysql> use (データベース名)
Database changed

テーブル作成

mysql> CREATE TABLE bbs(
    ->   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   name VARCHAR(255) NOT NULL,
    ->   title VARCHAR(255),
    ->   body TEXT NOT NULL,
    ->   date DATETIME NOT NULL,
    ->   pass CHAR(4) NOT NULL
    -> )DEFAULT CHARACTER SET=utf8;

これで完成。
PHPは問題なかったため掲示板は問題なく動作しました。
同じ間違いは二度と犯さないようにしよう。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MySQLのスロークエリのログを出力する設定

前提環境

  • MySQL 8.0

スロークエリログを出力する設定

my.cnf
slow_query_log
long_query_time=1
log_queries_not_using_indexes
  • slow_query_log … スロークエリログ出力を有効にする
  • long_query_time … 指定した秒以上かかったクエリ(デフォルトは10)
  • log_queries_not_using_indexes … インデックスを利用していないクエリを出力する

反映確認

※hostname … サーバに設定されているホスト名

mysql > SHOW VARIABLES LIKE 'slow_query%';
+---------------------+------------------------------------+
| Variable_name       | Value                              |
+---------------------+------------------------------------+
| slow_query_log_file | /var/lib/mysql/[hostname]-slow.log |
+---------------------+------------------------------------+
1 row in set (0.01 sec)

スロークエリログ出力確認

※hostname … サーバに設定されているホスト名

cat /var/lib/mysql/[hostname]-slow.log
/usr/sbin/mysqld, Version: 8.0.16 (MySQL Community Server - GPL). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2019-12-01T17:09:57.232128Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.002923  Lock_time: 0.000386 Rows_sent: 2  Rows_examined: 575
SET timestamp=1575220197;
SELECT * FROM performance_schema.variables_info WHERE VARIABLE_NAME LIKE 'slow_query%';

Query_time … クエリの実行にかかった時間
Lock_time … ロックした時間
Rows_sent … クエリ実行後に返却した行の数
rows_examined … クエリ実行で読み込んだ行数

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

Re:ゼロから始めるコンテナ生活

グレンジ Advent Calendar 2019 5日目の記事を担当しますkitaji_ngzkと申します。
昨年に引き続き、同プロジェクトでPHPを扱うサーバーサイドエンジニアをやってます。

自分が属するプロジェクトには専任のインフラエンジニアがいないため、サーバーサイドエンジニアが兼任する形で担当しています。

ので、必然的にインフラ知識も俄然学ぶ必要があるので、今回個人的に「コンテナ」という技術に触れてみたいと思い、Dockerでローカル環境の構築からクラウドにあげて公開してみるところまで実践した軌跡をご紹介したいと思います。

ここから始めましょう!1から、、いいえゼロから!!

▼今回やってみたこと

Chapter.1 Docker公式のWordPressイメージを用いて、超速構築!
Chapter.2 GCPにMySQLサーバー作成
Chapter.3 Dockerイメージの作成とGCRにアップロード
Chapter.4 GKEのクラスタ作成
Chapter.5 クラスタにデプロイ

▼環境

macOS Mojave10.14.6
Docker for mac 2.1.0.5

Chapter.1 Docker公式のWordPressイメージを用いて、超速構築!

まず手始めにローカル環境でコンテナに触れてみます。
※前提としてDockerがインストールされていることから話を進めます。

公式のWordPress, MySQLのイメージをローカルにpullします。
↓WordPressイメージのタグ一覧
https://hub.docker.com/_/wordpress
pullする時、指定のタグを:つなぎで指定できますが、指定しなければlatestがpullされます。
Wordpressはlatestで問題ないので、以下の通り

$ docker pull wordpress

続けてMySQLもpullしますが、ここで気をつけないといけないのは、MySQLのlatestは8系で認証方法が変わっていて、WordPressの接続エラーという罠が待っているので、バージョンを指定して落としましょう。(8系でも認証方式を昔のものに戻せば、使えるっちゃあ使えます)
↓MySQLイメージのタグ一覧
https://hub.docker.com/_/mysql
8系以外の最新では現時点で5.7系があったのでそれにします。ので、以下の通り

$ docker pull mysql:5.7

さて、両方ちゃんと落とせたか確認します。

$ docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
wordpress           latest              e4bd752aeb0d        7 days ago          539MB
mysql               5.7                 cd3ed0dfff7e        5 weeks ago         437MB

こんな感じで表示されてれば成功。

ではこの二つのイメージをそれぞれ使って、それぞれのコンテナを作成していきます。
まずはMySQLコンテナから

$ docker run --name sample-mysql -e MYSQL_ROOT_PASSWORD=任意のパス -d mysql:5.7

各オプションについてはdocker run --helpとかで調べるか、「docker run オプション」とかで調べましょう。なお、sample-mysqlという部分は任意のコンテナ名です。
(また、ぶっちゃけいちいち先にpullしなくても、このrunコマンドで勝手に持ってきてくれるっちゃあくれます。)

では、無事にコンテナが起動しているか、確認しましょう。

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                 NAMES
8703def027e0        mysql:5.7           "docker-entrypoint.s…"   2 minutes ago       Up 2 minutes        3306/tcp, 33060/tcp   sample-mysql

このように表示されてれば成功です。

それではこのMySQLサーバーにつなげるWordPressコンテナを作成していきます。

$ docker run --name sample-wordpress --link sample-mysql:mysql -d -p 8080:80 wordpress
8703def027e0e9b6cce6409ee01544285836f8cddd4293331fa07b7e1930f56b
$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                  NAMES
6eca366c2b0c        wordpress           "docker-entrypoint.s…"   24 seconds ago      Up 23 seconds       0.0.0.0:8080->80/tcp   sample-wordpress
8703def027e0        mysql:5.7           "docker-entrypoint.s…"   6 minutes ago       Up 6 minutes        3306/tcp, 33060/tcp    sample-mysql

無事起動したようだ。それでは上記のコマンドにもある通り、ポート8080をあけたので、
http://localhost:8080
にアクセスしてみましょう
localhost_wordpress.png

このように表示されれば成功です。あとは画面通りに設定項目埋めていけば、WordPressのローカル環境はできあがりです。
ただし、今は/var/www/htmlをホストにマウントしていないので、いじりたいhtmlやphpファイルをエディタでいじいじできないという壁があります。(vim愛好家の方はdocker execでコンテナ内に入れば、用を足せると思います。ただ、このコンテナにはvimを入れてないので、vimをapt-getでインストールする必要があります。)

ではwp-admin画面まで表示されたら、一度以下のコマンドで、ホストの任意のディレクトリにDocker内のファイルをコピーしておきます。

$ docker cp sample-wordpress:/var/www/html ホストPCのディレクトリ

終わったら、ホストPCの任意のディレクトリにコピーされてるか確認しときましょう

$ ls -al コピー先のディレクトリ

でhtml以下のWordPressのファイルがもろもろあればOKです。

ここまできたら、DEDEATHDEATHDE~~~ATH!
20160922041023.png
作成したWordPressコンテナのみ削除して死に戻り!

$ docker stop sample-wordpress
sample-wordpress
$ docker rm sample-wordpress
sample-wordpress
$ docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                 NAMES
8703def027e0        mysql:5.7           "docker-entrypoint.s…"   33 minutes ago      Up 33 minutes       3306/tcp, 33060/tcp   sample-mysql

戻ってきたら、当然先ほどのlocalhostにアクセスしても、応答がないと思います。ので、WordPressコンテナを作り直します。今度は先ほどコピーしたディレクトリをマウントさせて作ります。

$ docker run --name sample-wordpress-2 -v コピー先の絶対パス:/var/www/html --link sample-mysql:mysql -d -p 8080:80 wordpress
72d97a8374d383e4f366e1adfba3684e6d83aaffef428e23c7849be9abd216be
$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                  NAMES
72d97a8374d3        wordpress           "docker-entrypoint.s…"   2 seconds ago       Up 1 second         0.0.0.0:8080->80/tcp   sample-wordpress-2
8703def027e0        mysql:5.7           "docker-entrypoint.s…"   39 minutes ago      Up 39 minutes       3306/tcp, 33060/tcp    sample-mysql

ここで、先ほど同様localhost:8080にアクセスしてみましょう。
localhost_sample_wordpress-2.png
最初に設定した内容そのままに復元できました。
ホストにマウントされているので、これでテーマをインストールしたりしても、ホスト側にも反映されるようになり、ホスト側をgitで管理したりすれば開発が複数人でできるようになると思います。

さて、超速でローカル環境を構築できたものの、この構成をクラウド上でも実現したいと思います。ただ、ローカルではMySQLコンテナを作りましたが、クラウド上ではDBはスケールさせないのでVMインスタンスで十分だと思います。(データの永続化という方法もありますが)
ということで、クラウド(今回はGCP)上にMySQLサーバー作っていきます。

Chapter.2 GCPにMySQLサーバー作成

チャプターにはしてみたものの、あんまりコンテナ関係ないのでさらっといきます
①MySQLコンテナに入ってまるごとdumpfile作成

$ docker exec -it sample-mysql /bin/bash
root@~~# mysqldump -uroot -p -A > sample-wordpress.dump
root@~~# exit

②ホストにコピーしてくる

$ docker cp sample-mysql:/sample-wordpress.dump ./

↑コピー先は./入れてますが、任意のディレクトリで.(ぶっちゃけマウントしてるディレクトリにもともと格納すればホストに落ちてくるという)

③のちのちのためにVPCネットワーク作成
④VMインスタンス作成→MySQL5.7入れる(←ここで入れるMySQLのバージョンはローカルで使ってたやつと一緒にするように)
⑦VMインスタンスに②のdumpファイル転送&ぶっこみ

これでDockerで作られてるMySQLコンテナの中と同じ構成のサーバーができたはず。
ここまでで、もちろんホストPCからもDBサーバーのMySQLにアクセスできるはずなので、確かめてみます。

$ mysql -h外部IPアドレス -uroot -p
Enter password:ルートパスワード
ERROR 2003 (HY000): Can't connect to MySQL server on '外部IPアドレス' (61)

!?むむ!つながらぬ。。なぜ。。
お試しでローカルからアクセスしてみようと思い、一時的にIPとポート全開放(超非推奨)してましたが、ssh用のポートはtelnetコマンドで確認してみても問題なかったので、おそらくポートは開いているだろう。
また、サーバー自体に繋がってなかったら、エラーがUnkown hostとかになりそうな気もするので、サーバー内のMySQLまでは到達していそう。
となると、MySQLの設定周りが怪しいかもってことで、調べていると以下の情報に行き着きました。
https://gist.github.com/koudaiii/10696132

念の為リンク切れちゃった時用に一部以下抜粋しておきますが、自分が引っかかったのはここでした。

2.my.cnfのbind-address設定

my.confのbind-addressの設定を確認してみる。

$ vi /etc/mysql/my.cnf

bind-address = 127.0.0.1
bind-address = (接続したいマシンのIPアドレス)
追加したい接続先のIPを書いた「bind-address」を追加していけばOK。

どのIPからも接続許可する場合はbind-addressをコメントアウトすればOK。

たしかにサーバー内のmy.confの設定内にlocalhostのみ許可のデフォ設定がありました。。(ここにたどり着くまでに自分は4,5回死に戻り(=削除して再作成したり)してます笑)
ので、教えの通りにコメントアウトし、再度ホストPCからtelnetしてみると、接続ができたので、MySQLコマンドを再度叩いてみる。

$ mysql -h外部IPアドレス -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

:raised_hands::raised_hands::raised_hands::raised_hands:やったーーー!!:raised_hands::raised_hands::raised_hands::raised_hands:

ついにローカルからもアクセスできるようになりました。
WordPressコンテナの/var/www/html配下はホストにマウントしてあると思うので、wp-config.phpを自分の好きなエディタで編集してDB_HOSTの向き先を試しに変更してみます。以下の部分です。

/** MySQL hostname */
define( 'DB_HOST', 'mysql');

↓↓↓

/** MySQL hostname */
define( 'DB_HOST', '外部IPアドレス');

これで
http://localhost:8080
にアクセスしてみましょう。
localhost_sample_wordpress-2 2.png
少しローディングが長く感じますが、これでローカルのWordPressコンテナからクラウド上のMySQLサーバーに接続することができました!(無事MySQLをGCPに作れたようだ←これはこれで個人的にできたの嬉しい笑)

ただ、注意を一つですが、この時点でMySQLのユーザはこうなってるはずです。

mysql> SELECT user, host FROM mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

root権限がどんなIPからでも使えちゃう怖い状況なので、しっかり削除
そして、WordPressからアクセスする用のユーザを作成して、WordPressで使用するDBのみ許可する権限を与えておきましょう!→最終的に同じVPNネットワークにする予定なので、プライベートネットワークのみに制限
WordPressで使用するユーザを作成したら、wp-config.phpに反映することを忘れずに!(・・・ていうか、これ最初からやっとけばよかったんじゃね?|ω・`))
さて、もし同じようにローカルから接続してみた場合は、忘れずにIPとポートを閉めて、wp-config.phpの設定もローカルのmysqlコンテナ向きに戻しておきましょう。

Chapter.3 Dockerイメージの作成とGCRにアップロード

今できているWordPressコンテナをイメージ化する方法としては、コンテナを停止して、docker commitのコマンドを使用するか、DockerFileを作成してイメージをビルドするかなどがあります。

実際にGKEで動作させる際、WordPressのDB_HOSTは先ほど作成したMySQLサーバーを向いて欲しかったりします。となるとローカルコンテナをそっくりそのままイメージ化しても動かないでしょう。
ので、これまでやってきた流れをDockerfileで再現する形でイメージをビルドしてGCRにあげてみたいと思います。

Dockerfileは以下のように作成しました。一番最後に「クラウド用の設定でwp-config.phpを上書き」とありますが、最終的にGKEとMySQLサーバーが同VPCネットワーク内であればプライベートIPで接続できるはずなので、DB_HOSTの向き先をそのように変えてあるファイルを取り込んでいます。

FROM wordpress:latest
LABEL maintainer="kitaji_ngzk"

# vimインストール
RUN ["apt-get", "update"]
RUN ["apt-get", "install", "-y", "vim"]

# ホストのソースコードを取り込む
WORKDIR ローカルのWordPressコンテナをマウントした直上ディレクトリ
ADD /html/ /var/www/html/

# クラウド用の設定でwp-config.phpを上書き
ADD /gcp/wp-config.php /var/www/html/wp-config.php

イメージをビルドします。

$ docker build -t sample-wordpress:1.0.0 .
Sending build context to Docker daemon  52.51MB
Step 1/8 : FROM wordpress:latest
 ---> e4bd752aeb0d
Step 2/8 : LABEL maintainer="kitaji_ngzk"
 ---> d34337359340
Step 3/8 : RUN ["apt-get", "update"]
 ---> 0e10ffbdafc1
Step 4/8 : RUN ["apt-get", "install", "-y", "vim", "net-tools"]
 ---> 75c64ff2bacc
Step 5/8 : WORKDIR ローカルのWordPressコンテナをマウントした直上ディレクトリ
 ---> 5d913bf121e2
Step 6/8 : ADD /html/ /var/www/html/
 ---> b963ce7d1331
Step 7/8 : WORKDIR /var/www/html
 ---> 9d9e0ebbce17
Step 8/8 : EXPOSE 80
 ---> d7e54c624845
Successfully built d7e54c624845
Successfully tagged sample-wordpress:1.0.0

成功したようなので、確認します。

$ docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
sample-wordpress    1.0.0               5e28a38a1758        36 minutes ago      591MB
wordpress           latest              e4bd752aeb0d        2 weeks ago         539MB
mysql               5.7                 cd3ed0dfff7e        6 weeks ago         437MB

これでイメージ化できました。では、一応このイメージを使ってコンテナを起動できるかローカルで試してみて(docker run)、同じようにWordPress管理画面が表示されればOKです。
(実際プロジェクトとして開発する時は、ローカルのソースコードじゃなくて、ちゃんとgit管理されたmasterブランチのソースコードをpullしてきて、それをADDするべきかなと思います)

次に、GCRに上げるには、イメージにレジストリ名をタグ付けする必要があるので、以下の通りタグ付けします。

$ docker tag sample-wordpress:v1.0.0 gcr.io/kitaji_sample/sample-wordpress:v1.0.0
$ docker images
REPOSITORY                              TAG                 IMAGE ID            CREATED             SIZE
sample-wordpress                        v1.0.0              0465bcd14635        11 hours ago        539MB
gcr.io/kitaji_sample/sample-wordpress   v1.0.0              0465bcd14635        11 hours ago        539MB
wordpress                               latest              e4bd752aeb0d        2 weeks ago         539MB
mysql                                   5.7                 cd3ed0dfff7e        6 weeks ago         437MB

kitaji_sampleとなっているところはGCPのプロジェクト名です。続けて、GCRにイメージをpushします。

$ docker push gcr.io/kitaji_sample/sample-wordpress:v1.0.0
The push refers to repository [gcr.io/kitaji_sample/sample-wordpress]
d79a1d170eaf: Pushed 
d5b61126e77c: Pushed 
182f6bf618c4: Pushed 
02e5b86b9d8d: Pushed 
f190d03dd9cf: Pushed 
31edc4603d49: Pushed 
a6c798e344c1: Pushed 
7ad4f8a271af: Pushed 
8b9e7bae16d7: Pushed 
4c31d76c6594: Pushed 
983090088b87: Pushed 
53f8a4a17b10: Pushed 
01af4509e166: Pushed 
37a065eea6b3: Pushed 
8067bb2f50e2: Pushed 
ba31a1dbfcfb: Pushed 
3e84f33ac944: Pushed 
9f9d470ac131: Pushed 
86569e4ec54b: Pushed 
12fe3564ccac: Pushed 
4e9b2aba858c: Pushed 
b67d19e65ef6: Pushed 
v1.0.0: digest: sha256:a6326341ec7a3c2596125ea424460826e504265924e66e4b34f24bc762a82dce size: 4915

GCRのコンソールで確認してみると、
gcr.png
お、あがってるあがってる(ちなみにこのリポジトリ内を見ると、なんと脆弱性もチェックしてくれてて、350個もありました笑 うち重大は2個w 恒久的に外部に公開する前には重大:中以上は解決しておかないとですね汗)
インスタンスもそうですが、GCRにあげたイメージも課金対象なので、必要ない時は削除をおすすめします。詳しくはGCRの公式ドキュメント参照。

Chapter.4 GKEのクラスタ作成

先ほどのGCRへのpushもそうですが、これ以降のやり方はGCPの公式ドキュメントに詳細に書かれているので、それを参考にやってみた方がいいかと思います。(特にオプション周りとか)
自分は実際に実行したコマンドを並べて紹介していきたいと思います。完全にお試しクラスタなので、オプションで不十分だったりするところあるんですが、ご容赦ください。(autoscaleとか設定してなかったり、stackdriverの設定だったり..←後で消すのめんどくさいと思った)

$ gcloud container clusters create sample-wordpress-web --region asia-east1 --machine-type g1-small --subnetwork kitaji-sample-subnet --disk-size 10GB --network kitaji-sample-network --max-nodes-per-pool 100 --num-nodes 1
WARNING: Currently VPC-native is not the default mode during cluster creation. In the future, this will become the default mode and can be disabled using `--no-enable-ip-alias` flag. Use `--[no-]enable-ip-alias` flag to suppress this warning.
WARNING: Newly created clusters and node-pools will have node auto-upgrade enabled by default. This can be disabled using the `--no-enable-autoupgrade` flag.
WARNING: Starting in 1.12, default node pools in new clusters will have their legacy Compute Engine instance metadata endpoints disabled by default. To create a cluster with legacy instance metadata endpoints disabled in the default node pool, run `clusters create` with the flag `--metadata disable-legacy-endpoints=true`.
WARNING: Your Pod address range (`--cluster-ipv4-cidr`) can accommodate at most 1008 node(s). 
This will enable the autorepair feature for nodes. Please see https://cloud.google.com/kubernetes-engine/docs/node-auto-repair for more information on node autorepairs.
Creating cluster sample-wordpress-web in asia-east1... Cluster is being health-checked (master is healthy)...done.                                                                             
Created [https://container.googleapis.com/v1/projects/kitaji-sample/zones/asia-east1/clusters/sample-wordpress-web].
To inspect the contents of your cluster, go to: https://console.cloud.google.com/kubernetes/workload_/gcloud/asia-east1/sample-wordpress-web?project=kitaji-sample
kubeconfig entry generated for sample-wordpress-web.
NAME                  LOCATION    MASTER_VERSION  MASTER_IP       MACHINE_TYPE  NODE_VERSION    NUM_NODES  STATUS
sample-wordpress-web  asia-east1  1.13.11-gke.14  35.201.158.151  g1-small      1.13.11-gke.14  3          RUNNING

クラスタの作成は少し時間がかかります。
今回WARNINGたくさんあるけど、取り急ぎなんかできたっぽい。
ほんとならyamlとか作って体系的に書けるんだろうけど、いったんその辺置いといて、GKEコンソールで確認(gcloud container clusters describe クラスタ名とかでも確認できる)
gke.png

きっとあとはIngress(→LB)、Service、Deploymentの設定を入れたげれば、公開されるはずっ。

Chapter.5 クラスタにデプロイ

いよいよ最後デプロイと公開工程です。

まず先ほど作成したクラスタをkubectlコマンドで使えるようにする

$ gcloud container clusters get-credentials sample-wordpress-web --region asia-east1 --project kitaji-sample
Fetching cluster endpoint and auth data.
kubeconfig entry generated for sample-wordpress-web.

これで先ほどのクラスタに対しkubectlコマンドが使えるようになりました。kubectl get nodes とか実行したら、ぶら下がってるnodeが出てくるはず。

さて、それでは公開に向けて、Ingress、Service、Deploymentの設定を作成していきます。
設定はそれぞれyamlファイルをローカルに作成して、kubectl applyのコマンドでデプロイするだけです。(なんて簡単!)

ただ、Ingressを作るたびにIPアドレスが変わられるとインフラ開発工程上厄介になり得るので、先に静的IPアドレスを予約しておきます。

$ gcloud compute addresses create sample-wordpress-web --global
Created [https://www.googleapis.com/compute/v1/projects/kitaji-sample/global/addresses/sample-wordpress-web].
$ gcloud compute addresses list --global
NAME                  ADDRESS/RANGE  TYPE      PURPOSE  NETWORK  REGION  SUBNET  STATUS
sample-wordpress-web  XX.XXX.XX.XX   EXTERNAL                                    RESERVED

予約が完了したので、それでは各yamlファイルを作成します。それぞれ以下の通り作成しました。

sample-wordpress_deployment.yaml
apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: sample-wordpress-deploy
  labels:
    app: sample-wordpress
spec:
  replicas: 1
  selector:
    matchLabels:
      app: sample-wordpress
  template:
    metadata:
      labels:
        app: sample-wordpress
    spec:
      containers:
        - name: sample-wordpress-web
          image: gcr.io/kitaji-sample/sample-wordpress:1.3.0
          imagePullPolicy: Always
          ports:
            - containerPort: 80
              protocol: TCP
          readinessProbe:
            httpGet:
              path: /health.html
              port: 80
            initialDelaySeconds: 5
            periodSeconds: 5
sample-wordpress_service.yaml
apiVersion: v1
kind: Service
metadata:
  name: sample-wordpress-service
  labels:
    app: sample-wordpress
spec:
  selector:
    app: sample-wordpress
  type: NodePort
  ports:
    - port: 8080
      targetPort: 80
      protocol: TCP
sample-wordepress_ingress.yaml
apiVersion: extensions/v1beta1
kind: Ingress
metadata:
  name: sample-wordpress-ingress
  annotations:
    kubernetes.io/ingress.global-static-ip-name: sample-wordpress-web
spec:
  backend:
    serviceName: sample-wordpress-service
    servicePort: 8080

あとはkubectl applyでデプロイするだけ!(たぶん!)

$ kubectl apply -f sample-wordpress_deployment.yml
deployment.apps/sample-wordpress-deploy  created
$ kubectl apply -f sample-wordpress_service.yml
service/sample-wordpress-service created
$ kubectl apply -f sample-wordpress_ingress.yml
ingress.extensions/sample-wordpress-ingress created

あっさり作られました。一応作られてるか確認します

$ kubectl get deployment
NAME                      READY   UP-TO-DATE   AVAILABLE   AGE
sample-wordpress-deploy   1/1     1            1           10h
$ kubectl get service
NAME                       TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE
kubernetes                 ClusterIP   XX.XX.XXX.X     <none>        443/TCP          11h
sample-wordpress-service   NodePort    XX.XX.XXX.XXX   <none>        8080:30585/TCP   10h
$ kubectl get ingress
NAME                       HOSTS   ADDRESS        PORTS   AGE
sample-wordpress-ingress   *                      80      10h

ん?Ingressのアドレスが空欄だ・・・。さっき静的IPを予約したはず。
コンソールでもみてみると、まだ、ステータスがCreating Ingressでした。なるほどingressは時間がかかるんかぁ。
しばし待ちます。

:hourglass_flowing_sand::hourglass_flowing_sand::hourglass_flowing_sand::hourglass_flowing_sand::hourglass_flowing_sand::hourglass_flowing_sand::hourglass_flowing_sand::hourglass_flowing_sand::hourglass_flowing_sand::hourglass_flowing_sand::hourglass_flowing_sand::hourglass_flowing_sand:

再度ingressだけ確認してみます

$ kubectl get ingress
NAME                       HOSTS   ADDRESS        PORTS   AGE
sample-wordpress-ingress   *       XX.XXX.XX.XX   80      12m

お、無事に作られてそう。と思いきや、コンソールで見ると、ステータスにWARNアイコン。
nothealth.png
なにやらバックエンドサービスでヘルスチェックが通っていない的なことを言っている。
実際、割り当てられた外部IPアドレスにアクセスしてみると、502。

ここからかなり時間を費やしました。。
いろいろKubernetesのヘルスチェックを調べていると、ヘルスチェック(今回指定しているのはreadinessProbe)で指定したパスに対して、200が返ってこないとダメらしい。そしてStackdriverのContainerLogを見ると、返しているのは301。。なるほど。

wordpressのあれかな、.htaccessによるリダイレクトがいけないのかなってことで、この際、health.htmlなるファイル(中身はHello.と書いただけ)を/var/www/htmlに置いてみて、readinessProbeで指定するパスを/health.htmlに変えてもう一度死に戻り、全て再applyしてみます。

すると、
ingress_ok.png
ついにOK〜〜〜!!(←泣きそう)

もしかして、これで、、アクセスできるんちゃうか!?

・・・ドキドキ・・・ドキドキ・・・・

404.png
ぐぬぬぬぬ!!!!

ただ、502ではなくなったので、進撃はしているようだ。
今度はこの404を倒す・・・このくらいの絶望で俺が止まると思うなよ!!

とは言ったものの、LBからポッドまで通信はきていそうだし、/var/www/html内にも各ファイルが配置されているし、所有者とグループがwww-dataではなく、rootになっていたので直してみたし、、と悩みまくって禿げそうだったので、先輩エンジニアの方々に相談に乗ってみてもらったところ、ついに、原因が特定できましたっ!

なんとWordPressのこの設定部分でした

mysql> SELECT * FROM wp_options WHERE option_name IN ('home','siteurl');
+-----------+-------------+------------------------+----------+
| option_id | option_name | option_value           | autoload |
+-----------+-------------+------------------------+----------+
|         2 | home        | http://localhost:8080/ | yes      |
|         1 | siteurl     | http://localhost:8080/ | yes      |
+-----------+-------------+------------------------+----------+
2 rows in set (0.00 sec)

なんというチョンボ・・・orz
管理画面でいうと「設定>一般」の赤枠部分です。
address.png
ここを

mysql> UPDATE wp_options SET option_value = 'http://Ingressに割り当てられた外部IPアドレス/' where option_name IN ('home');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE wp_options SET option_value = 'http://Ingressに割り当てられた外部IPアドレス/' where option_name IN ('siteurl');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

に変更します。外部IPアドレス部分はドメインを取得していたらドメインでもOKなやつ

そしてついにっ、、、
スクリーンショット 2019-12-03 0.32.44.png

ごちそうさまです♪

最後に

やってたら意外と盛りだくさんで時間が最後たらなくなって急ぎ足になってしまいました。。
本当はこの後、Spinnakerチャレンジとかしてみたかった。

これ書くまでにいろいろグレンジの先輩エンジニアの方々にみなさん年末の鬼多忙の中、片手間でもアドバイスをいただいて、なんとか最後までできましたm(._.)m
僕の先輩方はみんな超鬼がかってました!!

グレンジには「困っていたら助けるのは当たり前」なたっち・みーさん的エンジニアがたくさんいるふれんどりぃな会社です^^
これを最後まで読んでくれた心優しきあなたのJOINをいつでもお待ちしています!!笑
https://www.grenge.co.jp/recruit/

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

【Rails】bundle install エラー「Errno::EACCES: Permission denied @ rb_sysopen -」の権限視点からの対応策 (mysql2)

備忘録です。
自身の記事ですが【Rails環境構築】MySQL2が原因で「bundle install」失敗した時の対処法のようなPATHの変更でも解決できない状況に陥ったため。

環境

OS       : Mac OS Catalina 10.15.1
Ruby      : v 2.6.3p62
Rails      : v 6.0.1
Homebrew  : v 2.1.16
Bundle    : v 1.17.2
MySQL    : v 8.0.18 for osx10.15 on x86_64 (Homebrew)

その他
※ username は個人の作業ユーザ名になります。

$ whoami
=> username

エラー

$ bundle install
The dependency tzinfo-data (>= 0) will be unused by any of the platforms Bundler is installingfor. Bundler is installing for ruby but the dependency is only for x86-mingw32, x86-mswin32, x64-mingw32, java. To add those platforms to the bundle, run `bundle lock --add-platform x86-mingw32 x86-mswin32 x64-mingw32 java`.
( ↑ bashの設定によって色が変わってたりするものの本稿でのエラーには関係なし)

省略

Installing mysql2 0.5.2 with native extensions
Errno::EACCES: Permission denied @ rb_sysopen -
/Users/username/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/CHANGELOG.md
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.

In Gemfile:
  mysql2

対応

1. 上記エラーに記載されたディレクトリへ移動

cd /Users/username/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/extensions/x86_64-darwin-19/2.6.0/mysql2-0.5.2/

2. 該当ディレクトリでファイルの詳細を表示(権限確認)

$ ls -la
total 64
drwxr-xr-x  10 root   staff    320 12  2 22:08 .
drwxr-xr-x  69 username  staff   2208 12  2 22:08 ..
-rw-r--r--   1 root   staff     87 12  2 22:08 CHANGELOG.md
-rw-r--r--   1 root   staff   1078 12  2 22:08 LICENSE
-rw-r--r--   1 root   staff  24534 12  2 22:08 README.md
drwxr-xr-x   4 root   staff    128 12  2 22:08 examples
drwxr-xr-x   3 root   staff     96 12  2 22:08 ext
drwxr-xr-x   4 root   staff    128 12  2 22:08 lib
drwxr-xr-x  10 root   staff    320 12  2 22:08 spec
drwxr-xr-x   6 root   staff    192 12  2 22:08 support

3. ディレクトリやファイルの権限を 「root」 → 「username」 に変更&確認

$ sudo chown -R username .
$ ls -la
total 64
drwxr-xr-x  10 username  staff    320 12  2 22:08 .
drwxr-xr-x  69 username  staff   2208 12  2 22:08 ..
-rw-r--r--   1 username  staff     87 12  2 22:08 CHANGELOG.md
-rw-r--r--   1 username  staff   1078 12  2 22:08 LICENSE
-rw-r--r--   1 username  staff  24534 12  2 22:08 README.md
drwxr-xr-x   4 username  staff    128 12  2 22:08 examples
drwxr-xr-x   3 username  staff     96 12  2 22:08 ext
drwxr-xr-x   4 username  staff    128 12  2 22:08 lib
drwxr-xr-x  10 username  staff    320 12  2 22:08 spec
drwxr-xr-x   6 username  staff    192 12  2 22:08 support

変更されていればok

4. 「bundle install」(成功 or 再度エラーの対応)

恐らくここで完了する方もいれば、筆者のようにまたエラー発生する方もいるかと思います。
「うわ、また同じエラーだ😑」と感じるかもしれませんが、
エラー先の場所をよくよく見ると、gem内(1回目)とextensions内(2回目)で異なるので再度ディレクトリへ移動して権限変更を繰り返します。

$ bundle install

省略

Errno::EACCES: Permission denied @ rb_sysopen -
/Users/username/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/extensions/x86_64-darwin-19/2.6.0/mysql2-0.5.2/gem_make.out
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.

In Gemfile:
  mysql2
$ cd /Users/username/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/extensions/x86_64-darwin-19/2.6.0/mysql2-0.5.2/
$ ls -la
total 128
drwxr-xr-x   4 root   staff    128 12  2 22:08 .
drwxr-xr-x  12 username  staff    384 12  2 22:08 ..
-rw-r--r--   1 root   staff   1999 12  2 22:08 gem_make.out
-rw-r--r--   1 root   staff  58721 12  2 22:08 mkmf.log
$ sudo chown -R username .
$ ls -la
total 128
drwxr-xr-x   4 username  staff    128 12  2 22:08 .
drwxr-xr-x  12 username  staff    384 12  2 22:08 ..
-rw-r--r--   1 username  staff   1999 12  2 22:08 gem_make.out
-rw-r--r--   1 username  staff  58721 12  2 22:08 mkmf.log
$ bundle install

省略

Bundle complete! 17 Gemfile dependencies, 75 gems now installed.
Use `bundle info [gemname]` to see where a bundled gem is installed.

これにて終了です!

その他

誤解や「このやり方のが断然いい」などあれば、ご指摘頂けると幸いです m(_ _)m

参考記事

rails newでErrno::EACCES: Permission denied @ dir_s_mkdir -の対処法 - Qiita
Errno::EACCES: Permission denied @ dir_s_mkdir が出たので対処した - mimikunの技術メモ
lsコマンドの使い方と覚えたい15のオプション【Linuxコマンド集】

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

【Rails】 bundle install エラー「Errno::EACCES: Permission denied @ rb_sysopen -」の権限視点からの対応策 (mysql2)

備忘録です。
自身の記事ですが【Rails環境構築】MySQL2が原因で「bundle install」失敗した時の対処法のようなPATHの変更でも解決できない状況に陥ったため。

環境

OS       : Mac OS Catalina 10.15.1
Ruby      : v 2.6.3p62
Rails      : v 6.0.1
Homebrew  : v 2.1.16
Bundle    : v 1.17.2
MySQL    : v 8.0.18 for osx10.15 on x86_64 (Homebrew)

その他
※ username は個人の作業ユーザ名になります。

$ whoami
=> username

エラー

$ bundle install
The dependency tzinfo-data (>= 0) will be unused by any of the platforms Bundler is installingfor. Bundler is installing for ruby but the dependency is only for x86-mingw32, x86-mswin32, x64-mingw32, java. To add those platforms to the bundle, run `bundle lock --add-platform x86-mingw32 x86-mswin32 x64-mingw32 java`.
( ↑ bashの設定によって色が変わってたりするものの本稿でのエラーには関係なし)

省略

Installing mysql2 0.5.2 with native extensions
Errno::EACCES: Permission denied @ rb_sysopen -
/Users/username/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/CHANGELOG.md
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.

In Gemfile:
  mysql2

対応

1. 上記エラーに記載されたディレクトリへ移動

cd /Users/username/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/extensions/x86_64-darwin-19/2.6.0/mysql2-0.5.2/

2. 該当ディレクトリでファイルの詳細を表示(権限確認)

$ ls -la
total 64
drwxr-xr-x  10 root   staff    320 12  2 22:08 .
drwxr-xr-x  69 username  staff   2208 12  2 22:08 ..
-rw-r--r--   1 root   staff     87 12  2 22:08 CHANGELOG.md
-rw-r--r--   1 root   staff   1078 12  2 22:08 LICENSE
-rw-r--r--   1 root   staff  24534 12  2 22:08 README.md
drwxr-xr-x   4 root   staff    128 12  2 22:08 examples
drwxr-xr-x   3 root   staff     96 12  2 22:08 ext
drwxr-xr-x   4 root   staff    128 12  2 22:08 lib
drwxr-xr-x  10 root   staff    320 12  2 22:08 spec
drwxr-xr-x   6 root   staff    192 12  2 22:08 support

3. ディレクトリやファイルの権限を 「root」 → 「username」 に変更&確認

$ sudo chown -R username .
$ ls -la
total 64
drwxr-xr-x  10 username  staff    320 12  2 22:08 .
drwxr-xr-x  69 username  staff   2208 12  2 22:08 ..
-rw-r--r--   1 username  staff     87 12  2 22:08 CHANGELOG.md
-rw-r--r--   1 username  staff   1078 12  2 22:08 LICENSE
-rw-r--r--   1 username  staff  24534 12  2 22:08 README.md
drwxr-xr-x   4 username  staff    128 12  2 22:08 examples
drwxr-xr-x   3 username  staff     96 12  2 22:08 ext
drwxr-xr-x   4 username  staff    128 12  2 22:08 lib
drwxr-xr-x  10 username  staff    320 12  2 22:08 spec
drwxr-xr-x   6 username  staff    192 12  2 22:08 support

変更されていればok

4. 「bundle install」(成功 or 再度エラーの対応)

恐らくここで完了する方もいれば、筆者のようにまたエラー発生する方もいるかと思います。
「うわ、また同じエラーだ😑」と感じるかもしれませんが、
エラー先の場所をよくよく見ると、gem内(1回目)とextensions内(2回目)で異なるので再度ディレクトリへ移動して権限変更を繰り返します。

$ bundle install

省略

Errno::EACCES: Permission denied @ rb_sysopen -
/Users/username/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/extensions/x86_64-darwin-19/2.6.0/mysql2-0.5.2/gem_make.out
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.

In Gemfile:
  mysql2
$ cd /Users/username/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/extensions/x86_64-darwin-19/2.6.0/mysql2-0.5.2/
$ ls -la
total 128
drwxr-xr-x   4 root   staff    128 12  2 22:08 .
drwxr-xr-x  12 username  staff    384 12  2 22:08 ..
-rw-r--r--   1 root   staff   1999 12  2 22:08 gem_make.out
-rw-r--r--   1 root   staff  58721 12  2 22:08 mkmf.log
$ sudo chown -R username .
$ ls -la
total 128
drwxr-xr-x   4 username  staff    128 12  2 22:08 .
drwxr-xr-x  12 username  staff    384 12  2 22:08 ..
-rw-r--r--   1 username  staff   1999 12  2 22:08 gem_make.out
-rw-r--r--   1 username  staff  58721 12  2 22:08 mkmf.log
$ bundle install

省略

Bundle complete! 17 Gemfile dependencies, 75 gems now installed.
Use `bundle info [gemname]` to see where a bundled gem is installed.

これにて終了です!

その他

誤解や「このやり方のが断然いい」などあれば、ご指摘頂けると幸いです m(_ _)m

参考記事

rails newでErrno::EACCES: Permission denied @ dir_s_mkdir -の対処法 - Qiita
Errno::EACCES: Permission denied @ dir_s_mkdir が出たので対処した - mimikunの技術メモ
lsコマンドの使い方と覚えたい15のオプション【Linuxコマンド集】

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

あなたはMySQLの挙動を本当に理解していますか?

はじめに

この記事は、CyberAgent 20新卒 エンジニア Advent Calendar 2019の3日目の記事です。

今回の記事は今年の夏に内定者バイトを行った際に、
僕「sqlでORDER BYをつけない場合の並び順ってprimary key順じゃないんですか?」
トレーナーさん「それは実行計画に依存するかな」
僕「?」
となったので、自分なりに調べたことをまとめたものになります。

内定者バイトの記事はまた後日書きます(遅くなっており本当に申し訳ございません。)。
ちなみに内定者バイトではこういうことをしていました。
https://twitter.com/ue_knnk/status/1192731466070806530

実行計画とは

SQL文はざっくりと以下の図のように処理されます。

image.png

つまり、実行計画とは統計情報を元にオプティマイザが、どのようにデータアクセスを行いSQLを実行するかをまとめたものになります。

実験

それでは本題のORDER BYをつけない場合の並び順はprimary key順なのかについて実験していきます。

MySQLのバージョンは8.0.18です。
また、ストレージエンジンはバイト先に合わせ、InnoDBを使います。

まず、日付をprimary keyとして降順に初期データを投入します。
イメージとしては動画配信サイトのコメントのテーブルです。
image.png

InnoDBのデータ構造はクラスタードインデックスなのでprimary keyを持つテーブルの場合データはprimary keyのキー値でソート済みになっています。
なので、ORDER BYをつけなくてもprimary key順になっています。
image.png

実行計画も見てみるとインデックスを使っていないことが分かります。
image.png

次にインデックスを使うようにsqlを実行すると、インデックスで指定したキー順が優先されて並んでいることが分かります。
image.png

実行計画を見ると、インデックスを使っていることが分かります。
image.png

このように実行計画でインデックスを使用している場合、インデックスのキーが優先されてソートされるため、primary key順で並べたい場合は以下のようにORDER BYをつけてsqlを実行する必要があります。
image.png

まとめ

今回は内定者バイト中に思った、考えてみたらそうだなあってなったことを調べて、まとめてみました。
挙動を理解していないとトラブルになりかねないので、知っておいて損はないのではと思います。
今回の例だと、動画配信サイトのコメントは基本的に投稿順で表示するため、primary key順でソートされていないと困りますよね。
読んでいただいてありがとうございます。

参考

https://blog.kamipo.net/entry/2016/12/24/234944
https://qiita.com/at_1016/items/33186523cfc20fb58675

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む