14.9 Breaks Site (WP DB Memory Error) if WP Author has Too Many Posts/Pages

JMHendon commented 4 years ago

[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.

Note: This has been verified by the Yoast Tech Support Team and reproduced in a testing environment.

Version 14.9 of Yoast SEO plugin triggers a WP Database Error - triggered by exhausting server resources. This happens when a certain # of Posts/Pages - above some threshold - are attributed to a single WP author. In our case, one author has about 800 posts.

Yoast tech team verified that splitting posts among multiple users does not trigger error. Also, Version 14.8.1 and all earlier versions do not trigger this bug.

Please describe what you expected to happen and why.

See above.

How can we reproduce this behavior?

Install version 14.9 of Yoast SEO
Publish a lot of posts/pages (pages in our case) to a single author. Note - we're not certain of the exact threshold - 300 does not trigger the bug, but 800 does.

Technical info

If relevant, which editor is affected (or editors):
[ ] Classic Editor
[ ] Gutenberg
[ ] Classic Editor plugin

Which browser is affected (or browsers):
[x] Chrome
[x] Firefox
[x] Safari
[ ] Other

Used versions

WordPress version: 5.5.1
Yoast SEO version: 14.9
Gutenberg plugin version:
Classic Editor plugin version:
Relevant plugins in case of a bug: All other plugins deactivated
Tested with theme: Default

Djennez commented 4 years ago

@JMHendon what is the error, what steps do I need to take to reproduce the issue, exactly?

JMHendon commented 4 years ago

Hi,

Here’s more detail:

Specific server error:

2020/09/01 22:07:22 [error] 74469#74469: 13865287 FastCGI sent in stderr: "PHP message: WordPress database error Commands out of sync; you can't run this command now for query SELECT umeta_id FROM wp_usermeta WHERE meta_key = 'wp_yoast_notifications' AND user_id = 1 made by shutdown_action_hook, do_action('shutdown'), WP_Hook->do_action, WP_Hook->apply_filters, Yoast_Notification_Center->update_storage, array_walk, Yoast_Notification_Center->store_notifications_for_user, update_user_option, update_user_meta, update_metadata PHP message: WordPress database error Commands out of sync; you can't run this command now for query SHOW FULL COLUMNS FROM wp_usermeta made by shutdown_action_hook, do_action('shutdown'), WP_Hook->do_action, WP_Hook->apply_filters, Yoast_Notification_Center->update_storage, array_walk, Yoast_Notification_Center->store_notifications_for_user, update_user_option, update_user_meta, update_metadata, add_metadata PHP message: WordPress database error Commands out of sync; you can't run this command now for query SELECT , CASE WHEN type = 3 THEN 0 WHEN type = 4 THEN 1 WHEN type = 7 THEN 2 WHEN type = 6 THEN 3 WHEN type = 5 THEN 4 WHEN type = 9 THEN 5 WHEN type = 8 THEN 6 WHEN type = 2 THEN 7 WHEN type = 1 THEN 8 ELSE 9999 END AS typeSort, CASE WHEN type = 3 THEN parameters WHEN type = 4 THEN parameters WHEN type = 1 THEN IP WHEN type = 9 THEN IP WHEN type = 5 THEN IP WHEN type = 6 THEN IP WHEN type = 7 THEN IP WHEN type = 2 THEN IP WHEN type = 8 THEN IP ELSE 9999 END AS detailSort FROM wp_wfblocks7 WHERE type IN (4) AND (expiration = 0 OR expiration > UNIX_TIMESTAMP()) ORDER BY typeSort ASC, id DESC made by wfWAFIPBlocksController::synchronizeConfigSettings, wfBlock::patternBlocks, wfBlock::allBlocks PHP message: WordPress database error Commands out of sync; you can't run this command now for query SELECT *, CASE WHEN type = 3 THEN 0 WHEN type = 4 THEN 1 WHEN type = 7 THEN 2 WHEN type = 6 THEN 3 WHEN type = 5

How error shows presents itself in Wordpress:

When navigating to Pages in the WP backend, you’ll see this error notification:

“There has been a critical error on your website. Please check your site admin email for instructions.”

To reproduce:

I wasn’t being tongue-in-cheek with my initial instructions. Here’s how to definitively reproduce:

a. Activate Yoast SEO v14.9 b. Have over 800 pages attributed to a single WP author. (There’s some lower threshold, but I don’t know what it is.) c. Navigate to Pages in the WP backend.

On Sep 9, 2020, at 1:50 AM, Djennez notifications@github.com wrote:

@JMHendon https://github.com/JMHendon what is the error, what steps do I need to take to reproduce the issue, exactly?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Yoast/wordpress-seo/issues/16027#issuecomment-689424141, or unsubscribe https://github.com/notifications/unsubscribe-auth/AE7RMYTOO3NP55M7WDEYZI3SE46WJANCNFSM4RAXSUGA.

Djennez commented 4 years ago

Ok, so I am unable to reproduce this. And from your errors, it looks like WordFence is sort of involved? So you might want to first try a conflict check to see if / which plugins are needed to reproduce this problem. It's not workable at the moment.

JMHendon commented 4 years ago

The error happens with no other plugins activated. We’ve tested it many times, including with the health check plugin.

If you can, you may want to check with the Yoast tech support team. They reproduced it on their end.

Or - if you give me a way to contact you, I can give you credentials for a staging site that reproduces the bug.

On Sep 10, 2020, at 00:53, Djennez notifications@github.com wrote: Ok, so I am unable to reproduce this. And from your errors, it looks like WordFence is sort of involved? So you might want to first try a conflict check to see if / which plugins are needed to reproduce this problem. It's not workable at the moment.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

Djennez commented 4 years ago

We have an internal issue (for developer reference) which could potentially be the same. It might be related to multiple posts with large amounts of metadata associated (so just a large amount of posts doesn't reproduce the error, my site is working fine with 2000+ pages). We're investigating it further.

JMHendon commented 4 years ago

Interesting. I wouldn’t have thought that pages on our sites have high levels of metadata on average, but perhaps.

Thanks

Jeremy

On Sep 10, 2020, at 04:29, Djennez notifications@github.com wrote:

We have an internal issue (for developer reference) which could potentially be the same. It might be related to multiple posts with large amounts of metadata associated (so just a large amount of posts doesn't reproduce the error, my site is working fine with 2000+ pages). We're investigating it further.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

Djennez commented 4 years ago

Adding the internal labels to this issue as a public-facing thread.

Overflow992 commented 4 years ago

Same problem !!

I have a website with more 1000+ post/pages, when i try to open the page https://mydomain.com/wp-admin/edit.php?post_type=page not load the list of pages and return the error "There has been a critical error on your website. Please check your site admin email inbox for instructions."

when go to see the error log i see:

PHP Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 667648 bytes)

and

PHP message: WordPress database error Commands out of sync; you can't run this command now for query SELECT umeta_id FROM wp_usermeta WHERE meta_key = 'wp_yoast_notifications' AND user_id = 1 made by shutdown_action_hook, do_action('shutdown'), WP_Hook->do_action, WP_Hook->apply_filters, Yoast_Notification_Center->update_storage, array_walk, Yoast_Notification_Center->store_notifications_for_user, update_user_option, update_user_meta, update_metadataPHP

I can downgrade the plugin to 1.4.7 from latest version or occurs some problematic ?

Solve ASAP

jamesbeninger commented 4 years ago

I have a similar problem, and I'm afraid I couldn't reproduce it on a separate server. It affects our staging server but not my personal dev server. I'll list the differences below in case it helps track the problem.

In my case, it's affecting the "List Posts" page for a custom post type with 1300 entries and lots of metadata. They're all by the same author as well.

Staging Server (has the problem) PHP: 7.4.10 WP: 5.5.1 Memory Limit: 256M

Dev Server (cannot reproduce) PHP: 7.0.30 WP: 4.9.8 Memory Limit: 128M

The dev server uses the same database, plugins, and theme.

Downgrading to Yoast 14.8 solved the problem.

Djennez commented 4 years ago

The problem is pretty inconsistent for me. It seems to only be happening on hierarchical posttype overview pages and only if our indexation has not been ran completely. But I guess that, even if the indexation is complete, there might be edge-cases where this can happen. Still investigating.

kjtolsma commented 4 years ago

Same problem here with Yoast SEO 14.9. It works all fine in version 14.8.

9000 pages with a lot of meta data PHP: 7.4.9 WP: 5.5.1 Memory Limit: 256M Plugins: Only Yoast SEO Theme: Twenty Twenty

If i bump up the memory limit to 1024 MB it works again.

Steeveuk commented 4 years ago

Hi all,

Exactly same issue. I managed to fix it by editing the following file: /wp-content/plugins/wordpress-seo/src/integrations/admin/admin-columns-cache-integration.php - fill_cache function

I simply added:

This fixed the issue for that particular post type. The post type in question has 20,420 entries. I discovered that the fill_cache function was loading in all POST IDs using query monitor on that post page after I commented out the \_prime_post_caches( $post_ids ); and added into functions.php:

add_filter( 'manage_edit-post_columns', 'yoast_seo_admin_remove_columns', 10, 1 );
add_filter( 'manage_edit-page_columns', 'yoast_seo_admin_remove_columns', 10, 1 );

function yoast_seo_admin_remove_columns( $columns ) {
  unset($columns['wpseo-score']);
  unset($columns['wpseo-score-readability']);
  unset($columns['wpseo-title']);
  unset($columns['wpseo-metadesc']);
  unset($columns['wpseo-focuskw']);
  unset($columns['wpseo-links']);
  unset($columns['wpseo-linked']);
  return $columns;
}

Here we can see a screenshot of what it is trying to do and the amount visible within the scroll window is about 1/100th of the amount of results (the scroll continues all the way to the bottom - as it says it is selecting 20412 rows):

Here is my PHP error: Got error 'PHP message: WordPress database error Commands out of sync; you can't run this command now for query SELECT umeta_id FROM wp_usermeta WHERE meta_key = 'wp_yoast_notifications' AND user_id = 19 made by shutdown_action_hook, do_action('shutdown'), WP_Hook->do_action, WP_Hook->apply_filters, Yoast_Notification_Center->update_storage, array_walk, Yoast_Notification_Center->store_notifications_for_user, update_user_option, update_user_meta, update_metadata, W3TC\\DbCache_WpdbNew->query, W3TC\\DbCache_WpdbInjection->query, W3TC\\DbCache_WpdbNew->default_query\nPHP message: WordPress database error Commands out of sync; you can't run this command now for query SHOW FULL COLUMNS FROMwp_usermetamade by shutdown_action_hook, do_action('shutdown'), WP_Hook->do_action, WP_Hook->apply_filters, Yoast_Notification_Center->update_storage, array_walk, Yoast_Notification_Center->store_notifications_for_user, update_user_option, update_user_meta, update_metadata, add_metadata, W3TC\\DbCache_WpdbNew->insert, W3TC\\DbCache_WpdbInjection->insert, W3TC\\DbCache_WpdbNew->default_insert, W3TC\\DbCache_WpdbNew->query, W3TC\\DbCache_WpdbInjection->query, W3TC\\DbCache_WpdbNew->default_query

Hope this helps.

Cheers, Steve

Steeveuk commented 4 years ago

Also I realised I forgot to mention: the reason the fatal error seems to be happening for certain websites and not others is due to the PHP memory limitations from what I can tell.

Another PHP error I got was: PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 20480 bytes) in \wp-includes\wp-db.php on line 1999

This coincides with Overflow992's comment further up and also is consistent with the general issues being described here.

Thanks, Steve

Djennez commented 4 years ago

The main issue is that we don't limit this caching process, and that we execute it on the post ID's in the main query. On hierarchical posttypes (like pages), all posts are retrieved and not limited (like with normal posts). If all those posts have not been indexed by our indexing process yet, that adds load as well. We are looking into a way to limit the amount of post ID's that we want to cache.

SimeonUnder2 commented 4 years ago

+1

We're having this issue too on a site with 35k posts of one type spread across roughly 150 Authors.

Downgrading to 14.8.* fixed it.

JMHendon commented 3 years ago

It's now broken multiple of our sites. Downgrading always fixes it.

fritexvz commented 3 years ago

Simmilar here: Server with 16GB DDR4, 4 CPU, 1Gbps, local SSD.

WordPress 5.5.3 with 30.000 posts Nginx 1.19.4 Yoast SEO Premium 15.2, News, Local, Video PHP-fpm 7.3 with OPCache Memcached (database cache), Redis (object cache) W3 Total Cache Page Cache Disk Enhanced Website behind CloudFlare Pro Plan (paid)

Result: CPU goes up to 400% for a minute and server load up to 6-7, with a lot of ajax requests when I open console/network manager on my Web browser (Chrome or Mozilla)

I have disabled all features and integrations except XML sitemap.

Yoast / wordpress-seo