@blog.justoneplanet.info

日々勉強

パーティショニングする

以下のクエリを実行する。但し、TIMESTAMP型はMySQLのバージョンによってはバグがあってTIMESTAMP型のカラムでパーティショニングできないのでdatetime型などに変更する必要がある。

ALTER TABLE `log` PARTITION BY RANGE columns (created) (
PARTITION p20130101 VALUES LESS THAN ('2013-01-01 00:00:00'),
PARTITION p20130201 VALUES LESS THAN ('2013-02-01 00:00:00'),
PARTITION p20130301 VALUES LESS THAN ('2013-03-01 00:00:00'),
PARTITION p20130401 VALUES LESS THAN ('2013-04-01 00:00:00'),
PARTITION p20130501 VALUES LESS THAN ('2013-05-01 00:00:00'),
PARTITION p20130601 VALUES LESS THAN ('2013-06-01 00:00:00'),
PARTITION p20130701 VALUES LESS THAN ('2013-07-01 00:00:00'),
PARTITION p20130801 VALUES LESS THAN ('2013-08-01 00:00:00'),
PARTITION p20130901 VALUES LESS THAN ('2013-09-01 00:00:00'),
PARTITION p20131001 VALUES LESS THAN ('2013-10-01 00:00:00'),
PARTITION p20131101 VALUES LESS THAN ('2013-11-01 00:00:00'),
PARTITION p20131201 VALUES LESS THAN ('2013-12-01 00:00:00'),
PARTITION p20140101 VALUES LESS THAN ('2014-01-01 00:00:00'),
PARTITION p20140201 VALUES LESS THAN ('2014-02-01 00:00:00'),
PARTITION p20140301 VALUES LESS THAN ('2014-03-01 00:00:00'),
PARTITION p20140401 VALUES LESS THAN ('2014-04-01 00:00:00'),
PARTITION p20140501 VALUES LESS THAN ('2014-05-01 00:00:00'),
PARTITION p20140601 VALUES LESS THAN ('2014-06-01 00:00:00'),
PARTITION p20140701 VALUES LESS THAN ('2014-07-01 00:00:00'),
PARTITION p20140801 VALUES LESS THAN ('2014-08-01 00:00:00'),
PARTITION p20140901 VALUES LESS THAN ('2014-09-01 00:00:00'),
PARTITION p20141001 VALUES LESS THAN ('2014-10-01 00:00:00'),
PARTITION p20141101 VALUES LESS THAN ('2014-11-01 00:00:00'),
PARTITION p20141201 VALUES LESS THAN ('2014-12-01 00:00:00'),
PARTITION pmax VALUES LESS THAN MAXVALUE
);

パーティションを指定してデータを削除するには以下のクエリを実行する。

Alter table log drop partition p20130101;

データはそのままでテーブルのパーティショニングを除去するには以下のクエリを実行する。

ALTER TABLE log REMOVE PARTITIONING;

以下のクエリでパーティションを確認する。

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='log';

RDSでインスタンスがブロックされMySQLに接続できなくなる

RDSを使っているとMulti-AZ DeploymentがYesになっている場合でも、以下のようにMySQLに接続できない時間が存在する。

Database connection "SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'endpoint.zone.amazonaws.com' " is missing, or could not be created.

ユーザーのアクセスごとにconnectが発生するようなシステムの場合、上述の接続できない時間の間に多数のユーザーがアクセスし、クライアントが接続エラーを繰り返す場合がある。その場合、以下のエラーが発生する。

Database connection "SQLSTATE[HY000] [1129] Host 'instance.zone.compute.internal' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'" is missing, or could not be created.

■上限値を増やす

max_connect_errorsを変更することで接続エラーの上限値を変更できる。

■ブロックを解除する

mysqladmin -h endpoint.zone.amazonaws.com -u user --password=password flush-hosts

参考

SolrでDataImportHandlerを使う

前回の続き。

■インポート元などの設定

solr/conf/data-config.xml

はてなのドキュメントが少し違っていて、以下のようにdocumentノードが無いとエラーになる。

<?xml version="1.0" encoding="UTF-8" ?>
<dataConfig>
  <dataSource
    name="dbname"
    driver="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost/dbname"
    user="username"
    password="password"
    batchSize="-1"
    useUnicode="true"
    characterEncoding="utf8"
    useOldUTF8Behavior="true"
    readOnly="true" />
  <document name="items">
    <entity
      name="table"
      dataSource="dbname"
      query="
        SELECT
          `id`,
          `key`,
          `value`
        FROM
          `table`
        WHERE
          `created` &lt; DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)
        "
      deltaQuery="
        SELECT
          `id`
        FROM
          `table`
        WHERE
          `created` &lt; DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)
        AND
          `created` &gt; DATE_SUB('${dataimporter.last_index_time}', INTERVAL '7 9' DAY_HOUR)
        "
     deltaImportQuery="
        SELECT
          `id`,
          `key`,
          `value`
        FROM
          `table`
        WHERE
          `id` = '${dataimporter.delta.id}'
        "
      transformer="ClobTransformer,DateFormatTransformer">
    </entity>
  </document>
</dataConfig>

solr/conf/solrconfig.xml

以下の記述を追加する。

  <requestHandler name="/dataimport" class="org.apache.solr.handler.dataimport.DataImportHandler">
    <lst name="defaults">
     <str name="config">data-config.xml</str>
    </lst>
  </requestHandler>

エラー1

後述のURLでインポートをすると以下のようなエラーが発生する。

Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
        at java.net.URLClassLoader$1.run(URLClassLoader.java:217)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:205)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:321)
        at java.net.FactoryURLClassLoader.loadClass(URLClassLoader.java:615)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:266)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:264)
        at org.apache.solr.core.SolrResourceLoader.findClass(SolrResourceLoader.java:378)

以下のコマンドを実行してJDBCドライバを配置する。

wget http://www.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.22.tar.gz/from/http://cdn.mysql.com/
tar xvzf mysql-connector-java-5.1.22.tar.gz
cp mysql-connector-java-5.1.22/mysql-connector-java-5.1.22-bin.jar ./lib/

■インポートの実行

以下のURLにアクセスする。

http://localhost/solr/admin/dataimport.jsp?handler=/dataimport

ちょっと不確かなのだが[debug now]の後に[full clean import]をすれば0からインデックスが構築される。構築中は古いインデックスが使われるようだ。

■差分インポートの実行

ドキュメントを見ると差分インポートの仕方もあるようなのだが、(Jetty経由で叩く?)いまいちよくわからないので、以下のようにcrontabとcurlで実行することにした。

00 * * * * curl "http://localhost/solr/dataimport?clean=false&commit=true&command=delta-import"

参考

node.jsでMySQLに接続する

MySQLサーバーから接続を切られた時は、errorイベントが発火する。一方で接続できなかった時はcallback関数の引き数にerrorオブジェクトが格納される。従って以下のように実装してみた。

var mysql = require('mysql');
var dbh = mysql.createConnection({
  "host"     : CONF['mysql']['host'],
  "user"     : CONF['mysql']['user'],
  "password" : CONF['mysql']['pass'],
  "database" : CONF['mysql']['db']
});
var handleDisconnect = function(dbh){
  dbh.on('error', function(err) {
    if (!err.fatal) {
      return;
    }
    if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
      throw err;
    }
    console.log('Re-connecting lost connection: ' + err.stack);
    util.puts('mysql::connect');
    dbh = mysql.createConnection(dbh.config);
    handleDisconnect(dbh);
    dbh.connect(dbhConnectHandler);
  });
}
var dbhConnectHandler = function(err){
  if (err) {
    util.puts('mysql::failed to connect[' + err.code + ']');
    setTimeout(
      function(){
        util.puts('mysql::connect');
        dbh = mysql.createConnection(dbh.config);
        handleDisconnect(dbh);
        dbh.connect(dbhConnectHandler);
      },
      1000 * 30// 30 seconds
    );
  }
  else {
    util.puts('mysql::connected');
  }
};
util.puts('mysql::connect');
handleDisconnect(dbh);
dbh.connect(dbhConnectHandler);

PHPとかでMySQLに繋ぐのに慣れてると発想の転換が必要になる。

他のファイルのスクリプトと接続を共有する

expressを使っているとapp.jsでの接続をroutes/index.jsでも使いたい事がある。他にも方法はあると思うが以下のようにしてオブジェクトを共有している。

app.js
require('./share').dbh = dbh;
routes/index.js
var dbh = require('./share').dbh;

その場合は以下のようにして再接続後の接続を共有オブジェクトにセットする必要がある。

var mysql = require('mysql');
var dbh = mysql.createConnection({
  "host"     : CONF['mysql']['host'],
  "user"     : CONF['mysql']['user'],
  "password" : CONF['mysql']['pass'],
  "database" : CONF['mysql']['db']
});
var handleDisconnect = function(dbh){
  dbh.on('error', function(err) {
    if (!err.fatal) {
      return;
    }
    if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
      throw err;
    }
    console.log('Re-connecting lost connection: ' + err.stack);
    util.puts('mysql::connect');
    dbh = mysql.createConnection(dbh.config);
    handleDisconnect(dbh);
    dbh.connect(dbhConnectHandler);
    require('./share').dbh = dbh;// ...(a)
  });
}
var dbhConnectHandler = function(err){
  if (err) {
    util.puts('mysql::failed to connect[' + err.code + ']');
    setTimeout(
      function(){
        util.puts('mysql::connect');
        dbh = mysql.createConnection(dbh.config);
        handleDisconnect(dbh);
        dbh.connect(dbhConnectHandler);
        require('./share').dbh = dbh;// ...(b)
      },
      1000 * 30// 30 seconds
    );
  }
  else {
    util.puts('mysql::connected');
  }
};
util.puts('mysql::connect');
handleDisconnect(dbh);
dbh.connect(dbhConnectHandler);
require('./share').dbh = dbh;

仮に(a)と(b)が存在しない場合、以下の様なroutes/index.js内では古い接続に対してクエリを発行する事になる。

dbh.query("something query", null, function (err) {
  if (err) {/*err*/}
  else {/*success*/}
});

