SQL Syntax Error in OpenEMM 5.5.1

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

Moderator: moderator

blix88
Posts: 4
Joined: Thu Dec 04, 2008 8:45 pm

SQL Syntax Error in OpenEMM 5.5.1

Post by blix88 »

I created an action based mailing. Which when I tested the first time worked flawlessly. I went to test it a second time and the emails were never sent out. I traced it down to an SQL Syntax Error that was reported in the /home/openemm/var/log/20081204-news-mailgun.log file.

Code: Select all

[04.12.2008  20:38:56] INFO/writer/meta/(1/1/14/33): Skip validation of XML document
[04.12.2008  20:38:56] DEBUG/dbase/(1/1/14/33): DB-Updt: INSERT INTO mailtrack_tbl (company_id, status_id, mailing_id, customer_id) SELECT 1, 33, 14, customer_id FROM null
[04.12.2008  20:38:56] DEBUG/dbase/(1/1/14/33): DB-Updt failed: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null' at line 1
[04.12.2008  20:38:56] ERROR/execute/(1/1/14/33): Unable to add mailtrack information using "INSERT INTO mailtrack_tbl (company_id, status_id, mailing_id, customer_id) SELECT 1, 33, 14, customer_id FROM null": java.lang.Exception: Update INSERT INTO mailtrack_tbl (company_id, status_id, mailing_id, customer_id) SELECT 1, 33, 14, customer_id FROM null failed: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null' at line 1
[04.12.2008  20:38:56] DEBUG/execute/(1/1/14/33): Successful end
Here is the log file in its entirerty. http://74.219.220.10/log/20081204-news-mailgun.log You can see that the first action based email went through no problem towards the top of the log. Then later failed with an SQL Syntax Error.

Note: It looks like its trying to stuff the data into a temporary table that it creates. But on the ones that fail the temporary table never gets created. Since it doesn't exist the update query is using the value of null for the table name.

I believe this is preventing the action based emails from getting sent. If you require any additional log files or configuration settings let me know and I'll post them.

Thanks for the help.
Blix88
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Post by maschoff »

Thanks for your feedback! You found a bug which we posted here:

https://sourceforge.net/tracker/index.p ... tid=848488

We try to provide a patch asap.
OpenEMM Maintainer
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Post by maschoff »

A bugfix is available now (attached to the bug report).
OpenEMM Maintainer
blix88
Posts: 4
Joined: Thu Dec 04, 2008 8:45 pm

Thanks!

Post by blix88 »

Thank you. That cleared up the errors in my mailgun log.

Blix88
glapolla
Posts: 1
Joined: Sat Dec 06, 2008 1:00 am

Post by glapolla »

I also expierenced a similar issue this week. I tried the BC.class fix and it didn't fix my issue. Here is the pertinent log info:

core_stderr.log

Code: Select all

Clearing mailing
triggerMailing: java.lang.Exception: Failed in precheck: java.lang.Exception: Query SELECT count(distinct customer_id) AND cust.mailtype != 0)) failed: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1
org.agnitas.backend.MailgunImpl.doExecute(MailgunImpl.java:338)
org.agnitas.backend.MailgunImpl.executeMailgun(MailgunImpl.java:119)
org.agnitas.beans.impl.MailingImpl.triggerMailing(MailingImpl.java:662)
org.agnitas.web.MailingSendAction.sendMailing(MailingSendAction.java:445)
org.agnitas.web.MailingSendAction.execute(MailingSendAction.java:203)
org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
javax.servlet.http.HttpServlet.service(HttpServlet.java:115)
javax.servlet.http.HttpServlet.service(HttpServlet.java:92)
com.caucho.server.dispatch.ServletFilterChain.doFilter(ServletFilterChain.java:106)
org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:174)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
com.caucho.server.dispatch.FilterFilterChain.doFilter(FilterFilterChain.java:70)
and 20081205-mail-mailgun.log

Code: Select all

