SQL generation failed: java.lang.NullPointerException

Dim Pap shared this problem 2 years ago
Resolved

Hello,

I am facing an issue which I cannot find the cause of it.

I have a simple report, just selecting some columns from a datamart, without extra joins.

This report exists in our ACCEPTANCE environment and in PRODUCTION. I wanted to do a change and add a new column. My steps were:

1. Export the Report from PR

2. Inserting it in ACC

3. Adding the new column

4. Check the 'Design' tab, all the data were there, the report worked fine

5. Run the filters

6. Cash the filters

7. As all the above were working, I click 'publish'

After that, I got the error:

2e6f43cd4da5f40d9bc780166a2bc2f7

In the logs, I have the following:

YF:2017-12-15 10:26:01:ERROR (ReportWrapperBean:usesDynamicFilters) - java.lang.NullPointerException

java.lang.NullPointerException

YF:2017-12-15 10:26:01: INFO (OrgCache:getOrgRefCodeDesc) - Null reference code requested

YF:2017-12-15 10:26:02: INFO (ColourPickerAjaxAction:runAction) - ColourPickerAjaxAction entered

YF:2017-12-15 10:26:03:ERROR (MIReportGeneratorProcess:generateSql) - SQL generation failed: java.lang.NullPointerException

java.lang.NullPointerException

YF:2017-12-15 10:26:03:ERROR (StandardResultSetGenerator:generateResultSet) - Error generating SQL for report: java.lang.NullPointerException

java.lang.NullPointerException


In addition, If I try to cash the filters after that error, I get:

d7092e18de6703375c7168e6cd2b46fd

and in the logs I have:

YF:2017-12-15 10:49:27: WARN (CachedFilterProcess:E) - Failed to load source 54704

YF:2017-12-15 10:49:27:ERROR (CachedFilterProcess:refreshCachedFilters) - Error: java.lang.Exception: Failed to load values from all sources.

java.lang.Exception: Failed to load values from all sources.

at com.hof.mi.process.CachedFilterProcess.E(CachedFilterProcess.java:1602)

at com.hof.mi.process.CachedFilterProcess.A(CachedFilterProcess.java:1233)

at com.hof.mi.process.CachedFilterProcess.refreshCachedFilters(CachedFilterProcess.java:423)

at com.hof.mi.process.MIReportProcess.refreshReportCachedFilters(MIReportProcess.java:21261)

at com.hof.mi.web.action.MIReportOutputAjaxAction.handleAction(MIReportOutputAjaxAction.java:1864)

at com.hof.mi.web.action.AbstractReportOutputAjaxAction.runAction(AbstractReportOutputAjaxAction.java:1348)

at com.hof.web.action.AjaxAction.execute(AjaxAction.java:155)

at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)

at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:229)

at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)

at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at com.hof.servlet.BrowserHeaderFilter.doFilter(BrowserHeaderFilter.java:43)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:613)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:745)


During my investigation, I did exact the same steps with another report, and it worked.

Also, I copied the data from PROD to ACC so I can have the same data set under the e-mail.

To solve the issue, I did the change in PROD directly and no error happened then.

But I would like to solve this issue, please :)


Regards,

Dim

Comments (26)

photo
1

Hi Dim,

Thanks for your email.

Sorry to see you got an error.

Can you please run the Yellowfin in Debug mode and perform the same steps?

Please click here to learn on how to turn on debug mode.

Send us all the log files and the time the action performed.

Please let us know if you have any questions.

Regards,

Mahe

photo
1

Hello Mahe,

I did what you said and you can see the log files using the following link:

https://www.dropbox.com/sh/zvbuqrmtial8wv7/AAABhUVxHgBzYJH_9ZmKKUtba?dl=0

At 10.12 I enabled the DEBUG log style and the steps regarding editing the report were done between 10.12 - 10.17.

(Amsterdam time)


Thanks and waiting for your answer,

Dim

photo
2

Hi Dim,

Thanks for sending the logs.

Unfortunately the logs did not finish saving the actions, the last line saved was only until 9:26:57

You may leave it for a bit and check the time in the log and send it to me. Below is the screenshot of Yellowfin.log


1209e43b6fb619e14ce60ad004f5bef8


Let me know if you have any questions.

Regards,

Mahe

photo
1

Hi Mahe,

Amsterdam is 1 hour ahead of Coordinated Universal Time so the 9.26 you see is actually 10.26 AMST time so all the actions I mention are included in the files you have already.

