Pentaho 7.1 迁移到MySQL

环境：
CentOS 6.5
Pentaho压缩包：pentaho-server-ce-7.1.0.0-12.zip
Java：java version "1.8.0_144" (64位)

工作目录：/pentaho/pentaho-server
注：所有涉及到username和password的内容都需要根据自己配置的数据库修改

按照下面步骤依次修改文件：

1. /pentaho/pentaho-server/pentaho-solutions/system/applicationContext-spring-security-jdbc.properties

datasource.driver.classname=com.mysql.jdbc.Driver
datasource.url=jdbc:mysql://localhost:3306/hibernate
datasource.username=root
datasource.password=root （根据自己的库更改）
datasource.validation.query=SELECT USER()

2. /pentaho/pentaho-server/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

3. /pentaho/pentaho-server/pentaho-solutions/system/hibernate/hibernate-settings.xml

system/hibernate/mysql5.hibernate.cfg.xml

4. /pentaho/pentaho-server/pentaho-solutions/system/quartz/quartz.properties

解除注释并修改为： org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

5. /pentaho/pentaho-server/pentaho-solutions/system/simple-jndi/jdbc.properties

SampleData/type=javax.sql.DataSource
SampleData/driver=com.mysql.jdbc.Driver
SampleData/url=jdbc:mysql://localhost:3306/sampledata
SampleData/user=pentaho_user
SampleData/password=password
Hibernate/type=javax.sql.DataSource
Hibernate/driver=com.mysql.jdbc.Driver
Hibernate/url=jdbc:mysql://localhost:3306/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Quartz/type=javax.sql.DataSource
Quartz/driver=com.mysql.jdbc.Driver
Quartz/url=jdbc:mysql://localhost:3306/quartz
Quartz/user=pentaho_user
Quartz/password=password
Shark/type=javax.sql.DataSource
Shark/driver=com.mysql.jdbc.Driver
Shark/url=jdbc:mysql://localhost:3306/shark
Shark/user=sa
Shark/password=
SampleDataAdmin/type=javax.sql.DataSource
SampleDataAdmin/driver=com.mysql.jdbc.Driver
SampleDataAdmin/url=jdbc:mysql://localhost:3306/sampledata
SampleDataAdmin/user=pentaho_admin
SampleDataAdmin/password=password

6. /pentaho/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml

注释掉所有的Resource，增加：
< Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource" factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5" maxWait="10000" username="hibuser" password="password" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
validationQuery="select user()" />

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

7. /pentaho/pentaho-server/tomcat/webapps/pentaho/WEB-INF/web.xml

注释掉以下两段内容
< !-- [BEGIN HSQLDB DATABASES] -->
< context-param>
< param-name>hsqldb-databases</param-name>
< param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value>
< /context-param>
< !-- [END HSQLDB DATABASES] -->
< !-- [BEGIN HSQLDB STARTER] -->
< listener>
< listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
< /listener>
< !-- [END HSQLDB STARTER] -->

8. 复查pentaho官方安装文档

9. 复制驱动包mysql-connector-java-5.1.44-bin.jar到：

/pentaho/pentaho-server/tomcat/lib

QA:
Q1. Pentaho日志目录：/pentaho/pentaho-server/tomcat/logs
Q2. 问题描述：
Error creating bean with name 'org.h2.tools.Server' defined in file [/pentaho/pentaho-server/pentaho-solutions/system/GettingStartedDB-spring.xml]: Invocation of init method failed; nested exception is org.h2.jdbc.JdbcSQLException: Exception opening port "H2 TCP Server (tcp://localhost:9092)" (port may be in use), cause: "timeout" [90061-131]

暂时解决方法：
在start-pentaho.sh中增加-Dh2.bindAddress=localhost，如：
CATALINA_OPTS="-Xms2048m -Xmx6144m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000 -Dfile.encoding=utf8 -DDI_HOME="$DI_HOME" -Dh2.bindAddress=localhost "

Q3. 报OPTION SQL_SELECT_LIMIT=5问题

解决方法：换mysql-connector-java.jar，换成mysql-connector-java-5.1.44-bin.jar

Q4. 问题描述：
[org.pentaho.platform.dataaccess.datasource.wizard.service.impl.ConnectionServiceImpl]ConnectionServiceImpl.ERROR_0002 - Unable to get the list of connections: DatasourceMgmtService.ERROR_0004 - Error occurred during retrieving the datasource

解决方法：先关闭BI Server(使用stop-pentaho.sh)，然后删除或改名如下文件夹/pentaho-solutions/system/jackrabbit/repository然后重启BI Server(start-pentaho.sh)