Posted on

SQLite3 を少し使ってみる

接続

1ファイル1DB なので，ファイル名を指定して起動する．

$ sqlite3 DBFILENAME
テーブルリスト，カラムリスト

覚えるしかないか．

テーブルリスト

> .table;

カラムリスト

> pragma table_info(TABLENAME);

カラムリストの要素は順に，

cid         Integer  インデックス
name        Text     カラム名
type        Text     そのカラムの型
notnull     Integer  not null なら 1
dflt_value  Text     DEFAULT value
pk          Integer  プライマリキーなら 1
型
NULL, INTEGER, REAL, TEXT, BLOB (バイナリーオブジェクト)
日付の取扱

日付関数で文字列か数値に変換して保存する．

例： データが python の datedata=datetime(2004, 10, 1, 13, 20, 15) の時

文字列で保存する例，エポックで保存する例

datestring=datedata.strftime('%Y-%m-%d %H:%M:%S')
connection=sqlite3.connect('filename')
cursor=connection.cursor()
cursor.execute('create table tablename ( str_date text, int_date integer );')
cursor.execute('insert into tablename values (\'%s\', strftime(\'%%s\',\'%s\'));'%(datestring, datestring))
cursor.commit()

格納状態

[(u'2004-10-01 13:20:15', 1096636815)]

値の取り出し方:
以下の取り出し関数を通して取り出す

  • date -- 日付
  • datetime -- 日時
  • time -- 時刻
  • julianday -- ユリウス日
  • strftime -- その他
cursor.execute('select time(str_date),date(int_date,\'unixepoch\') from tablename')

取得結果

[(u'13:20:15', u'2004-10-01')]
Posted on

特定ドメインでアクセスされた場合のみ、移転後ページへのリンクを張った移転メッセージを表示

前使っていたドメインから現在のドメインへリダイレクトをしていたが、どうにもアクセスが完全には途絶えていないらしい。
んで、リダイレクトしているホストやリファラから、古いドメインへリンクしているサイトを特定するために、また移転したよメッセージを表示するために、ワンクッションはさむことにした。←ほぼ検索エンジンか更新されていない掲示板等であることが判明したので，rewriteはやめた
なんとなくのイメージだけで作業開始したものの、実際に作業してみるとハマりポイントがいくつかあったため、メモに残す。

やったこと

  • 旧ドメイン配下の任意ページ(存在の有無によらず)へのアクセスがあった場合、移転メッセージを提示する。
  • 同居する他のドメイン名で80番ポートにアクセスが飛んだ時は、明らかに存在するはずのルートへのリクエストのみ403を返し、他のリクエストの場合は404を返す。
  • 移転メッセージには、http[s]://新ドメイン/元リクエストのパス?元リクエストのQUERY_STRING へのリンクを貼っておく。
  • アクセスログを mysql に残す。

ハマりポイント

任意ページへのアクセスの受け方
分かりにくいけど，これはもう使ってない方法．
任意ページへのアクセスを、上の処理を行なうスクリプトへ元のGET文字列も含めて飛ばすのは意外と難しい。
.htaccess を使って、Rewrite でリクエストを書き換えると簡単にできる。ただし REFERER はこの時点で死ぬので、REFERER を取りたい場合は他の手段を講じる必要がある。
具体的な記述は、例えば

RewriteEngine On
RewriteCond %{HTTP_HOST} ^(old\.domain\.com)(:80)?
RewriteCond %{HTTPS} off
RewriteRule ^(.*)$ http://redirect.domain.com/?$1 [QSA,R=301,L]

といった感じ。
QSA は QUERY_STRING を最後に付与する指示。

php での HTTP ヘッダの返し方
php で HTTP ヘッダを返して、特定のアクセスの場合に 404 とか 403 を返す方法はいくつかある。
詳しくは php 公式ページのリファレンスを見るとして、私の環境では次のコードがうまく動いた。
あらゆる出力 (HTML の DOCTYPE や XML 宣言を含む) より前に記述しないと意味がないことに注意。

header("HTTP",true,404);
header("Content-type: text/html; charset=iso-8859-1");
include('./404.php');

