Upgrading 4.0.8 -> 4.2.0 breaking outgoing email

RT Users
1

Hi All –

After an upgrade to 4.2.0 our instance of RT will not send out email. I don’t even get the “Outgoing email recorded” message in a ticket, so I assumed a problem with scrips. I’ve created new post-upgrade scrips for the sole purpose of emailing out, but doesn’t work. I’ve also successfully tested emails from the same host on CLI using mailx (works), changing MailCommand from “sendmailpipe” to “sendmail” (doesn’t work), rolling back to our 4.0.8 codebase (works). No problems with incoming email.

Help? If this is in the wrong channel please let me know.

2

After an upgrade to 4.2.0 our instance of RT will not send out email.
I don’t even get the “Outgoing email recorded” message in a ticket, so
I assumed a problem with scrips. I’ve created new post-upgrade scrips
for the sole purpose of emailing out, but doesn’t work.

Increase RT’s logging level to ‘debug’, and show the logs from an update
you would expect to send mail.

  • Alex
3

Thanks for the tip Alex – this is what I tried at first and there were
no suspicious messages which made me think that somehow I have a flag
turned off or something else.

Please keep your replies on-list. It is quite possible you missed
something in the logs – unless you show me them, as I asked, there is
nothing I can do to help.

  • Alex
4

Thanks Alex – what’s attached is me logging in and performing one correspond in which I expect an email. Hostnames/IPs/db names have been anonymized.

J. Cory Wright, RHCE | SRA Contractor | Mobile: 404-268-3402 | Email: nextgensupport@cdc.gov | Ticket: scicompsupport@mail.biotech.cdc.gov-----Original Message-----
From: Alex Vandiver [mailto:alexmv@bestpractical.com]
Sent: Friday, October 25, 2013 4:57 PM
To: Wright, Cory (CDC/OID/NCIRD) (CTR)
Cc: rt-users@lists.bestpractical.com
Subject: RE: [rt-users] Upgrading 4.0.8 → 4.2.0 breaking outgoing email

On Fri, 2013-10-25 at 20:38 +0000, Wright, Cory (CDC/OID/NCIRD) (CTR) wrote:

Thanks for the tip Alex – this is what I tried at first and there
were no suspicious messages which made me think that somehow I have a
flag turned off or something else.

Please keep your replies on-list. It is quite possible you missed something in the logs – unless you show me them, as I asked, there is nothing I can do to help.

  • Alex

rt-debug.log.gz (5.97 KB)

5

Thanks Alex – what’s attached is me logging in and performing one
correspond in which I expect an email. Hostnames/IPs/db names have
been anonymized.

Your logs are chock full of SQL statement execution errors, which should
have been difficult to miss.
Double-check that you have the sufficient version for all of RT’s
dependencies. Specifically, what version of DBIx::SearchBuilder are you
running? You can determine this by going to Admin → Tools → System
Configuration, and searching for “DBIx::SearchBuilder” in the “Loaded
perl modules” section.

  • Alex
6

DBIx::SearchBuilder 1.65 /usr/local/share/perl5/DBIx/SearchBuilder.pm

make testdeps indicated no problems, nor did make upgrade-database.

J. Cory Wright, RHCE | SRA Contractor | Mobile: 404-268-3402 | Email: nextgensupport@cdc.gov | Ticket: scicompsupport@mail.biotech.cdc.gov-----Original Message-----
From: Alex Vandiver [mailto:alexmv@bestpractical.com]
Sent: Monday, October 28, 2013 12:10 PM
To: Wright, Cory (CDC/OID/NCIRD) (CTR)
Cc: rt-users@lists.bestpractical.com
Subject: RE: [rt-users] Upgrading 4.0.8 → 4.2.0 breaking outgoing email

On Mon, 2013-10-28 at 12:58 +0000, Wright, Cory (CDC/OID/NCIRD) (CTR) wrote:

Thanks Alex – what’s attached is me logging in and performing one
correspond in which I expect an email. Hostnames/IPs/db names have
been anonymized.

Your logs are chock full of SQL statement execution errors, which should have been difficult to miss.
Double-check that you have the sufficient version for all of RT’s dependencies. Specifically, what version of DBIx::SearchBuilder are you running? You can determine this by going to Admin → Tools → System Configuration, and searching for “DBIx::SearchBuilder” in the “Loaded perl modules” section.

  • Alex
7

