Mysql Stored Procedure in Report fails
17 January, 2013
Hi,
I created a view to our mysql database, and used the stored procedure option. Our stored procedure contains 4 parameters, and yellowfin picked it up correctly. If I complete the parameters in the view, it works 100% and the data is returned. Our normal call in mysql is
call rpt_velocity('2012-09-25','2012-09-26','R24','D');
When I create a report however, with this new view, and the exact same parameters as part of the filter,
I get the following
"Error retrieving results.An error occurred retrieving the results from the database.
Please check the SQL syntax and try again."
I am not sure how to troubleshoot as the yellowfin.log file only shows:
Error running report stored procedure: java.lang.NullPointerException
Please help
I created a view to our mysql database, and used the stored procedure option. Our stored procedure contains 4 parameters, and yellowfin picked it up correctly. If I complete the parameters in the view, it works 100% and the data is returned. Our normal call in mysql is
call rpt_velocity('2012-09-25','2012-09-26','R24','D');
When I create a report however, with this new view, and the exact same parameters as part of the filter,
I get the following
"Error retrieving results.An error occurred retrieving the results from the database.
Please check the SQL syntax and try again."
I am not sure how to troubleshoot as the yellowfin.log file only shows:
Error running report stored procedure: java.lang.NullPointerException
Please help
Hi,
so far I have been unable to recreate this issue -
I created a stored procedure using a MySQL db:
then created the view in Yellowfin and as you found, it worked 100% and the data was returned:
the thing is, I then created a report and used the exact same values but the report worked correctly:
So, we've got to find out what is different between your environment and mine, I used MySQL 5.5 and Yellowfin 6.2 build 20121220, what versions and builds did you use?
Regards,
Dave
so far I have been unable to recreate this issue -
I created a stored procedure using a MySQL db:
then created the view in Yellowfin and as you found, it worked 100% and the data was returned:
the thing is, I then created a report and used the exact same values but the report worked correctly:
So, we've got to find out what is different between your environment and mine, I used MySQL 5.5 and Yellowfin 6.2 build 20121220, what versions and builds did you use?
Regards,
Dave
Hi,
Very weird. And I have tried this with a couple of other stored procs as well, and exactly the same. My Mysql version is: 5.5.24-0ubuntu0.12.04.1
An my Yellowfinbi is 6.2 I think. Where can I check the exact version?
Thanks for your help. It is greatly appreciated!!
Regards
Johan
Very weird. And I have tried this with a couple of other stored procs as well, and exactly the same. My Mysql version is: 5.5.24-0ubuntu0.12.04.1
An my Yellowfinbi is 6.2 I think. Where can I check the exact version?
Thanks for your help. It is greatly appreciated!!
Regards
Johan
This is from my yellowfinbi log file:
YF:2013-01-18 14:42:56:ERROR (ReportRunner:H) - Error running report stored procedure: java.lang.NullPointerException
java.lang.NullPointerException
at com.hof.mi.process.MIReportGeneratorProcess.A(MIReportGeneratorProcess.java:13160)
at com.hof.mi.process.MIReportGeneratorProcess.A(MIReportGeneratorProcess.java:7871)
at com.hof.mi.process.ReportRunner.H(ReportRunner.java:1145)
at com.hof.mi.process.ReportRunner.D(ReportRunner.java:981)
at com.hof.mi.process.ReportRunner.runReport(ReportRunner.java:265)
at com.hof.mi.process.MIReportGeneratorProcess.runReport(MIReportGeneratorProcess.java:109)
at com.hof.mi.process.MIReportGeneratorProcess.runReport(MIReportGeneratorProcess.java:82)
at com.hof.mi.web.action.MIReportPreviewAjaxAction.runAction(MIReportPreviewAjaxAction.java:311)
at com.hof.web.action.AjaxAction.execute(AjaxAction.java:142)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:722)
YF:2013-01-18 14:42:56: INFO (HtmlExporter:D) - Exporting report to HTML (48808: Draft Report)
YF:2013-01-18 14:42:56: INFO (AjaxAction:execute) - MIReportPreviewAjaxAction exiting
YF:2013-01-18 14:42:56:ERROR (ReportRunner:H) - Error running report stored procedure: java.lang.NullPointerException
java.lang.NullPointerException
at com.hof.mi.process.MIReportGeneratorProcess.A(MIReportGeneratorProcess.java:13160)
at com.hof.mi.process.MIReportGeneratorProcess.A(MIReportGeneratorProcess.java:7871)
at com.hof.mi.process.ReportRunner.H(ReportRunner.java:1145)
at com.hof.mi.process.ReportRunner.D(ReportRunner.java:981)
at com.hof.mi.process.ReportRunner.runReport(ReportRunner.java:265)
at com.hof.mi.process.MIReportGeneratorProcess.runReport(MIReportGeneratorProcess.java:109)
at com.hof.mi.process.MIReportGeneratorProcess.runReport(MIReportGeneratorProcess.java:82)
at com.hof.mi.web.action.MIReportPreviewAjaxAction.runAction(MIReportPreviewAjaxAction.java:311)
at com.hof.web.action.AjaxAction.execute(AjaxAction.java:142)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:722)
YF:2013-01-18 14:42:56: INFO (HtmlExporter:D) - Exporting report to HTML (48808: Draft Report)
YF:2013-01-18 14:42:56: INFO (AjaxAction:execute) - MIReportPreviewAjaxAction exiting
Hi Johan,
could you please double-check the version and build number of Yellowfin and let us know, here is a link to a forum post that explains how to do this. We had a look at the latest build of 6.2 at the line where the null pointer was thrown and there were no real clues there.
Also, could you please check the MySQL driver version, you can do this by doing the Connection Test in Step 1 of the Data Source builder - the driver information is shown after a successful test:
Regards,
Dave
could you please double-check the version and build number of Yellowfin and let us know, here is a link to a forum post that explains how to do this. We had a look at the latest build of 6.2 at the line where the null pointer was thrown and there were no real clues there.
Also, could you please check the MySQL driver version, you can do this by doing the Connection Test in Step 1 of the Data Source builder - the driver information is shown after a successful test:
Regards,
Dave
Hi,
Application Version: 6.2
java.version: 1.7.0_11
os.name: Mac OS X
os.arch: x86_64
os.version: 10.8.2
And the mysql driver is :
Connection Succeeded
Database: MySQL
Product Version: 5.5.24-0ubuntu0.12.04.1
Driver: MySQL-AB JDBC Driver
Driver Version: mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq )
Database Version: 5.5
Source Name: MySQL
As mentioned earlier, when I create the database connection and view, it pulls in the data 100%, from the stored procedure. So its only when I create a report that it returns the error.
Pls let me know
Johan
Application Version: 6.2
java.version: 1.7.0_11
os.name: Mac OS X
os.arch: x86_64
os.version: 10.8.2
And the mysql driver is :
Connection Succeeded
Database: MySQL
Product Version: 5.5.24-0ubuntu0.12.04.1
Driver: MySQL-AB JDBC Driver
Driver Version: mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq )
Database Version: 5.5
Source Name: MySQL
As mentioned earlier, when I create the database connection and view, it pulls in the data 100%, from the stored procedure. So its only when I create a report that it returns the error.
Pls let me know
Johan
Hi Johan,
thanks for the info, it looks like we are using the same version and build of Yellowfin, the same version of MySQL and the same MySQL driver, and same version of java, so unfortunately that rules out the possibility of an upgrade solving the issue.
Because we can't reproduce the issue over here we'll need your Yellowfin configuration database and also your datasource over here so we can investigate the issue further.
I have created an FTP site for you where you can upload it:
ftp://ftp.yellowfinteam.com/TinManDigital/
Username : upload
Password : Yellowfin2
Please let me know if you have any issues sending this across.
Regards,
Dave
thanks for the info, it looks like we are using the same version and build of Yellowfin, the same version of MySQL and the same MySQL driver, and same version of java, so unfortunately that rules out the possibility of an upgrade solving the issue.
Because we can't reproduce the issue over here we'll need your Yellowfin configuration database and also your datasource over here so we can investigate the issue further.
I have created an FTP site for you where you can upload it:
ftp://ftp.yellowfinteam.com/TinManDigital/
Username : upload
Password : Yellowfin2
Please let me know if you have any issues sending this across.
Regards,
Dave