[Closed] Posts get connected to wrong elements in many to many relationship

[Author]

Posts

[takoL]

Hello,

We are experiencing a kind of weird and complex problem, I will try to explain.

Basically the problem occurs when we have multiple, simultaneous $_POST requests (for instance multiple users submitting a form simultaneously) in which many to many relationships are created. When the scripts that process these requests run "simultaneously" connections are made between wrong elements. Sometimes event between posts that do not have a relationship.

For example see image: example_multiple_relationships _in_post.png

This only happens when the requests/processes overlap. So for instance when we have a long running script... and during this script another request is being made. And it only occurs for many-to-many relationships. It looks like that something goes wrong in the time that it takes for one request/process to insert the intermediary post into the wp_posts table, to make the association in the toolset_associations table and check for the connected_element in the toolset_connected_elements table. While another request/process also tries to insert rows in these tables.

We don't know too much about it but it sounds like something called phantom reads.
PHANTOM READS: Data getting changed in current transaction by other transactions is called Phantom Reads. New rows can be added by other transactions, so you get the different number of rows by firing the same query in the current transaction.

Could you look into this and see if this might be the case?

Maybe it could be fixed by something called record locks
hidden link

In the meantime we will keep investigating as well. We will keep you up to date of any progress.

Cheers and thanks in advance,

Tako

[takoL]

Hi, we have investigated further and we have a way to replicate this issue.

Create three custom post_types: father, mother, child.
Create two many-to-many relationships: father <=> child, mother <=> child.
Execute a script that creates 50 new children and for each child connect to one father.
Simultaneously execute a script that creates 50 new children and for each child connect to one mother.

We just used a shortcode to trigger a script like this:

function lp_trigger_connect_sc($atts)
{

    $pairs = [
        'post_type'    => 'father',
    ];

    $atts = shortcode_atts(
        $pairs,
        $atts
    );

    $query_args = [
        'post_type'     => $atts['post_type'],
        'post_status'   => 'publish',
        'nopaging'      => true,
    ];

    $query = new WP_Query($query_args);
    $response = [];

    if ($query->posts) {
        foreach ($query->posts as $post) {
            $index = 1;
            while ($index <= 50) {
                $post_data = [
                    'post_title'    => $atts['post_type'] . ': ' . $post->ID . '- Index: ' . $index,
                    'post_type'     => 'child',
                    'post_author'   => get_current_user_id(),
                    'post_status'  => 'publish'
                ];
                $new_child_id = wp_insert_post($post_data);

                if ($new_child_id && !is_wp_error($new_child_id)) {
                    $toolset_connect = toolset_connect_posts($atts['post_type'] . '-child', $post->ID, $new_child_id);
                    $response[] = $toolset_connect['message'];
                }
                $index = $index + 1;
            }
        }
    }
    print_r($response);
}

In the picture "child_single_relationships.png" you can see the result for one created child.
Based on the script this child should be connected to one single mother.
As you can see in the picture there are a lot of connections, and even some connections with father <=> child intermediary posts.

Hope this helps.

[takoL]

sorry, posted last reply double 🙂

[Waqar]

Hi Tako,

Thank you for contacting us and I'd be happy to assist.

I've performed several tests on my website with a similar setup, but, I couldn't reproduce this issue of overlapping posts.

Here are the exact steps, that I used to test your custom shortcode:

1. I added three custom post types:
a) father
b) mother
c) child

2. Also added two many-to-many post relationships between:
a) father & child
b) mother & child

3. Next, I added 1 post each, into father and mother post types:
a) Father A
b) Mother A

4, I included your custom shortcode into the active theme's "functions.php" file.

5. On a new page, I added two instances of your custom shortcode, 1 for the father post type and the other for the mother post type:

[lp_trigger_connect_sc]
[lp_trigger_connect_sc post_type="mother"]

After, I visited this page on the front-end, 100 posts were created in the child post type and as expected 50 posts had a relationship set with the "Father A" and the other 50 had a relationship set with the "Mother A".

