DB Error no such table civicrm_queue

Question

When opening extension manager I get a message that a database-upgrade is required for the extensions (Voor uw extensies is een database-upgrade nodig. Upgrade uitvoeren.) Running the upgrade results in an error: DB Error : no such table Suggestion was to tun the civicrm/upgrade?reset=1 (which I already executed after upgrading), but this now returns an error too: Database check returns an error

From the log:

core:

$Fatal Error Details = array:3 [ "message" => "Database check geeft een fout, het lijkt er op dat de database deels geüpgraded is. Zet de back-up van de database terug en probeer opnieuw te upgraden." "code" => null "exception" => CRM_Core_Exception {#2069 -errorData: array:1 [ "error_code" => 0 ] #cause: null -_trace: null #message: "Database check geeft een fout, het lijkt er op dat de database deels geüpgraded is. Zet de back-up van de database terug en probeer opnieuw te upgraden." #code: 0 #file: "/data/sites/web/vendelennet/www/drupal7/sites/all/modules/civicrm/CRM/Upgrade/Form.php" #line: 349 trace: { /data/sites/web/vendelennet/www/drupal7/sites/all/modules/civicrm/CRM/Upgrade/Form.php:349 { › elseif (stripos($currentVer, 'upgrade')) { › throw new CRM_Core_Exception(ts('Database check failed - the database looks to have been partially upgraded. You may want to reload the database with the backup and try the upgrade process again.')); › } }

extension:

$Fatal Error Details = array:3 [ "message" => "DB Error: no such table" "code" => null "exception" => PEAR_Exception {#1622 #cause: DB_Error {#1608 +error_message_prefix: "" +mode: 16 +level: 1024 +code: -18 +message: "DB Error: no such table" +userinfo: "UPDATE civicrm_queue SET status = NULL WHERE name = 'ext-upgrade' [nativecode=1146 ** Table 'ID291638_crm5vnet.civicrm_queue' doesn't exist]" +backtrace: array:20 [ 0 => array:6 [ "file" => "/data/sites/web/vendelennet/www/drupal7/sites/all/modules/civicrm/vendor/pear/db/DB.php" "line" => 997 "function" => "__construct" "class" => "PEAR_Error" "type" => "->" "args" => array:5 [ 0 => "DB Error: no such table" 1 => -18 2 => 16 3 => array:2 [ 0 => "CRM_Core_Error" 1 => "exceptionHandler" ] 4 => "UPDATE civicrm_queue SET status = NULL WHERE name = 'ext-upgrade' [nativecode=1146 ** Table 'ID291638_crm5vnet.civicrm_queue' doesn't exist]" ] ] 1 => array:7 [ "file" => "/data/sites/web/vendelennet/www/drupal7/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php" "line" => 575 "function" => "__construct" "class" => "DB_Error" "object" => DB_Error {#1608} "type" => "->" "args" => array:4 [ 0 => -18 1 => 16 2 => array:2 [ 0 => "CRM_Core_Error" 1 => "exceptionHandler" ] 3 => "UPDATE civicrm_queue SET status = NULL WHERE name = 'ext-upgrade' [nativecode=1146 ** Table 'ID291638_crm5vnet.civicrm_queue' doesn't exist]" ] ] 20230827-0021 Not sure if I'm updating this case correctly

Getting there one step at the time... Ran the SQL proposed by Jon, and now it is missing another table:

Is there a way to find which update did not complete succesfully and how to fix all of it? I do not remember any update failing and I can access the information. CiviCRM is at version 5.60 and I want to have it at latest version before migrating the site to Drupal10.

Aug 27 00:06:55 [info] $CRM_Queue_ErrorPolicy_reportException = PEAR_Exception: "DB Error: no such table"

• ERROR TYPE: DB_Error
• ERROR CODE: -18
• ERROR MESSAGE: DB Error: no such table
• ERROR MODE: 16
• ERROR USERINFO: SELECT uj.id FROM civicrm_queue q INNER JOIN civicrm_user_job uj ON q.id = uj.queue_id WHERE q.name = 'ext-upgrade' [nativecode=1146 ** Table 'ID291638_crm5vnet.civicrm_user_job' doesn't exist]
• ERROR DEBUGINFO: SELECT uj.id FROM civicrm_queue q INNER JOIN civicrm_user_job uj ON q.id = uj.queue_id WHERE q.name = 'ext-upgrade' [nativecode=1146 ** Table 'ID291638_crm5vnet.civicrm_user_job' doesn't exist]

Running Drupal 7.98, CiviCRM 5.60.0, PHP 7.4

Answer 1

Add the table

 CREATE TABLE `civicrm_queue` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the queue',
  `type` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Type of the queue',
  `runner` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the task runner',
  `batch_limit` int unsigned NOT NULL DEFAULT '1' COMMENT 'Maximum number of items in a batch.',
  `lease_time` int unsigned NOT NULL DEFAULT '3600' COMMENT 'When claiming an item (or batch of items) for work, how long should the item(s) be reserved. (Seconds)',
  `retry_limit` int NOT NULL DEFAULT '0' COMMENT 'Number of permitted retries. Set to zero (0) to disable.',
  `retry_interval` int DEFAULT NULL COMMENT 'Number of seconds to wait before retrying a failed execution.',
  `status` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT 'active' COMMENT 'Execution status',
  `error` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Fallback behavior for unhandled errors',
  `is_template` tinyint NOT NULL DEFAULT '0' COMMENT 'Is this a template configuration (for use by other/future queues)?',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UI_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

Answer 2

Your database has gotten out of sync with CiviCRM. This may have happened if you had a failed upgrade, for instance.

Pradeep's answer explained how to fix civicrm_queue but it may be more important to discover the underlying reason these tables are missing/incomplete.

You can fix the most recent error with this SQL:

ALTER TABLE civicrm_queue_item ADD COLUMN run_count int NOT NULL DEFAULT 0 COMMENT 'Number of times execution has been attempted.';

This will add the missing "run_count" field, which should have been added on upgrade to Civi 5.48.

Answer 3

The civicrm_queue table was introduced in 5.47 (see: CRM/Upgrade/Incremental/sql/5.47.alpha1.mysql.tpl).

This error can happen when your skip several CiviCRM upgrade versions or your upgrade failed at some point. I had the similar error when jumping from 5.44.0 to 5.69.2 and upgrade process was failing as well.

This table is required in newer versions while running upgrade (e.g. cv ext:upgrade-db). So ideally you should do upgrade of CiviCRM Core with small increments first to 5.47 (to create that table in the first place), then do further upgrades.