Solving Error Quartz and Pentaho BI Server CE 4.8.0


Installing Pentaho BI Server 4.8.0 over MySQL shows the error below on pentaho.log file :

ERROR [org.quartz.core.ErrorLogger] An error occured while scanning for the next trigger to fire.org.quartz.JobPersistenceException: Couldn’t acquire next trigger: Field ‘PRIORITY’ doesn’t have a default value
[See nested exception: java.sql.SQLException: Field ‘PRIORITY’ doesn’t have a default value]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:1778)
at org.quartz.impl.jdbcjobstore.JobStoreTX.acquireNextTrigger(JobStoreTX.java:1218)
at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:233)
* Nested Exception (Underlying Cause) —————
java.sql.SQLException: Field ‘PRIORITY’ doesn’t have a default value
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)

ERROR [org.quartz.core.ErrorLogger] An error occured while scanning for the next trigger to fire.
org.quartz.JobPersistenceException: Couldn’t acquire next trigger: Field ‘PRIORITY’ doesn’t have a
default value [See nested exception: java.sql.SQLException: Field ‘PRIORITY’ doesn’t have a default value]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:1778)
at org.quartz.impl.jdbcjobstore.JobStoreTX.acquireNextTrigger(JobStoreTX.java:1218)
at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:233)
* Nested Exception (Underlying Cause) —————
java.sql.SQLException: Field ‘PRIORITY’ doesn’t have a default value
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
To solve this errors execute the SQL code below

<br />ALTER TABLE  QRTZ_TRIGGERS DROP COLUMN PRIORITY<br />ALTER TABLE  QRTZ_FIRED_TRIGGERS DROP COLUMN PRIORITY<br />
Advertisements

Pentaho BI Server 4.5.0 & Pentaho BI Server 4.8.0- MySQL installation guide


How to install Pentaho BI Server 4.5.0 & 4.8.0 Community Edition with MySQL 5.x and Windows

Hi all this is David Fombella and I would like to share a correct way to deploy Pentaho Community last edition installing its security into a MySQL database server.

Help me keep the guides up to date and the posts flowing by donating, every small amount of money helps!

btn_donatecc_lg

Here is the download links to get the biserver 450 and 480 editions.

http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/4.5.0-stable/biserver-ce-4.5.0-stable.zip/download

http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/4.8.0-stable/biserver-ce-4.8.0-stable.zip/download

Remember to paste mysql_connector.jar into  /tomcat/lib  for biserver 480 edition since it is not included

Creation of Databases & Users

In our first stage we are going to create hibernate database and the user hibuser. To achieve this execute the SQL script  create_repository_mysql.sql included at biserver-ce\data\mysql5

SQL code

CREATE DATABASE IF NOT EXISTS `hibernate` DEFAULT CHARACTER SET latin1;
USE hibernate;
GRANT ALL ON hibernate.* TO 'hibuser'@'localhost' identified by 'password';
DROP TABLE IF EXISTS DATASOURCE;
--  Create Users Table
CREATE TABLE DATASOURCE(NAME VARCHAR(50) NOT NULL PRIMARY KEY,MAXACTCONN INTEGER NOT NULL,DRIVERCLASS VARCHAR(50) NOT NULL,IDLECONN INTEGER NOT NULL,USERNAME VARCHAR(50) NULL,PASSWORD VARCHAR(150) NULL,URL VARCHAR(512) NOT NULL,QUERY VARCHAR(100) NULL,WAIT INTEGER NOT NULL);
commit;
 

Next we create quartz database and the user pentaho_user. You only need to execute the SQL script  create_quartz_mysql.sql included at biserver-ce\data\mysql5


CREATE DATABASE IF NOT EXISTS `quartz` DEFAULT CHARACTER SET latin1;

grant all on quartz.* to 'pentaho_user'@'localhost' identified by 'password';

USE `quartz`;

DROP TABLE IF EXISTS QRTZ_JOB_LISTENERS;
DROP TABLE IF EXISTS QRTZ_TRIGGER_LISTENERS;
DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;
DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;
DROP TABLE IF EXISTS QRTZ_LOCKS;
DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_JOB_DETAILS;
DROP TABLE IF EXISTS QRTZ_CALENDARS;
-- continues
 

Appart from that to fix an issue that occurs on BI Server 4.8.0 execute the following code below the error log


ERROR [org.quartz.core.ErrorLogger] An error occured while scanning for the next trigger to fire.org.quartz.JobPersistenceException: Couldn't acquire next trigger: Field 'PRIORITY' doesn't have a default value
 [See nested exception: java.sql.SQLException: Field 'PRIORITY' doesn't have a default value]
 at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:1778)
 at org.quartz.impl.jdbcjobstore.JobStoreTX.acquireNextTrigger(JobStoreTX.java:1218)
 at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:233)
 * Nested Exception (Underlying Cause) ---------------
 java.sql.SQLException: Field 'PRIORITY' doesn't have a default value

 

