AnsweredAssumed Answered

Pentaho Server 8 CE: User/Role List could not be obtained.

Question asked by Joao Ciocca on Apr 10, 2018
Latest reply on Jan 15, 2019 by Elijah Abdulrahman

Like • Show 2 Likes2
Comment • 7

So, we've set up an installation of 8.0 CE, followed the docs, server is up and running... but can't login. Admin/password or Suzy/password will result in Login Error. Behind the scenes, catalina.out says:

18:27:01,378 INFO  [PeriodicStatusLogger] The system has finished initializing.
[pt_47] Pentaho BI Platform server is ready.
18:27:01,561 ERROR [CompositeUserRoleListService] User/Role List could not be obtained.
java.lang.IllegalStateException: Target of Bean was never resolved: org.springframework.security.core.userdetails.UserDetailsService
        at org.pentaho.platform.engine.core.system.objfac.spring.BeanBuilder$1.invoke(BeanBuilder.java:157)
        at com.sun.proxy.$Proxy83.loadUserByUsername(Unknown Source)
        at org.pentaho.platform.plugin.services.security.userrole.ChainedUserDetailsService.loadUserByUsername(ChainedUserDetailsService.java:47)
        at org.pentaho.platform.plugin.services.security.userrole.PentahoCachingUserDetailsService.loadUserByUsername(PentahoCachingUserDetailsService.java:74)
        at org.pentaho.platform.security.userroledao.service.UserRoleDaoUserRoleListService.getRolesForUser(UserRoleDaoUserRoleListService.java:143)
        at org.pentaho.platform.plugin.services.security.userrole.ExtraRolesUserRoleListServiceDecorator.getRolesForUser(ExtraRolesUserRoleListServiceDecorator.java:113)
        at org.pentaho.platform.plugin.services.security.userrole.CompositeUserRoleListService$7.perform(CompositeUserRoleListService.java:122)
        at org.pentaho.platform.plugin.services.security.userrole.CompositeUserRoleListService.collectResultsForOperation(CompositeUserRoleListService.java:131)
        at org.pentaho.platform.plugin.services.security.userrole.CompositeUserRoleListService.getRolesForUser(CompositeUserRoleListService.java:120)
        at org.pentaho.platform.engine.security.SecurityHelper.createAuthentication(SecurityHelper.java:352)
        at org.pentaho.platform.engine.security.SecurityHelper.runAsSystem(SecurityHelper.java:415)
        at pt.webdetails.cpf.CpfProperties.loadAsSystem(CpfProperties.java:46)
        at pt.webdetails.cpf.AbstractCpfProperties.loadSettings(AbstractCpfProperties.java:58)
        at pt.webdetails.cpf.AbstractCpfProperties.<init>(AbstractCpfProperties.java:35)
        at pt.webdetails.cpf.CpfProperties.<init>(CpfProperties.java:32)
        at pt.webdetails.cpf.CpfProperties.getInstance(CpfProperties.java:37)
        at pt.webdetails.cpf.SimpleLifeCycleListener.ready(SimpleLifeCycleListener.java:45)
        at org.pentaho.platform.web.http.context.PentahoSystemReadyListener.contextInitialized(PentahoSystemReadyListener.java:53)
        at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4853)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5314)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:145)
        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:753)
        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:729)
        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
        at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1129)
        at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1871)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

And when attempting to login...

18:31:14,057 ERROR [UsernamePasswordAuthenticationFilter] An internal error occurred while trying to authenticate the user.
org.springframework.security.authentication.InternalAuthenticationServiceException: Target of Bean was never resolved: org.springframework.security.core.userdetails.UserDetailsService
        at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:126)
        at org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:144)
        at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:174)
        at org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter.attemptAuthentication(UsernamePasswordAuthenticationFilter.java:94)
        at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:212)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:121)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.pentaho.platform.web.http.security.HttpSessionReuseDetectionFilter.doFilter(HttpSessionReuseDetectionFilter.java:133)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.pentaho.platform.web.http.filters.HttpSessionPentahoSessionIntegrationFilter.doFilter(HttpSessionPentahoSessionIntegrationFilter.java:271)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:169)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.pentaho.platform.web.http.filters.SystemStatusFilter.doFilter(SystemStatusFilter.java:55)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.pentaho.platform.web.http.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:114)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.pentaho.platform.web.http.filters.WebappRootForwardingFilter.doFilter(WebappRootForwardingFilter.java:70)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.pentaho.platform.web.http.filters.PentahoPathDecodingFilter.doFilter(PentahoPathDecodingFilter.java:34)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1539)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1495)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.IllegalStateException: Target of Bean was never resolved: org.springframework.security.core.userdetails.UserDetailsService
        at org.pentaho.platform.engine.core.system.objfac.spring.BeanBuilder$1.invoke(BeanBuilder.java:157)
        at com.sun.proxy.$Proxy83.loadUserByUsername(Unknown Source)
        at org.pentaho.platform.plugin.services.security.userrole.ChainedUserDetailsService.loadUserByUsername(ChainedUserDetailsService.java:47)
        at org.pentaho.platform.plugin.services.security.userrole.PentahoCachingUserDetailsService.loadUserByUsername(PentahoCachingUserDetailsService.java:74)
        at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:114)
        ... 49 more

