はじめに

昨日「MySQL8.0の間検索が遅い？」という記事(以降「前記事」とする)を書いたのですが、それに対するバグ報告を見つけたので検証してみました。
残念ながら本記事でも原因は特定できておらず解決には至っていませんが、本不具合をMySQL開発チームが確認済みであることは確認できました。(ステータスがVerifiedとなっているため)

https://qiita.com/miyauchi/items/f6621cc79aff98dd46e0

対象となるバグ報告

私が確認した事象に関すると思われる不具合報告は下記です。

https://bugs.mysql.com/bug.php?id=94655&thanks=2&notify=67

同不具合報告では、MBRWITHIN関数は速いのに、MBRINTERSECTS関数やMBROVERLAPS関数は遅い、という結果になったことが報告されています。

報告者であるCedric Tabin氏が使用しているのはMBRINTERSECTSやMBRWITHINなどの関数ですが、私が検証した限りではST_IntersectsやST_WithIn関数でも同様の結果となるようです。

検証結果

前記事により、ST_Intersects関数を使った実行結果の件数は391件で、実行時間は1〜2分であることは確認できています。

よって今回はST_WithIn関数とST_Overlaps関数で検証してみました。

結果としてはCedric Tabin氏の検証結果同様に、ST_WithIn関数では実行速度は速い(1秒かからない)にも関わらず、ST_Overlapsは遅い(1〜2分)となることがわかりました。

ST_WithIn関数を使った検証SQLは下記です。

SELECT
  ST_AsGeoJson((SHAPE))
FROM
  `e_stat.utf8` 
WHERE 
  ST_WithIn(
    SHAPE,
    ST_GeomFromText(
      'POLYGON((43.08256990265745 141.3162472988521,                                                                                                                                                                                     
                43.056831004971265 141.3162472988521,                                                                                                                                               
                43.056831004971265 141.35259659542191,                                                                                                                                               
                43.08256990265745 141.35259659542191,                                                                                                                                                
                43.08256990265745 141.3162472988521))',
      4326
    )
 );

実行結果は下記でした。

297 rows in set (0.13 sec)

また、ST_Opverlaps関数を使った検証用のSQLは下記です。

SELECT
  ST_AsGeoJson((SHAPE))
FROM
  `e_stat.utf8` 
WHERE 
  ST_Overlaps(
    SHAPE,
    ST_GeomFromText(
      'POLYGON((43.08256990265745 141.3162472988521,                                                                                                                                                                                     
                43.056831004971265 141.3162472988521,                                                                                                                                               
                43.056831004971265 141.35259659542191,                                                                                                                                               
                43.08256990265745 141.35259659542191,                                                                                                                                                
                43.08256990265745 141.3162472988521))',
      4326
    )
 );

実行結果は下記でした。

94 rows in set (1 min 47.24 sec)

ちなみに、ST_Intersects、ST_WithIn、ST_Overlaps関数のそれぞれの意味は下記の記事がわかりやすかったです。

https://qiita.com/boiledorange73/items/dc7c5e1ebe95625aee60

ST_Intersectsは、ST_WithInとST_OverlapsのORである気がするので(実際両関数の件数を足しあげるとST_Intersectsの件数と一致する)、せめてST_Overlapsが速ければ良かったのですが。

最後に

本不具合は2019年3月に登録され、最終更新が5月と比較的最近登録された不具合です。
Cedric Tabin氏の度重なる報告により、すでにMySQL開発チームの内部データベースに登録されているようですが、重要度はFeature Requestとあまり高くないように見えます。

ので、「Affects me」ボタンを押しておきました。

引き続き調査を続けたいと思います。

はじめに

昨日「MySQL8.0の空間検索が遅い？」という記事(以降「前記事」とする)を書いたのですが、それに対するバグ報告を見つけたので検証してみました。
残念ながら本記事でも原因は特定できておらず解決には至っていませんが、本不具合をMySQL開発チームが確認済みであることは確認できました。(ステータスがVerifiedとなっているため)

https://qiita.com/miyauchi/items/f6621cc79aff98dd46e0

対象となるバグ報告

私が確認した事象に関すると思われる不具合報告は下記です。

https://bugs.mysql.com/bug.php?id=94655&thanks=2&notify=67

同不具合報告では、MBRWITHIN関数は速いのに、MBRINTERSECTS関数やMBROVERLAPS関数は遅い、という結果になったことが報告されています。

