MySQL で新たにテーブルを作ったり、プライマリキー、ユニーク制約、またはインデックスを作成する際、下記のようなエラーが発生することがあります。

ERROR 1170 (42000): BLOB/TEXT column 'text_field' used in key specification without a key length 

結論として回避策から書くと、BLOB型またはTEXT型の場合は、インデックス作成時にキー長を明示してあげる必要があります。

create index new_index on table_name(text_field(100));

このエラーは、MySQL が BLOB型もしくはTEXT型 （これらに順ずる TINYTEXT型 や LONGTEXT型を含む）のような可変長カラムでは、その先頭から最大255文字分しかインデックスできないという制約から来ているようです。解決策は上記のとおり、キー長を明示するか、変わりに255以下で VARCHAR(100) のようなカラムを使って、そちらをインデックスとして使うといった工夫が考えられます。

考えてみれば当然と言える話ではあります。

題目を索引することはあれど、本文を索引した書籍など見たことがないですし、そこに意味がないことは誰もが分かるでしょう。たとえ本文を索引にするとしても、冒頭の一節というのが関の山。この MySQL の仕様は、理に適っている気がします。逆に本文を索引したい、つまり全文検索用の FULLTEXT インデックスであれば、TEXT型カラムにもちろん作成することができます。

このエラーに遭遇したときは、上記のような解決法もひとつの手ではありますが、そもそも TEXT型カラムを主キーにしたり、インデックスを張ろうという設計が正しいかどうかを考えてみた方が良いでしょう。

例外は、百人一首的に冒頭の一節から引きたいか、50音順インデックスを使うようなケースだけじゃないでしょうか。LIKE 検索で、先頭数文字で引っかけたい場合。それも、通常は最低1000 レコードを超えてこないとインデックスが効いてこなかったりするので、さらにレアケースな気がします。

エラーが起きるとついつい今すぐで回避方法を探してしまいがちですが、そもそもエラーになるような設計が正しいかどうか考えるクセは付けておきたいもんです。結局、そういう考え方が未来の自分を救ったりするんだから。

関連記事

MySQLで実行中のSQLをキャンセルする

mysql のスレーブを再構築する

MySQLのレプリ遅延の原因を調べる方法

PostgreSQLでMONTHS_BETWEEN

PostgreSQLで半角カナを全角カナに変換する関数

レプリケーションができていないのでログを見てみたら、「Client requested master to start replication from impossible position」というエラーが出力されていた。確かに、「show slave status」で表示されるポジションが、バイナリログ内に見つからない...。

不整合の度合いがハンパないのと、運用から切り離されているサーバだったので、スレーブをゼロから再構築することにした。以下、そのときの手順メモ。

まず、マスターの更新を止めてデータをdumpします。

mysql> FLUSH TABLES WITH READ LOCK;

スレーブ開始にそなえてポジションを確認。

mysql> SHOW MASTER STATUS \G
         File: db01-bin.010
     Position: 5432
 Binlog_Do_DB: db_name
Binlog_Ignore: 
1 row in set (0.00 sec)

データをdumpします。

$ mysqldump -uroot -hdb01 db_name > /tmp/db_name.dump

マスターの更新ロックを解除。

mysql> UNLOCK TABLES;

マスター側の操作はここまで。続いて、スレーブの再構築の作業に入ります。最初に、これまでのレプリ情報を削除してしまいます。

# /etc/rc.d/init.d/mysqld stop
# rm /var/lib/mysql/master.info
# rm /var/lib/mysql/relay-log.info
# /etc/rc.d/init.d/mysqld start

データベースをゼロから作るため、今までのデータベースを削除して作り直し。

mysql> DROP DATABASE db_name;
mysql> CREATE DATABASE db_name;

マスターのdumpデータをリストア。

# scp db01:/tmp/db_name.dump /tmp/
# mysql -uroot -hdb01 db_name < /tmp/db_name.dump

レプリケーションの設定を最新のものに。ポジションとログファイルは、先ほど「SHOW MASTER STATUS」した結果のものを使用します。

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO 
  MASTER_HOST='db01',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='db01-bin.010',
  MASTER_LOG_POS=5432;

スレーブを開始します。

mysql> START SLAVE;

