MariaDBでBLOBを取扱時の注意事項

BLOBでバイナリデータをPHPから書き込みするときにはまったポイントをいくつか。。。MariaDBでの発生事例だが、MySQLでも同様だと思う。

・BLOBは65535Bytesしか格納できない。BLOBよりも大きなサイズの格納が必要であれば、MEDIUMBLOB、LONGBLOBを利用する必要がある。
・BLOB型にデータをinsertするときにはバイナリデータをストリームのまま登録することはできないので、PHPであればbin2hex関数でいったん16進数に変換してinsertした後、selectするときにhex2bin関数で戻す必要がある。また、BLOB型で65,535byteを超えるデータをinsertすると先頭65,535byteだけが登録されてしまう(insert時にエラーにならない)ので、insert前にデータサイズのチェックが必要。
・パケットサイズを大きくする必要がある。max_allowed_packet という設定値だが、デフォルトでは1MBなので、設定値を超えるデータをinsertしようとするとエラーが発生する。mysqlで大きなファイルを保存するための設定 を参考にログファイルサイズの設定値も変更したほうが良いかもしれない。
・PHPの設定項目値として、php.ini でupload_max_filesize設定値を確認する。この値を超えたデータがPOSTされても、$_FILES[name][name]でファイル名は設定されるが、データは一時ディレクトリに保存されず、
$_FILES[name][tmp_name] は値がセットされない。また、環境によってはset_time_limit関数を使ってタイムアウト時刻を調整したほうが良い。

MySQLでロールバックしなかった件

MariaDB(MySQL)でトランザクションを設定して意図的にロールバックさせたつもりがコミットされていた。。。。
原因は、対象のテーブルがトランザクションセーフテーブル (InnoDB または NDB のテーブルなど) ではなかったから。
参照:MySQLリファレンスマニュアル
MariaDB [(table name)]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [price]> update (table name) set ean='9784798148816' where asin='B073J82NQX';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [price]> \q
Bye
$ mysql -u (user name) -p (table name)
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is (ID sequence)
Server version: (version name)-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [price]> select ean from (table name) where asin='B073J82NQX';
+---------------+
| ean |
+---------------+
| 9784798148816 |
+---------------+
1 row in set (0.00 sec)

テーブルのエンジンを調べる方法は、MySQLサーバのストレージエンジン確認方法を参照するとよい。
> use information_schema
Database changed
MariaDB [information_schema]> select table_schema, table_name, engine from tables where table_name='(table name)';
+--------------+------------+--------+
| table_schema | table_name | engine |
+--------------+------------+--------+
| (schema name)| (table name)| MyISAM |
+--------------+------------+--------+
1 row in set (0.02 sec)

mariadbが起動しない

SELinuxが有効だったというオチ。SELinuxは正しく設定できればよいのだが、難解で毛嫌いしてしまうところが問題。
$sudo tail /var/log/mariadb/mariadb.log
160828 20:10:36 mysqld_safe Starting mysqld daemon with databases from /(mariadb-path)/m
ysql
160828 20:10:36 [Note] /usr/libexec/mysqld (mysqld (mysqlversion)-MariaDB-log) starting
as process (processID) …
160828 20:10:36 [Warning] Can’t create test file /(mariadb-path)/mysql/(hostname).lower-test
160828 20:10:37 InnoDB: The InnoDB memory heap is disabled
160828 20:10:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160828 20:10:37 InnoDB: Compressed tables use zlib (zlibversion)
160828 20:10:37 InnoDB: Using Linux native AIO
160828 20:10:37 InnoDB: Initializing buffer pool, size = 128.0M
160828 20:10:37 InnoDB: Completed initialization of buffer pool
160828 20:10:37 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: ‘open’.
InnoDB: Cannot continue operation.
160828 20:10:37 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid en
ded

MySQLで遅いクエリを記録する

MySQL 5.0よりサポートされています。
/etc/my.cnf に以下の設定をして再起動すると書き出されます。
[mysqld] の設定項目の中に記載しなければなりません。
log-slow-queries=/var/log/mysqld-slow.log
long_query_time=5
long_query_timeの値を変更することで特定秒数を経過しても返ってこないクエリがlog-slow-queriesで設定したファイルに記録されます。

ORDER BY句におけるNULLの順番について

Mysqlでは、昇順(ASC)でORDER BYするとNULLは先頭に来る。降順の場合には、最後に来る。順番を逆にしたい場合には、COALESCE(列名, 型の最大値)を指定するなどの対応が必要である
一方で、Oracle Database 11gでは、昇順(ASC)でORDER BYするとNULLは最後に来る。降順の場合には、最初に来る。順番を逆にしたい場合には、NULLS FIRSTもしくはNULLS LASTを指定すればよい。

mysql-bin.000001が肥大化する

/varディレクトリへのディスク割り当てが100%になってしまい、MySQLサーバーの接続上限に達して、接続できないという現象が発生した。
調べてみたところmysql-bin.000001が1.8GBにもなっていたことがわかった。詳しく調べてみると、mysql-bin.NNNNNNはバイナリログになっており、レプリケーションを利用する際に使用される。
レプリケーションを使用しない場合(MySQLをサーバー1台で運用する場合)には、my.cnfの
log-bin=mysql-bin
をコメントアウトして再起動すればよい。
ログを削除する場合には、上記の設定変更を行う前に、rootユーザーでMySQLサーバーに接続後、
PURGE MASTER LOGS before now();
として安全にバイナリログを削除するとよい。

