0

Trying to carry out an upgrade of civicrm. It fails when trying to upgrade the database to 5.15.alpha1 due to "Incorrect key file for table" when trying to create an index called index_all. The table in question is not repairable. It is empty.

Output of SHOW INDEXES

mysql> show indexes from civicrm_prevnext_cache;
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| civicrm_prevnext_cache |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

I'd be grateful for any tips on how to fix this.

The log file contains the following:

Mar 24 16:26:10  [info] Running task: Upgrade DB to 5.15.alpha1: SQL

Mar 24 16:26:11  [info] Running task: Fix errant deferred revenue settings

Mar 24 16:26:12  [info] Running task: Fix cache key column name in prev next cache

Mar 24 16:26:12  [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entity_id2, entity_table, is_selected) [nativecode=1034 ** Incorrect key file for table 'civicrm_prevnext_cache'; try to repair it]
    [type] => DB_Error
    [user_info] => CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entity_id2, entity_table, is_selected) [nativecode=1034 ** Incorrect key file for table 'civicrm_prevnext_cache'; try to repair it]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entity_id2, entity_table, is_selected) [nativecode=1034 ** Incorrect key file for table 'civicrm_prevnext_cache'; try to repair it]"]
)


Mar 24 16:26:12  [debug] $backTrace = #0 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(915): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB.php(997): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entit...")
#3 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entit...")
#4 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entit...", "DB_Error", TRUE)
#5 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1925): PEAR->__call("raiseError", (Array:7))
#6 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(935): DB_common->raiseError(-1, NULL, NULL, "CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entit...", "1034 ** Incorrect key file for table 'civicrm_prevnext_cache'; try to repair it")
#7 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(405): DB_mysqli->mysqliRaiseError()
#8 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1231): DB_mysqli->simpleQuery("CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entit...")
#9 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2691): DB_common->query("CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entit...")
#10 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1829): DB_DataObject->_query("CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entit...")
#11 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(421): DB_DataObject->query("CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entit...")
#12 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1420): CRM_Core_DAO->query("CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entit...", TRUE)
#13 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Incremental/php/FiveFifteen.php(74): CRM_Core_DAO::executeQuery("CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entit...")
#14 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Queue/Task.php(74): CRM_Upgrade_Incremental_php_FiveFifteen::fixCacheKeyColumnNamePrevNext(Object(CRM_Queue_TaskContext))
#15 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Queue/Runner.php(202): CRM_Queue_Task->run(Object(CRM_Queue_TaskContext))
#16 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Queue/Page/AJAX.php(36): CRM_Queue_Runner->runNext(TRUE)
#17 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Queue/ErrorPolicy.php(90): CRM_Queue_Page_AJAX::{closure}()
#18 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Queue/Page/AJAX.php(38): CRM_Queue_ErrorPolicy->call(Object(Closure))
#19 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(234): CRM_Queue_Page_AJAX::runNext()
#20 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(68): CRM_Core_Invoke::runItem((Array:13))
#21 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:5))
#22 /var/www/html/civcrm-test/civicrm/wp-content/plugins/civicrm/civicrm.php(1449): CRM_Core_Invoke::invoke((Array:5))
#23 /var/www/html/civcrm-test/civicrm/wp-includes/class-wp-hook.php(288): CiviCRM_For_WordPress->invoke("")
#24 /var/www/html/civcrm-test/civicrm/wp-includes/class-wp-hook.php(312): WP_Hook->apply_filters("", (Array:1))
#25 /var/www/html/civcrm-test/civicrm/wp-includes/plugin.php(478): WP_Hook->do_action((Array:1))
#26 /var/www/html/civcrm-test/civicrm/wp-admin/admin.php(254): do_action("toplevel_page_CiviCRM")
#27 {main}
Improve this question
2
  • From which version of Civi to what version of Civi are you tryong to upgrade?
    – Pradeep Nayak
    Mar 24 '20 at 17:28
  • 5.11.0 --> 5.23.2
    – Peter Evans
    Mar 27 '20 at 10:25
2

In step 5_15_alpha1 the field cacheKey must be renamed in cachekey. Is that done.

Try to update the fieldname if it is cacheKey. and then: CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entity_id2, entity_table, is_selected)

Improve this answer
2
  • This was absolutely critical to the solution. Thanks!
    – Peter Evans
    Mar 27 '20 at 10:18
  • After doing the update successfully I changed the name of the field back to cacheKey as it is referenced as such in the code ./wp-content/plugins/civicrm/civicrm/CRM/Contact/Page/DedupeFind.php: $this->assign('cacheKey', $cacheKeyString); ./wp-content/plugins/civicrm/civicrm/CRM/Contact/Page/AJAX.php: $cacheKeyString = CRM_Utils_Request::retrieve('cacheKey', 'Alphanumeric', $null, FALSE); ./wp-content/plugins/civicrm/civicrm/CRM/Core/PrevNextCache/Sql.php: ->where('pnc.cachekey = @cacheKey', ['cacheKey' => $cacheKey])
    – Peter Evans
    Mar 27 '20 at 10:24
2

If the table is empty, try dropping and recreating it, i.e.

SHOW CREATE TABLE civicrm_prevnext_cache;

Then copy the output to the clipboard.

Then DROP TABLE civicrm_prevnext_cache;

Then paste from the clipboard.

Improve this answer
1
  • I did this too - this was kind of the partial implementation of the answer. In combination with the accepted answer the problem was solved. This alone was not enough. I had to s/cacheKey/cachekey/g in the create table statement.
    – Peter Evans
    Mar 27 '20 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.