カテゴリー: MySQL

【COALESCE関数で解決!】MySQLで値が NULL のデータを集計(count)したい

MySQL

どうも、はらぐちです。

本来は NULL を撲滅すべきなのですが、
MySQLで値が NULL のデータを、どうしても集計(count)してみたくなったもので……

というわけで、やってみました!

やりたかった事

以下のようなテーブルとデータがあるとして

テーブル data
+-------+
| value |
+-------+
| aaa   |
| aaa   |
| aaa   |
| aaa   |
| bbb   |
| bbb   |
| NULL  |
| NULL  |
| NULL  |
+-------+

それぞれの値の数を集計するために、以下のSQLを実行したら

SELECT value,count(value) FROM data GROUP BY value;

結果

+-----------+--------------+
| value     | count(value) |
+-----------+--------------+
| NULL      |            0 |
| aaa       |            4 |
| bbb       |            2 |
+-----------+--------------+

NULLのカウントは 0 になって集計できない……
これをなんとか集計したい!

解決策

COALESCE関数を使う

引数を順番に評価し、NULL と評価されない最初の式の現在の値を返し、
すべての引数がNULLの場合はNULLを返すという関数です。

例えば以下の場合

COAESCE(value,value2,'abc')

valueカラムが null の場合は value2 を返す。
value2も null の場合は ‘abc’ という値を返す

という動きになります。

これを踏まえて、はじめのSQLを以下のように編集

SELECT value,count(COALESCE(value,'')) FROM data GROUP BY value;

結果

+-----------+---------------------------+
| value     | count(COALESCE(value,'')) |
+-----------+---------------------------+
| NULL      |                         3 |
| aaa       |                         4 |
| bbb       |                         2 |
+-----------+---------------------------+

値が NULL のレコード数がカウントできました!
ばんざい。

mysqlのバックアップ(mysqldump)のロック問題

こんにちは、はらぐちです。

今回お話したいのは、mysqlのバックアップ方法についてのあれこれです。

バックアップ mysqldump

mysqlのバックアップといえばmysqldumpです。
以下のような形で使います。

mysqldump -u root -p -x -A > my_dumpall.db

これで全データベースのダンプができます。
特定のデータベースをダンプしたい場合は、以下のようにデータベース名を指定します。

mysqldump -u root -p -x データベース名 > dump.sql

定期的にバックアップを取りたい場合は、シェルスクリプトで以下のようなものを
cronで実行してあげるといいでしょう。
二日間のバックアップを保持するスクリプト例です。

#!/bin/bash
MPASS=パスワード
mysqldump --defaults-extra-file=<(printf '[mysqldump]\npassword=%s\n' ${MPASS}) -u root -x -A > my_dumpall_`date +%Y%m%d`.db
OLDDATE=`date "-d2 days ago" +%Y%m%d`
rmfile=my_dumpall_$OLDDATE.db
if [ -e $rmfile ]; then
sleep 5m
rm -f $rmfile
fi

ちなみにリストアは以下のような形です。

mysql -u root -p < dump.sql
mysql -u root -p データベース名 < dump.sql

バックアップ datadirのコピー

mysqlのdatadirをOS上でコピーしてしまえばバックアップになります。

この方法の最大の利点は、リストアが非常に高速な事です。
通常のリストアはダンプファイル(SQL)をmysqlに渡す事で処理をするのでIndexの再作成などの処理もかかり、
mysqlのcpuがボトルネックとなってきますが、datadirによるリストアはdatadirのファイルを
そのまま置き換えてあげるだけですので、簡単かつ高速です。

しかし、同様に整合性の問題がありますので、mysqlを停止してからでなければいけないのが欠点です。
停止となるので完全に止まってしまいます。また、データベース単位でのバックアップなどはできません。

このバックアップ方法をとるのはサーバー移行などの高速なリストアが必要な時です。

mysqldumpのロック問題

mysqldumpコマンドのオプションで「-x」というものがありますが、
これはデータベースのすべてのテーブルをリードロックする、というものです。
データベースをリードロックすると参照系のクエリは発行できますが、
更新系のクエリはロックが解除されるまで待ち状態となります。
これはデータベースの整合性を保つ為に必要です。