MySQLが高負荷になる

クエリを発行するとmysqldのCPU使用率が100%になったまま張り付いてしまう現象が発生した。
MySQL/PostgreSQLで実行中のSQLを確認する方法を参考にして、show pricesslistクエリを発行して処理が完了していないSQLを確認してみたところ、やはり長時間処理が完了しないクエリが原因であった。
対象のクエリはexplain select ~で確認してみても、正しくインデックスが使われていることがわかったが、複数のテーブルを結合するSQLだったので、対象テーブルにおいてshow index from tablenameコマンドを発行すると、CardinalityがNULLになっていることがわかった。
おそらくこれが原因で発行したSQLにおいてインデックスが利用されないパターンであったと判断し、analyze table tablenameを発行して再度クエリを発行したところ、すぐに処理が完了した。
レコードが頻繁に追加されるテーブルは、手動でanalyze tableコマンドを定期的に発行したほうがよさそうだ。

テーブルの定義を確認する

show columns from テーブル名;
でも確認できるが、外部キーなどの確認はできない。
MySQLのマニュアルにあるが、
show create table テーブル名\G;
とすることで定義を確認することができる。
なお、MyISAMでは外部キーは使用できず、alter table テーブル名 add foreign key 外部キー名(カラム名) references 参照テーブル名(参照カラム名)としても無視されてしまう。

検索結果が正しくない

なぜか「中」を含む文字列を調べようとしているのに、「団」とか「台」が検索されてしまう。
どうやらMySQLをrpmでインストールするとlatin1がデフォルト文字コードセットになってしまっていてそれでマルチバイトの検索がうまくいかないことがあるようだ。
現在の文字コード設定をチェックしてみる。
mysql> show variables like ‘%char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)
binaryというのは無変換を示している。現在は、入力された文字がlatin1と見なされ、latin1のデータベースに登録されるため、例えばEUCの文字で登録した場合でもデータが破壊されることはない。
データベースのデフォルト文字コードをEUCにするためには、/etc/my.iniに以下のように設定して再起動すればよい。
[mysqld]
default-character-set=ujis
skip-character-set-client-handshake
何もデータがない状態であれば、これでよいのだが、もうすでに運用済みの場合にはこれではデータが破壊されてしまう。(登録済みデータがlatin1なのに変更後はEUCと見なされて処理されるため)
不幸なことに、自分はUTF-8で運用しているデータベースもあれば、EUC-JPで運用しているデータベースもあるため、一括で変換することも難しい。
今回はいろいろ調べた結果、特定のデータベースの文字コードセットだけを変更する方法があったので紹介したい。hogehogeデータベースに対してlatin1からEUC-JPへ変換する方法をご紹介する。
#ダンプ(hogehoge.dmp)を取得する。
mysqldump –default-character-set=binary -u root news –password=XXXX > hogehoge.dmp
#MySQLへログインし、データベースのcharacter_set_databaseを変換する。
alter database hogehoge character set ujis;
#hogehoge.dmp の中のSQL文にcharsetが指定されている箇所があるので、viなどで開きlatin1からujisへ置換しておく。
mysql -u root -p hogehoge –default-character-set=ujis < hogehoge.dmp で取り込む これで正しく取り込めるのだが、問題はクライアントの問い合わせ方法になる。 alter database news character set ujis; のSQLで変更されているのはcharacter_set_databaseだけであることが分かる。 mysql -u root -p hogehoge mysql> show variables like ‘%char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)
このままだと、発行されるクエリはlatin1と見なされるが、データベースの内部文字コードはEUCなので、SELECT文はマッチしないだろうし、INSERT文などを発行すればデータが破壊されてしまう。
そこで、以下のSQLを事前に発行すると・・・
mysql> set names ujis;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘%char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | ujis |
| character_set_connection | ujis |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | ujis |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)
となる。つまり、SET NAMES ujis文によってcharacter_set_client、character_set_connection、character_set_databaseがEUCとなる。ちなみにcharacter_set_serverはデフォルト文字コードセットなので、latin1となっているが特に問題は発生しない。またcharater_set_systemはUTF-8固定でありこちらも処理上は問題はない。
※SET NAMES ujis; とおなじクエリを接続時にパラメータとして渡すこともできる。
mysql -u root -p hogehoge –default-character-set=ujis
mysql> show variables like ‘%char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | ujis |
| character_set_connection | ujis |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | ujis |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)
もしPHPなどのプログラムによってクエリを発行する場合には、データベースへ接続後
set names ujis;
を実行する必要がある。
今回の問題について非常に有益となったサイトを最後にご紹介する。
http://www.mysql.gr.jp/frame/modules/bwiki/index.php?FAQ#fb74bab6
MySQLの文字コードに関するメモ

Mysqlに関する管理コマンド

#rootユーザーのパスワードを設定
mysqladmin -u root password '(password)'
#DBの追加
mysql -u root -p
mysql> CREATE DATABASE hogehoge
なお、5.0では大文字小文字を区別するらしく、create databaseはエラーになる。
#ユーザーの追加(すべてのDBに権限を付与の場合)
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO (username)@localhost IDENTIFIED BY '(password)';
#ユーザーの追加(hogehogeデータベースの任意テーブルに権限を付与する場合)
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON hogehoge.* TO (username)@localhost IDENTIFIED BY '(password)';