報告者であるCedric Tabin氏が使用しているのはMBRINTERSECTSやMBRWITHINなどの関数ですが、私が検証した限りではST_IntersectsやST_WithIn関数でも同様の結果となるようです。

検証結果

前記事により、ST_Intersects関数を使った実行結果の件数は391件で、実行時間は1〜2分であることは確認できています。

よって今回はST_WithIn関数とST_Overlaps関数で検証してみました。

結果としてはCedric Tabin氏の検証結果同様に、ST_WithIn関数では実行速度は速い(1秒かからない)にも関わらず、ST_Overlapsは遅い(1〜2分)となることがわかりました。

ST_WithIn関数を使った検証SQLは下記です。

SELECT
  ST_AsGeoJson((SHAPE))
FROM
  `e_stat.utf8` 
WHERE 
  ST_WithIn(
    SHAPE,
    ST_GeomFromText(
      'POLYGON((43.08256990265745 141.3162472988521,                                                                                                                                                                                     
                43.056831004971265 141.3162472988521,                                                                                                                                               
                43.056831004971265 141.35259659542191,                                                                                                                                               
                43.08256990265745 141.35259659542191,                                                                                                                                                
                43.08256990265745 141.3162472988521))',
      4326
    )
 );

実行結果は下記でした。

297 rows in set (0.13 sec)

また、ST_Opverlaps関数を使った検証用のSQLは下記です。

SELECT
  ST_AsGeoJson((SHAPE))
FROM
  `e_stat.utf8` 
WHERE 
  ST_Overlaps(
    SHAPE,
    ST_GeomFromText(
      'POLYGON((43.08256990265745 141.3162472988521,                                                                                                                                                                                     
                43.056831004971265 141.3162472988521,                                                                                                                                               
                43.056831004971265 141.35259659542191,                                                                                                                                               
                43.08256990265745 141.35259659542191,                                                                                                                                                
                43.08256990265745 141.3162472988521))',
      4326
    )
 );

実行結果は下記でした。

94 rows in set (1 min 47.24 sec)

ちなみに、ST_Intersects、ST_WithIn、ST_Overlaps関数のそれぞれの意味は下記の記事がわかりやすかったです。

https://qiita.com/boiledorange73/items/dc7c5e1ebe95625aee60

ST_Intersectsは、ST_WithInとST_OverlapsのORである気がするので(実際両関数の件数を足しあげるとST_Intersectsの件数と一致する)、せめてST_Overlapsが速ければ良かったのですが。

最後に

本不具合は2019年3月に登録され、最終更新が5月と比較的最近登録された不具合です。
Cedric Tabin氏の度重なる報告により、すでにMySQL開発チームの内部データベースに登録されているようですが、重要度はFeature Requestとあまり高くないように見えます。

ので、「Affects me」ボタンを押しておきました。

引き続き調査を続けたいと思います。

はじめに

以前、家計簿管理システムの話をしたが、今回はそのシステムの DB 周りの権限を適切に設定した話である。

現在の設定

現在 API から DB に接続しているユーザーは midas であり、そのユーザーはデータベース midas の全権限を持っている。でも実際に必要としているのは、SELECT と INSERT のみである(あくまでも現時点では)。

また、DB から NAS にバックアップしているユーザーは root である。おかしい話だ。しかもバックアップをとるシェル内にパスワードを生で書いている。もうひどい。どっかの (3+4)pay のことが笑えない。

ちゃんと設定してあげる

今回準備するユーザーは下記の通り

• midas
  • データベース midas 配下の全権限と権限付与ができるユーザー
  • いわば データベース midas の root ユーザー
• midas_api
  • API から DB に接続するユーザー
  • データベース midas 配下の全てのテーブルに対しての SELECT と INSERT ができる
• midas_backup
  • バックアップ取得用のユーザー
  • データベース midas 配下の全てのテーブルに対して SELECT のみできる

設定したときのコマンド

• midas ユーザーにデータベース midas に GRANT OPTION 権限を付与

GRANT GRANT OPTION ON midas.* TO midas@localhost;

• midas_api ユーザーと midas_backup ユーザーの作成

CREATE USER midas_api@localhost IDENTIFIED BY 'hitonookanede';

CREATE USER midas_backup@localhost IDENTIFIED BY 'yakinikutabetai';

• midas_api ユーザーに SELECT と INSERT 権限を付与

GRANT SELECT, INSERT ON midas.* TO midas_api@localhost;