しかし、サービス運用中ですと、この「更新系が待ち」になる事が許容できない事があります。
たかだか1Gくらいのデータベースですと、ミッションクリティカルなサーバーでない限り
ロックがかかる時間も少ないのでまだ許容できるかもしれませんが、
これが20GBほどのサイズとなると、30分ほどかかったりする事がざらにあります。

セッションをDBで管理してたりすると、すべてのユーザーがサービスを利用できなくなるというわけです。

ロック問題の解決策 single-transaction

データベースがすべて「innodb」であればシングルトランザクションオプションを使ったダンプを行う事で
ロックせずにバックアップする事ができます。

mysqldump -u root -p --single-transaction -A > my_dumpall.db

内部的にはスナップショットをとって、そのデータをダンプする事でロックする事なく
整合性のとれたダンプを取る事が可能です。
通常のmysqldumpと異なる点は、ダンプデータが「ダンプが終了した時の状態」ではなく
「ダンプを開始した時の状態」であるという点です。

ちなみに–master-data=2を付けるとbinlogファイルと位置の情報をdumpに含めてくれるので
レプリケーションslaveを作る際に大変重宝します。
この方法の最大の欠点はすべてのデータがinnodbである必要がある点です。
myisamなどのテーブルは整合性が取れなくなる可能性があります。

ロック問題の解決策
レプリケーションslaveにてdump

サーバーがもう一台必要ですがレプリケーションしたslaveサーバーにてバックアップを行うと、
ロックをかけてもサービスには影響を与えません。

ロック問題の解決策
LVMのスナップショットでdatadirをコピー

こちらは実際には行った事がないのですが、LVMのスナップショットで
datadirのスナップショットを取ってしまえばmysqlの停止なくバックアップ可能です。
ですが、スナップショット作成中は非常に負荷が高くなるそうで、
該当時間の書き込み処理の性能がガタ落ちするそうです。

一番いいのはsingle-transaction

日々のバックアップ用途であれば今までの運用の経験から「single-transaction」オプションでの
mysqldumpが一番よかったかなぁと思います。
これからサーバーでmysqlを使う時は、意識的に「InnoDBを使う」という事を心がけていくといいと思います。

MySQL5.6にしていくつかのSQLでエラーが出るようになった

MySQL5.6にしていくつかのSQLでエラーが出るようになっちゃいました。

具体的にはINSERT文を実行した時、以下のようなエラーとなり処理が実行されなくなりました

SQLSTATE[HY000]: General error: 1364 Field 'hoge' doesn't have a default value

エラー文からデフォルトバリューが設定されてないカラムにNULLを入れようとした為のエラーのようです。
ぐぐるとずばりな回答をされてるブログが見つかりました。

iをgに変えるとorangeになることに気づいたoranieの日記
MySQL5.6で今までのVerでは問題無かったSQL文がエラーになった場合の対処法
http://d.hatena.ne.jp/oranie/20130402/1364906656

日々の覚書
MySQL5.6が勝手にsql_modeを書き換えてくれる話
http://yoku0825.blogspot.jp/2013/03/mysql56sqlmode.html

対処法

sql_modeの設定がMySQL5.5と5.6で異なる事が原因です。
sql_modeのSTRICT_TRANS_TABLESをはずせば、MySQL5.5と同じ動作となる為、
適切と判断した値を勝手に挿入して、警告を出力するだけにとどまり、上記エラーはおきません

mysql&gt; SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql&gt; SET @@GLOBAL.sql_mode='';
Query OK, 0 rows affected (0.04 sec)
mysql&gt; SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

上記だと再起動すると設定が戻ってしまうのでmy.cnfの設定を変更する必要があるのですが注意が必要なのは、mysql5.6から「scripts/mysql_install_db」を実行した場合にMySQLのbasedirにmy.cnfを自動的に作成してくる事です。
このmy.cnfには以下のようなsql_modeが設定されていますので値を空に変更します。

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
↓
sql_mode=''

/etc/my.cnfなど、他のmy.cnfを普段使用している場合、こちら設定してもbasedirのmy.cnfが設定を上書きしてくる為、必ず確認が必要です。(はまりました)

sql_mode STRICT_TRANS_TABLES

sql_modeは公式には以下のようにかかれています。

