Configuring for MySQL

Skip to end of metadata
Go to start of metadata

Currently available at this post.

There is a guide for a local (development) setup and a remote (production) setup.

Please see the links below for excellent installation walk through guides for installing and setting up the Pentaho platform for:

Local Installs:

http://www.prashantraju.com/pentaho/guides/biserver-2.0-final/biserver2.0-local-final.pdf

Remote Installs (Installations that accept remote connections.)

http://www.prashantraju.com/pentaho/guides/biserver-2.0-final/biserver2.0-remote-final.pdf

 Dealing with MySQL connection death after 8 hours

Hardly anything is more frustrating than coming back the day after to find your beautifully configured Pentaho installation handing up obscure error messages and seeming to hobble about as you log in and try to execute reports.

MySQL connections timeout by default every 8 hours.  If Pentaho sits idle for more than 8 hours, its connection to the repository silently drops dead without Pentaho's knowledge.  When something stirs Pentaho after an 8 hour or greater time lapse, Pentaho assumes that its connection is still alive and currently there is no default mechanism in place that handles bringing a connection back to life.  Enter c3p0 (JDBC Connection Pooling software).

The solution turns out to be fairly easy.  What is required is a piece of software to manage the database connection pool and keep the connections alive.  The recommended method is to use c3p0.

More information about c3p0 can be found on the project's site:

To use c3p0 to solve this problem, first download the c3p0-0.9.1.2.bin.zip, stop your Java application server, take the jar files c3p0-0.9.2.jar and mchange-commons-java-0.2.3.3.jar inside the downloaded archive and copy them to your WEB-INF/lib directory on your java application server. I use Tomcat 6 on Ubuntu 8.10, so my installation path happens to be:

 /var/lib/tomcat6/webapps/pentaho/WEB-INF/lib


Next, we'll need to modify the hibernate settings for MySQL.  The file we will modify is called mysql5.hibernate.cfg.xml and is located in the pentaho-solutions/system/hibernate folder by default.

You will insert the following text just after the <session-factory> tag and just before the <!-- MySQL Configuration --> comment.

         <!--
            hibernate c3p0 settings
        -->

        <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.acquire_increment">3</property>
        <property name="hibernate.c3p0.idle_test_period">10</property>
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.max_size">75</property>
        <property name="hibernate.c3p0.max_statements">0</property>
        <property name="hibernate.c3p0.timeout">25200</property>
        <property name="hibernate.c3p0.preferredTestQuery">select 1</property>
        <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>


        <!--
            hibernate cache settings
        -->
	<!-- End of patch added Friday April 3, 2009 to address issues of the database connection going dead
	-->

        <property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
        <property name="hibernate.generate_statistics">true</property>
        <property name="hibernate.cache.use_query_cache">true</property>

That's all there is to it.  Save the file and restart your application server and the dead connection problem should go away.

Labels

mysql mysql Delete
install install Delete
2 2 Delete
tomcat tomcat Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. May 16, 2013

    Pham Van Sac says:

    Dear All, I know using c3p0 to solve the problem that server will inactive afte...

    Dear All,

    I know using c3p0 to solve the problem that server will inactive after 8 hours. However, When I using c3po I have new issue that it's create too many connection without using. This is my mysql5.hibernate.cfg.xml file

    <hibernate-configuration><session-factory>

    <!--
          hibernate c3p0 settings
        -->

    <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

    <property name="hibernate.c3p0.acquire_increment">3</property>

    <property name="hibernate.c3p0.idle_test_period">10</property>

    <property name="hibernate.c3p0.min_size">5</property>

    <property name="hibernate.c3p0.max_size">75</property>

    <property name="hibernate.c3p0.max_statements">0</property>

    <property name="hibernate.c3p0.timeout">25200</property>

    <property name="hibernate.c3p0.preferredTestQuery">select 1</property>

    <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>

    <!--
           hibernate cache settings
        -->

    <property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>

    <property name="hibernate.generate_statistics">true</property>

    <property name="hibernate.cache.use_query_cache">true</property>

    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>

    <property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>

    <property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>

    <property name="connection.username">root</property>

    <property name="connection.password">password</property>

    <property name="connection.pool_size">10</property>

    <property name="show_sql">false</property>

    <property name="hibernate.jdbc.use_streams_for_binary">true</property>

    <property name="hibernate.hbm2ddl.auto">update</property>

    <mapping resource="hibernate/mysql5innodb.hbm.xml"/>

    <mapping resource="PentahoUser.hbm.xml"/>

    <mapping resource="PentahoRole.hbm.xml"/>

    <mapping resource="PentahoUserRoleMapping.hbm.xml"/>

    </session-factory>

    </hibernate-configuration>

    How can I fix for setting ?

  2. Sep 12, 2013

    Diego Rodrigues Ferreira says:

    Hello Pham, Have you tried adding      <property name=”hiber...

    Hello Pham,

    Have you tried adding 

        <property name=”hibernate.c3p0.acquireIncrement”>5</property>
        <property name=”hibernate.c3p0.idleConnectionTestPeriod”>300</property>
    <property name=”hibernate.c3p0.idleConnectionTestPeriod”>300</property>

    to the connection pool configuration?

    Hope this helps! =]