Corrupted database

[Javier Escudero BUS]

Hello,

I have recently started managing https://ahus.us.es and after performing some maintenance tasks, I've realized that my database might be corrupted. When running php symfony csv:export all.csv, it exports about 3000 items and then stops with the message "Unknown record property 'authorizedFormOfName' on 'QubitStaticPage'".

I've read a bit about this error on the forums and its challenging resolution. Initially, I suspected a mix of encodings in the records, and now I'm thinking it might be due to an installation done without properly clearing the tables... I would like to investigate it more thoroughly because I'm not sure if my problem involves dozens, hundreds, or thousands of items. The website works perfectly; the issue arises with CSV and EAD exports.

I was wondering if anyone knows which tables need to be consulted to generate the csv. I know the fields are as follows:

legacyId,parentId,qubitParentSlug,identifier,accessionNumber,title,levelOfDescription,extentAndMedium,repository,archivalHistory,acquisition,scopeAndContent,appraisal,accruals,arrangement,accessConditions,reproductionConditions,language,script,languageNote,physicalCharacteristics,findingAids,locationOfOriginals,locationOfCopies,relatedUnitsOfDescription,publicationNote,digitalObjectURI,digitalObjectChecksum,generalNote,subjectAccessPoints,placeAccessPoints,nameAccessPoints,genreAccessPoints,descriptionIdentifier,institutionIdentifier,rules,descriptionStatus,levelOfDetail,revisionHistory,languageOfDescription,scriptOfDescription,sources,archivistNote,publicationStatus,physicalObjectName,physicalObjectLocation,physicalObjectType,alternativeIdentifiers,alternativeIdentifierLabels,eventDates,eventTypes,eventStartDates,eventEndDates,eventDescriptions,eventActors,eventActorHistories,eventPlaces,culture,nameAccessPoints,genreAccessPoints,descriptionIdentifier,institutionIdentifier,rules,descriptionStatus,levelOfDetail,revisionHistory,languageOfDescription,scriptOfDescription,sources,publicationStatus,digitalObjectURI,digitalObjectChecksum,generalNote,appraisal,physicalObjectName,physicalObjectLocation,physicalObjectType,alternativeIdentifiers,alternativeIdentifierLabels,eventDates,eventTypes,eventStartDates,eventEndDates,eventDescriptions,eventActors,eventActorHistories,eventPlaces,publicationNote,archivistNote,culture

And i´m looking if anybody has:

legacyId--> information_object.id

parentId--> information_object.parent_id

qubitParentSlug --> NO IDEA

identifier --> information_object_i18n.title
....

I understand that I need to follow the Entity-Relationship diagram, but I'm not sure if someone has a more direct relation (or if it is posted somewhere). Or better the sentence SQL that creates the CSV entry.

Thanks

[Dan Gillean]

Hi Javier, 

I would encourage you not to try and ignore or bypass this problem by crafting your own complex SQL queries. Generally in my experience helping AtoM users with similar issues, data corruption can have little to no effect at first... only to become an increasingly challenging problem to resolve later if it is ignored. 

Fortunately, we do have some methods available that can help to find and resolve some of the most common forms of data corruption. I would recommend that you first run a number of our common maintenance tasks (these alone can solve several data corruption issues), and then try the SQL query listed below to see if we can find any remaining problems. 

First, before we do anything else, let's make a backup in case anything goes wrong. See: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#backing-up-the-database
  

With a backup in place, I suggest you try running the following maintenance tasks: 

• Generate slugs
• Build nested set
• Clear cache
• Restart PHP-FPM

Note that the restart PHP-FPM command depends on which version of PHP you have installed, which depends on your AtoM version. You didn't mention which version of AtoM you are using, so if it's not 2.7 or 2.8, be sure to check the correct version of the documentation for your installation. 

Now, I would suggest trying the SQL query we have in our Troubleshooting documentation to see if we can find any remaining issues. See: 

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

Depending on what you find, there are instructions in that section for fixing the most common issues. Long-term, this will be easier than ignoring the corruption or trying to bypass it for an export. Let us know how it goes, and we will offer suggestions if you get stuck. Good luck!

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/d6718c58-bd70-4d90-bbea-dda67f5981d4n%40googlegroups.com.

[Guillermo Castellano Casas]

Hi there,

We are experiencing the same issue as Javier when exporting to CSV via the CLI, as shown in the screenshot below:



Our environment is set up as follows:

• Multilingual installation in Spanish and English.
• AtoM: 2.8.2 - 193.
• OS: Ubuntu 20.04.LTS.
• PHP: 7.4.3.
• MySQL: 8.0.39.

This error does not occur when exporting other fonds. Additionally, we have tried to reproduce the issue on the AtoM instance of another institution with the same setup, and we were able to export all archival descriptions to CSV without any issues. Therefore, we assume that some particularity in the database is causing the problem.

So far, we have tried the following steps:

• Generate slugs.
• Build nested sets.
• Clear cache.
• Restart PHP-FPM.
• Use SQL to check the status of the information objects and confirm if all necessary values are present,

We have also reviewed the other two existing threads on the forum regarding this issue:

• Failed attempts to export large units as csv files.
• Error when exporting large fonds as CSV.

None of the proposed solutions have fixed the problem. We continue to receive the error message 'Unknown record property "authorizedFormOfName" on "QubitStaticPage"'. Do you have any suggestions on what else we could investigate?

Thank you very much and BR,

Guillermo

[Guillermo Castellano Casas]

Allow me to clarify that the SQL query showed that there were no missing slugs, publication status IDs or objects.

[Alejo Janin]

Hi Guillermo,

There is a command line tool that checks records integrity on the DB (not sure if you already ran this): https://www.accesstomemory.org/es/docs/2.8/admin-manual/maintenance/cli-tools/#find-data-integrity-issues

Cheers,

Alejo

To view this discussion visit https://groups.google.com/d/msgid/ica-atom-users/8cc31fc2-c8fb-41c3-8c8b-485d31a2b33bn%40googlegroups.com.

[Guillermo Castellano Casas]

Hi Alejo,

Yeah, we ran that CLI tool too and the report didn't list any errors:

cd /usr/share/nginx/atom/ && sudo php symfony tools:data-integrity-repair /home/<user>/results-data-integrity-check.csv
>> data-integrity-repair Adding missing object rows (except for descriptions):

>> data-integrity-repair   - QubitRepository: 0

>> data-integrity-repair   - QubitRightsHolder: 0

>> data-integrity-repair   - QubitUser: 0

>> data-integrity-repair   - QubitDonor: 0

>> data-integrity-repair   - QubitActor: 0

>> data-integrity-repair   - QubitAip: 0

>> data-integrity-repair   - QubitJob: 0

>> data-integrity-repair   - QubitDigitalObject: 0

>> data-integrity-repair   - QubitEvent: 0

>> data-integrity-repair   - QubitFunctionObject: 0

>> data-integrity-repair   - QubitObjectTermRelation: 0

>> data-integrity-repair   - QubitPhysicalObject: 0

>> data-integrity-repair   - QubitPremisObject: 0

>> data-integrity-repair   - QubitRelation: 0

>> data-integrity-repair   - QubitRights: 0

>> data-integrity-repair   - QubitRightsHolder: 0

>> data-integrity-repair   - QubitStaticPage: 0

>> data-integrity-repair   - QubitTaxonomy: 0

>> data-integrity-repair   - QubitTerm: 0

>> data-integrity-repair   - QubitAccession: 0

>> data-integrity-repair   - QubitDeaccession: 0

>> data-integrity-repair Regenerating slugs ...

>> propel    Generate accession slugs...
>> propel    Generate actor slugs...
>> propel    Generate deaccession slugs...
>> propel    Generate digital_object slugs...
>> propel    Generate event slugs...
>> propel    Generate function_object slugs...
>> propel    Generate information_object slugs...
>> propel    Generate physical_object slugs...
>> propel    Generate relation slugs...
>> propel    Generate rights slugs...
>> propel    Generate static_page slugs...
>> propel    Generate taxonomy slugs...
>> propel    Generate term slugs...
>> propel    Note: you will need to rebuild your search index for slug changes to show up in search results.
>> propel    Done!
>> data-integrity-repair Updating terms without parent id: 0

>> data-integrity-repair Checking descriptions integrity:

>> data-integrity-repair   - Descriptions without object row: 0

>> data-integrity-repair   - Descriptions without parent id: 0

>> data-integrity-repair   - Descriptions without parent: 0

>> data-integrity-repair   - Descriptions without publication status: 0

>> data-integrity-repair   - Affected descriptions: 0

>> data-integrity-repair All descriptions seem to be okay.

>> data-integrity-repair Rebuilding nested set ...

>> propel    Build nested set for information_object...
>> propel    Build nested set for term...
>> propel    Build nested set for menu...
>> propel    Note: you will need to rebuild your search index for updates to show up properly in search results.
>> propel    Done!
>> data-integrity-repair The ES index has not been updated! Run the search:populate task to do so.

Thanks and BR,

Guillermo

[Alejo Janin]

Hmm.. ok.

In your situation, i guess i would try exporting the sections or series in that fond. Just to narrow the search and be able to identify the corrupted record.

You should use the --rows-per-file option:

php symfony csv:export --rows-per-file="1000" /path/to/my/export-directory/

Cheers,

Alejo

To view this discussion visit https://groups.google.com/d/msgid/ica-atom-users/6e81ca53-ea14-49a3-85e1-78ec02a2c865n%40googlegroups.com.

[Guillermo Castellano Casas]

Hi Alejo,

Following the proposed procedure, we identified the archival description that was causing the export error. The issue was due to a subject access point that linked to AtoM's home page, which seems consistent with the message: "Unknown record property 'authorizedFormOfName' on 'QubitStaticPage'". This record also couldn’t be exported to EAD 2002 XML.

Once we removed that access point, the description could be exported to XML, and we were also able to export the entire collection it belongs to as a CSV file.

We’re not sure how this error was introduced into the database, but we’re documenting the issue and the steps taken to resolve it in case someone else encounters the same situation.

Thank you and BR,

Guillermo