SQL モード – http://dev.mysql.com/doc/refman/5.1/ja/server-sql-mode.html

SQL シンタックスを MySQL がサポートし、どのようなデータ バリデーション チェックを実行するべきかを定義するもの

mysql5.6で設定されているsql_modeのSTRICT_TRANS_TABLESは通称「ストリクトモード」と呼ばれているもので、無効なデータなどの挿入、更新時にエラーを発生させるモードです。

まとめ

mysql5.6からbasedirに作られるmy.cnfでsql_modeをストリクトモードとして動かすSTRICT_TRANS_TABLESが設定されている。

今後、新規につくるサービスなどであればデフォルト通り有効にする事が望ましいと思いますが、すでに運用しているMySQL5.5のサービスを5.6にアップグレードする場合などは上記のようにSTRICT_TRANS_TABLESをはずせばOKです。

MySQLでDB移行する時はmysql_upgradeを行おう

mysqlのDB移行ではまったお話。

MySQL4系で動いてたシステムをMySQL5.5のサーバーへ移行する事となりました。その際にいくつかトラブルが発生し、はまってしまったお話です。

mysqlの移行作業

移行作業は以下のような形で行いました。
旧サーバーのデータベースのダンプを取得
[code]
/usr/local/mysql/bin/mysqldump -pXXXX -u root -x -A > /tmp/my_dump.db
[/code]

取ったdumpを新サーバーへ転送
[code]
scp /tmp/my_dump.db hoge@xxx.xxx.xxx.xxx:/tmp/
[/code]
新サーバーでリストア
[code]
mysql -u root < /tmp/my_dump.db
[/code]
FLUSHを実行
[code]
mysql -u root

FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
[/code]
新サーバー上からmysqlへの接続を確認して作業完了したのでした。

rootユーザーがなんかおかしい

問題はユーザー作成を行ったときに発覚しました。
rootでログインしてユーザー作成を行おうとするも、権限がないというエラーが発生してしまう。
[code]
mysql> GRANT ALL PRIVILEGES ON . TO hoge@localhost IDENTIFIED BY ‘パスワード’ WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user ‘root’@’%’ (using password: YES)
[/code]
権限の確認の為に以下のコマンドで確認したのですが
[code]
mysql -u root
mysql> select * from mysql.user where user=’root’;
[/code]
表示されるすべての項目は「Y」となっていました。

rootユーザーを再作成してみる

どうしても原因がわからなかったのでrootユーザーが変になったのだと決めつけた。
検証環境でrootユーザーを削除して再作成を実行してみました
[code]
mysql> DELETE FROM mysql.user WHERE user=’root’;
Query OK, 1 row affected (0.00 sec)
mysql> exit
[/code]
mysqlを権限フリーモードで起動
[code]
/path/to/mysqld_safe –skip-grant-tables &
[/code]
mysqlにログイン。rootユーザーのパスなしでログインできるようになっている。
[code]
mysql -u root
[/code]
パスワードの設定
[code]
mysql> update user set password=PASSWORD(‘パスワード’) where user=’root’;
mysql> use mysql;
[/code]
このままでは権限は何ももたない一般ユーザーと同じなのでrootユーザーへ様々な権限を付与します。
この内容は正常に動作しているmysql5.5サーバーのrootユーザーの設定を確認しました。

別のmysqlサーバーにて以下のようにして権限の種類を確認
[code]
mysql -u root
mysql> select * from mysql.user where user=’root’;
+————+——+———-+————-+————-+————-+————-+————-+———–+————-+—————+————–+———–+————+—————–+————+————+————–+————+———————–+——————+————–+—————–+——————+——————+—————-+———————+——————–+——————+————+————–+————————+———-+————+————-+————–+—————+————-+—————–+———————-+——–+———————–+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+————+——+———-+————-+————-+————-+————-+————-+———–+————-+—————+————–+———–+————+—————–+————+————+————–+————+———————–+——————+————–+—————–+——————+——————+—————-+———————+——————–+——————+————+————–+————————+———-+————+————-+————–+—————+————-+—————–+———————-+——–+———————–+
| localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y
[/code]

