まだプログラマーですが何か?

プログラマーネタとアスリートネタ中心。たまに作成したウェブサービス関連の話も http://twitter.com/dotnsf

タグ:blob

Node.js で MySQL データベースを利用する場合、npm の mysql ライブラリが多く使われると思っています:
mysql - npm


このライブラリを使うと、例えば SELECT 文を実行するのであれば、こんな感じに記述することで実装できます:
var Mysql = require( 'mysql' );

//. データベースへ接続
var mysql = Mysql.createConnection({
  host: '192.168.10.10',
  user: 'username',
  password: 'password',
  database: 'mydb'
});
mysql.connect();

//. SELECT 文実行
mysql.query( 'select * from items where price > 1000', function( error, results, fields ){
  if( error ) throw error;
  results.forEach( function( result ){
    var id = result.id;
       :
       :

    //. 終了
    mysql.end();
  });
});

また INSERT 文やプレースホルダーっぽい機能を使うこともできます:
var Mysql = require( 'mysql' );

//. データベースへ接続
var mysql = Mysql.createConnection({
  host: '192.168.10.10',
  user: 'username',
  password: 'password',
  database: 'mydb'
});
mysql.connect();

//. INSERT 文実行
mysql.query( 'insert into items set ?', { id: 1234, name: 'シャンプー', price: 500 }, function( error, result ){
  if( error ) throw error;
       :
       :

    //. 終了
    mysql.end();
  });
});

詳しくは上記公式ページを参照してください。


さて、MySQL では create table でテーブルを定義する際に blob(バイナリラージオブジェクト)型の列を指定することができます。画像ファイルなどバイナリデータをそのまま格納することができる列が定義できます:
> create table items( id int primary key, name varchar(50), price int, img blob );

定義は上記のように指定すればいいのですが、ではこの blob 列に、特に mysql ライブラリを使ってどのように指定すればデータを格納すればよいか、が今回のテーマです。特にウェブ画面から画像ファイルを指定してアップロードするような場合に、その画像ファイルの内容を具体的にはどのようにして blob 列に格納すればよいか、という内容です。

この要件について、少しググると MySQL の LOAD_FILE() 関数を使う方法が見つかります。この場合、具体的には以下のように記述します(目的の画像ファイルが /tmp/aaa.png に存在すると仮定します):
    :

