Problem/Motivation

We discovered in #2975334-19: Prevent changes that would leak into the Live workspace that a very complex entity query which joins the base entity table multiple times also has to join the workspace_association table for each of them.

Proposed resolution

Figure out a solution to only join the workspace_association table once per entity type.

Remaining tasks

TBD.

User interface changes

Nope.

API changes

Nope.

Data model changes

Nope.

Issue fork drupal-2983639

Command icon Show commands

Start within a Git clone of the project using the version control instructions.

Or, if you do not have SSH keys set up on git.drupalcode.org:

About issue forks

Comments

amateescu created an issue. See original summary.

amateescu’s picture

amateescu commented

Just for future debugging reference, this is the query generated by \Drupal\Tests\workspace\Kernel\WorkspaceIntegrationTest::testEntityQueryRelationship() before #2975334: Prevent changes that would leak into the Live workspace:

SELECT COALESCE(workspace_association.target_entity_revision_id, base_table.vid) AS vid, base_table.nid AS nid
FROM 
test48799856node base_table
LEFT OUTER JOIN test48799856workspace_association workspace_association ON workspace_association.target_entity_type_id = 'node' AND workspace_association.target_entity_id = base_table.nid AND workspace_association.workspace = 'stage'
INNER JOIN test48799856node_field_revision node_field_revision ON node_field_revision.vid = COALESCE(workspace_association.target_entity_revision_id, base_table.vid)
INNER JOIN test48799856node_revision node_revision ON node_revision.vid = COALESCE(workspace_association.target_entity_revision_id, base_table.vid)
INNER JOIN test48799856node_field_data node_field_data ON node_field_data.nid = base_table.nid
INNER JOIN test48799856node node ON node.nid = base_table.nid
INNER JOIN test48799856node_revision__field_test_node node_revision__field_test_node ON node_revision__field_test_node.revision_id = COALESCE(workspace_association.target_entity_revision_id, base_table.vid)
LEFT OUTER JOIN test48799856node node_2 ON node_2.nid = node_revision__field_test_node.field_test_node_target_id
LEFT OUTER JOIN test48799856workspace_association workspace_association_2 ON workspace_association_2.target_entity_type_id = 'node' AND workspace_association_2.target_entity_id = node_2.nid AND workspace_association_2.workspace = 'stage'
INNER JOIN test48799856node_field_revision node_field_revision_2 ON node_field_revision_2.vid = COALESCE(workspace_association_2.target_entity_revision_id, node_2.vid)
LEFT OUTER JOIN test48799856node node_3 ON node_3.nid = node_revision__field_test_node.field_test_node_target_id
LEFT OUTER JOIN test48799856workspace_association workspace_association_3 ON workspace_association_3.target_entity_type_id = 'node' AND workspace_association_3.target_entity_id = node_3.nid AND workspace_association_3.workspace = 'stage'
INNER JOIN test48799856node_revision node_revision_2 ON node_revision_2.vid = COALESCE(workspace_association_3.target_entity_revision_id, node_3.vid)
LEFT OUTER JOIN test48799856node node_4 ON node_4.nid = node_revision__field_test_node.field_test_node_target_id
LEFT OUTER JOIN test48799856workspace_association workspace_association_4 ON workspace_association_4.target_entity_type_id = 'node' AND workspace_association_4.target_entity_id = node_4.nid AND workspace_association_4.workspace = 'stage'
INNER JOIN test48799856node_field_data node_field_data_2 ON node_field_data_2.nid = node_4.nid
LEFT OUTER JOIN test48799856node node_5 ON node_5.nid = node_revision__field_test_node.field_test_node_target_id
LEFT OUTER JOIN test48799856workspace_association workspace_association_5 ON workspace_association_5.target_entity_type_id = 'node' AND workspace_association_5.target_entity_id = node_5.nid AND workspace_association_5.workspace = 'stage'
INNER JOIN test48799856node node_6 ON node_6.nid = node_5.nid
INNER JOIN test48799856node_revision__field_test_entity node_revision__field_test_entity ON node_revision__field_test_entity.revision_id = COALESCE(workspace_association.target_entity_revision_id, base_table.vid)
LEFT OUTER JOIN test48799856entity_test_mulrev entity_test_mulrev ON entity_test_mulrev.id = node_revision__field_test_entity.field_test_entity_target_id
INNER JOIN test48799856entity_test_mulrev_property_revision entity_test_mulrev_property_revision ON entity_test_mulrev_property_revision.revision_id = entity_test_mulrev.revision_id
LEFT OUTER JOIN test48799856entity_test_mulrev entity_test_mulrev_2 ON entity_test_mulrev_2.id = node_revision__field_test_entity.field_test_entity_target_id
INNER JOIN test48799856entity_test_mulrev_property_data entity_test_mulrev_property_data ON entity_test_mulrev_property_data.id = entity_test_mulrev_2.id
LEFT OUTER JOIN test48799856entity_test_mulrev entity_test_mulrev_3 ON entity_test_mulrev_3.id = node_revision__field_test_entity.field_test_entity_target_id
INNER JOIN test48799856entity_test_mulrev entity_test_mulrev_4 ON entity_test_mulrev_4.id = entity_test_mulrev_3.id
WHERE (node_field_revision.title LIKE 'stage node 2' ESCAPE '\\') AND (node_revision.revision_uid = '2') AND (node_field_data.type = 'page') AND (node.uuid LIKE '20c0e17e-50a0-4945-ab0a-ca0fe521fcaa' ESCAPE '\\') AND (node_field_revision_2.title LIKE 'stage node 1' ESCAPE '\\') AND (node_revision_2.revision_uid = '2') AND (node_field_data_2.type = 'page') AND (node_6.uuid LIKE '2711d2aa-355b-46c6-b0f4-1cb8d3caf15c' ESCAPE '\\') AND (entity_test_mulrev_property_revision.name LIKE 'stage entity\\_test\\_mulrev' ESCAPE '\\') AND (entity_test_mulrev_property_data.non_rev_field LIKE 'stage non-revisionable value' ESCAPE '\\') AND (entity_test_mulrev_4.uuid LIKE 'f5af6aa6-5de6-421f-983f-43113b7d9051' ESCAPE '\\')

