How to debug email pulling process With AO table in a postgre database ?

Question

Dear All,

We use : JIRA Service Desk 4.3.4 / Jira Core 8.3.4

We face issue with the sending of notification when some comment are added inside an issue.

2 comments from a user hasn't generate any notification then no email.

Our outgoing email is in debug mode but we have nothing (neither error message or others about the 2 missing comments) inside atlassian-jira-outgoing-mail.log logs file.

In order to investigate further I try to check inside postgre Jira DB

I have found some notification link to this user and this issue inside table  notificationinstance (and in that table I see more than 500 lines and some about old issue --> Is it normal ?),

I see that the job com.atlassian.jira.internal.mail.services.MailPullerJobRunner is running well inside table rundetails

I would like to investigate further looking inside tables linked to pluggin : com.atlassian.jira.jira-email-processor-plugin

ie :

AO_2C4E5C_MAILITEMCHUNK
AO_2C4E5C_MAILGLOBALHANDLER
AO_2C4E5C_MAILHANDLER
AO_2C4E5C_MAILCHANNEL
AO_2C4E5C_MAILCONNECTION
AO_2C4E5C_MAILITEMAUDIT
AO_2C4E5C_MAILRUNAUDIT
AO_2C4E5C_MAILITEM

But I can't do any select on it as I got the error message :

jiradb=> select * from AO_2C4E5C_MAILITEMCHUNK;
ERROR: relation "ao_2c4e5c_mailitemchunk" does not exist
LINE 1: select * from AO_2C4E5C_MAILITEMCHUNK;

Is there a way to do such select ?

Is someone has face same kind of issue ? can you please help me to secure this functionality as it is important for us avoid to miss some notification ?

Thank you for your support!

Logan Hawkes · Answer

I ran into something similar:

// First try:
jira=> select * from AO_2C4E5C_MAILHANDLER;

ERROR:  relation "ao_2c4e5c_mailhandler" does not exist

LINE 1: select * from AO_2C4E5C_MAILHANDLER;

^
// Second Try: add quotes, no semicolon
jira=> select * from "AO_2C4E5C_MAILITEMCHUNK"

// Third Try: wha? Nothing happened. Hit up arrow and add the semicolon.
jira-> select * from "AO_2C4E5C_MAILITEMCHUNK";

ERROR:  syntax error at or near "select"

LINE 2: select * from "AO_2C4E5C_MAILITEMCHUNK";

^
// Fourth attempt: Line 2? What the what?
jira=> select * from "AO_2C4E5C_MAILITEMCHUNK";

ID | MAIL_ITEM_ID | MIME_MSG_CHUNK | MIME_MSG_CHUNK_IDX

----+--------------+----------------+--------------------

(0 rows)

// Success!

Things to notice:

In the first attempt I didn't put double quotes around the table name, so the table wasn't found.

In the second attempt I forgot to add a semicolon. That's important because it meant that the command was sitting in the buffer waiting for a semicolon to indicate completion.

In the third attempt I added the semicolon, but since there was already a command in the buffer it tried to execute both and gave me an error on line 2.

In the fourth attempt I had a clear buffer, double-quotes, and a semicolon at the end. Everything worked fine.

Brant Schroeder · Answer

If you do the following what happens: select * from "AO_2C4E5C_MAILITEMCHUNK"