WordPress database error MySQL server has gone away

  • Resolved mattgivertz

    (@mattgivertz)


    3 months, 1 week ago

    Hi

    We have been experiencing some issues and when I reviewed our php error logs I have seen a few entries suggesting that a long running query has timed out. The error we see is;

    WordPress database error MySQL server has gone away for query #012#011#011#011SELECT P.ID, P.post_content#012#011#011#011FROM wp_posts AS P#012#011#011#011LEFT JOIN wp_yoast_indexable AS I#012#011#011#011#011ON P.ID = I.object_id#012#011#011#011#011AND I.link_count IS NOT NULL#012#011#011#011#011AND I.object_type = ‘post’#012#011#011#011LEFT JOIN wp_yoast_seo_links AS L#012#011#011#011#011ON L.post_id = P.ID#012#011#011#011#011AND L.target_indexable_id IS NULL#012#011#011#011#011AND L.type = ‘internal’#012#011#011#011#011AND L.target_post_id IS NOT NULL#012#011#011#011#011AND L.target_post_id != 0#012#011#011#011WHERE ( I.object_id IS NULL OR L.post_id IS NOT NULL )#012#011#011#011#011AND P.post_status = ‘publish’#012#011#011#011#011AND P.post_type IN (‘post’, ‘page’, ‘testimonial’, ‘product’, ‘wpb_gutenberg_param’, ‘sfs-footer’, ‘wppcp_group’, ‘wppcp_fproduct_tabs’)#012#011#011#011LIMIT 3 /* From [bags-of-books.co.uoast-yk/wp-cron.php?doing_wp_cron=1771605154.8753519058227539062500] in [/nas/content/live/bagsofbomaster/wp-content/plugins/wordpress-seo/src/actions/indexing/post-link-indexing-action.php:56] */ made by do_action_ref_array(‘wpseo_indexable_index_batch’), WP_Hook->do_action, WP_Hook->apply_filters, Yoast\WP\SEO\Integrations\Admin\Background_Indexing_Integration->index, Yoast\WP\SEO\Actions\Indexing\Abstract_Link_Indexing_Action->index, Yoast\WP\SEO\Actions\Indexing\Post_Link_Indexing_Action->get_objects

    I have no idea how to resolve this. Normally I’d be able to establish what the actual query is and to make sure the appropriate indexes are in place and if they are that they are used in execution of the query. I have verified that all yoast tables have indexes and optimised the tables. The tables are all of the type InnoDB. Other than wp_yoast_prominent_words and wp_yoast_seo_links which have a collation of utf8mb3_general_ci the other tables have a collation of utf8mb4_unicode_520_ci.

    Any suggestions how to resolve this error would be appreciated.

    Kind regards
    Matt

    The page I need help with: [log in to see the link]

