Pentaho Migration 5.1 to 5.2 [Almost Working]

This are not the final instructions, but they will allow you to test the new server with your content and data as you have them now.

—————————————————————————————————————

This are the steps I took for my Pentaho 5.0 to 5.1 and 5.1 to 5.2 upgrades. My installation uses MySql for hibernate, quartz and business databases and I keep the Jackrabbit repository on filesystem. The steps are the same on Linux and Windows.

First Part: Install New Software

  1. Make an inventory: Marketplace, Config (datasources), Reports [That’s optional].
  2. Stop the Pentaho service.
  3. Rename your folder with the current version to something like ‘Pentaho5-old’.
  4. Create a new Pentaho5 directory an unzip your BI Apps into that folder, then:
    • For Pentaho Report Designer:
      1. Rename the folder to report-designer as it used to be named.
      2. Copy your development folder from the ‘backup’.
      3. Also copy your DB drivers: mysql-connector-java-5.1.17.jar, ojdbc14.jar, orai18n.jar, -IT is using Oracle 9, and still works- from your backup to report-designer/lib/jdbc
    • For PDI/Kettle
      1. Copy your development folder from the backup.
      2. Copy your DB drivers (mysql-connector-java-5.1.17.jar, ojdbc14.jar, orai18n.jar) from your backup to data-integration/bin.
    • For the BI Server itself:
      1. Copy your DB drivers -other than MySql- (ojdbc14.jar, orai18n.jar) from your backup folder to biserver-ce/tomcat\lib
  5. Start your demo:
    ./start-pentaho.sh
    Check out what’s new at http://localhost:8080/pentaho
    The Marketplace throwed several connection errors, until it was succesfull.
  6. Stop the server:
    ./stop-pentaho.sh

Second Part: Modify Configuration Files

Compare your config files before copying them, and verify they are in utf-8 format. Remember that you have the backup folder and you only need to rename it to recover to a working system.

  1. Delete the file
    biserver-ce/tomcat/conf/Catalina/localhost/pentaho.xml
    It will be created as a copy from the one you edit in point 2
  2. Copy
    biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
    mine has MySql connection strings already
  3. Copy
    biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml
    Only needed changes on line 73 and 205, to comment HSQLDB from starting
    and:
    At line 539 change your session-timeout. Its’ in seconds, I use: 180
  4. Copy
    biserver-ce/pentaho-solutions/system/hibernate/hibernate-settings.xml
    The only change is to replace mysql: mysql5.hibernate.cfg.xml
  5. Copy jindi datasources:
    biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
  6. Copy MySql config for Hibernate:
    biserver-ce/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties
  7. Copy
    biserver-ce/pentaho-solutions/system/applicationContext-spring-security-jdbc.properties
    It contains:

    datasource.driver.classname=com.mysql.jdbc.Driver
    datasource.url=jdbc:mysql://localhost:3306/hibernate
    datasource.username=hibuser
    datasource.password=password
    datasource.validation.query=SELECT 1
  8. Copy
    biserver-ce/pentaho-solutions/system/pentaho.xml
    I changed:

    SampleData
    localhost
    MySql
    3306
    pentaho_user
    password
    select 1

    And turn off the default users select combo at login page (that should be disabled in your administration console) in line 20:

    <login-show-sample-users-hint>false</login-show-sample-users-hint>
  9. This is where I had trouble:
    This didn’t work: I first ran the update on
    biserver-ce/data/mysql5/create_quartz_mysql.sql
    to make shure I had de correct quartz5 prefixed tables, then created upper/lower cased tables, tables, deleted conten on different folders, and so on. But ran on errors at startup about listener problems:

    1. Cannot create JDBC driver of class
    2. SystemListener.xml
    3. EmbeddedQuartzSystemListener.ERROR_0007_SQLERROR
    4. PentahoSystem.ERROR_0014 org.pentaho.platform.scheduler2.quartz.EmbeddedQuartzSystemListener
      with no luck on making it work.

    This did work

    1. Dropping the quartz database and ran the SQL script at:
      biserver-ce/data/create_quartz_mysql.sql
      that creates qrtz5_ prefixed tables and also updates user permissions.
    2. Modified as suggested in the script:
      biserver-ce/pentaho-solutions/system/quartz.properties
      that’s the quartz config, to:

      org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
    3. [skip this one, Marcelo suggested a better option]
      Edit biserver-ce/pentaho-solutions/system/systemListeners.xml and stopped Quartz Listener from runnig. That is the problem with this instructions: your system won’t be able to schedule jobs as it is:

      <!--bean id="quartzSystemListener" class="org.pentaho.platform.scheduler2.quartz.EmbeddedQuartzSystemListener" /-->
    4. Move to a backup folder -away from Pentaho- the following file:
       biserver-ce/tomcat/webapps/pentaho/WEB-INF/lib/pentaho-hadoop-hive-jdbc-shim-5.2.0.0-209.jar

Part Three: Move in Your Content

  1. Copy your content from your backup folder in
    biserver-ce/pentaho-solutions
    This is where I keep my .xmi, .mondrian.xml files and solution folders
  2. Delete and then replace your repository folder:
    biserver-ce/pentaho-solutions/system/jackrabbit
  3. Start your server. Clear your browser’s cache, reset your pentaho solutions and reload.

Part Four: Apply your Look and Feel

To finish

  1. Copy your images, .html , messages files with your modifications from your backup folder to the new one. There is a previous checklist.
    Note: Had only to modify directly the .properties files (insted of the _es.properties) on biserver-ce/tomcat/webapps/pentaho/mantle/ (browser & home/properties) for my localized text as the i18n calls didn’t work for the left panels.
  2. Update your plugins on the marketplace

Now you can try your new and beautifull NEW 5.2 BI system.

———————————

Notes on the 5.2 – MySql – Quartz Problem

There are some Quartz listener/socket problems reported since v3.6, but every recomendation on forums or blogs hasn’t worked so far. The error is present on windows/ubuntu, java 32/64 bits, mysql/mariadb.

This is what I found so far:

  1. The problem is not IN the database as no query appears on the MySql general log, until the Pentaho shows the problem.
  2. The 1st error in the log makes me think of a Tomcat config problem :
    eduler2.quartz.EmbeddedQuartzSystemListener
    EmbeddedQuartzSystemListener.ERROR_0007_SQLERROR
    Cannot create JDBC driver of class ‘com.mysql.jdbc.Driver’ for connect URL ‘jdbc:mysql://localhost:3306/quartz’
  3. The diff in config files shows more classes on the new version, but the driver configurations remais the same
Advertisements