I've retraced all setup steps, everything is correct, the server is connected to the postgres database, according to netstat, but nothing. I've googled around but found nothing helpful. Ideas?

Joao Ciocca Apr 13, 2018 5:47 AM

Correct Answer

Figured out: the problem was caused by disabling the HSQLDB connections, without configuring a proper new auth method. We were disabling it and enabling postgreSQL following the documentation, but there's nothing on the docs that say the default users (admin, suzy, joe) don't get loaded on the postgreSQL create scripts - and we just assumed they were.

Once we switched back HSQLDB, everything worked out fine.

See the reply in context

1 person had this question

Outcomes

Joao Ciocca Apr 13, 2018 5:47 AM

Once we switched back HSQLDB, everything worked out fine.

Actions

Alexis Araya @ Joao Ciocca on Sep 13, 2018 8:30 AM

I have the same problem, but with mysql, is there a SQL script without the need to re-enable the HSQLDB?
If it is not there, how is the change of users from HSQLDB to Mysql?
Thank you.

1 person found this helpful

Actions

Joao Ciocca @ Alexis Araya on Sep 13, 2018 10:42 AM

sorry mate, we were thinking of integrating auth with our company's SSO mechanism, but so far, no progress - so we've just changed the default passwords for those default users and kept life going.

1 person found this helpful

Actions

Elijah Abdulrahman @ Joao Ciocca on Jan 15, 2019 9:50 AM

What did you edit to change the passwords? I see the script files in the data/hsqldb which have hashed passwords. Did you change these?

Thank you.

Actions

Gian Paolo Perillo Sep 26, 2018 9:07 AM

Hi Joao, please could you specify files you have modified to switch back to HSQLDB?

We have just terminated Pentaho CE Server 8.1 installation but we have to stop our task due this issue.

Thanks in advance

Gian Paolo

1 person found this helpful

Actions

Daniele Sibio Nov 5, 2018 7:54 AM

Does Someone figured out how to resolve this issue?

I'm on RedHat/Tomcat/MySQL 5.7 pentaho 8.1 and i got the same error.

Cannot login on the Console with Admin or Suzy and the "User/Roles cannot be obtained .." in the logs file.

We also modified

SQL code

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

USE hibernate;

GRANT ALL ON hibernate.* TO 'hibuser'@'localhost' identified by 'password';

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

CREATEDATABASEIF NOTEXISTS `quartz` DEFAULTCHARACTERSETlatin1; 
 
grant all on quartz.* to 'pentaho_user'@'localhost' identified by 'password';
 
USE `quartz`;
 
DROP TABLE IF EXISTS QRTZ5_JOB_LISTENERS;
DROP TABLE IF EXISTS QRTZ5_TRIGGER_LISTENERS;
DROP TABLE IF EXISTS QRTZ5_FIRED_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_PAUSED_TRIGGER_GRPS;
DROP TABLE IF EXISTS QRTZ5_SCHEDULER_STATE;
DROP TABLE IF EXISTS QRTZ5_LOCKS;
DROP TABLE IF EXISTS QRTZ5_SIMPLE_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_CRON_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_BLOB_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_JOB_DETAILS;
DROP TABLE IF EXISTS QRTZ5_CALENDARS;
 
CREATE TABLE QRTZ5_JOB_DETAILS
  (
    JOB_NAME  VARCHAR(200) NOT NULL,
    JOB_GROUP VARCHAR(200) NOT NULL,
    DESCRIPTION VARCHAR(250) NULL,
    JOB_CLASS_NAME   VARCHAR(250) NOT NULL,
    IS_DURABLE VARCHAR(1) NOT NULL,
    IS_VOLATILE VARCHAR(1) NOT NULL,
    IS_STATEFUL VARCHAR(1) NOT NULL,
    REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
    JOB_DATA BLOB NULL,
    PRIMARY KEY (JOB_NAME,JOB_GROUP)
);
 
