Thanks moiseh.

For those that may be helped by this, here’s a more complete query that lets you know the Subscription ID, the status of the subscription and you can add whatever variation meta values you’d like to compare (we have one called Subscription Type) to make it easy to find the offending subscriptions.

SELECT oi.order_id, p.post_type, p.post_status, oim.meta_value, oim2.meta_value
FROM wp_woocommerce_order_items oi
INNER JOIN wp_posts p ON p.ID = oi.order_id AND p.post_type='shop_subscription'
INNER JOIN wp_woocommerce_order_itemmeta oim ON oim.order_item_id = oi.order_item_id
INNER JOIN wp_woocommerce_order_itemmeta oim2 ON oim2.order_item_id = oi.order_item_id AND oim2.meta_key='subscription-type'
WHERE oim.meta_key='_line_total' AND oim.meta_value='0'

Tried to update the meta_values of line item directly in the database, but it doesn’t update the calculation of the subscription… So it seems the easiest way is to manually go into each subscription, remove the old variation and add the updated one.

moiseh, I recommend that you put a notice in the description of your plugin that you must first make sure that all subscriptions contain up to date product variations, because old variations will become $0 in the subscription.

Unfortunately a backup doesn’t help when the issue isn’t necessarily apparent until a few weeks after when $0 renewals go through. Too late to revert to a backup.