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 · Accepted solution]

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

[RoBo]

Hello Andy, you've nailed this issue to the spot! Although this sounds quite logical, I was not able to relate that myself somehow. Very much appreciated.
The table is auto-created and being filled. So good self healing job there Atlassian!
(however a warning for incorrect database permission scheme would have been appreciated here)

Argh.. I've updated Jira from 8.3.2 to 8.4.1 and now the option '> System > Batching email notifications' is gone???

Ha, that one was rather easy: The following system plugins had to be enabled:

Jira inform - batchers
Jira inform - batching plugin

[Stefan Wendelmann]

Having the same Problem with our Jira Server.

But we use a MS SQL Server and it never worked, and we did not do a migration.

The Log says:

020-01-16 17:22:56,778+0100 plugin-transaction-0 INFO      [c.a.jira.plugin.PluginTransactionListener] [plugin-transaction] numberStartEvents:573, numberEndEvents:573, numberSendEvents:369, numberEventsInTransactions:11830, numberOfPluginEnableEvents:274
2020-01-16 17:23:07,672+0100 Caesium-1-3 ERROR ServiceRunner     [c.a.j.p.inform.performance.MeasurementWorkerFactory] Exception occured
com.querydsl.core.QueryException: Caught SQLServerException for select "AO_733371_EVENT"."ID", "AO_733371_EVENT"."EVENT_TYPE", "AO_733371_EVENT"."USER_KEY", "AO_733371_EVENT"."ACTION", "AO_733371_EVENT"."ACTION_ID", "AO_733371_EVENT"."EVENT_BUNDLE_ID", "AO_733371_EVENT"."CREATED" from "null"."AO_733371_EVENT" "AO_733371_EVENT" where exists (select "AO_733371_EVENT_RECIPIENT"."ID" from "null"."AO_733371_EVENT_RECIPIENT" "AO_733371_EVENT_RECIPIENT" where "AO_733371_EVENT_RECIPIENT"."EVENT_ID" = "AO_733371_EVENT"."ID" and "AO_733371_EVENT_RECIPIENT"."STATUS" = ? and "AO_733371_EVENT_RECIPIENT"."CONSUMER_NAME" = ? and "AO_733371_EVENT_RECIPIENT"."SEND_DATE" <= ?) and ("AO_733371_EVENT"."EVENT_TYPE" in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) and "AO_733371_EVENT"."ID" >= ? order by "AO_733371_EVENT"."ID" asc offset ? rows fetch next ? rows only
 at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
 at com.querydsl.sql.Configuration.translate(Configuration.java:459)
 at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502)
 at com.atlassian.jira.plugins.inform.events.managers.EventManagerImpl.lambda$search$6(EventManagerImpl.java:276)
 at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$0(DatabaseAccessorImpl.java:69)
 at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:87)
 at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.lambda$execute$0(DefaultTransactionalExecutor.java:43)
 at com.atlassian.jira.database.DatabaseAccessorImpl.runInManagedTransaction(DatabaseAccessorImpl.java:131)
 ... 2 filtered
 at java.lang.reflect.Method.invoke(Method.java:498)
 at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
 at com.sun.proxy.$Proxy401.runInManagedTransaction(Unknown Source)
 ... 2 filtered
 at java.lang.reflect.Method.invoke(Method.java:498)
 at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:131)
 at com.sun.proxy.$Proxy401.runInManagedTransaction(Unknown Source)
 at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.runInStartedOrExistingTransaction(JiraHostConnectionAccessor.java:130)
 at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.execute(JiraHostConnectionAccessor.java:60)
 at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.execute(DefaultTransactionalExecutor.java:40)
 at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.execute(DatabaseAccessorImpl.java:67)
 at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:43)
 at com.atlassian.jira.plugins.inform.events.managers.EventManagerImpl.search(EventManagerImpl.java:252)
 ... 3 filtered
 at java.lang.reflect.Method.invoke(Method.java:498)
 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
 at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
 at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
 at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
 at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
 at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
 at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
 at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
 at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
 at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
 at com.sun.proxy.$Proxy1382.search(Unknown Source)
 at com.atlassian.jira.plugins.inform.batching.cron.BatchNotificationJob.lambda$searchEvents$10(BatchNotificationJob.java:266)
 at com.atlassian.jira.plugins.inform.performance.MeasurementWorkerFactory$1.measure(MeasurementWorkerFactory.java:39)
 at com.atlassian.jira.plugins.inform.batching.cron.BatchNotificationJob.searchEvents(BatchNotificationJob.java:264)
 at com.atlassian.jira.plugins.inform.batching.cron.BatchNotificationJob.lambda$notifyUsers$1(BatchNotificationJob.java:112)
 at com.atlassian.jira.plugins.inform.performance.MeasurementWorkerFactory$1.measure(MeasurementWorkerFactory.java:39)
 at com.atlassian.jira.plugins.inform.batching.cron.BatchNotificationJob.notifyUsers(BatchNotificationJob.java:109)
 at com.atlassian.jira.plugins.inform.batching.cron.BatchNotificationJob.lambda$runJob$0(BatchNotificationJob.java:86)
 at com.atlassian.jira.plugins.inform.performance.MeasurementWorkerFactory$1.measure(MeasurementWorkerFactory.java:39)
 at com.atlassian.jira.plugins.inform.batching.cron.BatchNotificationJob.runJob(BatchNotificationJob.java:84)
 at com.atlassian.jira.plugins.inform.batching.cron.ConditionalJobRunner.runJob(ConditionalJobRunner.java:33)
 at com.atlassian.jira.plugins.inform.batching.cron.ConditionalJobRunner.runJob(ConditionalJobRunner.java:33)
 at com.atlassian.jira.plugins.inform.batching.cron.OncePerClusterJobRunner.runJob(OncePerClusterJobRunner.java:46)
 at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:134)
 at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:106)
 at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:90)
 at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:435)
 at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJob(CaesiumSchedulerService.java:430)
 at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJobWithRecoveryGuard(CaesiumSchedulerService.java:454)
 at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:382)
 at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:66)
 at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:60)
 at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:35)
 at java.lang.Thread.run(Thread.java:748)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Ung�ltiger Objektname "null.AO_733371_EVENT".
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:592)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
 at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:42)
 at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeQuery$5(DiagnosticPreparedStatement.java:59)
 at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:69)
 at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:55)
 at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeQuery(DiagnosticPreparedStatement.java:59)
 at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:446)
 ... 65 more