And this is the query generated after #2975334: Prevent changes that would leak into the Live workspace, where we switched the referenced entity type from entity_test_mulrev to entity_test_mulrevpub:

SELECT COALESCE(workspace_association.target_entity_revision_id, base_table.vid) AS vid, base_table.nid AS nid
FROM 
test25990418node base_table
LEFT OUTER JOIN test25990418workspace_association workspace_association ON workspace_association.target_entity_type_id = 'node' AND workspace_association.target_entity_id = base_table.nid AND workspace_association.workspace = 'stage'
INNER JOIN test25990418node_field_revision node_field_revision ON node_field_revision.vid = COALESCE(workspace_association.target_entity_revision_id, base_table.vid)
INNER JOIN test25990418node_revision node_revision ON node_revision.vid = COALESCE(workspace_association.target_entity_revision_id, base_table.vid)
INNER JOIN test25990418node_field_data node_field_data ON node_field_data.nid = base_table.nid
INNER JOIN test25990418node node ON node.nid = base_table.nid
INNER JOIN test25990418node_revision__field_test_node node_revision__field_test_node ON node_revision__field_test_node.revision_id = COALESCE(workspace_association.target_entity_revision_id, base_table.vid)
LEFT OUTER JOIN test25990418node node_2 ON node_2.nid = node_revision__field_test_node.field_test_node_target_id
LEFT OUTER JOIN test25990418workspace_association workspace_association_2 ON workspace_association_2.target_entity_type_id = 'node' AND workspace_association_2.target_entity_id = node_2.nid AND workspace_association_2.workspace = 'stage'
INNER JOIN test25990418node_field_revision node_field_revision_2 ON node_field_revision_2.vid = COALESCE(workspace_association_2.target_entity_revision_id, node_2.vid)
LEFT OUTER JOIN test25990418node node_3 ON node_3.nid = node_revision__field_test_node.field_test_node_target_id
LEFT OUTER JOIN test25990418workspace_association workspace_association_3 ON workspace_association_3.target_entity_type_id = 'node' AND workspace_association_3.target_entity_id = node_3.nid AND workspace_association_3.workspace = 'stage'
INNER JOIN test25990418node_revision node_revision_2 ON node_revision_2.vid = COALESCE(workspace_association_3.target_entity_revision_id, node_3.vid)
LEFT OUTER JOIN test25990418node node_4 ON node_4.nid = node_revision__field_test_node.field_test_node_target_id
LEFT OUTER JOIN test25990418workspace_association workspace_association_4 ON workspace_association_4.target_entity_type_id = 'node' AND workspace_association_4.target_entity_id = node_4.nid AND workspace_association_4.workspace = 'stage'
INNER JOIN test25990418node_field_data node_field_data_2 ON node_field_data_2.nid = node_4.nid
LEFT OUTER JOIN test25990418node node_5 ON node_5.nid = node_revision__field_test_node.field_test_node_target_id
LEFT OUTER JOIN test25990418workspace_association workspace_association_5 ON workspace_association_5.target_entity_type_id = 'node' AND workspace_association_5.target_entity_id = node_5.nid AND workspace_association_5.workspace = 'stage'
INNER JOIN test25990418node node_6 ON node_6.nid = node_5.nid
INNER JOIN test25990418node_revision__field_test_entity node_revision__field_test_entity ON node_revision__field_test_entity.revision_id = COALESCE(workspace_association.target_entity_revision_id, base_table.vid)
LEFT OUTER JOIN test25990418entity_test_mulrevpub entity_test_mulrevpub ON entity_test_mulrevpub.id = node_revision__field_test_entity.field_test_entity_target_id
LEFT OUTER JOIN test25990418workspace_association workspace_association_6 ON workspace_association_6.target_entity_type_id = 'entity_test_mulrevpub' AND workspace_association_6.target_entity_id = entity_test_mulrevpub.id AND workspace_association_6.workspace = 'stage'
INNER JOIN test25990418entity_test_mulrevpub_property_revision entity_test_mulrevpub_property_revision ON entity_test_mulrevpub_property_revision.revision_id = COALESCE(workspace_association_6.target_entity_revision_id, entity_test_mulrevpub.revision_id)
LEFT OUTER JOIN test25990418entity_test_mulrevpub entity_test_mulrevpub_2 ON entity_test_mulrevpub_2.id = node_revision__field_test_entity.field_test_entity_target_id
LEFT OUTER JOIN test25990418workspace_association workspace_association_7 ON workspace_association_7.target_entity_type_id = 'entity_test_mulrevpub' AND workspace_association_7.target_entity_id = entity_test_mulrevpub_2.id AND workspace_association_7.workspace = 'stage'
INNER JOIN test25990418entity_test_mulrevpub_property_data entity_test_mulrevpub_property_data ON entity_test_mulrevpub_property_data.id = entity_test_mulrevpub_2.id
LEFT OUTER JOIN test25990418entity_test_mulrevpub entity_test_mulrevpub_3 ON entity_test_mulrevpub_3.id = node_revision__field_test_entity.field_test_entity_target_id
LEFT OUTER JOIN test25990418workspace_association workspace_association_8 ON workspace_association_8.target_entity_type_id = 'entity_test_mulrevpub' AND workspace_association_8.target_entity_id = entity_test_mulrevpub_3.id AND workspace_association_8.workspace = 'stage'
INNER JOIN test25990418entity_test_mulrevpub entity_test_mulrevpub_4 ON entity_test_mulrevpub_4.id = entity_test_mulrevpub_3.id
WHERE (node_field_revision.title LIKE 'stage node 2' ESCAPE '\\') AND (node_revision.revision_uid = '2') AND (node_field_data.type = 'page') AND (node.uuid LIKE '4fbac680-59ba-4105-a01b-41f19f0e34a4' ESCAPE '\\') AND (node_field_revision_2.title LIKE 'stage node 1' ESCAPE '\\') AND (node_revision_2.revision_uid = '2') AND (node_field_data_2.type = 'page') AND (node_6.uuid LIKE '7219cd34-32af-4e24-90bc-46dd875e0a44' ESCAPE '\\') AND (entity_test_mulrevpub_property_revision.name LIKE 'stage entity\\_test\\_mulrevpub' ESCAPE '\\') AND (entity_test_mulrevpub_property_data.non_rev_field LIKE 'stage non-revisionable value' ESCAPE '\\') AND (entity_test_mulrevpub_4.uuid LIKE '2ccc4280-05e1-4d16-a50e-5e525eb832eb' ESCAPE '\\')
Mixologic’s picture

