AAAR_Etract.sh is failing due to connection

[manirangasamy]

Hi Francesco ,

I am facing problem while executing the AAAR_Extract.sh.

I am using mysql and I used AAAR_Manual_Installation.pdf

Pentaho Combined Server 7.0.0.0-25 Distribution

mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1

Pentaho and Alfresco are not same servers.

Below is the repositories.xml

]# cat /oracle/app/pentaho/data-integration/repositories.xml
<?xml version="1.0" encoding="UTF-8"?>
<repositories>
  <connection>
    <name>AAAR_Kettle</name>
    <type>MYSQL</type>
    <access>JNDI</access>
    <database>AAAR_Kettle</database>
  </connection>
  <repository>
    <id>KettleDatabaseRepository</id>
    <name>AAAR_Kettle</name>
    <description>AAAR_Kettle</description>
    <connection>AAAR_Kettle</connection>
  </repository>
</repositories>

and

<?xml version="1.0" encoding="UTF-8"?>
<AAAR>
 <parameter name="AAARGetAudit" value="true"> </parameter>
 <parameter name="AAARGetNodes" value="true"> </parameter>
 <parameter name="AAARGetWorkflows" value="true"> </parameter>
 <parameter name="alfrescoAuditLimit" value="50000"> </parameter>
 <parameter name="alfrescoFtpPath" value="alfresco"> </parameter>
 <parameter name="alfrescoFtpPort" value="121"> </parameter>
 <parameter name="alfrescoHost" value="http://x.x.x.x"> </parameter>
 <parameter name="alfrescoLogin" value="admin"> </parameter>
 <parameter name="alfrescoNodeLimit" value="50000"> </parameter>
 <parameter name="alfrescoPassword" value="welcome1"> </parameter>
 <parameter name="alfrescoProtocol" value="http"> </parameter>
 <parameter name="alfrescoPort" value="8080"> </parameter>
 <parameter name="alfrescoType" value="Alfresco_5_1"> </parameter>
 <parameter name="databaseBin" value="/usr/bin"> </parameter>
 <parameter name="databaseType" value="mySql"> </parameter>
 <parameter name="databaseHost" value="localhost"> </parameter>
 <parameter name="databaseLogin" value="root"> </parameter>
 <parameter name="databasePassword" value="xxxxx"> </parameter>
 <parameter name="databasePort" value="3303"> </parameter>
 <parameter name="databaseSchemaAAAR" value="AAAR_DataMart"> </parameter>
 <parameter name="databaseSchemaKettle" value="AAAR_Kettle"> </parameter>
 <parameter name="databaseSchemaMain" value="root"> </parameter>
 <parameter name="kettlePath" value="/opt/data-integration"> </parameter>
 <parameter name="kettleVersion" value="6"> </parameter>
 <parameter name="pentahoAAARPath" value="/public/AAAR"> </parameter>
</AAAR>

I am getting below error while executing AAAR_Extract.sh and log file says "dummy". I am not sure what is wrong with connection. Please guide me in troubleshooting.

 sh AAAR_Extract.sh

A.A.A.R. - Alfresco Audit Analysis and Reporting
Import procedure version 4.2.
Author: Francesco Corti (all rights reserved)
Date: 21 February 2016
Documentation and tips: http://fcorti.com.

Disclaimer:
We can't be responsible for any damage done to your system,
which hopefully will not happen.

You are going to execute this command:
./kitchen.sh /rep:AAAR_Kettle /job:"Get all" /dir:/Alfresco /user:admin /pass:admin /param:get_audit=true /param:get_nodes=true /param:get_workflows=true /log="/oracle/app/pentaho/pentaho-server/pentaho-solutions/system/AAAR/endpoints/kettle/script/../logs/AAAR.log" /levelebug

Press <enter> to start import.

#######################################################################
WARNING:  no libwebkitgtk-1.0 detected, some features will be unavailable
    Consider installing the package with apt-get or yum.
    e.g. 'sudo apt-get install libwebkitgtk-1.0-0'
