RUCSS - "database error table doesn't exist for queries" errors in the debug.log

vmanthos commented 2 years ago

Before submitting an issue please check that you’ve completed the following steps:

Made sure you’re on the latest version ✅ 3.12.1
Used the search feature to ensure that the bug hasn’t been reported before ✅

Describe the bug

Database table errors like the following have been popping up in the debug.log during QA:

WordPress database error Table 'vasilis_rocketlabsqa_ovh_NVniM4qB.wp_wpr_rucss_used_css' doesn't exist for query SELECT  wpr_rucss.id FROM wp_wpr_rucss_used_css wpr_rucss WHERE wpr_rucss.url = 'https://vasilis.rocketlabsqa.ovh/wpr_api_log/https-saas-wp-rocket-me-rucss-job-success-3364' ORDER BY wpr_rucss.id DESC LIMIT 100 made by shutdown_action_hook, do_action('shutdown'), WP_Hook->do_action, WP_Hook->apply_filters, wp_ob_end_flush_all, ob_end_flush, WP_Rocket\Engine\Optimization\Buffer\Optimization->maybe_process_buffer, apply_filters('rocket_buffer'), WP_Hook->apply_filters, WP_Rocket\Engine\Optimization\RUCSS\Frontend\Subscriber->treeshake, WP_Rocket\Engine\Optimization\RUCSS\Controller\UsedCSS->treeshake, WP_Rocket\Engine\Optimization\RUCSS\Frontend\APIClient->add_to_queue, WP_Rocket\Engine\Optimization\RUCSS\AbstractAPIClient->handle_post, WP_Rocket\Engine\Optimization\RUCSS\AbstractAPIClient->handle_request, wp_remote_request, WP_Http->request, do_action('http_api_debug'), WP_Hook->do_action, WP_Hook->apply_filters, WPR_Log_API_Main->log_api, wp_insert_post, clean_post_cache, do_action('clean_post_cache'), WP_Hook->do_action, WP_Hook->apply_filters, WP_Rocket\Engine\Optimization\RUCSS\Admin\Subscriber->delete_used_css_on_update_or_delete, WP_Rocket\Engine\Optimization\RUCSS\Controller\UsedCSS->delete_used_css, WP_Rocket\Engine\Optimization\RUCSS\Database\Queries\UsedCSS->get_rows_by_url, WP_Rocket\Dependencies\Database\Query->query, WP_Rocket\Dependencies\Database\Query->get_items, WP_Rocket\Dependencies\Database\Query->get_item_ids

or @Mai-Saad found this:

WordPress database error Table 'database.wp_wpr_rucss_used_css' doesn't exist for query SELECT * FROM wp_wpr_rucss_used_css WHERE id = 86 LIMIT 1 made by do_action('wp_ajax_as_async_request_queue_runner'), WP_Hook->do_action, WP_Hook->apply_filters, WP_Async_Request->maybe_handle, ActionScheduler_AsyncRequest_QueueRunner->handle, do_action('action_scheduler_run_queue'), WP_Hook->do_action, WP_Hook->apply_filters, ActionScheduler_QueueRunner->run, ActionScheduler_QueueRunner->do_batch, ActionScheduler_Abstract_QueueRunner->process_action, ActionScheduler_Action->execute, do_action_ref_array('rocket_rucss_job_check_status'), WP_Hook->do_action, WP_Hook->apply_filters, WP_Rocket\Engine\Optimization\RUCSS\Frontend\Subscriber->check_job_status, WP_Rocket\Engine\Optimization\RUCSS\Controller\UsedCSS->check_job_status, WP_Rocket\Engine\Optimization\RUCSS\Database\Queries\UsedCSS->increment_retries, WP_Rocket\Dependencies\Database\Query->update_item, WP_Rocket\Dependencies\Database\Query->get_item_raw