After some further troubleshooting I have determined that email from the upgraded 4.2.0 instance is in fact working (testing with dashboard subs), but that no scrips execute. The bulk of errors in my debug log are:
[6973] [Tue Oct 29 21:24:15 2013] [warning]: DBD::mysql::st execute failed: ‘rt_helpdesk_420.main.Name’ isn’t in GROUP BY at /usr/local/share/perl5/DBIx/SearchBuilder/Handle.pm line 589. (/usr/local/share/perl5/
DBIx/SearchBuilder/Handle.pm:589)
[6973] [Tue Oct 29 21:24:15 2013] [warning]: RT::Handle=HASH(0x7fd5eea7f588) couldn’t execute the query 'SELECT main.* FROM CustomFields main JOIN ObjectCustomFields ObjectCustomFields_1 ON ( ObjectCustomFields
_1.CustomField = main.id ) WHERE (ObjectCustomFields_1.ObjectId = ‘1’ OR ObjectCustomFields_1.ObjectId = ‘0’) AND (main.Disabled = ‘0’) AND (main.LookupType = ‘RT::Queue-RT::Ticket’) AND (main.id = ‘0’) GROUP
BY main.id ORDER BY MIN(ObjectCustomFields_1.SortOrder) ASC ’ at /usr/local/share/perl5/DBIx/SearchBuilder/Handle.pm line 602.

I am unable to determine the impact of these warnings or if they are the culprit. Full log can also be seen in previous communication.From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Wright, Cory (CDC/OID/NCIRD) (CTR)
Sent: Monday, October 28, 2013 12:17 PM
To: ‘Alex Vandiver’
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Upgrading 4.0.8 → 4.2.0 breaking outgoing email

DBIx::SearchBuilder 1.65 /usr/local/share/perl5/DBIx/SearchBuilder.pm

make testdeps indicated no problems, nor did make upgrade-database.

8

After some further troubleshooting I have determined that email from the upgraded 4.2.0
instance is in fact working (testing with dashboard subs), but that no scrips execute. The
bulk of errors in my debug log are:
[6973] [Tue Oct 29 21:24:15 2013] [warning]: DBD::mysql::st execute failed:
‘rt_helpdesk_420.main.Name’ isn’t in GROUP BY at
/usr/local/share/perl5/DBIx/SearchBuilder/Handle.pm line 589. (/usr/local/share/perl5/
DBIx/SearchBuilder/Handle.pm:589)
[6973] [Tue Oct 29 21:24:15 2013] [warning]: RT::Handle=HASH(0x7fd5eea7f588) couldn’t execute
the query 'SELECT main.* FROM CustomFields main JOIN ObjectCustomFields ObjectCustomFields_1
ON ( ObjectCustomFields
_1.CustomField = main.id ) WHERE (ObjectCustomFields_1.ObjectId = ‘1’ OR
ObjectCustomFields_1.ObjectId = ‘0’) AND (main.Disabled = ‘0’) AND (main.LookupType =
‘RT::Queue-RT::Ticket’) AND (main.id = ‘0’) GROUP
BY main.id ORDER BY MIN(ObjectCustomFields_1.SortOrder) ASC ’ at
/usr/local/share/perl5/DBIx/SearchBuilder/Handle.pm line 602.

I am unable to determine the impact of these warnings or if they are the culprit. Full log
can also be seen in previous communication.

These warnings are almost certainly a culprit.

What version of mysql and what non-standard options are you using?
On a stock 5.5.27 your query runs fine.

-kevin

9

Kevin

Version:

mysql Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1

all data locations pointed to a 160GiB FusionIO.

and various mysql options:

max_allowed_packet = 16M

max_connect_errors = 1000000

skip_name_resolve

sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_Z ERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

sysdate_is_now = 1

innodb = FORCE

#innodb_strict_mode = 1

tmp_table_size = 32M

max_heap_table_size = 32M

query_cache_type = 0

query_cache_size = 0

max_connections = 500

thread_cache_size = 50

open_files_limit = 65535

table_definition_cache = 1024

table_open_cache = 2048

innodb_flush_method = O_DIRECT

innodb_log_files_in_group = 2

innodb_log_file_size = 512M

innodb_flush_log_at_trx_commit = 1

innodb_file_per_table = 1

innodb_buffer_pool_size = 24G-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Kevin Falcone
Sent: Wednesday, October 30, 2013 3:20 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Upgrading 4.0.8 → 4.2.0 breaking outgoing email

