Hello Stuart, Thank you for your reply. I followed you suggestion. There was indeed a "rogue" SQL query, the one below.
SELECT COUNT(*) AS num FROM item WHERE in_archive = true AND withdrawn = false AND item_id IN ( SELECT item_id FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned') AND text_value::timestamp > '2011-11-01'::timestamp AND text_value::timestamp < '2011-11-30'::timestamp ) AND item_id IN ( SELECT item_id FROM metadatavalue WHERE text_value LIKE '%Thesis or Dissertation%' AND metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'type' AND qualifier IS NULL) ) The last subquery, SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'type' AND qualifier IS NULL, return two results instead of one. I don't think this error was caused by dspace itself ( running v 1.7.0) but due to a plug-in installed for Europeana, a few months back. The plug-in adds another namespace for Europeana. The two (hint!) rows returned are the following: - element = type, qualifier = null, scope_note = Nature or genre of content - element = type, qualifier = null, scope_note = The element's type (TEXT, IMAGE, SOUND, VIDEO) This query does not take into account the metadata_schema_id column, which is the only difference between the two rows above. The first one has a metadata_schema_id =1, and the second one equals 2. The europeana schema has another "type" field. I changed the qualifier for that to 1 (just so it is not NULL), to test it out, and all scripts related to statistics worked like a charm. I will do some tests to see if it plays well with europeana during future weeks and see how it goes. Thanks again, Evelthon On 11/28/2011 07:39 PM, Stuart Lewis wrote: > Hi Evelthon, > > If you enable DEBUG logging in DSpace, and then re-run the command, the > dspace.log file should show the SQL that is failing. If you could show us > that, it might help to work out where the error is. My guess is a bad value > in the dspace statistics config file, but the SQL should help. > > - > https://wiki.duraspace.org/display/DSPACE/TechnicalFaq#TechnicalFaq-SettingloggingleveluptoDEBUG > > Thanks, > > > Stuart Lewis > Digital Development Manager > Te Tumu Herenga The University of Auckland Library > Auckland Mail Centre, Private Bag 92019, Auckland 1142, New Zealand > Ph: +64 (0)9 373 7599 x81928 > > > > On 29/11/2011, at 1:42 AM, Evelthon Prodromou wrote: > >> Hello again, >> >> I am asking once more in case someone missed my last email. It will be >> really helpful if anyone can point me to the right direction. >> >> I did the following since my last email. I edited dstat.cfg and changed >> start.year to 2001 and start.month to 11. I then cleared /[dspace]/reports >> and executed dspace stat-initial. Still the same problem. >> >> >> Many thanks, >> >> >> Evelthon >> >> On 11/19/2011 11:58 PM, Evelthon Prodromou wrote: >>> Hello everyone, >>> >>> I am facing an issue with dspace 17.0 and monthly reports. We are moving >>> collections from greenstone to dspace. The monthly reports worked fine >>> up to 02/2011. Seems like whenever a stat-* command is run it returns >>> the error below. >>> ----- >>> Exception: ERROR: more than one row returned by a subquery used as an >>> expression >>> org.postgresql.util.PSQLException: ERROR: more than one row returned by >>> a subquery used as an expression >>> at >>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1531) >>> at >>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1313) >>> at >>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188) >>> at >>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452) >>> at >>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354) >>> at >>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:258) >>> at >>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) >>> at >>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) >>> at >>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) >>> at >>> org.dspace.storage.rdbms.DatabaseManager.query(DatabaseManager.java:284) >>> at >>> org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.java:330) >>> at >>> org.dspace.app.statistics.LogAnalyser.getNumItems(LogAnalyser.java:1249) >>> at >>> org.dspace.app.statistics.LogAnalyser.processLogs(LogAnalyser.java:498) >>> at >>> org.dspace.app.statistics.CreateStatReport.statInitial(CreateStatReport.java:259) >>> at >>> org.dspace.app.statistics.CreateStatReport.main(CreateStatReport.java:129) >>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >>> at >>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) >>> at >>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) >>> at java.lang.reflect.Method.invoke(Method.java:597) >>> at >>> org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:183) >>> >>> ----- >>> Since we have not yet gone public on dspace, it is of no problem to >>> just clear everything out and start our monthly reports from scratch. >>> Any pointers on how can that be accomplished? >>> >>> Thanks >>> >>> >>> >>> ------------------------------------------------------------------------------ >>> All the data continuously generated in your IT infrastructure >>> contains a definitive record of customers, application performance, >>> security threats, fraudulent activity, and more. Splunk takes this >>> data and makes sense of it. IT sense. And common sense. >>> >>> http://p.sf.net/sfu/splunk-novd2d >>> >>> _______________________________________________ >>> DSpace-tech mailing list >>> >>> [email protected] >>> https://lists.sourceforge.net/lists/listinfo/dspace-tech >> <prodromou_evelthon.vcf>------------------------------------------------------------------------------ >> All the data continuously generated in your IT infrastructure >> contains a definitive record of customers, application performance, >> security threats, fraudulent activity, and more. Splunk takes this >> data and makes sense of it. IT sense. And common sense. >> http://p.sf.net/sfu/splunk-novd2d_______________________________________________ >> DSpace-tech mailing list >> [email protected] >> https://lists.sourceforge.net/lists/listinfo/dspace-tech > >
<<attachment: prodromou_evelthon.vcf>>
------------------------------------------------------------------------------ All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d
_______________________________________________ DSpace-tech mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-tech
