Error while trying to send campaign mailing

Use this forum to report bugs and to check for bugfixes and new releases of OpenEMM

Moderator: moderator

MSelke
Posts: 28
Joined: Thu Feb 25, 2016 11:19 am

Error while trying to send campaign mailing

Post by MSelke »

OpenEMM:

=============================================
= OpenEMM Maintenance Tool (OMT) v21.04.099 =
=============================================
Hostname: vm50312.<Hoster>.com
OpenEMM License: OpenEMM (ID: 0)
OpenEMM Runtime Version: 21.04.100
OpenEMM Version: 21.04.000.124
System-Url: https://news.<DomaiN>.com

Current menu: Show OpenEMM status

OpenEMM status:
Operating System (OS): CentOS 7
Python version: 3.9.0 (default, Dec 4 2020, 12:21:39) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)]
Postfix Version: 2.10.1
Database client version: /usr/bin/mysql Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using EditLine wrapper
Database Connection: OK
System-Time: 2021-11-18 14:59:16
System-Time-DB: 2021-11-18 14:59:16
Database version: MySQL 5.7.33
OpenEMM database structure exists (Version 21.04.018)
Database table emm_db_errorlog_tbl contains error messages: 1
Jobqueue status: OK
DKIM keys available for domains: None
Java version: 11.0.10 (Oracle)
Tomcat version: 9.0.45.0
Tomcat-Native version: 1.2.23
Wkhtml version: wkhtmltopdf 0.12.5 (with patched qt)
OpenEMM Application is running (Tomcat ProcessID: 26588, started at: Nov11 51:00)
OpenEMM Backend ok: once
OpenEMM Backend running: mailout, update, trigger, generate, mta, pickdist, slrtscn, direct-path, bav-update, bavd, bav, mlcontrol

-----------------------------------------------------

I created a campaign in OpenEMM. If a link in an mailing is clicked there should be another mailing. If i look into the campaign i can see that there is one (the test) receiver but no mail ist send.

In the Logs i get the following erros:

2021-11-10 10:08:33,895: WARN [http-nio-8080-exec-38] org.agnitas.taglib.ShowByPermissionTag - Invalid permission token: mailing.content.readonly
java.lang.Exception: Invalid security token found: mailing.content.readonly

Error: PreparedStatementCallback; bad SQL grammar [SELECT bind.customer_id FROM customer_1_tbl cust, customer_1_binding_tbl bind WHERE bind.user_type IN (?, ?, ?) AND bind.user_status = ? AND bind.mailinglist_id NOT IN (SELECT mailinglist_id FROM disabled_mailinglist_tbl WHERE admin_id = ?) AND (bind.customer_id = cust.customer_id OR bind.customer_id IS NULL) ORDER BY bind.user_type, bind.customer_id LIMIT ?]; nested exception is java.sql.SQLSyntaxErrorException: (conn=211658) Table 'openemm.disabled_mailinglist_tbl' doesn't exist
SQL: SELECT bind.customer_id FROM customer_1_tbl cust, customer_1_binding_tbl bind WHERE bind.user_type IN (?, ?, ?) AND bind.user_status = ? AND bind.mailinglist_id NOT IN (SELECT mailinglist_id FROM disabled_mailinglist_tbl WHERE admin_id = ?) AND (bind.customer_id = cust.customer_id OR bind.customer_id IS NULL) ORDER BY bind.user_type, bind.customer_id LIMIT ?
Parameter: 'T', 't', 'A', 1, 1, 1

Caused by: java.sql.SQLSyntaxErrorException: (conn=211658) Table 'openemm.disabled_mailinglist_tbl' doesn't exist

com.agnitas.emm.core.userform.service.impl.ComUserformServiceImpl - Could not generate UID for companyID/adminID 1/1

These four Errors i can find in the logs at nearly the same timestamp.

I cant find the table "openemm.disabled_mailinglist_tbl" in the sql scripts.

Is there anything i can do or provide for deeper insights?

Thank you in advance.
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Error while trying to send campaign mailing