Can you please test this code again on a clean install and let me know the exact steps, to reproduce this?

regards,
Waqar

[takoL]

Hello Waqar,

Thank you for for the reply.

If I understand correctly you put two shortcodes on one page, and than visited this page.
This creates "one" request from your browser.
The problem only occurs when there are multiple simultaneous requests.
So could you try the following?

Make two pages.
In one page you paste the shortcode:

[lp_trigger_connect_sc]

And in the other page you paste the shortcode

[lp_trigger_connect_sc post_type="mother"]

Than in two separate browser tabs visit these pages simultaneously.

Regards,
Tako

[takoL]

Hello Waqar,

Another update.
When I said this only happened with many-to-many relationships that was a bit premature.
I have made a different test setup with only one, one-to-many relationship: parent => child.
When i do the same thing as described in the previous setup... trigger two separate, simultaneous scripts the connections also go wrong. (see picture child_single__one_to_many_relationship.png).

What I did is the following:
1. Create two custom post_types: parent, child.
2. Create a one-to-many relationship: parent => child.
3. Create two different parents (e.g. parent 1, parent 2).
4. Create two pages.
One with the following shortcode:

[lp_trigger_connect_one_too_many parent_id="{post_id_parent_1}"]

The other with

[lp_trigger_connect_one_too_many parent_id="{post_id_parent_2}"]

5. Add the following shortcode to functions.php or a plugin.

function lp_trigger_connect_one_too_many_sc($atts)
{

    $pairs = [
        'parent_id'    => 0,
    ];

    $atts = shortcode_atts(
        $pairs,
        $atts
    );

    if ($atts['parent_id'] == 0) {
        return 'Missing attribute: parent_id';
    }

    $parent = get_post((int)$atts['parent_id']);

    if (!$parent || $parent->post_type != 'parent') {
        return 'Invalid parent_id: post not found or not of post_type parent';
    }

    $current_user_id = get_current_user_id();

    $i = 1;
    while ($i <= 20) {
        $post_data = [
            'post_title'    => 'Parent: ' . $atts['parent_id'] . '; Index: ' . $i,
            'post_type'     => 'child',
            'post_author'   => $current_user_id,
            'post_status'  => 'publish'
        ];
        $new_child_id = wp_insert_post($post_data);

        if ($new_child_id && !is_wp_error($new_child_id)) {
            $toolset_connect = toolset_connect_posts('parent-child', $atts['parent_id'], $new_child_id);
            $response[] = $toolset_connect;
        }
        $i++;
    }

    print_r($response);
}

6. Visit both pages simultaneously in two different browser tabs.

I am are very curious if you can replicate this issue.
I have tested this on multiple servers with various php versions and different setups, as wel as on my local host (xampp).

Our clients are mostly schools... which are all starting again this week after the summer holidays.
So hopefully you can understand that we are very anxious to solve this issue 🙂

Thanks in advance.

Regards,
Tako

[Waqar]

Thank you for sharing these details.

I had to use some custom script to ensure that two separate instances of the shortcodes could be executed simultaneously, in two different browser tabs.

When I tested the code from your first message in two simultaneous sessions, I noticed that two instances of the same relationship were created for each child post.

On the edit screen of each automatically generated child type post, it showed 2 relationships with Father A post and 2 relationships with Mother A post.

To overcome this, I used a slightly updated approach in the code that worked:

