MySQLの最近のブログ記事

MySQL でデータを管理する CGI を書いていたのだが、EUC-JP で保存したデータが化ける。

う～む・・・

DB も、

DEFAULT CHARACTER SET ujis;

で作ってるし、その中の TABLE も、

DEFAULT CHARSET=ujis;

で作成している。

そこに、Jcode.pm で EUC-JP に変換したデータを突っこんでるのだが、例えば「事務局」が「???局」みたいに化けてしまう。

う～ん・・・(^^;

結局、DB と TABLE の CHARSET を latin1 で作り直したら解消した。

どうも、character_set_client が latin1 で、character_set_database が ujis のためのようだな。
↓ここに詳しく書いてある。
http://www.mysql.gr.jp/frame/modules/bwiki/index.php?FAQ#content_1_44

う～む・・・
自分ところのサーバなら、mysqld 他全ての charset を統一することも出来るが、お客さんところの MySQL の設定は、他のシステムがどうなってるのかわからないので触れんしなあ・・・

ということで、取りあえず今回はこのままで。

 

Perl で、DBD::mysql モジュールを使って MySQL のデータベースに接続しているのだが、

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at hogehoge.pl line 97

というエラーになって接続出来ず。
この間書いたエントリー「MTOS で MySQL の Socket が見つからない時」と同じパターンじゃね。

これは、DB に接続する時に、

$dbname = 'hogehoge';
$user = 'hogeuser';
$passwd = 'hogepass';
$host = 'localhost';
$port = '3306';
$option = 'mysql_socket=/tmp/mysql.sock';

$dbh = DBI->connect("dbi:mysql:$dbname:$host:$port;$option", "$user", "$passwd");

と、オプションでソケットの位置を明示的に指定してやればＯＫ。

しかし、何で my.cnf の socket 指定は読んでくれんのかね？
/etc/my.cnf には、

socket  = /tmp/mysql.sock

と書いているのだが。この値を使ってくれないの？

/etc/my.cnf に、

mysql_socket  = /tmp/mysql.sock

なんて書くと

[ERROR] /usr/local/mysql/libexec/mysqld: unknown variable 'mysql_socket=/tmp/mysql.sock'

って怒られるしね。

まあ、取りあえず動いているので良いが、釈然としないな。誰かエロい、いや、エラい人、MySQL のこと教えて！

一応、（本気でハックしたことは無いが）MovableType も 3 の頃からの付き合いなので、先日 MTOS5 をインストールした時もついつい今までの習慣で mt-config.cgi を自分で編集して put したが、MT5 からは Web 画面の「環境設定ウィザード」で自動生成するのが推奨されているようである。

つーことで、先日入れた MTOS5 を一旦全部消して入れ直してみた。

なるほど。
全ファイルをサーバ上に put して、cgi に実行権与えて（必要であれば、httpd の実行ユーザで更新が行われるディレクトリやファイルの権限を修正して）、mt.cgi とかにアクセスすれば、セットアップが済んでいないので

http://www.exsample.jp/mt/mt-wizard.cgi

に強制的にリダイレクトされて、環境設定ウィザードの開始である。

なるほど、簡単だな。（もちろん、まったく知識の無い人間では厳しいが(^^;）

この間「MTOS で MySQL の Socket が見つからない時」で書いた MySQL の Socket ファイルのパスも（「高度な設定」リンクをクリックしなければいけないが）、画面に「データベースソケット」という項目があるのでここで入力すれば良い。
Socket のパスが /var/lib/mysql/mysql.sock ではない人は必ず設定しようね！

まあ、ただ、こういう具合に「一見簡単」なインタフェースを用意されると、かえってうちらのような業者は面倒くさいことになるんだよな。

「中途半端に知識があるお客さんのチャレンジ」が始まるからだ。(^^;

絶対、こういうインタフェースがあると「自分でやる」と言い出すお客さんが出てくるのだ。それはかまわない。俺らも、やってない作業に金をくれ」とは言わないので。是非自分で頑張ってコスト削減に励んでほしい。
・・・でも、「質問はタダ」とは思わないでほしい。(^^;

絶対、これ、「DB の接続テストが OK にならんのじゃけど、なんで？」と聞いてくるお客さん、いるよなあ。(^^;
そこから原因調べて「こうすればいいですよ」とアドバイスするところ、そこが正に俺らの仕事なわけで「タダではない」んですよ！俺らに相談したところで「既に自分の力だけでやっているのではない」んですよ！

・・・ってことを、ちゃんと認識してほしいな。社会人として。:-)

と、そんなことを思っているうちに作業は終了した。楽になったものだ。

MovableType をベースにする案件があるので、自社サーバに MTOS5 をセットアップしていたのだが、対話式インストールウィザードの実行で

Connection error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

というエラーが出てしまう。

まあ、読んで字の如く「Socket が見つからないので MySQL デーモンと Socket 通信が出来ませんぜ」ということだろう。

そりゃそうだ。
Socket の位置は /var/lib/mysql/mysql.sock ではないもの。

現在テスト機で動かしている MySQL は、

socket = /tmp/mysql.sock

という設定で実行している。
ちなみに、同じサーバで某地域 SNS（OpenPNE ベース）を動かしていて、PHP から MySQL の利用は問題なく行われているし、拡張機能のいくつかを Perl で組んで実装しているので Perl からの接続も問題無い。

ということで、これは MovableType 側の問題だ。

で、ざっと grep かけて Socket のパスをどこで指定しているか探してみたが分からなかったので、何か情報は無いかと思ってググってみたら・・・とんでもねえ。
この件に関して記事を書いてるブログはけっこうあるのだが、ほとんどが、

/etc/my.cnf の socket の値を /var/lib/mysql/mysql.sock に変更して MySQL を再起動しましょう

と説明しているのだ。（驚）

ええ？？？アプリケーション側の事情にデーモン側が設定変更して合わせるの？(^^;
とんでもねえ！そんな対応、下の下の対応だ。
どこぞの素人が書いた記事が、これまた素人によってどんどん拡散していってるんじゃないのか？こえ～（笑）

正解は、mt-config.cgi に

DBSocket /tmp/mysql.sock

という記述を追加する・・・だ。

最近 MySQL では UTF-8 しか扱ってなかったのだが、今回作成したプログラムは「Shift_JIS で書かれた CSV ファイルを読み込んで DB に登録。もちろん NEC 特殊文字や NEC 選定 IBM 拡張文字もガンガン混ざってますぜ！」という香ばしいもの。

ソース自体は EUC-JP で書いたので、DB の中身も EUC-JP で保管するようにして、自前のコード変換関数で Shift_JIS→EUC-JP に変換している。
CREATE TABLE するときにも、ちゃんと DEFAULT CHARSET=ujis 付けてるしね。

なのに、データを登録した後で phpMyAdmin でチェックすると、日本語化け化けやん。

どうも、MySQL ではサーバ側で勝手に文字コードのエンコードをしてしまうので、UTF-8 以外のコードではこういう文字化けが発生してしまうという。
日頃 PostgreSQL ばかり使ってて、いきなり MySQL に来たら絶対ハマっちゃうよなあ、これは。

で、解決方法としては、本来の SQL を投げる前に、

SET NAMES ujis

という SQL を発行すること。

具体的には、

$query    =<<EndOfQuery;
SET NAMES ujis
EndOfQuery

$sth = $dbh->prepare($query);
$sth->execute();

としてから、本来の SQL を

$query    =<<EndOfQuery;
INSERT
INTO
  hogehoge
～本来の SQL～
EndOfQuery

$sth = $dbh->prepare($query);
$sth->execute();

と実行すれば良いと。

なんだかなぁ(^^; テーブル作る時に DEFAULT CHARSET=ujis なんて宣言している意味がなかったな。(^^;
まあ、DB に詳しい偉い人たちが文句を言ってないってことは、この実装も間違いではないんだろうけど、なんだかなあ(^^;

ちなみに、MySQL の設定ファイルや実行時のオプションであれこれする方法もあるようなのだが、今回使用しているのが安いレンタルサーバで、MySQL の設定ファイルは触れない仕様らしいので、毎度 SET NAMES ujis を発行する方法しかないようだ。

とほほ。またも無駄な時間を・・・

いや、まあ、MySQL に限ったことじゃないけどな。

PostgreSQL にしても、バックアップしてリストアしたデータが化けちゃうってのは良くあることで、まあ、最初の原型作ったヤツらにとっては英語が全てなわけで、その後多言語化のために色々な仕組みが継ぎ足されたわけだから、問題が起こって当たり前って感じもしますが。
日本語だけでも、何個のコード体系があるんだよ、実際。(^^;

今回の MovableType の移行でもハマったぁ！

mysqldump によるバックアップ時と、mysql によるリストア時の、default-character-set の指定だけで何とかならんかいなと色々やったんだけど、結局、

mysqldump --default-character-set=binary -u hoge -phoge mtos > mtos_20091106.dump

で、文字コード変えずにバックアップして、mtos_20091106.dump を一旦ローカル PC にダウンロード。
で、秀丸エディタを使って中身を修正。

修正箇所は、

DROP TABLE IF EXISTS `mt_ts_job`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `mt_ts_job` (
  `ts_job_jobid` int(11) NOT NULL AUTO_INCREMENT,
＜略＞
  KEY `mt_ts_job_funccoal` (`ts_job_funcid`,`ts_job_coalesce`),
  KEY `mt_ts_job_funcrun` (`ts_job_funcid`,`ts_job_run_after`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

この、「ENGINE=MyISAM DEFAULT CHARSET=latin1;」の部分。
ここが、latin1 になってるので、utf8 に修正する（テーブルの数だけ。40箇所くらいかな）。
「ENGINE=MyISAM DEFAULT CHARSET=utf8;」という具合に。

で、これを新サーバにアップして、

mysql --default-character-set=utf8 -u hogehoge -p mtos < mtos_20091106.dump

とリストアする。

この方法以外ではどうすることも出来なかった。

ああ・・・面倒臭い、面倒臭い。元の MySQL の設定にも依存しちゃうし、なかなかすっきりいかないもんですなあ。

次に遭遇した問題が、mt.cgi の実行で、

Connection error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

というエラーが発生しちゃう件。

いや、確かに、旧サーバでは Socket ファイルを /var/lib/mysql 以下に作成していたけど、新サーバでは /tmp の下に作成してる。だから、'/var/lib/mysql/mysql.sock' を使ってソケット通信しようとすれば、そりゃあエラーになるよ。

・・・が、決め打ちなの？(^^;

どこかで指定してるのかと思い、grep しまくったけど結局 '/var/lib/mysql/mysql.sock' を固定的にセットしている部分は見つからず。
でも、まあ、どっかに記述があるんだろうな・・・

別に、/tmp 以下にこだわっているわけではないので、ディレクトリを /var/lib/mysql に変えてもいいのだが（/etc/my.cnf の socket = /tmp/mysql.sock の記述を修正するだけだし）、何となく悔しいので MovableType 側で対応することに。

結局、mt-config.cgi に環境変数として path を記述するのが一番スマートな気がしたので、

DBSocket /tmp/mysql.sock

という記述を追加。
これで、件のエラーは発生せず、正常に管理画面を使うことが出来るようになったのである。

・・・が、そんな俺に、新たな敵が。
あ、いや、新たな問題が・・・

mysqladmin で hoge-hoge という名前の DB 作って、そいつを mysql 上で扱おうとしたらエラーが・・・

mysql> GRANT ALL ON hoge-hoge.* TO dbuser@localhost;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-hoge.* TO dbuser@localhost' at line 1

どうも、ハイフンが式として判断されてるのか？コマンドの始まりと判断されているのか？・・・と思い、シングルクォーテーションで囲ってみたけど、

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''hoge-hoge'' at line 1

やっぱり駄目。同じエラーだなあ・・・
\ でエスケープしてみたら？

ERROR:
Unknown command '\-'.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\-hoge' at line 1

やっぱり駄目。つーか、エラーが増えてるし。(^^;

結局、バッククォーテーションで囲めばよかった。

mysql> GRANT ALL ON `hoge-hoge.*` TO dbuser@localhost;

こんな感じ。
そうかあ。MySQL じゃ、ハイフンを含んだ DB名を使うときは常にバッククォーテーションで囲まないと駄目なのか。ハイフンを含んだ DB名を使うのって今回が初めてなので知らんかった。(^^;

いちいち面倒なので、ハイフン使うのやめて、DBの名前を hoge_hoge に変えちゃった。
これが一番正解。（笑）

OpenPNE の DB を移行するため、旧サーバで

mysqldump -u hoge -phogehoge pne_db > pne_db_backup_20081230

で吸い上げたデータを、新サーバで

mysql -u hoge -phogehoge pne_db < pne_db_backup_20081230

したら、

ERROR at line 415: Unknown command '\'.

とエラーに。
ああ・・・新しいサーバの MySQL の default-character-set は utf8 だった。
つーことで、明示的に、

mysql -u hoge -phogehoge --default-character-set=sjis pne_db < pne_db_backup_20081230

と default-character-set を指定してやればOK。

ああ、そういえば昔、別の仕事で同じようなエラーが出た時は、エラーになった行の \ を、\\ と全部エスケープして登録したな。
今時は、UTF-8 をディフォルトの文字コードに指定していることが多いだろう。
Shift_JIS で作ってる DB を移行するときは、--default-character-set=sjis を忘れるなということだな。

ずっと Oracle ばかり使ってると、「あり？ROWNUM って MySQL で、どうすんだっけ？」となっちゃうので（すまんのお、最近記憶力がなくて）メモっとく。

20件目から10件表示なら、

SELECT
    *
FROM
    hogehoge
LIMIT
    20, 10

でね。LIMIT を使いましょう。

最後の 10件を、降順に ORDER BY せずに抜くために、

SELECT
    *
FROM
    c_access_log
LIMIT
    (
        SELECT 
            COUNT(*) - 10
        FROM 
            c_access_log
    ), 10

とかやってみたけど、You have an error in your SQL syntax と怒られちゃって駄目。LIMIT のところにサブクエリの値は突っ込めないのか？

MySQL で、並び順を変えずにラスト10件を取得するスマートな方法ってどうすんだろう？

＜参考＞
MySQLとApacheのもやもや情報
http://pagerank.cocolog-nifty.com/blog/2007/12/mysqloraclerown_4c4f.html