Missing taxonomy hierarchy items in 8.6.0 after running taxonomy_update_8502

Can confirm, we are seeing a similar issue in our testing of 8.6.0. Our taxonomy_term__parent table has 105 rows while taxonomy_term_hierarchy in the 8.5.6 schema had 19,624.

This is critical if it is causing data loss on upgrade.

The ranged query is needed to avoid updating all terms in a single batch, so simply removing it is not the solution. However it may indicate we are not tracking the $sandbox['current'] id correctly.

What about this?

We could use test coverage with > 100 terms for this.

Also there's a setting for entity update batch size, i.e. Settings::get('entity_update_batch_size', 50); (this would allow the test database to contain a smaller number of rows and also gives sites more flexibility.

Instead of $sandbox['current']++ in the loop we could maybe do $sandbox['current'] += count($hierarchy) but they should be the same in the end.

The patch looks OK - the second argument should be the number of rows (the limit), however the current code should just be making that number bigger, so I can't really see why it's causing the issue here.

There is no sort on the query; is LIMIT without ORDER BY guaranteed to be deterministic in MySQL (and other databases)?

No there's no guarantee on order and that's definitely a bug. Updated patch to include that.

@franco cazzaro Drupal updates are batched - i.e. the update function is supposed to run multiple times, so the bug is in that not happening properly (either query ordering or $sandbox not being updated correctly.

I spotted another bug that I mentioned in #2997982: Orphan term hierarchy records can cause taxonomy_update_8502 to enter an infinite loop - if the same tid arrives in two separate batches we will attempt to reuse delta 0 for the second batch. I think $tid should be stored in the sandbox so we remember it across batches. Is it worth fixing this here as well, or in the other issue?

I'm wondering whether it would be easier/possible to just perform an insert from select and skip the batch altogether.

Not sure how we would generate the deltas in pure SQL with an INSERT INTO ... SELECT statement. It is perhaps technically possible, but probably not portable across database engines.

@longwave I think it's worth doing that here to avoid two competing patches or dependencies between them, we should mark the other issues as duplicate of this.

@catch:

I agree to bring that patch over, however the two issues seem to reports slightly different problems: the one here is caused by performing the update in multiple batches, while #2997982: Orphan term hierarchy records can cause taxonomy_update_8502 to enter an infinite loop seems to have troubles with just a single batch.

@plach yes this seems like a different bug just not sure how to manage fixing both bugs in independent issues.

@longwave, #17:

This seems to be portable but it involves temporary + filesort usage, so it might time out on large datasets:

SELECT t.vid AS bundle, 0 AS deleted, t.tid AS entity_id, t.tid AS revision_id, t.langcode AS langcode, COUNT(h2.parent) AS delta, h.parent AS parent_target_id
FROM taxonomy_term_hierarchy h
LEFT JOIN taxonomy_term_hierarchy h2 ON h.tid = h2.tid AND h2.parent < h.parent
JOIN taxonomy_term_data t ON t.tid = h.tid
GROUP BY h.tid, h.parent
ORDER BY h.tid, h.parent

@catch, #20:

Do you mean we'd want to make sure both are fixed before committing anything?

MySQL queries don't count towards max_execution_time so it shouldn't time out in PHP. The only risk is if apache or varnish has a short timeout when running via the browser.

@plach actually I don't think it matters, this is much more critical than the multiple hierarchy one, so we might want to proceed with different issues after all if it means we can hotfix this one quickly.

Yep, I was referring to browsers updates.

@plach Oh, that self join is clever, nice work - but I also worry about how long it might take on sites with extremely large numbers of terms, which is the reason we batch in the first place I guess?

Back to NR because #13 failed on some spurious curl error.

Not going to mark this reviewed, but want to follow up from my "me-too" to share some insights from our testing for others that hit this issue.

We tested this with a build-in-progress with multiple migrations and ~20k taxonomy terms running PostgreSQL. We found that nearly all of our taxonomy terms had "disappeared", but the term data was still in the database. We were able to repair in place by re-running our migrations, but that obviously isn't going to be an option for most.

Testing patches this morning and I can confirm that the patch in #13 does fix the issue on database updates, when run with Drush's

updb

command. Interestingly, the corresponding Drupal Console command still fails with the patch, only performing 100 rows (one batch cycle) through the process. I haven't dug into the differences between the two enough to give more detail, but that's another potential wrinkle that folks might want to be aware of. We have NOT tested with update.php yet, as our docker containers are built to refuse connection to those files (because you have Drush and Console, rite?) :D

Locally, the Drush command executes relatively quickly in our cli containers - on the order of a couple of seconds. I'm not sure that 20k terms is the largest dataset, but I didn't experience anything that makes me concerned for query performance off the bat.

#2998221: Primary language taxonomy terms unpublished during 8.6.0 upgrade

I just performed some testing of the query proposed in #21. In my local env (MacBook Pro, 2,6 GHz Intel Core i7, 16 GB 1600 MHz DDR3, OS X 10.10.5 :D, MySql 5.5.38) I get the following numbers for the following query:

INSERT taxonomy_term__parent
SELECT t.vid AS bundle, 0 AS deleted, t.tid AS entity_id, t.tid AS revision_id, t.langcode AS langcode, COUNT(h2.parent) AS delta, h.parent AS parent_target_id
FROM taxonomy_term_hierarchy h
LEFT JOIN taxonomy_term_hierarchy h2 ON h.tid = h2.tid AND h2.parent < h.parent
JOIN taxonomy_term_data t ON t.tid = h.tid
GROUP BY h.tid, h.parent
ORDER BY h.tid, h.parent

• A taxonomy with ~20K terms and ~60K hierarchy items takes ~1s to migrate
• A taxonomy with ~200K terms and ~600K hierarchy items takes ~10s to migrate
• A taxonomy with ~1.3 million terms and ~4 million hierarchy items takes ~95s to migrate

The latter seems definitely at risk of timing out when running update.php via browser. It's fairly likely that sites with such numbers would update via drush but we cannot rely on that, so I guess this rules out the "insert from select" approach.

@franco cazzaro:

Would you be able to test patch #13 on a DB backup and check whether it fixes the issue you reported?

Thank you for trying the patch and reporting back.

This points to a bug in Drupal Console not performing batch updates correctly. @logickal also said

Interestingly, the corresponding Drupal Console command still fails with the patch, only performing 100 rows (one batch cycle) through the process.

However the patch in #13 is still valid as this solves a different issue regarding consistency between batches.

This is the patch from #13:

--- a/core/modules/taxonomy/taxonomy.install
+++ b/core/modules/taxonomy/taxonomy.install
@@ -40,7 +40,9 @@ function taxonomy_update_8502(&$sandbox) {
   $hierarchy = $select
     ->fields('h', ['tid', 'parent'])
     ->fields('d', ['vid', 'langcode'])
-    ->range($sandbox['current'], $sandbox['current'] + 100)
+    ->range($sandbox['current'], 100)
+    ->orderBy('tid', 'ASC')
+    ->orderBy('parent', 'ASC')
     ->execute()
     ->fetchAll();
 

The first change looks right: the API docs for Select::range() include

$length: The number of records to return from the result set.

Adding the two orderBy() calls in that order looks odd to me. That means sort by tid, and if there are multiple rows with the same value of tid, then sort by parent.

I guess the point is that Drupal supports taxonomy terms with multiple parents, although I have never seen it. Even though it looks odd, it is the right thing to do.

I have reviewed, and it looks good to me, but I have not tested, so I will leave this issue as "Needs Review". I am not sure whether the core maintainers will require test coverage for this change.

I am also adding the "Needs issue summary update" tag.

From Comments #27 and #31-33, it seems that the initial report comes from a bug in Drupal Console. Has anyone created a bug report for that project? It would be nice to have a link in the comments here.

The issue summary should describe the problem(s) that the patch actually solves. I guess there are two problems:

1. If N, the number of entries in taxonomy_term_hierarchy, is very large, then the original code will try to process about sqrt(N) of them at once, which may use up PHP's memory.
2. If the database orders terms inconsistently each time it is queried, then some terms will be processed more than once and some terms will be skipped. (Without an ORDER BY clause, SQL does not guarantee a consistent order, so this would not be a bug in the database.)

Both of these problems are hard to test, so I hope that the core maintainers will not insist on test coverage.

Hi,
I got issue with taxonomies. I will see all of terms when I am logged in to site. When i log out, I see only few terms. Rollback to 8.5.7 and everything works fine.

My issue is solved. Need to go term settings and click 'This translation is published'.

This merges #13 with #2997982-7: Orphan term hierarchy records can cause taxonomy_update_8502 to enter an infinite loop and allows to fix three different issues with taxonomy_update_8502:

• sorting/consistency problems with multiple batches that can lead to skipping taxonomy hierarchy records or processing them multiple times, which in turns would lead to either missing parent records or integrity constraint violations;
• incorrect handling of multiple parents when those spans multiple batches (what @longwave fixed in #2997982-7: Orphan term hierarchy records can cause taxonomy_update_8502 to enter an infinite loop, great catch!);
• scalability issues when processing a large number of terms, since at each batch step the batch size increases, potentially leading to OOMs, as reported in [2997982-14].

It seems we clarified that parts of the problems reported here were caused by Drupal Console and should be fixed there. By just inspecting the code I cannot identify any other obvious problem with the update logic, so I'm +1 to RTBC/commit this as soon as we have a final confirmation this is good by the people that were affected by this and showed up in #2997982: Orphan term hierarchy records can cause taxonomy_update_8502 to enter an infinite loop. I asked them to move here.

I will retitle/update the issue summary shortly.

Updated IS

Credited @longwave for his work in #2997982: Orphan term hierarchy records can cause taxonomy_update_8502 to enter an infinite loop.

Minor IS update

Expected failure for the test-only patch.

Mentioned the Drupal Console issue in the IS.

Code looks good to me, we just need to some people to manually test and report back

Looks good. I just migrated 40.000+ terms on PostgreSQL with 512MB PHP-Memory limit using drush and patch from #38. I tested on two boxes and run every update twice. Nice work, thanks!

Based on #47

It appears Drupal Console doesn't make any attempt to deal with batched updates:

https://github.com/hechoendrupal/drupal-console/blob/master/src/Command/... implies that the update function is only ever run once and the $context parameter is not inspected.

https://github.com/hechoendrupal/drupal-console/issues/3574 and https://github.com/hechoendrupal/drupal-console/issues/3787 are related questions about batch jobs but have no response.

edit: raised https://github.com/hechoendrupal/drupal-console/issues/3929

Thanks @longwave, we'll mention your findings in the 8.6.0 release notes.

#38 works.

Previously I thought it was not working because I was getting [error] SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-0-0-es' when I was updating with drush updb.

The problem was on my side. I was using
drush sql:cli < db-backup.sql
over my local db, that I tried to update previously without the patch.

the drush sql:cli was overriding the old tables, but not removing the tables creaded by previous drush updb like (taxonomy_term__parent). So when I tried to update the db with the patch applied the following error appeared:

[error]  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-0-0-es' for key 'PRIMARY': INSERT INTO {taxonomy_term__parent} (bundle, entity_id, revision_id, langcode, delta, parent_target_id) ...

...

 [error]  Update failed: taxonomy_update_8502 
 [error]  Update aborted by: taxonomy_update_8502 
 [error]  Finished performing updates. 

Solution

I removed all the database tables and ran
drush sql:cli < db-backup.sql
again.

This time the update worked with no errors.

I hope it helps.

Committed/pushed to 8.7.x and cherry-picked to 8.6.x. Thanks!

@joonapenttila:

That issue seems related to #2998221: Primary language taxonomy terms unpublished during 8.6.0 upgrade.

@espurnes:

Thanks for the feedback, that sounds exactly what happened in #2997982: Orphan term hierarchy records can cause taxonomy_update_8502 to enter an infinite loop.

I'm not seeing the commit in 8.6.x or 8.7.x

It seems they were not actually pushed.

Now pushed, sorry folks.

It would be great to solve the logic for this problem space once, adding a related issue: #2977990: Add a helper class to make updating content entities easy.

Apparently nobody ran this update path since this got committed to Drupal 8.6 in January of this year. 😲 😞

@fgm reported a problem similar to this in #2543726-362: Make $term->parent behave like any other entity reference field, to fix REST and Migrate support and de-customize its Views integration, but then discovered that this was a bug in some Drush 9 plugin/some new behavior in Drush 9.

I have not seen any error when I update the Drupal 8.6 release

I have about 80 taxonomy terms.

How do I know if I need to restore the site ?

Thank you

The various errors mentioned in this issue only apply for sites with more than 100 terms. Sites with 100 terms or less would have them all processed in a single batch (whether you use update.php, Drush or even Drupal Console), which bypasses the mentioned bugs.

I was wrong. I have 219 terms in all. Distributed in 19 vocabularies.

When Drupal 8.6 was released on September 6th, I updated it with drush updatedb

How can I check that the update has gone smoothly?

@zenimagine: I assume you backed up the database before the update!

You can compare the actual database tables: taxonomy_term_hierarchy before the update and taxonomy_term__parent after the update.

You could also install your database backup on a test server. Check each vocabulary and compare to what you have on the live site.

Note that this problem only potentially affects the parent/child relations in vocabularies. If most of your vocabularies are flat (no parents) then you might have had fewer than 100 terms in the taxonomy_term_hierarchy table, in which case you are safe.

I'm confused. I don't use taxonomy on my site as far as I'm aware. Should I update to 8.6.1 from 8.6.0, or ignore the update, or what?

@anthonyjs if you are not using more than 100 Taxonomy terms, and it sounds like you are not using any, then this issue does not apply.
And yes, you should update to 8.6.1 when you get a chance. :)

@everyone_else.. thanks for the hard work here!

I do not have much database management. I added 2 screenshot before and after the update.

After the update I created 3 new taxonomy terms.

Is this correct ? Is there anything else to check?

Is the update ok?

Thank you

@zenimagine: Your screenshots only show the summary of the queries. You would have to compare the full results.

I think the two tables (taxonomy_term_hierarchy before the update and taxonomy_term__parent after the update) should have the same data. The column names will be different.

@benjifisher

How do I do with phpmyadmin to see the complete result ?

In my table there is a column TID and a column PARENT.

In the PARRENT column everything is at 0 before and after the update.

What do you mean by

"If most of your vocabularies are flat (no parents)"

?

I tried to update my Drupal 8.5.7 to 8.6.1 (from tar.gz package and via update.php admin interface), with 3 simple flat taxonomies (all with less than 10 terms) and I found the following error:

taxonomy module
Update #8502
Failed: Drupal\Core\Database\IntegrityConstraintViolationException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '6-0-0-en' for key 'PRIMARY': INSERT INTO {taxonomy_term__parent} (bundle, entity_id, revision_id, langcode, delta, parent_target_id) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5), (:db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9, :db_insert_placeholder_10, :db_insert_placeholder_11), (:db_insert_placeholder_12, :db_insert_placeholder_13, :db_insert_placeholder_14, :db_insert_placeholder_15, :db_insert_placeholder_16, :db_insert_placeholder_17), (:db_insert_placeholder_18, :db_insert_placeholder_19, :db_insert_placeholder_20, :db_insert_placeholder_21, :db_insert_placeholder_22, :db_insert_placeholder_23), (:db_insert_placeholder_24, :db_insert_placeholder_25, :db_insert_placeholder_26, :db_insert_placeholder_27, :db_insert_placeholder_28, :db_insert_placeholder_29), (:db_insert_placeholder_30, :db_insert_placeholder_31, :db_insert_placeholder_32, :db_insert_placeholder_33, :db_insert_placeholder_34, :db_insert_placeholder_35), (:db_insert_placeholder_36, :db_insert_placeholder_37, :db_insert_placeholder_38, :db_insert_placeholder_39, :db_insert_placeholder_40, :db_insert_placeholder_41), (:db_insert_placeholder_42, :db_insert_placeholder_43, :db_insert_placeholder_44, :db_insert_placeholder_45, :db_insert_placeholder_46, :db_insert_placeholder_47), (:db_insert_placeholder_48, :db_insert_placeholder_49, :db_insert_placeholder_50, :db_insert_placeholder_51, :db_insert_placeholder_52, :db_insert_placeholder_53), (:db_insert_placeholder_54, :db_insert_placeholder_55, :db_insert_placeholder_56, :db_insert_placeholder_57, :db_insert_placeholder_58, :db_insert_placeholder_59), (:db_insert_placeholder_60, :db_insert_placeholder_61, :db_insert_placeholder_62, :db_insert_placeholder_63, :db_insert_placeholder_64, :db_insert_placeholder_65), (:db_insert_placeholder_66, :db_insert_placeholder_67, :db_insert_placeholder_68, :db_insert_placeholder_69, :db_insert_placeholder_70, :db_insert_placeholder_71), (:db_insert_placeholder_72, :db_insert_placeholder_73, :db_insert_placeholder_74, :db_insert_placeholder_75, :db_insert_placeholder_76, :db_insert_placeholder_77), (:db_insert_placeholder_78, :db_insert_placeholder_79, :db_insert_placeholder_80, :db_insert_placeholder_81, :db_insert_placeholder_82, :db_insert_placeholder_83); Array ( [:db_insert_placeholder_0] => section [:db_insert_placeholder_1] => 6 [:db_insert_placeholder_2] => 6 [:db_insert_placeholder_3] => en [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => 0 [:db_insert_placeholder_6] => section [:db_insert_placeholder_7] => 10 [:db_insert_placeholder_8] => 10 [:db_insert_placeholder_9] => en [:db_insert_placeholder_10] => 0 [:db_insert_placeholder_11] => 0 [:db_insert_placeholder_12] => tags [:db_insert_placeholder_13] => 12 [:db_insert_placeholder_14] => 12 [:db_insert_placeholder_15] => en [:db_insert_placeholder_16] => 0 [:db_insert_placeholder_17] => 0 [:db_insert_placeholder_18] => tags [:db_insert_placeholder_19] => 13 [:db_insert_placeholder_20] => 13 [:db_insert_placeholder_21] => en [:db_insert_placeholder_22] => 0 [:db_insert_placeholder_23] => 0 [:db_insert_placeholder_24] => tags [:db_insert_placeholder_25] => 15 [:db_insert_placeholder_26] => 15 [:db_insert_placeholder_27] => en [:db_insert_placeholder_28] => 0 [:db_insert_placeholder_29] => 0 [:db_insert_placeholder_30] => publication_category [:db_insert_placeholder_31] => 17 [:db_insert_placeholder_32] => 17 [:db_insert_placeholder_33] => en [:db_insert_placeholder_34] => 0 [:db_insert_placeholder_35] => 0 [:db_insert_placeholder_36] => publication_category [:db_insert_placeholder_37] => 18 [:db_insert_placeholder_38] => 18 [:db_insert_placeholder_39] => en [:db_insert_placeholder_40] => 0 [:db_insert_placeholder_41] => 0 [:db_insert_placeholder_42] => tags [:db_insert_placeholder_43] => 19 [:db_insert_placeholder_44] => 19 [:db_insert_placeholder_45] => en [:db_insert_placeholder_46] => 0 [:db_insert_placeholder_47] => 0 [:db_insert_placeholder_48] => publication_category [:db_insert_placeholder_49] => 20 [:db_insert_placeholder_50] => 20 [:db_insert_placeholder_51] => en [:db_insert_placeholder_52] => 0 [:db_insert_placeholder_53] => 0 [:db_insert_placeholder_54] => publication_category [:db_insert_placeholder_55] => 21 [:db_insert_placeholder_56] => 21 [:db_insert_placeholder_57] => en [:db_insert_placeholder_58] => 0 [:db_insert_placeholder_59] => 0 [:db_insert_placeholder_60] => publication_category [:db_insert_placeholder_61] => 22 [:db_insert_placeholder_62] => 22 [:db_insert_placeholder_63] => en [:db_insert_placeholder_64] => 0 [:db_insert_placeholder_65] => 0 [:db_insert_placeholder_66] => publication_category [:db_insert_placeholder_67] => 23 [:db_insert_placeholder_68] => 23 [:db_insert_placeholder_69] => en [:db_insert_placeholder_70] => 0 [:db_insert_placeholder_71] => 0 [:db_insert_placeholder_72] => publication_category [:db_insert_placeholder_73] => 24 [:db_insert_placeholder_74] => 24 [:db_insert_placeholder_75] => en [:db_insert_placeholder_76] => 0 [:db_insert_placeholder_77] => 0 [:db_insert_placeholder_78] => publication_category [:db_insert_placeholder_79] => 25 [:db_insert_placeholder_80] => 25 [:db_insert_placeholder_81] => en [:db_insert_placeholder_82] => 0 [:db_insert_placeholder_83] => 0 ) in Drupal\Core\Database\Connection->handleQueryException() (line 683 of /dati/data/virtual/drupal/cms/drupal-8.6.1/core/lib/Drupal/Core/Database/Connection.php).

This seems related to #2997982: Orphan term hierarchy records can cause taxonomy_update_8502 to enter an infinite loop.

@plach: Yes, it's possible. I posted my comments also on #2997982. Thanks!

Reporting in from #2998221: Primary language taxonomy terms unpublished during 8.6.0 upgrade.

My site had multilingual taxonomy terms that happened to have "This Translation is published" unchecked. (The content_translation_status of the taxonomy_field_term_data table was 0).

When I upgraded to 8.6.0, a view that referenced these taxonomies had a new filter added: "Taxonomy Term Published = True". I've checked against my 8.5.6 install and the filter was definately added to the view.

This caused a number of rows tagged with these terms to simply vanish, and my site to "break" (though an argument could be made that it was already broken before).

I simply checked the "This translation is published" where it was missing on my sites, and the error resolved itself. I left the filter in place on the views.

Adding issue credit to @samuhe for reporting #2997982: Orphan term hierarchy records can cause taxonomy_update_8502 to enter an infinite loop, which started as a duplicate of this before it veered off into solving a different bug.

Can someone confirm that it's good for #70 ? Thank you

Is this related to the above:

Database updates
Out of date
Some modules have database schema updates to install. You should run the database update script immediately.
Entity/field definitions
Mismatched entity and/or field definitions
The following changes were detected in the entity type and field definitions.
Taxonomy term

The Publishing status field needs to be uninstalled.
The Term Parents field needs to be updated.

This is marked as fixed but at least it does not work in my projects. Am I the only one?

@TipiT This was fixed in the 8.6.1 release, what version are you using? What errors are you getting or symptoms are you seeing? There have been similar issues reported such as #2998221: Primary language taxonomy terms unpublished during 8.6.0 upgrade which are still under investigation.

I try to upgrade 8.53 -> 8.61.

Result:

taxonomy module : 
  Clear entity_bundle_field_definitions cache for new parent field settings.
  Clear caches due to updated taxonomy entity views data.
  Add a 'published' = TRUE filter for all Taxonomy term views and converts  existing ones that were using the 'content_translation_status' field.

Do you wish to run all pending updates? (y/n): y
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-0-0-fi' for key 'PRIMARY': INSERT INTO {taxonomy_term__parent} (bundle, entity_id, revision_id, langcode, [error]
delta, parent_target_id) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4,
:db_insert_placeholder_5), (:db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9, 
...
:db_insert_placeholder_185), (:db_insert_placeholder_186, :db_insert_placeholder_187, :db_insert_placeholder_188, :db_insert_placeholder_189, :db_insert_placeholder_190,
:db_insert_placeholder_191); Array
(
    [:db_insert_placeholder_0] => lisaa_etusivun_tabeihin
    [:db_insert_placeholder_1] => 1
    [:db_insert_placeholder_2] => 1
    [:db_insert_placeholder_3] => fi
    [:db_insert_placeholder_4] => 0
    [:db_insert_placeholder_5] => 0
    [:db_insert_placeholder_6] => lisaa_etusivun_tabeihin
    [:db_insert_placeholder_7] => 2
    [:db_insert_placeholder_8] => 2
    [:db_insert_placeholder_9] => fi
...
    [:db_insert_placeholder_186] => treatment_subcategory
    [:db_insert_placeholder_187] => 34
    [:db_insert_placeholder_188] => 34
    [:db_insert_placeholder_189] => fi
    [:db_insert_placeholder_190] => 0
    [:db_insert_placeholder_191] => 0
)