これでレプリケーションが正常に行われるはずです。「SHOW SLAVE STATUS」やログにエラーなど発生していないことを確認しましょう。うまくいかない場合は、しかるべきときに mysqld のプロセスがきちんと停止していなかったとか、dump がうまくいっていなかったとか、そういったところだと思います。手順が少ないので、問題になる場所も限られます。

ここまで書いて気付いたのですが、スレーブが複数ある場合は、何もマスターの更新を止めてやる必要がないですね。そして、もっとシンプルな手順でできそう。

お疲れさまでした。

関連記事

MySQLでBLOB/TEXT型カラムにインデックスを張る

MySQLのレプリ遅延の原因を調べる方法

MySQLで実行中のSQLをキャンセルする

MySQLのレプリ遅延の原因には、大きく分けて、転送遅延とSQL実行遅延の2つがあります。

転送遅延は、マスタでバイナリログに出力されたSQLクエリが、スレーブのリレーログに出力されていないケース。SQL実行遅延は、SQLクエリがリレーログに出力されたが、実行が遅れているケース。レプリ遅延時の問題の切り分けとして、まずどちらのケースに該当しているかを判断する必要があります。

転送遅延の判定方法

マスタでの SHOW MASTER STATUS の結果と、対象スレーブでの SHOW SLAVE STATUS の結果を比較します。比較する項目は、以下のとおり。

この2項目で差がある場合、転送遅延が起こっている。マスタとスレーブのサーバ間に何らかの問題が起こっていると考えられるので、その線で本質的な問題を追っていくことになります。

SQL実行遅延の判定方法

転送に遅延がなかった場合、こちらの調査に移ります。

対象スレーブでの SHOW SLAVE STATUS から、次の項目同士を比較して判断します。

スレーブ側における、I/OスレッドとSQLスレッドの処理差から、つまりSQLの実行処理がどのくらい遅れているのかを調べることができます。SQL実行遅延の場合、スレーブの負荷が何らかの理由で高まっているケースなどが考えられます。マスタに比べてスレーブサーバの処理性能が劣るような場合、更新クエリが詰まることも考えられます。結構、厄介な話。

SQL実行遅延の場合、ほっときゃ直るケースも多かったりするのですが、転送遅延の場合は抜本的な対策が求められることが多いような気がします。両者とも各環境に応じたノウハウの積み上げから、最終判断をする必要はありますが。

ちなみに、そもそもの話として、レプリ遅延を監視するには、SHOW SLAVE STATUS のSeconds_Behind_Master という項目を見る方法が手っ取りばやいです。ただ、MySQL4.1.1以降から対応らしいので、MySQL4.0系では、こちらで紹介されているような方法は良いですね。

SHOW MASTER STATUS 、SHOW SLAVE STATUS で見られる項目の説明については、本家が一番わかりやすいと思います。

良いレプリ運用ライフを！

関連記事

MySQLでBLOB/TEXT型カラムにインデックスを張る

mysql のスレーブを再構築する

MySQLで実行中のSQLをキャンセルする

誤って、巨大なテーブルの全レコードを対象にするようなクエリを投げてしまって、いつまで経っても計算結果が返ってこないことってあるじゃないですか。そんなときの話。

mysql> show processlist;
+----+------+---------------+------+---------+------+-------+------------------+
| Id | User | Host          | db   | Command | Time | State | Info             |
+----+------+---------------+------+---------+------+-------+------------------+
|  9 | root | localhost     | NULL | Query   |    0 | NULL  | show processlist |
| 10 | root | localhost     | NULL | Sleep   |    1 |       | NULL             |
+----+------+---------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

processlist では、現在実行中のスレッドを一覧表示することができる。1列目の Id はスレッドID。時間のかかっているSQLがあれば、Info にそのクエリが表示されるはずなので、その Id を調べて Kill すれば、スレッドを強制終了することができる。

mysql> kill 10;

ターミナルコンソールから Ctrl+C でキャンセルしたつもりになっていたのに、ロードアベレージがぐんぐん伸びていって焦って、ようやくこの方法を知ることになったのは、ここだけの秘密。

