Error during upgrade 11.0.3 to 12

Hi,

I'm currently in the process of upgrading my local development instance of a customer from 11.0.3 Enterprise to 12.0.0. The problem currenty is that it fails after the healtcheck (or during, the logs arent really clear) with the following SQL error: 

Mon, 02 May 2022 12:32:13 +0000 [Upgrader] - ERROR: Exception: An exception occurred while executing 'SELECT outbound_email.id, outbound_email.eapm_id, outbound_email.name, outbound_email.type, outbound_email.user_id, outbound_email
.email_address_id, outbound_email.authorized_account, outbound_email.mail_authtype, outbound_email.reply_to_name, outbound_email.reply_to_email_address_id, outbound_email.mail_sendtype, outbound_email.mail_smtptype, outbound_email.m
ail_smtpserver, outbound_email.mail_smtpport, outbound_email.mail_smtpuser, outbound_email.mail_smtppass, outbound_email.mail_smtpauth_req, outbound_email.mail_smtpssl, outbound_email.deleted, outbound_email.team_id, outbound_email.
team_set_id, outbound_email.acl_team_set_id, teams_tn.name tn_name, teams_tn.name_2 tn_name_2 FROM outbound_email LEFT JOIN teams teams_tn ON (outbound_email.team_id = teams_tn.id) AND (teams_tn.deleted = ?) WHERE (outbound_email.id
 = ?) AND (outbound_email.deleted = ?) LIMIT 1' with params [0, "d717dbfa-e3dc-11eb-b061-0242ac170007", 0]:

I just checked the database prior upgrading to 12.0 and indeed this table does not have a acl_team_set_id column. When I truncate that specific table and start the upgrade all seems to work as expected. But whenever I import the data again and do a QRR the application is broken with the same error. 

Did someone see this behaviour in the past? 

