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で設定したファイルに記録されます。

Accessで外部データ取り込みする場合の制約?

Accessで外部データ取り込みする場合の注意点
知らないとハマるので要注意。
1.csvなどを取り込む場合には、末尾に半角のスペースが含まれる場合には切り詰められてしまう。=Accessを通じてインポートとエクスポートだけをした場合には列ごとの末尾の半角スペースの情報は失われる。
2.最後の列の値がどの行もnullだった場合には列として認識されない。=インポート時に列を追加してあげる必要がある。

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コマンドを定期的に発行したほうがよさそうだ。