Failed Upgrade 4.6.4 to 4.6.5 Joomla 2.5 & 3.4

Question

My attempts to upgrade 4.6.4->4.6.5 fails in Joomla 2.5 with an 'already exists' error on CREATE TABLE `civicrm_cxn

Any clues? The full error message is:

[Error: Upgrade DB to 4.6.5] Error Field Error Value Type DB_Error Code -5 Message DB Error: already exists Mode 16 UserInfo CREATE TABLE civicrm_cxn ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Connection ID', app_guid varchar(128) COMMENT 'Application GUID', app_meta text COMMENT 'Application Metadata (JSON)', cxn_guid varchar(128) COMMENT 'Connection GUID', secret text COMMENT 'Shared secret', perm text COMMENT 'Permissions approved for the service (JSON)', options text COMMENT 'Options for the service (JSON)', is_active tinyint DEFAULT 1 COMMENT 'Is connection currently enabled?', created_date timestamp NULL DEFAULT NULL COMMENT 'When was the connection was created.', modified_date timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When the connection was created or modified.', fetched_date timestamp NULL DEFAULT NULL COMMENT 'The last time the application metadata was fetched.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_appid( app_guid ) , UNIQUE INDEX UI_keypair_cxnid( cxn_guid ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci [nativecode=1050 ** Table 'civicrm_cxn' already exists] DebugInfo CREATE TABLE civicrm_cxn ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Connection ID', app_guid varchar(128) COMMENT 'Application GUID', app_meta text COMMENT 'Application Metadata (JSON)', cxn_guid varchar(128) COMMENT 'Connection GUID', secret text COMMENT 'Shared secret', perm text COMMENT 'Permissions approved for the service (JSON)', options text COMMENT 'Options for the service (JSON)', is_active tinyint DEFAULT 1 COMMENT 'Is connection currently enabled?', created_date timestamp NULL DEFAULT NULL COMMENT 'When was the connection was created.', modified_date timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When the connection was created or modified.', fetched_date timestamp NULL DEFAULT NULL COMMENT 'The last time the application metadata was fetched.' , PRIMARY KEY ( id ) , UNIQUE INDEX UI_appid( app_guid ) , UNIQUE INDEX UI_keypair_cxnid( cxn_guid ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci [nativecode=1050 ** Table 'civicrm_cxn' already exists] PEAR_Exception: DB Error: already exists in unknown on line unknown - DB_Error: DB Error: already exists in unknown on line unknown Exception trace

Answer 1

Civi 4.6.5 adds a new table "civicrm_cxn". Your upgrade is failing because it's trying to add this table, but it already exists.

Usually this happens when you attempt an upgrade which didn't complete for whatever reason, then attempt a second upgrade without doing a DROP DATABASE and CREATE DATABASE command. Even though you may have restored a backup, newly-created tables will still be present.

The most "correct" way to handle this is to do a DROP DATABASE command for your Civi database, then CREATE DATABASE and load in your backup. That said, I suspect you can get away with blowing away the new table and running the upgrade again; try DROP TABLE civicrm_cxn; in MySQL and see if the upgrade works. Make a backup first, of course!

Answer 2

This table is newly-introduced in 4.6.5. It has been under discussion for a little while, however--have you installed a patch, pulled a non-release branch from GitHub, or anything like that?

I assume that before retrying the failed upgrade, you're restoring your database to the backup that you took prior to the upgrade.

Assuming you don't know/care how the table got in your database, you can go ahead and drop the table. In MySQL, just enter

DROP TABLE civicrm_cxn;

You can then retry the upgrade, which will re-create the table.

If you failed to backup before upgrading, at least only a couple of queries would be attempted going from 4.6.4 to 4.6.5. The file is at https://github.com/civicrm/civicrm-core/blob/4.6.5/CRM/Upgrade/Incremental/sql/4.6.5.mysql.tpl. You'll see that it creates the table, and then it adds a new activity type of "Failed Payment" to the civicrm_option_value table. If you need to start over, you can just drop the table and delete that row.