add_shortcode('lp_trigger_connect_sc', 'lp_trigger_connect_sc');
function lp_trigger_connect_sc($atts)
{
 
    $pairs = [
        'post_type'    => 'father',
    ];
 
    $atts = shortcode_atts(
        $pairs,
        $atts
    );
 
    $query_args = [
        'post_type'     => $atts['post_type'],
        'post_status'   => 'publish',
        'nopaging'      => true,
    ];
 
    $query = new WP_Query($query_args);
    $new_posts = [];
 
    if ($query->posts) {
        foreach ($query->posts as $post) {
            $index = 1;
            while ($index <= 50) {
                $post_data = [
                    'post_title'    => $atts['post_type'] . ': ' . $post->ID . '- Index: ' . $index,
                    'post_type'     => 'child',
                    'post_author'   => get_current_user_id(),
                    'post_status'  => 'publish'
                ];
                $new_child_id = wp_insert_post($post_data);
 
                if ($new_child_id && !is_wp_error($new_child_id)) {
                    $new_posts[] = $new_child_id;
                }
                $index = $index + 1;
            }
        }
        foreach ($new_posts as $key => $value) {
        	$toolset_connect = toolset_connect_posts($atts['post_type'] . '-child', $post->ID, $value);
        }
    }
    print_r($new_posts);
}

You'll note that instead of creating the relationships in the same loop that generates new posts, I used the first loop to only create new posts and stored their IDs in a new array "new_posts".

Next, I added another for each loop and used the IDs of those newly generated posts to join them in the relationships.

In my tests, this code worked as expected even with the simultaneous sessions and you're welcome to test it and let me know how it goes.

[takoL]

Actually we had the same idea and we already tested separating the wp_insert_post loop and the toolset_connect_posts loop in our one-to-many test case. In our case however this did not solve the issue. Did you inspect multiple children that were made, because for some children the error does not occur.
I will test your code in the many-to-many relationship. But even if your suggestion to separate the loops would work (which it did not for us) this is still a potential risk.

It is important for me to indicate that due to this issue we had to take two clients of ours offline (since last Friday) and they are starting to get very anxious, as are we. In the meantime we are monitoring our other clients for irregularities in the database, and if we find any we will be forced to shut them down as well. There is a lot of time pressure involved for us, and you are from a different time zone and might be stopping for today. So would it be possible to add another support colleague on this issue so that we don't have to wait another day? We would really appreciate that.

Could you also try to replicate the issue in the one-to-many relationship.
This is the script we used for separating the loops:

function lp_trigger_connect_one_too_many_badge_sc($atts)
{

    if (is_admin()) {
        return;
    }

    $pairs = [
        'parent_id'    => 0,
    ];

    $atts = shortcode_atts(
        $pairs,
        $atts
    );

    if ($atts['parent_id'] == 0) {
        return 'Missing attribute: parent_id';
    }

    $parent = get_post((int)$atts['parent_id']);

    if (!$parent || $parent->post_type != 'parent') {
        return 'Invalid parent_id: post not found or not of post_type parent';
    }

    $current_user_id = get_current_user_id();
    $new_child_ids = [];

    $i = 1;
    while ($i <= 20) {
        $post_data = [
            'post_title'    => 'Parent: ' . $atts['parent_id'] . '; Index: ' . $i,
            'post_type'     => 'child',
            'post_author'   => $current_user_id,
            'post_status'  => 'publish'
        ];
        $new_child_id = wp_insert_post($post_data);

        if ($new_child_id && !is_wp_error($new_child_id)) {
            $new_child_ids[] = $new_child_id;
        }
        $i++;
    }

    if ($new_child_ids) {
        foreach ($new_child_ids as $child_id) {
            $toolset_connect = toolset_connect_posts('parent-child', $atts['parent_id'], $child_id);
            $response[] = $toolset_connect;
        }
    }

    print_r($response);
}

add_shortcode('lp_trigger_connect_one_too_many_badge', 'lp_trigger_connect_one_too_many_badge_sc');

Cheers and thanks

[takoL]

I forgot to ask in my last reply.
Since you can replicate the issue, did you notify the developer team of this issue?
Because this could have major consequences for people using toolset_reltionships.

[Waqar]

> Did you inspect multiple children that were made, because for some children the error does not occur.
- Yes, during testing with your and mine code, I checked the edit screen of each of those 100 created posts after every test and then shared the results, with you.

It is possible that your test website has some left-over code or the database has some abandoned relationship entries.

You're welcome to test code with my suggested approach again and I'll share our common findings with the concerned team. At the moment, the results of our tests are different so it is important to narrow down to that difference first.