SQL code to fix PRIORITY column errors


ALTER TABLE  QRTZ_TRIGGERS DROP COLUMN PRIORITY;
ALTER TABLE  QRTZ_FIRED_TRIGGERS DROP COLUMN PRIORITY;

 

Finally you have to get a MySQL  dump of Sampledata  database.

Configuring JDBC Security

This section describes how to configure the Pentaho BI Platform JDBC security to use a MySQL server, this means the Pentaho BI Platform will now point to the hibernate database on the MySQL server instead of the packaged HSQL in memory database.

1. applicationContext-spring-security-jdbc.xml

Edit the file pentaho-solutions\system\applicationContext-spring-security-jdbc.xml.

Original code

<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>
 

Make the changes necessary to get the snippet of code below

<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/hibernate" />
<property name="username" value="hibuser" />
<property name="password" value="password" />
</bean>
 

     2. applicationContext-spring-security-hibernate.properties.

Edit the file pentaho-solutions\system\applicationContext-spring-security-hibernate.properties.

Original code

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
 

Make the changes necessary to get the snippet of code below

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

     3. hibernate-settings.xml

Edit the file pentaho-solutions\system\hibernate\hibernate-settings.xml.

Original code

<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>
 

Make the changes necessary to get the snippet of code below

<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>
 

4. mysql5.hibernate.cfg.xml

Edit the file pentaho-solutions\system\hibernate\mysql5.hibernate.cfg.xml .

You do not need to make any changes to this file if you would like to use the default user hibuser. However, if you would like to specify your custom user, change connection.username and password  properties.

Original code

<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">hibuser</property>
<property name="connection.password">password</property>
 

Configuring Hibernate and Quartz

Hibernate and Quartz need to specifically use the hibernate and quartz databases which were created on the MySQL server. To do so modifications need to be executed in context.xml file .

     5. context.xml

Edit the file tomcat\webapps\pentaho\META-INF\context.xml.

Remember deleting tomcat\conf\Catalina\localhost\pentaho.xml , Pentaho creates  on startup pentaho.xml as a copy of context.xml.

Original code

<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="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/hibernate"
validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES" />

<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="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"
validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>
 

Make the changes necessary to get the snippet of code below

<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 1" />

<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 1"/>
 

Configuring Apache-Tomcat Web Server and Servlet Container

In order to configure the settings of the Apache-Tomcat server for your Pentaho BI Server 4.5.0 the great majority of the modifications should be made inside the web.xml file which is located under the \tomcat\webapps\pentaho\WEB_INF\ folder.

     6. web.xml

In this first modification we will indicate the web application the location of our solution folder , the place where reports, xactions, dashboards …  will be stored.

Edit the file tomcat\webapps\pentaho\WEB-INF\web.xml.

Original code with empty solution-path

<context-param>
<param-name>solution-path</param-name>
<param-value></param-value>
</context-param>
 

Make the changes necessary to indicate your pentaho-solutions path

<context-param>
<param-name>solution-path</param-name>
<param-value>C:/Pentaho/biserver-ce/pentaho-solutions</param-value>
</context-param>

 

fully-qualified-server-url

Now we are going to set the URL to access Pentaho’s BI User console, the default URL is the following http://localhost:8080/pentaho .If you would like others to access the site (remotely or on a network) you will need to make changes to this parameter.

Original code with default url

<context-param>
<param-name>fully-qualified-server-url</param-name>
<param-value>http://localhost:8080/pentaho/</param-value>
</context-param>
 

Make the changes necessary to indicate your server’s domain

 <context-param>
<param-name>fully-qualified-server-url</param-name>
<param-value>http://www.mydomain.com:8080/pentaho/</param-value>
</context-param>
 

Quit HSQL Hypersonic automatic startup

By default  Hypersonic database starts up automatically – to avoid this  comment or delete locate the following snippets of code from 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] -->
 

Second  section you need to comment or eliminate

<!-- [BEGIN HSQLDB STARTER] -->
<listener>
<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
</listener>
<!-- [END HSQLDB STARTER] -->
 

Pentaho User Console

Now it is time to run Pentaho BI Server, you only need to execute  start-pentaho.bat script stored in biserver-ce, another script named stop-pentaho.bat is included in the same directory.

If you haven’t changed the URL type on your browser the following: http://localhost:8080/pentaho . The default admin user is joe and joe’s password is password.

Pentaho Administrator Console

Now we are going to start the admin console  a web tool where we could manage users,roles and datasources. We execute start-pac.bat  and  if we open a browser and we typehttp://localhost:8099 we will be prompted for administrator console credentials user: admin and admin’s password:password.

We move to Database Connections and clicking on + we create SampleData datasource, pentaho_user should be previously granted access to this sample database

After creating the datasource restart BI server, stop-pentaho.bat …start-pentaho.bat and enjoy the SampleData database.

Hope you have enjoyed