Performing taxonomy_update_8502                                                                                                                                                    [ok]
Failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-0-0-fi' for key 'PRIMARY': INSERT INTO {taxonomy_term__parent} (bundle, entity_id, revision_id,   [error]
langcode, delta, parent_target_id) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4,
:db_insert_placeholder_5), (:db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9, 
...
:db_insert_placeholder_185), (:db_insert_placeholder_186, :db_insert_placeholder_187, :db_insert_placeholder_188, :db_insert_placeholder_189, :db_insert_placeholder_190,
:db_insert_placeholder_191); Array
(
    [:db_insert_placeholder_0] => lisaa_etusivun_tabeihin
    [:db_insert_placeholder_1] => 1
    [:db_insert_placeholder_2] => 1
    [:db_insert_placeholder_3] => fi
    [:db_insert_placeholder_4] => 0
    [:db_insert_placeholder_5] => 0
    [:db_insert_placeholder_6] => lisaa_etusivun_tabeihin
    [:db_insert_placeholder_7] => 2
    [:db_insert_placeholder_8] => 2
    [:db_insert_placeholder_9] => fi
    [:db_insert_placeholder_10] => 0
    [:db_insert_placeholder_11] => 0
...
   [:db_insert_placeholder_180] => treatment_subcategory
    [:db_insert_placeholder_181] => 33
    [:db_insert_placeholder_182] => 33
    [:db_insert_placeholder_183] => fi
    [:db_insert_placeholder_184] => 0
    [:db_insert_placeholder_185] => 0
    [:db_insert_placeholder_186] => treatment_subcategory
    [:db_insert_placeholder_187] => 34
    [:db_insert_placeholder_188] => 34
    [:db_insert_placeholder_189] => fi
    [:db_insert_placeholder_190] => 0
    [:db_insert_placeholder_191] => 0
)