I can understand the urgency and I've requested team members from different time zones to also join into this investigation.

[takoL]

Hi Waqar,

Thank you for the quick response and for requesting additional team members, we really appreciate it.
Also we really appreciate how you think along with us and suggest solutions. So cheers man.

In response to your remark:
>It is possible that your test website has some left-over code or the database has some abandoned relationship entries.
- I just tested your code on a new, clean website and the problem still occurred. See image.

One additional update:
We use InnoDB as the storage engine for mysql.
In one of our tests we switched the storage engine for the toolset_connected_elements and toolset_associations tables to MyISAM. This did not change anything.

[Waqar]

Thank you for waiting, while I performed some more tests.

For clarity and transparency, I'm going to share the detailed testing steps along with the online staging test website with you:

1. The staging website can be accessed at:
hidden link

2. For this test, I used your second shortcode for a one-to-many relationship and included it at Toolset -> Settings -> Custom Code:
(I added this shortcode but kept it deactivated in the beginning and will explain this point in later steps)

add_shortcode('lp_trigger_connect_one_too_many', 'lp_trigger_connect_one_too_many_sc');
function lp_trigger_connect_one_too_many_sc($atts)
{
 
    $pairs = [
        'parent_id'    => 0,
    ];
 
    $atts = shortcode_atts(
        $pairs,
        $atts
    );
 
    if ($atts['parent_id'] == 0) {
        return 'Missing attribute: parent_id';
    }
 
    $parent = get_post((int)$atts['parent_id']);
 
    if (!$parent || $parent->post_type != 'parent') {
        return 'Invalid parent_id: post not found or not of post_type parent';
    }
 
    $current_user_id = get_current_user_id();
 
    $i = 1;
    while ($i <= 20) {
        $post_data = [
            'post_title'    => 'Parent: ' . $atts['parent_id'] . '; Index: ' . $i,
            'post_type'     => 'child',
            'post_author'   => $current_user_id,
            'post_status'  => 'publish'
        ];
        $new_child_id = wp_insert_post($post_data);
 
        if ($new_child_id && !is_wp_error($new_child_id)) {
            $toolset_connect = toolset_connect_posts('parent-child', $atts['parent_id'], $new_child_id);
            $response[] = $toolset_connect;
        }
        $i++;
    }
 
    print_r($response);
}

3. As you mentioned, I added "Parent" and "Child" post types and joined them through a one-to-many relationship.

4. I added two posts in parent post type, Parent 1 and Parent 2:
hidden link

5. I created two pages to include the shortcode of these two parent posts.

hidden link
hidden link

Note: I made sure that after the pages have been created their edit screen is no longer open in any browser tab.

6. Because, I wanted to load these two pages simultaneously, using a custom script, I added a new content template "CT for Pages" and assigned it to all the "Pages" posts.

You'll see the custom script to re-load the pages at the same time, in the template "JS editor":
hidden link

Note: Please make sure that after the content template has been created its edit screen is no longer open in any browser tab.

7. Next, I loaded the two pages with the shortcodes in two separate browser tabs. As the shortcode was not active, the pages showed it as text and nothing was performed in the background, yet. But, the custom script's time to reload both the pages at exact same time started.

8. Now, I activated the custom shortcode added in Toolset -> Settings -> Custom Code, so that when the time completes and the pages reload, the shortcode can actually do its processing.

9. Once the timer is completed and both pages have reloaded, I deactivated the shortcode again, so that it can't be accidentally executed again.

After that, I checked the child posts and found exactly 40 child posts were created, and as expected 20 were connected to parent 1 and the other 20 were connected to parent 2.
hidden link

Coming back to the point of shortcode deactivation, the challenge with using the shortcode approach for troubleshooting/testing in the new Gutenberg/blocks editor is that it gets executed more than once in the background, when we add it or the editor content is saved. This is why I took extra care to only activate the custom shortcode, when the pages were due to refresh with the timer and deactivated it right after.