#######################################################################
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0
14:51:21,421 INFO  [KarafBoot] Checking to see if org.pentaho.clean.karaf.cache is enabled
14:51:21,528 INFO  [KarafInstance]
*******************************************************************************
*** Karaf Instance Number: 1 at /oracle/app/pentaho/data-integration/./syst ***
***   em/karaf/caches/kitchen/data-1                                        ***
*** Karaf Port:8802                                                         ***
*** OSGI Service Port:9051                                                  ***
*******************************************************************************
May 15, 2017 2:51:22 PM org.apache.karaf.main.Main$KarafLockCallback lockAquired
INFO: Lock acquired. Setting startlevel to 100
2017/05/15 14:51:22 - Kitchen - Logging is at level : Debug
2017/05/15 14:51:22 - Kitchen - Repository and username supplied
2017/05/15 14:51:22 - Kitchen - Start of run.
2017/05/15 14:51:22 - Kitchen - Allocate new job.
2017/05/15 14:51:22 - Kitchen - Parsing command line options.
2017/05/15 14:51:22 - Kitchen - Loading available repositories.
2017/05/15 14:51:22 - RepositoriesMeta - Reading repositories XML file: /oracle/app/pentaho/data-integration/repositories.xml
2017/05/15 14:51:22 - RepositoriesMeta - We have 1 connections...
2017/05/15 14:51:22 - RepositoriesMeta - Looking at connection #0
2017/05/15 14:51:22 - RepositoriesMeta - Read at connection: AAAR_Kettle
2017/05/15 14:51:22 - RepositoriesMeta - We have 1 repositories...
2017/05/15 14:51:22 - RepositoriesMeta - Looking at repository #0
2017/05/15 14:51:22 - RepositoriesMeta - Read at repository: AAAR_Kettle
2017/05/15 14:51:22 - Kitchen - Finding repository [AAAR_Kettle]
2017/05/15 14:51:22 - Kitchen - Allocate & connect to repository.
Processing stopped because of an error:
Error connecting to the repository!
Error occurred while trying to connect to the database
Access denied for user 'root'@'xxxxxx.com.xxx' (using password: YES)
ERROR: Kitchen can't continue because the job couldn't be loaded.

Extraction complete!

Log file has been generated in the file below:
/oracle/app/pentaho/pentaho-server/pentaho-solutions/system/AAAR/endpoints/kettle/script/../logs/AAAR.log

[fcorti]

Hi,

It seems you don't have AAAR_Kettle database installed or it is not reachable from the Kettle instance.

Please try to run spoon and have access to the AAAR repository.

You should experience the same issue and it should be faster to solve.

I hope this will help you.

[manirangasamy]

Database is created.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| AAAR_DataMart      |
| AAAR_Kettle        |
| hibernate          |
| jackrabbit         |
| mysql              |
| quartz             |
| test               |
+--------------------+
8 rows in set (0.00 sec)

Ran the ./spoon.sh -rep=KettleDatabaseRepository -user=admin -pass=admin. As you said, it is not connecting to database.

and I got below error. I am not sure where to configure the database and how to troubleshoot. I am using MySQL server and updated the relevant drivers in the respective directories.

This is the

cat repositories.xml
<?xml version="1.0" encoding="UTF-8"?>
<repositories>
  <connection>
    <name>AAAR_Kettle</name>
    <type>MYSQL</type>
    <access>JNDI</access>
    <database>AAAR_Kettle</database>
  </connection>
  <repository>
    <id>KettleDatabaseRepository</id>
    <name>AAAR_Kettle</name>
    <description>AAAR_Kettle</description>
    <connection>AAAR_Kettle</connection>
  </repository>
</repositories>
 

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
2017/05/16 16:39:24 - RepositoriesMeta - Reading repositories XML file: /oracle/app/pentaho/data-integration/repositories.xml
2017/05/16 16:39:26 - RepositoriesMeta - Reading repositories XML file: /oracle/app/pentaho/data-integration/repositories.xml
2017/05/16 16:39:27 - Spoon - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Sorry, we didn't recognize those connection details. Please check the user name and password and try again.
2017/05/16 16:39:38 - Spoon - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : No repositories defined on this system.

[fcorti]

Ok, good.
Probably it is corrupted or not correctly imported.
Once imported, you should be able to connect to it using Spoon.

Login and password for the repository are admin/admin.

I hope this help you.

[manirangasamy]

I am missing something.

"Once imported, you should be able to connect to it using Spoon."

Is it "imported" means, import data from Alfresco to AAAR_Kettle database using AAAR_Extract.sh?

If yes, this is not working because of connecting to mySQL database and same reason spoon also not loading.

I might be missed some configuration related to connection, it will be great to help me troubleshoot the connection issues to my MySql server

Regards

Mani

[manirangasamy]

Hi Francesco,

I moved on from this error and AAAR_Extract.sh is executing. script ended after 1 minutes with below error

