mroongaのデータを壊してテーブルも消せなくなったのを何とかした風

先週末ハマった件のメモ書き。
これで合っているかどうか保証も何も全く無いので
参考にする場合は自己責任で。

mroongaをストレージモードで使った開発中、
安直な設計でテーブル作って膨大なデータのinsertクエリを実行してしまった。

全然戻ってこないのでクエリのキャンセルをしたのだがそれでも戻ってこず。
mysqldのrestartも実行したが戻ってこず。
仕方なくkill -9で抹殺。

その後再起動し、この設計じゃダメだなと思いdrop tableしようとしたら
drop tableも戻ってこない。

プロセスリストを見てみると

select count(*) into @discard from `information_schema`.`PARTITIONS`

というSQLをdebian-sys-maintが実行していて全然戻ってきていない。
こいつがロックしている線が濃厚。


当該のSQLやdebian-sys-maintでググってみると
「debian系のおせっかいだからスクリプトいじって黙らせちゃえよ」
みたいな記事が見つかるが、
それもなんだかイカン気がする。

やけくそでmysql止めて

/var/lib/mysql/【スキーマ名】.mrn.*

のファイルを全消しして
再起動してdrop tableしてみる。
瞬時にdisconnected from server的なメッセージが出て、
mysqldのプロセスIDが変わってる。
悪化／（^o^）＼

repair tableとかあがいてみる。
「このエンジンはリペアとかないよ」的なメッセージが出る。

そこでふと、以前mysqlのバージョンだけ上がってmroongaプラグインが読み込めなくなって
mysqlの起動が出来なくなってヒイコラした時、
その後drop tableは普通にできたよなーという記憶が。

ということでUNINSTALL PLUGINして、dropしてみる。
…成功！


あらためてINSTALL PLUGINして、テーブル作ってまともな量insert。
無事動作している模様。


多分こうなった場合、

1. UNINSTALL PLUGIN
2. drop table
3. mrnファイル削除
4. INSTALL PLUGIN
5. create table

とすればいいのかなという気がしているけど
実はまずいのかもしれない。不明。


やってみる場合自己責任で。

mysqldumpでlocalhostのポート指定

リモート環境のmysqlをsshポートフォワード経由でlocalhost:13306にマップして接続し、
ローカル環境のmysqlとスキーマ等の違いを確認するためにmysqldumpを使った。
manを見る限り、ポート番号は -P または --port= で指定出来ると書いてある。

mysqldump -h localhost -P 13306 -u XXX -p XXX -d --compact XXX > out.sql

そしてローカルのmysqlに対し同様に出したsqlと比較。

奇跡の完全一致。

そんなバカな。

ローカルのmysqlを停止させて実行してみる。

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

やっぱり。

エラーメッセージからするに、ドメインソケットを使ってるっぽい。
-P 指定していてもお構いなしっていうのはどっちかというとバグなんじゃないのかな。

強制的にTCPを使用させればうまくいく。

mysqldump --protocol=TCP -h localhost -P 13306 -u XXX -p XXX -d --compact XXX > out.sql

mysql5.5のcharset指定

ubuntu 12.04のmysqlは5.5。

DBのデフォルトの文字コードをutf8にするのに前までは
/etc/mysql/conf.dの下に charset.cnf とか適当な名前で

[mysqld]
default-character-set=utf8

と書いていたけど、5.5だとエラーになってしまう。

[mysqld]

character-set-server=utf8 

に書き換える。

Ubuntuにmecab+groonga+mroongaをソースからインストール

Ubuntuにmecab+groonga+mroongaをソースからインストールする。

mroongaのサイトにある手順ではリポジトリ追加してapt-get install、で
それはもう簡単にインストールできたのですが
その後mysqlがアップデートされたらうまく動かなくなってしまったので
ひと通りソースからビルドしました。

以下はバイナリやライブラリは/usr/bin/,/usr/lib/など、
confは/etc/に配置する手順です。