Thanks,

Dim

photo
1

Hi Dim,

Thanks for your reply.

As per your advice, I have looked at the logs again.

If 9:26 is actually 10:26 AMST, I couldn't find anything at 9:12 -9:17 (10:12 - 10:17 AMST), please see below screenshot:

f5d9675590d180324984b147adbd6928

Also the DEBUG is turned off as well.

Anyhow I have looked at the logs for the date 2017-12-15, which you have mentioned in your first email.

After looking at the code we found that the issue is caused due to one or more Cached Filters couldn't get data from the DataSources.

I have tried to replicate it but was not successful. Can you please try to Refresh each and every Cached Filter, and if you still face an issue,

Can you please send us your Yellowfin Config DB and the DataSource with the test data, so that I can restore here and replicate the issue.

I have created a FTP login for you to upload the files when you are ready.

Let me know if you have any questions.

Regards,

Mahe

photo
1

Hi Mahe,

I did as you told me and now I can see I have results during that time. :)The new time is 15.54-15.58 (text's file time).

You can find the files here:

https://www.dropbox.com/sh/9s0qxgzvm2bgi62/AAAnqDomvc-oMKeeelQDYyb0a?dl=0

(I did not receive any e-mail for the FTP log in)

Regards,

Dim

photo
1

Hi Dim,

Thanks for the log files.

I have looked at them and unfortunately, I couldn't see the SQL statement it is trying to generate to find out which filter it is trying to cache and have trouble with.

In this investigation looks like the issue with the fields from the Data Source "dbexpereo_accp".

e4544a69504db09bdc1c66d5aabfe0e7


Are any of these Filters are View level filters? If Yes, please try to create a new report withe using same View filter group and see if it errors out?

Also please send me these 2 Table Data from the Yellowfin Config DB:

ReportFieldTemplate

ReportHeader

and please tell me the Report name, so that I can have a look at the required records in the database tables.

Sometimes the FTP email goes into the SPAM folder, please check the folder.

You may use my FTP credentials incase for uploading the files:

https://au1.hostedftp.com/login/~yellowfin

Username: mahesh.thatipamula@yellowfin.bi

Password: Test123

Please let me know if you have any questions.

Regards,

Mahesh

photo
1

The View has this SQL Statement:

SELECT

"sales_ods_report"."selling_entity",

"sales_ods_report"."site_code",

"sales_ods_report"."quote_name",

"sales_ods_report"."account_manager",

"sales_ods_report"."supplier_name_access",

"sales_ods_report"."api_call",

"sales_ods_report"."supplier_name_equipment",

"sales_ods_report"."customer_order_id",

"sales_ods_report"."customer_contact_email",

"sales_ods_report"."customer_contact_phone",

"sales_ods_report"."availability",

"sales_ods_report"."request_type_current",

"sales_ods_report"."request_type_original",

"sales_ods_report"."invoice_currency",

"sales_ods_report"."ac_status",

"sales_ods_report"."access_technology",

"sales_ods_report"."access_medium",

"sales_ods_report"."additional_address_info",

"sales_ods_report"."ttr_priority_1",

"sales_ods_report"."customer_contact_name",

"sales_ods_report"."address",

"sales_ods_report"."assigned_support",

"sales_ods_report"."city",

"sales_ods_report"."comment",

"sales_ods_report"."commentinternal",

"sales_ods_report"."contract_period_months",

"sales_ods_report"."contract_period_years",

"sales_ods_report"."local_nrc",

"sales_ods_report"."local_mrc",

"sales_ods_report"."country",

"sales_ods_report"."country_code",

"sales_ods_report"."customer",

"sales_ods_report"."date_received",

"sales_ods_report"."delivery_time_weeks",

"sales_ods_report"."end_customer_name",

"sales_ods_report"."local_currency",

"sales_ods_report"."ltq_days",

"sales_ods_report"."parent_name",

"sales_ods_report"."pricing_status",

"sales_ods_report"."qlstatus",

"sales_ods_report"."quote_id",

"sales_ods_report"."quote_line_id",

"sales_ods_report"."region",

"sales_ods_report"."request_id",

"sales_ods_report"."request_status",

"sales_ods_report"."response_date",

"sales_ods_report"."sales_comment",

"sales_ods_report"."service_name",

"sales_ods_report"."service_note",

"sales_ods_report"."invoice_cost_mrc",

"sales_ods_report"."selling_price_mrr",

"sales_ods_report"."selling_price_nrr",

"sales_ods_report"."invoice_cost_nrc",

"sales_ods_report"."invoice_price_mrr",

"sales_ods_report"."invoice_price_nrr",

"sales_ods_report"."site_id",

"sales_ods_report"."site_name",

"sales_ods_report"."site_phone",

"sales_ods_report"."speed_down",

"sales_ods_report"."speed_unit",

"sales_ods_report"."speed_up",

"sales_ods_report"."state_or_province",

"sales_ods_report"."technology",

"sales_ods_report"."zipcode",

"sales_ods_report"."speed_down_access",

"sales_ods_report"."speed_up_access"

FROM "datamart"."sales_ods_report"


The report this one:

SELECT DISTINCT

"sales_ods_report"."date_received",

"sales_ods_report"."response_date",

"sales_ods_report"."ltq_days",

"sales_ods_report"."assigned_support",

"sales_ods_report"."account_manager",

"sales_ods_report"."selling_entity",

"sales_ods_report"."customer",

"sales_ods_report"."country_code",

"sales_ods_report"."country",

"sales_ods_report"."region",

"sales_ods_report"."site_id",

"sales_ods_report"."site_code",

"sales_ods_report"."address",

"sales_ods_report"."additional_address_info",

"sales_ods_report"."city",

"sales_ods_report"."state_or_province",

"sales_ods_report"."zipcode",

"sales_ods_report"."site_phone",

"sales_ods_report"."technology",

"sales_ods_report"."speed_up",

"sales_ods_report"."speed_down",

"sales_ods_report"."speed_unit",

"sales_ods_report"."contract_period_years",

"sales_ods_report"."customer_contact_name",

"sales_ods_report"."customer_contact_email",

"sales_ods_report"."customer_contact_phone",

"sales_ods_report"."sales_comment",

"sales_ods_report"."comment",

"sales_ods_report"."commentinternal",

"sales_ods_report"."request_id",

"sales_ods_report"."quote_line_id",

"sales_ods_report"."quote_id",

"sales_ods_report"."quote_name",

"sales_ods_report"."customer_order_id",

"sales_ods_report"."end_customer_name",

"sales_ods_report"."site_name",

"sales_ods_report"."access_technology",

"sales_ods_report"."speed_up_access",

"sales_ods_report"."speed_down_access",

"sales_ods_report"."service_name",

"sales_ods_report"."supplier_name_access",

"sales_ods_report"."supplier_name_equipment",

"sales_ods_report"."access_medium",

"sales_ods_report"."service_note",

"sales_ods_report"."availability",

"sales_ods_report"."ttr_priority_1",

"sales_ods_report"."contract_period_months",

"sales_ods_report"."local_currency",

"sales_ods_report"."local_nrc",

"sales_ods_report"."local_mrc",

"sales_ods_report"."invoice_currency",

"sales_ods_report"."invoice_cost_nrc",

"sales_ods_report"."selling_price_nrr",

"sales_ods_report"."invoice_price_nrr",

"sales_ods_report"."selling_price_nrr" - "sales_ods_report"."invoice_cost_nrc",

"sales_ods_report"."invoice_price_nrr" - "sales_ods_report"."invoice_cost_nrc",

"sales_ods_report"."invoice_cost_mrc",

"sales_ods_report"."selling_price_mrr",

"sales_ods_report"."invoice_price_mrr",

"sales_ods_report"."selling_price_mrr" - "sales_ods_report"."invoice_cost_mrc",

AVG("sales_ods_report"."selling_price_mrr" - "sales_ods_report"."invoice_cost_mrc"),

"sales_ods_report"."invoice_price_mrr" - "sales_ods_report"."invoice_cost_mrc",

AVG("sales_ods_report"."invoice_price_mrr" - "sales_ods_report"."invoice_cost_mrc"),

"sales_ods_report"."pricing_status",

"sales_ods_report"."ac_status",

"sales_ods_report"."qlstatus",

"sales_ods_report"."delivery_time_weeks",

"sales_ods_report"."request_status",

"sales_ods_report"."api_call",

"sales_ods_report"."request_type_original"

FROM "datamart"."sales_ods_report"

WHERE (

"sales_ods_report"."quote_id" IN ('105')

)

GROUP BY

"sales_ods_report"."supplier_name_equipment",

"sales_ods_report"."invoice_price_nrr",

"sales_ods_report"."selling_entity",

"sales_ods_report"."invoice_cost_mrc",

"sales_ods_report"."contract_period_months",

"sales_ods_report"."pricing_status",

"sales_ods_report"."customer",

"sales_ods_report"."contract_period_years",

"sales_ods_report"."site_id",

"sales_ods_report"."selling_price_mrr" - "sales_ods_report"."invoice_cost_mrc",

"sales_ods_report"."customer_order_id",

"sales_ods_report"."additional_address_info",

"sales_ods_report"."invoice_price_mrr",

"sales_ods_report"."speed_down",

"sales_ods_report"."response_date",

"sales_ods_report"."region",

"sales_ods_report"."site_name",

"sales_ods_report"."api_call",

"sales_ods_report"."selling_price_nrr" - "sales_ods_report"."invoice_cost_nrc",

"sales_ods_report"."local_currency",

"sales_ods_report"."zipcode",

"sales_ods_report"."assigned_support",

"sales_ods_report"."qlstatus",

"sales_ods_report"."customer_contact_name",

"sales_ods_report"."site_code",

"sales_ods_report"."service_name",

"sales_ods_report"."speed_up_access",

"sales_ods_report"."selling_price_mrr",

"sales_ods_report"."account_manager",

"sales_ods_report"."invoice_price_nrr" - "sales_ods_report"."invoice_cost_nrc",

"sales_ods_report"."site_phone",

"sales_ods_report"."speed_up",

"sales_ods_report"."request_id",

"sales_ods_report"."request_type_original",

"sales_ods_report"."end_customer_name",

"sales_ods_report"."customer_contact_phone",

"sales_ods_report"."speed_down_access",

"sales_ods_report"."access_medium",

"sales_ods_report"."invoice_currency",

"sales_ods_report"."country_code",

"sales_ods_report"."commentinternal",

"sales_ods_report"."speed_unit",

"sales_ods_report"."quote_name",

"sales_ods_report"."ac_status",

"sales_ods_report"."invoice_price_mrr" - "sales_ods_report"."invoice_cost_mrc",

"sales_ods_report"."city",

"sales_ods_report"."state_or_province",

"sales_ods_report"."country",

"sales_ods_report"."sales_comment",

"sales_ods_report"."request_status",

"sales_ods_report"."supplier_name_access",

"sales_ods_report"."service_note",

"sales_ods_report"."invoice_cost_nrc",

"sales_ods_report"."availability",

"sales_ods_report"."ttr_priority_1",

"sales_ods_report"."quote_id",

"sales_ods_report"."quote_line_id",

"sales_ods_report"."selling_price_nrr",

"sales_ods_report"."customer_contact_email",

"sales_ods_report"."local_mrc",

"sales_ods_report"."local_nrc",

"sales_ods_report"."address",

"sales_ods_report"."delivery_time_weeks",

"sales_ods_report"."ltq_days",

"sales_ods_report"."date_received",

"sales_ods_report"."technology",

"sales_ods_report"."access_technology",

"sales_ods_report"."comment"


The new column I add is this:

site_code


The name of the report: Request Report

The name of the View: Sales (ODS)

The filters are not View Level Filters.


I did the whole procedure again and noticed that:

When I drag and drop the new column (site_code), in the Data tab, I can see it:

a697163348adf5d965bbe00739584a6f


However, when I click the Design tab, after I run the filters the column is gone.

28e7dfac9bcdf57fc6732191cf1c9c3f


This can show something for our problem.

I also uploaded the files using your account.


Thanks :)