CREATE TABLE QRTZ5_JOB_LISTENERS
  (
    JOB_NAME  VARCHAR(200) NOT NULL,
    JOB_GROUP VARCHAR(200) NOT NULL,
    JOB_LISTENER VARCHAR(200) NOT NULL,
    PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER),
    FOREIGN KEY (JOB_NAME,JOB_GROUP)
        REFERENCES QRTZ5_JOB_DETAILS(JOB_NAME,JOB_GROUP)
);
 
CREATE TABLE QRTZ5_TRIGGERS
  (
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    JOB_NAME  VARCHAR(200) NOT NULL,
    JOB_GROUP VARCHAR(200) NOT NULL,
    IS_VOLATILE VARCHAR(1) NOT NULL,
    DESCRIPTION VARCHAR(250) NULL,
    NEXT_FIRE_TIME BIGINT(13) NULL,
    PREV_FIRE_TIME BIGINT(13) NULL,
    PRIORITY INTEGER NULL,
    TRIGGER_STATE VARCHAR(16) NOT NULL,
    TRIGGER_TYPE VARCHAR(8) NOT NULL,
    START_TIME BIGINT(13) NOT NULL,
    END_TIME BIGINT(13) NULL,
    CALENDAR_NAME VARCHAR(200) NULL,
    MISFIRE_INSTR SMALLINT(2) NULL,
    JOB_DATA BLOB NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (JOB_NAME,JOB_GROUP)
        REFERENCES QRTZ5_JOB_DETAILS(JOB_NAME,JOB_GROUP)
);
 