Cache rebuild complete. [ok]
Finished performing updates.

So the behavior is like described in 2997982 but the patch did not solve the problem.

If you are restoring from a backup, you need to ensure you delete the taxonomy_term__parent table before rerunning the updates. The taxonomy_term__parent is created by the update but does not exist in the backup, so it will not be automatically wiped if you just restore the old database over the top of a broken one.

@plach Do you think we should reconsider adding a truncate here? I don't see how it can harm, it can only help in these cases, as far as I can tell?

not only the taxonomy hierarchy，but also the menu hierarchy.

@sealionking: Menu hierarchy issues are likely unrelated to this issue. Can you open a new issue with details on what you're seeing, such as what version you're updating from, what version you're updating to, and what messages, if any, were output during the update process?

@plach Do you think we should reconsider adding a truncate here? I don't see how it can harm, it can only help in these cases, as far as I can tell?

I'm not sure to be honest: on one hand I agree that it would make the upgrade UX better, but if people need to run the update twice, it means something went wrong previously and it would be good to find out what it is.

Discussed #85 with @catch: neither of us is a fan of the idea of truncating/deleting the table for the reasons stated above. However we could add a warning if the table already exists and bail out early by throwing a more descriptive exception. We could even add a small helper method, so that we start making our update functions more user friendly :)

