OpenEMM Technical Support Forum

I created a date field in settings/edit profile fields. I used type 'Date'. On page 177 of the manual, I eventually found the note which said that to set values in this date, I need 3 fields in my form: fieldname_DAY_DATE, fieldname_MONTH_DATE, and fieldname_YEAR_DATE. (my field is named next_alert)

HOWEVER ...

Once I tried using this form, I started getting the error page instead of the confirmation page ... with no real details. I found that a second submission always worked, and the recipient was added to the database, but with no mailings subscribed/checked.

Troubleshooting:

I found a log file on the server, under /home/openemm/var/log, called core_stdout.log. (I didn't find anything useful in core_stderr.log.)

I found an entry where RecipientDaoImpl was adding new customer, and my date field was being entered as STR_TO_DATE('14.04.2010' 00:00:00', '%d.%m.%Y %H:%i:%s'). Shortly thereafter, I see a message: SQL State [01004] Error code [0]; data truncation: Data truncated for column 'next_alert' at row 1 .... etc.

Solution:

I eventually investigated the MySQL database, database: openmm, table customer_1_tbl. I found that the built-in fields, creation_date and change_date were type timestamp. But my custom field, next_alert, was type date.

Apparently, a date field can't hold the time values being provided in the code. Since I can't rewrite the code, I revised the database. For my field next_alert, I did:

alter table customer_1_tbl change colulmn next_alert next_alert TIMESTAMP;

That did it. No more error pages.

Suggestion: The system should be changed to declare these custom date fields as timestamp instead of date. Alternatively, fix the insert process to avoid trying to stuff time values into a field that can't hold them.

Thanks for the info, we will look into this issue.

Actually, the problem runs even deeper. I posted a bug report at the SourceForge bugtracker here:

https://sourceforge.net/tracker/?func=d ... tid=848488

We wil try to fix it for relase 6.RC2 (scheduled for next week).

I have a problem that looks very much like this old report. I'm running OpenEMM 2013

I create a Profile Field named REG with type date. When I try to subscribe a test recipient using the url:
http:// news.mydomain.com:8080/form.do?agnCI=1&agnFN=Auto_Subscribe&agnSUBSCRIBE=1&agnMAILINGLIST=1&GENDER=2&FIRSTNAME=Bongo&LASTNAME=Terry&EMAIL=Bongo2%40terry-realty.com&MAILTYPE=1&REG_DAY_DATE=10&REG_MONTH_DATE=12&REG_YEAR_DATE=2015
I get served the error page

However I also have a Profile Field named test_text and
http:// news.terry-realty.com:8080/form.do?agnCI=1&agnFN=Auto_Subscribe&agnSUBSCRIBE=1&agnMAILINGLIST=1&GENDER=2&FIRSTNAME=Bongo&LASTNAME=Terry&EMAIL=Bongo2%40terry-realty.com&test_text=foobar
It works correctly, the recipient is subscribed or updated and the test_text field is set to foobar. So I believe I have the forms and actions correct.

When I view /home/openemm-2013/logs/openemm_core.log I find:
2013-02-19 22:15:54,154: ERROR [http-8080-5] org.agnitas.dao.impl.RecipientDaoImpl - updateInDB: StatementCallback; uncategorized SQLException for SQL [UPDATE customer_1_tbl SET change_date=current_timestamp, test_date=null, lastname='Terry', firstname='Bongo', mailtype=1, title='Mrs', email='bongo2@terry-realty.com', reg=STR_TO_DATE('10-12-2015 00:00:00', '%d-%m-%Y %h:%i:%s'), gender=2, test_text=null WHERE customer_id=3973]; SQL state [HY000]; error code [1411]; Incorrect datetime value: '10-12-2015 00:00:00' for function str_to_date; nested exception is java.sql.SQLException: Incorrect datetime value: '10-12-2015 00:00:00' for function str_to_date
org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [UPDATE customer_1_tbl SET change_date=current_timestamp, test_date=null, lastname='Terry', firstname='Bongo', mailtype=1, title='Mrs', email='bongo2@terry-realty.com', reg=STR_TO_DATE('10-12-2015 00:00:00', '%d-%m-%Y %h:%i:%s'), gender=2, test_text=null WHERE customer_id=3973]; SQL state [HY000]; error code [1411]; Incorrect datetime value: '10-12-2015 00:00:00' for function str_to_date; nested exception is java.sql.SQLException: Incorrect datetime value: '10-12-2015 00:00:00' for function str_to_date
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:429)
at org.agnitas.dao.impl.RecipientDaoImpl.updateInDB(RecipientDaoImpl.java:497)
at org.agnitas.beans.impl.RecipientImpl.updateInDB(RecipientImpl.java:91)
at org.agnitas.actions.ops.SubscribeCustomer.executeOperation(SubscribeCustomer.java:146)
at org.agnitas.actions.impl.EmmActionImpl.executeActions(EmmActionImpl.java:105)
at org.agnitas.beans.impl.UserFormImpl.evaluateAction(UserFormImpl.java:308)
at org.agnitas.beans.impl.UserFormImpl.evaluateStartAction(UserFormImpl.java:330)
at org.agnitas.beans.impl.UserFormImpl.evaluateForm(UserFormImpl.java:351)
at org.agnitas.web.UserFormExecuteAction.executeForm(UserFormExecuteAction.java:232)
at org.agnitas.web.UserFormExecuteAction.execute(UserFormExecuteAction.java:115)
at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.ajaxanywhere.AAFilter.doFilter(AAFilter.java:46)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.agnitas.web.filter.SerializeRequestFilter.doFilter(SerializeRequestFilter.java:28)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.agnitas.web.filter.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.agnitas.web.filter.SessionHijackingPreventionFilter.doFilter(SessionHijackingPreventionFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:615)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: Incorrect datetime value: '10-12-2015 00:00:00' for function str_to_date
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2571)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:782)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:625)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:261)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
... 50 more
2013-02-19 22:15:54,528: ERROR [http-8080-5] org.agnitas.beans.impl.UserFormImpl - Action Result: false

It appears that the correct date is being passed but it fails to make it into the DB. Very similar to the earlier bug:
http:// sourceforge.net/tracker/?func=detail&aid=2888657&group_id=168937&atid=848488

I'm pretty new to this, but I hope somebody smart can explain / sort this for me. I've tried everything I can

It is a bug. I will mail you the solution in a minute and make the fix public if it works for you.

It has been a few hours and I haven'r received a fix. Did you send it, or has it been delayed?

Sorry, I sent it to the original poster of this thread by mistake.

But I have send it again just a minute ago.

That appears to fix it! Thanks

The official bugfix is now available at

https://sourceforge.net/projects/openem ... /Bugfixes/