CREATE TABLE QRTZ5_SIMPLE_TRIGGERS
  (
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    REPEAT_COUNT BIGINT(7) NOT NULL,
    REPEAT_INTERVAL BIGINT(12) NOT NULL,
    TIMES_TRIGGERED BIGINT(10) NOT NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
 
CREATE TABLE QRTZ5_CRON_TRIGGERS
  (
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    CRON_EXPRESSION VARCHAR(200) NOT NULL,
    TIME_ZONE_ID VARCHAR(80),
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
 
CREATE TABLE QRTZ5_BLOB_TRIGGERS
  (
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    BLOB_DATA BLOB NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
 
CREATE TABLE QRTZ5_TRIGGER_LISTENERS
  (
    TRIGGER_NAME  VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    TRIGGER_LISTENER VARCHAR(200) NOT NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
 
CREATE TABLE QRTZ5_CALENDARS
  (
    CALENDAR_NAME  VARCHAR(200) NOT NULL,
    CALENDAR BLOB NOT NULL,
    PRIMARY KEY (CALENDAR_NAME)
);
 
CREATE TABLE QRTZ5_PAUSED_TRIGGER_GRPS
  (
    TRIGGER_GROUP  VARCHAR(200) NOT NULL,
    PRIMARY KEY (TRIGGER_GROUP)
);
 
CREATE TABLE QRTZ5_FIRED_TRIGGERS
  (
    ENTRY_ID VARCHAR(95) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    IS_VOLATILE VARCHAR(1) NOT NULL,
    INSTANCE_NAME VARCHAR(200) NOT NULL,
    FIRED_TIME BIGINT(13) NOT NULL,
    PRIORITY INTEGER NOT NULL,
    STATE VARCHAR(16) NOT NULL,
    JOB_NAME VARCHAR(200) NULL,
    JOB_GROUP VARCHAR(200) NULL,
    IS_STATEFUL VARCHAR(1) NULL,
    REQUESTS_RECOVERY VARCHAR(1) NULL,
    PRIMARY KEY (ENTRY_ID)
);
 
CREATE TABLE QRTZ5_SCHEDULER_STATE
  (
    INSTANCE_NAME VARCHAR(200) NOT NULL,
    LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
    CHECKIN_INTERVAL BIGINT(13) NOT NULL,
    PRIMARY KEY (INSTANCE_NAME)
);
 
CREATE TABLE QRTZ5_LOCKS
  (
    LOCK_NAME  VARCHAR(40) NOT NULL,
    PRIMARY KEY (LOCK_NAME)
);
 
INSERT INTO QRTZ5_LOCKS values('TRIGGER_ACCESS');
INSERT INTO QRTZ5_LOCKS values('JOB_ACCESS');
INSERT INTO QRTZ5_LOCKS values('CALENDAR_ACCESS');
INSERT INTO QRTZ5_LOCKS values('STATE_ACCESS');
INSERT INTO QRTZ5_LOCKS values('MISFIRE_ACCESS');
commit;

Finally we create jackrabbit database and the user jcr_user. You only need to execute the SQL script create_jcr_mysql.sql included at biserver-ce\data\mysql5

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

grant all on jackrabbit.* to 'jcr_user'@'localhost' identified by 'password';

commit;

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

2. hibernate-settings.xml

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

Original code

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

Make the changes necessary to get the snippet of code below

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

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

4. quartz.properties

Edit the file pentaho-solutions\system\quartz\quartz.properties .

Original code

1	`org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate`

Make the changes necessary to get the snippet of code below

1	`org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate`

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

6. repository.xml

Comment the original code in the FileSystem part

<!-- 
Replace the following "FileSystem" XML node to use supported databases as
the repository file system.  Change the url, user, password and other parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->
<FileSystem>
<param name="path" value="${rep.home}/repository"/>
</FileSystem>

Make this code active on FileSystem part of the code

<FileSystem>

<param name="driver" value="com.mysql.jdbc.Driver"/>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="fs_repos_"/>

</FileSystem>

Comment the original code in the DataStore part

<!-- 
data store configuration
-->
<!--
Replace the following "DataStore" XML node to use supported databases as the data
store for the repository.  Change the url, user, password and other parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->
<DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>

Make this code active on DataStore part of the code

<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="databaseType" value="mysql"/>

<param name="driver" value="com.mysql.jdbc.Driver"/>

<param name="minRecordLength" value="1024"/>

<param name="maxConnections" value="3"/>

<param name="copyWhenReading" value="true"/>

<param name="tablePrefix" value=""/>

<param name="schemaObjectPrefix" value="ds_repos_"/>

</DataStore>

Below the security part comment the original code in the FileSystem Workspace part

<!-- 
virtual file system of the workspace:
class: FQN of class implementing the FileSystem interface
-->
<!--
Replace the following "FileSystem" XML node to use supported databases as
the repository file system.  Change the url, user, password and other parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->
<FileSystem>
<param name="path" value="${wsp.home}"/>
</FileSystem>

Make this code active on FileSystem WorkSpace part of the code

<FileSystem>

<param name="driver" value="com.mysql.jdbc.Driver"/>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="fs_ws_"/>

</FileSystem>

Below FileSystem Workspace part you will find the PersistenceManager part

<!-- 
persistence manager of the workspace:
class: FQN of class implementing the PersistenceManager interface
-->
<!--
Replace the following "PersistenceManager" XML node to use a supported database as the
persistenanceManager store.  Change the url, user, password and parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->
 
<PersistenceManager>
<param name="url" value="jdbc:h2:${wsp.home}/db"/>
<param name="schemaObjectPrefix" value="${wsp.name}_"/>
</PersistenceManager>

Make this code active on PersistenceManager part of the code

<PersistenceManager>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user" />

<param name="password" value="password" />

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>

</PersistenceManager>

Below you will find FileSystem Versioning part

<!-- 
Configures the filesystem to use for versioning for the respective
persistence manager
-->
<!--
Replace the following "FileSystem" XML node to use a supported database as
the repository file system.  Change the url, user, password and other parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->
<FileSystem>
<param name="path" value="${rep.home}/version" />
</FileSystem>

Make this code active on FileSystem Versioning part

<FileSystem>

<param name="driver" value="com.mysql.jdbc.Driver"/>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="fs_ver_"/>

</FileSystem>

Below you will find PersistenceManager Versioning part

<!-- 
Configures the persistence manager to be used for persisting version state.
Please note that the current versioning implementation is based on
a 'normal' persistence manager, but this could change in future
implementations.
-->
<!--
Replace the following "PersistenceManager" XML node to use a supported database as the
persistenanceManager store.  Change the url, user, password and parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->
<PersistenceManager>
<param name="url" value="jdbc:h2:${rep.home}/version/db"/>
<param name="schemaObjectPrefix" value="version_"/>
</PersistenceManager>

Make this code active on PersistenceManager Versioning part

<PersistenceManager>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user" />

<param name="password" value="password" />

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="pm_ver_"/>

</PersistenceManager>

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:

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

Second section you need to comment or eliminate

<listener>

<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>

</listener>

Took the guide online

Actions

Marcus Berglund Jan 2, 2019 4:35 AM

As far as I can tell, no one has managed to resolve this issue (?)

The only proposed workaround in this thread (including the accepted answer) is to use HSQLDB instead.

Does this mean that Pentaho PUC (CE) no longer can be said to work with Postgresql/MySQL ?

If so - is there an official statement to this fact ?

... Or if Pentaho PUC / Pentaho Server 8.1 is supposed to work with MySQL or Postgresql - perhaps I can raise a bug somewhere.

I'm a bit new around here so advice on where to report a bug would be most welcome !

2 people found this helpful

Actions

Jive

Hitachi Vantara Community

Pentaho Server 8 CE: User/Role List could not be obtained.

Outcomes

Related Content

Recommended Content