topic Re: Publish error on Select SQL in Alfresco Forum

Publish error on Select SQL

jsimonet — Tue, 16 May 2017 05:22:15 GMT

Hello All,I am having issues with the AAAR_Publish. It is apparently unable to launch the select statement which I could launch using an Pgadmin with same login.It seems that the login being used by the AAAR_Publish is not correct...for the extract I had to open the transformations using spoon and t

Re: Publish error on Select SQL

fcorti — Tue, 16 May 2017 06:56:02 GMT

Hi Jeremie,

The publish task uses a different login/password and IP for Alfresco
This is because you could want to extract informations from one (or more) Alfresco instances and publish reports on different Alfresco instances (or one only).

Please check at the following link to understand where to setup the connection parameters to the target Alfresco instances.

http://fcorti.com/alfresco-audit-analysis-reporting/aaar-faq/#q3

Re: Publish error on Select SQL

jsimonet — Tue, 16 May 2017 07:30:34 GMT

Hello Francesco,

The connections to pentaho and alfresco are ok. However the connection to the Postgres sql database is not working correctly it seems:

11:18:56,507 ERROR [PdfExportTask] Failed
org.pentaho.di.core.exception.KettleException:
There was an unexpected error processing report 'C:\Users\jsimonet\AppData\Local\Temp\\audit_use.prpt' to produce file 'C:\Users\jsimonet\AppData\Local\Temp\\audit_use.pdf' with processor: PDF.
Failed at query: select
a.id as instance_id,
a.desc as instance_name
from
dm_dim_alfresco a
order by
a.desc asc

Is there a place where you specify the Database credentials. Because if I use PGadmin I can connect to database?

Or is it another issue?

Re: Publish error on Select SQL

fcorti — Tue, 16 May 2017 09:05:17 GMT

Mmm... this is caused from the report itself, not able to connect to the DB for some reasons.

You can solve editing the report using Pentaho Report Designer and check that the report is using the AAAR_DataMart connection with valid settings.

ATTENTION: The valid settings ARE NOT the Report Designer settings but the Pentaho Data Integration settings. Because you will use the report through the ETL, so in the PDI environment.

Report should use the JNDI connection but you can try replacing the "explicits" connection settings for your environment.

Re: Publish error on Select SQL

jsimonet — Tue, 16 May 2017 18:08:01 GMT

The report seems to be re-created with wrong credentials...no way to change it through report designer. But its confirmed, connection credentials are all wrong. Will try to re-check the connections which I really messed up as far as I can....

Re: Publish error on Select SQL

jsimonet — Wed, 17 May 2017 07:53:42 GMT

Hello Francesco,

I wrongly understood that I should modify the file generated in the Temp folder using Report Designer. But rather I had to modify the report from the repository itself and save it there using the RD. Its working ok now. Up to the point where it is FTPing the file.

2017/05/17 11:46:33 - Report - Starting entry [Carica file con FTP]
2017/05/17 11:46:33 - Carica file con FTP - Using PDIFTPClient override for exists() fix.
2017/05/17 11:46:35 - Carica file con FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Error putting to FTP : Connection refused: connect
2017/05/17 11:46:35 - Carica file con FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : java.net.ConnectException: Connection refused: connect

I am investigating. Surely FTP is not set well...

If you have a clue where to change let me know. thanks.

Jeremie

Re: Publish error on Select SQL

fcorti — Wed, 17 May 2017 08:02:26 GMT

Yes, I was thinking we were not on the same page... but happy to hear you solved.

To change settings about FTP check into the dm_dim_reports table.

I'll be easy to understand how to setup the credentials.

You are close to the solution...

Re: Publish error on Select SQL

jsimonet — Wed, 17 May 2017 08:27:33 GMT

Yes its working now. Issue was I defined ftp port as 21 on top of my global alfresco file and port 1121 at the bottom of my alfresco file. First occurrence took precedence. Remove top entry restarted. Process is in progress...ftp OK. Yaye!

Waiting for the process to finish.

Re: Publish error on Select SQL

jsimonet — Wed, 17 May 2017 10:30:41 GMT

Hello,

I had to change each and every report to set the connection. I did something wrong during the installation surely. Also one should not forget to publish the Report once it has been modified. The connection to modify here is the Custom one which is set to Oracle and JNBI by default. I hope this can help other people having the same issue.

The A.A.A.R. Manual installation by Vicenzo Sorrentino also helped me a lot to debug connection issues.

The Publish process is still in progress. Will let you know when it succeeds...

Re: Publish error on Select SQL