• midas_backup ユーザーに SELECT 権限を付与

GRANT SELECT ON midas.* TO midas_backup@localhost;

最後に

権限周りはちゃんとしておくことで事故が防げると思っているので、今回対応できてよかった。

DB ユーザー周りのコマンドをまとめてみた

DB のユーザーの作成、確認、権限の付与などいつも使うときに忘れてしまうのでまとめてみる。
※ 下記コマンドは root ユーザーで実行する必要があるものがある(てかほとんどがそうだと思う)
※ MySQL5.5 で実行確認している

ユーザーに関するもの

• 登録されているユーザーの確認

SELECT Host, User FROM mysql.user;

• ユーザーの作成

CREATE USER user@host IDENTIFIED BY 'password';

• ユーザーのパスワードの変更

SET PASSWORD FOR user@host=password('password');

※ password() 関数で暗号化している

• ユーザーの削除

DELETE FROM mysql.user WHERE user='user';

権限に関するもの

• ユーザーの権限の確認

SHOW GRANTS FOR user@host;

• ユーザーに全権限を付与

GRANT ALL PRIVILEGES ON *.* TO user@host;

• ユーザーから全権限を剥奪

REVOKE ALL, GRANT OPTION FROM user@host;

• ユーザーに特定の権限を付与

GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO user@host;

※ 付与した権限は、mysqldump するのに最低必要な権限

• ユーザーから一部の権限を剥奪

REVOKE SHOW VIEW, TRIGGER, LOCK TABLES ON *.* FROM user@host;

※ 特定の条件下だと mysqldump するには SELECT 権限だけよかったので他は剥奪というケースを想定

はじめに

この記事は初心者インフラエンジニアが備忘録的に書いたものです。

やりたいこと

1.MySQLに接続し、バックアップの取得
2.cronを使ってバックアップの自動化

動作環境

$ mysql -V
mysql  Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using  EditLine wrapper

バックアップ時の問題点

$ mysqldump -u USER_NAME -pPASSWORD -x -A > FILE_NAME

上記のコマンドだとパスワードが丸見えなのでセキュリティ上大問題です。
なので、

$ mysqldump -u USER_NAME -p
$ Enter password:PASSWORD

とするのが普通です。
しかし、自動バックアップとなると後者のコマンドは使用できないため必然的に前者のコマンドになってしまいます。

解決案

1.スクリプトを作成し、変数を使う

結果から言うと意味ないです。

backup.sh

#!/bin/bash
USER_NAME="root"
PASSWORD="hogehoge"

mysqldump -u $USER_NAME -p$PASSWORD -x -A > FILE_NAME

上記のようにスクリプト内に記述してもcronのログに

[Warning] Using a password on the command line interface can be insecure.

という警告文を吐かれます。
コマンドラインにパスワードを書くなと怒られます。

2.設定ファイルを読み込ませる

今回はこの方法で解決しました。
適当な場所に設定ファイルを作成します。

$ vi /etc/sql.cnf

隠しファイルにしたい場合はファイル名の先頭に「.(ドット)」をつけます。(てか、隠しファイルにしたほうが安全です。)
設定ファイルの中身は下記のようにします。

sql.cnf

[client]
user = root
password = hogehoge
host = localhost

パーミッションを最低限に設定します。

$ chmod 600 /etc/sql.cnf

以下のコマンドで設定ファイルを読み込むことができます。

$ mysqldump --defaults-extra-file=/etc/sql.cnf -x -A > FILE_NAME

これであとはcronに設定するだけです。

まとめ

・バックアップを自動化する際はパスワードをコマンドラインに入力しない
・設定ファイルを用いたやり方が分かりやすい
・他にいい方法があったら教えてください

PHP で MySQL を利用する時、通常は PHP 拡張モジュール(extension)を用いて接続します。

その時、 mysqli, mysql, mysqlnd, pdo_mysql, PDO, libmysqlclient といったわかりづらい名前の諸々があるので、ここでまとめておきます。

拡張モジュール

php.ini

extension=mbstring.so

こんな感じで設定を書くことで、ネイティブ C で記述された機能を PHP 上で利用出来るようになります。

PHP の実装よりも高速に動作するため、 MySQL との通信処理など処理速度がボトルネックになるようなものは拡張モジュールとして実装することで、処理速度の向上をしています。

拡張モジュール用ライブラリ

まず、 MySQL 拡張モジュールを実装する際にベースとされるライブラリがあります。