Parents
  • The column acl_team_set_id does exist in Sugar 11.0.x

    Sugar 11.0.3

    desc outbound_email;
    +---------------------------+--------------+------+-----+---------+-------+
    | Field                     | Type         | Null | Key | Default | Extra |
    +---------------------------+--------------+------+-----+---------+-------+
    | id                        | char(36)     | NO   | PRI | NULL    |       |
    | eapm_id                   | char(36)     | YES  |     | NULL    |       |
    | name                      | varchar(255) | YES  |     | NULL    |       |
    | type                      | varchar(15)  | YES  | MUL | user    |       |
    | user_id                   | char(36)     | NO   |     | NULL    |       |
    | email_address_id          | char(36)     | YES  |     | NULL    |       |
    | authorized_account        | varchar(255) | YES  |     | NULL    |       |
    | mail_authtype             | varchar(10)  | YES  |     | NULL    |       |
    | reply_to_name             | varchar(255) | YES  |     | NULL    |       |
    | reply_to_email_address_id | char(36)     | YES  |     | NULL    |       |
    | mail_sendtype             | varchar(8)   | YES  |     | SMTP    |       |
    | mail_smtptype             | varchar(20)  | YES  |     | other   |       |
    | mail_smtpserver           | varchar(100) | YES  |     | NULL    |       |
    | mail_smtpport             | int(5)       | YES  |     | 465     |       |
    | mail_smtpuser             | varchar(100) | YES  |     | NULL    |       |
    | mail_smtppass             | varchar(255) | YES  |     | NULL    |       |
    | mail_smtpauth_req         | tinyint(1)   | YES  |     | 0       |       |
    | mail_smtpssl              | int(1)       | YES  |     | 1       |       |
    | deleted                   | tinyint(1)   | YES  |     | 0       |       |
    | team_id                   | char(36)     | YES  |     | NULL    |       |
    | team_set_id               | char(36)     | YES  | MUL | NULL    |       |
    | acl_team_set_id           | char(36)     | YES  | MUL | NULL    |       |
    +---------------------------+--------------+------+-----+---------+-------+
    

    Sugar 12.0.0

    mysql> desc outbound_email;
    +---------------------------+--------------+------+-----+---------+-------+
    | Field                     | Type         | Null | Key | Default | Extra |
    +---------------------------+--------------+------+-----+---------+-------+
    | id                        | char(36)     | NO   | PRI | NULL    |       |
    | eapm_id                   | char(36)     | YES  |     | NULL    |       |
    | name                      | varchar(255) | YES  |     | NULL    |       |
    | type                      | varchar(15)  | YES  | MUL | user    |       |
    | user_id                   | char(36)     | NO   |     | NULL    |       |
    | email_address_id          | char(36)     | YES  |     | NULL    |       |
    | authorized_account        | varchar(255) | YES  |     | NULL    |       |
    | mail_authtype             | varchar(10)  | YES  |     | NULL    |       |
    | reply_to_name             | varchar(255) | YES  |     | NULL    |       |
    | reply_to_email_address_id | char(36)     | YES  |     | NULL    |       |
    | mail_sendtype             | varchar(8)   | YES  |     | SMTP    |       |
    | mail_smtptype             | varchar(20)  | YES  |     | other   |       |
    | mail_smtpserver           | varchar(100) | YES  |     | NULL    |       |
    | mail_smtpport             | int(5)       | YES  |     | 465     |       |
    | mail_smtpuser             | varchar(100) | YES  |     | NULL    |       |
    | mail_smtppass             | varchar(255) | YES  |     | NULL    |       |
    | mail_smtpauth_req         | tinyint(1)   | YES  |     | 0       |       |
    | mail_smtpssl              | int(1)       | YES  |     | 1       |       |
    | preferred_sending_account | tinyint(1)   | YES  |     | 0       |       |
    | deleted                   | tinyint(1)   | YES  |     | 0       |       |
    | team_id                   | char(36)     | YES  |     | NULL    |       |
    | team_set_id               | char(36)     | YES  | MUL | NULL    |       |
    | acl_team_set_id           | char(36)     | YES  | MUL | NULL    |       |
    +---------------------------+--------------+------+-----+---------+-------+
    

    If you look in the sugarcrm.log is there more information about the error?

  • Interesting, my outbound_email table is also missing the acl_team_set_id

    Though I'm on 11.0.2 Professional not Ent.

  • Aha, could it be an issue when converting an instance onsite from pro to enterprise? The instance I was upgrading was originally also a pro and when upgrading to 12 you have to convert it to a ENT flavor (see: https://sugarclub.sugarcrm.com/explore/product-updates/b/enterprise-professional-updates/posts/upgrade-paths-for-on-site-installations). 

    do you have an idea?

  • Hi ,

    Yes if you're in a pro edition going to 12, you must first, convert from 11.0.3 PRO to 11.0.3 ENT by following the steps here. (which basically is download 11.0.3 conversion package from Download store).

    Once you have 11.0.3 Enterprise up and running, then you can move forward with your upgrade 11.0.3 Enterprise to 12.0.0.. 

    rafa

    SugarCRM | Principal Developer Advocate

Reply Children
  • Thanks for the answer but that didn't actually answer the question. The issue that I faced was that at some point the process stops because the acl_team_set_id column wasn't found on the outbound_email table. This should have been fixed when upgrading but for some reason doesn't. I'm currently trying to determine where that problem started. Could it be that when upgrading from 10.0.x to 11.0.x the tables arent correctly altered? 

  • Apologies if I wasn't clear but what you're doing is not a valid/supported path therefore you might face even more issues that we don't know about.

    Yes, it will fail because that field isn't there, most likely because it is expected by ENT edition and not available on PRO. By converting to ENT, it will create that field (along with many other tables+files) so the upgrade from 11 to 12 can be carried on.

    As Andre pointed out, if you follow the instructions you should not face any issues.

    SugarCRM | Principal Developer Advocate