Missing Recipient Entries

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

Moderator: moderator

ospsg
Posts: 5
Joined: Fri Feb 28, 2014 8:01 pm

Missing Recipient Entries

Post by ospsg »

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?
maschoff
Site Admin
Posts: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Missing Recipient Entries

Post by maschoff »

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
ospsg
Posts: 5
Joined: Fri Feb 28, 2014 8:01 pm

Re: Missing Recipient Entries

Post by ospsg »

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.
maschoff
Site Admin
Posts: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Missing Recipient Entries

Post by maschoff »

If you are in doubt you should disable the cleanup process. We could not reproduce this behaviour so far.
OpenEMM Maintainer
ospsg
Posts: 5
Joined: Fri Feb 28, 2014 8:01 pm

Re: Missing Recipient Entries

Post by ospsg »

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.
maschoff
Site Admin
Posts: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Missing Recipient Entries

Post by maschoff »

Thanks to your report we could identify a bug, sorry for this! We will provide a fix next week. Stay tuned ...
OpenEMM Maintainer
ospsg
Posts: 5
Joined: Fri Feb 28, 2014 8:01 pm

Re: Missing Recipient Entries

Post by ospsg »

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.
maschoff
Site Admin
Posts: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Missing Recipient Entries

Post by maschoff »

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
ospsg
Posts: 5
Joined: Fri Feb 28, 2014 8:01 pm

Re: Missing Recipient Entries

Post by ospsg »

Yes, please send it over. Let me know if you need anything from me to get this done. Thanks!
bk007
Posts: 19
Joined: Sat May 18, 2013 3:29 am

Re: Missing Recipient Entries

Post by bk007 »

@maschoff -

How serious is this bug?

Are we all potentially having data deleted without knowing it?
maschoff
Site Admin
Posts: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Missing Recipient Entries

Post by maschoff »

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: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Missing Recipient Entries

Post by maschoff »

@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
Post Reply