Twilio extension failure w/ DB Error: unknown error

Question

Twilio extension worked great for the past year, but fails now. Outgoing messages seem unable to reach the Twilio API, though CRON is working. I've tried different versions of PHP with no effect. I've disabled all plugins except Civi, also with no effect. No changes were made to our extension settings or to our Twilio account configuration.

From the Jobs Log:

Entity: Job Action: process_sms

Summary

Finished execution of Send Scheduled SMS with result: Failure, Error message: DB Error: unknown error

Details

Parameters parsed (and passed to API method): a:1:{s:7:"version";i:3;}

Full message: Finished execution of Send Scheduled SMS with result: Failure, Error message: DB Error: unknown error

That's a bit vague, so I installed a working log viewer extension. Seems we're getting one of these every minute when the CRON job executes and fails to connect with Twilio.

 [info] $Fatal Error Details = Array (
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => 
                    SELECT      civicrm_mailing_event_queue.id,
                                civicrm_phone.phone as phone,
                                civicrm_mailing_event_queue.contact_id,
                                civicrm_mailing_event_queue.hash,
                                NULL as email
                    FROM        civicrm_mailing_event_queue
                    INNER JOIN  civicrm_phone
                            ON  civicrm_mailing_event_queue.phone_id = civicrm_phone.id
                    INNER JOIN  civicrm_contact
                            ON  civicrm_contact.id = civicrm_phone.contact_id
                    LEFT JOIN   civicrm_mailing_event_delivered
                            ON  civicrm_mailing_event_queue.id = civicrm_mailing_event_delivered.event_queue_id
                    LEFT JOIN   civicrm_mailing_event_bounce
                            ON  civicrm_mailing_event_queue.id = civicrm_mailing_event_bounce.event_queue_id
                    WHERE       civicrm_mailing_event_queue.job_id = 807
                        AND     civicrm_mailing_event_delivered.id IS null
                        AND     civicrm_mailing_event_bounce.id IS null
                        AND    ( civicrm_contact.is_opt_out = 0
                        OR       civicrm_contact.do_not_sms = 0 ) [nativecode=1712 ** Index civicrm_mailing_event_queue is corrupted]
    [type] => DB_Error
    [user_info] => 
                    SELECT      civicrm_mailing_event_queue.id,
                                civicrm_phone.phone as phone,
                                civicrm_mailing_event_queue.contact_id,
                                civicrm_mailing_event_queue.hash,
                                NULL as email
                    FROM        civicrm_mailing_event_queue
                    INNER JOIN  civicrm_phone
                            ON  civicrm_mailing_event_queue.phone_id = civicrm_phone.id
                    INNER JOIN  civicrm_contact
                            ON  civicrm_contact.id = civicrm_phone.contact_id
                    LEFT JOIN   civicrm_mailing_event_delivered
                            ON  civicrm_mailing_event_queue.id = civicrm_mailing_event_delivered.event_queue_id
                    LEFT JOIN   civicrm_mailing_event_bounce
                            ON  civicrm_mailing_event_queue.id = civicrm_mailing_event_bounce.event_queue_id
                    WHERE       civicrm_mailing_event_queue.job_id = 807
                        AND     civicrm_mailing_event_delivered.id IS null
                        AND     civicrm_mailing_event_bounce.id IS null
                        AND    ( civicrm_contact.is_opt_out = 0
                        OR       civicrm_contact.do_not_sms = 0 ) [nativecode=1712 ** Index civicrm_mailing_event_queue is corrupted]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="
                    SELECT      civicrm_mailing_event_queue.id,
                                civicrm_phone.phone as phone,
                                civicrm_mailing_event_queue.contact_id,
                                civicrm_mailing_event_queue.hash,
                                NULL as email
                    FROM        civicrm_mailing_event_queue
                    INNER JOIN  civicrm_phone
                            ON  civicrm_mailing_event_queue.phone_id = civicrm_phone.id
                    INNER JOIN  civicrm_contact
                            ON  civicrm_contact.id = civicrm_phone.contact_id
                    LEFT JOIN   civicrm_mailing_event_delivered
                            ON  civicrm_mailing_event_queue.id = civicrm_mailing_event_delivered.event_queue_id
                    LEFT JOIN   civicrm_mailing_event_bounce
                            ON  civicrm_mailing_event_queue.id = civicrm_mailing_event_bounce.event_queue_id
                    WHERE       civicrm_mailing_event_queue.job_id = 807
                        AND     civicrm_mailing_event_delivered.id IS null
                        AND     civicrm_mailing_event_bounce.id IS null
                        AND    ( civicrm_contact.is_opt_out = 0
                        OR       civicrm_contact.do_not_sms = 0 ) [nativecode=1712 ** Index civicrm_mailing_event_queue is corrupted]"] )

And here is the corresponding backtrace:

 [info] $backTrace = #0 /REDACTED-PLUGINS-PATH/civicrm/civicrm/CRM/Core/Error.php(948): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /REDACTED-PLUGINS-PATH/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /REDACTED-PLUGINS-PATH/civicrm/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "\n                    SELECT      civicrm_mailing_event_queue.id,\n          ...")
#3 /REDACTED-PLUGINS-PATH/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "\n                    SELECT      civicrm_mailing_event_queue.id,\n          ...")
#4 /REDACTED-PLUGINS-PATH/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "\n                    SELECT      civicrm_mailing_event_queue.id,\n          ...", "DB_Error", TRUE)
#5 /REDACTED-PLUGINS-PATH/civicrm/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
#6 /REDACTED-PLUGINS-PATH/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "\n                    SELECT      civicrm_mailing_event_queue.id,\n          ...", "1712 ** Index civicrm_mailing_event_queue is corrupted")
#7 /REDACTED-PLUGINS-PATH/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /REDACTED-PLUGINS-PATH/civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("\n                    SELECT      civicrm_mailing_event_queue.id,\n          ...")
#9 /REDACTED-PLUGINS-PATH/civicrm/civicrm/packages/DB/DataObject.php(2415): DB_common->query("\n                    SELECT      civicrm_mailing_event_queue.id,\n          ...")
#10 /REDACTED-PLUGINS-PATH/civicrm/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("\n                    SELECT      civicrm_mailing_event_queue.id,\n          ...")
#11 /REDACTED-PLUGINS-PATH/civicrm/civicrm/CRM/Core/DAO.php(438): DB_DataObject->query("\n                    SELECT      civicrm_mailing_event_queue.id,\n          ...")
#12 /REDACTED-PLUGINS-PATH/civicrm/civicrm/CRM/Mailing/BAO/MailingJob.php(1125): CRM_Core_DAO->query("\n                    SELECT      civicrm_mailing_event_queue.id,\n          ...")
#13 /REDACTED-PLUGINS-PATH/civicrm/civicrm/CRM/Mailing/BAO/MailingJob.php(540): CRM_Mailing_BAO_MailingJob::findPendingTasks("807", "sms")
#14 /REDACTED-PLUGINS-PATH/civicrm/civicrm/CRM/Mailing/BAO/MailingJob.php(199): CRM_Mailing_BAO_MailingJob->deliver(Object(org_civicrm_sms_twilio), NULL)
#15 /REDACTED-PLUGINS-PATH/civicrm/civicrm/CRM/Mailing/BAO/Mailing.php(2843): CRM_Mailing_BAO_MailingJob::runJobs(NULL, "sms")
#16 /REDACTED-PLUGINS-PATH/civicrm/civicrm/api/v3/Job.php(380): CRM_Mailing_BAO_Mailing::processQueue("sms")
#17 /REDACTED-PLUGINS-PATH/civicrm/civicrm/Civi/API/Provider/MagicFunctionProvider.php(100): civicrm_api3_job_process_sms((Array:1))
#18 /REDACTED-PLUGINS-PATH/civicrm/civicrm/Civi/API/Kernel.php(169): Civi\API\Provider\MagicFunctionProvider->invoke((Array:9))
#19 /REDACTED-PLUGINS-PATH/civicrm/civicrm/Civi/API/Kernel.php(100): Civi\API\Kernel->runRequest((Array:9))
#20 /REDACTED-PLUGINS-PATH/civicrm/civicrm/api/api.php(23): Civi\API\Kernel->runSafe("Job", "process_sms", (Array:1), NULL)
#21 /REDACTED-PLUGINS-PATH/civicrm/civicrm/CRM/Core/JobManager.php(146): civicrm_api("Job", "process_sms", (Array:1))
#22 /REDACTED-PLUGINS-PATH/civicrm/civicrm/CRM/Core/JobManager.php(79): CRM_Core_JobManager->executeJob(Object(CRM_Core_ScheduledJob))
#23 /REDACTED-PLUGINS-PATH/civicrm/civicrm/bin/cron.php(42): CRM_Core_JobManager->execute()
#24 {main}

PHP 7.1.22

CiviCRM 5.12.4

WordPress 5.1.1

Answer 1

Drop index civicrm_mailing_event_queue (table civicrm_mailing_event_queue) then re-create it.

After doing this, run the following check:

check table civicrm_mailing_event_queue

You should get a message that table status is 'OK'.