技術をかじる猫

適当に気になった技術や言語、思ったこと考えた事など。

HikariCP + ScalaActiveRecord + MySQL5.1 をやってみた

何故にメモかというと、HikariCP がそもそもサンプル無さ過ぎて地獄を見たから。

まずは基本的な ScalaActiveRecord アプリを作る

Play のサンプルアプリケーションをまずは作る。

    play new HikariSample

ウィザードくらいは任せた。

で、速攻、 ScalaActiveRecord を突っ込む。

build.sbt はこれ

    name := "HikariSample"
    
    version := "1.0-SNAPSHOT"
    
    libraryDependencies ++= Seq(
      jdbc,
      anorm,
      cache,
      "com.github.aselab" %% "scala-activerecord" % "0.2.3",
      "com.github.aselab" %% "scala-activerecord-play2" % "0.2.3",
      "mysql" % "mysql-connector-java" % "5.1.22"
    )     
    
    play.Project.playScalaSettings

そしたら、下記の様にまずは書く。

conf/play.plugins

    9999:com.github.aselab.activerecord.ActiveRecordPlugin

conf/application.conf

    # 下記を追加
    db.activerecord.driver=com.mysql.jdbc.Driver
    db.activerecord.url="jdbc:mysql://localhost:3306/testdb"
    db.activerecord.user="root"
    db.activerecord.password="test"
    activerecord.schema=models.Tables

で、モデルは下記の通り。

    package models
    
    import com.github.aselab.activerecord._
    import com.github.aselab.activerecord.dsl._
    
    case class Todo(name: String, description: String) extends ActiveRecord
    
    object Todo extends ActiveRecordCompanion[Todo]
    
    object Tables extends ActiveRecordTables {
      val todos = table[Todo]
      
      on(todos)(s => declare(
        s.description is(dbType("text"))
      ))
    }

controlllers/Application.scala

    package controllers

    import play.api._
    import play.api.mvc._
    import play.api.data._
    import play.api.data.Forms._
    import models._
    import com.github.aselab.activerecord.dsl._

    object Application extends Controller {

      def form = Form(mapping(
        "name" -> nonEmptyText,
        "description" -> nonEmptyText
      )(Todo.apply)(Todo.unapply))

      def index = Action { implicit request =>
        Ok(views.html.index(form, Todo.all.toList))
      }

      def post = Action { implicit request =>
        val req = form.bindFromRequest()
        if (!req.hasErrors) {
          req.value.foreach(v => {
            v.save()
          })
        }
        Ok(views.html.index(req, Todo.all.toList))
      }
    }