photo
1

Hi Dim,

Thanks for the files.

I have downloaded and can see site_code field in the database under view.

What I found in the ReportHeader table is, the View is in Archived state, that means the View is in Draft mode.

Is it possible for me to access your Yellowfin on Internet, I want to check few things and do some testing.

Let me know if you have any questions.

Regards,

Mahe

photo
1

Hi Mahe,

can you please provide me your e-mail so I can create a user for you?

Thanks,

Dimitra

photo
1

Hi Dim,

Thanks for your reply.

My email address is mahesh.thatipamula@yellowfin.bi

Send me the URL as well.

Let me know if you have any questions.

Regards,

Mahe

photo
1

Hi Mahe,

I sent an e-mail to you with the credentials and link so you can log in :)Let me know what else you need.

Regards,

Dim

photo
1

Hi Dim,

Thanks I was able to login with the details.

I couldn't figure out which report it was, also didn't had permissions to edit or create a report.

Can you please set more permissions for my login, such that I can access views, create, edit, export and import a report.

Let me know the Datasource, and View I can use for this test, also the report which you have an issue with.

Email me if you have any questions.

Regards,

Mahe

photo
1

Hi Mahe,

I could not find what each role means as I have different options that the documentation (http://wiki.yellowfin.com.au/display/USER73Plus/Users)

I updated your role as report writer, is that enough? My other options are Corp Writer or Admin.

The details for the report follow:

Data Source: Redshift Datamart

Data View: Sales_ODS

Report Name: Request Report

New column to be added: site_code

Thanks,

Dim

photo
1

Hi Dim,

Thanks for the details.

I am able to login and test the report.

I have tried to add a new field and when I go to Design page, the field disappears, and if I remove a field and after going to Design page, the field appears.

Looks like, it is not allowing me to make any changes to the report, the reason could be it is a Private Report.

First thing to notice is in the browse page, I am unable to edit the report, I have only option to "Make a copy of Report", even if I make a copy of the report, it is not allowing me to change the columns.

We were able to create a report and save it by using the View: Sales_ODS ( all QL ), but were unable to test it by exporting and importing.

Can you please give me permissions for exporting and importing the report?

Hopefully I will test it tonight and reply back.

Please let me know if you have any questions.

Regards,

Mahe

photo
1

Hi Mahe,

I just gave you rights to import and export files.

The View we need is the Sales_ODS and not the Sales_ODS (all QL).

"I have tried to add a new field and when I go to Design page, the field disappears" this also happened to me.

Let me know :)Dim

