500 error on attempting to delete record

[Carolyn Sullivan]

Hello,

We're running AtoM 2.6, and we're attempting to delete this accession record:

https://biblio.uottawa.ca/atom/index.php/acc-2011-04ca-2

On attempting to do so, it gives us a 500 error.  I noticed it has several archival descriptions that also give errors on access linked to it.

I checked the logs after trying to delete it.  They say:

Since the documentation recommend regenerating slugs when we expect data corruption, and since some of the files under this accession record seem corrupt, I tried:

sudo php symfony propel:generate-slugs

sudo php symfony propel:build-nested-set

sudo php symfony search:populate

Search populate ran, but gave me the error:

I'm not sure if this is related to the accession record in question or not.

I then ran:

php symfony cc

sudo systemctl restart memcached

sudo systemctl restart php7.2-fpm

After that, I tried to delete the record in an incognito browser.  It gave me a 500 error again.  

Any suggestions on what to do next?

Thank you so much for your help,

Carolyn.

[Dan Gillean]

Hi Carolyn, 

It's great to see all the effort you have gone to so far to try troubleshooting this yourself, and to explain in your post what you've already tried - thank you! This makes responding much easier. 

It sounds like it's possible that you have some data corruption, possibly resulting from a long-running operation (such as an import, etc) that might have timed out mid-process?

In any case, my suggestion on next step would be to first make a backup of your database, and then try running the SQL query we have provided that looks for common forms of data corruption in information objects: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/troubleshooting/#dealing-with-data-corruption
  

Specifically, it sounds like you might have some partial information object rows that don't have an object row (and therefore no object ID). The query at the link above should help to identify if this is the case - and if so, there is another INSERT query below that will show you how you can attempt to repair this. 

Good luck, and let us know what you find! 

Cheers, 

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

he / him

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/3d7eccbe-3f01-4d1c-bbb9-9d317ad3d15en%40googlegroups.com.

[Lrellis D'erth]

Hello Dan,

Thank you so much for your response.  I ran the SQL query to display the information object id, object id, slug, and publication status.  Since it gave me 55455 rows, I then tried filtering it for NULL entries in each by successively running this (substitute data.slug with data.io_id, data.obj_id, data.pub_status_id):

SELECT * FROM (SELECT io.id AS io_id, obj.id AS obj_id, st.status_id AS p
ub_status_id, slug.slug FROM information_object io LEFT JOIN object obj ON io.id
=obj.id LEFT JOIN status st ON io.id=st.object_id AND st.type_id=158 LEFT JOIN s
lug ON slug.object_id=io.id) AS data WHERE data.slug IS NULL;

(side question... what does type_id=158 mean?)

The only one that returned a null entry was the pub_status_id, which gave me information object #207351 that created the error in repopulating the search index (mentioned in my initial message!).  I checked it out and it was giving a 500 value when I tried to access that page, so I did INSERT INTO status (object_id, type_id, status_id, id, serial_number) VALUES ('207351', '158', '159', NULL,'0'), which solved the issue with that page... but didn't fix the problem with deleting the accession record :/  I then checked for duplicate publication status as recommended in this section .  No duplicate publication statuses.

One of the weird thing about this accession record is that it has so many archival descriptions listed under it that give 'Sorry, page not found' errors, including the information_object 310847 of the first image in my initial message, which I found out when I tried doing SELECT * FROM slug WHERE slug='conference-illusions-and-realities-in-the-nuclear-age-1-of-2' and it showed me that number as the object_id.  Oddly, running SELECT * FROM information_object WHERE id='310847' gives an empty set.  Likewise, running SELECT * FROM slug WHERE object_id NOT IN (SELECT id FROM information_object); gives me a whole 87461 rows...  If I'm doing this correctly (and I'm not a professional programmer, so that's uncertain) it's like there's a ton of information objects with slugs, but that aren't listed in the information_object table.  Thoughts?

We'll be updating to version 2.7.1 in about two weeks.  I'm not sure if that will solve all our data issues though, so I'm eager to hear all your suggestions ;)

Best,

Carolyn.

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CAC1FhZKUqHYxbh0YV1iU%2BmMEJbh9ng-s%2BYbtr%2B8Ut_KRbhh2ag%40mail.gmail.com.

[Dan Gillean]

Hi Carolyn, 

Sorry for the long delay. This is getting a bit more complex than my simple archivist skills can quite parse. I have been trying to get the developer who originally crafted our data corruption SQL queries to take a look at this thread and see if he could offer some additional input on possible next steps - but he's unfortunately been out sick this week so far. I hope we can get back to you with some additional suggestions soon!

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

he / him

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CA%2BpcS1TxYxmOJEJAJ4%3DZAy-fj5QAz3NQzxhNYD3K7P4Mbt3E9w%40mail.gmail.com.

[José Raddaoui]

Hi Carolyn,

Great detective work! Some comments and ideas:

(side question... what does type_id=158 mean?)

There are a few status types, 158 represents the publication status. 

One of the weird thing about this accession record is that it has so many archival descriptions listed under it that give 'Sorry, page not found' errors, including the information_object 310847 of the first image in my initial message, which I found out when I tried doing SELECT * FROM slug WHERE slug='conference-illusions-and-realities-in-the-nuclear-age-1-of-2' and it showed me that number as the object_id.  Oddly, running SELECT * FROM information_object WHERE id='310847' gives an empty set.  Likewise, running SELECT * FROM slug WHERE object_id NOT IN (SELECT id FROM information_object); gives me a whole 87461 rows...  If I'm doing this correctly (and I'm not a professional programmer, so that's uncertain) it's like there's a ton of information objects with slugs, but that aren't listed in the information_object table.  Thoughts?

Those 87461 rows from the slug table are most likely slugs for other entity types (authority records, terms, etc). If SELECT * FROM information_object WHERE id='310847'; gives you an empty set, try with SELECT * FROM object WHERE id='310847';. If that one gives you a record, that may be the cause of your issue, try deleting it from the object table with DELETE FROM object WHERE id='310847';. Most of the deletions done through the database directly need to be performed from the object table, that way they cascade properly and delete all relations from other tables.

Best regards,

Radda.

[Carolyn Sullivan]

Thank you all so much for your help, and apologies for the tardy response, I was ill for awhile there.  So if I look up the accession record I was trying to delete... the one with all the odd archival descriptions that also didn't have entries in the information_object table... I see this:

Does this mean that I can simply back up my database , do

DELETE * FROM object WHERE id='310556';

and it will delete both this accession record and all the archival descriptions attached to it, including the ones that don't have entries in the information_object table?
(Side note: this is what the faulty archival descriptions look like from the browser; they lead to 'Sorry page not found')

Thanks,

Carolyn.

[José Raddaoui]

Hi Carolyn,

Deleting the accession record will delete all the relations but it won't remove the related descriptions, you'll need to delete those independently.

To find all the descriptions without information_object row in the DB, you could use the following query:

SELECT * FROM object o
LEFT JOIN information_object i
ON o.id=i.id
WHERE o.class_name='QubitInformationObject'

AND i.id IS NULL; 

And, a similar one if you really want to delete them:

DELETE o FROM object o
LEFT JOIN information_object i
ON o.id=i.id
WHERE o.class_name='QubitInformationObject'
AND i.id IS NULL;

Make sure you rebuild the nested set and reindex after that.

Best,

Radda.

[Lrellis D'erth]

Thanks, José!

--

You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/fed7aff8-b3d1-4cde-be34-3ae8d9e1226fn%40googlegroups.com.