1. ビルドに必要なものをインストール
   

   sudo apt-get install build-essential
   sudo apt-get install libtool
   sudo apt-get install autoconf
   sudo apt-get install apt-src
   sudo apt-get install libmysqlclient-dev

2. mecabをビルドしてインストール
   
   http://mecab.googlecode.com/svn/trunk/mecab/doc/index.html#download
   ここからのリンクでソースをゲット。
   今はmecab-0.993.tar.gzでした。

   tar zxf mecab-0.993.tar.gz
   cd mecab-0.993
   ./configure --prefix=/usr \
       --sysconfdir=/etc \
       --with-charset=utf8
   make
   sudo make install
   sudo ldconfig

3. mecabの辞書をビルドしてインストール
   
   上のリンク先の「IPA辞書」をダウンロード。
   今はmecab-ipadic-2.7.0-20070801.tar.gzでした。
   

   tar zxf mecab-ipadic-2.7.0-20070801.tar.gz
   cd mecab-ipadic-2.7.0-20070801
   ./configure --prefix=/usr \
       --sysconfdir=/etc \
       --with-charset=utf8
   make
   sudo make install

4. groongaをビルドしてインストール
   
   https://github.com/groonga/groonga/tags
   から、タグ振ってあるものをダウンロード。
   今回はgroonga-groonga-v2.0.1-0-g700532f.tar.gz
   というファイル名でダウンロードしました。
   

   tar zxf groonga-groonga-v2.0.1-0-g700532f.tar.gz
   cd groonga-groonga-4627bd1
   ./autogen.sh
   ./configure --prefix=/usr \
       --sysconfdir=/etc \
       --with-default-encoding=utf8
   make
   sudo make install
   sudo ldconfig

5. mroongaをビルドしてインストール
   
   mysqlのソースをaptでゲット。
   apt-src install mysql-server
   今回はmysql-5.1-5.1.62というディレクトリにソースが落ちました。
   
   https://github.com/mroonga/mroonga/tags
   から、タグ振ってあるものをダウンロード。
   今回はmroonga-mroonga-v2.01-0-g8378adf.tar.gz
   というファイル名でダウンロードできました。
   

   tar zxf mroonga-mroonga-v2.01-0-g8378adf.tar.gz
   cd mroonga-mroonga-1bfcbe2
   ./autogen.sh
   ./configure --prefix=/usr \
       --sysconfdir=/etc \
       --with-mysql-source=$PWD/../mysql-5.1-5.1.62 \
       --with-mysql-config=/usr/bin/mysql_config \
       --with-default-parser=TokenMecab
   make
   sudo make install

6. apparmorの設定を変更
   
   ubuntuではmysqldの動きはapparmorで制限されていて、
   このままではMecabの辞書もgroongaの設定ファイルも参照できないので、
   アクセス出来るように設定を追加します。
   
   以下の内容のファイルを作成します。
   /etc/apparmor.d/abstractions/mysql-server-mroonga
   

   /usr/lib/groonga/plugins/ r,
   /usr/lib/groonga/plugins/** rm,
   /etc/mecabrc r,
   /usr/lib/mecab/ r,
   /usr/lib/mecab/** r,

   以下のファイルに、追記します。
   /etc/apparmor.d/local/usr.sbin.mysqld
   

   #include <abstractions/mysql-server-mroonga>

7. mysqlにプラグインを登録
   
   mysqlにログイン。
   

   mysql -uroot -p
   password:

   プラグイン登録。
   

   mysql> INSTALL PLUGIN mroonga SONAME 'ha_mroonga.so';

   funtionの登録。
   

   mysql> CREATE FUNCTION last_insert_grn_id RETURNS INTEGER soname 'ha_mroonga.so';

 以上で、動くようになりました。

この情報は2012/04/26頃の情報です。

Django+MySQL+Cast as SIGNED INTEGERでTruncated incorrect INTEGER valueでUnicodeEncodeErrorなところから逃げ延びる

Django+MySQLで、unicodeの全角文字と数字が混じった列のdistinctな一覧を
【数字の昇順】→「文字順」で取得したい。
単純に文字順にしてしまうのは 1,10,11,2,3,4 になるのでだめなパターン。

QuerySet APIの distinct はPostgreSQLでしかサポートされていないので、
DBのViewを書いた。

create view app_view1 as
select distinct col1,
    case when length(col1) = 0 then 0
                when cast(col1 as UNSIGNED INTEGER) > 0 then cast(col1 as UNSIGNED INTEGER)
                else 2147483647 end as sort
from table1

ちなみに本題とはあまり関係ないけどModelはこんな感じ。

class View1(models.Model):
    col1 = models.CharField(max_length=10,blank=True,primary_key=True)
    sort = models.IntegerField()
    class Meta:
        app_label="app"
        ordering=["sort","col1"]
        managed=False

文字はsortを十分に大きくしておいた上でsort昇順→col1昇順にすればいいはず。

MySQL Workbenchからだとゴキゲンに動作する。

実際に実行する。タイトルのような文字列を叫んで憤死する。

原因としては、実際にはMySQL内で実行した際にcastで「Truncated incorrect INTEGER value：'【文字列】'」というwarningが出ており、
それをMySQLdbモジュールが拾おうとしてしまって、【文字列】部分がUnicodeになっているせいでUnicodeEncodeErrorになるという
余計な事すんなよというもの。

何とかする方法を考える。
・MySQLdbをいじる（UnicodeEncodeErrorを吐かないようにする）→できればやりたくない
・Djangoをいじる（warningを拾わないようにする）→できればやりたくない
・継承で何とかする→djangoのdb backendを作れば何とか出来るんだろうができればやりたくない
・MySQLでWarningを出さないようにできないか→MySQL Suppress Warningとかでググる。死屍累々。
・あきらめてPython上で並べ替えをする→今回はいいがこんな感じので大量のデータに対してやろうとしたらどうするよ

SQLに立ち戻る。
要は数字だけ以外の場合に、CASTさえしなければよい。

MySQLにはREGEXPがあった。

create view app_view1 as
select distinct col1,
    case when length(col1) = 0 then 0
                when col1 REGEXP '^[[:digit:]]+$' then cast(col1 as UNSIGNED INTEGER)
                else 2147483647 end as sort
from table1

何とか逃げ延びた。

MySQLでSELECT時に連番生成

OracleでいうROWNUM()みたいなやつがやりたい。

アドホッククエリとしては、こうすればできるっちゃできる。
SELECT @rownum:=@rownum+1 rownum, t.* FROM (SELECT @rownum:=0) r, table1 t;

でもこれをViewにしようとしたら、MySQLはView内でサブクエリ使えない。

詰んだ。

mysqldump使って特定の行をinsert文の形で取り出す

単なるmysqldumpの使い方メモ。

誤って消してしまったデータを別なサーバに復元したバックアップからピンポイントで戻すために使う。

mysqldump -u【ユーザ】 -p【パスワード】 【スキーマ名】 【テーブル名】 --compact -t "--where=【where句】"

パイプで繋いで、mysqlコマンドで戻し先に直接突っ込むもよし。

MySQLのテーブルを一括削除するワンライナー

モデル定義からテーブルを自動生成できるフレームワークでガチャガチャやってると、
外部キー制約とかに引っかかってうまく消えなくなったりする。

無理やり片っ端から消すワンライナー。

mysql -Br -u【ユーザ名】 -p【パスワード】 【スキーマ名】 -e "show tables;" | tail -n +2 | xargs -i{} mysql -u【ユーザ名】 -p【パスワード】 【スキーマ名】 -e "SET FOREIGN_KEY_CHECKS=0; drop table {};"

バッチモードで実行することで枠線なし、tailの行数を+2指定する事で「Tables_of_【スキーマ名】」の行を削り取る。
あとは外部キー制約外し→drop tableのコンボをひたすら叩き込む。

必要であればtailとxargsの間でgrepとかで必要なテーブルのみに絞り込む。
（というか絞り込まないんだったらdrop databaseでいいじゃんって話ではある）