photo
1

Hi Dim,

Thanks for the details.

I have created a report using the View Sales_ODS, saved it and reopened that report and found that it is connected with a different view Sales_ODS (all QL).

Looks like something gone wrong with the view.

Can you please tell me how did you create the View Sales_ODS which back porting or reverting back to Sales_ODS (all QL)?

I am not sure why did this happen.

Happy to investigate this further, but I will need your Yellowfin Configuration DB, if you can take a backup and upload to the FTP, Also need the DB schema of your data source (without data).

I will restore them here and investigate further.

Please let me know if you have any questions.

Regards,

Mahe

photo
1

Hi Mahe,

thank you for your investigation. After you indicated there is something wrong with the view,

I deleted both in ACC and insert them together with the reports from PROD using the full import and not the one I used before (which you need to indicate the view for each report).

Now I am able to edit the report again.

I am not sure how these two Views were mixed up and how to prevent it from happening again.

The conclusion is that now, after the full import the reports are working :)

Thank you very much and in case something happens again I will let you know :)

Dim

photo
1

Hi Dim,

Thanks for letting me know that the issue is fixed after deleting the view.

It might have happened while importing, if you have imported view twice.

See how you from now on, and keep track of your steps, so that we know why the issue has occurred if it happens again.

Please let me know your findings.

