sql for retrieving descendants

Skip to first unread message


May 29, 2020, 8:29:25 PM5/29/20
to AtoM Users

We have a long standing problem that pops up on occasion. We are using atom 2.5.3 The error is:
Parent Resource id '311113' does not exist.  This comes from an import that went really bad.

I have tried the rebuilding the nested sets, repopulating the search and clearing the cache etc. I have also ran the query to find the corruption  that is explained here:

Nothing seems to help.  I just may be left with the option to delete. Now the problem is deleting the record using the command:

php symfony tools:delete-description general-administation-operations-and-publicity

gives me an integrity constraint violation.
Cannot delete or update a parent row: a foreign key constraint fails (`atom253`.`information_object`, CONSTRAINT `information_object_FK_5` FOREIGN KEY (`parent_id`) REFERENCES `information_object` (`id`))

I try to dig down with a query:

SELECT s.object_id, s.slug, i.parent_id FROM information_object i, slug s
.parent_id=311113 AND i.id = s.object_id

.   This does yield a few records:


When I try to delete any of the above using the delete-description I get the same constraint violation (information_object_FK_5)
I try to dig down some more by using the query below for each of the above records
select * from information_object where parent_id = 311116

but 0 records are returned each time.  

Either the data is corrupt or I'm completely missing something here. The delete-description tool says there are 19 descendants.  How does it calculate that?

Also I am wondering how do I get the next level of children (grand children, great grandchildren etc)?  I'd like to know the sql behind it if possible just so I have a better understanding of the database and maybe construct a series of sql delete statements. 

Thanks for your time. I know it's a big question.


José Raddaoui

Jun 1, 2020, 11:39:56 AM6/1/20
to AtoM Users
Hi Jeremy,

Dealing with this kind of data corruptions is hard in AtoM 2.5.x and this is something we're trying to improve in the 2.6 version; with the upgrade to MySQL 8 and the ability to use Common Table Expressions plus some improvements in the foreign keys constraints, it will be a lot easier to fix those issues.

However, in AtoM 2.5 you can only trust the nested set (or "manually" go one by one like you did) to be able to delete a descriptions hierarchy. That being said, the build nested set task should take care of rebuilding the hierarchy and the delete description task does the deletion from the bottom to the top of that hierarchy, which should avoid the foreign key constraint fails. Could it be that you didn't run the deletion task right after rebuilding the nested set? Otherwise, we could try with raw SQL but as I said, it can get complicated.

Best regards.


Jun 1, 2020, 1:46:31 PM6/1/20
to AtoM Users

Thanks for responding. Yes I am sure I ran the delete immediately after the rebuild-nested-set.   I did it once more just to be extra sure.

One interesting point is that the delete process works on our public facing site but not on our production site.  The difference is the public facing site has all the drafts deleted.  We of course can't delete the drafts on our production site.  I'm not sure if that's a clue to help solve the problem.

If you can walk me though some of the sql or perhaps if there is a way to echo out the SQL during a php tools:delete-description process that would be great. Maybe I can work backwards from there.


José Raddaoui

Jun 1, 2020, 2:56:41 PM6/1/20
to AtoM Users
Hi Jeremy,

Okay, that's a shame. I'm not sure if the drafts situation would have something to do with this issue if you only run the problematic import in one of the instances. We have worked recently in a data corruption issue (not exactly the same as this one was caused by the FOREIGN_KEY_CHECKS being disabled in the DB for a while) and we developed the script linked below. Please, make sure you have created a backup of your current database as this script will delete the "corrupted" data entirely and it may go further than what you expect.

Copy that script to your AtoM folder and run it (from the AtoM folder too) with:

php symfony tools:run clean_broken_ios.php

If it works, try deleting the description again and re-index. You may find some useful SQL queries inside the script and, if you want to go deeper, I'd suggest to enable and use the MySQL general log, although the queries generated by the ORM can be hard to read.

Please, let us know how it goes.

Best regards,

José Raddaoui

Jun 1, 2020, 3:04:49 PM6/1/20
to AtoM Users
I forgot to mention that the script will generate a file called "deleted_ios.csv", with the slug and other values from the deleted descriptions during the process. So make sure you run it with a user with enough permissions.


Jun 2, 2020, 11:51:10 AM6/2/20
to AtoM Users
Thank you!  I ran the script and had the output reviewed.  I'm told it revealed many more errors then we thought we had.  We will have to go through them carefully and decide what we want deleted.

Reply all
Reply to author
0 new messages