A follow-up would be welcome.

In my system I have fixed the issue adding:

  try {
    $insert->execute();
  } catch (Exception $e) {
    // Do nothing.
  }

It's a bad practice but works.

@longwave Thank you for #82 tip, that works!

I just did: DROP TABLE `taxonomy_term__parent`;

And the update goes as planned.

I added a follow-up issue as suggested in #86: #3001205: Make update functions more user friendly.

We're still experiencing this issue (updating fomr 8.5.8 to 8.6.2) and I believe it boils down to the final check that assigns $sandbox['#finished']. For us (maybe we're weird) the value of the count for the taxonomy_term_hierarchy table is greater than the number of records returned from the join between taxonomy_term_hierarchy and taxonomy_term_data.

I'm going to investigate that a bit further but for all I know, this mismatch is totally normal. Either way, with the current iteration of the code, the upgrade never exists because the check always thinks there's more rows to process.

Any thoughts?

@mark-mackenzie-nexus:

This issue is closed, and will not get much attention going forward. Please continue to investigate. When you have as much information as you can get (ideally, enough information to reproduce the problem) open a new issue and reference this one.

I had the same issue updating two websites lately.

To repair the terms after the Drupal console upex command, I set my php.ini to a high max_execution_time and run this in Devel's PHP execute form:

use Drupal\taxonomy\Entity\Term;

// First find root terms and then look for children.
$parentIds = [0];
do {
  $childrenIds = []; // This will be used to fill the $parentIds on each iteration when drilling down to the child terms.
  foreach ($parentIds as $parentId) {
    print "Looking for children of tid $parentId";
    if ($parentId !== 0) {
      $parentTerm = Term::load($parentId);
      print ' : "' . $parentTerm->getName() . '"';
    }
    print "\n";

    $childrenResultSet = db_select('taxonomy_term_hierarchy', 'tth')
      ->fields('tth', ['tid'])
      ->condition('parent', $parentId)
      ->execute();

    foreach ($childrenResultSet as $row) {
      $term = Term::load($row->tid);
      $term->set('parent', $parentId);
      print '    saving "' . $term->getName() . "\"\n";
      $term->save();
      $childrenIds[] = $row->tid;
    }
  }
  // All the parent ids have been processed so the next ones are their children.
  $parentIds = $childrenIds;
} while (!empty($parentIds));

This repaired all my vocabularies.
Hope this can help someone else.

I have also faced same issue while database update.

#88 Solved the "taxonomy_term__parent" issue " Integrity constraint violation: 1062 Duplicate entry '4-0-0-en' for key 'PRIMARY'" But facing the following issue.