On Tue, Oct 29, 2013 at 09:32:20PM +0000, Wright, Cory (CDC/OID/NCIRD) (CTR) wrote:

After some further troubleshooting I have determined that email from the upgraded 4.2.0

instance is in fact working (testing with dashboard subs), but that no scrips execute. The

bulk of errors in my debug log are:

[6973] [Tue Oct 29 21:24:15 2013] [warning]: DBD::mysql::st execute failed:

‘rt_helpdesk_420.main.Name’ isn’t in GROUP BY at

/usr/local/share/perl5/DBIx/SearchBuilder/Handle.pm line 589. (/usr/local/share/perl5/

DBIx/SearchBuilder/Handle.pm:589)

[6973] [Tue Oct 29 21:24:15 2013] [warning]: RT::Handle=HASH(0x7fd5eea7f588) couldn’t execute

the query 'SELECT main.* FROM CustomFields main JOIN ObjectCustomFields ObjectCustomFields_1

ON ( ObjectCustomFields

_1.CustomField = main.id ) WHERE (ObjectCustomFields_1.ObjectId = ‘1’ OR

ObjectCustomFields_1.ObjectId = ‘0’) AND (main.Disabled = ‘0’) AND (main.LookupType =

‘RT::Queue-RT::Ticket’) AND (main.id = ‘0’) GROUP

BY main.id ORDER BY MIN(ObjectCustomFields_1.SortOrder) ASC ’ at

/usr/local/share/perl5/DBIx/SearchBuilder/Handle.pm line 602.

I am unable to determine the impact of these warnings or if they are the culprit. Full log

can also be seen in previous communication.

These warnings are almost certainly a culprit.

What version of mysql and what non-standard options are you using?

On a stock 5.5.27 your query runs fine.

-kevin

10

These warnings are almost certainly a culprit.

Can you show your CustomFields definitions?

    SHOW CREATE TABLE CustomFields;
    SHOW INDEXES FROM CustomFields;
  • Alex
11

mysql> show create table CustomFields;

±-------------±-(dashes truncated)-+

| Table | Create Table |

±-------------±-(dashes truncated)-+

| CustomFields | CREATE TABLE CustomFields (

id int(11) NOT NULL AUTO_INCREMENT,

Name varchar(200) DEFAULT NULL,

Type varchar(200) CHARACTER SET ascii DEFAULT NULL,

RenderType varchar(64) CHARACTER SET ascii DEFAULT NULL,

MaxValues int(11) DEFAULT NULL,

Pattern text,

BasedOn int(11) DEFAULT NULL,

ValuesClass varchar(64) CHARACTER SET ascii DEFAULT NULL,

Description varchar(255) DEFAULT NULL,

SortOrder int(11) NOT NULL DEFAULT ‘0’,

LookupType varchar(255) CHARACTER SET ascii NOT NULL,

Creator int(11) NOT NULL DEFAULT ‘0’,

Created datetime DEFAULT NULL,

LastUpdatedBy int(11) NOT NULL DEFAULT ‘0’,

LastUpdated datetime DEFAULT NULL,

Disabled smallint(6) NOT NULL DEFAULT ‘0’,

PRIMARY KEY (id)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

±-------------±-(dashes truncated)-+

1 row in set (0.00 sec)

mysql> show indexes from CustomFields;

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

| CustomFields | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | |

1 row in set (0.00 sec)

mysql>

On Wed, 2013-10-30 at 12:20 -0700, Kevin Falcone wrote:

These warnings are almost certainly a culprit.

Can you show your CustomFields definitions?

    SHOW CREATE TABLE CustomFields;

    SHOW INDEXES FROM CustomFields;
  • Alex
12

sql_mode = […],ONLY_FULL_GROUP_BY

That would be your problem. RT doesn’t support running with that option
enabled.

  • Alex
13

This did the trick, thanks Alex!-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Alex Vandiver
Sent: Wednesday, October 30, 2013 8:20 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Upgrading 4.0.8 → 4.2.0 breaking outgoing email

On Wed, 2013-10-30 at 19:30 +0000, Wright, Cory (CDC/OID/NCEZID) (CTR) wrote:

sql_mode = […],ONLY_FULL_GROUP_BY

That would be your problem. RT doesn’t support running with that option enabled.

  • Alex