現状は mysqlnd と libmysqlclient のどちらかです。

PHP: どのライブラリを選ぶか - Manual

これらは、 PHP のコンパイル時に --with-pdo-mysql=mysqlnd などと指定して選ぶことが可能です。

mysqlnd

MySQL Native Driver が現在推奨されているネイティブライブラリで、デフォルトでこれが使われます。

libmysqlclient よりも様々な点で優れているため、こちらを選ぶことがほとんどのケースで最適です。

mysqlnd それ自体も PHP 拡張モジュールとして実装されているため、パフォーマンスも高いです。

libmysqlclient

mysqlnd の前に MySQL チーム(現 Oracle)が作成した、 PHP 依存のない純粋な C 言語のライブラリです。

利用時に MySQL をインストールする必要があったり、 MySQL ライセンスのため PHP に同梱出来なかったりと欠点がいくつかあります。

サポート自体は継続されていますが、一般に利用することはほぼないでしょう。

まとめ1

• mysqlnd と libmysqlclient は MySQL に接続するためのベースライブラリで選択可能
• 基本は mysqlnd を選択する

mysql

単純に mysql と言った時は、大体 PHP 2.0 からある古い拡張モジュールのことを指します。

mysql_connect 関数などを提供している extension ですが、 PHP 7 以降は利用出来ません。

なので、この拡張モジュールは新規開発では利用されないでしょう。そもそもトランザクションすらないので、実用性もありません。
超絶古い PHP 3 や 4 で開発されたプロダクトがまだ生きていて、保守しなければならない時に見かけることがあるかもしれない、くらいです。

mysqli

MySQL Improved 拡張モジュールです。公式ではこの拡張モジュールが推奨されています。

$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
}

$res = $mysqli->query("SELECT 'choices to please everybody.' AS _msg FROM DUAL");
$row = $res->fetch_assoc();
echo $row['_msg'];

こんな感じで手軽にクエリを投げることが出来る API を提供しています。

mysql と似た API も提供しているので、移行も比較的楽に可能です。

PDO

PHP の基本機能として、複数のデータベースアプリケーション(MySQL, sqlite, Oracle など...)間で出来るだけインターフェースを統一し、同じ PHP コードで別 DB にも投げれるようにした API 実装が PDO です。

これを使うことで、接続先のDB用のドライバを切り替えるだけで手軽に別の DB アプリケーションへ移行することが出来ます。
(実際に別の DB アプリケーションに移行することがあるのかはおいといて)

Laravel や CakePHP は PDO をラップして実装されているので、 mysqli 拡張モジュールを利用することは出来ません。
逆に、 Doctrine や CodeIgniter は mysqli を使うことも可能です。

PDO を利用するのであれば、 extension=mysqli として mysqli 拡張モジュールを有効にする必要はありません。

pdo_mysql

pdo_mysql 拡張モジュールは、 PDO API で MySQL に接続するためのドライバです。

PDO を使って MySQL に接続したい場合はこの拡張モジュールを有効にする必要があります。

他にも、 pdo_sqlite pdo_pgsql などのドライバ拡張モジュールがあり、それらを有効にすることで PDO を通して接続可能になります。

まとめ2

• mysql はもう使われない古い拡張モジュール
• mysqli は推奨されている拡張モジュール
• pdo_mysql は他の DB に繋ぐことも考えた抽象化層である PDO を一個挟んだ拡張モジュール

フレームワークや OR マッパーによってこの辺りは隠蔽されることがほとんどですが、実際に使われている拡張モジュールが何かを知ることは php.ini の設定や Dockerfile を書く時などで必要になってきます。これでもう間違わないで拡張モジュールを選べるようになったのではないでしょうか。

参考資料

• PHP: MySQL - Manual
• PHP: 用語説明 - Manual
• PHP: どの API を使うか - Manual
• PHP: どのライブラリを選ぶか - Manual
• PHP: Overview - Manual
• PHP: はじめに - Manual
• PHP: PDO - Manual

シェルスクリプトからMySQLの実行結果を、Slackへの通知やPrometheusのexporter用データの生成などに利用しています。

mysqlSelect.sh

#!/bin/bash
# シェルスクリプトからMySQL実行
# コマンド例）$ sh mysqlSelect.sh 127.0.0.1 3306 mysql_user mysql_password
# 第１引数：MySQLホスト
# 第２引数：MySQLポート
# 第３引数：MySQLユーザ
# 第４引数：MySQLパスワード

