New reporting functionality

edward.borst

I have compiled nxreporting from source (1.2.16), Configured the datasources to use mysql.
So far so good. nxreporting server is started (after creating the QUARTZ table in mysql)
from the client I can see the IP inventory report, but when I click it I get mysql connect errors in the reporting console log.

attached I have the log with debug switched on.

nxreporting.properties is:

# Workspace root. Contains deployed report definitions and output files
nxreporting.workspace=./workspace

# Datasource used by nxReporting itself to store job and schedule details
system.datasource.driverClassName=com.mysql.jdbc.Driver
system.datasource.url=jdbc:mysql://127.0.0.1/netxms
system.datasource.dialect=org.hibernate.dialect.MySQLDialect
system.datasource.username=netxms
system.datasource.password=<password>

# Datasource passed to Jasper Reports
report.datasource.driverClassName=com.mysql.jdbc.Driver
report.datasource.url=jdbc:mysql://127.0.0.1/netxms
report.datasource.username=netxms
report.datasource.password=<password>

report.datasource2.driverClassName=com.mysql.jdbc.Driver
report.datasource2.url=jdbc:mysql://127.0.0.1/netxms
report.datasource2.username=netxms
report.datasource2.password=<password>

org.quartz.dataSource.myDS.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.myDS.URL = jdbc:mysql://127.0.0.1/netxms
org.quartz.dataSource.myDS.user = netxms
org.quartz.dataSource.myDS.password = <password>

From Mysql I can see that it is connection, so the URL is correct.
What could be the problem?

nxreporting.output

Victor Kirhenshtein

Hi!

I see lot of errors like "Access denied for user 'netxms'@'netxms01' (using password: YES)" in your log. Are you sure everything is correct with password and access control for this user?

Best regards,
Victor

edward.borst

username and password are 100% correct.
same username and password as netxmsd is using.
Also same host.
the most strange thing is that the quartz scheduler is accessing the database correct.
looks like the password is not parsed corectly.
is there a way to debug this?

Alex Kirhenshtein

Hello.

For historical reasons (however I'll change that in a future), all password except quartz's, should be encrypted by nxencpasswd:

AirAlk:~() $ nxencpasswd netxms netxms1
H02kxYckADXCpgp+8SvHuMKmCn7xK8e4wqYKfvErx7g=

Sample config (login=netxms, password=netxms1):

system.datasource.username = netxms
system.datasource.password = H02kxYckADXCpgp+8SvHuMKmCn7xK8e4wqYKfvErx7g=
report.datasource.username = netxms
report.datasource.password = H02kxYckADXCpgp+8SvHuMKmCn7xK8e4wqYKfvErx7g=
org.quartz.dataSource.myDS.user = netxms
org.quartz.dataSource.myDS.password = netxms1

edward.borst

Much better!!

the only thing is that I'm missing some tables.(reporting_results and report_notification)
In the source I see the scripts for Oracle, but not for mysql.
Could you please add them?
maybe also include the QUARTZ scripts

Regards,
Edward

edward.borst

Any update on the missing tables?

Thanks,
Edward

Alex Kirhenshtein

Hello.

I just pushed schema file into git: http://git.netxms.org/public/netxms.git/blob/develop:/src/java/nxreporting/sql/mysql/nxreporting.sql

edward.borst

Thanks!

I have created these tables and now I can execute a report.
I see results in the tables, but I do not see anything in the console.
I can add a schedule, this is shown in the console correctly.
Even when using the schedule I do not see any results.
Also when adding a notification to my email address I do not receive an email.
I see the actions in the reporting server log as:
Hibernate: insert into reporting_results (executionTime, jobId, reportId, userId) values (?, ?, ?, ?)
Hibernate: select notificati0_.id as id1_0_, notificati0_.attach_format_code as attach_f2_0_, notificati0_.jobid as jobid3_0_, notificati0_.mail as mail4_0_, notificati0_.report_name as report_n5_0_ from report_notification notificati0_ where jobid=?
Hibernate: select notificati0_.id as id1_0_, notificati0_.attach_format_code as attach_f2_0_, notificati0_.jobid as jobid3_0_, notificati0_.mail as mail4_0_, notificati0_.report_name as report_n5_0_ from report_notification notificati0_ where jobid=?
Hibernate: select reportresu0_.id as id1_1_, reportresu0_.executionTime as executio2_1_, reportresu0_.jobId as jobId3_1_, reportresu0_.reportId as reportId4_1_, reportresu0_.userId as userId5_1_ from reporting_results reportresu0_ where reportresu0_.reportId=? order by reportresu0_.executionTime desc

so it is doing something!

edward.borst

Hi Alex,

There seems a problem in the table script with the column reportId.
As I can see from the source that is is an hybernate uuid binary column I think it should be binary(16) instead of binary(255).
I have tried it (recreated the table) and now it is showing up in the console.

Only thing is that I can't render the results. I'm getting the following error in the nxreporting log:
Exception in thread "Thread-9" java.lang.IllegalArgumentException: 32768 > 16384
at java.util.Arrays.copyOfRange(Arrays.java:2549)
at com.radensolutions.reporting.infrastructure.impl.TcpConnector$SessionWorker.sendFileData(TcpConnector.java:232)
at com.radensolutions.reporting.infrastructure.impl.TcpConnector$SessionWorker.run(TcpConnector.java:174)
at java.lang.Thread.run(Thread.java:722)

edward.borst

the iligalArgumentExeption is coming from the Arrays.copyOfRange
Arrays.copyOfRange should have start and end instead of position and length.
After changing this to the following code this works.

		private void sendFileData(final long requestId, final byte[] data) throws IOException
		{
			NXCPMessage msg = new NXCPMessage(NXCPCodes.CMD_FILE_DATA, requestId);
			msg.setBinaryMessage(true);

			for(int start = 0; start < data.length; start += 16384)
			{
				int end = start+16384;
				if(end>data.length) end=data.length;
				msg.setBinaryData(Arrays.copyOfRange(data, start, end));
				sendMessage(msg);
			}
		}

After deploying it I can execute a report and the results are shown in the console.
Now when rendering I do not get any error, but the console is executing the job for a very long time.
After that I get the following error in the console:
Cannot render report IP Inventory (job ID 5277760b-41b4-4520-ac0a-95144c78367b): Input/Output error

edward.borst

Some additional info:
in the nxmc log I have this info:

!ENTRY org.netxms.ui.eclipse.console 4 0 2014-08-27 10:57:26.046
!MESSAGE Exception in ConsoleJob
!STACK 0
org.netxms.client.NXCException: Input/Output error
	at org.netxms.client.NXCSession.renderReport(NXCSession.java:7474)
	at org.netxms.ui.eclipse.reporter.widgets.ReportExecutionForm$11.runInternal(ReportExecutionForm.java:496)
	at org.netxms.ui.eclipse.jobs.ConsoleJob.run(ConsoleJob.java:85)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)

