Upgrade Failed (from 7.8.21 to 7.10.9)

jmathew · 18 September 2018 07:05

Upgrade process completed successfully. Then I got a db failure when trying to save a target. Here is the last couple of entries in stuiecrm.log


Tue Sep 18 12:31:07 2018 [9542][1][FATAL] Mysqli_query failed.
Tue Sep 18 12:31:07 2018 [9542][1][FATAL]  Query Failed: SELECT
                    ea.email_address,
                    ea.email_address_caps,
                    ea.invalid_email,
                    ea.opt_out,
                    ea.confirm_opt_in,
                    ea.date_created,
                    ea.date_modified,
                    ear.id,
                    ear.email_address_id,
                    ear.bean_id,
                    ear.bean_module,
                    ear.primary_address,
                    ear.reply_to_address,
                    ear.deleted
                FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id
                WHERE
                    ear.bean_module = 'Leads'
                    AND ear.bean_id = '1d10a206-f9bd-72b2-d32a-5b8cd171de7a'
                    AND ear.deleted = 0
                ORDER BY ear.reply_to_address, ear.primary_address DESC: MySQL error 1054: Unknown column 'ea.confirm_opt_in' in 'field list'
Tue Sep 18 12:31:10 2018 [9670][1][FATAL] Mysqli_query failed.
Tue Sep 18 12:31:10 2018 [9670][1][FATAL]  Query Failed: SELECT
                    ea.email_address,
                    ea.email_address_caps,
                    ea.invalid_email,
                    ea.opt_out,
                    ea.confirm_opt_in,
                    ea.date_created,
                    ea.date_modified,
                    ear.id,
                    ear.email_address_id,
                    ear.bean_id,
                    ear.bean_module,
                    ear.primary_address,
                    ear.reply_to_address,
                    ear.deleted
                FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id
                WHERE
                    ear.bean_module = 'Users'
                    AND ear.bean_id = '1'
                    AND ear.deleted = 0
                ORDER BY ear.reply_to_address, ear.primary_address DESC: MySQL error 1054: Unknown column 'ea.confirm_opt_in' in 'field list'

Thanks

-john

amariussi · 18 September 2018 07:24

Try the following:

reset permissions correctly (if you don’t know how to, please ask)
log in as admin and go to Admin->Repairs->Quick Repair and Rebuild
If at the bottom of the Quick Repair and Rebuild you are prompted to accept some operation on the DB, proceed accepting.

Then log out and log in again. Clear the browser cache (press Ctrl+F5) then check if the error persists. If this doesn’t work you may have to retry the update.

What is your OS? PHP version? DB?

jmathew · 18 September 2018 09:10

@amariussi Thanks that worked

However, I did not do step 1 for resetting permissions. Step 2 did show a difference between the db structure and vardefs.

OS: Debian 9 (64bit)
Php: 7.0.3
DB: Mariadb 15.1

Thanks again!

slavenglaafs · 16 July 2019 17:04

Hi Guys,

I have been trying to update LTS 7.8.8 to latest LTS 7.10.18 with database failure at the end of the upgrade.

When I try to follow these steps @amariussi have mentioned above I get database failure when executing sync within Quick Repair Rebuild