Post by maschoff »

Please downgrade to 21.04.000.118 or upgrade to 21.10.
OpenEMM Maintainer
MSelke
Posts: 28
Joined: Thu Feb 25, 2016 11:19 am

Re: Error while trying to send campaign mailing

Post by MSelke »

I upgraded to 21.10. And the Update breaks up, because i started the upgrade from the OpenEMMInstaller.sh and at the second or third step the directory tomcat9 was expected but it was tomcat10 already. I figured out myself that i have to use OMT.sh now. I started the Upgrade from there an it runs without any other erros.

Now i have some Issues, in the frontend i see in the system status that the "overall Status" shows error. I look in the diagnostic information and there i see an Tomcat Version: Error. But on the Server in the OMT i see Tomcat Version 10.0.10.0. But in the files after the Upgrade i have the old tomcat9 folder with some jsps in it, but nothing else and the tomcat folder link to the new tomcat10 directory.

In the logs i get two sql exceptions, the first one is

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [CREATE TABLE tmp_report_aggregation_-1_tbl (category VARCHAR(200), category_index INTEGER, targetgroup_id INTEGER, targetgroup VARCHAR(200), targetgroup_index INTEGER, value INTEGER, rate DOUBLE, rate_delivered DOUBLE)]; nested exception is java.sql.SQLSyntaxErrorException: 42X01 : [0] Encountered "-" at line 1, column 37

But i dont know where does it come from, and the second one

2021-11-25 10:54:30,877: ERROR [http-nio-8080-exec-10] com.agnitas.emm.core.logon.beans.LogonStateBundle - Unexpected logon state: PENDING. Expected: COMPLETE. (hostId: `null`, admin: `?`)
2021-11-25 10:55:18,133: ERROR [http-nio-8080-exec-7] com.agnitas.emm.core.logon.beans.LogonStateBundle - Unexpected logon state: PENDING. Expected: COMPLETE. (hostId: `null`, admin: `?`)
2021-11-25 10:55:29,443: ERROR [http-nio-8080-exec-5] com.agnitas.reporting.birt.external.dataset.BIRTDataSet - Could not get next tmpID from database !
org.springframework.dao.DataAccessResourceFailureException: Could not increment value for birt_report_tmp_tbl_seq sequence table; nested exception is java.sql.SQLSyntaxErrorException: (conn=226563) Table 'openemm.birt_report_tmp_tbl_seq' doesn't exist

I dont know if the lines are connected.

And in the emm_error.log i get the following lines.

2021-11-25 10:57:00,545: ERROR [main] com.agnitas.dao.impl.ComAdminDaoImpl - Deleted unallowed premium features for admins: 1
2021-11-25 10:57:00,545: ERROR [main] com.agnitas.dao.impl.ComAdminDaoImpl - master.show
2021-11-25 10:57:28,397: ERROR [http-nio-8080-exec-1] com.agnitas.web.perm.AuthorizationInterceptor - Permission denied: anonymous user is not authorized to request
2021-11-25 10:57:28,412: ERROR [http-nio-8080-exec-1] com.agnitas.web.perm.exceptions.AuthorizationExceptionHandler - User authorization required

Can i use OpenEMM with these Errors? Tell me if i can give some more Information.
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Error while trying to send campaign mailing

Post by maschoff »

When does the first SQL problem happens?

You can ignore the 2nd one, it is just an internal warning.

The 3rd one is a feature, it cleans permissions.
OpenEMM Maintainer
MSelke
Posts: 28
Joined: Thu Feb 25, 2016 11:19 am

Re: Error while trying to send campaign mailing

Post by MSelke »

The first messages i received were the following directly after the upgrade in the emm_error.log

Code: Select all

