Cleanup bad data in the wp_pmpro_memberships_users table of PMPro

cleanup_pmpro_memberships_users.sql

#
# PMPro will sometimes get bad data in the wp_pmpro_memberships_users table.
# This happens often during imports and/or when levels are deleted.
# These queries below will search for bad records and inactivate them.
# Run queries (c) and (e) to deactivate the bad records.
# The other queries are for reference.
# IMPORTANT NOTE: If your DB prefix is not wp_, you will have to update it in the queries below
#
# (a) show latest entries in mu
SELECT * FROM wp_pmpro_memberships_users ORDER BY id DESC;

# (b) find rows for levels that have been delted
SELECT * 
FROM wp_pmpro_memberships_users mu 
	LEFT JOIN wp_pmpro_membership_levels l ON mu.membership_id = l.id 
WHERE mu.status = 'active' 
	AND l.id IS NULL;

# (c) set those rows as inactive
UPDATE wp_pmpro_memberships_users mu
	LEFT JOIN wp_pmpro_membership_levels l ON mu.membership_id = l.id
SET mu.status = 'inactive'
WHERE mu.status = 'active' 
	AND l.id IS NULL;

# (d) find rows for users with more than one active status for the same level
SELECT *
FROM wp_pmpro_memberships_users mu1, wp_pmpro_memberships_users mu2 		
WHERE mu1.id < mu2.id
	AND mu1.user_id = mu2.user_id
	AND mu1.membership_id = mu2.membership_id		
	AND mu1.status = 'active'
	AND mu2.status = 'active'
GROUP BY mu1.id
ORDER BY mu1.user_id, mu1.id DESC;

# (e) set those rows as inactive
UPDATE wp_pmpro_memberships_users t1
INNER JOIN (SELECT mu1.id as id
FROM wp_pmpro_memberships_users mu1, wp_pmpro_memberships_users mu2 		
WHERE mu1.id < mu2.id
	AND mu1.user_id = mu2.user_id
	AND mu1.membership_id = mu2.membership_id		
	AND mu1.status = 'active'
	AND mu2.status = 'active'
GROUP BY mu1.id
ORDER BY mu1.user_id, mu1.id DESC) t2
ON t1.id = t2.id
SET status = 'inactive';