Re: [Dspace-tech] How to find item with strange metadata value of |||
Bingo, I found the item, and then got its handle using the item_id: dspace=# SELECT * FROM item, handle WHERE handle.resource_id = item.item_id AND item.item_id = '66841'; The item did indeed have empty contents for that field. I deleted it from the Edit this item function in the web interface and now my facets are clean again. ;) Thanks a bunch, Alan On Sun, Aug 2, 2015 at 5:24 AM Kim Shepherd kim.sheph...@gmail.com wrote: Hi Alan, that's close, but not quite what I was suggesting: the ||| will not be stored in the database, that's added in when indexing the values to Solr, so I think a SQL query like: select item_id from metadatavalue where metadata_field_id=86 and (text_value='' OR text_value IS NULL); Will be closer to what you're after. If you want to try and find items that might match in solr, you could try something like curl http://localhost:8080/solr/search/select?query=*:*fq=FIELDNAME_filter :||| Where FIELDNAME is the name of the field as configured in the discovery spring configuration (eg. 'subject', 'type', 'author', and so on) Cheers! Kim M: k...@shepherd.nz T: @kimshepherd P: +6421883635 0CCB D957 0C35 F5C1 497E CDCF FC4B ABA3 2A1A FAEC https://keybase.io/kshepherd On 1 August 2015 at 01:28, Alan Orth alan.o...@gmail.com wrote: So I found the metadata_field_id for this metadata item using a known valid value: dspace=# select * from metadatavalue where text_value='DRYLAND CEREALS'; ... and then I tried to find text values containing several variations of |||, like '\|\|\|', '%|%', '%\|%' etc... dspace=# select item_id from metadatavalue where metadata_field_id=86 and text_value='|||'; But I am not getting any hits. I also figured that, since we figured CSV export by nature wouldn't expose this anomaly, that I could simple export and re-import, but DSpace said no changes were detected upon re-import. Hmmm. Anyone know how I could query Solr directly for this information? Alan On Fri, Jul 31, 2015 at 9:35 AM Alan Orth alan.o...@gmail.com wrote: Thanks for the interesting suggesting, Kim! This is happening in DSpace 4.3. I will try to poke around in the database, as you're probably right that this anomaly probably wouldn't make it through a CSV export... Alan On Thu, Jul 30, 2015 at 1:44 PM Kim Shepherd kim.sheph...@gmail.com wrote: Hi Alan, Discovery filters use ||| as a separator between lowercase value and a display/stored value, eg. something|||SomeThing I'm sort of surprised it displays in the sidebar, because acting as the separator, it should be pulled out, but I think the first thing I'd look for is empty or null metadata values in your database (probably easier than CSV because of how metadata export works), for the field in question. What version is this happening in? M: k...@shepherd.nz T: @kimshepherd P: +6421883635 0CCB D957 0C35 F5C1 497E CDCF FC4B ABA3 2A1A FAEC https://keybase.io/kshepherd On 30 July 2015 at 20:45, Alan Orth alan.o...@gmail.com wrote: Hi, I've got an strange metadata value shown in one of our XMLUI Discovery sidebar facets, and I'm not sure how to find the offending item to fix it. The value is displaying as |||, and I've narrowed down the collection the item belongs to by navigating through my communities and looking at the facet in question. I've exported the metadata as CSV and attempted to find the item using LibreOffice and OpenRefine but nothing strange sticks out... Does anyone have any suggestions? Thanks, Alan -- ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette -- ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] How to find item with strange metadata value of |||
Hi Alan, that's close, but not quite what I was suggesting: the ||| will not be stored in the database, that's added in when indexing the values to Solr, so I think a SQL query like: select item_id from metadatavalue where metadata_field_id=86 and (text_value='' OR text_value IS NULL); Will be closer to what you're after. If you want to try and find items that might match in solr, you could try something like curl http://localhost:8080/solr/search/select?query=*:*fq=FIELDNAME_filter :||| Where FIELDNAME is the name of the field as configured in the discovery spring configuration (eg. 'subject', 'type', 'author', and so on) Cheers! Kim M: k...@shepherd.nz T: @kimshepherd P: +6421883635 0CCB D957 0C35 F5C1 497E CDCF FC4B ABA3 2A1A FAEC https://keybase.io/kshepherd On 1 August 2015 at 01:28, Alan Orth alan.o...@gmail.com wrote: So I found the metadata_field_id for this metadata item using a known valid value: dspace=# select * from metadatavalue where text_value='DRYLAND CEREALS'; ... and then I tried to find text values containing several variations of |||, like '\|\|\|', '%|%', '%\|%' etc... dspace=# select item_id from metadatavalue where metadata_field_id=86 and text_value='|||'; But I am not getting any hits. I also figured that, since we figured CSV export by nature wouldn't expose this anomaly, that I could simple export and re-import, but DSpace said no changes were detected upon re-import. Hmmm. Anyone know how I could query Solr directly for this information? Alan On Fri, Jul 31, 2015 at 9:35 AM Alan Orth alan.o...@gmail.com wrote: Thanks for the interesting suggesting, Kim! This is happening in DSpace 4.3. I will try to poke around in the database, as you're probably right that this anomaly probably wouldn't make it through a CSV export... Alan On Thu, Jul 30, 2015 at 1:44 PM Kim Shepherd kim.sheph...@gmail.com wrote: Hi Alan, Discovery filters use ||| as a separator between lowercase value and a display/stored value, eg. something|||SomeThing I'm sort of surprised it displays in the sidebar, because acting as the separator, it should be pulled out, but I think the first thing I'd look for is empty or null metadata values in your database (probably easier than CSV because of how metadata export works), for the field in question. What version is this happening in? M: k...@shepherd.nz T: @kimshepherd P: +6421883635 0CCB D957 0C35 F5C1 497E CDCF FC4B ABA3 2A1A FAEC https://keybase.io/kshepherd On 30 July 2015 at 20:45, Alan Orth alan.o...@gmail.com wrote: Hi, I've got an strange metadata value shown in one of our XMLUI Discovery sidebar facets, and I'm not sure how to find the offending item to fix it. The value is displaying as |||, and I've narrowed down the collection the item belongs to by navigating through my communities and looking at the facet in question. I've exported the metadata as CSV and attempted to find the item using LibreOffice and OpenRefine but nothing strange sticks out... Does anyone have any suggestions? Thanks, Alan -- ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette -- ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] How to find item with strange metadata value of |||
So I found the metadata_field_id for this metadata item using a known valid value: dspace=# select * from metadatavalue where text_value='DRYLAND CEREALS'; ... and then I tried to find text values containing several variations of |||, like '\|\|\|', '%|%', '%\|%' etc... dspace=# select item_id from metadatavalue where metadata_field_id=86 and text_value='|||'; But I am not getting any hits. I also figured that, since we figured CSV export by nature wouldn't expose this anomaly, that I could simple export and re-import, but DSpace said no changes were detected upon re-import. Hmmm. Anyone know how I could query Solr directly for this information? Alan On Fri, Jul 31, 2015 at 9:35 AM Alan Orth alan.o...@gmail.com wrote: Thanks for the interesting suggesting, Kim! This is happening in DSpace 4.3. I will try to poke around in the database, as you're probably right that this anomaly probably wouldn't make it through a CSV export... Alan On Thu, Jul 30, 2015 at 1:44 PM Kim Shepherd kim.sheph...@gmail.com wrote: Hi Alan, Discovery filters use ||| as a separator between lowercase value and a display/stored value, eg. something|||SomeThing I'm sort of surprised it displays in the sidebar, because acting as the separator, it should be pulled out, but I think the first thing I'd look for is empty or null metadata values in your database (probably easier than CSV because of how metadata export works), for the field in question. What version is this happening in? M: k...@shepherd.nz T: @kimshepherd P: +6421883635 0CCB D957 0C35 F5C1 497E CDCF FC4B ABA3 2A1A FAEC https://keybase.io/kshepherd On 30 July 2015 at 20:45, Alan Orth alan.o...@gmail.com wrote: Hi, I've got an strange metadata value shown in one of our XMLUI Discovery sidebar facets, and I'm not sure how to find the offending item to fix it. The value is displaying as |||, and I've narrowed down the collection the item belongs to by navigating through my communities and looking at the facet in question. I've exported the metadata as CSV and attempted to find the item using LibreOffice and OpenRefine but nothing strange sticks out... Does anyone have any suggestions? Thanks, Alan -- ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette -- ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] How to find item with strange metadata value of |||
Thanks for the interesting suggesting, Kim! This is happening in DSpace 4.3. I will try to poke around in the database, as you're probably right that this anomaly probably wouldn't make it through a CSV export... Alan On Thu, Jul 30, 2015 at 1:44 PM Kim Shepherd kim.sheph...@gmail.com wrote: Hi Alan, Discovery filters use ||| as a separator between lowercase value and a display/stored value, eg. something|||SomeThing I'm sort of surprised it displays in the sidebar, because acting as the separator, it should be pulled out, but I think the first thing I'd look for is empty or null metadata values in your database (probably easier than CSV because of how metadata export works), for the field in question. What version is this happening in? M: k...@shepherd.nz T: @kimshepherd P: +6421883635 0CCB D957 0C35 F5C1 497E CDCF FC4B ABA3 2A1A FAEC https://keybase.io/kshepherd On 30 July 2015 at 20:45, Alan Orth alan.o...@gmail.com wrote: Hi, I've got an strange metadata value shown in one of our XMLUI Discovery sidebar facets, and I'm not sure how to find the offending item to fix it. The value is displaying as |||, and I've narrowed down the collection the item belongs to by navigating through my communities and looking at the facet in question. I've exported the metadata as CSV and attempted to find the item using LibreOffice and OpenRefine but nothing strange sticks out... Does anyone have any suggestions? Thanks, Alan -- ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette -- ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette