Jump to content


Photo

Upgrade 7.0 -> 8.0 db error

database error

  • Please log in to reply
2 replies to this topic

#1 emmalone

emmalone

    Member

  • Members
  • PipPip
  • 10 posts

Posted 02 January 2020 - 01:01 PM

Hello,

 

I am trying to upgrade from 7.0 to 8.0 and running into the following issue.

 

Before upgrading, I was warned of Compatibility issues detected.

 

The following preexisting fields conflict with fields to be added:

Accounts
  • alternativeEmail
  • businessEmail
  • personalEmail
  • preferredEmail
Contacts
  • alternativeEmail
  • businessEmail
  • personalEmail
  • preferredEmail
Opportunity
  • alternativeEmail
  • businessEmail
  • personalEmail
  • preferredEmail
X2Leads
  • alternativeEmail
  • businessEmail
  • personalEmail
  • preferredEmail

So, I ensured that I have good backups and attempted to upgrade anyway.  No luck, I got the following error.

 

Exception: "A database change failed to apply: ALTER TABLE `x2_accounts` ADD COLUMN `businessEmail` varchar(250) NULL AFTER `doNotEmail`, ADD COLUMN `personalEmail` varchar(250) NULL AFTER `businessEmail`, ADD COLUMN `alternativeEmail` varchar(250) NULL AFTER `personalEmail`, ADD COLUMN `preferredEmail` varchar(250) NULL AFTER `alternativeEmail`;. The error message given was: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'businessEmail'
A database change failed to apply: ALTER TABLE `x2_accounts` ADD COLUMN `businessEmail` varchar(250) NULL AFTER `doNotEmail`, ADD COLUMN `personalEmail` varchar(250) NULL AFTER `businessEmail`, ADD COLUMN `alternativeEmail` varchar(250) NULL AFTER `personalEmail`, ADD COLUMN `preferredEmail` varchar(250) NULL AFTER `alternativeEmail`;. The error message given was: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name &#039;businessEmail&#039;<br />To restore the database to its previous state, use the database dump file update_backup.sql stored in protected/data<br /><br />Update failed.

 

I then connected directly to the database, all of these tables had these columns with NULL values.  So, I manually dropped the columns and attempted the upgrade again.

 

New Error:

 

