Pentaho configuration for MySQL

Steps to change Pentaho preconfigured hypersonic database to MySQL

Recently I integrated Pentaho with Liferay and Mysql. The open source Pentaho Pre-Configured Install (PCI) uses hypersonic as its sample data source, I want to remove this hypersonic dependency and want Pentaho to point to mysql5 database. I have searched for exact steps on Pentaho’s official site (http://wiki.pentaho.com/) but did not find easy steps to follow. In this blog I have explained the exact steps to remove hypersonic dependency and use mysql5 database.

I have used the following steps to achieve this objective

1. Extract pentaho-data.zip in [drive name]liferay-portal-5.2.3 folder.
2. Extract pentaho-solutions.zip in [drive name]liferay-portal-5.2.3 folder.
3. Execute the SQL Scripts from the "pentaho-data/mysql5" folder.

create_quartz_mysql.sql
create_repository_mysql.sql
create_sample_datasource_mysql.sql
migration.sql

4. Copy Pentaho.war file in [drive name]liferay-portal-5.2.3tomcat-6.0.18webapps
5. Copy pentaho-portal-layout.war file in [drive name]liferay-portal-5.2.3tomcat-6.0.18webapps
6. Copy pentaho-style.war file in [drive name]liferay-portal-5.2.3tomcat-6.0.18webapps
7. Open server.xml file from [drive name]liferay-portal-5.2.3tomcat-6.0.18conf folder and add following <context> entries after <Host> tag entry.

<Context path="/pentaho" docbase="webapps/pentaho/">
<Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource" maxActive="20" maxIdle="5" maxWait="10000" username="root" password="root" factory="org.apache.commons.dbcp.BasicDataSourceFactory"  driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/sampledata" />

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource" factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5" maxWait="10000" username="root" password="root"driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate" />

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource" factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5" maxWait="10000" username="root" password="root"driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz" />

<Resource name="jdbc/Shark" auth="Container" type="javax.sql.DataSource" factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5" maxWait="10000" username="root" password="root"driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/shark" />

Please provide the correct MySQL database URL, username and password in above tags. The fields are highlighted with a yellow background.

8. Configure database username, password and driver information in mysql5.hibernate.cfg.xml, hibernate-settings.xml file from pentaho-solutionssystemhibernate folder.

Edit file applicationContext-spring-security-hibernate.properties

jdbc.driver=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.HSQLDialect

Changed to

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3309/hibernate
jdbc.username=<<database user name>>
jdbc.password=<
<password>>
hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

Edit file applicationContext-spring-security-jdbc.xml

<!--  This is only for Hypersonic. Please update this section for any other database you are using -->
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
<property name="url"
	    value="jdbc:hsqldb:hsql://localhost:9001/hibernate" />
<property name="username" value="hibuser" />
<property name="password" value="password" />
    </bean>

Changed to

<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url"
        value="jdbc:mysql://localhost:3306/liferayreport" />
<property name="username" value="<<database user name>>" />
<property name="password" value="<<password>>" />
</bean>

Edit file mysql5.hibernate.cfg.xml

change user/password

<property name="connection.username"><<database user name>></property>
<property name="connection.password"><
<password>>/property>

9. Restart the tomcat, now Pentaho will use mysql as its database