Mixologic
He/Him/His
Primary language English
Location Portland, OR
commented

So I noticed that testEntityQueryRelationship was taking a long time to run on drupalci.

Turns out that the behemoth of a query that gets generated is hitting a bug in mysql where it cant hardly parse the query, and it takes it 4-5 minutes to run.

When I try on mysql 5.7, everything is fine, and the test is almost instant, so its only a bug with mysql 5.5.

So we should also be concerned about triggering the same sorts of bugs for people with mysql 5.5. in production.

effulgentsia’s picture

effulgentsia commented

When I try on mysql 5.7, everything is fine, and the test is almost instant, so its only a bug with mysql 5.5.

What's the behavior on mysql 5.6? I ask because security support of mysql 5.5 ends at the end of 2018 (see page 19 of http://www.oracle.com/us/support/library/lifetime-support-technology-069...), which is before workspace.module will be released as a non-experimental module.

Mixologic’s picture

Mixologic
He/Him/His
Primary language English
Location Portland, OR
commented

havent had anybody ask for a 5.6 mysql container.

I've mentioned in other threads that Im loathe to add every possible db container version + php version because we end up with gigantic, expensive matrix'es of tests. But If we really want a mysql container for 5.6 we could do that too.

I'll see about adding it and see what happens.

Version: 8.6.x-dev » 8.7.x-dev