conf/routes

    GET     /                           controllers.Application.index
    POST    /                           controllers.Application.post
    GET     /assets/*file               controllers.Assets.at(path="/public", file)
    

そして、views/index.scala.html を

    @(form: Form[models.Todo], todos: List[models.Todo])

    @main("Welcome to Play") {

        @for(todo <- todos){
        &lt;div&gt;
            &lt;strong&gt;@todo.name&lt;/strong&gt;&amp;nbsp;
            &lt;span&gt;@todo.description&lt;/span&gt;
        &lt;/div&gt;
        }

        @helper.form(action = routes.Application.index()) {
            @helper.inputText(form("name"))
            @helper.inputText(form("description"))
            &lt;input type="submit" value="送信"/&gt;
        }
    }

実験環境を作る

実験環境を色々するのめんどいので、Vagrant するか。Vagrant 知らない人はここ参照。*1

HikariSample ディレクトリの外で適当なディレクトリを掘って Vagrant コマンド。

    vagrant init test https://github.com/2creatives/vagrant-centos/releases/download/v6.5.3/centos65-x86_64-20140116.box

デプロイめんどいから、適当に共有かけるか。

Vagrantfile 開いてごりごり

    # -*- mode: ruby -*-
    # vi: set ft=ruby :

    # Vagrantfile API/syntax version. Don't touch unless you know what you're doing!
    VAGRANTFILE_API_VERSION = "2"

    Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
      config.vm.box = "test"

      config.vm.box_url = "https://github.com/2creatives/vagrant-centos/releases/download/v6.5.3/centos65-x86_64-20140116.box"

      config.vm.network "private_network", ip: "192.168.33.10"

      config.vm.synced_folder "/Users/azalea/Documents/HikariSample", "/vagrant_data"
    end
    

で、実行環境を整えて

    vagrant up
    vagrant ssh
    sudo su -
    yum install -y mysql-server wget
    yum install -y java-1.7.0-openjdk-devel
    service mysqld start
    mysql -u root
    create database testdb
    SET PASSWORD FOR root@localhost=PASSWORD('test');
    exit

後は起動するだけ。

play とか sbt めんどいので、sbt dist しておく。

    cd /vagrant_data/
    cd /opt
    cp /vagrant_data/target/universal/hikarisample-1.0-SNAPSHOT.zip ./
    unzip hikarisample-1.0-SNAPSHOT.zip
    cd hikarisample-1.0-SNAPSHOT
    ./bin/hikarisample

http://192.168.33.100/ でアクセスできれば OK

HikariCP に乗り換える

HikariCP ってなんぞ?と思ったらここをクリック

要するに BoneCP がいつまで経ってもバグだらけだから嫌がって作ったそうな。

ScalaActiveRecord の Play plugin は playframwork からコネクションを拾ってるから、バックエンドが変わるくらいは問題なく動作できる。

まずは build.sbt を編集。

    name := "HikariSample"

    version := "1.0-SNAPSHOT"

    resolvers += Resolver.url("Edulify Repository", url("http://edulify.github.io/modules/releases/"))(Resolver.ivyStylePatterns)

    libraryDependencies ++= Seq(
      jdbc,
      anorm,
      cache,
      "com.github.aselab" %% "scala-activerecord" % "0.2.3",
      "com.github.aselab" %% "scala-activerecord-play2" % "0.2.3",
      "com.edulify" % "play-hirakicp_2.10" % "1.0.0",
      "mysql" % "mysql-connector-java" % "5.1.22"
    )     

    play.Project.playScalaSettings

次に application.conf でデフォルトの DBPlugin(BoneCP) を停止 & DB設定。

    dbplugin=disabled
    # db.activerecord.driver=com.mysql.jdbc.Driver
    # db.activerecord.url="jdbc:mysql://localhost:3306/testdb"
    # db.activerecord.user="root"
    # db.activerecord.password="test"

    db.activerecord.hikaricp.file="conf/hikaricp.prod.properties"

hikaricp.prod.properties には下記設定

    dataSourceClassName=com.mysql.jdbc.jdbc2.optional.MysqlDataSource
    dataSource.url=jdbc:mysql://localhost:3306/testdb
    dataSource.user=root
    dataSource.password=test
    dataSource.cachePrepStmts=true
    dataSource.prepStmtCacheSize=250
    dataSource.prepStmtCacheSqlLimit=2048
    dataSource.useServerPrepStmts=true

    connectionTestQuery=SELECT 1
    connectionInitSql=SELECT 1
    maximumPoolSize=20

お次は conf/play.plugin に追加。

    1500:com.edulify.play.hikaricp.HikariCPPlugin

これで準備は整った。

ちなみに、maximumPoolSize はMySQL の設定にあわせておく事。 (デフォルトでは 150 あったはず)

これで dist して再度起動。

    [root@vagrant-centos65 hikarisample-1.0-SNAPSHOT]# ./bin/hikarisample 
    Play server process ID is 3020
    [info] application - Loading Hikari configuration from Play configuration.
    [info] application - Loading from file configured by db.default.hikaricp.file that is Some(conf/hikaricp.prod.properties)
    [info] application - Loading Hikari configuration from conf/hikaricp.prod.properties
    [info] application - Properties: {dataSource.useServerPrepStmts=true, dataSource.user=root, dataSource.password=test, dataSourceClassName=com.mysql.jdbc.jdbc2.optional.MysqlDataSource, connectionTestQuery=SELECT 1, maximumPoolSize=20, connectionInitSql=SELECT 1, dataSource.prepStmtCacheSqlLimit=2048, dataSource.prepStmtCacheSize=250, dataSource.cachePrepStmts=true, dataSource.url=jdbc:mysql://localhost:3306/testdb}
    [error] n.s.e.Cache - Unable to set localhost. This prevents creation of a GUID. Cause was: vagrant-centos65.vagrantup.com: vagrant-centos65.vagrantup.com: Name or service not known
    java.net.UnknownHostException: vagrant-centos65.vagrantup.com: vagrant-centos65.vagrantup.com: Name or service not known
            at java.net.InetAddress.getLocalHost(InetAddress.java:1473) ~[na:1.7.0_55]
            at net.sf.ehcache.Cache.<clinit>(Cache.java:214) ~[net.sf.ehcache.ehcache-core-2.6.6.jar:na]
            at net.sf.ehcache.config.ConfigurationHelper.createCache(ConfigurationHelper.java:296) [net.sf.ehcache.ehcache-core-2.6.6.jar:na]
            at net.sf.ehcache.config.ConfigurationHelper.createDefaultCache(ConfigurationHelper.java:219) [net.sf.ehcache.ehcache-core-2.6.6.jar:na]
            at net.sf.ehcache.CacheManager.configure(CacheManager.java:722) [net.sf.ehcache.ehcache-core-2.6.6.jar:na]
            at net.sf.ehcache.CacheManager.doInit(CacheManager.java:439) [net.sf.ehcache.ehcache-core-2.6.6.jar:na]
    Caused by: java.net.UnknownHostException: vagrant-centos65.vagrantup.com: Name or service not known
            at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method) ~[na:1.7.0_55]
            at java.net.InetAddress$1.lookupAllHostAddr(InetAddress.java:901) ~[na:1.7.0_55]
            at java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1293) ~[na:1.7.0_55]
            at java.net.InetAddress.getLocalHost(InetAddress.java:1469) ~[na:1.7.0_55]
            at net.sf.ehcache.Cache.<clinit>(Cache.java:214) ~[net.sf.ehcache.ehcache-core-2.6.6.jar:na]
            at net.sf.ehcache.config.ConfigurationHelper.createCache(ConfigurationHelper.java:296) [net.sf.ehcache.ehcache-core-2.6.6.jar:na]
    [info] application - Starting HikariCP connection pool...
    [info] application - database [activerecord] connected at jdbc:mysql://localhost:3306/testdb
    [info] play - Application started (Prod)
    [info] play - Listening for HTTP on /0:0:0:0:0:0:0:0:9000

てなことで起動した。

*1:Version若干古いけど、使い方変わらんから大目に見て