" Call to a member function getConfigDependencyName() on null"
" Update failed: lightning_media_update_8019"

"Update aborted by: lightning_media_update_8019"
"Finished performing updates."

System details:
Lightning Version upgrade from 3.1.7 to 3.2.0.
- Updating drupal/core (8.5.10 => 8.6.9): Loading from cache

Current Drupal version: 8.6.9

Any updates for the issue will be appreciated. Same issue is open on https://www.drupal.org/project/lightning_media/issues/3029107

patacra: #93 definitely helped me, almost all my vocabularies were broken after the upgrade. Do you know why this script is needed and what exactly is wrong with the upgrade script?

Error sighted.

[notice] Update started: taxonomy_update_8502
[error] SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-0-0-en' for key 'PRIMARY': INSERT INTO {taxonomy_term__parent}

How to get PASS this error.
Before doing update taxonomy_update_8502 make sure that {taxonomy_term__parent} is empty.

This particular update is prone to crashes! If you can do command-line to do updates. Command line eg. drush, terminus, drupal-console, etc..

@longwave Thank you for #82 tip, that works!

I just did: DROP TABLE `taxonomy_term__parent`;

So, is necesary delete before apply /update.php.

thanks

I am facing this issue while updb , I tried comment #87 it worked for me ,
So providing patch for same .

I hit this problem (taxonomy update #8205) while doing a long-jump update (from 8.5.5 to 8.7.0). The suggestion to DROP TABLE taxonomy_term__parent didn't work for me (because the update didn't recreate the table). Instead, what did work was to first empty the table (DELETE FROM taxonomy_term__parent;), and then the update worked. I can't claim this "really did the right thing" though, because I don't actively use the taxonomy module/feature.

Same problem with an update from 8.5.6 to 8.7.1. Deleting all rows from taxonomy_term__parent as in #100 helped and also the taxonomy still seems to work.

Hello, I think I have run into this problem:

"missing terms in taxonomy tree UIs, even if the corresponding records are available in the taxonomy tables."

The site has been updated to 8.60, 8.6.3, 8.6.4, 8.6.5, 8.6.7 and then 8.6.12 and 8.7.0, 8.7.2...
Problem is, I can't rollback to 8.5 as data has been addded to the site since.

As the correct taxonomy data is present on the terms themselves, is there a way to rebuild the hierarchical data?