mysql.query( 'insert into items set ?', { id: 1234, name: 'シャンプー', price: 500, img: LOAD_FILE('/tmp/aaa.png') }, function( error, result ){
: :

この方法はローカル MySQL サーバーに対しては有効に利用できます。LOAD_FILE() 関数はサーバー側のファイルシステムに対して実行されます。なので上記命令を実行してデータを格納する MySQL サーバーのファイルシステムに /tmp/aaa/png というファイルが存在していれば正しく動きます(命令を実行するクライアント側のファイルシステムにあっても動きません)。

しかし一般的なウェブシステムではウェブサーバーとデータベースサーバーは分離しています。そのようなケースでは(ウェブサーバーはデータベースクライアントになるので)ユーザーがウェブでアップロードしたファイルはウェブサーバーに一時格納されるだけで、データベースサーバーへは送られません。そこでウェブサーバー上で LOAD_FILE を実行してもデータは格納できないことになります。

では改めて、 LOAD_FILE() を使わずに blob データをどのように MySQL に格納するか、その方法がこちらです:
var fs = require( 'fs' );
    :
var img_content = fs.readFileSync( '/tmp/aaa.png' );
mysql.query( 'insert into items set ?', { id: 1234, name: 'シャンプー', price: 500, img: img_content }, function( error, result ){
    :
    :

ファイルシステムライブラリである fs をロードし、readFileSync 関数でローカルの(ウェブサーバー上の)バイナリファイルを読み込み、その結果をプレースホルダーに指定するだけです。

ちなみに取り出すときはこんな感じ:
    :
mysql.query( 'select * from items where id = 1234', function( error, results, fields ){
  if( error ) throw error;
  var img_content = results[0].img;
    :
    :

パフォーマンス等の観点からバイナリラージオブジェクトを MySQL などのデータベースに格納するべきか?という問題はあると思いますが、S3 ストレージなどの外部に格納する場合と比べて「データベースのバックアップ/リストアでオブジェクトごとバックアップ/リストアされる」というメリットはあります。用途に応じては使う価値があると思っています。



2日ほど泥沼にハマって抜け出せなくなっていた問題が解決できたので、その謎を共有します。

元々はこんなシステムを PHP で作ろうとしていました:
 - 画像をアップロードできる
 - アップロード画像は ID を付与した上で MySQL の BLOB (バイナリ・ラージ・オブジェクト)として格納する
 - ID を指定して、アップロード済みの画像を表示させることができる

自分自身でも Java では何度も作ったことのある仕組みで、その PHP 版を作ろうとしただけでした。

いざ作って動かしてみると、アップロードは成功します。でも表示時に画像が壊れてしまい、うまく表示できないのです。この原因究明と解決に時間を使ってしまいました。


もう少し詳しく状況を説明します。調べていくうちに同じ現象が MySQL を使わなくても再現できることがわかったので、話をシンプルにするために MySQL なしで説明します(実際にはこの切り分けも簡単ではなかった)。

ソースコードはこんな感じ。同一ディレクトリにある bmxug148.png というファイルのバイナリコンテンツ(元々のコードでは MySQL の BLOB に格納されていたもの)を取り出して、PNG 画像用の HTTP ヘッダを指定して echo でバイナリをそのまま出力しています:
<?php
$img = file_get_contents("./bmxug148.png");
header('Content-Type: image/png');
echo $img;
?>

この PHP ファイルをブラウザから呼び出すと、画像が表示・・・されることを期待していたのに、画像が壊れてしまっている旨のメッセージが表示されてしまいます(FireFox で確認した場合):
2015100202


こういう状況だと、表示側の PHP スクリプトに問題があるのか、それともアップロードしたファイルバイナリデータの格納時に既に壊れてしまっているのかすらわかりません。まあ、上記のシンプルなスクリプトでも再現するということは前者ということになるのですが、それが分かったのは相当後になってのことです。


で、とりあえずこの「壊れた画像バイナリ」を無理やり取り出してファイルとして保存し、バイナリエディタで開いてみました。そこで分かったのは画像の最初3バイトに PNG 画像としてふさわしくない情報が追加されていた、ということです:
2015100203


0xEF, 0xBB, 0xBF という3バイトが余分です(4バイト目から本来の PNG 画像のヘッダが始まっています)。4バイト目から表示されていれば正しく表示できそうなのですが、ではこの最初の3バイトはどこで付与されてしまったのでしょう? DB への格納時の処理を疑うこともできたのですが、この EF BB BF という3バイトの組みあわせ、どこかで見たことありました。。

ちょっと調べると分かるのですが、これは BOM(Byte Order Mark) と呼ばれる符号で、テキストが Unicode で記述されていることと、その符号化の種類を示すものです。で、UTF-8 の場合の BOM がまさにこの 0xEF 0xBB 0xBF の3バイトなのです。


つまり、実はもとの PHP ファイルそのものが UTF-8 で記述されていて、しかも BOM 付きでファイルが保存されていたことが直接の原因だったのでした。このため、ブラウザでこのファイル実行した場合も、最初に 0xEF 0xBB 0xBF の3バイトは(PHP ファイルの一部として)返ってきて、次に PHP で処理した結果の画像バイナリが続いていた、ということになります:
2015100201


画像の情報は正しく返っていたのに、その前に画像とは関係のない3バイトが送られてきていたため、「画像としては壊れている」と判断された、ということです:
2015100202


したがって、解決策は PHP ファイルを BOM なしで保存することです:
2015100204


この PHP をブラウザから実行すれば、本来の正しい画像が表示されるようになります:
2015100205


あー、よかった。
 

このページのトップヘ