Re: [Dspace-tech] How to clear all monthly reports and start from the beginning
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
Re: [Dspace-tech] How to clear all monthly reports and start from the beginning
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 DSpace-tech@lists.sourceforge.net 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 DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
Re: [Dspace-tech] How to clear all monthly reports and start from the beginning
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 DSpace-tech@lists.sourceforge.net 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 DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security