Drupal 8.6.0-alpha1 will be released the week of July 16, 2018, which means new developments and disruptive changes should now be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

amateescu’s picture

amateescu commented
Component: workspace.module » workspaces.module

Fix component following module rename.

amateescu’s picture

amateescu commented

A possible solution for this problem is to change the way we track the workspace association of a revision, and use a "workspace target" revision metadata field on the tracked entity type rather than the current standalone `workspace_association` entity type.

Version: 8.7.x-dev » 8.8.x-dev

Drupal 8.7.0-alpha1 will be released the week of March 11, 2019, which means new developments and disruptive changes should now be targeted against the 8.8.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.0-alpha1 will be released the week of October 14th, 2019, which means new developments and disruptive changes should now be targeted against the 8.9.x-dev branch. (Any changes to 8.9.x will also be committed to 9.0.x in preparation for Drupal 9’s release, but some changes like significant feature additions will be deferred to 9.1.x.). For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.9.x-dev » 9.1.x-dev

Drupal 8.9.0-beta1 was released on March 20, 2020. 8.9.x is the final, long-term support (LTS) minor release of Drupal 8, which means new developments and disruptive changes should now be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 9.1.x-dev » 9.2.x-dev

Drupal 9.1.0-alpha1 will be released the week of October 19, 2020, which means new developments and disruptive changes should now be targeted for the 9.2.x-dev branch. For more information see the Drupal 9 minor version schedule and the Allowed changes during the Drupal 9 release cycle.

Version: 9.2.x-dev » 9.3.x-dev

Drupal 9.2.0-alpha1 will be released the week of May 3, 2021, which means new developments and disruptive changes should now be targeted for the 9.3.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.0-rc1 was released on November 26, 2021, which means new developments and disruptive changes should now be targeted for the 9.4.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.0-alpha1 was released on May 6, 2022, which means new developments and disruptive changes should now be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 10.1.x-dev

Drupal 9.5.0-beta2 and Drupal 10.0.0-beta2 were released on September 29, 2022, which means new developments and disruptive changes should now be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 10.1.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch, which currently accepts only minor-version allowed changes. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

amateescu’s picture

amateescu commented
Title: Figure out a way to not join the workspace_association table for every duplicate base table join of an entity query » [PP-1] Figure out a way to not join the workspace_association table for every duplicate base table join of an entity query
Status: Active » Postponed
Related issues: +#2875033: Optimize joins and table selection in SQL entity query implementation

The suggestion from #8 was implemented in #3062434: Track the workspace of a revision in a base field and convert the workspace_association entity type to a custom index, but it didn't help with the problem described in this issue.

I think #2875033: Optimize joins and table selection in SQL entity query implementation has a very big potential to help with workspace's query alters, so postponing on that issue.

Version: 11.x-dev » main

Drupal core is now using the main branch as the primary development branch. New developments and disruptive changes should now be targeted to the main branch.

Read more in the announcement.

amateescu’s picture

amateescu commented
Title: [PP-1] Figure out a way to not join the workspace_association table for every duplicate base table join of an entity query » Re-enable a bit of test coverage for Workspaces
Status: Postponed » Active

#2875033: Optimize joins and table selection in SQL entity query implementation is in, let's check if we can re-enable those query conditions now.

amateescu opened merge request !15961

amateescu’s picture

amateescu commented
Status: Active » Needs review

Seems to work fine locally. It helps that we don't have to deal with mysql 5.5 as well :)

smustgrave’s picture

smustgrave commented
Status: Needs review » Reviewed & tested by the community

Based on all 3 database types running green and change seems minimal see no objections

  • catch committed 8ddeae42 on 11.4.x 
    task: #2983639 Re-enable a bit of test coverage for Workspaces

By:...

  • catch committed c2fb9afc on 11.x 
    task: #2983639 Re-enable a bit of test coverage for Workspaces

By:...

  • catch committed 524f43b0 on main 
    task: #2983639 Re-enable a bit of test coverage for Workspaces

By:...
catch’s picture

catch
he/him
Primary language English
commented
Version: main » 11.4.x-dev
Status: Reviewed & tested by the community » Fixed

Committed/pushed to main and cherry-picked to 11.x and 11.4.x, thanks!

Now that this issue is closed, review the contribution record.

As a contributor, attribute any organization that helped you, or if you volunteered your own time.

Maintainers, credit people who helped resolve this issue.

catch closed merge request !15961

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.