Viewing 7 replies - 1 through 7 (of 7 total)
  • Thread Starter mattgivertz

    (@mattgivertz)

    3 months, 1 week ago

    Hi

    As I haven’t heard anything, I have been through the knowledge base and decided to reset the indexables and then start seo optimisation. The progress bar moves along quite happily and I don’t seem to get an error however I see the following in or php error logs;

    WordPress database error Unknown column ‘I.version’ in ‘on clause’ for query #012#011#011#011SELECT COUNT(term_id)#012#011#011#011FROM wp_term_taxonomy AS T#012#011#011#011LEFT JOIN wp_yoast_indexable AS I#012#011#011#011#011ON T.term_id = I.object_id#012#011#011#011#011AND I.object_type = ‘term’#012#011#011#011#011AND I.version = 2#012#011#011#011WHERE I.object_id IS NULL#012#011#011#011#011AND taxonomy IN (‘category’, ‘post_tag’, ‘product_brand’, ‘product_cat’, ‘product_tag’, ‘product_shipping_class’, ‘pa_1’, ‘pa_authors’, ‘pa_binding’, ‘pa_board-book’, ‘pa_hardback’, ‘pa_paperback’, ‘pa_series’, ‘giftcard-category’) /* From [bags-of-books.co.uk/wp-admin/tools.php?page=yoast-test-helper] in [/nas/content/live/bagsofbomaster/wp-content/plugins/wordpress-seo/src/actions/indexing/abstract-indexing-action.php:86] */ made by require_once(‘wp-admin/admin.php’), do_action(‘admin_init’), WP_Hook->do_action, WP_Hook->apply_filters, Yoast\WP\SEO\Integrations\Admin\Indexing_Notification_Integration->maybe_create_notification, Yoast\WP\SEO\Integrations\Admin\Indexing_Notification_Integration->notification, Yoast\WP\SEO\Integrations\Admin\Indexing_Notification_Integration->get_presenter, Yoast\WP\SEO\Helpers\Indexing_Helper->get_filtered_unindexed_count, Yoast\WP\SEO\Helpers\Indexing_Helper->get_unindexed_count, Yoast\WP\SEO\Actions\Indexing\Abstract_Indexing_Action->get_total_unindexed

    I’ve used myphpadmin to look at the structure of wp_yoast_indexable and there is a column version o type int so I don’t understand this error.

    We are using the free plugin and I can’t seem to ind someone at yoast to contact. Help is really needed here

    Regards
    Matt

    Plugin Support Maybellyne

    (@maybellyne)

    3 months ago

    Hello @mattgivertz,

    Thanks for reaching out about the WordPress database error. Did the SEO data optimization complete successfully?

    Thread Starter mattgivertz

    (@mattgivertz)

    3 months ago

    Unfortunately not. I added some exclusion filters and have tried again on our staging site so there are no users and only this running. It has been running for 8 hours and still hasn’t completed 🙁

    Once it has I’ll have a look at the logs to see if there’s any my sql errors

    Regards

    Plugin Support Maybellyne

    (@maybellyne)

    3 months ago

    Thanks for letting me know. 8 hours is a long time for it to run. Also, it is not advisable to do so on a staging site to prevent temporary data from being saved to the WordPress database, especially if the staging site is pushed to production.

    Do you have a large site? If so,  I strongly recommend running the SEO data optimisation via WP-CLI commands after resetting the three buttons. This requires access to your server through a terminal service. Your web host may be able to help. If you have a large site, we highly recommend this method.

    Once the command-line process is complete, the third process for prominent word calculation should be performed in the WordPress interface.

    Thread Starter mattgivertz

    (@mattgivertz)

    3 months ago

    Hi

    I tried your suggestion to do the optimisation via the wp cli and it has failed again. I still see the following in the error logs


    WordPress database error Unknown column ‘I.version’ in ‘on clause’ for query #012#011#011#011SELECT COUNT(term_id)#012#011#011#011FROM wp_term_taxonomy AS T#012#011#011#011LEFT JOIN wp_yoast_indexable AS I#012#011#011#011#011ON T.term_id = I.object_id#012#011#011#011#011AND I.object_type = ‘term’#012#011#011#011#011AND I.version = 2#012#011#011#011WHERE I.object_id IS NULL#012#011#011#011#011AND taxonomy IN (‘category’, ‘post_tag’, ‘product_brand’, ‘product_cat’, ‘product_tag’, ‘product_shipping_class’, ‘pa_1’, ‘pa_authors’, ‘pa_binding’, ‘pa_board-book’, ‘pa_hardback’, ‘pa_paperback’, ‘pa_series’, ‘giftcard-category’) /* From [bagsofbo464stg.wpengine.com/wp-admin/tools.php?page=yoast-test-helper] in [/nas/content/live/bagsofbo464stg/wp-content/plugins/wordpress-seo/src/actions/indexing/abstract-indexing-action.php:86] */ made by require_once(‘wp-admin/admin.php’), do_action(‘admin_init’), WP_Hook->do_action, WP_Hook->apply_filters, Yoast\WP\SEO\Integrations\Admin\Indexing_Notification_Integration->maybe_create_notification, Yoast\WP\SEO\Integrations\Admin\Indexing_Notification_Integration->notification, Yoast\WP\SEO\Integrations\Admin\Indexing_Notification_Integration->get_presenter, Yoast\WP\SEO\Helpers\Indexing_Helper->get_filtered_unindexed_count, Yoast\WP\SEO\Helpers\Indexing_Helper->get_unindexed_count, Yoast\WP\SEO\Actions\Indexing\Abstract_Indexing_Action->get_total_unindexed

    I’m also a bit concerned as I followed the instructions in your documentation to exclude certain taxonomy terms (see below) and yet I see them in the above query.

    add_filter( ‘wpseo_indexable_excluded_taxonomies’, ‘do_not_create_term_indexables_for_wpseo’ );
    function do_not_create_term_indexables_for_wpseo( $excluded_taxonomies ) {
    // setup array of taxonomy terms to exclude
    $exclude_these_array =array(‘post_tag’,’product_tag’,’pa_binding’,
    ‘pa_paperback’,’pa_hardback’,’pa_boardbook’,’product_shipping_class’,
    ‘link_category’,’gift_card_category’,’pa_1′,’product_type’);
    $exclude=”;
    // loop through the array and add the value to the exclusion array
    foreach ($exclude_these_array as $exlude) {
    // add the excluusion array to yoast array
    $excluded_post_types[] = $exclude;
    }
    return $excluded_taxonomies;
    }

    I don’t see any errors how do I check the exclusions are being applied.

    As a desperation measure I want to delete the plugin and all the data and tables associated with it and start again to see i the problem simply goes away. what’s the best way to do this. I want to start with just the basic Yoast seo and go from there.

    getting pretty desperate now although as it’s in staging there’s no risk to our production (live) environment

    Regards
    Matt

    Thread Starter mattgivertz

    (@mattgivertz)

    3 months ago

    Hi

    I’ve been looking at this in a little more detail and with the help of our hosting provider we have isolated one query (there maybe more) that have an excessive execution time. The following query took over 65 seconds to return the count (11307) 🙁

    SELECT COUNT(P.ID) FROM wp_posts AS P LEFT JOIN wp_yoast_indexable AS I ON P.ID = I.object_id AND I.link_count IS NOT NULL AND I.object_type = ‘post’ LEFT JOIN wp_yoast_seo_links AS L ON L.post_id = P.ID AND L.target_indexable_id IS NULL AND L.type = ‘internal’ AND L.target_post_id IS NOT NULL AND L.target_post_id != 0 WHERE ( I.object_id IS NULL OR L.post_id IS NOT NULL ) AND P.post_status = ‘publish’ AND P.post_type IN (‘post’, ‘page’, ‘testimonial’, ‘product’, ‘country_restriction’, ‘wpb_gutenberg_param’, ‘sfs-footer’, ‘wppcp_group’, ‘wppcp_fproduct_tabs’)

    The explain plan for the query is

    *** row 1 ***
    table: P
    type: range
    possible_keys: type_status_date,type_status_author
    key: type_status_author
    key_len: 124
    ref: NULL
    rows: 21617
    Extra: Using where; Using index
    *** row 2 ***
    table: I
    type: ref
    possible_keys: object_type_and_sub_type,object_id_and_type
    key: object_id_and_type
    key_len: 139
    ref: wp_bagsofbomaster.P.ID,const
    rows: 1
    Extra: Using where
    *** row 3 ***
    table: L
    type: ref
    possible_keys: link_direction,target_indexable_id_index
    key: target_indexable_id_index
    key_len: 5
    ref: const
    rows: 4
    Extra: Using where

    It seems to me that this query needs to be looked at as again there were post types included in the query that I added to an exclusion filter (e.g. ‘testimonial’, ‘product’, ‘country_restriction’, ;wpb_gutenberg_param’, ‘sfs-footer’, ‘wppcp_group’, ‘wppcp_fproduct_tabs) what is this query actually trying to determine?

    I have determined that it’s the product post type that is adding the time as without it I get a count of 10 and execution time is 0.4 seconds. We have over 19,000 products and we don’t add seo meta data to these as it’s impractical so I have tried to exclude post type product but as for the taxonomies it would seem the exclusions aren’t being filtered out. I created a unction which uses a foreach statement to add a value to the exclusion array as shown below. I think my code is OK but it’s possible it’s not working so any help welcome
    foreach ($exclude_these_array as $exlude) {
    // add the exclusion array value to yoast array
    $excluded_post_types[] = $exclude;
    }

    Regards
    Matt

    Thread Starter mattgivertz

    (@mattgivertz)

    3 months ago

    HI

    I have now implemented both post type and texonomy exclusions and SEO optimisation completes in around 30 minutes. It’s clear that including the post type product was causing the query to take an excessive length of time. We are a bookshop and so having a large number of products is not out of the ordinary. As far as seo goes we wouldn’t add seo data to individual records as it would be impractical for 19,000 products. I’m marking this as resolved as we no longer have an issue by excluding products and other unnecessary post types and taxonoomies from being indexed.

    Regards
    Matt

Viewing 7 replies - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.