mysql - skip-show-database

MySQLでのSHOW DATABASESコマンド権限の管理。

バージョンの表示と、テスト用データベース'sample'の作成。

$ mysql -u root

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.0.51-log | 
+------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)

fooユーザを作成し、SELECTのみ許可する。

$ mysql -u root

mysql> GRANT Select ON *.* TO foo@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mysql.user WHERE `User` = 'foo'\G
*************************** 1. row ***************************
                 Host: localhost
                 User: foo
             Password: 
          Select_priv: Y
          Insert_priv: N
          Update_priv: N

          ...

         Show_db_priv: N
           Super_priv: N

          ...

1 row in set (0.00 sec)

fooユーザにShow_dbの権限は無いはずなのに、SHOW DATABASESコマンドが実行でき、全てのデータベースが見れてしまう。

$ mysql -u foo

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| sample             | 
| test               | 
+--------------------+
4 rows in set (0.00 sec)

fooユーザを再作成し、'sample'データベースでのSELECTのみ許可する。

$ mysql -u root

mysql> DELETE FROM mysql.user WHERE `User` = 'foo';
Query OK, 1 row affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT Select ON sample.* TO foo@localhost;
Query OK, 0 rows affected (0.00 sec)

先程と同様fooユーザにShow_dbの権限は無く、SHOW DATABASESコマンドも実行できる。しかし、権限の無い'mysql'データベースは見えなくなる。('information_schema'や'test'が見えるのはよくわからない。)

$ mysql -u foo

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| sample             | 
| test               | 
+--------------------+
3 rows in set (0.00 sec)

次に、my.cnfに'skip-show-database'を追加し、mysqlを再起動する。

$ vi /path/to/my.cnf

[mysqld]

...

skip-show-database

...

fooユーザでSHOW DATABASESコマンドを実行すると、拒否するようになったことが分かる。'skip-show-database'がONで、Show_db権限がない場合に、SHOW DATABASESコマンドは拒否される模様。

$ mysql -u foo

mysql> SHOW DATABASES;
ERROR 1227 (42000): Access denied; you need the SHOW DATABASES privilege for this operation

確認のために、Show_db権限をfooユーザに与える。

$ mysql -u root

mysql> UPDATE mysql.user SET Show_db_priv = 'Y' WHERE `User` = 'foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

SHOW DATABASESコマンドを実行すると、全データベースが表示される。

$ mysql -u foo

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| sample             | 
| test               | 
+--------------------+
4 rows in set (0.00 sec)

一般ユーザはSHOW DATABASESコマンドは必要ない(自分が使用するデータベースだけ知っていればいい)ので、'skip-show-database'は常にONにしておいたほうが良さそう。で、Show_db権限は与えないこと。

SHOW PROCESSLISTやSHOW VARIABLESとかも禁止にするにはどうするんだろう。小一時間情報を探してみたけれど、わからなかった。できないのかもしれない。

Use sequence in each database

MySQLはカラムをAUTO_INCREMENTで定義すれば、新規連番を振ってくれる。振られた連番はLAST_INSERT_ID()関数で取得できる。