ほら、PostgreSQL だと Ctrl+C でクエリはキャンセルできるし、接続はプロセスで見れるから、mysql ビギナーの自分はこの挙動に驚きました、という話です。メモメモ。

関連記事

MySQLでBLOB/TEXT型カラムにインデックスを張る

mysql のスレーブを再構築する

MySQLのレプリ遅延の原因を調べる方法

PostgreSQLでMONTHS_BETWEEN

PostgreSQLで半角カナを全角カナに変換する関数

２つの日付から経過月数を求めたい場合、Oracle には MONTHS_BETWEEN という便利なファンクションが用意されているのだけど、あいにく PostgreSQL にそういったものは無い。

「こんなもの、きっと誰かが考えてくれているだろう」と検索したら、やっぱりあった。

[ThinkIT] 第8回：ファンクションの移行（2）

CREATE FUNCTION MONTHS_BETWEEN(DATE,DATE)
  RETURNS NUMERIC AS
'SELECT to_number((date($1)-date($2)),
                  ''999999999'')/31 '
LANGUAGE 'sql'; 

記事では上記のように関数定義しているのだけど、このように常に 31 で除算すると、2008/3/21 と 2008/1/4 の差がおよそ 2.48 となる。切り捨てて2ヵ月間と見るか、切り上げて3ヵ月間と見るか、四捨五入して2ヵ月間と見るか。四捨五入するならば、実際は2ヵ月17日の差なので、「3ヶ月間」とする方が、みんなの求めるものに近いと思う。

どうするか。

日付の差を操作する場合は、age関数を使いましょう。