404.php は、httpd の 404 ドキュメントを模したファイルで、上の .htaccess を用いた場合は QUERY_STRING に アクセスパスがついてくるので、展開して使えば、それっぽく表示できる。
apache2 の場合、404 や 403 はブラウザの設定に依らず charset が iso-8859-1 で返っていたので、ヘッダに入れた。

今時のmysql

なんか php 公式のリファレンスに mysql は古いから mysqli を使え、と書かれていたので、使うことにしたが、ちょっと梃摺った。 

$mysqli = new mysqli("DBHOST", "DBUSER", "DBPASS", "DBNAME");
$mysqli->query($query);

mysqli では、特段の理由がない限りはセッションの close をしないらしい。
$query は使いたい SQL をそのまま書いたもの。

いざ文書にしてみると、迫力のない tips だな。
他にも元アクセスのパターンによって何度か print デバッグしないといけないと思うけど、頑張れ！

Posted on

mysql のログ

一般クエリログ、スロークエリログ、バイナリクエリログ、エラーログ、があるらしい。
エラーログは、意識しなくても標準で吐くようになっていることが多いと思う。
設定は my.cnf で行なうとして、設定方法はぐぐってもらうとして、各ログについて調べた内容とか自分の運用で知ったことをメモっておく。

エラーログ
mysql のエラー出力をログっているっぽい。
普通に稼動していると、起動時と停止時のログしか出てこない。
なんかエラー起こすとログに残るけど、その直前に実行されていた SQL 文とかサッパリ不明。
ざっと 1ヶ月くらい運用していて、サーバが DB エラーで 3 回～ 4 回くらい停止したのだけれど、クラッシュしたよ、ってメッセージしか残ってなくて原因がワケワカメ。
だみだこりゃ。
一般クエリログ
発行された全SQL文を残すらしい。これをオンにして再起動かけたら、一発でログのサイズが 6KB, 100行以上 とかになる。
これを吐きながら、月に 3回程度のエラーを待つのは容量の面やいざエラーが起こった時の解析量から見て面倒くさい。
多分、最後の手段。
スロークエリログ
インデックスを使わなかったクエリ、とか 1回の実行に○秒以上要したクエリ、とかを残せる。
中期的に観察するのに、ログを間引くのにはいいかも。でもこれで原因が特定できなかったら一般エラーログに託すことになるんだろーなー。
ちゃんとインデックスを作ってないと、めっちゃログ出るっぽい。

なんかインデックスを適切に作るには、explain select ほげほげ って感じでやるらしい。
ログ見て、特定のsql文が沢山出てきたら、ちゃんと調べよう。

バイナリログ
トランザクションとかの情報らしくって、DB のレプリケーションとか、復元後、最新状態までロールフォワードしたりするのに使われるらしい。
ファイル出力の設定が my.cnf にコメントアウト状態で書かれているけど、web で調べると DB 内に溜まったバイナリログを見たり消したりする方法が出てくる。
そも、他のログも DB 内に溜められるはずだけど。
まー個人運用レベルでは必要なさげ。例えば我が家では、定期的に mysqldump して、別 HDD に書き出している。とりあえずうちの鯖くらいなら、これで良いと思う。
Posted on

mysql 設定メモ

追記2
mysql のデータファイルを別パーティションにうつしたので追記。
例えば、/path/mysql に移すことにする。

# service mysql stop
# mv /var/lib/mysql /path/mysql
# vi /etc/mysql/my.cnf

- datadir = /var/lib/mysql
+ datadir = /path/mysql

# vi /etc/apparmor.d/local/usr.sbin.mysqld