Exception: "A database change failed to apply: INSERT INTO `x2_fields` (`attributeLabel`,`custom`,`data`,`defaultValue`,`description`,`fieldName`,`isVirtual`,`keyType`,`linkType`,`modelName`,`modified`,`readOnly`,`relevance`,`required`,`safe`,`searchable`,`type`,`uniqueConstraint`) VALUES ('Preferred Email',0,NULL,NULL,NULL,'preferredEmail',0,NULL,'-1','Contacts',0,0,'',0,1,0,'dropdown',0),('Alternative Email',0,NULL,NULL,NULL,'alternativeEmail',0,'MUL',NULL,'X2Leads',0,0,'Medium',0,1,1,'email',0),('Personal Email',0,NULL,NULL,NULL,'personalEmail',0,'MUL',NULL,'Contacts',0,0,'Medium',0,1,1,'email',0),('Personal Email',0,NULL,NULL,NULL,'personalEmail',0,'MUL',NULL,'X2Leads',0,0,'Medium',0,1,1,'email',0),('Business Email',0,NULL,NULL,NULL,'businessEmail',0,'MUL',NULL,'X2Leads',0,0,'Medium',0,1,1,'email',0),('Preferred Email',0,NULL,NULL,NULL,'preferredEmail',0,NULL,'-1','Accounts',0,0,'',0,1,0,'dropdown',0),('Personal Email',0,NULL,NULL,NULL,'personalEmail',0,'MUL',NULL,'Accounts',0,0,'Medium',0,1,1,'email',0),('Alternative Email',0,NULL,NULL,NULL,'alternativeEmail',0,'MUL',NULL,'Contacts',0,0,'Medium',0,1,1,'email',0),('Business Email',0,NULL,NULL,NULL,'businessEmail',0,'MUL',NULL,'Accounts',0,0,'Medium',0,1,1,'email',0),('Business Email',0,NULL,NULL,NULL,'businessEmail',0,'MUL',NULL,'Opportunity',0,0,'Medium',0,1,1,'email',0),('Preferred Email',0,NULL,NULL,NULL,'preferredEmail',0,NULL,'-1','Opportunity',0,0,'',0,1,0,'dropdown',0),('Personal Email',0,NULL,NULL,NULL,'personalEmail',0,'MUL',NULL,'Opportunity',0,0,'Medium',0,1,1,'email',0),('Business Email',0,NULL,NULL,NULL,'businessEmail',0,'MUL',NULL,'Contacts',0,0,'Medium',0,1,1,'email',0),('Alternative Email',0,NULL,NULL,NULL,'alternativeEmail',0,'MUL',NULL,'Opportunity',0,0,'Medium',0,1,1,'email',0),('Preferred Email',0,NULL,NULL,NULL,'preferredEmail',0,NULL,'-1','X2Leads',0,0,'',0,1,0,'dropdown',0),('Alternative Email',0,NULL,NULL,NULL,'alternativeEmail',0,'MUL',NULL,'Accounts',0,0,'Medium',0,1,1,'email',0);. 4 changes were applied prior to this failure:

  1. ALTER TABLE `x2_accounts` ADD COLUMN `businessEmail` varchar(250) NULL AFTER `doNotEmail`, ADD COLUMN `personalEmail` varchar(250) NULL AFTER `businessEmail`, ADD COLUMN `alternativeEmail` varchar(250) NULL AFTER `personalEmail`, ADD COLUMN `preferredEmail` varchar(250) NULL AFTER `alternativeEmail`;

  2. ALTER TABLE `x2_contacts` ADD COLUMN `businessEmail` varchar(250) NULL AFTER `accountName`, ADD COLUMN `personalEmail` varchar(250) NULL AFTER `businessEmail`, ADD COLUMN `alternativeEmail` varchar(250) NULL AFTER `personalEmail`, ADD COLUMN `preferredEmail` varchar(250) NULL AFTER `alternativeEmail`;

  3. ALTER TABLE `x2_opportunities` ADD COLUMN `businessEmail` varchar(250) NULL AFTER `doNotEmail`, ADD COLUMN `personalEmail` varchar(250) NULL AFTER `businessEmail`, ADD COLUMN `alternativeEmail` varchar(250) NULL AFTER `personalEmail`, ADD COLUMN `preferredEmail` varchar(250) NULL AFTER `alternativeEmail`;

  4. ALTER TABLE `x2_x2leads` ADD COLUMN `businessEmail` varchar(250) NULL AFTER `leadscore`, ADD COLUMN `personalEmail` varchar(250) NULL AFTER `businessEmail`, ADD COLUMN `alternativeEmail` varchar(250) NULL AFTER `personalEmail`, ADD COLUMN `preferredEmail` varchar(250) NULL AFTER `alternativeEmail`;


Please save the above list.