[16-May-2022 14:15:57 UTC] PHP Fatal error:  Uncaught Error: Class 'ActionScheduler_Lock' not found in /var/www/example.com/htdocs/wp-content/plugins/wp-rocket/inc/Dependencies/ActionScheduler/classes/abstracts/ActionScheduler.php:29
Stack trace:
#0 /var/www/example.com/htdocs/wp-content/plugins/wp-rocket/inc/Engine/Common/Queue/RUCSSQueueRunner.php(144): ActionScheduler::lock()
#1 /var/www/example.com/htdocs/wp-includes/class-wp-hook.php(307): WP_Rocket\Engine\Common\Queue\RUCSSQueueRunner->maybe_dispatch_async_request('')
#2 /var/www/example.com/htdocs/wp-includes/class-wp-hook.php(331): WP_Hook->apply_filters(NULL, Array)
#3 /var/www/example.com/htdocs/wp-includes/plugin.php(474): WP_Hook->do_action(Array)
#4 /var/www/example.com/htdocs/wp-includes/load.php(1100): do_action('shutdown')
#5 [internal function]: shutdown_action_hook()
#6 {main}
  thrown in /var/www/example.com/htdocs/wp-content/plugins/wp-rocket/inc/Dependencies/ActionScheduler/classes/abstracts/ActionScheduler.php on line 29

To Reproduce Steps to reproduce the behavior:

While preload is enabled clear the used CSS.
Delete the wpr_remove_unused_css table.
See the error in the log.

Expected behavior

We should prevent such errors from taking place. According to @Tabrisrp:

We could check if the table exists before doing a request to it.

Additional context

Slack conversation: https://wp-media.slack.com/archives/GUT7FLHF1/p1652717738651909

Backlog Grooming (for WP Media dev team use only)

[x] Reproduce the problem
[x] Identify the root cause
[x] Scope a solution
[x] Estimate the effort

jeawhanlee commented 1 year ago

Reproduce the problem ✅

I was able to reproduce the problem

Identify the root cause ✅

We are not checking the status of the table before making requests to it.

Scope a solution ✅

Like @Tabrisrp stated we need to do a check for every request we send to the rucss table. we can add a method in WP_Rocket\Engine\Optimization\RUCSS\Database\Queries like this

Private function table_exists() {

        // Get the database interface
        $db = $this->get_db();

        // Bail if no database interface is available
        if ( empty( $db ) ) {
            return false;
        }

        // Query statement
        $query    = "SHOW TABLES LIKE %s";
        $like     = $db->esc_like( $this->table_name );
        $prepared = $db->prepare( $query, $like );
        $result   = $db->get_var( $prepared );

        // Does the table exist?
        return $this->is_success( $result );
    }

then we can either use this to do checks on every method that sends request to the table in WP_Rocket\Engine\Optimization\RUCSS\Database\Queries or do the checks on WP_Rocket\Engine\Optimization\RUCSS\Controller\UsedCSS

Might need to update tests also.

Estimate the effort ✅ [S]

CrochetFeve0251 commented 1 year ago

Reproduce the problem white_check_mark

I was able to reproduce the problem

Identify the root cause white_check_mark

We are not checking the status of the table before making requests to it.

Scope a solution white_check_mark

Like @Tabrisrp stated we need to do a check for every request we send to the rucss table. we can add a method in WP_Rocket\Engine\Optimization\RUCSS\Database\Queries like this
Private function table_exists() {

      // Get the database interface
      $db = $this->get_db();

      // Bail if no database interface is available
      if ( empty( $db ) ) {
          return false;
      }

      // Query statement
      $query    = "SHOW TABLES LIKE %s";
      $like     = $db->esc_like( $this->table_name );
      $prepared = $db->prepare( $query, $like );
      $result   = $db->get_var( $prepared );

      // Does the table exist?
      return $this->is_success( $result );
  }
then we can either use this to do checks on every method that sends request to the table in WP_Rocket\Engine\Optimization\RUCSS\Database\Queries or do the checks on WP_Rocket\Engine\Optimization\RUCSS\Controller\UsedCSS

Might need to update tests also.

Estimate the effort white_check_mark [S]

@jeawhanlee can we check only on the first time we send a query? Normally the table as no reason to disappear for following queries and that would prevent from doubling every single request we do to the DB.

wp-media / wp-rocket