Unable to complete upgrade from 6.1.x to 6.2.3

[kdmp]

After upgrade, receive the following:

Query Failed: ALTER TABLE calls modify column date_end datetime NULL , add column 0 DEFAULT '0' NULL ,add column NULL ,add column NULL , add column 0 DEFAULT '0' NULL ,add column NULL ,add column NULL : Database failure. Please refer to sugarcrm.log for details.

If I try to look at an account, I receive this:

Error running count query for Account List: Query Failed: ( SELECT count(*) c FROM documents INNER JOIN documents_accounts ON (documents.id=documents_accounts.document_id AND documents_accounts.account_id= '4ece0b69-3652-1b8a-a5a8-4de4f631476b') where ( documents_accounts.deleted=0 AND documents.deleted=0) AND documents.deleted=0 ): Database failure. Please refer to sugarcrm.log for details.

If I try to repair the database, I receive:

Differences found between database and vardefs
The following script will sync the database structure with the structure defined in the vardefs. You have the option of exporting this script and then running it against your database using external database management tools, or to allow the administration module to run the script.

NOTE: any changes you make to the script in the textbox will be reflected in the exported or executed code.

/* Table : calls */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - date_end - ROW [name] => 'date_end' [type] => 'date' */
/* VARDEF - date_end - ROW[name] => 'date_end' [vname] => 'LBL_DATE_END' [type] => 'datetime' [dbType] => 'datetime' [massupdate] => '' [comment] => 'Date is which call is scheduled to (or did) end' [enable_range_search] => '1' [options] => 'date_range_search_dom' [len] => '' [required] => '' */
/*MISSING IN DATABASE - - ROW*/
/*MISSING IN DATABASE - - ROW*/
/* INDEXES */
ALTER TABLE calls modify column date_end datetime NULL , add column 0 DEFAULT '0' NULL ,add column NULL ,add column NULL , add column 0 DEFAULT '0' NULL ,add column NULL ,add column NULL ;
/* Table : meetings */
/*COLUMNS*/
/*MISSING IN DATABASE - password - ROW*/
/*MISSING IN DATABASE - join_url - ROW*/
/*MISSING IN DATABASE - host_url - ROW*/
/*MISSING IN DATABASE - displayed_url - ROW*/
/*MISSING IN DATABASE - creator - ROW*/
/*MISSING IN DATABASE - external_id - ROW*/
/*MISMATCH WITH DATABASE - date_end - ROW [name] => 'date_end' [type] => 'date' */
/* VARDEF - date_end - ROW[name] => 'date_end' [vname] => 'LBL_DATE_END' [type] => 'datetime' [massupdate] => '' [comment] => 'Date meeting ends' [enable_range_search] => '1' [options] => 'date_range_search_dom' [dbType] => 'datetime' [len] => '' [required] => '' */
/*MISSING IN DATABASE - type - ROW*/
/* INDEXES */
ALTER TABLE meetings add column password varchar(50) NULL , add column join_url varchar(200) NULL , add column host_url varchar(400) NULL , add column displayed_url varchar(400) NULL , add column creator varchar(50) NULL , add column external_id varchar(50) NULL , modify column date_end datetime NULL , add column type varchar(255) DEFAULT 'Sugar' NULL ;
/* Table : documents */
/*COLUMNS*/
/*MISSING IN DATABASE - assigned_user_id - ROW*/
/*MISSING IN DATABASE - doc_id - ROW*/
/*MISSING IN DATABASE - doc_type - ROW*/
/*MISSING IN DATABASE - doc_url - ROW*/
/* INDEXES */
ALTER TABLE documents add column assigned_user_id char(36) NULL , add column doc_id varchar(100) NULL , add column doc_type varchar(100) DEFAULT 'Sugar' NULL , add column doc_url varchar(255) NULL ;
/* Table : document_revisions */
/*COLUMNS*/
/*MISSING IN DATABASE - doc_id - ROW*/
/*MISSING IN DATABASE - doc_type - ROW*/
/*MISSING IN DATABASE - doc_url - ROW*/
/* INDEXES */
/*MISSING INDEX IN DATABASE - documentrevision_mimetype -index ROW */
ALTER TABLE document_revisions add column doc_id varchar(100) NULL , add column doc_type varchar(100) NULL , add column doc_url varchar(255) NULL , ADD INDEX documentrevision_mimetype (file_mime_type);
/* Table : user_preferences */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - contents - ROW [name] => 'contents' [type] => 'text' */
/* VARDEF - contents - ROW[name] => 'contents' [type] => 'longtext' [vname] => 'LBL_DESCRIPTION' [isnull] => '1' [dbType] => 'longtext' [len] => '' [required] => '' */
/* INDEXES */
ALTER TABLE user_preferences modify column contents longtext NULL ;
/* Table : eapm */
/* MISSING TABLE: eapm */
CREATE TABLE eapm (id char(36) NOT NULL ,name varchar(255) NULL ,date_entered datetime NULL ,date_modified datetime NULL ,modified_user_id char(36) NULL ,created_by char(36) NULL ,description text NULL ,deleted bool DEFAULT '0' NULL ,assigned_user_id char(36) NULL ,password varchar(255) NULL ,url varchar(255) NULL ,application varchar(100) DEFAULT 'webex' NULL ,api_data text NULL ,consumer_key varchar(255) NULL ,consumer_secret varchar(255) NULL ,oauth_token varchar(255) NULL ,oauth_secret varchar(255) NULL ,validated bool DEFAULT 0 NULL , PRIMARY KEY (id), KEY idx_app_active (assigned_user_id, application, validated)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : email_addr_bean_rel */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - bean_module - ROW [name] => 'bean_module' [type] => 'varchar' [len] => '25' */
/* VARDEF - bean_module - ROW[name] => 'bean_module' [type] => 'varchar' [len] => '100' [required] => '' [dbType] => 'varchar' */
/* INDEXES */
ALTER TABLE email_addr_bean_rel modify column bean_module varchar(100) NULL ;
/* Table : emails_beans */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - bean_module - ROW [name] => 'bean_module' [type] => 'varchar' [len] => '36' */
/* VARDEF - bean_module - ROW[name] => 'bean_module' [type] => 'varchar' [len] => '100' [comment] => 'bean's Module' [dbType] => 'varchar' [required] => '' */
/* INDEXES */
ALTER TABLE emails_beans modify column bean_module varchar(100) NULL ;
/* Table : documents_accounts */
/* MISSING TABLE: documents_accounts */
CREATE TABLE documents_accounts (id varchar(36) NOT NULL ,date_modified datetime NULL ,deleted bool DEFAULT '0' NULL ,document_id varchar(36) NULL ,account_id varchar(36) NULL , PRIMARY KEY (id), KEY documents_accounts_account_id (account_id, document_id), KEY documents_accounts_document_id (document_id, account_id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : documents_contacts */
/* MISSING TABLE: documents_contacts */
CREATE TABLE documents_contacts (id varchar(36) NOT NULL ,date_modified datetime NULL ,deleted bool DEFAULT '0' NULL ,document_id varchar(36) NULL ,contact_id varchar(36) NULL , PRIMARY KEY (id), KEY documents_contacts_contact_id (contact_id, document_id), KEY documents_contacts_document_id (document_id, contact_id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : documents_opportunities */
/* MISSING TABLE: documents_opportunities */
CREATE TABLE documents_opportunities (id varchar(36) NOT NULL ,date_modified datetime NULL ,deleted bool DEFAULT '0' NULL ,document_id varchar(36) NULL ,opportunity_id varchar(36) NULL , PRIMARY KEY (id), KEY idx_docu_opps_oppo_id (opportunity_id, document_id), KEY idx_docu_oppo_docu_id (document_id, opportunity_id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : documents_cases */
/* MISSING TABLE: documents_cases */
CREATE TABLE documents_cases (id varchar(36) NOT NULL ,date_modified datetime NULL ,deleted bool DEFAULT '0' NULL ,document_id varchar(36) NULL ,case_id varchar(36) NULL , PRIMARY KEY (id), KEY documents_cases_case_id (case_id, document_id), KEY documents_cases_document_id (document_id, case_id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : documents_bugs */
/* MISSING TABLE: documents_bugs */
CREATE TABLE documents_bugs (id varchar(36) NOT NULL ,date_modified datetime NULL ,deleted bool DEFAULT '0' NULL ,document_id varchar(36) NULL ,bug_id varchar(36) NULL , PRIMARY KEY (id), KEY documents_bugs_bug_id (bug_id, document_id), KEY documents_bugs_document_id (document_id, bug_id)) CHARACTER SET utf8 COLLATE utf8_general_ci;

If I execute the script, I receive this in the error log:

[7887][c6d90463-0e54-8cd6-43a7-4b7c2df11f5b][FATAL] Executing repair query: Query Failed: ALTER TABLE calls add column 0 DEFAULT '0' NULL ,add column NULL ,add column NULL , add column 0 DEFAULT '0' NULL ,add column NULL ,add column NULL: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0 DEFAULT '0' NULL ,add column NULL ,add column NULL , add column 0 D' at line 1

Any suggestions as to why this is happening and how I can fix it?

[Angel]

Have you tried to directly execute the ALTER queries against the database via an admin tool such as phpMyAdmin or SQLyog?

[kdmp]

Hi Angel,

Yes I have. Directly through phpmyadmin and received the same results. I tried exporting the sql script from the database repair in SugarCRM and importing it with the same results.

Kevin

[kdmp]

Getting to the point where I have to start working toward 6.4. So I am back to this and haven't solved this yet. Any other suggestions?

[kdmp]

I never did figure this out, and now I am back at it because I want to get prepared for 6.4. Anyone have any suggestions why the upgrade could not run alter on the table?

Thanks,

Kevin

[rpruitt625]

Kevin, I'm preparing to do the same upgrade (6.1.6 --> 6.2.4)
I have a fully functioning copy of 6.1.6 on a dev server, so I can twiddle and tweak to my hearts desire. I'm running CentOS 5.6 I'm happy to share my experiences with you.

[Angel]

Just a quick side note/warning, I've seen multiple instances of Sugar experience problems with custom relationships in 6.2.4. In a nutshell, it changes the names of the fields used for the relationship, effectively breaking the link to old relationship records.

I recently saw this happen with 6.3.1, to a degree, but I am not yet convinced it is a bug over just coincidence.

So, word to the wise, if you have custom modules/relationships in play, definitely make a backup before you upgrade and preferably upgrade to 6.3.1 instead.

[kdmp]

Were you able to upgrade?

I don't have any custom relationships and uninstalled all custom modules....

Kevin

[kdmp]

I don't think I pointed out when this pops up. It happens after clicking next on 'Confirm Layouts'. It occurs whether I choose to make the changes to Module Layouts (Accounts, Contacts, Notes) or not. I can also add that there are no custom relationships in calls and there are no add-on modules installed.

[FATAL] Query Failed: ALTER TABLE calls modify column date_end datetime NULL , add column 0 DEFAULT '0' NULL ,add column NULL ,add column NULL , add column 0 DEFAULT '0' NULL ,add column NULL ,add column NULL : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0 DEFAULT '0' NULL ,add column NULL ,add column NULL , add column 0 D' at line 1

I would love to know what this query is trying to do....it replaced every field name past column date_end with a blank or a 0. If soimeone could let me know what it is doing I could run this query manually in phpmyadmin.

Okay...this is nuts...I can't even upgrade from 6.1.4 to 6.1.8!! Same problem. Not sure why it is even touching the calls table. I don't see any changes between 6.1.4 and 6.1.8. I looked at the tables side by side.

I have also tried the silent upgrade and received the same database error.

Thanks,

Kevin

[kdmp]

So this has nothing to do with the calls table. I took the call tables from a fresh install of sugarcrm 6.1.4 and received the same error message.