The error message given was: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Contacts-preferredEmail' for key 'modelName_2'
A database change failed to apply: INSERT INTO `x2_fields` (`attributeLabel`,`custom`,`data`,`defaultValue`,`description`,`fieldName`,`isVirtual`,`keyType`,`linkType`,`modelName`,`modified`,`readOnly`,`relevance`,`required`,`safe`,`searchable`,`type`,`uniqueConstraint`) VALUES ('Preferred Email',0,NULL,NULL,NULL,'preferredEmail',0,NULL,'-1','Contacts',0,0,'',0,1,0,'dropdown',0),('Alternative Email',0,NULL,NULL,NULL,'alternativeEmail',0,'MUL',NULL,'X2Leads',0,0,'Medium',0,1,1,'email',0),('Personal Email',0,NULL,NULL,NULL,'personalEmail',0,'MUL',NULL,'Contacts',0,0,'Medium',0,1,1,'email',0),('Personal Email',0,NULL,NULL,NULL,'personalEmail',0,'MUL',NULL,'X2Leads',0,0,'Medium',0,1,1,'email',0),('Business Email',0,NULL,NULL,NULL,'businessEmail',0,'MUL',NULL,'X2Leads',0,0,'Medium',0,1,1,'email',0),('Preferred Email',0,NULL,NULL,NULL,'preferredEmail',0,NULL,'-1','Accounts',0,0,'',0,1,0,'dropdown',0),('Personal Email',0,NULL,NULL,NULL,'personalEmail',0,'MUL',NULL,'Accounts',0,0,'Medium',0,1,1,'email',0),('Alternative Email',0,NULL,NULL,NULL,'alternativeEmail',0,'MUL',NULL,'Contacts',0,0,'Medium',0,1,1,'email',0),('Business Email',0,NULL,NULL,NULL,'businessEmail',0,'MUL',NULL,'Accounts',0,0,'Medium',0,1,1,'email',0),('Business Email',0,NULL,NULL,NULL,'businessEmail',0,'MUL',NULL,'Opportunity',0,0,'Medium',0,1,1,'email',0),('Preferred Email',0,NULL,NULL,NULL,'preferredEmail',0,NULL,'-1','Opportunity',0,0,'',0,1,0,'dropdown',0),('Personal Email',0,NULL,NULL,NULL,'personalEmail',0,'MUL',NULL,'Opportunity',0,0,'Medium',0,1,1,'email',0),('Business Email',0,NULL,NULL,NULL,'businessEmail',0,'MUL',NULL,'Contacts',0,0,'Medium',0,1,1,'email',0),('Alternative Email',0,NULL,NULL,NULL,'alternativeEmail',0,'MUL',NULL,'Opportunity',0,0,'Medium',0,1,1,'email',0),('Preferred Email',0,NULL,NULL,NULL,'preferredEmail',0,NULL,'-1','X2Leads',0,0,'',0,1,0,'dropdown',0),('Alternative Email',0,NULL,NULL,NULL,'alternativeEmail',0,'MUL',NULL,'Accounts',0,0,'Medium',0,1,1,'email',0);. 4 changes were applied prior to this failure:<ol><li>ALTER TABLE `x2_accounts` ADD COLUMN `businessEmail` varchar(250) NULL AFTER `doNotEmail`, ADD COLUMN `personalEmail` varchar(250) NULL AFTER `businessEmail`, ADD COLUMN `alternativeEmail` varchar(250) NULL AFTER `personalEmail`, ADD COLUMN `preferredEmail` varchar(250) NULL AFTER `alternativeEmail`;</li><li>ALTER TABLE `x2_contacts` ADD COLUMN `businessEmail` varchar(250) NULL AFTER `accountName`, ADD COLUMN `personalEmail` varchar(250) NULL AFTER `businessEmail`, ADD COLUMN `alternativeEmail` varchar(250) NULL AFTER `personalEmail`, ADD COLUMN `preferredEmail` varchar(250) NULL AFTER `alternativeEmail`;</li><li>ALTER TABLE `x2_opportunities` ADD COLUMN `businessEmail` varchar(250) NULL AFTER `doNotEmail`, ADD COLUMN `personalEmail` varchar(250) NULL AFTER `businessEmail`, ADD COLUMN `alternativeEmail` varchar(250) NULL AFTER `personalEmail`, ADD COLUMN `preferredEmail` varchar(250) NULL AFTER `alternativeEmail`;</li><li>ALTER TABLE `x2_x2leads` ADD COLUMN `businessEmail` varchar(250) NULL AFTER `leadscore`, ADD COLUMN `personalEmail` varchar(250) NULL AFTER `businessEmail`, ADD COLUMN `alternativeEmail` varchar(250) NULL AFTER `personalEmail`, ADD COLUMN `preferredEmail` varchar(250) NULL AFTER `alternativeEmail`;</li></ol><br />Please save the above list. <br /><br />The error message given was: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry &#039;Contacts-preferredEmail&#039; for key &#039;modelName_2&#039;<br />The database has been restored to the backup copy.<br /><br />Update failed.

Update failed." in /var/www/html/X2CRM-master/x2engine/protected/components/UpdaterBehavior.php L2227

Any suggestions would be greatly appreciated.

 

Thanks,

 

Mark

 

 

 



#2 X2Clifton

X2Clifton

    Advanced Member

  • Moderators
  • 37 posts

Posted 03 January 2020 - 09:50 AM

Hi emmalone,

 

Since it seems you already have these fields, you will probably have to update manually. This is relatively easy and this section in the wiki should help you update: http://wiki.x2crm.co...pdates_Manually

 

Let me know if there's anything else I can help with!



#3 emmalone

emmalone

    Member

  • Members
  • PipPip
  • 10 posts

Posted 04 January 2020 - 10:13 AM

Thank you very much for the link to this document, this is exactly what I needed.  I believe that I have done the manual update correctly and almost everything that I tested works.

 

Thanks,

 

Mark






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users