この権限の通りに設定を行います。結果以下のコマンドになりました。
[code]
mysql> update user setSelect_priv=’Y’,
Insert_priv=’Y’,
Update_priv=’Y’,
Delete_priv=’Y’,
Create_priv=’Y’,
Drop_priv=’Y’,
Grant_priv=’Y’,
Alter_priv=’Y’,
Shutdown_priv=’Y’,
Process_priv=’Y’,
File_priv=’Y’,
Grant_priv=’Y’,
References_priv=’Y’,
Index_priv=’Y’,
Alter_priv=’Y’,
Reload_priv=’Y’,
Show_db_priv=’Y’,
Super_priv=’Y’,
Create_tmp_table_priv=’Y’,
Lock_tables_priv=’Y’,
Execute_priv=’Y’,
Repl_slave_priv=’Y’,
Repl_client_priv=’Y’,
Create_user_priv=’Y’,
ssl_cipher=”,
x509_issuer=”,
x509_subject=”,
max_questions=”,
max_updates=”,
max_connections=”
where User=’root’;
[/code]
しかしこのコマンドでエラー発生しました。Create_user_priv といった項目など存在ない、というのです。
mysql4のドキュメントを確認した所、たしかに「Create_user_priv」はない。
mysql5.1や5.5のドキュメントでは「Create_user_priv」が存在します。

・権限システムはどのように機能するか(mysql4.1) – http://dev.mysql.com/doc/refman/4.1/ja/privileges.html
MySQL 提供の権限(mysql5.1) – http://dev.mysql.com/doc/refman/5.1/ja/privileges-provided.html
・Privileges Provided by MySQL(mysql5.5) – http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html

つまり、バージョン違いによるprivilegesカラムの変化が問題だったんだよ!

なんだってー! Ω ΩΩ

なんとなくバージョン違いなのが問題なんだろうなー、とわかってはきたのですがどうしたら解決できるの・・・と困っていたのですが、この件は会社の何人かに相談していて、nakagawa氏から以下のようなページを教えてもらったのでした。

4.5.4. mysql_upgrade — MySQL アップグレードのテーブル チェック
http://dev.mysql.com/doc/refman/5.1/ja/mysql-upgrade.html

上記のページを確認してただ以下のコマンドを実行
[code]
mysql_upgrade
[/code]
このコマンドにより
[code]
mysql> select * from mysql.user where user=’root’;
[/code]
で確認した所 「Create_user_priv」の項目ができている事を確認。

この上で上記の権限を付与さしてあげました。
[code]
mysql> update user setSelect_priv=’Y’,
Insert_priv=’Y’,
Update_priv=’Y’,
Delete_priv=’Y’,
Create_priv=’Y’,
Drop_priv=’Y’,
Grant_priv=’Y’,
Alter_priv=’Y’,
Shutdown_priv=’Y’,
Process_priv=’Y’,
File_priv=’Y’,
Grant_priv=’Y’,
References_priv=’Y’,
Index_priv=’Y’,
Alter_priv=’Y’,
Reload_priv=’Y’,
Show_db_priv=’Y’,
Super_priv=’Y’,
Create_tmp_table_priv=’Y’,
Lock_tables_priv=’Y’,
Execute_priv=’Y’,
Repl_slave_priv=’Y’,
Repl_client_priv=’Y’,
Create_user_priv=’Y’,
ssl_cipher=”,
x509_issuer=”,
x509_subject=”,
max_questions=”,
max_updates=”,
max_connections=”
where User=’root’;
[/code]
rootの設定が完了したら今は権限スルーモードで立ち上げているいるのでいったん終了して普通モードで起動します
[code]
/path/to/mysqld_safe &
[/code]
これでログインしてユーザーが作成できるか確認します
[code]
mysql -u root
mysql> GRANT ALL PRIVILEGES ON . TO hoge@localhost IDENTIFIED BY ‘パスワード’ WITH GRANT OPTION;
[/code]
成功しました!

まとめ

古いバージョンのmysql dumpを最近のmysqlに流し込んだ時は mysql_upgradeを実行し、mysqlテーブルを最新仕様にしておく。また、その後rootなどの管理ユーザーに適切な権限を付与する必要があるという事を学びました。

考えたらDB単位での移行であればこの問題はおきないでしょうね。まるごと移行を行うときは注意、というお話でした。

© SEEDS Co.,Ltd.