How to empty a taxonomy

[Roger Rutishauser]

Hi,

I would like to remove all items in the places Taxonomy. In my case, the ID of the places taxonomy is 42. So I thought I could do like this:

SET FOREIGN_KEY_CHECKS=0;

DELETE term, term_i18n FROM term INNER JOIN term_i18n ON term.id = term_i18n.id WHERE term.taxonomy_id = 42;

SET FOREIGN_KEY_CHECKS=1;

so far so good.

But after clearing cache and restart php7 and memcache, the terms appear again, one after the other. After like 5 minutes or so, all terms are back again (but without hierarchy)

There must be a better way to empty a taxonomy, but couldn't find a solution. Can anybody help?

Cheers, Roger

[Dan Gillean]

Hi Roger, 

In AtoM, most records have an entry in the object table. My guess it that terms might be reappearing because the source row in the object table with the corresponding term ID has not been deleted? The other possibility is that you haven't repopulated the search index - so while the terms are gone from the database, they are still in the search index. 

Instead, I would recommend trying the following: 

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

This deletion should cascade to the other tables (with the foreign key check in place). I suggest restarting PHP-FPM and memcached, clearing the application cache, and repopulating the search index after running this query. Also, it's always a good idea to make a backup before using SQL queries in AtoM - especially if you are working against a production instance! 

 I would also caution you about turning off that foreign key check - we've seen some pretty corrupted databases where this setting has been accidentally disabled for a long time! 

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/b8cdf3be-7214-48f2-92fb-be115706c0b9%40googlegroups.com.

[Roger Rutishauser]

Hi Dan

Thank you. Your Query worked, but only when turning off foreign key check. 

If executed without turning it off, the output is: 

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`atom25dbtest`.`term`, CONSTRAINT `term_FK_3` FOREIGN KEY (`parent_id`) REFERENCES `term` (`id`))

Furthermore, the slugs of the deleted terms are still there, so the next time I import SKOS with more or less the same terms, it will create a slug like "slug-2", and next time "slug-3".

So I purged the whole DB and started all over. The first SKOS import was now successful and the slugs are nice, without an "-2" ending.

Cheers, Roger

To unsubscribe from this group and stop receiving emails from it, send an email to ica-ato...@googlegroups.com.

[Dan Gillean]

Hi Roger, 

Interesting - I did test that query in my local Vagrant box, and run it by one of our developers. It should have cascaded to other tables, including the slug table. That said, it's possible that some of the earlier queries you attempted altered the final outcome of this, but I will try to follow up on this to make sure I'm not missing something. 

In any case, I'm glad that you've gotten it to work! FYI, I am now working on a dedicated documentation page for common SQL queries in AtoM, greatly expanding the current small section based on many of the previous threads in the forum. 

Cheers, 

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

@accesstomemory

he / him

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/59248e4d-b99a-4f6b-8fcd-b0dabc393a7f%40googlegroups.com.

[Roger Rutishauser]

Hey cool, looking forward to the extended SQL section!

BTW: As mentioned before I dropped the database with php symfony propel:insert-sql, deleted config file and started all over with the web-installer. I then inserted loads of taxonomy terms and archival descriptions etc. I tried your SQL-query again, and still, it echoes the same error. So I guess it's not because I did some weird SQL queries before :-)

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/59248e4d-b99a-4f6b-8fcd-b0dabc393a7f%40googlegroups.com.

[Dan Gillean]

Hi Roger, 

Thanks for following up. 

When I brought this error up with one of our developers, we realized that the problem may be hierarchical organization. Terms in AtoM can be organized hierarchically (i.e. Broader and Narrower in SKOS terminology). In a table-based relational database, we manage these relationships using parent ID values and a Nested set model. However, the query I provided will delete the terms in the order they are in the database, without factoring in relational position. Consequently, you might end up deleting a parent term before its children are deleted, which could lead to  the kind of foreign key constraint error you encountered. When I tested the query before sharing it, I was using a Subjects taxonomy filled only with sibling terms - there were no hierarchical relations. Disabling the foreign key check allowed you to bypass that problem, which I *think* should be okay for something as broad as deleting all terms in a taxonomy, so long as you re-enable it after. I'm still hoping we can craft a better query that doesn't require that, however. 

Our developer is going to take a look and see if he can prepare an updated query that will meet these requirements. If he's successful, I'll include it in the documentation, and update this thread with the query for anyone else who comes across it in the future.  

Regards, 

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

@accesstomemory

he / him

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/2490a959-974f-4b3e-989c-d7948233c685%40googlegroups.com.

[Roger Rutishauser]

Hi Dan

Many thanks to you and your team.

Indeed, I am using hierarchical term relationships.

Cheers, Roger

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/2490a959-974f-4b3e-989c-d7948233c685%40googlegroups.com.