4.2 drush upgrade deletes valid membership and activity records

The following changes are being made to the upgrade script to fix this problem:
1. deleteBadData() method is removed. No data will be deleted.

2. Replacement 'invalidData' query properly checks for the condition where a single contribution is linked to > 1 membership record via membership_payment table AND is not linked to the line_item table (i.e.. the contribution wasn't submitted via price set). New query is:

SELECT mp.contribution_id, mp.membership_id, mem.membership_type_id, mem.start_date, mem.end_date, mem.status_id
FROM civicrm_membership_payment mp
INNER JOIN ( SELECT cmp.contribution_id
FROM civicrm_membership_payment cmp
LEFT JOIN civicrm_line_item cli ON cmp.contribution_id=cli.entity_id and cli.entity_table = 'civicrm_contribution'
WHERE cli.entity_id IS NULL
GROUP BY cmp.contribution_id
HAVING COUNT(cmp.membership_id) > 1) submp ON submp.contribution_id = mp.contribution_id
INNER JOIN civicrm_membership mem ON mem.id = mp.membership_id
ORDER BY mp.contribution_id, mp.membership_id;

3. If this query returns any rows, the upgrade will be aborted. Changes are being made in Drush integration to ensure that the 'abort' will happen regardless of whether the upgrade is run via web or via drush (CRM-11156). Abort message is:

"The upgrade is being aborted due to data integrity issues in your database. There are multiple membership records linked to the same contribution record. This is unexpected, and some of the membership records may be duplicates. The problem record pairs are listed below. Please copy the list to your clipboard for reference. Refer to <a href="http://wiki.civicrm.org/confluence/display/CRMDOC42/Repair+database+script+for+4.2+upgrades">this wiki page for instructions on repairing your database</a> so that you can run the upgrade successfully."

4. Message will be followed by a list of problem rows. Display a group of rows for each set of civicrm_membership_payment rows where a given contribution_id is linked to multiple membership_id's (and there is not corresponding line_item). Sets should be ordered by civicrm_membership_payment ID descending (newest first) Example:

Contact ID, Contribution ID, Contribution Amount, Contribution Status, Membership ID, Membership Type, Membership Start Date, Membership End Date, Membership Status.

151, 55, $100, Completed, 79, General, 01/01/2010, 12/31/2012, Current
151, 55, $100, Completed, 76, General, , , Pending

291, 97, $100, Completed, 85, General, 01/01/2010, 12/31/2012, Current
291, 97, $100, Completed, 84, General, , , Pending

5. Message and list of problem rows will also be written to the CiviCRM log file.

6. Create standalone PHP "repair" script which will delete the "extra" civicrm_membership_payment rows so that the upgrade can run (i.e. the invalidData query return 0 rows). The script should"

• Identify "problem sets" of membership_payment rows and iterate through them (same code / logic as we use to create the listing in step 4):

• Retain the newest membership_payment row in each set (MAX(civicrm_membership_payment.id), and delete all other membership_payment rows in the set
• Construct a list of the processed sets and write it to STDOUT using same table structure above, but adding an "Action Taken" column - value is "Un-linked" or "Retained" (see below). The instructions on the wiki will tell the user to pipe the output to "tee" and to a file for subsequent review, print-out, etc.

151, 55, $100, Completed, 79, General, 01/01/2010, 12/31/2012, Current, Retained
151, 55, $100, Completed, 76, General, , , Pending, Un-linked

291, 97, $100, Completed, 85, General, 01/01/2010, 12/31/2012, Current, Retained
291, 97, $100, Completed, 84, General, , , Pending, Un-linked

— original post ----
The deleteBadData() function in CRM/Upgrade/Incremental/php/FourTwo.php looks for bad data (meaning contribution records related to more than one membership) and deletes both the extra membership record and activity.

However, rather than finding contribution records associated with more than one membership it seems to find membership with more than one contribution (which is valid data).

The sql statement is:

SELECT cc.id, cmp.membership_id
FROM civicrm_membership_payment cmp
INNER JOIN `civicrm_contribution` cc ON cc.id = cmp.contribution_id
LEFT JOIN civicrm_line_item cli ON cc.id=cli.entity_id and cli.entity_table = 'civicrm_contribution'
INNER JOIN civicrm_membership cm ON cm.id=cmp.membership_id
INNER JOIN civicrm_membership_type cmt ON cmt.id = cm.membership_type_id
INNER JOIN civicrm_membership_payment cmp1 on cmp.contribution_id = cmp1.contribution_id
WHERE cli.entity_id IS NULL
GROUP BY cmp.membership_id
HAVING COUNT(cmp.contribution_id) > 1
ORDER BY cmp.membership_id ASC

I think it should be:

SELECT cc.id, cmp.membership_id
FROM civicrm_membership_payment cmp
INNER JOIN `civicrm_contribution` cc ON cc.id = cmp.contribution_id
LEFT JOIN civicrm_line_item cli ON cc.id=cli.entity_id and cli.entity_table = 'civicrm_contribution'
INNER JOIN civicrm_membership cm ON cm.id=cmp.membership_id
INNER JOIN civicrm_membership_type cmt ON cmt.id = cm.membership_type_id
INNER JOIN civicrm_membership_payment cmp1 on cmp.contribution_id = cmp1.contribution_id
WHERE cli.entity_id IS NULL
GROUP BY cmp.contribution_id
HAVING COUNT(cmp.membership_id) > 1
ORDER BY cmp.membership_id ASC