-- 切捨する場合
SELECT extract(month FROM age(date1,date2));
-- 切上する場合
SELECT extract(month FROM age(date1,date2) + interval '@ 1 month');
-- 四捨五入(15日以前は切捨、16日以降は切上）
SELECT extract(month FROM age(date1 + interval '@ 0.5 month',date2);

実際の現場では、4/10と5/10という２つの日付について、1ヵ月とみなす場合もあろうし、2ヵ月としたいケースもあるんじゃないかと思います。その辺は、上記参考に柔軟にイジイジすると良いと思います。

関連記事

PostgreSQLで半角カナを全角カナに変換する関数

MySQLでBLOB/TEXT型カラムにインデックスを張る

MySQLで実行中のSQLをキャンセルする

NULL文字を削除する

PostgreSQLの配列を使って文字列を集約をする方法

半角カナでの登録を拒否するために、自動で全角カナに変換したいときってあるじゃないですか。アプリケーション側で、AOPやFilterといった仕組みで変換してあげる方がスマートだと思ってるんだけど、DB側でやらないといけないケースもあるでしょう。

ということで、こんな関数を用意してみました。

CREATE OR REPLACE FUNCTION h2z_kana(text) 
  RETURNS text AS
$BODY$
    DECLARE
        zenkaku alias FOR $1;
        result text;
        i int;


        zt varchar[] = ARRAY['ガ', 'ギ', 'グ', 'ゲ', 'ゴ', 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ', 'ダ', 'ヂ', 'ヅ', 'デ', 'ド', 'バ', 'ビ', 'ブ', 'ベ', 'ボ', 'パ', 'ピ', 'プ', 'ペ', 'ポ', 'ヴ'];
        ht varchar[] = ARRAY['ｶﾞ', 'ｷﾞ', 'ｸﾞ', 'ｹﾞ', 'ｺﾞ', 'ｻﾞ', 'ｼﾞ', 'ｽﾞ', 'ｾﾞ', 'ｿﾞ', 'ﾀﾞ', 'ﾁﾞ', 'ﾂﾞ', 'ﾃﾞ', 'ﾄﾞ', 'ﾊﾞ', 'ﾋﾞ', 'ﾌﾞ', 'ﾍﾞ', 'ﾎﾞ', 'ﾊﾟ', 'ﾋﾟ', 'ﾌﾟ', 'ﾍﾟ', 'ﾎﾟ', 'ｳﾞ'];
    BEGIN


        result = zenkaku;


        -- 2バイトで変換
        FOR i IN 1..26 LOOP
            result = replace(result, ht[i], zt[i]);
        END LOOP;


        -- 1バイトで変換
        result = translate(result,
                   ' ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｯｬｭｮﾜｲｴｶｹｰ､｡･｣｢ﾞ,<.>/?_}]*:+;{[~@|\\`^=-)(&%$#"!',
                   '　アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョヮヰヱヵヶー、。・」「゛，＜．＞／？＿｝］＊：＋；｛［￣＠｜￥｀＾＝－）（＆％＄＃"！'
              );


        RETURN result;
    END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

ガやザのような濁音のものは、半角カナになるとカと濁点の合計2バイトになる。まずはじめに変換テーブルを用いてそれらを置換してから、残る半角カナを一括して置換してます。記号の置換処理のところは、不要なら除けばOK。でも、大体のケースであっても困らないと思いますけど。

あとは、この関数を使って UPDATE するトリガーを、INSERT と UPDATE 時に発動するように定義してあげれば、冒頭の件は対応できると思います。

DBから値を引っ張ってきて、そのままメール送信するようなケースで、半角カナが含まれてたらイヤンなときにも活用できるかな。そのケースでは、機種依存文字全般の対応もあるので、送信プログラム側でもろもろを処理した方が、スッキリするかしら？

ま、ひとつの参考までに。

関連記事

PostgreSQLでMONTHS_BETWEEN

MySQLでBLOB/TEXT型カラムにインデックスを張る

MySQLで実行中のSQLをキャンセルする

NULL文字を削除する

PostgreSQLの配列を使って文字列を集約をする方法

1対N（OneToMany）の関係にある2つのエンティティについて、N個の文字列を集約する必要があって、方法を考えてみた。

次のような部署マスタと社員マスタを例にとって、説明します。

これに対して、次のような表を出すという要件。

「2つのエンティティを JOIN すれば良いじゃん」という声もあるけど、単純に結合しちゃうと、結果セットから取り出すとき、もしくは表示時点でプログラムでごにょごにょやらないと、うまくいかない。所属社員の一覧を、改行区切りの文字列として、ひとつの変数に格納できれば、らくちん。要するに、DBから値を取得する時点で、表示するときの形まで持っていきたいという話。

ここの方法については賛否両論あるかもしれないけど、SQLはプログラムが最もシンプルで済むような結果が得られるように書くべきだと思ってます。

話が逸れたけど、じゃあ、それをどうやってやるか、という話ですよ。

続きを読む "PostgreSQLの配列を使って文字列を集約をする方法"

PostgreSQLで、同一イベントに複数のトリガーがある場合に、どういう順番で実行されるのか気になったので調べてみた。

トリガ動作の概要 同一リレーション、同一イベントに対して1つ以上のトリガが定義された場合、トリガはその名前のアルファベット順に発生します。 BEFOREトリガの場合では、各トリガで返される、変更された可能性がある行が次のトリガの入力となります。もし、あるBEFOREトリガがNULLを返したら、操作はその行で中断し、残りのトリガは発生しません。

アルファベット順、これは予想外だった。以前は順番を保証することさえなかったみたいだから、それに比べたら方法があるだけマシなのかもしれないけど、トリガ名を考えないといけないということか。なんか明示的な制御の方法はないのかな。

メモ。

C:\>bcp DB.dbo.TABLE out c:\test.csv -c -t "," -S localhost\sqlexpress -U sa

こんな感じ？

でも、これだと単純にデータ間にカンマを入れるだけなので、厳密なCSVではないんだよなあ。データ中にカンマが入るような場合だと、カラムの区切り文字と区別つかなくなっちゃうから、本来はカラムごとに引用符付きで出力したいんだけど、bcp ユーティリティでは方法が分からず。フォーマットとか使えば良いのかもしれないけど、テーブルごとに用意するのでは、面倒っちいです。

やっぱり、自分でDBを読み込んでCSVで書き出すようなプログラムを書かないとダメですかね。うーん、Windows は苦手なので逡巡します。

Excel で作成したデータを DB に取り込むシーンって、意外とあるんだけど、これが厄介。

そういうとき自分は、Excel データを csv 形式で出力してから、それを postgresql に COPY で取り込むような流れでやってるんですが、

COPY test FROM '/home/postgres/test.csv' WITH delimiter ',' null '';

こういう風にすると、データ中に「"aaa,bbb"」みたいな項目があると、そのカンマにも反応しちゃってカラムが区切られてしまうので、

ERROR:  extra data after last expected column

なんて怒られてしまう。

どうしたもんかなーと思って調べていたら、いつの間にやら COPY は CSV 対応している模様。

COPY test FROM '/home/postgres/test.csv' WITH CSV;

これでOKでした。

CSV への対応は PostgreSQL 8系以降でのサポートっぽい。ちょっとしたことですが、これは便利。

集合を返す関数 generate_series

今さら知ったんですが、こんな便利な関数があったんですね。PostgreSQL8.0から追加された関数らしく、これを使えば連番やカレンダーテーブルを簡単に作成することができる。うわー、もっと早く知っておきたかった！朝から大興奮！

例えば、1～10までの項番を作成する場合。

SELECT * FROM generate_series(1,10);


 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)

今週の日曜～土曜の一覧を生成する場合。

SELECT
    current_date + s.a AS date
FROM
    generate_series(
        (SELECT 0-extract(dow FROM current_date))::int,
        (SELECT 6-extract(dow FROM current_date))::int
    ) AS s(a);


    date
------------
 2008-04-13
 2008-04-14
 2008-04-15
 2008-04-16
 2008-04-17
 2008-04-18
 2008-04-19
(7 rows)

これまでプログラムでごりごり頑張っていた部分のいくつかは、generate_series を使うことで、SQLだけで書けるようになる。プログラムで SQL の足りないところを補おうとすると、非常にややこしや～なことになりがちなので、AP と DB の関係を疎にするアイデアとして、generate_series は良いと思います。PostgreSQL の方言というのが、たまにキズですが…。

plpgsql で SELECT 文を発行して、その結果を変数に代入したいときは、SELECT INTO 構文を使用する。

CREATRE OR REPLACE FUNCTION test() RETURNS int AS
'
DECLARE
    ct int;
BEGIN
    SELECT INTO ct count(*) FROM table;
    return ct;
END
'
language 'plpgsql';

このように書くと、count(*) の値が、宣言済み変数 ct に代入できる。

複数の結果をそれぞれ変数に代入したいときは、

DECLARE
    one int;
    two int;
BEGIN
    SELECT INTO one,two count(one),count(two) FROM table;
    RETURN one * two;
END

といった感じで並べればOK。

まあ、取り扱うカラム数が多い場合には、不向き。そういう場合は、RECORD で受け取ったりすると良いのかな。

さーて、もりもり CREATE FUNCTION でもするかと思ったら、こんなエラー。

ERROR:  language "plpgsql" does not exist

PostgreSQL を標準インストールすると、plpgsql は入らないんだろうか。plpgsql を後から追加するときは、コマンドラインから次のようにするらしい。

$ /usr/local/pgsql-8.3.0/bin/createlang -d database_name plpgsql

これで無事に CREATE FUNCTION できました。わーい。忘れそうなので、メモ。

Oracleでシーケンスを取得する際には

SELECT sequence.nextval FROM dual;

というように、DUAL表を使うのだけど、同じことを PostgreSQL で行う場合には、

SELECT nextval('sequence');

というように、FROM句なしで事足りてしまう。

両者の差は、方言だということで納得するしかないんだけど、やっぱりFROM句が無いというのは、違和感がある。もちろんこれはシーケンスの取得に限った話じゃなくて、全般的に。論理的な意味合いとして、「FROM句あってのSELECTじゃないの？」という気分。まあ、DUAL表にも意味はないわけで、「ムダなもんは排除しようよ」っていう流れは分かる。

まあ、あまりDBのコアな仕様を把握していないので、あまり尊大なことは言えない。FROM句の有無というインタフェースの問題以上に、内部では大きな差があるような気もするなあ。

テーブルのカラム数が増えていくと、DB性能が悪くなる気がする。

そんなメンバーの声を聴いて、そういえば自分も実体験として、100カラムほどあるテーブルの性能問題にぶつかったことがあったことを思い出した。やたらとSELECTが重い。とは言え、その原因がカラム数にあるというのは眉唾。

実際に手元にあった PostgreSQL8.1 で試してみることにした。

続きを読む "カラム数が増えるとDB性能が劣化する？"