2021-11-25 10:54:30,877: ERROR [http-nio-8080-exec-10] com.agnitas.emm.core.logon.beans.LogonStateBundle - Unexpected logon state: PENDING. Expected: COMPLETE. (hostId: `null`, admin: `?`)
2021-11-25 10:55:18,133: ERROR [http-nio-8080-exec-7] com.agnitas.emm.core.logon.beans.LogonStateBundle - Unexpected logon state: PENDING. Expected: COMPLETE. (hostId: `null`, admin: `?`)
2021-11-25 10:55:29,443: ERROR [http-nio-8080-exec-5] com.agnitas.reporting.birt.external.dataset.BIRTDataSet - Could not get next tmpID from database ! 
org.springframework.dao.DataAccessResourceFailureException: Could not increment value for birt_report_tmp_tbl_seq sequence table; nested exception is java.sql.SQLSyntaxErrorException: (conn=226563) Table 'openemm.birt_report_tmp_tbl_seq' doesn't exist
	at org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer.getNextKey(MySQLMaxValueIncrementer.java:147)
	at org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer.nextIntValue(AbstractDataFieldMaxValueIncrementer.java:123)
	at com.agnitas.reporting.birt.external.dataset.BIRTDataSet.getNextTmpID(BIRTDataSet.java:464)
	at com.agnitas.reporting.birt.external.dataset.MailingSummaryDataSet.createTempTable(MailingSummaryDataSet.java:118)
	at com.agnitas.reporting.birt.external.dataset.MailingSummaryDataSet.prepareDashboardForCharts(MailingSummaryDataSet.java:299)
	at com.agnitas.emm.core.dashboard.service.impl.DashboardServiceImpl.getReportData(DashboardServiceImpl.java:111)
	at com.agnitas.emm.core.dashboard.service.impl.DashboardServiceImpl.getStatisticsInfo(DashboardServiceImpl.java:82)
	at com.agnitas.emm.core.dashboard.web.DashboardController.getStatistics(DashboardController.java:78)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:854)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:765)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at com.agnitas.emm.core.commons.filter.OriginUriFilter.doFilter(OriginUriFilter.java:43)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at com.agnitas.emm.core.commons.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:80)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at com.agnitas.emm.core.sessionhijacking.web.GroupingSessionHijackingPreventionFilter.doFilter(GroupingSessionHijackingPreventionFilter.java:105)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at com.agnitas.web.filter.HttpSecurityHeaderFilter.doFilter(HttpSecurityHeaderFilter.java:113)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at com.agnitas.emm.core.commons.filter.LoadBalancerFilter.doFilter(LoadBalancerFilter.java:153)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:667)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1707)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.sql.SQLSyntaxErrorException: (conn=226563) Table 'openemm.birt_report_tmp_tbl_seq' doesn't exist
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:155)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:363)
	at org.mariadb.jdbc.MariaDbStatement.executeUpdate(MariaDbStatement.java:627)
	at org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:340)
	at org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:340)
	at org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer.getNextKey(MySQLMaxValueIncrementer.java:142)
	... 62 more
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Table 'openemm.birt_report_tmp_tbl_seq' doesn't exist
	at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:262)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357)
	... 66 more
Caused by: java.sql.SQLException: Table 'openemm.birt_report_tmp_tbl_seq' doesn't exist
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1694)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1556)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1519)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:256)
	... 67 more
2021-11-25 10:55:29,762: ERROR [http-nio-8080-exec-5] com.agnitas.reporting.birt.external.dataset.MailingSummaryDataSet - Error: PreparedStatementCallback; bad SQL grammar [CREATE TABLE tmp_report_aggregation_-1_tbl (category VARCHAR(200), category_index INTEGER, targetgroup_id INTEGER, targetgroup VARCHAR(200), targetgroup_index INTEGER, value INTEGER, rate DOUBLE, rate_delivered DOUBLE)]; nested exception is java.sql.SQLSyntaxErrorException: 42X01 : [0] Encountered "-" at line 1, column 37
SQL:EMBEDDED: CREATE TABLE tmp_report_aggregation_-1_tbl (category VARCHAR(200), category_index INTEGER, targetgroup_id INTEGER, targetgroup VARCHAR(200), targetgroup_index INTEGER, value INTEGER, rate DOUBLE, rate_delivered DOUBLE)
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [CREATE TABLE tmp_report_aggregation_-1_tbl (category VARCHAR(200), category_index INTEGER, targetgroup_id INTEGER, targetgroup VARCHAR(200), targetgroup_index INTEGER, value INTEGER, rate DOUBLE, rate_delivered DOUBLE)]; nested exception is java.sql.SQLSyntaxErrorException: 42X01 : [0] Encountered "-" at line 1, column 37
in the emm.log i see now the following error

