Batch email notification not sending email and how to restore missing tables

RoBo

Hmm, didn't mean to create a new question. This is also added to https://community.atlassian.com/t5/Jira-questions/Batch-email-notification-not-sending-email/qaq-p/1081529. 

I'm having the issue that batching email notifications does not work. Sending the separate emails is working fine. I did some debugging and my conclusion is that this is due to missing database tables, as I found in the jira log entries like:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'AO_733371_EVENT_RECIPIENT' doesn't exist.

I recently upgraded MariaDB to MySQL. I don't know if this is related.

Existing table:

• AO_733371_EVENT

Tables that do not exist within the MySQL database:

• AO_733371_EVENT_RECIPIENT
• AO_733371_EVENT_PARAMETER

Has anyone a clue how to create these 2 missing tables?

Andy Heinzer

Hi RoBo,

I understand that you recently switch the backend database that Jira is using from mariadb to Mysql, and in addition to this database migration, it seems that you are missing at least two tables that are needed for the batch notification feature.  This feature was introduced in Jira 8.0, more details in Jira Software 8.0 release notes.

What I suspect might have happened is that you might not have granted a MySQL 5.7.x database the proper permissions when making this switch.  There was a change that MySQL rolled out in 5.7.6 to require a REFERENCES permission.  This is one that creates foreign key constraints as well IIRC.   If it happens that you created the MySQL database without all the needed permissions, Jira could have been unable to create the new tables as needed during the upgrade.  There are some more details about the permission I am thinking of here in com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: REFERENCES command denied to user.

 

What I would recommend would be to first make sure that the database Jira is using is setup as expected per Connecting Jira applications to MySQL 5.7.  If we can confirm that part is setup correctly, then we can look to try to correct this failed upgradetask.   It is possible that simply restarting Jira after correcting the database permissions could correct this.  But if not, then it might be that forcing an upgrade of Jira once more could trigger another attempt to run this upgradetask.   I would not want to try to create these manually, although that might work.  I'd prefer to try to let Jira itself create this table in the database (provided we know for sure it has the correct permissions needed to do so.)

 

If I have misunderstood your situation, my apologies, please let me know more details about the specific version of Jira and MySQL you are currently using and I will try to offer more help here.

Andy