fcorti — Thu, 18 May 2017 06:13:01 GMT

Good job!
And thank you for sharing the solution that worked for you.

Re: Publish error on Select SQL

jsimonet — Mon, 04 Sep 2017 06:21:42 GMT

One more thing to consider is that Symantec Endpoint Protection blocks communication. What I did is disable the software on both side and it worked. Not recommended solution but it worked. If someone knows how to configure it please share.

Re: Publish error on Select SQL

jsimonet — Mon, 04 Sep 2017 06:26:25 GMT

This is not the correct way to do it you have to set the connections as follows in "[Your pdi installation]\data-integration\simple-jndi\jdbc.properties". Change user name, password, ports,dns and ip accordingly

SampleData/type=javax.sql.DataSource
SampleData/driver=org.h2.Driver
SampleData/url=jdbc:h2:file:samples/db/sampledb;IFEXISTS=TRUE
SampleData/user=PENTAHO_USER
SampleData/password=PASSWORD
Quartz/type=javax.sql.DataSource
Quartz/driver=org.hsqldb.jdbcDriver
Quartz/url=jdbc:hsqldb:hsql://localhost/quartz
Quartz/user=pentaho_user
Quartz/password=password
Hibernate/type=javax.sql.DataSource
Hibernate/driver=org.hsqldb.jdbcDriver
Hibernate/url=jdbc:hsqldb:hsql://localhost/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Shark/type=javax.sql.DataSource
Shark/driver=org.hsqldb.jdbcDriver
Shark/url=jdbc:hsqldb:hsql://localhost/shark
Shark/user=sa
Shark/password=

# A.A.A.R. - Alfresco Audit Analysis and Reporting - AAAR_DataMart.
AAAR_DataMart/type=javax.sql.DataSource
AAAR_DataMart/driver=org.postgresql.Driver
AAAR_DataMart/url=jdbcostgresql://[Your DNS name or IP]:5433/AAAR_DataMart
AAAR_DataMart/user=postgres
AAAR_DataMart/password=admin

# A.A.A.R. - Alfresco Audit Analysis and Reporting - AAAR_Kettle.
AAAR_Kettle/type=javax.sql.DataSource
AAAR_Kettle/driver=org.postgresql.Driver
AAAR_Kettle/url=jdbcostgresql://[Your DNS name or IP]/AAAR_Kettle
AAAR_Kettle/user=postgres
AAAR_Kettle/password=admin

Re: Publish error on Select SQL

ganesh_batwal — Mon, 18 Sep 2017 11:23:29 GMT

Hello,

We are also getting the below mention errors while executing the " ./AAAR_Publish.sh" script. We have change the ftp port 21 in alfresco global level and during the AAAR configuration also.. But still not sure why we are getting the ftp connection errors, Kindly check and advice.

2017/09/18 16:50:36 - Pentaho Reporting for pdf.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2017/09/18 16:50:36 - Dummy (do nothing).0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2017/09/18 16:50:36 - Report - Starting entry [Delete prpt]
2017/09/18 16:50:36 - Delete prpt - File [/tmp/audit_use.prpt] deleted!
2017/09/18 16:50:36 - Report - Starting entry [Carica file con FTP]
2017/09/18 16:50:36 - Carica file con FTP - Using PDIFTPClient override for exists() fix.
2017/09/18 16:50:39 - Carica file con FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Error putting to FTP : Login incorrect.
2017/09/18 16:50:39 - Carica file con FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : com.enterprisedt.net.ftp.FTPException: 530 Login incorrect.
2017/09/18 16:50:39 - Carica file con FTP -    at com.enterprisedt.net.ftp.FTPControlSocket.validateReply(FTPControlSocket.java:1194)
2017/09/18 16:50:39 - Carica file con FTP -    at com.enterprisedt.net.ftp.FTPClient.password(FTPClient.java:1855)
2017/09/18 16:50:39 - Carica file con FTP -    at com.enterprisedt.net.ftp.FTPClient.login(FTPClient.java:1777)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.entries.ftpput.JobEntryFTPPUT.execute(JobEntryFTPPUT.java:598)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:724)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:609)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.entries.job.JobEntryJobRunner.run(JobEntryJobRunner.java:69)
2017/09/18 16:50:39 - Carica file con FTP -    at java.lang.Thread.run(Thread.java:748)
2017/09/18 16:50:39 - Report - Finished job entry [Carica file con FTP] (result=[false])
2017/09/18 16:50:39 - Report - Finished job entry [Delete prpt] (result=[false])
2017/09/18 16:50:39 - Report - Finished job entry [Create report] (result=[false])
2017/09/18 16:50:39 - Report - Finished job entry [HTTP] (result=[false])
2017/09/18 16:50:39 - Report - Finished job entry [Create report info] (result=[false])
2017/09/18 16:50:39 - Report all - Finished job entry [Create and upload report] (result=[false])
2017/09/18 16:50:39 - Report all - Finished job entry [Get all id] (result=[false])
2017/09/18 16:50:39 - Report all - Job execution finished
2017/09/18 16:50:39 - Kitchen - Finished!
2017/09/18 16:50:39 - Kitchen - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Finished with errors
2017/09/18 16:50:39 - Kitchen - Start=2017/09/18 16:50:26.943, Stop=2017/09/18 16:50:39.171
2017/09/18 16:50:39 - Kitchen - Processing ended after 12 seconds.