/* Table : users */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - first_name -  ROW [name] => 'first_name'  [type] => 'varchar'  [len] => '30'  */
/* VARDEF - first_name -  ROW[name] => 'first_name'  [vname] => 'LBL_FIRST_NAME'  [dbType] => 'varchar'  [type] => 'varchar'  [len] => '255'  [required] => ''  */
/*MISMATCH WITH DATABASE - last_name -  ROW [name] => 'last_name'  [type] => 'varchar'  [len] => '30'  */
/* VARDEF - last_name -  ROW[name] => 'last_name'  [vname] => 'LBL_LAST_NAME'  [dbType] => 'varchar'  [type] => 'varchar'  [len] => '255'  [importable] => 'required'  [required] => ''  */
/*MISSING IN DATABASE - factor_auth -  ROW*/
/*MISSING IN DATABASE - factor_auth_interface -  ROW*/
/* INDEXES */
ALTER TABLE users   modify COLUMN first_name varchar(255)  NULL ,  modify COLUMN last_name varchar(255)  NULL ,  add COLUMN factor_auth bool  DEFAULT '0' NULL ,  add COLUMN factor_auth_interface varchar(255)  NULL ;
/* Table : campaigns */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - name -  ROW [name] => 'name'  [type] => 'varchar'  [len] => '50'  */
/* VARDEF - name -  ROW[name] => 'name'  [vname] => 'LBL_CAMPAIGN_NAME'  [dbType] => 'varchar'  [type] => 'varchar'  [len] => '255'  [comment] => 'The name of the campaign'  [importable] => 'required'  [required] => ''  [unified_search] => '1'  [full_text_search] => 'Array'  */
/*MISSING IN DATABASE - survey_id -  ROW*/
/* INDEXES */
/*MISSING INDEX IN DATABASE - idx_survey_id -index  ROW */
ALTER TABLE campaigns   modify COLUMN name varchar(255)  NULL ,  add COLUMN survey_id char(36)  NULL ,  ADD INDEX idx_survey_id (survey_id);
/* Table : prospect_lists */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - name -  ROW [name] => 'name'  [type] => 'varchar'  [len] => '50'  */
/* VARDEF - name -  ROW[name] => 'name'  [vname] => 'LBL_NAME'  [type] => 'varchar'  [len] => '255'  [importable] => 'required'  [unified_search] => '1'  [full_text_search] => 'Array'  [dbType] => 'varchar'  [required] => ''  */
/* INDEXES */
ALTER TABLE prospect_lists   modify COLUMN name varchar(255)  NULL ;
/* Table : prospects */
/*COLUMNS*/
/*MISSING IN DATABASE - lawful_basis -  ROW*/
/*MISSING IN DATABASE - date_reviewed -  ROW*/
/*MISSING IN DATABASE - lawful_basis_source -  ROW*/
/* INDEXES */
ALTER TABLE prospects   add COLUMN lawful_basis text(100)  NULL ,  add COLUMN date_reviewed date  NULL ,  add COLUMN lawful_basis_source varchar(100)  NULL ;
/* Table : campaign_trkrs */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - tracker_name -  ROW [name] => 'tracker_name'  [type] => 'varchar'  [len] => '30'  */
/* VARDEF - tracker_name -  ROW[name] => 'tracker_name'  [vname] => 'LBL_TRACKER_NAME'  [type] => 'varchar'  [len] => '255'  [comment] => 'The name of the campaign tracker'  [dbType] => 'varchar'  [required] => ''  */
/* INDEXES */
ALTER TABLE campaign_trkrs   modify COLUMN tracker_name varchar(255)  NULL ;
/* Table : emailman */
/*COLUMNS*/
/*MISSING IN DATABASE - related_confirm_opt_in -  ROW*/
/* INDEXES */
ALTER TABLE emailman   add COLUMN related_confirm_opt_in bool  DEFAULT '0' NULL ;
/* Table : contacts */
/*COLUMNS*/
/*MISSING IN DATABASE - lawful_basis -  ROW*/
/*MISSING IN DATABASE - date_reviewed -  ROW*/
/*MISSING IN DATABASE - lawful_basis_source -  ROW*/
/* INDEXES */
ALTER TABLE contacts   add COLUMN lawful_basis text(100)  NULL ,  add COLUMN date_reviewed date  NULL ,  add COLUMN lawful_basis_source varchar(100)  NULL ;
/* Table : email_templates */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - body -  ROW [name] => 'body'  [type] => 'text'  */
/* VARDEF - body -  ROW[name] => 'body'  [vname] => 'LBL_BODY'  [type] => 'longtext'  [comment] => 'Plain text body to be used in resulting email'  [dbType] => 'longtext'  [len] => ''  [required] => ''  */
/*MISMATCH WITH DATABASE - body_html -  ROW [name] => 'body_html'  [type] => 'text'  */
/* VARDEF - body_html -  ROW[name] => 'body_html'  [vname] => 'LBL_PLAIN_TEXT'  [type] => 'longtext'  [comment] => 'HTML formatted email body to be used in resulting email'  [dbType] => 'longtext'  [len] => ''  [required] => ''  */
/* INDEXES */
ALTER TABLE email_templates   modify COLUMN body longtext  NULL ,  modify COLUMN body_html longtext  NULL ;
/* Table : emails */
/*COLUMNS*/
/*MISSING IN DATABASE - orphaned -  ROW*/
/*MISSING IN DATABASE - last_synced -  ROW*/
/*MISSING IN DATABASE - date_sent_received -  ROW*/
/*MISSING IN DATABASE - uid -  ROW*/
/*MISSING IN DATABASE - category_id -  ROW*/
/* INDEXES */
/*MISSING INDEX IN DATABASE - idx_email_cat -index  ROW */
ALTER TABLE emails   add COLUMN orphaned bool  DEFAULT '0' NULL ,  add COLUMN last_synced datetime  NULL ,  add COLUMN date_sent_received datetime  NULL ,  add COLUMN uid varchar(255)  NULL ,  add COLUMN category_id varchar(100)  NULL ,  ADD INDEX idx_email_cat (category_id);
/* Table : alerts */
/*COLUMNS*/
/*MISSING IN DATABASE - reminder_id -  ROW*/
/* INDEXES */
ALTER TABLE alerts   add COLUMN reminder_id char(36)  NULL ;
/* Table : email_addresses */
/*COLUMNS*/
/*MISSING IN DATABASE - confirm_opt_in -  ROW*/
/*MISSING IN DATABASE - confirm_opt_in_date -  ROW*/
/*MISSING IN DATABASE - confirm_opt_in_sent_date -  ROW*/
/*MISSING IN DATABASE - confirm_opt_in_fail_date -  ROW*/
/*MISSING IN DATABASE - confirm_opt_in_token -  ROW*/
/* INDEXES */
ALTER TABLE email_addresses   add COLUMN confirm_opt_in varchar(255)  DEFAULT 'not-opt-in' NULL ,  add COLUMN confirm_opt_in_date datetime  NULL ,  add COLUMN confirm_opt_in_sent_date datetime  NULL ,  add COLUMN confirm_opt_in_fail_date datetime  NULL ,  add COLUMN confirm_opt_in_token varchar(255)  NULL ;
/* Table : aobh_businesshours */
/*COLUMNS*/
/*MISSING IN DATABASE - open_status -  ROW*/
/* INDEXES */
ALTER TABLE aobh_businesshours   add COLUMN open_status bool  DEFAULT '0' NULL ;
/* Table : reminders */
/*COLUMNS*/
/*MISSING IN DATABASE - date_willexecute -  ROW*/
/*MISSING IN DATABASE - popup_viewed -  ROW*/
/* INDEXES */
ALTER TABLE reminders   add COLUMN date_willexecute int(60)  DEFAULT '-1' NULL ,  add COLUMN popup_viewed bool  DEFAULT '0' NULL ;
/* Table : aos_pdf_templates */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - description -  ROW [name] => 'description'  [type] => 'text'  */
/* VARDEF - description -  ROW[name] => 'description'  [vname] => 'LBL_DESCRIPTION'  [type] => 'longtext'  [comment] => 'Full text of the note'  [rows] => '6'  [cols] => '80'  [required] => ''  [massupdate] => '0'  [no_default] => ''  [comments] => 'Full text of the note'  [help] => ''  [importable] => 'true'  [duplicate_merge] => 'disabled'  [duplicate_merge_dom_value] => '0'  [audited] => ''  [reportable] => '1'  [unified_search] => ''  [merge_filter] => 'disabled'  [size] => '20'  [studio] => 'visible'  [dbType] => 'longtext'  [len] => ''  */
/* INDEXES */
ALTER TABLE aos_pdf_templates   modify COLUMN description longtext  NULL ;
/* Table : aos_quotes */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - number -  ROW [name] => 'number'  [type] => 'int'  [len] => '11'  [required] => 'true'  */
/* VARDEF - number -  ROW[name] => 'number'  [vname] => 'LBL_QUOTE_NUMBER'  [type] => 'int'  [len] => '11'  [isnull] => 'false'  [unified_search] => '1'  [comments] => ''  [importable] => 'true'  [duplicate_merge] => 'disabled'  [reportable] => '1'  [disable_num_format] => '1'  [dbType] => 'int'  [required] => ''  */
/* INDEXES */
ALTER TABLE aos_quotes   modify COLUMN number int(11)  NULL ;
/* Table : outbound_email */
/*COLUMNS*/
/*MISSING IN DATABASE - smtp_from_name -  ROW*/
/*MISSING IN DATABASE - smtp_from_addr -  ROW*/
/*MISMATCH WITH DATABASE - mail_smtpport -  ROW [name] => 'mail_smtpport'  [type] => 'int'  [len] => '5'  */
/* VARDEF - mail_smtpport -  ROW[name] => 'mail_smtpport'  [vname] => 'LBL_MAIL_SMTPPORT'  [type] => 'varchar'  [len] => '5'  [default] => '0'  [reportable] => ''  [dbType] => 'varchar'  [required] => ''  */
/* INDEXES */
ALTER TABLE outbound_email   add COLUMN smtp_from_name varchar(255)  NULL ,  add COLUMN smtp_from_addr varchar(255)  NULL ,  modify COLUMN mail_smtpport varchar(5)  DEFAULT '0' NULL ;
/* Table : oauth2tokens */
/* MISSING TABLE: oauth2tokens */
CREATE TABLE oauth2tokens (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 ,token_is_revoked bool  NULL ,token_type varchar(255)  NULL ,access_token_expires datetime  NULL ,access_token varchar(4000)  NULL ,refresh_token varchar(4000)  NULL ,refresh_token_expires datetime  NULL ,grant_type varchar(255)  NULL ,state varchar(1024)  NULL ,client char(36)  NULL ,assigned_user_id char(36)  NULL  , PRIMARY KEY (id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : oauth2clients */
/* MISSING TABLE: oauth2clients */
CREATE TABLE oauth2clients (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 ,secret varchar(4000)  NULL ,redirect_url varchar(255)  NULL ,is_confidential bool  DEFAULT '1' NULL ,allowed_grant_type varchar(255)  DEFAULT 'password' NULL ,duration_value int(11)  NULL ,duration_amount int(11)  NULL ,duration_unit varchar(255)  DEFAULT 'Duration Unit' NULL ,assigned_user_id char(36)  NULL  , PRIMARY KEY (id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : surveyresponses */
/* MISSING TABLE: surveyresponses */
CREATE TABLE surveyresponses (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 ,happiness int  NULL ,email_response_sent bool  NULL ,account_id char(36)  NULL ,campaign_id char(36)  NULL ,contact_id char(36)  NULL ,survey_id char(36)  NULL  , PRIMARY KEY (id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : surveys */
/* MISSING TABLE: surveys */
CREATE TABLE surveys (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 ,status varchar(100)  DEFAULT 'Draft' NULL ,submit_text varchar(255)  DEFAULT 'Submit' NULL ,satisfied_text varchar(255)  DEFAULT 'Satisfied' NULL ,neither_text varchar(255)  DEFAULT 'Neither Satisfied nor Dissatisfied' NULL ,dissatisfied_text varchar(255)  DEFAULT 'Dissatisfied' NULL  , PRIMARY KEY (id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : surveyquestionresponses */
/* MISSING TABLE: surveyquestionresponses */
CREATE TABLE surveyquestionresponses (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 ,answer text  NULL ,answer_bool bool  NULL ,answer_datetime datetime  NULL ,surveyquestion_id char(36)  NULL ,surveyresponse_id char(36)  NULL  , PRIMARY KEY (id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : surveyquestions */
/* MISSING TABLE: surveyquestions */
CREATE TABLE surveyquestions (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 ,sort_order int(255)  NULL ,type varchar(100)  NULL ,happiness_question bool  NULL ,survey_id char(36)  NULL  , PRIMARY KEY (id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : surveyquestionoptions */
/* MISSING TABLE: surveyquestionoptions */
CREATE TABLE surveyquestionoptions (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 ,sort_order int(255)  NULL ,survey_question_id char(36)  NULL  , PRIMARY KEY (id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* Table : folders */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - name -  ROW [name] => 'name'  [type] => 'varchar'  [len] => '25'  */
/* VARDEF - name -  ROW[name] => 'name'  [type] => 'varchar'  [len] => '255'  [required] => ''  [dbType] => 'varchar'  */
/* INDEXES */
ALTER TABLE folders   modify COLUMN name varchar(255)  NULL ;
/* Table : securitygroups_records */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - module -  ROW [name] => 'module'  [type] => 'varchar'  [len] => '255'  */
/* VARDEF - module -  ROW[name] => 'module'  [type] => 'char'  [len] => '36'  [dbType] => 'char'  [required] => ''  */
/* INDEXES */
ALTER TABLE securitygroups_records   modify COLUMN module char(36)  NULL ;
/* Table : surveyquestionoptions_surveyquestionresponses */
/* MISSING TABLE: surveyquestionoptions_surveyquestionresponses */
CREATE TABLE surveyquestionoptions_surveyquestionresponses (id varchar(36)  NOT NULL ,date_modified datetime  NULL ,deleted bool  DEFAULT '0' NULL ,surveyq72c7options_ida varchar(36)  NULL ,surveyq10d4sponses_idb varchar(36)  NULL  , PRIMARY KEY (id),   KEY surveyquestionoptions_surveyquestionresponses_alt (surveyq72c7options_ida, surveyq10d4sponses_idb)) CHARACTER SET utf8 COLLATE utf8_general_ci;

slavenglaafs · 16 July 2019 17:18

Hi Guys,

I have opened up another thread since the version of SuiteCRM and upgrade are different. Sorry for the inconvenience.