この場合、厄介な事にcallbackが実行されず原因究明が困難となる。

CakePHPとMySQLのON UPDATE CURRENT_TIMESTAMPを使う

CakePHPにおいてON UPDATE CURRENT_TIMESTAMPを使用すると、文字列としてCURRENT_TIMESTAMPを格納しようとするので、MySQL側でTIMESTAMPとして処理できない。以下のようにすることで対処できる。

■CURRENT_TIMESTAMP

modifiedというカラム名をつければCakeが自動的に更新日時をセットするので必ずしもON UPDATE CURRENT_TIMESTAMPをセットする必要はない。

■saveメソッド

CakePHP 2系からどうも挙動が変わったようで、1.3系の手法では上手くいかなくなった。以下のように、アップデートして欲しくないカラムにfalseを指定することで対応できる。

$data['modified'] = false;

CakePHP 1.3系

付けたい場合、saveメソッドで保存するカラムを指定することで対処できる。

$this->User->save($this->data, true, array('name', 'birthday'));

上述のようにすることで$this->dataにあらゆるキーとそれに紐付く値が格納されていても、保存されるのはnameカラムとbirthdayカラムのみである。これはアプリケーションをセキュアにする目的でも使用できるので、常時使用することをお勧めする。

MySQLでテーブルをコピーする

忘れてしまうのでメモしておく。

CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;

これは以下のように異なったDB間でも行うことができる。

CREATE TABLE new_table LIKE `old_database`.`old_table`;
INSERT INTO new_table SELECT * FROM `old_database`.`old_table`;

mysqliを使ってPHPでMySQLにSSLで接続する

MySQLサーバがクライアントとは別のコンピュータにある場合、ネットワークを平文で流れるデータを途中でキャプチャされる可能性がある。そこでSSL接続することにする。

■ソース

以下のように、リソースを生成後にmysqli_ssl_setでSSLをセットしてから、mysqli_real_connectを使って接続する。

$dbh   = mysqli_init();
$isSSL = @ mysqli_ssl_set(
    $dbh,
    '/home/ssl/key.pem',
    null,
    null,
    null,
    null
);
$isConnected = @ mysqli_real_connect(
    $dbh,
    DB_HOST,
    DB_USER,
    DB_PASS,
    DB_NAME,
    DB_PORT,
    null,
    MYSQLI_CLIENT_SSL
);

MySQLサーバーをインストールして初期設定をする

■インストール

yum install mysql-server

PHPから使う場合は以下のようにphp-mysqlもインストールする。

yum install php-mysql

MySQLにアクセスする。

mysql -u root

■パスワード

アクセスできるのは良いんだがパスワードが設定されてないのはイカン。(・ε・)

SET PASSWORD FOR root@localhost=PASSWORD('fugafuga');

■データベース

ちょっとデータベースを作ってみる。

CREATE DATABASE `sample`;

■テーブル

ちょっとテーブルを作ってみる。

USE `sample`;
CREATE TABLE  `sample`.`tbl1` (
    `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR( 255 ) NOT NULL ,
    PRIMARY KEY (  `id` )
) ENGINE = MYISAM ;

■文字コード

ネットで探してもなかなか適切な文献が見当たらない。凄く深いので結論だけまとめておく。

ちょっとPHPからデータを入れてみる。

$dbh = mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME, $dbh);
$query = "INSERT INTO `tbl1`(`name`) VALUES('山田');";
$result = mysql_query($query);

phpMyAdminで見てみると以下のように文字化けする。

capture

MySQLのデフォルト文字コードはlatin1になっている為だ。アプリケーション側で表示するときは特に文字化けすることはないかもしれないが、管理上は非常に面倒なのでデフォルト文字コードをutf-8にする事を強く勧める。

my.cnf(my.ini)

以下のようにする。

[mysqld]
character-set-server=utf8
[client]
default-character-set=utf8

但しMySQLクライアントのコンパイルオプションによっては全然文字化けしまくる。従ってプログラム側での修正も必要だ。

PHP

mysql_connectを使う

mysql_connectを使用している場合は5.2.3以上が必須で、以下のようにクライアント側の文字コードを設定する。

$dbh = mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_set_charset('utf8', $dbh);
PDOを使う

PDOを使用している場合は以下のように、MySQLの設定ファイルを読み込み、ATTR_EMULATE_PREPARESをfalseにしてサーバーサイドPrepared Statementを使うようにする。

$dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME;
$dbh = new PDO(
    $dsn,
    DB_USER,
    DB_PASS,
    array(
        PDO::MYSQL_ATTR_READ_DEFAULT_FILE => '/etc/my.cnf'
    )
);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

但し、MySQL<5.1の場合はクエリキャッシュが聞かないので注意が必要だ。現時点ではPDOでクライアント側の文字コードを指定する方法は無い。

誤り
  • 「SET NAMES utf8」クエリを発行する。
  • 「skip-character-set-client-handshake」をmy.cnfに記述する。
参考

「PHP5.2.3以前」かつ「PDOを使用できない」かつ「再コンパイルできない」とき文字コードの問題を完全に解決するのは不可能とのこと。