Publication complete!

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

Re: Publish error on Select SQL

jsimonet — Mon, 18 Sep 2017 12:34:40 GMT

Hello Ganesh,

In your case it seems the login is incorrect...as per log file

First use the ftp link in the log file to try to download the file on the target machine. Try it also on the source machine.

source machine = client. target machine = server (Alfresco).

Usually on target machine it will ask for permission to access. It will be an internet explorer pop up. Give the permission. Same on source machine may be asked. Do it on both to make sure. Once you do this once it should work in script.

If ever the link works on target machine and not on source machine then you have something blocking it. For my case it was the Symantec endpoint security. Found out by checking the logs. Disabled it on source machine and it all worked out.

Try these out and let me know if it works.

Regards

Jeremie

Re: Publish error on Select SQL

ganesh_batwal — Thu, 21 Sep 2017 12:27:05 GMT

Thanks for an update..Now the ftp connectivity issue stand resolved after installation of "Alflytics", But now the error message are for FTP Access denied while publication.

2017/09/21 17:51:33 - Pentaho Reporting for pdf.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2017/09/21 17:51:33 - Dummy (do nothing).0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2017/09/21 17:51:33 - Put into Alfresco - Starting entry [Put a file with FTP]
2017/09/21 17:51:33 - Put a file with FTP - Using PDIFTPClient override for exists() fix.
2017/09/21 17:51:33 - Put a file with FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Error putting to FTP : Access denied
2017/09/21 17:51:33 - Put a file with FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : com.enterprisedt.net.ftp.FTPException: 530 Access denied
2017/09/21 17:51:33 - Put a file with FTP -    at com.enterprisedt.net.ftp.FTPControlSocket.validateReply(FTPControlSocket.java:1194)
2017/09/21 17:51:33 - Put a file with FTP -    at com.enterprisedt.net.ftp.FTPClient.password(FTPClient.java:1855)
2017/09/21 17:51:33 - Put a file with FTP -    at com.enterprisedt.net.ftp.FTPClient.login(FTPClient.java:1777)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.entries.ftpput.JobEntryFTPPUT.execute(JobEntryFTPPUT.java:598)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:724)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.Job.execute(Job.java:609)
2017/09/21 17:51:33 - Put a file with FTP -    at org.pentaho.di.job.entries.job.JobEntryJobRunner.run(JobEntryJobRunner.java:69)
2017/09/21 17:51:33 - Put a file with FTP -    at java.lang.Thread.run(Thread.java:748)
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Put a file with FTP] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Create target file from prpt] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [HTTP] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Set tmp files] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get ftpPath from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get ftpPassword from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get ftpLogin from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get ftpPort from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get ftpHost from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get outputType from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get httpCall from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Set queryReportInfo] (result=[false])
2017/09/21 17:51:33 - Report all - Finished job entry [Put into Alfresco] (result=[false])
2017/09/21 17:51:33 - Report all - Finished job entry [Get rows from query] (result=[false])
2017/09/21 17:51:33 - Report all - Finished job entry [Set queryReportIds] (result=[false])
2017/09/21 17:51:33 - Report all - Finished job entry [Check Db connections] (result=[false])
2017/09/21 17:51:33 - Report all - Job execution finished

Re: Publish error on Select SQL

fcorti — Thu, 21 Sep 2017 14:32:41 GMT

Did you setup the correct endpoint, username and password for the reports into the DWH_REPORTS table?
Please check the Alflytics/Alflytics-Manual.pdf at master · fcorti/Alflytics · GitHub at page 44.

Re: Publish error on Select SQL

amar7295 — Thu, 14 Jan 2021 04:42:16 GMT

hello @ganesh_batwal