CREATE TABLE foo
(
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

mysql_query("INSERT INTO foo(name) VALUES('test')", $conn);
$res = mysql_query("SELECT LAST_INSERT_ID() AS id", $conn);
$row = mysql_fetch_assoc($res);
var_dump($row["id"]);

PostgreSQLはカラムをSERIAL、またはBIGSERIALで定義すれば、新規連番を振ってくれる。振られた連番はLASTVAL()関数で取得できる。

CREATE TABLE foo
(
  id SERIAL NOT NULL,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

pg_query($conn, "INSERT INTO foo(name) VALUES('test')");
$res = pg_query($conn, "SELECT LASTVAL() AS id");
$row = pg_fetch_assoc($res);
var_dump($row["id"]);

SQLiteはカラムを"INTEGER PRIMARY KEY"または"INTEGER NOT NULL PRIMARY KEY"で定義すれば、新規連番を振ってくれる。振られた連番はPDOのlastInsertId()メソッドで取得できる。

CREATE TABLE foo
(
  id INTEGER PRIMARY KEY,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

$stmt = $pdo->prepare("INSERT INTO foo(name) VALUES('test')");
$stmt->execute();
var_dump($pdo->lastInsertId());

Firebirdではシーケンス(旧ジェネレータ)を作成し、GEN_ID()関数にそのシーケンス名を渡し新規連番を取得する。その値でINSERTする。

CREATE TABLE FOO
(
  ID INTEGER NOT NULL PRIMARY KEY,
  NAME VARCHAR(24) NOT NULL
)

CREATE SEQUENCE FOO_ID_SEQ;

-------------------------------------------

$res = ibase_query($conn, 'SELECT GEN_ID(FOO_ID_SEQ, 1) AS ID FROM RDB$DATABASE');
$row = ibase_fetch_assoc($res);
$lastId = $row["ID"];
ibase_query($conn, "INSERT INTO FOO(ID, NAME) VALUES({$lastId}, 'test')");
var_dump($lastId);

Oracleではシーケンスオブジェクトを作成し、NEXTVALにより新規連番を取得する。その値でINSERTする。

CREATE TABLE FOO
(
  ID INTEGER NOT NULL PRIMARY KEY,
  NAME VARCHAR(24) NOT NULL
)

CREATE SEQUENCE FOO_ID_SEQ;

-------------------------------------------

$stmt = oci_parse($conn, "SELECT FOO_ID_SEQ.NEXTVAL AS ID FROM DUAL");
oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
$row = oci_fetch_assoc($stmt);
$lastId = $row["ID"];
$stmt = oci_parse($conn, "INSERT INTO FOO(ID, NAME) VALUES({$lastId}, 'test')");
oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
var_dump($lastId);

SQL ServerはカラムをIDENTIFYで定義すれば、新規連番を振ってくれる。振られた連番はSCOPE_IDENTITY()関数で取得できる。

CREATE TABLE foo
(
  id INTEGER IDENTITY(1, 1) NOT NULL,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

mssql_query("INSERT INTO foo(name) VALUES('test')", $conn);
$res = mssql_query("SELECT SCOPE_IDENTITY() AS id", $conn);
$row = mssql_fetch_assoc($res);
var_dump($row["id"]);

SQL Server - Default Constraint

SQL Serverはカラムのデフォルト値を制約として扱っているため、時々面倒なことがある。例えばデフォルト値が設定されているカラムを削除する場合にエラーになる。

先にデフォルト制約を削除し、それからカラムを削除する。
デフォルト制約の削除には制約名が必要で、それは以下のようなSQLで取得できる。

SELECT dc.name
  FROM sys.schema s
    INNER JOIN sys.objects obj ON obj.schema_id = s.schema_id
    INNER JOIN sys.columns cols ON cols.object_id = obj.object_id
    INNER JOIN sys.default_constraints dc ON dc.object_id = cols.default_object_id
  WHERE s.name = 'SCHEMA_NAME'
    AND obj.name = 'TABLE_NAME'
    AND cols.name = 'COLUMN_NAME'

取得した制約名で、デフォルト制約を削除する。

ALTER TABLE TABLE_NAME DROP CONSTRAINT DEFAULT_CONSTRAINT_NAME

その後、カラムを削除する。

ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME

mysql error "Incorrect information"

mysqlで以下のエラーが発生し、再起動できない場合の対処。

ERROR 1033 (HY000): Incorrect information in file: ...

"innodb_log_file_size"の値を増やすとこうなる。
innodbのトランザクションログ(ib_logfile)をどこかに退避するか削除してしまう。

その後、再起動する。

小さくした場合は起動はできるがSELECTすると同様のエラーが発生する模様。
なので、同様の作業をすること。

PHP & Oracle 10g XE

Windows & PHP & Oracle 10g XE

Oracleを'C:\Oracle'にインストールする。

Oracle Instant Client(instantclient-basic-win32-10.2.0.3-20061115.zip)をダウンロードし、展開する。

C:\Oracle\instantclientフォルダを作成する。

展開したフォルダ内のoraociei10.dll、orannzsbb10.dll、oci.dllの3つのファイルを、作成したinstantclientフォルダにコピーする。

C:\Oracle\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.oraを作成したinstantclientフォルダにコピーする。

環境変数(コントロールパネル->システム->詳細設定->環境変数)の設定でinstantclientフォルダへのパスをPATHの先頭に追加する。
ユーザ環境変数の「新規」でNLS_LANG変数を作成し、値を'Japanese_Japan.AL32UTF8'にする。
また、TNS_ADMIN変数を作成し、値を'C:\Oracle\instantclient'にする。

php.iniでOCI8をロードする。

extension=php_oci8.dll

接続する。

$conn = oci_connect("USERNAME", "PASSWORD", "//localhost/XE", "AL32UTF8");

PHP & SQL Server 2005 XE

Windows & PHP & SQL Server 2005 XE

Microsoftから下記のものをダウンロード&インストール。

・SQL Server
SQL Server 2005 Express Edition Service Pack 1
・管理ツール
SQL Server Management Studio Express

SQL Serverのインストール時の「認証モード」の選択では、接続ユーザをWindowsアカウントと関係なくするため「混合モード」を選択する。
照合順序の設定では補助文字(サロゲートペアによる文字)を扱えるようにするため、「Japanese_90」を選択する。また、普段使用するデータベースに合わせ、「大文字と小文字を区別する」「アクセントを区別する」「かなを区別する」「文字幅を区別する」にチェックを入れる。これは'Japanese_90_CS_AS_KS_WS'となる。CSとかASとかは以下の通り。

・CI => Case Insensitive
    大文字・小文字を区別しない。
・CS => Case Sensitive
    大文字・小文字を区別する。
・AI => Accent Insensitive
    濁音などを区別しない。(「ハ」=「バ」=「パ」)
・AS => Accent Sensitive
    濁音などを区別する。
・KS => Kana Sensitive
    ひらがなとカタカナを区別する。
・WS => Width Sensitive
    文字幅(全角・半角)を区別する。
・BIN => Binary
    すべて区別する。

管理ツールを起動する。
起動時に「ファイル C:\WINDOWS\〜\...\mscorlib.tlbを読み込むことができませんでした。」のようなメッセージが出る場合はレジストリを編集する。

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\TlbAutoRepair\mscorlib.tlb

TlbPathのパス(データ)の%CLRVERSION%を'v2.0.50727'に変更する

管理ツールを起動しSQL Server認証を選択し、saアカウントで接続する。
オブジェクトエクスプローラの「セキュリティ」->「ログイン」を右クリックし、新しいログインをクリック。
ログイン名'develop'を入力し「SQL Server認証」を選択しパスワード'develop'を入力。「パスワードポリシーを適用する」のチェックを外し、「規定の言語」をJapaneseにする。

オブジェクトエクスプローラの「データベース」を右クリックし、新しいデータベースをクリック。
データベース名'test'を入力し、所有者を先程作成した'develop'にする。

オブジェクトエクスプローラの「セキュリティ」->「ログイン」->「develop」を右クリックし、プロパティをクリック。
規定のデータベースを先程作成した'test'にする。

・SQL Serverとphpの連携。
デフォルトの設定(php.ini)だと4096バイトより大きいデータがphpから書き込めないので、そのサイズを大きくしておく。また、通常のDATETIME形式で取得できるようにするための設定もする。

mssql.textlimit = 2147483647
mssql.textsize = 2147483647
mssql.datetimeconvert = Off

接続にmssql拡張を使用する場合。

extension=php_mssql.dll

$conn = mssql_connect(".\SQLEXPRESS", "develop", "develop");

dllがロードできない場合はコンソールでphpを実行してみて、「ntwdblib.dllが見つからないため〜」のようなメッセージが出るなら、http://www.webzila.com/からntwdblib.dllをダウンロードしC:\WINDOWS\system32に置く。

接続にPDO_ODBC拡張を使用する場合。

extension=php_pdo_odbc.dll

$pdo = new PDO("odbc:Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=test;Uid=develop;Pwd=develop;");

Firebird Service Setting

PHP Warning:  ibase_connect(): Unable to complete network request to host "localhost". Failed to locate host machine. Undefined service gds_db/tcp.

Windowsで上のようなエラーが出る場合の対処。

C:\WINDOWS\system32\drivers\etc\SERVICES

...

gds_db          3050/tcp

...

replicate by pgpool

>> 構成
pgpool - 192.168.0.50
master - 192.168.0.11
secondary - 192.168.0.16

$ vi /usr/local/pgpool/etc/pgpool.conf
listen_address = '192.168.0.50'
port = 5433

backend_host_name = '192.168.0.11'
backend_port = 5432

secondary_backend_host_name = '192.168.0.16'
secondary_backend_port = 5432

replication_mode  = true
load_balance_mode = true

>> ベンチマーク
プライマリキーで取得するSELECTを3回発行するプログラム。
プライマリキーは1〜100000の中からランダムに生成。
データは10万件。

$ ab -n 500 -c 100 -k http://www.example.com/testapp

[ 単一サーバ(192.168.0.11) ]

Requests per second:    247.18 [#/sec]
Time per request:       4.046 [ms]

Requests per second:    273.41 [#/sec]
Time per request:       3.658 [ms]

Requests per second:    269.48 [#/sec]
Time per request:       3.711 [ms]

[ load barancing & connection pooling ]

Requests per second:    261.33 [#/sec]
Time per request:       3.827 [ms]

Requests per second:    261.51 [#/sec]
Time per request:       3.824 [ms]

Requests per second:    256.63 [#/sec]
Time per request:       3.897 [ms]

後者の方が気持ち速いが、ほとんど変わらず。コネクションプーリングによるパフォーマンス向上がバランサ(というかpgpool自体)のオーバーヘッドで相殺されている感じ。
パフォーマンスが変わらずフェイルオーバーできるというのは結構良いかもしれない。

postgresql logging

PostgreSQLでログ(クエリログ)を採る方法。

PostgreSQL 8.3.0

"log_statement"を有効にする。

$ vi /usr/local/pgsql/data/postgres.conf

log_statement = 'all'

ログディレクトリを作成。

$ mkdir /usr/local/pgsql/logs

起動時にログファイルを指定する。

$ cd /usr/local/pgsql
$ ./bin/pg_ctl -o -i -D ./data -l logs/pglog.log start &

mysql export as csv

セル内の改行コードと行区切りのための改行コードのアレがうまくいかず、とりあえずstr_replace()で対処することに。
改行コードはとりあえずwindowsに合わせ'\r\n'に。

SQLでエクスポート

SELECT * FROM tblname
  INTO OUTFILE '/path/to/output.csv'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n';

なんかおかしいセル内改行コード置換

$contents = file_get_contents("/path/to/output.csv");
$replaced = str_replace("\\\r\n", "\r\n", $contents);