To see this behavior in action, here is a quick test. You can register a simple custom shortcode that writes the current time in the error log and also outputs it:

add_shortcode('show_current_time', 'show_current_time_func');
function show_current_time_func() {

	$time = "The time is " . date("h:i:sa");
	error_log( $time );

	return $time;

}

Create a new page and place this shortcode [show_current_time] on a new page with blocks editor through either the "Fields and Text" block or the "Shortcode" block. Save the page and without visiting it on the front-end, check the server's error log and you'll see the shortcode's time entries in the log. Reload or Re-Save the page in the back-end and those entries will keep adding up.

I suspect the discrepancies that you're seeing in your results are also due to these extra/undesired executions of the shortcode in the background.

You're welcome to review these points and perform the tests again following these precautions and let me know if this helps.

[takoL]

Hi Waqar,

Thanks for the reply. I will walk through your setup and try to see if I can explain the differences in our test cases based on it.
But in advance (so I don't lose too much time) I would like to reply to some statements that I think do not reflect our situation properly.

1. Gutenberg
You say: "Coming back to the point of shortcode deactivation, the challenge with using the shortcode approach for troubleshooting/testing in the new Gutenberg/blocks editor is that it gets executed more than once in the background, when we add it or the editor content is saved."

In reply: We tested this on sites where the Gutenberg editor was not activated. And the shortcode was not triggered in the admin.

2. Undesired executions of the shortcode in the background
You say: "I suspect the discrepancies that you're seeing in your results are also due to these extra/undesired executions of the shortcode in the background."

In reply: In our live server, the scripts that are causing the problems are not triggered by a shortcode (in the test cases this was purely for simplicity). In our live servers the scripts are triggered by submitting a form. For instance when a teacher uses a form to add participants (custom_post_type) to a project (custom_post_type). If another teacher, on another computer, tries to add participants to another project at the same time, the relationships get scrambled.

3. Replication
In an earlier reply you said: "When I tested the code from your first message in two simultaneous sessions, I noticed that two instances of the same relationship were created for each child post."

In reply: So it seems you could replicate this issue. However by separating the loops and altering the test cases you found a way to avoid the issue. Can you stille replicate the issue when not separating the loops and if so does that not concern you?

I will try to review your test case and update you as soon as possible.

Kind regards

[takoL]

Hello Waqar,

I looked at the sandbox and indeed I cannot replicate the issue there.

To rule out the shortcode trigger, I made a new test setup on my local host.
I did the following step:
1. Create a toolset-form that edits existing parents.
2. Create a content-template that displays single parent
3. Add the form to the content-template.
3. Add a cred_save_data hook that triggers the script.
When I simultaneously edit two different parents with this form, on the front-end, the problem occurs.

I also implemented this exact test scenario on the sandbox (I hope you don't mind) but there the issue does not occur.

My local host (and the live servers that we use) are multi-site networks. So than I thought maybe this could explain the difference.
However I also tested on a single site on my local host (clean install), and the problem occurs there as well.

The only thing I can think of right now is that my local host uses php 8... which is not recommended.
So, I will have to test this scenario on another server using php 7 as well.
Will let you know how that goes.

I hope you are still willing to think with us. I can imagine that this is becoming a little bit of a drag 🙂
Cheers!

[takoL]

Hi Waqar,

We have made a new online test environment where we are able to replicate the issue.
We posted the login information on a private page in your sandbox website (can't miss it).

The process to reproduce the issue is not straightforward: you need the correct timing and you really have to look for the posts where concurrency occurred and relationships were corrupted. To help you see how to replicate the issue we have recorded our test. You can find the video here:
hidden link

We have tried everything we can think of to explain/solve this issue, so we are basically out of ideas at the moment.
We really hope this is enough evidence to let the developers of relationships team take a look at this issue and we hope they would consider the probability of concurrency conflicts (in high load sites).

If it would help, we would gladly schedule a screen share session to share thoughts.

Regards