# SQL
sql="select id, name from db_name.table_name;"
echo "sql=$sql"

# SQL結果出力先
nowDateTime=(`date '+%Y%m%d%H%M%S'`)
sqlResult="./logs/mysqlSelect_${nowDateTime}.log"

# SQL実行
$(mysql -h $1 -P $2 -u $3 -p"$4" -N -e "${sql}" > $sqlResult)

# SQL実行結果
cat $sqlResult
sqlCount=`cat $sqlResult | wc -l`
echo "sqlCount=$sqlCount"

NULL値にもっと注意深くなろう

今回は値がないということに対する正しい挙動を学ぶことがゴールです。
プログラミング言語によって「値がない」ということをどう扱うかは様々です。

SQLではどのように扱われるでしょうか。
それはNULLです。

• SQLはNULLと(false、0、空の文字列）を区別します。
• NULLとの四則演算の結果は必ずNULLになります。決して0の扱いではありません。
  • coalesce関数で0に変換はできる
• NULLとの文字結合もまたNULLです。（DBの種類によっては違うそうです）

NULLを含む検索

SELECT * FROM Books WHERE category = 'comedy';

この検索を行ったとき、categoryがNULLの行は当然取得されません。

SELECT * FROM Books WHERE NOT (category = 'comedy');

この検索を行ったときもまた、categoryがNULLの行は取得されません。
ある値ではないということは等価演算になりますが
つまり、 NULL != 'comedy'がTRUE、もしくは
NULL == 'comedy'がFALSEを返せば良いのですが
NULLとの等価演算は決してTRUEにはなりません。
どこまでいってもNULLです。

MySQLには、NULLに対応した演算子があります。
<=>です。

SELECT * FROM Books WHERE category <=> NULL;

プリペアドステートメントにNULLを渡せるか

プリペアドステートメントで予約語であるNULLを値として渡すには
bindValue()を使うしかありません。第3引数に型を指定できるので
そこでPDO::PARAM_NULL'を指定してあげます。
excute`でそのまま渡すのでは対応ができないということです。

NULLから逃げるのも違う

そんなNULLはTRUEorFALSEで扱えるわけでもないので
また別の値を使った独自のルールを使うことで対応しようとすることもあるでしょう。
例えば数字でステータスを表す時に
1以上は有効、-1は存在しないというルールを決めたとします。
得点計算をするカラムがそのように扱われていたとしたらどうなるでしょう。
もし仮に、平均や偏差値などを算出する際に
単純に-1を計算に含めてしまうと、求めていない計算結果が出力されてしまいます。

ここでは、本来得点を意味するデータが入るところに
別論理の存在しているかどうかを定義する-1が混ざってしまっています。
これではまともなテーブル設計とは言えません。
アプリケーションレベルでも注意する必要のある厄介な仕様となってしまいます。

NULLを理解しよう

NULLは真偽のロジックである2値論理とは別の、3値論理として理解する必要があります。
4値論理とかもあるそうで。
参考

NULLと演算子

NULLを使った計算がどうなるか、まとめてみましょう。

下記の計算結果は全てNULLです。

NULL = 0
NULL = 123
NULL <> 123
NULL + 123 (四則演算全て)
NULL = NULL
NULL <> NULL

特に
NULL = NULLはこういう論理です。
不明な値と不明な値を比較しても、等しいかどうかはわからない
NULL <> NULLは
不明な値と不明な値を比較しても、等しくないかどうかはわからない
ということになります。

NULLと論理式

NULL AND TRUEは、NULLとなります。
NULL AND FALSEでは、AND FALSEでFALSEが確定しているのでFALSEになります。
NULL OR FALSEはNULLとなります。
NULL OR TRUEはOR TRUEでTRUEが確定しているのでTRUEになります。
NOT (NULL)は、NULLになります。
AND FLASEとOR TRUEがキーですね。
これはそのまま覚えるのが良さそう。

NULLで検索したい時

MySQLにはNULLで検索するための構文が用意されています。
それがIS NULLです。

SELECT * FROM Books WHERE category IS NULL;

こう使います。

IS NOT NULLも存在します。

呟き

思ったけど、NULLって虚数みたいな感覚で扱うとわかりやすいんじゃ？？

メモ

a<=>bはaとbは等しい(NULLにも対応) => spaceshipというらしい
a<>bはaとbは等しくない
知らなかった。