+ /mnt/mysql/ r,
+ /mnt/mysql/** rwk,

# service apparmor restart

↑し忘れてもちゃんと動いたので、必要かは不明、いちおう後でやっといた

# service mysql start

なお、UNIX ソケットファイル socket = hogehoge を ramdisk とか早いディスクに移しても意味がないらしい。ファイルを実際に read/write するわけではないから、だそうな。

追記
mysql を 5.5 にして、チューニングしたので追記。
詳しくは、これだけはやっておきたいMySQL 5.5系の設定 - uehatsu's tech blog

character-set-server    = utf8
skip-character-set-client-handshake
default-storage-engine  = InnoDB
collation-server        = utf8_general_ci
innodb_file_format      = Barracuda
innodb_file_per_table   = 1
innodb_use_sys_malloc   = 0

先のページの通りだとエラーが出るので、default-storage-engine はIとDBをキャピタルに。(というか、5.5系ではもともと、InnoDB がデフォルトらしいので、不要っぽい)
また、innodb の独自ヒープを使えないよ、というエラーがなんか出て気持ち悪いので、innodb_use_sys_malloc=0 を追加した。

手順

  1. バックアップ。 
    # mysqldump -u USER -p --opt DBNAME --net_buffer_length=8000 | gzip > GZIPFILE

    オプションの順番はテキトー

  2. DB削除。
    上の手順で--opt をつけていれば、drop table は関係ない。
    どこまでやるかは、どれだけスッキリしたいかで変えれば良いと思う。
    mysql の該当テーブルを消す。(drop table TBLNAME;)
    データベースごと消すのもあり。(drop database DBNAME;)
    mysql を完全に消すのもあり。(apt-get purge mysql-hogehoge、hogehoge は、色々あるやつ全部)
  3. mysql 5.5 を入れる。
  4. 設定変更。
    /etc/my.cnf へ追記する内容。

    character-set-server=utf8
skip-character-set-client-handshake
default-storage-engine=innodb
collation-server=utf8_general_ci
innodb_file_format=Barracuda
innodb_file_per_table=1
  5. mysql 設定反映と確認。 
    # service mysql restart
# mysql -p
> show variables like 'inno%';
  6. mysql を purge した場合、php5-mysql も消えてるかも知れんので再インストール。 
    # apt-get install php5-mysql
# service apache2 restart
  7. リストア。
    mysql の初期設定とか、DB 作ったりとか、dump した sql ファイルの取り込みは下の旧エントリを確認のこと。

これで、もと通りのはず。
でも、CPU の非力なマシンなので、未展開の領域にアクセスした時、応答が恐しく遅くなる。
あんまり酷かったら、このオプション (Barracuda) は外すかも…

追記、ここまで。

apache2 の設定メモ の続編．
mysql の設定とか．

設定

  • # mysql_secure_installation

    で初期設定．基本，全部 Y で良い．root パスをインストール時からイチイチ変えるのがメンドイ時は，最初だけn．

  • 全てのユーザー情報が削除されているため，root ログインのみ可能． 

    # mysql -u root -p
  • ユーザの追加． 
    grant all privileges on databasename.* to username@localhost identified by 'password';
show grants for username@localhost;
flush privileges;
  • ユーザーの DB を作成． 
    $ mysql -u username -p
> create database databasename;
  • データ移行． 
    $ mysql -u username -p databasename < sqlfile

その他のtips

/etc/my.cnf の [mysqld] ディレクティブに

character-set-server    = utf8
collation-server        = utf8_general_ci

を追記すると、サーバー文字コードは変更可能
DB文字コードは、mysqlクライアント内で目的のDBに接続して、

alter database DBNAME character set utf8;

文字コードの調べ方は、
mysql クライアント内で

> status

または、

> show variables like "chara%";

1行が長すぎてエディットが難しいときは、

# mysqldump -u USERNAME -pPASSWD --opt --default-character-set=utf8 DBNAME --net_buffer_length=8000 | gzip > sql.gz

としてやれば、1行毎の文字数が 8,000 文字以下となるよう出力してくれる。

ダンプした sql ファイルの取り込みは、クライアントから

> source SQLFILE;

テーブルの文字コードを調べるには、show create table TBNAME; でテーブル作成コマンドを見る。
同じように、show create database DBNAME; でデータベース作成コマンドも見れる。

Posted on

自宅にサーバを立てる 第3弾

Local Area Network
Local Area Network

一応、DMZ を設けてみた。
右図みたいな感じ。
詳しい設定は秘密だよ。

  • DB として、mysql をインストール。
    • mysql_secure_installation で初期設定。
    • test database はいらない
    • root の remote login は off にしても、ssh コンソールからは普通に使える
  • localhost のユーザー用DBを作る。
    • 権限付与。不要な権限を削除。そして適用。
      grant all privileges on databasename.* to username@localhost identified by "password";
      revoke all privileges on test.* from username@localhost;
      flush privileges;
    • で、該当ユーザーになって、
      mysql -u username(アカウント名と同じなら省略可) -p
      create database databasename;
      とすれば DB 自体は作成完了。
  • ヨソから DB の出力を持ってきた場合は、
    mysql databasename -p < sqlfile

外から sql 叩くのに、ssh の口を空けないためには、phpmyadmin とかを入れると良い。
apt-get installphpmyadmin
dbconfig-common yes
その後、/etc/phpmyadmin/apache.conf で /phpmyadmin を alias している行を任意のディレクトリへ変更すること。
また、最近のアップデートを当てたところ、/usr/share/phpmyadmin のディレクティブに Allow from 行を記載しないとアクセスできなくなってしまった。なんでやねん。

おまけ。
WordPress を移設した場合にも使える SQL 文集。 → WordPressで使える知ってると便利な13のSQL文 | Web活メモ帳
WordPress のリビジョン削除 → WordPress でリビジョンと自動保存を簡単に停止する方法 | ウェブル

Posted on

rooted NOOK Color に Google 日本語入力を入れたよ

ちょっとてこずったので，メモ．
今回，やりたかったことと完全に一致する記事が見付からなかったので，色んな手順をマージしている．
バグった時に自己復帰できない人は手を出さないこと…ってそんな人は NOOK Color なんかに手を出してねーか．

これやると， android 2.2 のコアな部分の設定が少しだけ垣間見れるので，やって良かったと思う．

ちなみに，hadacchi の大好きな OpenWnnニコタッチ対応版 を使う場合は，com.hiroshica.android.input.nicownn2/NicoWnnJAJPでいいと思うけど，めんどくせーので試してない．
sourceforge の方は DL したこともないので知らない．

前提

  • ファームは1.3.0．（1.1 以下なら， autonooter が楽らしい．）
  • microSD は 3.8GB 以上のものを用意する．
    後に紹介する CMW のイメージサイズで十分である．具体的には，1GB の microSD で良い．

root化

  1. CrockworkMod (1gb CWM 3.2.0.1 sdcard) を DL
    win32diskimager を DL
    ( 参照 [GUIDE] Install CM7 or CM9(!) + Clockworkmod to EMMC - ALL NOOKS! (Updated:1/28/12) - xda-developers )
  2. CWM の zip から img を展開
  3. win32diskimager で microSD カードへ書き込み
  4. Manual nooter を DL
    ( 参照 [NC][1.2][1.3] ManualNooter 4.6.16 - xda-developers )
  5. manual nooter を 3. で作成した microSD カードへ書き込み
  6. nook color へ，5. で作成した microSD カードを差し込み，再起動
  7. 注：CWM Recovery では， ボリュームキーで上下にカーソル移動する
    install zip from sdcard → choose zip from sdcard → 4. の zip
    怖い人は，backup を取ること．
  8. microSD を抜いて， reboot
    ( CWM Recovery の画面で，上下にループさせてると，そのうち return とかいう選択肢が出てくるので， top menu まで戻る )
  9. zeam launcher を選択すれば，OK
    ( 1.2.0 の場合，再起動に 1 回失敗するらしいが，気にせず改めて再起動をかければ良いらしい )

adb を使えるようにする

  1. Nook Color ADB Install.zip を DL
    ( 参照 [SCRIPT] Nook Color USB Drivers and ADB Setup (WinXP&7) - xda-developers )
  2. 解凍して adbfix.bat を実行
  3. デバイスマネージャからnook color を選択し，ドライバ更新
    先の 11. で解凍したフォルダの usbdrivers フォルダを指定
  4. ddms を起動
    ( 何故か，adb shell を叩いても device not found が返っていたが， ddms を起動して reset adb とか叩いたら，adb shell が通るようになった )
  5. cmd から adb shell

日本語入力をインストール

  1. Market から Google Japanese Input をインストール
  2. adb shell から
    mount -o rw,remount -t yaffs2 /dev/block/mtdblock03 /system
  3. Google Japanese Input をコピー
    adb pull /data/app/com.google.android.inputmethod.japanese-1.apk
    adb push com.google.android.inputmethod.japanese-1.apk /system/app
  4. sqlite3 を DL
    ( 参照 SQLite Home Page )
  5. adb pull /data/data/com.android.providers.settings/databases/settings.db
  6. sqlite3 settings.db
  7. update secure set value = 'com.android.inputmethod.latin/.LatinIME:com.google.android.inputmethod.japanese/.MozcService' where name = 'enabled_input_methods';
  8. adb push settings.db /data/data/com.android.providers.settings/databases
  9. adb shell から
    cd /data/data/com.android.providers.settings/databases
    chmod 660 settings.db
    chown system settings.db
  10. reboot
  11. nookcolor tool で choose keyboard から Google Japanese Input Beta を選択
Posted on

spammer counter v4 と viewer v2

当サイトでは， spammer と思しき IP アドレスからのアクセスを禁止している．しかし，禁止後もアクセスを続けるしつこい spammer が多い．
特にしつこい spammer は何か，あっさり消えた spammer は誤爆かも知れない，という観点から，アクセス禁止後も引き続き継続するアクセスを， IP アドレス毎にカウントしたい．
ということで，前回の spammer counter v3 では， MySQL を使って簡易に実装した．

今回は， IP アドレス毎にソートできるようにした．
これは，

  • ある程度の IP アドレスのレンジでアクセスを繰り返す spammer をネットワークアドレスで弾いて，.htaccess をスリムにしたい．
  • ネットワークアドレスによる指定で，過剰に絞りすぎているレンジがあれば，アクセス禁止を解きたい．

という狙いから， IP アドレスでソートをかけ，可読性を高めるためだ．

spammer counter v4

ソースコードに入る前に注意

.htaccess へ ErrorDocument 403 を追記する場合，その飛ばし先のファイルへのアクセスは可能とすること．
さもなくば， 403 の転送で無限ループが発生する．
具体的には，.htaccess へ下記を追記．hogehoge.phpの部分は，設置したphpファイル名に変更すること．

ソースコード

<?php
header('HTTP/1.1 403 Forbidden');
header('Content-Type: text/html; charset=iso-8859-1');
?>
<!DOCTYPE HTML PUBLIC '-//IETF//DTD HTML 2.0//EN'>
<HTML><HEAD>
<TITLE>403 Forbidden</TITLE>
</HEAD><BODY>
<H1>Forbidden</H1>
<?php printf('You don't have permission to access %s\non this server.',
             htmlentities(strip_tags($_SERVER['REQUEST_URI']))); ?><P>
<HR>
<ADDRESS><?php
$e = explode(' ',$_SERVER['SERVER_SOFTWARE']);
printf('%s Server at %s Port %d',$e[0],$_ENV['SERVER_NAME'],$_ENV['SERVER_PORT']);
?></ADDRESS>
</BODY></HTML>
<?php
// MySQL
$mySqlHost = 'xxx.xxx.xxx';
$dbname    = 'database_name';
$user      = 'user_name';
$password  = 'password';
$table     = 'spam_counter';

// connecting
$mobj = mysql_connect($mySqlHost,$user,$password);
if ($mobj == FALSE) { die('broken');}

// count IP addr
$addr    = $_SERVER['REMOTE_ADDR'];
$addr_pt = explode('.',$addr);
if (count($addr_pt)<4) { die('wrong address...');}

$sql_str = sprintf('insert into %s (ipaddr,number,ip1,ip2,ip3,ip4) '
                   .'values ('%s',1,%s,%s,%s,%s) on duplicate key update number=number+1;',
                   $table,$addr,$addr_pt[0],$addr_pt[1],$addr_pt[2],$addr_pt[3]);

mysql_select_db($dbname,$mobj);
$done=mysql_query($sql_str);
mysql_close($mobj);
if ($done == FALSE) { die('no response...');}

?>

説明 (前回からの差分)

IP アドレスを分割して，格納しているだけ．
カウントさせるために，主キーたる IP アドレスは保持．

spammer viewer v2

当然，テーブルが変更になるため， viewer にも変更が加わる．

ソースコード

<?php require_once './php/head.inc.php'; ?>
<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN'
    'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
<html xmlns='http://www.w3.org/1999/xhtml' xml:lang='ja' lang='ja'>
 <head><title>spammer viewer v2</title></head>
 </body>
 <h1>spammer viewer v2</h1>
 <table style='border: black solid 1px;'>
 <tr>
  <th>IP addr
   <a href='<?php echo $_SERVER['PHP_SELF'];?>?mode=ip&order=asc'>▲</a>
   <a href='<?php echo $_SERVER['PHP_SELF'];?>?mode=ip&order=desc'>▼</a>
  </th>
  <th># of access
   <a href='<?php echo $_SERVER['PHP_SELF'];?>?mode=num&order=asc'>▲</a>
   <a href='<?php echo $_SERVER['PHP_SELF'];?>?mode=num&order=desc'>▼</a>
  </th>
 </tr>
<?php
// MySQL
$mySqlHost = 'xxx.xxx.xxx';
$dbname = 'database_name';
$user = 'user_name';
$password = 'password';
$table = 'spam_counter';

$mode      = $_GET['mode'];
$order     = $_GET['order'];

if ($mode == 'num' and $order=='asc') { $oby = ' order by number ASC;';}
elseif ($mode == 'num') { $oby = ' order by number DESC;';}
elseif ($mode == 'ip' and $order=='desc') { $oby = ' order by ip1,ip2,ip3,ip4 DESC;';}
elseif ($mode == 'ip') { $oby = ' order by ip1,ip2,ip3,ip4 ASC;';}
else { $oby = ';';}

// connecting
$mobj = mysql_connect($mySqlHost,$user,$password);
if ($mobj == FALSE) { die('broken');}

mysql_select_db($dbname,$mobj);

$done=mysql_query('select * from '.$table.$oby);
//$done=mysql_query('select * from '.$table.' order by CAST(ipaddr AS SIGNED);');
mysql_close($mobj);
if ($done == FALSE) { die('no response...');}

$total = 0;
$num_host = 0;
$ostr = '';
while ( $row = mysql_fetch_row($done)) {
    $ostr .= sprintf('  <tr><td>%s</td><td style=\'text-align:right;\'>%d</td></tr>\n',$row[0],$row[1]);
    $total = $total + (int)$row[1];
    ++$num_host;
}
$ostr .= sprintf('  <tr><td>total</td><td style=\'text-align:right;\'>%d</td></tr>\n"
                 .'  <tr><td># of hosts</td><td style=\'text-align:right;\'>%d</td></tr>',$total,$num_host);
print $ostr;

?>
 </table>
 </body>
</html>

説明 (前回からの差分)

分割した IP アドレスの前から 1Byte 毎の値で，複数キーによるソートをかけているだけ．

おまけ

SQL文

テーブル作成．

create table spam_counter
(
ipaddr varchar(16) not null,
number int unsigned not null,
ip1 tinyint unsigned not null,
ip2 tinyint unsigned not null,
ip3 tinyint unsigned not null,
ip4 tinyint unsigned not null,
primary key (ipaddr)
);
Posted on

weblogの記事をSQL叩いて修正

前のblogが，何故か管理者ページから更新できなくなってしまったので，mysqlを勉強した．

update db_name set key=value where condition1;

where句で指定するエントリの特定キーだけアップデートできるんだ，と分かって安心した．
レンタルサーバ上で日本語の修正をするのは面倒そうだが，公開/非公開とか，日付変更とかなら問題ない．
日本語修正する場合は，ファイルからのリダイレクトでコマンド実行してやれば，なんとかなりそうな気がする．

Posted on

akismet の結果抽出

wordpress のコメントのDB (prefix_comments) から，comment_approved='spam' で抜ける．
なので，例えば…

select comment_author_IP,comment_author from prefix_comments where comment_approved='spam' order by comment_author_IP;

などと抜いて，
xlsでピボットテーブルを使って，頻度順に並べかえれば，特にアクセスの多いIPアドレスを抽出可能．