Regards,

Mahesh

photo
1

Hello,

I think I have the same error again but in a more critical way: Yellowfin does not starting up.

Attached are the recent logs.

Can you please help me?

As long as I know, a view was giving the same error as above and in order to correct it, we copied the view with the same name.

Thanks,

Dim

Files: logs.txt
photo
1

The error I see after starting up the environment is:

"ERROR: Duplicate loading rltshp data"

photo
1

Hi Dim,

Please create a new ticket with high priority if you are unable to start Yellowfin. It is now 10:30 in Australia ( my time zone). Our team members from global team will be able to help you in resolving the issue for you. Other team members will not be able to look into this ticket. I am just checking my emails and saw your email.

Regards,

Mahesh

photo
1

Hi Dim,

Hope you are doing well.

Sorry, I couldn't do a followup on this ticket.

Please let me know if you have any questions.

If everything is working fine, I will mark this as completed.

Regards,

Mahesh

photo
1

Hi Dim,

Hope you are doing well.

Let me know if you have any questions.

Regards,

Mahesh

photo
1

Hi Dim,

Hope you are doing well.

Can you please let me know if this issue is resolved or not?

If the issue is resolved, I will mark this ticket as Resolved.

Regards,

Mahesh

photo
1

Hi Dim,

Hope you are doing well.

I am marking this ticket as resolved for the moment.

If you have any questions, please let me know.

Regards,

Mahesh