2017/05/17 11:28:52 - Modified Java Script Value.0 - No starting Script found!
2017/05/17 11:28:52 - stg_workflow_instances.0 - Prepared statement : INSERT INTO stg_workflow_instances (alfresco_id, id, url, name, title, description, isActive, startDate, message, endDate, dueDate, package, initiator, definitionUrl) VALUES ( ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?)
org.pentaho.di.core.exception.KettleValueException:
Javascript error:
TypeError: Cannot read property "userName" from null (script#48)
        at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.addValues(ScriptValuesMod.java:475)
        at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.processRow(ScriptValuesMod.java:541)
        at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.mozilla.javascript.EcmaError: TypeError: Cannot read property "userName" from null (script#48)
        at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3785)
        at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3763)
        at org.mozilla.javascript.ScriptRuntime.typeError(ScriptRuntime.java:3791)
        at org.mozilla.javascript.ScriptRuntime.typeError2(ScriptRuntime.java:3810)
        at org.mozilla.javascript.ScriptRuntime.undefReadError(ScriptRuntime.java:3823)
        at org.mozilla.javascript.ScriptRuntime.getObjectProp(ScriptRuntime.java:1508)
        at org.mozilla.javascript.gen.script_6._c_script_0(script:48)
        at org.mozilla.javascript.gen.script_6.call(script)
        at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:426)
        at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3178)
        at org.mozilla.javascript.gen.script_6.call(script)
        at org.mozilla.javascript.gen.script_6.exec(script)
        at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.addValues(ScriptValuesMod.java:388)
        ... 3 more
2017/05/17 11:28:52 - Modified Java Script Value.0 - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Unexpected error
2017/05/17 11:28:52 - Modified Java Script Value.0 - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : org.pentaho.di.core.exception.KettleValueException:
2017/05/17 11:28:52 - Modified Java Script Value.0 - Javascript error:
2017/05/17 11:28:52 - Modified Java Script Value.0 - TypeError: Cannot read property "userName" from null (script#48)
2017/05/17 11:28:52 - Modified Java Script Value.0 -
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.addValues(ScriptValuesMod.java:475)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.processRow(ScriptValuesMod.java:541)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at java.lang.Thread.run(Thread.java:745)
2017/05/17 11:28:52 - Modified Java Script Value.0 - Caused by: org.mozilla.javascript.EcmaError: TypeError: Cannot read property "userName" from null (script#48)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3785)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3763)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.ScriptRuntime.typeError(ScriptRuntime.java:3791)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.ScriptRuntime.typeError2(ScriptRuntime.java:3810)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.ScriptRuntime.undefReadError(ScriptRuntime.java:3823)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.ScriptRuntime.getObjectProp(ScriptRuntime.java:1508)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.gen.script_6._c_script_0(script:48)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.gen.script_6.call(script)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:426)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3178)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.gen.script_6.call(script)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.mozilla.javascript.gen.script_6.exec(script)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.addValues(ScriptValuesMod.java:388)
2017/05/17 11:28:52 - Modified Java Script Value.0 -    ... 3 more
child index = 1, logging object : org.pentaho.di.core.logging.LoggingObject@6e6ea1ab parent=8943465d-663b-4e0b-b700-f8c04550efb4
2017/05/17 11:28:52 - Modified Java Script Value.0 - Finished processing (I=0, O=0, R=1, W=11, U=0, E=1)
2017/05/17 11:28:52 - Get workflow instances - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Errors detected!
2017/05/17 11:28:52 - Get workflow instances - Transformation detected one or more steps with errors.

[fcorti]

Ah...

This is an unexpected null value into the ETL in a javascript transformation.

I solved in Alflytics solution but for your version of AAAR you need to patch the source code.

I can give you guidance on this.

If available launch Spoon (the PDI user interface) and access to the repository and to the ETL source code.

Then I'll tell you where to change the code.

Please confirm.

[manirangasamy]

Hi Francesco,

I am able to lunch Spoon. Please guide me how to access the repository and ETL source code to fix the code.

Another question? Is this fixed in latest version of AAAR? Can I download try it?

Regards

Mani

[fcorti]

Hi,

Open the transformation named 'Get workflow instances' in the /Alfresco/Staging/Utility folder and edit the javascript task.

Inside the source code there is there is a line similar to this:

newRow[fieldNum++] = (wfInstance.initiator != null) ? wfInstance.initiator.userName : null;

Change the line according to the following source code:

newRow[fieldNum++] = (wfInstance.initiator != null) ? (wfInstance.initiator.userName != null ? wfInstance.initiator.userName : null) : null;

Save and extract again... the error should be solved.

About the newest version of AAAR:
It's called Alflytics (GitHub - fcorti/Alflytics: The Open Source Business Intelligence solution over the Alfresco platform... ).

I'm working on the latest tuning and documentation... so today is not yet released.

I hope this help you.

[manirangasamy]

Hi Francesco,

Thank you for response.

I am totally new to pentaho and spoon.

Please guide me or refer some documentation how to reach the code using spoon

I am able to lunch spoon. but not able to locate the source code.

I tried opening the all the *.ktr extension files under AAAR folder but not able to locate it. I am not sure I am doing right thing.

Regards

Mani