[05.12.2008  16:03:50] DEBUG/init: Data read from rsc for 35
[05.12.2008  16:03:50] DEBUG/dbase: DB-Exec: SHOW TABLES
[05.12.2008  16:03:50] DEBUG/init: Initial database connection established
[05.12.2008  16:03:50] DEBUG/dbase: DB-Exec: SELECT company_id, mailing_id, status_field, senddate, step, blocksize, genstatus FROM maildrop_status_tbl WHERE status_id = 35
[05.12.2008  16:03:50] DEBUG/dbase: DB-Updt: UPDATE maildrop_status_tbl SET genchange = now(), genstatus = 2 WHERE status_id = 35 AND genstatus = 1
[05.12.2008  16:03:50] DEBUG/dbase: DB-Exec: SELECT mailinglist_id, shortname, target_expression FROM mailing_tbl WHERE mailing_id = 6
[05.12.2008  16:03:50] DEBUG/dbase: DB-Exec: SELECT mediatype, param FROM mailing_mt_tbl WHERE mailing_id = 6
[05.12.2008  16:03:50] DEBUG/dbase: DB-Exec: SELECT url_id, full_url, MEASURE_TYPE FROM rdir_url_tbl WHERE company_id = 1 AND mailing_id = 6
[05.12.2008  16:03:50] DEBUG/dbase: DB-Exec: SELECT title_id, title, gender FROM title_gender_tbl WHERE title_id IN (SELECT title_id FROM title_tbl WHERE company_id = 1 OR company_id = 0 OR company_id IS null)
[05.12.2008  16:03:50] DEBUG/dbase: DB-Exec: SELECT * FROM customer_1_tbl WHERE 1 = 0
[05.12.2008  16:03:50] DEBUG/dbase: DB-Exec: SELECT col_name, shortname FROM customer_field_tbl WHERE company_id = 1
[05.12.2008  16:03:50] DEBUG/dbase: DB-Exec: SELECT shortname, xor_key, rdir_domain FROM company_tbl WHERE company_id = 1
[05.12.2008  16:03:50] DEBUG/init: Initial data read from database
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35): Initial data valid
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35): All set variables:
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   logLevel = DEBUG (7)
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   mailDir = /home/openemm/var/spool/ADMIN
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   defaultEncoding = quoted-printable
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   defaultCharset = ISO-8859-1
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   dbLogin = agnitas
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   dbPassword = ******
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   sqlConnect = jdbc:mysql://localhost/openemm
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   blockSize = 5000
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   metaDir = /home/openemm/var/spool/META
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   xmlBack = /home/openemm/bin/xmlback
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   xmlValidate = false
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   sampleEmails = null
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   mailLogNumber = 400
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   accLogfile = /home/openemm/var/spool/log/account.log
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   defaultUserStatus = 1
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   dbase = org.agnitas.backend.DBase@502ca
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   maildrop_status_id = 35
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   company_id = 1
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   company_name = Agnitas Admin
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   mailtracking_table = mailtrack_tbl
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   mailinglist_id = 3
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   mailing_id = 6
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   status_field = T
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   senddate = null
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   sendtime = null
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   sendtimestamp = 2008-12-05 16:03:50.0
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   sendSeconds = 1228521830
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   step = 0
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   blocksPerStep = 1
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   subselect = *not set*
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   mailing_name = AV & IE ADDTL BROKERS
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   subject = 6 Percent Broker Commission - 4 Weeks left!
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   from_email = (My Mailer <no_reply@cs.mydomain.com>, My Mailer <no_reply@cs.mydomain.com>, no_reply@cs.mydomain.com, no_reply@cs.mydomain.com)
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   reply_to = (My Mailer <no_reply@cs.mydomain.com>, My Mailer <no_reply@cs.mydomain.com>, no_reply@cs.mydomain.com, no_reply@cs.mydomain.com)
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   encoding = quoted-printable
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   charset = UTF-8
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   domain = openemm.invalid
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   boundary = AGNITAS
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   eol = LF
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   mailer = OpenEMM V5.5.1
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   profileURL = http://mail.cs.mydomain.com:8080/p.html?
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   unsubscribeURL = http://mail.cs.mydomain.com:8080/uq.html?
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   autoURL = http://mail.cs.mydomain.com:8080/r.html?
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   onePixelURL = http://mail.cs.mydomain.com:8080/g.html?
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   masterMailtype = 1
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   lineLength = 72
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   onepixlog = 2
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   password =
[05.12.2008  16:03:50] DEBUG/init/(1/3/6/35):   rdirDomain = http://mail.cs.mydomain.com:8080
[05.12.2008  16:03:50] DEBUG/prepare/(1/3/6/35): Starting firing
[05.12.2008  16:03:50] DEBUG/dbase/(1/3/6/35): DB-Exec: SELECT comptype, url_id, compname, mtype, target_id, emmblock, binblock FROM component_tbl WHERE company_id = 1 AND (mailing_id = 6) ORDER BY component_id
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 0 (8): agnHead [null]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 1 (8): agnHtml [text/html]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 2 (8): agnText [text/plain]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 3 (8): http://images.cs.mydomain.com/images/ie-01.gif [image/gif]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 4 (8): http://images.cs.mydomain.com/images/ie-01.jpg [image/jpeg]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 5 (8): http://images.cs.mydomain.com/images/ie-02.gif [image/gif]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 6 (8): http://images.cs.mydomain.com/images/ie-02.jpg [image/jpeg]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 7 (8): http://images.cs.mydomain.com/images/ie-03.jpg [image/jpeg]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 0 (8): agnHead [null]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 1 (8): agnText [text/plain]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 2 (8): agnHtml [text/html]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 3 (8): http://images.cs.mydomain.com/images/ie-01.gif [image/gif]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 4 (8): http://images.cs.mydomain.com/images/ie-01.jpg [image/jpeg]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 5 (8): http://images.cs.mydomain.com/images/ie-02.gif [image/gif]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 6 (8): http://images.cs.mydomain.com/images/ie-02.jpg [image/jpeg]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Block 7 (8): http://images.cs.mydomain.com/images/ie-03.jpg [image/jpeg]
[05.12.2008  16:03:50] DEBUG/dbase/(1/3/6/35): DB-Exec: SELECT dyn_name_id, dyn_name FROM dyn_name_tbl WHERE mailing_id = 6 AND company_id = 1
[05.12.2008  16:03:50] DEBUG/dbase/(1/3/6/35): DB-Exec: SELECT dyn_content_id, dyn_name_id, target_id, dyn_order, dyn_content FROM dyn_content_tbl WHERE dyn_name_id IN (SELECT dyn_name_id FROM dyn_name_tbl WHERE mailing_id = 6 AND company_id = 1)
[05.12.2008  16:03:50] DEBUG/prepare/(1/3/6/35): Parse blocks
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Parsing block 0
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Added Tag: [agnSYSINFO name="EPOCH"]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Added Tag: [agnEMAIL code="punycode"]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Added Tag: [agnSYSINFO name="FQDN" default="openemm.invalid"]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Added Tag: [agnEMAIL]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Added Tag: [agnSYSINFO name="RFCDATE"]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Added Tag: [agnMESSAGEID]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Skip existing Tag: [agnSYSINFO name="RFCDATE"]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Skip existing Tag: [agnEMAIL code="punycode"]
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Parsing block 1
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Parsing block 2
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Parsing block 3
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Parsing block 4
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Parsing block 5
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Parsing block 6
[05.12.2008  16:03:50] DEBUG/collect/(1/3/6/35): Parsing block 7
[05.12.2008  16:03:50] DEBUG/dbase/(1/3/6/35): DB-Exec: SELECT selectvalue, type FROM tag_tbl WHERE tagname = 'agnMAILTYPE' AND (company_id = 1 OR company_id = 0 OR company_id IS null)
[05.12.2008  16:03:50] DEBUG/dbase/(1/3/6/35): DB-Exec: SELECT company_id, email FROM cust_ban_tbl WHERE company_id = 0 OR company_id = 1
[05.12.2008  16:03:50] INFO/readblist/(1/3/6/35): Found 0 entries in global blacklist, 0 entries in local blacklist
[05.12.2008  16:03:50] DEBUG/dbase/(1/3/6/35): DB-Exec: SELECT company_id, deleted FROM mailing_tbl WHERE mailing_id = 6
[05.12.2008  16:03:50] DEBUG/dbase/(1/3/6/35): DB-Updt: CREATE TABLE TMP_CRT_T_6_35_TBL (customer_id int, user_type varchar(1), mediatype int) AS SELECT cust.customer_id, bind.user_type, bind.mediatype FROM customer_1_tbl cust, customer_1_binding_tbl bind WHERE cust.customer_id = bind.customer_id AND ((bind.user_status = 1 AND bind.mailinglist_id = 3 AND bind.user_type IN ('A', 'T')))
[05.12.2008  16:03:50] DEBUG/dbase/(1/3/6/35): DB-Exec: SELECT count(cust.customer_id) FROM customer_1_tbl cust, customer_1_binding_tbl bind WHERE cust.customer_id = bind.customer_id AND (bind.user_status = 1 AND bind.mailinglist_id = 3)
[05.12.2008  16:03:50] DEBUG/selectvalue/(1/3/6/35): SQL-String: SELECT cust.customer_id, bind.user_type,cust.mailtype,cust.email
[05.12.2008  16:03:50] DEBUG/selectvalue/(1/3/6/35): SQL-String: SELECT cust.customer_id, bind.user_type,cust.mailtype,cust.email
[05.12.2008  16:03:50] INFO/execute/(1/3/6/35): Pure text mailing detected, prechecking mailing type
[05.12.2008  16:03:50] DEBUG/selectvalue/(1/3/6/35): SQL-String: SELECT count(distinct customer_id)
[05.12.2008  16:03:50] DEBUG/dbase/(1/3/6/35): DB-Exec: SELECT count(distinct customer_id) AND cust.mailtype != 0))
[05.12.2008  16:03:50] DEBUG/dbase/(1/3/6/35): DB-Exec failed: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Post by maschoff »

We added a corrected file MailgunImpl.class to the bug report at SourceForge to fix your problem:

https://sourceforge.net/tracker/index.p ... tid=848488
OpenEMM Maintainer
Post Reply