Integrity constraint violation while importing SKOS

[Josef Vítovec]

Hi,

I've encoutered a problem while importing SKOS. It doesn't matter which taxonomy I try to import into it still gives me the same error:

[info] [2021-03-16 17:56:42] Job 41564 "arFileImportJob": Job started.
[info] [2021-03-16 17:56:42] Job 41564 "arFileImportJob": Importing SKOS file: technology.xml.
[info] [2021-03-16 17:56:42] Job 41564 "arFileImportJob": Type of scheme: file
[info] [2021-03-16 17:56:42] Job 41564 "arFileImportJob": Taxonomy: Places
[info] [2021-03-16 17:56:42] Job 41564 "arFileImportJob": Term ID: 1
[info] [2021-03-16 17:56:42] Job 41564 "arFileImportJob": The graph contains 117 concepts.
[info] [2021-03-16 17:56:42] Job 41564 "arFileImportJob": Exception: Unable to execute INSERT statement. [wrapped: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (atom.note, CONSTRAINT note_FK_2 FOREIGN KEY (type_id) REFERENCES term (id) ON DELETE SET NULL)]

It all started when I try to empty certain taxonomies. I was searching through this group and run into this thread and saw the recommendation to run this query:

DELETE FROM object WHERE id IN (SELECT id FROM term WHERE taxonomy_id = 42);

So I did it and all terms in the taxonomy were removed as intended. However, from that time I'm getting this error and it's not possible to import a single SKOS.

I was trying to fix it with the use of this thread but without success. I also tried to run all the commands like:

php symfony propel:build-nested-set 

php symfony propel:generate-slugs
php symfony search:populate 

It's still possible to create terms from GUI though. Maybe the "Term ID: 1" in the job report looks a bit suspicious but I still don't know how to fix it.

Any idea what could've gone wrong?

Thanks,

Josef

[Dan Gillean]

Hi Josef, 

There's a lot going on here. First, can you tell us more about your AtoM installation? Such as: 

• Full version number (as listed in Admin > Settings > Global)
• Operating system and version (e.g. Linux Ubuntu 18.04)
• PHP version
• MySQL version
• Any other changes you have made from the recommended installation instructions for your AtoM version. 

Often when I see integrity constraint violation errors, it's because someone is not using the correct version of MySQL, and/or they are not using the recommended SQL modes. When STRICT_TRANS_TABLES sqlmode is enabled, we often see these types of errors in AtoM, for example - and it is enabled by default in MySQL 5.7 and later, so if you missed the part of the installation documentation where we create a new file to enter our sqlmodes, this could happen. Fortunately these can be checked and changed at a later date. 

First, if you are running AtoM 2.6.0 or later, AtoM expects MySQL 8.0 - meaning that trying to upgrade in place from an earlier version is likely not possible. See: 

• https://www.accesstomemory.org/docs/latest/admin-manual/installation/linux/ubuntu-bionic/#mysql
  

You can also see where we set the SQL modes later in that section. If you would like to double-check and/or modify those in your installation, see: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#check-server-sql-modes
  

Doing so requires access to the MySQL command prompt. Instructions on how to access this are here: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#accessing-the-mysql-command-prompt
  

As for the term ID 1 thing - I'll have to check with our developers, but yes, this might be a sign of a problem. Typically AtoM has a root information object, to which all other descriptions are attached, and I believe this root object normally has the ID of 1. 

One other thing you could do in the meantime is check for data corruption. We have a query that can help identify the most common forms: 

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

Check your SQLmodes, please provide more information on your installation, and let me know if anything comes up when checking for data corruption. Together all this information should help our team provide better suggestions. 

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/6290d23c-bb66-4645-b1ce-5c3e8b384f0cn%40googlegroups.com.

[Josef Vítovec]

Hi Dan,

thank you for quick reply. We're currently running version 2.6.0 - 184 plus I applied this patch https://projects.artefactual.com/issues/13470.

Our installation setup includes:

• CentOS 7
• MySQL 8.0.21
• Elasticsearch 5.6.9
• PHP 7.2 from the remi repository (we had to use the remi's version because of CentOS 7 – https://groups.google.com/g/ica-atom-users/c/4_hZZzCminQ)

We followed the official installation guide and haven't made any other changes. When running SELECT @@sql_mode, @@GLOBAL.SQL_MODE; output looks like this:

+---------------------------------------------------+---------------------------------------------------+

| @@sql_mode                                        | @@GLOBAL.SQL_MODE                                 |

+---------------------------------------------------+---------------------------------------------------+

| ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |

+---------------------------------------------------+---------------------------------------------------+

1 row in set (0.00 sec)

So I think MySQL is configured as it should be. 

Regarding the data corruption I tried to execute various SQL SELECTS listed in the docs and nothing extraordinary appeared. However there are not many queries validating the Term entity so that could be the reason. I'm pretty sure our errors started happening because of the SQL DELETE I mentioned above. Before the execution of the DELETE we imported few SKOSes and they finished without any errors.

Thanks for your help.

Regards,

Josef

Dne čtvrtek 18. března 2021 v 21:41:53 UTC+1 uživatel Dan Gillean napsal:

[Dan Gillean]

Hi Josef, 

Thanks for the information. I've talked this over with a developer and it may be beyond what we can help troubleshoot via the forum, but let's see how far we can get. 

His theory is that you were correct in finding the error similar to this thread you linked to in your first post - but not identical, which is why the solution there may not have worked. 

The error seems to be about trying to create an entry in the note table in AtoM, but the note type_id not being set. It could therefore mean that one or more of the root note type terms is missing data, or has been deleted. We're going to check for the Scope Note and Source Note term types, as well as the root term ID wit the following two queries - please share the output: 

• SELECT * FROM term WHERE id IN (110, 121, 122);
  
• SELECT * FROM slug WHERE object_id IN (110, 121, 122);
  

Hopefully this theory might pan out, and we can provide further suggestions based on what you find. 

Cheers, 

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/c6b05fcd-9d29-47d3-befb-e35c5cc9aa59n%40googlegroups.com.

[Josef Vítovec]

Hi Dan,

thanks for the explanation. I tried both of the queries you sent and these are the outputs.

mysql> SELECT * FROM term WHERE id IN (110, 121, 122);

+-----+-------------+------+-----------+------+------+----------------+

| id  | taxonomy_id | code | parent_id | lft  | rgt  | source_culture |

+-----+-------------+------+-----------+------+------+----------------+

| 110 |          30 | NULL |      NULL |    1 |  578 | en             |

+-----+-------------+------+-----------+------+------+----------------+

1 row in set (0.00 sec)

mysql> SELECT * FROM slug WHERE object_id IN (110, 121, 122);

+-----------+----------------+----+---------------+

| object_id | slug           | id | serial_number |

+-----------+----------------+----+---------------+

|       110 | 5qwe-6m9t-h5mg | 56 |             0 |

+-----------+----------------+----+---------------+

1 row in set (0.00 sec)

I hope it's not anything serious.

Regards,

Josef

Dne pondělí 22. března 2021 v 18:08:50 UTC+1 uživatel Dan Gillean napsal:

[Dan Gillean]

Hi Josef, 

So, it appears that somehow some of the default note type terms have been deleted in your database - specifically, the terms for scopeNote and sourceNote, which is why you're getting the errors. 

If you ran the deletion query you found in the other thread, I don't think it would have had this effect... unless you ran it against a different ID than 42 (for example against ID 37)? In any case, we always recommend that users proceed at their own risk when running SQL queries to resolve issues, and strongly recommend that backups are made in advance just in case! I hope you've done this!

Honestly, if you made a backup before you ran the SQL queries that lead to this outcome, the easiest solution would be to load that back now. You might lose a bit of work but you will save a lot of headaches. 

If that's not an option, I've asked one of our developers to try and devise a couple queries to add back these missing terms. Please keep in mind that if other note type terms are missing, you may still run into issues, and resolving every single one of these manually will be beyond the level of support we can provide via the forum. 

We'll get back to you when we have some queries that can recreate these two missing terms - hopefully that's all it takes! If you intend to load a previous backup, let us know. Otherwise, hang tight and hopefully this is the only issue. 

Regards, 

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/56404160-48c3-4baf-a50a-7db67bb7bb2fn%40googlegroups.com.

[Josef Vítovec]

Hi Dan,

Yeah it makes sense actually. I didn't know that some taxonomies are more important than others and shouldn't be deleted so I also deleted the 'Note types' one (ID 37). I made a backup before running the DELETE command. Unfortunately after the command finished and everything seemed to work I made another backup which overwrote the previous one. So now I don't have a backup before the DELETE command anymore.

So basically all my note type terms are missing. Is there any solution to this, like importing all of them again via some INSERT command? If it's too complicated your recommendation is to reinstall AtoM from scratch?

Thanks.

Regards,

Josef

Dne středa 24. března 2021 v 14:47:52 UTC+1 uživatel Dan Gillean napsal:

[José Raddaoui]

Hi Josef,

There are 13 note type terms in a default AtoM installation (2.6.x):

- Accession event note

- Accession event note

- Actor occupation note

- Archivist's note

- Conservation note

- Display note

- General note

- Language note

- Maintenance note

- Other Descriptive Data

- Publication note

- Scope note

- Source note

- Title note

With all of them deleted you will see similar issues in other parts of the application, so we'd need to restore them all. Moreover, the existing notes in the database won't have a type now and restoring that programmatically could be complicated. Knowing that, how bad would it be to start from scratch? You wouldn't need to do a full reinstall, we have a CLI task to purge the database going back to its initial status; from the AtoM folder ...

php symfony tools:purge

If that's not an option for you I could create a PHP script to recreate those note type terms, but you'll need to go over the existing notes manually to set their type after.

Best regards,

Radda.

[Josef Vítovec]

Hi Radda,

thanks for the reply. The CLI task to purge the database sounds of course better than full reinstall however it'd be still a bit painful to start from scratch. I don't know how much time does it take to create the PHP script you mentioned. Maybe could you please do that if you think it's not anything complex and you're able to finish it easily?

Regarding the existing notes, you mean setting their type manually via SQL update statements (I can do that) or it's possible via GUI? Also, does AtoM come with any predefined notes? Because there are about 250 notes in our database right now and I'm not sure which type should be the right one for each of them. So if AtoM comes with empty notes table maybe it's easier to just truncate the table?

Thanks and appreciate your help.

Regards,

Josef

Dne čtvrtek 25. března 2021 v 9:46:34 UTC+1 uživatel José Raddaoui napsal:

[José Raddaoui]

Hi Josef,

Sorry for the delay, I hope this is still helpful. I've created a PHP script to restore the terms, it uses the data from AtoM's fixtures folder and it could be used to restore other taxonomies (defaults to note types [id = 37]). You'll need to download the script to the AtoM folder and execute it with a Symfony command:

cd /path/to/atom

wget https://gist.githubusercontent.com/jraddaoui/c5495f1050e7a49cc601ff00e236f960/raw/restore_taxonomy_terms.php

php symfony tools:run restore_taxonomy_terms.php

AtoM comes with around 20 notes, all of them are display and source notes added to other terms, so I guess you could truncate the `note` and `note_i18n` tables. The search index won't be updated so, after you manually fix/delete the notes without type, make sure to run the search populate task ...

php symfony search:populate

Best regards,

Radda.

[Josef Vítovec]

Hi Radda,

thank you so much for the script, it was still helpful! I just had to add polyfill for the array_key_first function (https://www.php.net/manual/en/function.array-key-first.php) since we're using PHP 7.2 but then the script finished without any errors. After running the search populate task all note types terms appeared in GUI and moreover importing SKOSes works like a charm now!

I really appreciate your effort.

Regards,

Josef  

Dne úterý 6. dubna 2021 v 10:35:07 UTC+2 uživatel José Raddaoui napsal:

[José Raddaoui]

Hi Josef,

I didn't notice array_key_first was PHP 7.3 or higher, thanks for the feedback and for reporting back, I'm glad it was helpful :)

Best,

Radda.