[Archivesspace_Users_Group] Problems with ASpace reports
Brian Hoffman
brianjhoffman at gmail.com
Tue Sep 1 17:38:32 EDT 2015
Hi Karrie,
Yes, as it happens I was dealing with the same issue today. You can install this plugin and it will bring back the stored procedures. Please test this on a copy of your database first!
https://github.com/quoideneuf/aspace_stored_procedures <https://github.com/quoideneuf/aspace_stored_procedures>
And I am obliged to say tsk tsk for doing find and replace on your sql dump but will leave it at that if you promise not to do it again!
Brian
> On Sep 1, 2015, at 5:33 PM, Karrie L Roberson <robersonkl at sfasu.edu> wrote:
>
> It returns "empty set". Is there anything we can do to add those stored procedures back?
>
> We had to make some changes to the data that got carried over from the migration from Archon. :/
>
> Thanks,
> Karrie
> From: archivesspace_users_group-bounces at lyralists.lyrasis.org [archivesspace_users_group-bounces at lyralists.lyrasis.org] on behalf of Brian Hoffman [brianjhoffman at gmail.com]
> Sent: Tuesday, September 01, 2015 4:28 PM
> To: Archivesspace Users Group
> Subject: Re: [Archivesspace_Users_Group] Problems with ASpace reports
>
> Hi,
>
> It sounds like you may have lost the mysql stored procedures that the reports require.
>
> What happens when you log in to mysql and type:
>
> > select name from mysql.proc
>
> ?
>
> P.S. What caused you to dump the mysql file, edit it, and reload it? That is not something I recommend doing.
>
>
>
>> On Sep 1, 2015, at 5:08 PM, Karrie L Roberson <robersonkl at sfasu.edu <mailto:robersonkl at sfasu.edu>> wrote:
>>
>> We're seeing the same type of messages as below with the messages that are seen below. Our error message is "ResourcesLocationsListReport - net.sf.jasperreports.engine.JRException: Error executing SQL statement for : resourcesLocationsList".
>>
>> We edited the 'config.rb' file with the jasper reports uncommented, updated the 2 files in the PR issue mentioned below, and restarted Archivesspace.
>>
>> Just FYI: Weeks ago, we've done a command line mysqldump of the database without the "--routines" flag added (with ArchivesSpace shutdown), made some changes to the file and replaced the database with the updated file.
>>
>> Thanks,
>> Karrie
>>
>> “The views and opinions expressed in this message are my own and do not necessarily reflect the views and opinions of Stephen F. Austin State University, its Board of Regents, or the State of Texas.”
>> From: archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org> [archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org>] on behalf of Emma Jolley [EJOLLEY at nla.gov.au <mailto:EJOLLEY at nla.gov.au>]
>> Sent: Sunday, August 30, 2015 11:57 PM
>> To: Archivesspace Users Group
>> Subject: Re: [Archivesspace_Users_Group] Problems with ASpace reports
>>
>> Hi
>>
>> Apologies for the long delay in replying. I can send you a MySQL dump of the AS database – however there is a lot of confidential acquisition information in the database. Are there any procedures for confidential information?
>>
>> Thanks
>>
>> Emma
>>
>> From: archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org> [mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org>] On Behalf Of Nathan Stevens
>> Sent: Friday, 14 August 2015 10:28 PM
>> To: Archivesspace Users Group
>> Subject: Re: [Archivesspace_Users_Group] Problems with ASpace reports
>>
>> Hi,
>>
>> The JDK version shouldn't be a problem. This seems like the report is encountering some data it can't handle. Can you send us a MySQL dump of your ASpace database so we can try and replicate the problem on our end?
>>
>> On Fri, Aug 14, 2015 at 3:31 AM, Emma Jolley <EJOLLEY at nla.gov.au <mailto:EJOLLEY at nla.gov.au>> wrote:
>> My apologies for not having sent through the information about the Log. I have to liaise for some time with our IT to get this information. They have sent me the following information which may be of help?
>>
>> Many thanks
>>
>> Emma
>> ****
>>
>> The log entry when running (as an example) the report mentioned in the email below is:
>>
>> D, [2015-08-10T17:58:14.599000 #3236] DEBUG -- : Thread-10076116: GET /repositories/2/reports/accessionsunprocessedreport?lock_version=&format=json [session: nil]
>>
>> D, [2015-08-10T17:58:14.602000 #3236] DEBUG -- : Thread-10076116: Post-processed params: {:format=>"json", :repo_id=>2}
>>
>> D, [2015-08-10T17:58:14.892000 #3236] DEBUG -- : Thread-10076116: Responded with [404, {"Content-Type"=>"application/json; charset=UTF-8", "Cache-Control"=>"private, must-revalidate, max-age=0", "Content-Length"=>"144"}, ["{\"error\":{\"AccessionsUnprocessedReport\":[\"net.sf.jasperreports.engine.JRException: Error executing SQL statement for : accessionsUnprocessed\"]}}"]]... in 292.0ms
>>
>> Rendered shared/_breadcrumb.html.erb (1.0ms)
>>
>> Rendered /opt/archivesspace-1.3.0/archivesspace/plugins/nla_accession_reports/frontend/views/reports/index.html.erb within layouts/application (82.0ms)
>>
>> Rendered shared/_browser_support.html.erb (0.0ms)
>>
>> Rendered shared/_header_user.html.erb (6.0ms)
>>
>> Rendered shared/_header_global.html.erb (6.0ms)
>>
>> Rendered site/_branding.html.erb (0.0ms)
>>
>> Rendered shared/_advanced_search.html.erb (5.0ms)
>>
>> Rendered shared/_header_repository.html.erb (18.0ms)
>>
>> Rendered site/_footer.html.erb (0.0ms)
>>
>> Rendered shared/_templates.html.erb (2.0ms)
>>
>> Completed 200 OK in 448.0ms (Views: 114.0ms)
>>
>>
>> Which is not helpful as it essentially just says exactly what gets displayed on the interface.
>>
>> net.sf.jasperreports.engine.JRException: Error executing SQL statement for : accessionsUnprocessed
>>
>> I’ve run the query in this report directly against the database successfully (cut and paste from reports/Accessions/AccessionsUnprocessedReport/AccessionsUnprocessedReport.jrxml and substituting in the repo id) so I think the SQL itself is sound.
>>
>> Further info:
>>
>> The report config being used are:
>> #AppConfig[:report_page_layout] = "letter landscape"
>>
>> AppConfig[:enable_jasper] = true
>>
>> AppConfig[:compile_jasper] = true
>> I’ve tried with the report_page_layout both commented and uncommented.
>>
>> The java version is:
>> java version "1.7.0_85"
>>
>> OpenJDK Runtime Environment (rhel-2.6.1.3.el6_7-x86_64 u85-b01)
>>
>> OpenJDK 64-Bit Server VM (build 24.85-b03, mixed mode)
>>
>>
>> Could there be an issue with using the OpenJDK rather than Oracle’s JDK perhaps ?
>>
>>
>>
>> From: archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org> [mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org>] On Behalf Of Brian Hoffman
>> Sent: Friday, 14 August 2015 12:37 PM
>> To: Archivesspace Users Group
>>
>> Subject: Re: [Archivesspace_Users_Group] Problems with ASpace reports
>>
>> Is it possible that your database was restored from a backup or that you created your database elsewhere and then imported it into MySQL? If so, you may simply be missing the stored procedures the Jasper reports rely on. There is a PR concerning this issue here:
>>
>> https://github.com/archivesspace/archivesspace/pull/244 <https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_archivesspace_archivesspace_pull_244&d=AwMGaQ&c=2X_btuPRWkGwRX26NHIotw&r=FD0Xci-wh01Dc1yt0aSp4ADMuu-zB2G2dpCCNNXfih0&m=fW2PblqG-EuWe_NmccDnQngjowe_utp-8LnFzzlbwaU&s=lnMBrdO99DMYnO5QSios7qmzuamXO_pZTlgirFph-9E&e=>
>>
>> Brian
>>
>>
>>
>> On Aug 7, 2015, at 5:49 AM, Chris Fitzpatrick <Chris.Fitzpatrick at lyrasis.org <mailto:Chris.Fitzpatrick at lyrasis.org>> wrote:
>>
>>
>> Hi Emma,
>>
>> Can you look at what's being output into the log?
>>
>> The easiest way to see what the report is doing is to look at the Jasper XML, which you can see here =>https://github.com/archivesspace/archivesspace/blob/master/reports/Accessions/AccessionsUnprocessedReport/AccessionsUnprocessedReport.jrxml#L20-L37 <https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_archivesspace_archivesspace_blob_master_reports_Accessions_AccessionsUnprocessedReport_AccessionsUnprocessedReport.jrxml-23L20-2DL37&d=AwMGaQ&c=2X_btuPRWkGwRX26NHIotw&r=FD0Xci-wh01Dc1yt0aSp4ADMuu-zB2G2dpCCNNXfih0&m=fW2PblqG-EuWe_NmccDnQngjowe_utp-8LnFzzlbwaU&s=E3g_D7ALOFUy_TOVZ77fVSG_kMXqF5u9AZkt-ff0MVc&e=>
>>
>> The basic SQL query is defined in the <query> tag, with the formatting happening below. You can edit this file to fit your needs, then restart ASpace to have it recompiled.
>>
>> b,chris.
>>
>> Chris Fitzpatrick | Developer, ArchivesSpace
>> Skype: chrisfitzpat | Phone: 918.236.6048 <tel:918.236.6048>
>> http://archivesspace.org/ <https://urldefense.proofpoint.com/v2/url?u=http-3A__archivesspace.org_&d=AwMGaQ&c=2X_btuPRWkGwRX26NHIotw&r=FD0Xci-wh01Dc1yt0aSp4ADMuu-zB2G2dpCCNNXfih0&m=fW2PblqG-EuWe_NmccDnQngjowe_utp-8LnFzzlbwaU&s=tLDoAR4LkXMLuD8XoLPRfR3Q--MISpnxaUxWqVJPZY8&e=>
>>
>> From: archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org> <archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org>> on behalf of Emma Jolley <EJOLLEY at nla.gov.au <mailto:EJOLLEY at nla.gov.au>>
>> Sent: Friday, August 7, 2015 7:13 AM
>> To: Archivesspace Users Group (archivesspace_users_group at lyralists.lyrasis.org <mailto:archivesspace_users_group at lyralists.lyrasis.org>)
>> Subject: Re: [Archivesspace_Users_Group] Problems with ASpace reports
>>
>> Hi Chris
>>
>> We have had the same issues as Robin with the reports. We have tried to implement your suggested solutions below but now received the following error message which I’ve copied below. Any advice on this?
>>
>> In addition, I have asked previously but have never received an answer – I wonder if there is any documentation on what criteria the reports use to identify records that meet the nominated criteria. For example what fields is used to indicate an unprocessed Accession and what field is used to indicate an uncatalogued accession?
>>
>> Many thanks
>>
>> Emma
>>
>> <image007.png>
>>
>>
>> From: archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org> [mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org>] On Behalf Of Chris Fitzpatrick
>> Sent: Thursday, 6 August 2015 9:16 PM
>> To: Archivesspace Users Group
>> Cc: Zalduendo, Ines; Wendler, Robin King; Della Monica, Ilaria
>> Subject: Re: [Archivesspace_Users_Group] Problems with ASpace reports
>>
>>
>>
>> Hi Robin,
>>
>> So, first you need to make sure you compiled your jasper reports on startup. To do this, edit the config.rb <https://urldefense.proofpoint.com/v2/url?u=http-3A__config.rb&d=AwQGaQ&c=2X_btuPRWkGwRX26NHIotw&r=FD0Xci-wh01Dc1yt0aSp4ADMuu-zB2G2dpCCNNXfih0&m=fW2PblqG-EuWe_NmccDnQngjowe_utp-8LnFzzlbwaU&s=gsazHb5GvapfRKK_QuagvejxEUPByer3KlDtd_8SStI&e=> file to have:
>> AppConfig[:compile_jasper] = true
>>
>> and restart ASpace.
>>
>>
>> This will compile the jasper jxml files ( which is most likely what's causing the "can't convert nil to String" errors)
>>
>> Also, you'll need to look at both your application and database server and make sure you're not running into a performance issue. Reports can hit the database really hard, so the timeout errors you're seeing could be the result of your server running out of RAM.
>>
>> In regards to the CSV and Excel and HTML files, yes I would say those are formatted rather poorly by default. You can edit this by modifing your Jasper reports using something like iReport or Jasper studio <https://urldefense.proofpoint.com/v2/url?u=http-3A__community.jaspersoft.com_project_jaspersoft-2Dstudio&d=AwMGaQ&c=2X_btuPRWkGwRX26NHIotw&r=FD0Xci-wh01Dc1yt0aSp4ADMuu-zB2G2dpCCNNXfih0&m=fW2PblqG-EuWe_NmccDnQngjowe_utp-8LnFzzlbwaU&s=1ZkU9fzjJHPAB5OIF_s_JSQTeVGtBEcG5xDJVfwWvww&e=>. Just be sure to recompile your reports.
>>
>> For the parameters, yes this feature is going to be included in a future release. I believe those labels are artifacts from AT.
>>
>> best, Chris.
>>
>>
>> Chris Fitzpatrick | Developer, ArchivesSpace
>> Skype: chrisfitzpat | Phone: 918.236.6048 <tel:918.236.6048>
>> http://archivesspace.org/ <https://urldefense.proofpoint.com/v2/url?u=http-3A__archivesspace.org_&d=AwMGaQ&c=2X_btuPRWkGwRX26NHIotw&r=FD0Xci-wh01Dc1yt0aSp4ADMuu-zB2G2dpCCNNXfih0&m=fW2PblqG-EuWe_NmccDnQngjowe_utp-8LnFzzlbwaU&s=tLDoAR4LkXMLuD8XoLPRfR3Q--MISpnxaUxWqVJPZY8&e=>
>>
>> From:archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org> <archivesspace_users_group-bounces at lyralists.lyrasis.org <mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org>> on behalf of McElheny, Robin G. <robin_mcelheny at harvard.edu <mailto:robin_mcelheny at harvard.edu>>
>> Sent: Wednesday, August 5, 2015 5:55 PM
>> To: Archivesspace Users Group
>> Cc: Zalduendo, Ines; Wendler, Robin King; Della Monica, Ilaria
>> Subject: [Archivesspace_Users_Group] Problems with ASpace reports
>>
>> At Harvard, we are having major problems with the reporting functionality in the latest ArchivesSpace release. We have tested the reports in our own installation and in the ASpace sandbox with very little success. See below for more details.
>> We didn’t see any issues in JIRA that reflects these problems. Has anyone else encountered them? If so, have you resolved them – and how?
>> Record selection issues:
>> The Accessions Acquired and Accessions Production reports supposedly allow for specified time periods, but no date parameters show up in the report user interface.
>> <image008.jpg>
>>
>> In other cases it is unclear how/where to select records for the report (e.g. Digital Object Record). No selection parameters appear in the report interface. Are we supposed to browse records, then select them using the check boxes, then generate reports?
>>
>> Report format issues:
>> In cases where we were able to download CSV and Excel reports (Accessions Cataloged, Accessions Deaccessions List, Accessions Inventory), we were unable to sort these reports because the formatting appears to be frozen. The output of the report does not match the file type. It is like getting a static document layered on top of a spreadsheet, rather than an actual spreadsheet.
>>
>> Xlsx downloads don't export uniform cell sizes, so Excel sorting returns a warning saying "this operation requires the merged cells to be identically sized"
>>
>> In order for the Xlsx downloads to be useful, each field should be in a separate column to allow tallying and sorting, but the ASpace downloads don’t do this. Some examples:
>>
>> In the case of the Accession Deaccession List Report, opening the CSV download in Excel resulted in a very messy, single column spreadsheet that broke data about one deaccesion across multiple rows. (Example: row 48= "Additional records of the League of Women Voters (Cambridge, Mass.),,,,,,1376,,," and row 49 = ",Accession Date:,,05/01/1968,Extent:,1.00 Linear_feet,,,,:").
>>
>> Here is a screen shot:
>> <image009.jpg>
>> Here’s another example:
>> <image010.jpg>
>> In the case of an HTML report, it displays page footers – odd for online display.
>> <image011.jpg>
>>
>> Report failure/timeout:
>> We encountered many failures.
>> Example:
>> When we attempted to download an XLSX AccessionsAcquired report in the ArchivesSpace sandbox, we got the following message:
>>
>> Internal Server Error (500)
>> Request Method: GET
>> Request URL: http://localhost:8089/repositories/4/reports/accessionsacquiredreport?lock_version=&format=xlsx <https://urldefense.proofpoint.com/v2/url?u=http-3A__localhost-3A8089_repositories_4_reports_accessionsacquiredreport-3Flock-5Fversion-3D-26format-3Dxlsx&d=AwMGaQ&c=2X_btuPRWkGwRX26NHIotw&r=FD0Xci-wh01Dc1yt0aSp4ADMuu-zB2G2dpCCNNXfih0&m=fW2PblqG-EuWe_NmccDnQngjowe_utp-8LnFzzlbwaU&s=QLVRITSkiwZ5mUuNKW9IXhybdcp_1VY7kG4axjN1YpI&e=>
>>
>> (TypeError) can't convert nil into String
>>
>> You're seeing this error because you use JRuby::Rack::ErrorApp::ShowStatus.
>>
>> Another example:
>> <image012.jpg>
>> We were unable to generate any successful Resource reports. All attempts to download reports in any format resulted in “Waiting for (server)” messages with no results.
>>
>>
>> Robin McElheny
>> Associate University Archivist for Collections and Public Services
>> Harvard University Archives
>> Pusey Library
>> Cambridge, MA 02138
>> Email: robin_mcelheny at harvard.edu <mailto:robin_mcelheny at harvard.edu>
>> Phone: 617-495-2461 <tel:617-495-2461>
>> Fax: 617-495-8011 <tel:617-495-8011>
>>
>> _______________________________________________
>> Archivesspace_Users_Group mailing list
>> Archivesspace_Users_Group at lyralists.lyrasis.org <mailto:Archivesspace_Users_Group at lyralists.lyrasis.org>
>> http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group <https://urldefense.proofpoint.com/v2/url?u=http-3A__lyralists.lyrasis.org_mailman_listinfo_archivesspace-5Fusers-5Fgroup&d=AwMGaQ&c=2X_btuPRWkGwRX26NHIotw&r=FD0Xci-wh01Dc1yt0aSp4ADMuu-zB2G2dpCCNNXfih0&m=fW2PblqG-EuWe_NmccDnQngjowe_utp-8LnFzzlbwaU&s=UKQoV4YFnThAz6ota9QAIQcE7acnwRZOAFv-CYNYT00&e=>
>>
>>
>> _______________________________________________
>> Archivesspace_Users_Group mailing list
>> Archivesspace_Users_Group at lyralists.lyrasis.org <mailto:Archivesspace_Users_Group at lyralists.lyrasis.org>
>> http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group <https://urldefense.proofpoint.com/v2/url?u=http-3A__lyralists.lyrasis.org_mailman_listinfo_archivesspace-5Fusers-5Fgroup&d=AwMGaQ&c=2X_btuPRWkGwRX26NHIotw&r=FD0Xci-wh01Dc1yt0aSp4ADMuu-zB2G2dpCCNNXfih0&m=fW2PblqG-EuWe_NmccDnQngjowe_utp-8LnFzzlbwaU&s=UKQoV4YFnThAz6ota9QAIQcE7acnwRZOAFv-CYNYT00&e=>
>>
>>
>>
>> --
>> Nathan Stevens
>> Programmer/Analyst
>> Digital Library Technology Services
>> New York University
>>
>> 1212-998-2653
>> ns96 at nyu.edu <mailto:ns96 at nyu.edu>_______________________________________________
>> Archivesspace_Users_Group mailing list
>> Archivesspace_Users_Group at lyralists.lyrasis.org <mailto:Archivesspace_Users_Group at lyralists.lyrasis.org>
>> http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group <https://urldefense.proofpoint.com/v2/url?u=http-3A__lyralists.lyrasis.org_mailman_listinfo_archivesspace-5Fusers-5Fgroup&d=AwMF-g&c=2X_btuPRWkGwRX26NHIotw&r=FD0Xci-wh01Dc1yt0aSp4ADMuu-zB2G2dpCCNNXfih0&m=m4k_eKATRBkkyELfLCrqHTHMjbgOiSmlN0YmAReLKiQ&s=51LSe_Vcf1uCLVXSMRiGrR4DlqfsMUZAUFSH0fTLhYE&e=>
> _______________________________________________
> Archivesspace_Users_Group mailing list
> Archivesspace_Users_Group at lyralists.lyrasis.org <mailto:Archivesspace_Users_Group at lyralists.lyrasis.org>
> http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group <http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20150901/214b8179/attachment.html>
More information about the Archivesspace_Users_Group
mailing list