DB error: "Column 'ancestor_id' cannot be null" when setting unpublished post_parent on non-production env.

[adamcapriola]

• [X] I've read and understood the contribution guidelines.
• [X] I've searched for any related issues and avoided creating a duplicate issue.

Please give us a description of what happened.

I am creating new posts programmatically as drafts and attaching images to them (meaning I am updating the post_parent for existing images as the new posts, which are drafts). On my development environment, I get this error in my debug.log:

[24-Aug-2021 17:45:38 UTC] WordPress database error Column 'ancestor_id' cannot be null for query INSERT INTO wp_yoast_indexable_hierarchy (indexable_id, ancestor_id, depth, blog_id) VALUES ('33', NULL, '1', '1') made by include('phar:///usr/local/bin/wp/php/boot-phar.php'), include('phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/wp-cli.php'), WP_CLI\bootstrap, WP_CLI\Bootstrap\LaunchRunner->process, WP_CLI\Runner->start, WP_CLI\Runner->run_command_and_exit, WP_CLI\Runner->run_command, WP_CLI\Dispatcher\Subcommand->invoke, call_user_func, WP_CLI\Dispatcher\CommandFactory::WP_CLI\Dispatcher{closure}, call_user_func, Post_Command->update, WP_CLI\CommandWithDBObject->_update, Post_Command->{closure}, wp_update_post, wp_insert_post, do_action('wp_insert_post'), WP_Hook->do_action, WP_Hook->apply_filters, Yoast\WP\SEO\Integrations\Watchers\Indexable_Post_Watcher->build_indexable, Yoast\WP\SEO\Builders\Indexable_Builder->build_for_id_and_type, Yoast\WP\SEO\Builders\Indexable_Hierarchy_Builder->build, Yoast\WP\SEO\Builders\Indexable_Hierarchy_Builder->save_ancestors, Yoast\WP\SEO\Repositories\Indexable_Hierarchy_Repository->add_ancestor, Yoast\WP\Lib\Model->save, Yoast\WP\Lib\ORM->save, Yoast\WP\Lib\ORM::execute

Please describe what you expected to happen and why.

I did not expect an error. This does not occur on my production site and it also does not happen if the new posts are post_status=publish (rather than draft, or pending).

How can we reproduce this behavior?

1. Create a fresh WordPress install

2. Add to /wp-config.php: define( 'WP_ENVIRONMENT_TYPE', 'local' ); (development and staging also cause the error)

3. Install & enable Yoast SEO (no configuration necessary)

4. CLI: wp post update $(wp post create --post_type=page --post_title=publish --post_status=publish --porcelain) --post_parent=$(wp post create --post_type=page --post_title=draft --post_status=draft --porcelain)

   This command creates (1) a draft page and (2) a published page and sets the published page's parent as the draft page. (Which is functionally identical to what I was doing w/ the draft posts and attaching images.)

Technical info

• If relevant, which editor is affected (or editors):

• [ ] Classic Editor
• [ ] Gutenberg
• [ ] Classic Editor plugin

(Editor not relevant.)

• Which browser is affected (or browsers):

• [ ] Chrome
• [ ] Firefox
• [ ] Safari
• [X] Other (Edge)

(Browser not relevant.)

Used versions

• WordPress version: 5.8
• Yoast SEO version: 17.0
• Relevant plugins in case of a bug: No other plugins active besides Yoast SEO.
• Tested with theme: Twenty Twenty-One

[Djennez]

Hi @adamcapriola , thanks for the detailed report en reproduction steps. We were able to verify the problem.

On non-production sites, the indexables should not get created. I think the indexable hierarchy is still initiated and tries to create ancestry for non-existing objects.

[alexholsgrove]

We're experiencing the same issue on a site on our staging environment define('WP_ENVIRONMENT_TYPE', 'staging'); which may have been caused by taking a copy of the production database onto the staging server.

Is there a WP-CLI command to repair or remove the indexables (based on your comment that "On non-production sites, the indexables should not get created"), or instead, would we be able to empty the wp_yoast_indexable and wp_yoast_indexable_hierarchy tables to resolve the issue?

[alexholsgrove]

Can we have an update on this please? Will running the CLI command wp yoast index --reindex work to clear the indexes (and not create new ones if the environment type is set to staging)?

[alexholsgrove]

@Djennez are you able to give me an update please?

[margarita-boomCodes]

It would be great, if this could be solved. I have to run every couple of hours wp yoast index --reindex to suppress the error.

[Djennez]

Since I am not actively working on these projects anymore, I will ping @iamazik to keep an eye on this.

[alexholsgrove]

Thanks @Djennez, much appreciated

[mmikhan]

Thanks for your continued patience @alexholsgrove. I gave it a shot and couldn't reproduce the error anymore. As far as I understood, the error occurs due to the SEO data optimization, but it's disabled when the environment type is set to local. So, if anybody can share a bit more clear guidance on how to reproduce the issue, that would be much better to prioritize the issue.

[margarita-boomCodes]

@iamazik I think it's CPT-related.

[mmikhan]

I don't think so since the initial description shows the step with Pages

[margarita-boomCodes]

@iamazik i get the error on a CPT with page-attributes support. Maybe this is the link to the pages?

[mmikhan]

Can you please share the exact steps that I could follow to reproduce the error on my end before I could confirm anything? @margarita-boomCodes

[margarita-boomCodes]

Can you please share the exact steps that I could follow to reproduce the error on my end before I could confirm anything? @margarita-boomCodes

After I mentioned the page-attributes support I went on the parent CPT-page, just clicked on save and the error was gone. This is good news, but I cannot reproduce the error anymore, even after I tried later different child-parent cases.

So... in case the error comes again, I'll try to investigate more. Thanks for your readiness by the way!

[mmikhan]

Since I can no longer reproduce the issue, I will go ahead and close it. However, if it comes to any one of you and is able to reproduce it, please do feel free to share the reproduction guideline and ping me here.