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:

Fullscreen

1
2
3
4
5
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]:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

0 Jeff Bickart over 3 years ago

The column acl_team_set_id does exist in Sugar 11.0.x

Sugar 11.0.3

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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       |       |
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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       |       |
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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?

Reply

0 Jeff Bickart over 3 years ago

The column acl_team_set_id does exist in Sugar 11.0.x

Sugar 11.0.3

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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       |       |
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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       |       |
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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?

Children

0 Jeroen Somhorst over 3 years ago in reply to Jeff Bickart

I just checked with a local 10.x instance and it seems that in this version that specific column doesn't exist yet. So I think that something has gone wrong on the upgrade. But what I don't understand is that if I do a repair this specific column doesn't get added since it is missing (and a key column)?
0 Francesca Shiekh over 3 years ago in reply to Jeff Bickart

Interesting, my outbound_email table is also missing the acl_team_set_id

Though I'm on 11.0.2 Professional not Ent.