Missing Recipient Entries

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

Moderator: moderator

Missing Recipient Entries

Postby ospsg » Fri Feb 28, 2014 8:53 pm

We are using OpenEMM 2013 to manage three mailing lists and over the last month and a half or so, I have noticed that recipient entries are being deleted seemingly at random.

I have been exporting each mail list in its entirety (not just the active members but members who have opted-out, waiting for confirmation, etc) about once a week for the last 5 weeks to compare the entries from week to week and entries will go missing between exports. They are no longer in the database anywhere, as if they were being deleted manually. I know for sure that some of these members are active in the system when they go missing, and some are 'waiting on confirmation'.

I thought that it could be purging entries from the system that have been opted-out of all lists (some of our entries are subscribed to multiple lists), however I was going through the lists today and all of the entries that should have been missing based on that hypothesis are all still in the system. Instead, there are others missing that were definitely active on at least one list.

They are not just missing from the exports, as I have double checked the database for all the missing entries.

Has anyone else experienced this issue?
ospsg
 
Posts: 5
Joined: Fri Feb 28, 2014 8:01 pm

Re: Missing Recipient Entries

Postby maschoff » Tue Mar 04, 2014 4:03 pm

You should have a look at the Install & Admin Guide where you can read how addresses of status 5 (waiting for confirmation) are removed from the database.
OpenEMM Maintainer
maschoff
Site Admin
 
Posts: 2129
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany

Re: Missing Recipient Entries

Postby ospsg » Wed Mar 05, 2014 7:00 pm

Are you referring to the Database Cleanup section? This only applies to those waiting on confirmation, which is fine. However, my biggest problem is that subscribers that I know are active are disappearing from the database. Why would active subscribers be purged along with some that are waiting on confirmation? Would this happen if a subscriber was active on one list and still waiting on confirmation for another list? We have many subscribers that are on multiple lists that we maintain so it is possible that they have already subscribed and are active on one list and decided to sign up for another list but never completed the double opt-in process.
ospsg
 
Posts: 5
Joined: Fri Feb 28, 2014 8:01 pm

Re: Missing Recipient Entries

Postby maschoff » Wed Mar 05, 2014 9:32 pm

If you are in doubt you should disable the cleanup process. We could not reproduce this behaviour so far.
OpenEMM Maintainer
maschoff
Site Admin
 
Posts: 2129
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany

Re: Missing Recipient Entries

Postby ospsg » Wed Mar 05, 2014 11:38 pm

We are going to disable this feature and continue to monitor to confirm that this is where the problem lies. If this is the problem though, and after disabling this feature we no longer have any missing subscribers, that would mean that this feature is deleting recipients that are waiting on confirmation on one list but also active on another.
ospsg
 
Posts: 5
Joined: Fri Feb 28, 2014 8:01 pm

Re: Missing Recipient Entries

Postby maschoff » Fri Mar 14, 2014 6:14 pm

Thanks to your report we could identify a bug, sorry for this! We will provide a fix next week. Stay tuned ...
OpenEMM Maintainer
maschoff
Site Admin
 
Posts: 2129
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany

Re: Missing Recipient Entries

Postby ospsg » Wed Mar 19, 2014 4:31 pm

I have been going through and comparing my lists between 3/6 and 3/18 and I have found that 8 entries that were active on all the lists that they were on, have now been deleted. They were not waiting on confirmation on any list, but all active. Is this due to the bug that you found? Or is this something entirely different? We had only disabled the clean up feature briefly, so I know it has been running for at least a week. Just to note, there were 4 entries that were all still waiting on confirmation that were deleted, so for those, that feature was working correctly.

If this isn't due to the bug that was found, I am at a loss for what to do here. 4 out of the 8 active members that were deleted were active on the only three lists that we have. I have this set up so that duplicates are checked based on the email address field so there is no way that these entries would be waiting on confirmation on any list.
ospsg
 
Posts: 5
Joined: Fri Feb 28, 2014 8:01 pm

Re: Missing Recipient Entries

Postby maschoff » Wed Mar 19, 2014 5:33 pm

New code for CleanDBDaoImpl.java:
Code: Select all
package org.agnitas.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.agnitas.beans.Recipient;
import org.agnitas.beans.impl.RecipientImpl;
import org.agnitas.dao.CleanDBDao;
import org.agnitas.util.AgnUtils;
import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