2021-11-25 14:24:19,172: ERROR [http-nio-8080-exec-9] com.agnitas.emm.core.serverstatus.web.ServerStatusControllerBasic - Error while starting job queue by description BirtReports
java.lang.Exception: This JobQueueNode is currently not active

Is this somehow connected to the other errors?
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Error while trying to send campaign mailing

Post by maschoff »

BIRT reports is not included in OpenEMM. Go to the system status page and have a look at tab "job list". If a BIRT job is included there (and marked red), you can set field deleted in job_queue_tbl. to value 1.
OpenEMM Maintainer
MSelke
Posts: 28
Joined: Thu Feb 25, 2016 11:19 am

Re: Error while trying to send campaign mailing

Post by MSelke »

This helped for the first of four jobs, but now i see the following in the log

2021-11-25 15:21:12,213: ERROR [http-nio-8080-exec-7] com.agnitas.emm.core.serverstatus.web.ServerStatusControllerBasic - Error while starting job queue by description LoginTrackTableCleaner
java.lang.Exception: This JobQueueNode is currently not active

And this in the joblist

3 Inaktiv LoginTrackTableCleaner 26.11.2021 04:00 OK
4 Inaktiv CalendarCommentMailingService 25.11.2021 11:00 OK
5 Inaktiv DeletedContentblockCleaner 26.11.2021 01:35 OK
6 Inaktiv UndoRelictCleaner 26.11.2021 01:30 OK
7 Inaktiv AutoOptimization 25.11.2021 10:55 OK
8 Inaktiv DBErrorCheck 25.11.2021 11:00 OK
9 Inaktiv WebserviceLoginTrackTableCleaner 25.11.2021 15:45 OK
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Error while trying to send campaign mailing

Post by maschoff »

This cleaner runs only once per day. You should check back tomorrow.
OpenEMM Maintainer
MSelke
Posts: 28
Joined: Thu Feb 25, 2016 11:19 am

Re: Error while trying to send campaign mailing

Post by MSelke »

Today there are no more sql errors in the logs, but i see the folling errors a few times

2021-11-26 06:11:11,359: ERROR [http-nio-8080-exec-1] com.agnitas.web.RedirectServlet - service: uid missing

and

Code: Select all

2021-11-26 05:19:15,128: ERROR [http-nio-8080-exec-6] com.agnitas.emm.core.unsubscribe.ComUnsubscribe - Exception during one-click unsubscription
org.agnitas.emm.core.commons.uid.parser.exception.UIDParseException: No registered parser could handle the UID. See log messages above for more details. uid: E.B.x.D_X.B.B.bieC8HzcCBuUEgBBKt4kbyftdIuF9Ve8ELKF1C89TAkc4f0YbDDJmmy7vJaDg9MaUy-Zk4fBt2MVUXAA7CKv9j
And the OMT show the following in the status

Jobqueue status: 7 errorneous jobs (LoginTrackTableCleaner, CalendarCommentMailingService, DeletedContentblockCleaner, ...)
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Error while trying to send campaign mailing

Post by maschoff »

Hm, the cleaner jobs shouldn't be executed because there is no cleaner code in OpenEMM. Try

Code: Select all

DELETE FROM job_queue_result_tbl WHERE job_id = (SELECT id FROM job_queue_tbl WHERE description = 'DBCleaner');
DELETE FROM job_queue_tbl WHERE description = 'DBCleaner';
in the OpenEMM database.
OpenEMM Maintainer
Post Reply