!ENTRY org.netxms.ui.eclipse.reporter 4 16 2014-08-27 10:57:26.051
!MESSAGE Cannot render report IP Inventory (job ID 7c3839e0-9543-4f29-881d-ee7313b57b61): Input/Output error

in my temp folder there is a file created: nxc323266819402099091data
If I rename this file to .pdf I can open the report

Hope this will help finding the issue.
Regards,

Edward

multix

@edward.borst said in New reporting functionality:

Hi Alex,

There seems a problem in the table script with the column reportId.
As I can see from the source that is is an hybernate uuid binary column I think it should be binary(16) instead of binary(255).
I have tried it (recreated the table) and now it is showing up in the console.

Only thing is that I can't render the results. I'm getting the following error in the nxreporting log:
Exception in thread "Thread-9" java.lang.IllegalArgumentException: 32768 > 16384
at java.util.Arrays.copyOfRange(Arrays.java:2549)
at com.radensolutions.reporting.infrastructure.impl.TcpConnector$SessionWorker.sendFileData(TcpConnector.java:232)
at com.radensolutions.reporting.infrastructure.impl.TcpConnector$SessionWorker.run(TcpConnector.java:174)
at java.lang.Thread.run(Thread.java:722)

I am getting same error after changing binary(255) to binary(16). I did not change anything else.

I had a look to output directory, and saw that report has been created. I tried to open it with jaspersoft studio, it succeeded and I could export it as pdf with in jaspersoft studio. But I can't do this in netxms console.

I hope, it will be corrected in ashort time.

red

develop version v2.0 dont have this bug, so i mix some code from v2.0 to 1.2.17 for fastfix
replace method
private void sendFileData(final long requestId, final byte[] data)
in
src\java\nxreporting\src\main\java\com\radensolutions\reporting\infrastructure\impl\TcpConnector.java
with

/**
         * @param requestId
         * @param data
         * @throws IOException
         */
        private void sendFileData(final long requestId, final byte[] data) throws IOException {
            NXCPMessage msg = new NXCPMessage(NXCPCodes.CMD_FILE_DATA, requestId);
            msg.setBinaryMessage(true);
            boolean success = false;
            for (int pos = 0; pos < data.length; pos += 16384) {
                int len = Math.min(16384, data.length - pos);
                msg.setBinaryData(Arrays.copyOfRange(data, pos, pos + len));
                sendMessage(msg);
                if (pos + len == data.length )
                {
                    success = true;
                    break;
                }
            }
            if (success)
            {
                msg.setEndOfFile(true);
                msg.setBinaryData(new byte[0]);
                sendMessage(msg);
            }
            else
           {
              NXCPMessage abortMessage = new NXCPMessage(NXCPCodes.CMD_ABORT_FILE_TRANSFER, requestId);
              abortMessage.setBinaryMessage(true);
              sendMessage(abortMessage);
           }
        }

NetXMS Forum

New reporting functionality