/**
*
* @author Nicole Serek
*/
public class CleanDBDaoImpl implements CleanDBDao {
   
   private static final transient Logger logger = Logger.getLogger( CleanDBDaoImpl.class);
   
   @Override
   public void cleanup() {
      JdbcTemplate jdbc = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
      String sql = null;
      
      if(AgnUtils.isMySQLDB()) {
         sql = "DELETE FROM bounce_tbl WHERE " + AgnUtils.changeDateName() + " < date_sub(curdate(), interval " + AgnUtils.getDefaultIntValue("bounces.maxRemain.days") + " day)";
      } else {
         // OracleDB handles bounces differently
      }
      
      try {
         jdbc.execute(sql);
      } catch (Exception e) {
         logger.error( "Error deleting old bounces: " + e.getMessage(), e);
         AgnUtils.sendExceptionMail("sql:" + sql, e);
      }
      
      sql = null;
      if(AgnUtils.isMySQLDB()) {
         sql = "DELETE FROM customer_1_binding_tbl WHERE user_status=5 AND "   + AgnUtils.changeDateName() + " < date_sub(curdate(), interval " + AgnUtils.getDefaultIntValue("pending.maxRemain.days") + " day)";
      } else {
         // OracleDB handles pending subscribers differently
      }
      
      try {
         jdbc.execute(sql);
         List<Recipient> customer = (List<Recipient>) jdbc.queryForObject("select * from customer_1_tbl c WHERE NOT EXISTS (SELECT 1 FROM customer_1_binding_tbl b WHERE b.customer_id = c.customer_id)", new CustomerID_RowMapper());
         for (Recipient customerList : customer) {
            sql = "DELETE FROM customer_1_tbl WHERE customer_id = ?";
            jdbc.update(sql, new Object[]{customerList.getCustomerID()});
         }
      } catch (Exception e) {
         logger.error( "error deleting pending subscribers: " + e.getMessage(), e);
         AgnUtils.sendExceptionMail("sql:" + sql, e);
      }
   }
   
   public class CustomerID_RowMapper implements ParameterizedRowMapper<Recipient> {
      @Override
      public Recipient mapRow(ResultSet resultSet, int row) throws SQLException {
         Recipient custID = new RecipientImpl();
         
         custID.setCompanyID((resultSet.getInt("customer_id")));
         
         return custID;
      }
   }
   
   /**
     * Holds value of property applicationContext.
     */
    protected ApplicationContext applicationContext;
   
   /**
     * Setter for property applicationContext.
     * @param applicationContext New value of property applicationContext.
     */
    @Override
    public void setApplicationContext(ApplicationContext applicationContext) {
        this.applicationContext = applicationContext;
    }
}

Should I mail you a compiled version for testing?
OpenEMM Maintainer
maschoff
Site Admin
 
Posts: 2129
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany

Re: Missing Recipient Entries

Postby ospsg » Thu Mar 20, 2014 3:18 pm

Yes, please send it over. Let me know if you need anything from me to get this done. Thanks!
ospsg
 
Posts: 5
Joined: Fri Feb 28, 2014 8:01 pm

Re: Missing Recipient Entries

Postby bk007 » Fri Mar 21, 2014 6:51 pm

@maschoff -

How serious is this bug?

Are we all potentially having data deleted without knowing it?
bk007
 
Posts: 19
Joined: Sat May 18, 2013 3:29 am

Re: Missing Recipient Entries

Postby maschoff » Fri Mar 21, 2014 7:13 pm

A potential subscriber who does NOT confirm his/her double-opt-in mail for 30 days (default value) is deleted from the database. But if this subscriber is actively subscribed to other mailing lists too, these bindings (obviously) disappear as well.

This bug has been in the code for several years. :-(
OpenEMM Maintainer
maschoff
Site Admin
 
Posts: 2129
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany

Re: Missing Recipient Entries

Postby maschoff » Sat Mar 22, 2014 7:19 am

@ospsg: I mailed you the new classes. Please check if it works for you so that I can publish them as public bugfix.
OpenEMM Maintainer
maschoff
Site Admin
 
Posts: 2129
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany


Return to Bugs, bug fixes & releases

Who is online

Users browsing this forum: No registered users and 2 guests

cron